MySQL实战之——索引选择分析

最新推荐文章于 2023-11-22 15:30:23 发布

Eternal_yys

最新推荐文章于 2023-11-22 15:30:23 发布

阅读量304

点赞数

分类专栏： MySQL 文章标签： MySQL索引

本文链接：https://blog.csdn.net/eternal_yangyun/article/details/102529799

版权

MySQL 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

MySQL实战之——索引选择分析

文章目录

MySQL实战之——索引选择分析

再MySQL中，一张表是可以有多个索引的，那么当我们没有指定使用哪个索引的时候，MySQL在执行SQL语句的时候， 索引的选择发生在优化器中 ，那么本片博客带你来分析一种情况： MySQL选择索引出错 ；

1. 建表插数据过程（实验准备）

先来建立一张表：

mysql> create table test3(
    -> id int(11) primary key,    #主键索引
    -> a int(11),
    -> b int(11),
    -> index(a),     #建立普通索引
    -> index(b));
Query OK, 0 rows affected (0.93 sec)

建立好test3这张表，下面查看一下它的结构：

mysql> desc test3;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
| a     | int(11) | YES  | MUL | NULL    |       |
| b     | int(11) | YES  | MUL | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.19 sec)

接下来我们向这张表中插入10万行记录，取值按整数递增，即：（1，1，1）、（2，2，2）…（100000，100000，100000）；这里采用存储过程来进行快速插入：

mysql> delimiter ;;
mysql> create procedure idata()
    -> begin
    -> declare i int;
    -> set i=1;
    -> while(i<=100000)do
    -> insert into test3 values(i,i,i);
    -> set i=i+1;
    -> end while;
    -> end;;
Query OK, 0 rows affected (0.20 sec)

mysql> delimiter ;
mysql> call idata();

在进行插入过程前，注意先设置下面两个参数为0，否则插入过程时间将会高达10分钟左右，下面参数关闭了
设置这两个参数后，会发现10万条记录的插入只需要几秒；

mysql> set global innodb_flush_log_at_trx_commit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global sync_binlog = 0;
Query OK, 0 rows affected (0.04 sec)

2. 数据的查询

1. 正常案例

基于上面建立的表，执行下面的查询语句分析（用explain）：

mysql> explain select * from test3 where a between 10000 and 20000;
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+
|  1 | SIMPLE      | test3 | NULL       | range | a             | a    | 5       | NULL | 10001 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+
1 row in set, 1 warning (0.15 sec)

可以看到，key的类型是a，这符合我们的预期，执行该语句使用了a索引，索引很快就能执行完成；

3. 异常案例一

1. 案例背景

接下来我们做如下操作，执行两个事物分别如下：
事物A：
在这里插入图片描述

2. 异常案例分析（对于异常案例一的分析）

1. 优化器如何选择索引

导致上面索引选错的原因就是优化器选择索引的时候出现了问题，那么为什么会选错，此前我们必须先了解优化器选择索引的标准是什么；
我们知道：优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行SQL语句；

在数据库中，扫描行数是影响执行代价的因素之一
- 扫描行数越小，则执行代价越小，因为访问磁盘的次数就会越少，消耗的CPU资源越少；
注意：执行代价的影响因素不止上面这一个，比如还有是否使用临时表、是否排序等等因素;

那么，扫描行数是如何判断的呢？

在上面的查询语句中，其实MySQL并不能知道满足这个查询的行数有多少，只能根据统计信息来估算记录数；

而这个统计信息就是索引的区分度：

一个索引上不同的值越多，这个索引的区分度就越好，而一个索引上不同值的个数，我们称之为基数；
- 所以基数越大，这个索引的区分度就越好
我们可以通过show index from test3；这条语句来查看这个表中索引信息；
- Cardinality 这一列就代表该索引的基数；

进一步，那么MySQL是如何得到索引的基数的呢？

采用了采样统计的方法；
- 由于得到精确结果需要取整张表，代价太高，所以只能选择”采样统计“；
采样统计方法过程：
- InnoDB默认选择N个数据页，统计这些页面上的不同值，算出平均值，最后将平均值*这个索引的页面数就得到了这个索引的基础；

我们可以通过explain SQL语句来查看执行这条SQL语句的具体信息，里面有一列为rows，这个数就是优化器预估执行这条语句预计扫描行数；
$(img-3knz3fG8-1570930835822)(C:\Users\Laptop\AppData\Local\Temp\1570787975512.png)]$

从这张图可以看到，在上面的row还算差不多，而下面的，本来应该在10000左右，结果为37116，差距就有点大了，看来选错索引的原因就在这了；
可能有人会问，上面不走索引下预算扫描104620行，下面预算只需要扫描37116行，那么为什么优化器还选择上面呢？

原因：在下面使用a索引查询时，需要回表查询，即回到主键取出整行数据，这个代价优化器也算入的，而在主键上，虽然扫描行数多，但不需要回表；
从结果来看，优化器认为不使用索引的代价比较小，这是正确的（假如下面的预估行数是正确的）；
但从我们查询的结果来看，不使用索引时间比较长，所以我们认为优化器的选择有误；

