SQLmysql

MySQL调优笔记 (一)：索引优化，查询优化

SQL语句性能下降原因

MySQL性能瓶颈

• CPU：CPU饱和时一般发生在数据装入内存或者从磁盘读取数据时
• IO：IO瓶颈发生在装入数据远大于内存容量时
• 其他硬件瓶颈，通过 top, free, iostat, vmstat来查询系统状态

查询语句执行时间长，等待时间长可能有以下几种原因：

• 语句优化差（写的烂）
• 索引失效：Mysql有自己的SQL优化器(MySQL Query Optimizer)，特殊情况下，可能会导致虽然创建了索引，但是索引没有生效。
• 太多join：join笛卡尔积导致查询数据集疯狂增长
• 参数配置不合理，缓冲、线程数等

性能调优前提知识

索引

索引本质上是一种已排序的方便查找的数据结构

因此，索引具有以下优势：

• 提高检索效率，减少磁盘查询次数，降低IO
• 由于索引本身已经排序，通过索引排序，降低排序CPU

但是，索引其实也是一张表，创建索引同样存在劣势：

• 创建索引，将在索引表中创建索引主键，以及索引字段，并指向索引表，因此会占用存储空间
• 索引能够大大提高查询速度，但是会降低更新速度
• 如何创建优秀索引，需要花费大量时间研究优化

一般除了主键索引，唯一键索引和外键索引，一般不建议使用单值索引，一般高并发下，推荐使用复合索引； 建议为其他表的关联字段、频繁作为查询条件的字段、排序、分组、统计字段创建索引。

不建议在以下情况创建索引：

• 表很小，记录很少（10万以下） 官方数字800万开始出现性能下降，实际操作中，表数据超过300万就需要优化
• 经常增删改的表 增删改时，不仅要更新字段数据文件，还要修改对应的索引，保存索引文件；频繁修改，特别是删除操作，可能导致索引失效
• 数据重复且分布均匀的字段 如果数据列包含很多，重复内容（如性别字段），为该字段建立索引没有很大效果

工具：explain

开发过程中，遇到执行比较慢的SQL语句，可以使用explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。

explain + SQL语句

+----+--------------+--------+-------------+-------+----------------+------+----------+------+-------+-----------+--------+
| id |  select_type |  table |  partitions |  type |  possible_keys |  key |  key_len |  ref |  rows |  filtered |  Extra |
+----+--------------+--------+-------------+-------+----------------+------+----------+------+-------+-----------+--------+

其中各个字段的含义：

id

序列号，表示查询中执行select或操作的顺序

1. id相同时，执行顺序由上至下
2. 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
3. id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

select_type

示查询中每个select子句的类型

1. SIMPLE(简单SELECT,不使用UNION或子查询等)
2. PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)
3. UNION(UNION中的第二个或后面的SELECT语句)
4. DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
5. UNION RESULT(UNION的结果)
6. SUBQUERY(子查询中的第一个SELECT)
7. DEPENDENT SUBQUERY(子查询中的第一个SELECT，取决于外面的查询)
8. DERIVED(派生表的SELECT, FROM子句的子查询)
9. UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

table

显示这一行的数据是关于哪张表的，有时不是真实的表名字，可能是别名或者derivedx

partitions

该列显示的为分区表命中的分区情况。非分区表该字段为空（null）。

type

表示MySQL在表中找到所需行的方式，又称“访问类型”。 常用的类型有： ALL < index < range < ref < eq_ref < const < system 以及 NULL（ < 表示性能对比）

• ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
• index: Full Index Scan，index与ALL区别为index类型只遍历索引树
• range:只检索给定范围的行，使用一个索引来选择行
• ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
• eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
• const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system
• NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

key

实际使用的索引，如果为NULL表示没有使用索引。如果查询中使用了覆盖索引（Extra标志位 Using index），则该索引和查询的select字段重叠。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用关键字FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）,不损失精确性的情况下，长度越短越好。

ref

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。

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

rows

表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数。

filtered

这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比。

Extra

这个列可以显示的信息非常多，有几十种，常用的有：

