MySQL优化系列之查询优化

傷城~ 2022-06-08 04:25 269阅读 0赞

# **前言** #

MySQL优化有两大层面，第一是数据库层面，第二是硬件层面。数据库层面可以细分为以下几个方面：

*  [数据库schema设计优化][schema]
 *  [SQL查询优化][SQL]
 *  锁策略优化
 *  存储引擎优化
 *  服务器配置优化
 *  主从，读写分离
 *  集群，负载均衡

本文讲解SQL查询优化

# **分析你的SQL** #

## **Explain查看SQL执行计划** ##

Explain命令用来帮助我们获取Query的执行计划，用法很简单：在你的Query前面加上这个命令就行，例如：

Explain信息解释：

### **id** ###

*  Query Optimizer所选定的执行计划中查询的序列号，id相同顺序执行， id不同，数字越大，越先执行

### **select\_type** ###

所使用的查询类型，主要有以下这几种查询类型

*  SIMPLE：查询中不包含子查询或者UNION，有连接查询时，外层的查询为SIMPLE，且只有一个
 *  PRIMARY：查询中包含子查询或者UNION，最外层查询被标记为PRIMARY，注意并不是主键查询
 *  UNION：UNION 语句中第二个SELECT 开始的后面所有SELECT，第一个SELECT 为PRIMARY
 *  UNION RESULT：包含UNION 的结果集，在UNION 和UNION ALL语句中，因为它不需要参与查询，所以id字段为NULL
 *  DEPENDENT UNION：与UNION 一样，出现在UNION或UNION ALL语句中，但是这个查询要受到外部查询的影响
 *  SUBQUERY：子查询内层查询的第一个SELECT，结果不依赖于外部查询结果集；
 *  DEPENDENT SUBQUERY：子查询中内层的第一个SELECT，依赖于外部查询的结果集；
 *  DERIVED：FROM字句中出现的子查询，也叫做派生表

### **table** ###

显示的查询表名，如果查询使用了别名，那么这里显示的是别名，如果不涉及对数据表的操作，那么这显示为null，如果显示为尖括号括起来的就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生。如果是尖括号括起来的

### **type** ###

查询类型，评估查询性能好坏的重要指标，按照效率从高到底排序：

*  const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下， 使用system
 *  eq\_ref：唯一性索引扫描，最多只会有一条匹配结果，一般是通过主键或者唯一键索引来访问
 *  ref：非唯一性索引扫描，返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找
 *  ref\_or\_null：与ref 的唯一区别就是可以搜索包含null值的行
 *  index\_merge：查询中同时使用两个（或更多）索引，常见and ，or的条件使用了不同的索引
 *  rang：索引范围扫描，返回匹配值域的行，常见于between、in 、like、<、>等查询
 *  index：全索引扫描，index与all区别为index类型只遍历索引树，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询
 *  all：全表扫描，遍历全表以找到匹配的行

### **possible\_keys** ###

该查询可以利用的索引. 如果没有任何索引可以使用，就会显示成null，这一  
项内容对于优化时候索引的调整非常重要；

### **key** ###

MySQL Query Optimizer从possible\_keys中实际选择使用的索引，若没有使用索引则显示为NULL

### **key\_len** ###

被选中使用索引的索引键长度，key\_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key\_len中。key\_len字节的计算规则：

*  字符串：char(n)-n字节，varchar(n)-n字节 + 2字节(变长) ，多字节charset \* \[1~4\]字节（utf8为3字节，utf8mb4为4字节计算）
 *  数值类型：TINYINT-1字节，SMALLINT-2字节， MEDIUMINT-3字节， INT-4字节，BIGINT-8字节
 *  时间类型：DATE-3字节， TIMESTAMP-4字节， DATETIME-8字节
 *  字段属性：NULL属性+ 1字节

### **ref** ###

如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

### **rows** ###

MySQL Query Optimizer通过系统收集到的统计信息估算出来扫描行数，不是精确值

### **extra** ###

查询中每一步实现的额外细节信息，下面列举几个比较常见的：

*  Using index：表示MySQL将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了
 *  Using where：如果查询未能使用索引，Using where的作用只是提醒我们MySQL将用where子句来过滤结果集
 *  Using filesort：无法利用索引完成的排序，需要额外的排序操作，可能在内存也可能在磁盘完成，常见于group by和order by 操作中
 *  Using temporary：表示使用了临时表存储中间结果，临时表可以是内存临时表和磁盘临时表。主要常见于group by、order by和多表join等操作中。
 *  Distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作
 *  Not exists： 优化left join操作，一旦它找到了匹配left join标准的行，就不再搜索了

Explain更详细信息请查看[官网][Link 1]

## **Profiling查看SQL查询性能** ##

### **1.查看profile是否打开** ###

### **2.当前会话开启profiling** ###

mysql> set profiling=1;
    Query OK, 0 rows affected

### **3.show profiles** ###

show profiles获取系统中保存的所有Query 的profile概要信息

show profiles;

### **4.show profile** ###

SHOW PROFILE *\** FOR QUERY n 获取单个查询的profile信息，比如cpu、io等，语法如下：

