【享学MySQL】慢查询分析与排查

最新推荐文章于 2024-09-13 10:02:27 发布

少侠露飞

最新推荐文章于 2024-09-13 10:02:27 发布

阅读量371

点赞数

分类专栏：《数据库》MySQL原理及实践文章标签：数据库索引 mysql

本文链接：https://blog.csdn.net/carson_chu/article/details/112795956

版权

《数据库》MySQL原理及实践专栏收录该内容

16 篇文章 1 订阅

订阅专栏

引言

这篇文章主要是站在老司机的肩膀上从原理总结、MySQL慢查询优化方法、case案例分析等几个方面结合自己这段时间在工作上遇到的慢查询谈谈数据库索引的原理和如何优化慢查询。一方面给自己总结，另一方面希望和大家共同学习进步。

1 原理回顾

这部分我主要想总结一下数据库索引的原理，可能是老生常谈的东西了，慢查询这个词主要的重点就是慢，就像我们开车一样，我们发车前最重要的就是了解这部车，而我们要知道我们的SQL语句为什么会慢，我们当然必须对数据的查找过程有所了解。

1.1 磁盘IO

举个例子。其实对于数据索引这样的例子，在我们日常生活其实也是很多，通常大家都举查字典的例子吧？为了新鲜感，我换一个，比如你找对象，如果你是男的，你最先的目标是女孩子的吧（除个别外），这样我们就排除了一部无效数据，然后咱们再选咱们同一个城市的吧？又剔除了一部分无效数据，最后我们再选年龄等，最后留下了我们目标人群。这种查找过程其实也是一种索引过程。

磁盘IO。我们计算机是怎么查询数据的呢？当计算机把数据保存在磁盘上，而为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，所以结合我们的例子以及计算机查询数据的原理，为了提高查询数据的查询速度，需要保证最小的IO次数，B+树的数据结构应运而生。

1.2 索引结构

在这里插入图片描述

如上图，是一颗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并不真实存在于数据表中。
如果要查找数据项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，显然成本非常非常高。
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的数据了，这个是非常重要的性质，即索引的最左匹配特性。

2 慢查询优化

从上面的原理我们可以知道，我们其实要做的就是让数据库在查找数据时，尽可能地选择最短的路径查找到想要的数据，尽可能地减少磁盘IO次数。

2.1索引的一些概念

索引概念（重要）

排好序的快速查找的数据结构（我们平时说的索引，如果没有特别指明，都是指B树，其中聚集索引、次要索引、覆盖索引、复合索引、前缀索引、唯一索引默认使用的都是B+树索引，除B+树这种类型的索引外还有哈希索引等）
优缺点---->何种情况建索引

1、优点
查找：提高数据检索效率，降低IO成本。

排序：通过索引对数据进行排序，降低排序成本，降低cpu消耗

2、缺点

实际上索引也是一张表，该表保存了主键与索引字段，并指向索引的记录，所以索引列也需要占空间。

更新表时（insert、update、delete）不仅要保存数据还要更新保存索引文件新添加的索引列。
索引分类

1、单值索引（单列索引）：一个索引只包含单个列，一个表中可以有多个单列索引。

2、唯一索引：索引列必须唯一，但可以允许有空值

3、联合索引：一个索引包含多个列
MySQL索引结构

1、BTree索引

2、Hash索引

3、full-text全文检索

4、R-Tree索引

2.2 适合创建索引和不适合创建索引的情况

哪些情况要建索引

1、主键自动建主键索引

2、频繁作为查询条件的字段应该创建索引

3、查询中与其他表关联的字段，外键关系建立索引

4、在高并发下倾向建立组合索引

5、查询中的排序字段，排序字段若通过索引去访问将大大提高排序速度

6、查询中统计或者分组的数据
哪些情况不适合建索引

1、频繁更新的字段

2、where条件用不到的字段不创建索引

3、表记录太少

4、经常增删改的表

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

3. 原理分析（explain字段分析）

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

1、id相同：执行顺序自上而下