那么归于一切，还是因为估算行数差距太大导致优化器综合来看选择了不使用索引，即假如估算使用索引a要查询的行数正确的话，后面就肯定会选择a索引，因为在最开始的正确案例中预估行数为10001是正确的，优化器就选择了索引a去查询；

2. 如何修正统计信息（以及异常案例一总结）

对于异常案例一，我们只需修复统计信息即可，可以用如下的命令：（analyze）

mysql> analyze table test3;
+-------------+---------+----------+----------+
| Table       | Op      | Msg_type | Msg_text |
+-------------+---------+----------+----------+
| java7.test3 | analyze | status   | OK       |
+-------------+---------+----------+----------+
1 row in set (0.15 sec)

用上面的语法后，再在异常案例一中执行那句查找就会发现预估行数就正确了（10001），此时优化器就选择了索引a来进行查询；

总结：

对于案例一，在我的实践过程中其实并没有出现索引失效的情况，估计是出现上面那种情况是小概率的，但我们要预防那种情况的出现，能找出应对策略（修正统计信息）；
所以在实践中，如果发现MySQL的预估行数（explain语句后的rows那一行）与实际要查询的行数相差太大导致的优化器选择索引出错，则可以通过修正统计信息来进行更正；（⭐⭐）

4. 异常案例二

1. 案例背景

对于异常案例一，我们知道了，优化器索引失效的原因是统计信息的偶尔出错（预估行数差距较大），对于这种情况，通过analyze就可以解决大多数情况，但是我们得注意，优化器不仅仅只看扫描行数；

下面来看看这条查询语句（还是我们那个表）

mysql> select * from test3 where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
Empty set (0.17 sec)

可以知道，我们查询的结果就是为空；
（order by 表示排序查询，这里默认升序，配合limit表示结果取第一个，如果后面是x，则代表取前x个）
例如下面的查询：

mysql> select * from test3 where (a between 1 and 1000) and (b between 500 and 1000) order by b limit 5;
+-----+------+------+
| id  | a    | b    |
+-----+------+------+
| 500 |  500 |  500 |
| 501 |  501 |  501 |
| 502 |  502 |  502 |
| 503 |  503 |  503 |
| 504 |  504 |  504 |
+-----+------+------+
5 rows in set (0.00 sec)

2. 案例分析

对于上面那个查询，我们试着来分析一下优化器会选择哪个索引来执行那条SQL语句：（只有可能下面两种方案）

如果使用索引 a 进行查询，那么就是扫描索引 a 的前 1000 个值，然后取到对应的 id，再到主键索引上去查出每一行，然后根据字段 b 来过滤。显然这样需要扫描 1000 行。
如果使用索引 b 进行查询，那么就是扫描索引 b 的最后 50001 个值，与上面的执行过程相同，也是需要回到主键索引上取值再判断，所以需要扫描 50001 行。

按照我们对异常案例一的分析，查询行数是优化器选择索引的一个因素，这里显然优化器会选择索引a，因为它的查询行数只有1000行，而索引b需要查询50001行，那我们来explain一下，看优化器到底选择了哪个索引；

mysql> explain select * from test3 where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                              |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+------------------------------------+
|  1 | SIMPLE      | test3 | NULL       | range | a,b           | b    | 5       | NULL | 50128 |     1.00 | Using index condition; Using where |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

太惊讶了，优化器居然选择了索引b，为什么呢？看到这个结果，我们还不确定索引到底选择正确没，下面我来看看这两种方案分别需要的查询时间：

#这种优化器选择了索引b，上面已经测试了，时间为0.06秒
mysql>  select * from test3 where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
Empty set (0.06 sec)
#下面这种我们强制使用索引a，force语句，执行时间为0秒
mysql>  select * from test3 force index(a) where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
Empty set (0.00 sec)

可以看到使用索引a的时间很明显比使用b的时间短，说明优化器在选择索引的时候又选择错了，这是为什么呢？

这里我们可以排除统计信息出错的问题，可以看到上面中rows为50128，与实际相差不大（当使用explain 查看强制使用a索引，rows为1000，完全正确），说明优化器知道选择索引b要查询这么多行还偏要选择它；

⭐原因：因为我们使用了order by语句对结果进行排序，而索引b本身就是有序的，如果选择它作为查询索引就不用排序了，只需要遍历，此时就不再考虑扫描行数的影响，即使扫描行数更多，也判定为代价更小；

3. 解决方案

1. force 语句

上面我们已经提出了第一种方案：使用force 语句强制选择我们想要的索引去执行SQL语句；

但他的缺点比较多：

写法上不优美；
如果索引更改了名字，那里的语句也得更改索引名字；

2. 修改语句

在上面的案例中，我们将原查询语句中的order by b limit 1改成 order by b,a limit 1；
这两句逻辑上是一样的，并不会影响结果，来看看结果：

mysql> explain select * from test3 where (a between 1 and 1000) and (b between 50000 and 100000) order by b,a limit 1;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | test3 | NULL       | range | a,b           | a    | 5       | NULL | 1000 |    50.00 | Using index condition; Using where; Using filesort |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)