慢查询问题一般都是由索引没有正确使用引起的。

索引介绍

索引的概念

索引是排好序的快速查找的数据结构

索引的优缺点

优点：

  • 查找：提高数据检索效率，降低IO成本。
  • 排序：通过索引对数据进行排序，降低排序成本，降低cpu消耗。

缺点：

  • 实际上索引也是一张表，该表保存了主键与索引字段，并指向索引记录，索引列也需要占空间。（占有磁盘空间）
  • 更新表时（insert、update、delete）不仅要保存数据还要更新保存索引文件新添加索引列。降低了速率。

    索引的分类

  1. 单值索引（单列索引）：一个索引只包含单个列，一个表中可以有多个单列索引。
  2. 唯一索引：索引必须唯一，但可以允许有空值。
  3. 复合索引：一个索引包含多个列

    哪些情况要建索引

  • 主键自动建主键索引
  • 频繁作为查询条件的字段应该创建索引
  • 查询中与其他表关联的字段，外键关系建立索引
  • 在高并发下倾向建立联合索引
  • 查询中的排序字段，排序字段若通过索引去访问将大大提高排序速度

  • 查询中统计或者分组的数据

    哪些情况不适合建索引

  • 频繁更新的字段

  • where条件用不到的字段不创建索引
  • 表记录太少
  • 经常增删改的表

  • 数据重复太多字段，为它建立索引意义不大（假如一个表有10万，有一个字段只有T和F两种值，每个值的分布概率大约只有50%，那么对这个字段的建索引一般不会提高查询效率，索引的选择性是指索引列的不同值数据与表中索引记录的比，，如果一个表中有2000条记录，表中索引列的不同值记录有1980个，这个索引的选择性为1980/2000=0.99，如果索引项越接近1，这个索引效率越高）

    慢查询（未命中索引）

    慢查询的定义

    mysql的慢查询日志是mysql提供的一种日志记录，用来记录在mysql中响应时间超过阈值的语句，具体指运行时间超过long_query_time值得SQL，则会被记录在慢查询日志中。

    慢查询相关参数

  • show_query_log：是否开启慢查询日志，1表示开启，0表示关闭。

  • log-slow-queries：旧版（5.6以下版本）mysql数据库慢查询日志存储路径。可以不设置该参数，系统会默认给一个缺省得文件host_name_slow.log
  • slow-query-log-file：新版（5.6以上版本）mysql数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省得文件host_name_slow.log
  • long_query_time：慢查询阈值，当查询时间高于设定得阈值时，记录到日志
  • log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）

默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的，可以通过设置slow_query_log的值来开启，如：
image.png

使用set global slow_query_log=1 开启了慢查询日志只对当前数据库生效，如果MySQL重启后则会失效。如果要永久生效，必须修改配置文件 my.cnf

ong_query_time的默认值为10 秒，支持二次修改。线上我们一般会设置成1秒，如果业务对延迟敏感的话，我们根据需要设置一个更低的值。
image.png

explain字段分析

explain是排查慢sql的一种常见的手段

  1. mysql> EXPLAIN SELECT 1;

a0c080290e010b9652e02237cd9220cb.png
id：表示select子句或者操作的顺序

  • id相同：执行顺序自上而下
  • id不同：id值越大优先级越高，越先被执行
  • id相同不同：id越大越先执行，相同的自上而下执行

select_type：主要是区分普通查询、联合查询、子查询等。

  • SIMPLE：简单的select查询，不包含子查询与union
  • PRIMARY：查询中包含复杂的子部分，最外层会被标记为primary
  • SUBQUERY：在select或者where列表中包含了子查询
  • DERIVED：在from列表中包含的子查询衍生表
  • UNION：若第二个select出现在union之后，则被标记为union
  • UNION RESESULT：从union表获取结果的select

table：这一行数据是哪个表的数据
type：查询中使用了何种类型
结果值从最好到最坏：system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>all

  • 一般来说，得保证查询至少达到range级别，最好能到达ref
  • ref：非唯一性索引扫描，返回匹配某个单独值的所有行
  • range：只检索给定范围的行，使用一个索引来选择行，一般就是在where语句中出现了between、<、>、in等的查询
  • index：index比all快，因为index是从索引中读取，all是从硬盘中读取
  • all：遍历全表才能找到

Extra：包含不适合在其他列中显示，但十分重要的的额外信息

  • Using filesort 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，mysql中无法利用索引完成的排序成为文件排序
  • Using temporary 使了用临时表保存中间结果，mysql在对查询结果排序时使用了临时表，常见于排序order by 和分组查询group by
  • Using index 表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率高
  • Using where 表明使用了where进行过滤
  • Using join buffer 使用了连接缓存
  • impossible where 如果where子句的值总是false，不能用来获取任何元组

  • select table optimized away 在没有group by子句的情况下，基于索引优化min/max操作或者对于myisam存储引擎优化count(*)操作，不必等到执行阶段再进行计算

    索引失效

  • 应该尽量全值匹配

  • 复合最佳左前缀法则（第一个索引不能掉，中间不能断开）
  • 不在索引列上做任何操作（计算、函数、类型转换）会导致索引失效而转向全表扫描
  • 存储存引擎不能使用索引中范围条件右边的列
  • 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select*
  • mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描
  • is null，is not null也会无法使用索引
  • like以统配符开头
  • 字符串不加单引号

  • 少用or

    order by优化

  • 避免filesort，尽量在索引上进行排序，遵照最佳左前缀原则