• distinct：在select部分使用了distinc关键字
• no tables used：不带from字句的查询或者From dual查询
• 使用not in()形式子查询或not exists运算符的连接查询，这种叫做反连接。即，一般连接查询是先查询内表，再查询外表，反连接就是先查询外表，再查询内表。
• using filesort：排序时无法使用到索引时，就会出现这个。常见于order by和group by语句中
• using index：查询时不需要回表查询，直接通过索引就可以获取查询的数据。
• using join buffer（block nested loop），using join buffer（batched key accss）：5.6.x之后的版本优化关联查询的BNL，BKA特性。主要是减少内表的循环数量以及比较顺序地扫描查询。
• using sort_union，using_union，using intersect，using sort_intersection：
• using intersect：表示使用and的各个索引的条件时，该信息表示是从处理结果获取交集
• using union：表示使用or连接各个使用索引的条件时，该信息表示从处理结果获取并集
• using sort_union和using sort_intersection：与前面两个对应的类似，只是他们是出现在用and和or查询信息量大时，先查询主键，然后进行排序合并后，才能读取记录并返回。
• using temporary：表示使用了临时表存储中间结果。临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看status变量，used_tmp_table，used_tmp_disk_table才能看出来。
• using where：表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤。查询条件中分为限制条件和检查条件，5.6之前，存储引擎只能根据限制条件扫描数据并返回，然后server层根据检查条件进行过滤再返回真正符合查询的数据。5.6.x之后支持ICP特性，可以把检查条件也下推到存储引擎层，不符合检查条件和限制条件的数据，直接不读取，这样就大大减少了存储引擎扫描的记录数量。extra列显示using index condition
• firstmatch(tb_name)：5.6.x开始引入的优化子查询的新特性之一，常见于where字句含有in()类型的子查询。如果内表的数据量比较大，就可能出现这个
• loosescan(m..n)：5.6.x之后引入的优化子查询的新特性之一，在in()类型的子查询中，子查询返回的可能有重复记录时，就可能出现这个
• impossible where：这个值强调了where语句会导致没有符合条件的行。
• select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

对于extra列，官网上有这样一段话：

If you want to make your queries as fast as possible, look out for Extra column values of Using filesort and Using temporary, or, in JSON-formatted EXPLAINoutput, for using_filesort and using_temporary_table properties equal to true.

大概的意思就是说，如果你想要优化你的查询，那就要注意extra辅助信息中的using filesort和using temporary，这两项非常消耗性能，需要注意。

实际生产中，group by操作特别容易产生using filesort和using temporary，分组时请务必按照索引的顺序和个数。

工具：Profile

mysql提供了可以用来分析当前会话中语句执行的资源消耗情况、生命周期等细节。默认情况下profile处于关闭状态，且保存最近15次运行结果。

• 启用：set profiling=1;
• 查看： show profiles;

例子：

mysql> select uid from u_user_master where `id_card` like '%233%';
+-----+
| uid |
+-----+
|  48 |
|  62 |
|  67 |
|  68 |
|  69 |
| 140 |
| 145 |
| 146 |
| 166 |
+-----+
9 rows in set (0.00 sec)

查看mysql性能

mysql> show profiles;
+----------+------------+------------------------------------------------------------+
| Query_ID | Duration   | Query                                    |
+----------+------------+------------------------------------------------------------+
|      1 | 0.00033300 | select uid from u_user_master where `id_card` like '%233%' |
|      2 | 0.00005900 | show version()                              |
|      3 | 0.00013300 | select version()                             |
|      4 | 0.00041800 | select uid from u_user_master where `id_card` like '%233%' |
+----------+------------+------------------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)

第二列表示查询所用的时间。根据Query_ID 查看某个查询的详细时间耗费

mysql> show profile for query 4;
+----------------------+----------+
|  Status          | Duration |
+----------------------+----------+
| starting         | 0.000071 |
| checking permissions | 0.000007 |
| Opening tables     | 0.000016 |
| init            | 0.000023 |
| System lock       | 0.000020 |
| optimizing        | 0.000010 |
| statistics        | 0.000016 |
| preparing        | 0.000012 |
| executing        | 0.000003 |
| Sending data      | 0.000148 |
| end            | 0.000005 |
| query end        | 0.000006 |
| closing tables     | 0.000008 |
| freeing items      | 0.000020 |
| logging slow query   | 0.000041 |
| cleaning up       | 0.000012 |
+----------------------+----------+
16 rows in set, 1 warning (0.00 sec)

查看cpu、IO等信息

mysql> show profile block io, cpu for query 4;
+----------------------+----------+----------+------------+--------------+---------------+
|  Status          | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting         | 0.000071 | 0.000065 |   0.000005 |        0 |         0 |
| checking permissions | 0.000007 | 0.000005 |   0.000002 |        0 |         0 |
| Opening tables     | 0.000016 | 0.000016 |   0.000001 |        0 |         0 |
| init            | 0.000023 | 0.000021 |   0.000001 |        0 |         0 |
| System lock       | 0.000020 | 0.000009 |   0.000011 |        0 |         0 |
| optimizing        | 0.000010 | 0.000009 |   0.000002 |        0 |         0 |
| statistics        | 0.000016 | 0.000015 |   0.000001 |        0 |         0 |
|  preparing        | 0.000012 | 0.000011 |   0.000001 |        0 |         0 |
|  executing        | 0.000003 | 0.000001 |   0.000001 |        0 |         0 |
|  Sending data      | 0.000148 | 0.000148 |   0.000001 |        0 |         0 |
|  end            | 0.000005 | 0.000002 |   0.000002 |        0 |         0 |
|  query end        | 0.000006 | 0.000006 |   0.000001 |        0 |         0 |
| closing tables     | 0.000008 | 0.000007 |   0.000001 |        0 |         0 |
| freeing items      | 0.000020 | 0.000008 |   0.000012 |        0 |         0 |
|  logging slow query  | 0.000041 | 0.000022 |   0.000019 |        0 |         0 |
| cleaning up       | 0.000012 | 0.000011 |   0.000001 |        0 |         0 |
+----------------------+----------+----------+------------+--------------+---------------+
16 rows in set, 1 warning (0.00 sec)