例子：

mysql> DROP TABLE IF EXISTS t1;
    Query OK, 0 rows affected
    
    mysql> CREATE TABLE T1 (id INT);
    Query OK, 0 rows affected
    
    mysql> SHOW PROFILES;
    +----------+------------+--------------------------+
    | Query_ID | Duration   | Query                    |
    +----------+------------+--------------------------+
    |        1 |  0.0004705 | DROP TABLE IF EXISTS t1 | | 2 | 0.81492075 | CREATE TABLE T1 (id INT) | +----------+------------+--------------------------+ 2 rows in set mysql> SHOW PROFILE CPU, BLOCK IO FOR QUERY 2;
    +----------------------+----------+----------+------------+--------------+---------------+
    | Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
    +----------------------+----------+----------+------------+--------------+---------------+
    | starting             | 0.000155 | 0        | 0          |            0 |             0 |
    | checking permissions | 3.4E-5   | 0        | 0          |            0 |             0 |
    | Opening tables       | 9.7E-5   | 0        | 0          |            0 |             0 |
    | creating table       | 0.814011 | 0.013998 | 0.006999   |            0 |           304 |
    | After create | 0.000168 | 0 | 0 | 0 | 0 | | query end | 3.6E-5 | 0 | 0 | 0 | 0 | | closing tables | 5.1E-5 | 0 | 0 | 0 | 0 | | freeing items | 8E-5 | 0 | 0 | 0 | 0 | | cleaning up | 0.00029 | 0 | 0 | 0 | 0 | +----------------------+----------+----------+------------+--------------+---------------+ 9 rows in set

通过查看profiling信息，我们可以清楚知道单个Query的CPU和Block IO的消耗，对于定位性能瓶颈非常适用。更详细信息请查看[官网][Link 2]

# **合理设计并利用索引** #

## **索引类型** ##

MySQL中，主要有四种类型的索引：

### **1.B-Tree索引** ###

我们通常说的索引指的是B-Tree索引，MySQL除了Archive存储引擎之外的其他所有的存储引擎都支持B-Tree索引，最主要的索引类型，Innodb引擎使用的是B+Tree索引，是B-Tree索引的一种。下面会详细介绍B-Tree原理

### **2.Hash索引** ###

Hash索引在MySQL 中使用的并不是很多，目前主要是Memory存储引擎使用。Hash索引可以一次性定位到查询目标，不像B-Tree索引需要从跟节点遍历到叶节点，效率很高，但是却没有广泛使用，原因是Hash索引也有它的限制：

*  Hash索引指包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过，访问内存中的行的速度很快，大部分情况下对性能影响并不明显
 *  Hash索引只支持等值比较查询，包括=、in()和<=>查询，不能使用范围查询；
 *  Hash索引并不是按照索引指顺序存储的，所以无法用于排序
 *  存在大量Hash冲突时，会以链表的方式存放多个记录指针到同一个哈希条目中，这样维护操作代价就很高，查找性能可能变得更差

### **3.Full-Text索引** ###

即全文索引，CHAR、VARCHAR、和TEXT这三种数据类型的列可以建Full-Text索引，主要用来代替效率低下的like模糊查询。类似搜索引擎做的事情，适用match against操作，而不是普通的where条件操作

### **4.R-Tree索引** ###

空间数据索引，可以用作地理数据存储，只有MyISAM引擎支持，与B-Tree不同，这类索引无须前缀查询，能从所有维度来索引数据。必须使用MySQL的GIS相关函数来维护数据。MySQL对GIS支持并不完善，对GIS的解决方案做的比较好的是PostgreSQL的PostGIS。

## **索引原理** ##

数据库查找数据有两种方式，第一种就是遍历表，一条一条找，第二种方式就是通过索引，好比通过字典查找某个字一个，哪种效率更高不得而知。索引是数据库优化最有效的手段，能够轻易将查询性能提升几个数量级。下面将详细介绍Innodb存储引擎的B+Tree索引原理。

### **详解B+树** ###

![btree][]

如上图，是一颗B+树，关于B+树的定义可以参见[B+树][B]，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

### **B+树的查找过程** ###

如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的B+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

### **B+树性质** ###

1.通过上面的分析，我们知道IO次数取决于B+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么B+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。

2.当B+树的数据项是复合的数据结构，比如(name,age,sex)的时候，B+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，B+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，B+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，B+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

