MySQL优化系列之查询优化

发表于 | 分类于 | 阅读次数:
字数统计: 5,116字 | 阅读时长 ≈ 19分钟

前言

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

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

本文讲解SQL查询优化

分析你的SQL

Explain查看SQL执行计划

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

1
2
3
4
5
6
7
mysql> explain select * from t where id = 1;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | t  | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set

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，表示结果来自于这个查询产生。如果是尖括号括起来的，与类似，也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集。

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更详细信息请查看官网

Profiling查看SQL查询性能

1.查看profile是否打开

1
2
3
4
5
6
7
8
mysql> show variables like "profiling%";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| profiling              | OFF   |
| profiling_history_size | 15    |
+------------------------+-------+
2 rows in set

2.当前会话开启profiling

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

3.show profiles

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

1
show profiles;

4.show profile

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SHOW PROFILE [type [, type] ... ]
    [FOR QUERY n]
    [LIMIT row_count [OFFSET offset]]
type:
    ALL
  | BLOCK IO
  | CONTEXT SWITCHES
  | CPU
  | IPC
  | MEMORY
  | PAGE FAULTS
  | SOURCE
  | SWAPS

例子：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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的消耗，对于定位性能瓶颈非常适用。更详细信息请查看官网

合理设计并利用索引

索引类型

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+树，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块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

何时创建索引

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

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

高性能索引策略

独立的列

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

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

前缀索引

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

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

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

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

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

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

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

覆盖索引

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

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

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

聚簇索引

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

索引会失效常见场景

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

错误的例子：

1
select * from t where id-1=9

正确的例子：

1
select * from t where id=10

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

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

1
select * from t where num=10

正确写法：

1
select * from t where num='10'

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

错误例子：

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

正确例子：

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

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

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

错误例子：

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

正确例子：

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

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

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

1
select id from t where num=@num

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

1
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