如果详细信息中出现以下几种操作，说明该查询语句存在很大风险，非常耗资源

• converting Heap to MyISAM：查询结果太大，内存放不下，正在写入磁盘中
• creating tmp table：中间过程需要创建临时表（一般是group by），需要创建，拷贝数据，用完销毁，非常耗资源
• copying to tmp table on disk：将内存中的临时表写入磁盘
• locked： 锁

索引优化

单表优化

1. 对性能出现问题的语句，使用explain命令行，分析问题原因
2. 如果type出现All，表明该查询是全表扫描，需要优化，根据业务创建对应的复合索引，一般需要优化至ref或者range
3. 修改索引后，查看possible_keys key,确认修改索引是否生效，该查询是否使用预期索引
4. 如果extra出现using filesort，表明索引对于排序没有生效，需要优化索引
5. 如果extra出现using temporary，表示使用了临时表存储中间结果，极度影响性能。

两表优化（两表join）

1. left join，会返回所有左表结果，即使左表索引生效，仍旧会查询并返回所有左表记录，因此索引加在右表效果比较明显；
2. right join，类似上面，right join索引应该加在左表
3. 尽量减少join查询NestLoop循环总次数，即，永远使用小数据集驱动大数据集
4. 优先优化NestLoop内层循环
5. 当内存充足的情况下，可以适当调整JoinBuffer的设置

避免索引失效

1. 查询语句需要符合最佳左前缀法则（查询从索引的最左列开始，并且不能跳过索引中的列）
   • 部分匹配可以触发索引，但必须严格按照索引顺序
   • 查询条件包含不存在字段不能触发索引
   • 查询条件顺序与索引顺序不同不能触发索引
2. 不在索引列上做任何计算、函数、（自动或手动）类型装换，会导致索引失效而转向全表扫描
3. 搜索引擎索引不能使用范围条件右边的列，范围条件可以放在索引和查询的最后
4. 少用select *，尽量使用覆盖索引，即select 查询字段、顺序与索引保持一致或者符合最佳左前缀法则
5. 尽量不适用 != 或 <> 会导致全表扫描
6. is null 或者is not null不能使用索引
7. like '%condition%' 不能使用索引，生产情况可以使用覆盖索引提高like性能
8. 字符串不加单引号会导致索引失效 （发生自动类型转换）
9. 少用or，使用or也会导致索引失效

查询优化

一般步骤：

1. 运行程序1-2天，观察SQL运行情况
2. 开启慢查询日志，设置阈值（>5s），抓取SQL语句
3. explain 分析语句，调优索引或者语句
4. 如果还是慢， show profile，查看SQL执行细节和生命周期
5. 实在还是慢，调整mysql参数

如何打开慢查询日志：

• 查看状态 show variables like 'show_query_log%';
• 开启日志 set global slow_query_log=1
• 设置阈值 set global long_query_time=10 超过10秒的查询认为是慢查询
• 注意，上述设置需要打开会话才能生效，并且重启mysql后失效
• 可以用mysqldumpslow 工具查看并统计码查询日志

优化原则：

• 小表驱动大表
  • join时将小表作为基础表
  • select * from 小表 where exists （大表子查询）；select * from 大表 where id in （小表子查询）
• order by 优化
  • MySql支持两种排序方式，FileSort以及index，index指使用索引本身完成排序，效率较高，应避免使用FileSort方式
  • 当where子句与order by子句条件满足索引最佳左前缀法则，就能够使用index
  • 参数调整，当无法使用索引时，适当增加 sort_buffer_size max_length_sort_data，当读取文件超过缓存时，mysql的单路排序会转变成多路排序，甚至可能出现using temporary影响性能
• group by 优化
  • goup by的实质是先分组后排序，因此优化方法与order by一样
  • where性能高于having，能在where实现的条件尽量在where中实现

Order by 例子：

假设表中有a, b, c，d四个字段，索引为 Key a_b_c(a,b,c)

• order by 子句符合最佳左前缀
  • order by a
  • order by a,b
  • order by a,b,c
  • order by a desc, b desc, c desc
• 当where子句与order by子句条件满足索引最佳左前缀法则
  • where a=const orer by b,c
  • where a=const and b=const order by c
  • where a=const and b>const order by b,c
• 以下情况不能使用索引排序
  • 排序不一致：order by a asc, b desc, c desc
  • 丢失a字段：order by d,c
  • 不满足最佳左前缀：where a=const order by c
  • 条件包含索引外字段：where a=const order by a,d
  • where子句包含范围查询（范围查询之后的条件或子句都不能使用索引）：where a in (...) order by b,c

本站文章除注明转载/出处外，均为本站原创或翻译，转载前请务必署名,转载请标明出处
最后编辑时间为: 2019/02/05 19:26