B-Tree扩展阅读：[http://blog.csdn.net/v\_JULY\_v/article/details/6530142/\#t7][http_blog.csdn.net_v_JULY_v_article_details_6530142_t7]

## **何时创建索引** ##

索引是完全独立于基础数据之外的一部分数据，如果一个表的索引过多，而索引字段频繁的新增或插入，那将消耗大量的CPU和IO来维护索引，重新调整B-Tree。那么，应该何时创建索引？

*  较频繁的作为查询条件的字段应该创建索引，相反，不会出现在WHERE子句中的字段不该创建索引
 *  唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件，比如一些状态字段，就那么几个值，索引过滤效果差
 *  更新非常频繁的字段不适合创建索引

## **高性能索引策略** ##

### **独立的列** ###

独立的列是指索引列不能是表达式的一部分，也不能是函数的参数，比如下面的sql都无法使用索引：

select name from t where id + 1 = 5;
    select name from t where to_days(date_col) <= 5;

### **前缀索引** ###

有时候需要索引很长的字符列，这会让索引变大且慢。通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但是这样也会降低索引的选择性。索引选择性是指，不重复的索引值(也称为基数，cardinality)和数据表的总记录数（\#T）的比值，范围从1/\#T到1之间。唯一索引的选择性是1，这是最好的索引选择性，性能也最好。

当前缀的选择性越接近全列选择性的时候，索引效果越好。因此，对于前缀索引，找到合适长度的前缀是关键，我们可以这么计算全列选择性：

# 全列选择性
    SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;
    # 测试某一长度前缀的选择性
    SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;

找出合适的长度后建立前缀索引：

ALTER TABLE table_name ADD KEY(column_name(prefix_length));

对于blog、text或者很长的varcharr类型列，必须使用前缀索引，因为MySQL不允许索引这些列的完成长度。前缀索引是一种能使索引更小更有效的方法，但是也有缺点：

*  无法使用前缀索引做order by和group by
 *  无法使用前缀索引做覆盖扫描（后面会介绍覆盖索引）

### **覆盖索引** ###

索引不且可以查询数据，还可以直接获取列的数据，因为索引的叶子节点已经包含要查询的数据，所以就不需要再读取数据行（也称回表操作）。如果索引包含（或者说覆盖）所有需要查询的字段的值，我们称之为覆盖索引。因为覆盖索引必须存储索引列的值，所以只有B-Tree索引能使用覆盖索引。举个例子，对于下面查询:

SELECT col1,col2 FROM t WHERE col1 = 1 AND col2 = 1

如果col1和col2列分别建立单列索引，是无法使用覆盖索引的，如果col1和col2列建立复合索引就可以使用覆盖索引

### **聚簇索引** ###

聚簇索引，并不是一种单独的索引类型，而是一种数据存储方式。InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行信息。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。这里主要讨论InnoDB引擎，InnoDB通过主键聚集数据，如果没有主键会选择一个非空的唯一索引，如果还没有，InnoDB会隐式定义一个主键来作为聚簇索引。因为索引和数据保存在同一个B-Tree中，所以聚簇索引通常比非聚簇索引查找更快。但是缺点也很明显，更新聚簇索引列的代价很高。篇幅有限，关于聚簇索引不详细展开

## **索引会失效常见场景** ##

1.查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，\*，/，! 等)，导致索引失效

错误的例子：

select * from t where id-1=9

正确的例子：

select * from t where id=10

2.使用不等于（!= 或者<>）导致索引失效，id<>3可使用id>3 or id<3来代替

3.隐式转换导致索引失效，比如varchar类型的列num创建了索引，下面SQL索引失效

select * from t where num=10

正确写法：

select * from t where num='10'

4.like操作如果条件以通配符开始（比如常见的%，但要注意通配符并不是只有%），由于索引的前缀性导致索引失效

错误例子：

select id from t where name like ‘%abc%，

正确例子：

select id from t where name like ‘abc%‘

5.join语句中join条件字段类型不一致的时候索引失效

6.条件中有or导致索引失效（并不绝对，MySQL5.0及更高版本可以使用多列索引、或者优化器自己使用了索引合并策略）

错误例子：

select id from t where num=10 or num=20

正确例子：

select id from t where num=10 union all select id from t where num=20

7.not in导致索引失效，可以用not exists代替

8.如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

select id from t where num=@num

可以改为强制查询使用索引：

select id from t with(index(索引名)) where num=@num

9.is null不使用索引，因为索引不会包含null值，不会出现在索引树上，所以建表时尽量不要用null,可以用0等代替null值

10.使用非等值查询的时候MySQL无法使用Hash索引

# **其他注意点** #

1.  不要使用select \* ，将\*解析为对应的列需要额外操作，而且如果有排序操作的，会加载很多无用的列到内存，浪费资源，所以应该返回只需要的具体的列
2.  当只要一行数据时使用LIMIT 1，MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据

# **参考** #

\[1\]《高性能MySQL》  
\[2\] [https://tech.meituan.com/mysql-index.html][https_tech.meituan.com_mysql-index.html]

[schema]: https://blog.csdn.net/u014513883/article/details/77981645
[SQL]: https://blog.csdn.net/u014513883/article/details/77981608
[Link 1]: https://dev.mysql.com/doc/refman/5.5/en/explain-output.html
[Link 2]: https://dev.mysql.com/doc/refman/5.5/en/show-profile.html
[btree]: /images/20220608/c0729bb6a05a4a9b881e9cabd3cc6cb2.png
[B]: https://zh.wikipedia.org/wiki/B%2B%E6%A0%91
[http_blog.csdn.net_v_JULY_v_article_details_6530142_t7]: http://blog.csdn.net/v_JULY_v/article/details/6530142/#t7
[https_tech.meituan.com_mysql-index.html]: https://tech.meituan.com/mysql-index.html