2、id不同：id值越大优先级越高，越先被执行

3、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

system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现

const：表示通过索引一次就能够找到

eq_ref:唯一性索引扫描，对于每个索引键，表示只有一条记录与之匹配，常见于主键或唯一索引扫描

ref：非唯一性索引扫描，返回匹配某个单独值的所有行

range：只检索给定范围的行，使用一个索引来选择行，一般就是在where语句中出现了between、<、>、in等的查询

index：index比all快，因为index是从索引中读取，all是从硬盘中读取

all：遍历全表才能找到
possible_key：显示可能应用在这张表中的索引，但实际上不一定用到
key：实际上使用的索引，如果没有则为null
key_len：表示索引中使用的字节数（可能使用的，不是实际的），可通过该列查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好
ref：显示索引的哪一列被用到，如果可能的话是一个常数，哪些常量被用于查找索引列上的值
rows：大致估算找出所需的记录要读取的行数
Extra：包含不适合在其他列中显示，但十分重要的的额外信息

1、Using filesort 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，mysql中无法利用索引完成的排序成为“文件排序”

2、Using temporary 使了用临时表保存中间结果，mysql在对查询结果排序时使用了临时表，常见于排序order by 和分组查询group by

3、Using index 表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率高，如果同时出现了using where 表明索引被用来执行索引键值查找，如果没有出现 using where 表明索引用来读取而非执行查询动作。

4、Using where 表明使用了where进行过滤

5、Using join buffer 使用了连接缓存

6、impossible where where子句的值总是false，不能用来获取任何元组

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

8、distinct:在优化distinct操作，在找到第一匹配的元组后即停止找到同样值的动作

3.1 索引失效_复合索引（避免）

1、应该尽量全值匹配

2、复合最佳左前缀法则（第一个索引不能掉，中间不能断开）

3、不在索引列上做任何操作（计算、函数、类型转换）会导致索引失效而转向全表扫描

4、储存引擎不能使用索引中范围条件右边的列

5、尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select*

6、mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描

7、is null，is not null也会无法使用索引

8、like以通配符开头

9、字符串不加单引号

10、少用or（可以用union all优化）

3.2 order by优化

1、避免filesort

2、尽量在索引上进行排序，遵照最佳左前缀原则

3、filesort有两种排序：

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

原因：尽可能一次拿到内存

4.真实Case Study

4.1 join慢查询

  慢查询压垮了DB，了确认刚刚上线的效果，李某通过命令行连上db502执行了两条慢查询SQL，发现sql写错后执行中断操作，但是客户端与数据库突然断开连接，李某并未注意到。由于用户JAVA服务暂时没有数据库重连机制，影响到约三分之一的需要获取用户信息的JAVA服务接口。

李某执行的sql语句如下：

代码块

select * from useracct join userinfo order by useracct.id desc limit 11;
select * from useracct join userinfo where useracct.id > 116277616 order by useracct.id desc limit 11;

对第一条sql进行explain可以发现，因为忘写了join的on条件，这是扫全表sql，如下图：

在这里插入图片描述

我们首先看type级别两个表的级别都是ALL，说明该条语句没有用到索引，做了全表扫描是最差的情况，

4.2 order by慢查询

该案例中的慢查询语句：

SELECT *  FROM `coupon_userid_61` FORCE INDEX (`orderid`)  WHERE `userid` = 91241561 AND `status` IN (0,144) ORDER BY `id` ASC ;

该SQL由于强制指定了使用orderid索引，但条件中并没有orderid，导致产生全表扫描（type: ALL）；如下为问题SQL和正确SQL的执行计划：

mysql> explain SELECT *  FROM `coupon_userid_61` FORCE INDEX (`orderid`)  WHERE `userid` = 91241561 AND `status` IN (0,144) ORDER BY `id` ASC ;

mysql> explain SELECT *  FROM `coupon_userid_61` FORCE INDEX (`orderid`)  WHERE `userid` = 91241561 AND `status` IN (0,144) and orderid=1 ORDER BY `id` ASC ;