MySQL明明有索引,为什么不用?

2022-04-02 00:00:00 索引 成本 的是 扫描 代价

微信公众号:DBA随笔

一个经典的MySQL索引问题

    今天分享一个线上的经典MySQL索引问题。

1
背景介绍

     今天在线上运维过程中,遇到了一个MySQL的经典索引问题。线上的表结构不方便展示,我模拟了一个表结构用于说明问题:

CREATE TABLE `test_index` (
  `id` int(11NOT NULL AUTO_INCREMENT,
  `name` varchar(10DEFAULT NULL,
  `age` int(11DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`)
ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4


    上面的表结构中,有两个索引:一个是id,也是自增主键,另外一个是idx_age,它是一个普通二级索引。

     表里面插入了从1~10w的数据,形如下面这样:

+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | 1    |    1 |
|  2 | 2    |    2 |
|  3 | 3    |    3 |
|  4 | 4    |    4 |
|  5 | 5    |    5 |
|  6 | 6    |    6 |
|  7 | 7    |    7 |
|  8 | 8    |    8 |
|  9 | 9    |    9 |
| 10 | 10   |   10 |
+----+------+------+
10 rows in set (.01 sec)


    下面我们看两个查询的SQL:

  

SQL 1:

select * from test_index where age>0 and age<20000 order by age ;


SQL 2:

select * from test_index where age>0 and age<20000 order by age limit ?;


其中,?代表一个具体的数字。

那么对于这两个SQL,MySQL会采用何种执行计划呢?


2
执行计划分析



   执行计划分析如下:

MySQL  >explain select * from test_index where  age> and age<20000 order by age ;
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                       |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
|
  1 | SIMPLE      | test_index | NULL       | ALL  | idx_age       | NULL | NULL    | NULL | 100139 |    37.47 | Using where; Using filesort |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
1 row in set, 1 warning (.00 sec)

MySQL >explain select * from test_index where  age> and age<20000 order by age limit 100,200;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra                 |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------+
|
  1 | SIMPLE      | test_index | NULL       | range | idx_age       | idx_age | 5       | NULL | 37524 |   100.00 | Using index condition |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------+
1 row in set, 1 warning (.00 sec)

可以看到:

个SQL,关键字标红的是:all,using filesort

第二个SQL,关键字表用的是:range,idx_age


我们表的age字段上有普通索引,本身就是根据age字段进行排序的。但是我们的SQL语句中,除了要访问age,还需要访问id列和name列,所以,在age这个索引上,不能获取所有的数据。

那么,正常情况下,这个SQL的可能的执行方法有以下两种:

方法1、扫描age字段,先找到符合条件的age值,再根据age字段的索引,获得对应的id列值,然后"回表",去聚集索引(也就是主键id)上去查找对应的name列的值

方法2、直接在聚集索引id上面查找满足age字段的值,然后再利用文件排序


从实际的情况来看,

SQL 1:

select * from test_index where age>0 and age<20000 order by age ;

采用的是上述方法2

  

而SQL2:

select * from test_index where age>0 and age<20000 order by age limit 100,200;

采用的是上述方法1


age列上已经创建了索引,查询也是按照age来的,而且还有排序操作。为什么第2个SQL的限制条件更多,但是却能用到索引,个SQL的限制条件更少,却用不到索引?


真实情况是什么样子的?


我们再来看下面的对比:

MySQL >explain select * from test_index where  age> and age<20000 order by age limit 100,200;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra                 |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------+
|
  1 | SIMPLE      | test_index | NULL       | range | idx_age       | idx_age | 5       | NULL | 37524 |   100.00 | Using index condition |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+-------+----------+-----------------------+
1 row in set, 1 warning (.00 sec)

MySQL >explain select * from test_index where  age> and age<20000 order by age limit 100,20000;
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                       |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
|
  1 | SIMPLE      | test_index | NULL       | ALL  | idx_age       | NULL | NULL    | NULL | 100139 |    37.47 | Using where; Using filesort |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
1 row in set, 1 warning (.00 sec)

上面的这两个SQL语句:

SQL 3:

explain select * from test_index where  age>0 and age<20000 order by age limit 100,200;

SQL 4:

explain select * from test_index where  age>0 and age<20000 order by age limit 100,20000;

仅仅是limit 后面的值不一样,但是执行计划却完全不相同。

扫描行数100的,采用的方法1,先索引查询,再回表;

扫描行数19900的,采用的方法2,直接查聚集索引。


3
为什么会有这种差异呢?


      MySQL优化器的逻辑是关键。 

      在MySQL存储引擎中,磁盘和内存通过数据页来交互,


     

      MySQL中,采用的是基于成本的优化。通常我们说的成本,指代的是CPU成本和IO成本

    在MySQL优化器代码中有这样的设定:

1、读取一个数据页的成本是1(也就是IO成本);

2、从这个数据页中找到一个数据记录的成本可以大概表示成0.2(也就是CPU成本);

3、读取一个范围的数据记录相当于读取一个数据页的成本,也是1。

4、每条记录每次回表操作都相当于访问一个页面


     当然,实际中,比这个计算方法要复杂。


    MySQL中,决定一个查询究竟用哪个索引的过程,可以简单模拟成下面这样:

1、根据搜索条件,找到所有可能的索引,并逐一计算走每条索引的代价

2、计算全表扫描的代价

3、对比各种执行方案,选出成本低的一个


全表扫描的代价=

IO代价

+CPU代价

=

所有的页面*1+

所有的记录数*0.2


索引扫描的代价=

二级索引IO代价

+二级索引CPU代价

+回表访问IO代价

+回表访问CPU代价

=

1个数据页*1 

+ 所有满足条件的记录数*0.2

+ 所有满足条件的记录数*1 (每次回表都相当于访问一个页面)

+ 所有满足条件的记录数*0.2


上述案例中,我们累计有10w记录,总的页面数大约是225个,计算方法如下(先查看表的基本状况,然后利用data_length计算页面数):

MySQL >show table status like 'test_index'\G
*************************** 1. row ***************************
           Name: test_index
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 100139
 Avg_row_length: 36
    Data_length: 3686400
Max_data_length: 
   Index_length: 1589248
      Data_free: 4194304
 Auto_increment: 100001
    Create_time: 2022-01-10 21:50:53
    Update_time: 2022-01-10 21:57:01
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (.00 sec)

MySQL 5724 myadmin_common@127...1 :test 11:06:07 >select 3686400/16/1024;
+-----------------+
| 3686400/16/1024 |
+-----------------+
|    225.00000000 |
+-----------------+
1 row in set (.00 sec)

而扫描的age范围是[0,20000], 也就是大概1/5的所有记录。


我们简单计算下这个数值:

如果扫描行数比较多,例如有2w个,则:

全表扫描代价=225*1+100000*0.2=20225

索引扫描代价=1+4000+20000*1 + 4000=28001

这里全表扫描,代价更低


如果扫描行数很小,例如只有100个,则:

全表扫描代价=225*1+100000*0.2=20225

索引扫描代价=1+100*0.2+100*1 + 100*0.2=141

这里索引扫描,代价更低


看到这里,想必上述的问题能够回答了,对于:

SQL 1:

select * from test_index where age>0 and age<20000 order by age ;

相当于使用了limit 100000,采用的是全表扫描

  

而SQL2:

select * from test_index where age>0 and age<20000 order by age limit 100,200;

只扫描了100行,因此采用索引扫描的方法



总结:

    当然,真正的查询代价计算方法,比上述描述更为复杂,这里是想通过这样一个特殊案例和计算方法,告诉大家:

1、不同的扫描行数,会导致不同的扫描代价。

2、不能认为限制条件少的语句更应该使用索引。

3、不能认为索引创建了就一定会用到而要通过。

4、explain是个好工具,可以对SQL语句真正的执行计划加以分析,从而给出正确的优化方案。









相关文章