filesort有两种排序：

  • 双路排序：两次磁盘扫描
  • 单路排序：一次性读取保存在内存中，没拉完的数据再次拉
  • 单路排序总体好于双路排序
  • 优化策略：1、增大sort_buffer_size参数的设置，2、增大max_length_for_sort_data参数的设置，尽可能一次拿到内存

参考文献：https://mp.weixin.qq.com/s/sL64uQP0iHKxkMFx1QGLkg

慢查询（命中索引）

首先看以下几种场景的SQL语句执行时，命中的索引情况
1.执行 explain select from user; ，发现key这列为null，说明了没有命中索引，走了全表扫描。
image.png
2.执行 explain select from user where id= 10; ，发现key这列为PRIMARY，说明使用了主键索引。
image.png
3.执行 explain select user_name from user;，发现key这列为idx_user_name，说明使用了二级普通索引。
image.png
但是，实验发现，虽然走了二级索引，但是rows扫描行为79741，说明走了全表扫描，性能很差。
如果请求并发量很高，很容易引发数据库连接无法及时释放，导致客户端无法获取数据库连接而报错。

我们知道所有的数据都是存储在 B+ 索引树上，当执行 explain select * from user where id>0; 时，发现使用了主键索引。
image.png
mysql优化器根据主键索引找到第一个id>0的值，虽然走了索引但其实还是全表扫描。
没命中索引会走全表扫描，命中了索引依旧有可能走全表扫描
判断SQL性能时，不一定看是否命中索引；还要看扫描行数；当表很大时，不仅关注是否有索引，还要关注索引的过滤性是否足够好。

回表优化

为user表增加一个user_name和age的联合索引

  1. ALTER TABLE `user` ADD INDEX idx_user_name_age ( `user_name`,`age` );

image.png
执行explain select * from user where user_name like ‘Cherry1%’ and age =18;
image.png
执行流程：

  • 第一步：首先在idx_user_name_age索引树，查找第一个以’Cherry1开头的记录对应的主键id
  • 第二步：根据主键id从主键索引树中找到整行记录，并根据age做判断过滤，等于18则留下，否则丢弃，则这个过程也成为回表
  • 第三步：然后，在idx_user_name_age联合索引树上向右遍历，找到下一个主键id
  • 第四步：在执行第二步
  • 第五步：后面重复执行第三步，第四步，直到user_name不是以Cherry1开头，则结束。
  • 第六步：返回所有查询结果

分析：
由于按user_name 的前缀匹配，idx_user_name_age二级索引中的 age 部分并没有发挥作用。导致了大量回表查询，性能较差。
优化后，执行流程：

  • 第一步：首先在idx_user_name_age索引树，查找一个以Cherry1开头的索引记录
  • 第二步：然后，判断这个索引记录中的age是否等于18.如果是回表取出整行数据，作为后面的结果返回；如果不是则丢弃。
  • 第三步：在idx_user_name_age联合索引树上向右遍历，重复第二步，直到user_name不是以Cherry1开头，则结束。
  • 第四步：返回所有查询结果

和上面的过程差别，在于判断age是否等于18放在遍历联合索引过程中进行，不需要回表判断，大大降低了回表的次数，提升性能。
这个优化遵循最左前缀原则，索引的过滤性仍然有提升空间。

虚拟列

mysql5.7之后引入了虚拟列。 在MySQL 5.7中，支持两种Generated Column，即Virtual Generated Column和Stored Generated Column，前者只将Generated Column保存在数据字典中（表的元数据），并不会将这一列数据持久化到磁盘上；后者会将Generated Column持久化到磁盘上，而不是每次读取的时候计算所得。很明显，后者存放了可以通过已有数据计算而得的数据，需要更多的磁盘空间，与Virtual Column相比并没有优势，因此，MySQL 5.7中，不指定Generated Column的类型，默认是Virtual Column。
修改表结构

ALTER TABLE `user` add user_name_first varchar(12) generated always as (left(user_name,6)) , add index(user_name_first,age);

image.png
执行 explain select * from user where user_name_first like ‘Cherry1%’ and age =18;
image.png
比较发现，扫描行数row变小了，证明优化有效。

slow_query_log 收集到的慢 SQL ，结合 explain 分析是否命中索引，结合扫描行数，有针对性的优化慢 SQL。
但是要注意一点，慢 SQL 日志中也可能有正常的 SQL，可能只是当时CPU等系统资源过载，影响到正常 SQL 的执行速度。慢查询和索引没有必然联系，一个SQL语句的执行效率最终要看的是扫描行数。另外可以使用虚拟列和联合索引来提升复杂查询的执行效率。