ITPub博客

首页 > 数据库 > MySQL > MySQL为什么有时候会选错索引?

MySQL为什么有时候会选错索引?

原创 MySQL 作者:StevenBeijing 时间:2019-12-12 16:49:33 0 删除 编辑

今天在生产环境中看到一个慢SQL,是个核心业务表,数据1300万+

看一下表索引:

mysql>show index from `order`
+-----------------+----------------------+--------------------+------------------------+-----------------------+---------------------+-----------------------+--------------------+------------------+----------------+----------------------+-------------------+-------------------------+
| Table           | Non_unique           | Key_name           | Seq_in_index           | Column_name           | Collation           | Cardinality           | Sub_part           | Packed           | Null           | Index_type           | Comment           | Index_comment           |
+-----------------+----------------------+--------------------+------------------------+-----------------------+---------------------+-----------------------+--------------------+------------------+----------------+----------------------+-------------------+-------------------------+
| order           | 0                    | PRIMARY            | 1                      | id                    | A                   | 10493505              |                    |                  |                | BTREE                |                   |                         |
| order           | 0                    | uidx_order         | 1                      | order_seq             | A                   | 10512924              |                    |                  |                | BTREE                |                   |                         |
| order           | 1                    | idx_user           | 1                      | user_id               | A                   | 1995181               |                    |                  | YES            | BTREE                |                   |                         |
| order           | 1                    | idx_shop           | 1                      | shop_id               | A                   | 53933                 |                    |                  | YES            | BTREE                |                   |                         |
| order           | 1                    | idx_out_channel    | 1                      | out_channel           | A                   | 524                   |                    |                  | YES            | BTREE                |                   |                         |
| order           | 1                    | idx_out_channel    | 2                      | out_order_no          | A                   | 10512924              |                    |                  | YES            | BTREE                |                   |                         |
| order           | 1                    | idx_order_time     | 1                      | order_time            | A                   | 9867734               |                    |                  |                | BTREE                |                   |                         |
| order           | 1                    | idx_update_time    | 1                      | update_time           | A                   | 8305698               |                    |                  |                | BTREE                |                   |                         |
| order           | 1                    | idx_create_time    | 1                      | create_time           | A                   | 9951390               |                    |                  |                | BTREE                |                   |                         |
+-----------------+----------------------+--------------------+------------------------+-----------------------+---------------------+-----------------------+--------------------+------------------+----------------+----------------------+-------------------+-------------------------+
返回行数:[9],耗时:4 ms.


mysql>SELECT id,order_seq,user_id 
FROM
	`ORDER`
WHERE
	delete_flag = 0 
	AND user_id = 'd4b0c318b28a46968718dddbaf4775c0' 
	AND display_status = 2 
ORDER BY
	order_time asc 
	LIMIT 0,20
+--------------+---------------------+----------------------------------+
| id           | order_seq           | user_id                          |
+--------------+---------------------+----------------------------------+
| 6068129      | 20161128183300861   | d4b0c318b28a46968718dddbaf4775c0 |
| 6118611      | 20161206171509550   | d4b0c318b28a46968718dddbaf4775c0 |
| 6885081      | 20170427104933189   | d4b0c318b28a46968718dddbaf4775c0 |
| 7720299      | 2017101718252243    | d4b0c318b28a46968718dddbaf4775c0 |
| 10319613     | 201905281103186182  | d4b0c318b28a46968718dddbaf4775c0 |
| 505498       | 2019082116584284235 | d4b0c318b28a46968718dddbaf4775c0 |
| 10840144     | 1119082315041792571 | d4b0c318b28a46968718dddbaf4775c0 |
+--------------+---------------------+----------------------------------+
返回行数:[7],耗时:18534 ms.

耗时18s,这个查询速度肯定是不能接受的。

我们看一下执行计划:

mysql>EXPLAIN SELECT id,order_seq,user_id 
FROM
	`ORDER`
WHERE
	delete_flag = 0 
	AND user_id = 'd4b0c318b28a46968718dddbaf4775c0' 
	AND display_status = 2 
ORDER BY
	order_time asc 
	LIMIT 0,20
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+----------------+-------------------+---------------+----------------+--------------------+-----------------+
| id           | select_type           | table           | partitions           | type           | possible_keys           | key            | key_len           | ref           | rows           | filtered           | Extra           |
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+----------------+-------------------+---------------+----------------+--------------------+-----------------+
| 1            | SIMPLE                | ORDER           |                      | index          | idx_user                | idx_order_time | 5                 |               | 2705           |               0.01 | Using where     |
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+----------------+-------------------+---------------+----------------+--------------------+-----------------+

 执行计划中看到,这个SQL走索引idx_order_time,根据经验判断,此索引效率很差。而扫描行数为2705,慢日志显示扫描行数为13,347,074,二者相差甚远,那么为什么会出现如此大的差异呢?

        选择索引是优化器的工作。而优化器选择索引的目的,是找一个最优的执行方案,并用最小的代价去执行语句。在数据库里面,扫描行数是影响执行代价的因素之一。扫描的行数越少,意味着访问磁盘数据的次数越少,消耗的CPU资源越少。

        当然,扫描行数并不是唯一的判断标准,优化器还会结合是否使用临时表,是否排序等因素进行综合判断。扫描行数是怎么判断的?

        MySQL在真正执行SQL之前,并不能准确的判断满足这个条件的数据有多少行,只能按统计信息来估算行数。

索引的统计信息就是索引的“区分度”,一个索引不同的值越多,这个索引的区分度就越好,而一个索引上不同的值的个数,我们称之为“基数”,基数越大,索引的区分度越好。

若强制使用idx_user索引,看下执行情况:

mysql>SELECT id,order_seq,user_id 
FROM
	`ORDER` force index(idx_user)
WHERE
	delete_flag = 0 
	AND user_id = 'd4b0c318b28a46968718dddbaf4775c0' 
	AND display_status = 2 
ORDER BY
	order_time asc 
	LIMIT 0,20
+--------------+---------------------+----------------------------------+
| id           | order_seq           | user_id                          |
+--------------+---------------------+----------------------------------+
| 6068129      | 20161128183300861   | d4b0c318b28a46968718dddbaf4775c0 |
| 6118611      | 20161206171509550   | d4b0c318b28a46968718dddbaf4775c0 |
| 6885081      | 20170427104933189   | d4b0c318b28a46968718dddbaf4775c0 |
| 7720299      | 2017101718252243    | d4b0c318b28a46968718dddbaf4775c0 |
| 10319613     | 201905281103186182  | d4b0c318b28a46968718dddbaf4775c0 |
| 505498       | 2019082116584284235 | d4b0c318b28a46968718dddbaf4775c0 |
| 10840144     | 1119082315041792571 | d4b0c318b28a46968718dddbaf4775c0 |
+--------------+---------------------+----------------------------------+

查询速度还是很快的,看一下执行计划:

mysql>explain SELECT id,order_seq,user_id 
FROM
	`ORDER` force index(idx_user)
WHERE
	delete_flag = 0 
	AND user_id = 'd4b0c318b28a46968718dddbaf4775c0' 
	AND display_status = 2 
ORDER BY
	order_time asc 
	LIMIT 0,20
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+----------------------------------------------------+
| id           | select_type           | table           | partitions           | type           | possible_keys           | key           | key_len           | ref           | rows           | filtered           | Extra                                              |
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+----------------------------------------------------+
| 1            | SIMPLE                | ORDER           |                      | ref            | idx_user                | idx_user      | 163               | const         | 77706          |                  1 | Using index condition; Using where; Using filesort |
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+----------------------------------------------------+
返回行数:[1],耗时:4 ms.

如果换成数据行数少一些的user_id

mysql>EXPLAIN SELECT id,order_seq,user_id 
FROM
	`ORDER` 
WHERE
	delete_flag = 0 
	AND user_id = '1e41c833fc6f4f57b490a4627a4170dc' 
	AND display_status = 2 
ORDER BY
	order_time asc 
	LIMIT 0,20
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+----------------------------------------------------+
| id           | select_type           | table           | partitions           | type           | possible_keys           | key           | key_len           | ref           | rows           | filtered           | Extra                                              |
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+----------------------------------------------------+
| 1            | SIMPLE                | ORDER           |                      | ref            | idx_user                | idx_user      | 163               | const         | 13             |                  1 | Using index condition; Using where; Using filesort |
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+----------------------------------------------------+
返回行数:[1],耗时:4 ms.
mysql>SELECT id,order_seq,user_id 
FROM
	`ORDER` 
WHERE
	delete_flag = 0 
	AND user_id = '1e41c833fc6f4f57b490a4627a4170dc' 
	AND display_status = 2 
ORDER BY
	order_time asc 
	LIMIT 0,20
+--------------+---------------------+----------------------------------+
| id           | order_seq           | user_id                          |
+--------------+---------------------+----------------------------------+
| 10397123     | 2019092523044218361 | 1e41c833fc6f4f57b490a4627a4170dc |
+--------------+---------------------+----------------------------------+
返回行数:[1],耗时:4 ms.

对比一下两个user_id对应的数据量:

返回行数:[1],耗时:4 ms.
mysql>select count(*) from order01 where user_id='1e41c833fc6f4f57b490a4627a4170dc'
+--------------------+
| count(*)           |
+--------------------+
| 15                 |
+--------------------+
返回行数:[1],耗时:4 ms.
mysql>select count(*) from order01 where user_id='d4b0c318b28a46968718dddbaf4775c0'
+--------------------+
| count(*)           |
+--------------------+
| 38611              |
+--------------------+
返回行数:[1],耗时:14 ms.

总结:在此业务场景中,MySQL优化器认为检索38000行数据然后进行排序要比检索15行数据排序代价大得多,所以选择了有序的索引idx_order_time,但未必是最快的执行计划。

但是,此处还有一个疑问,如果对于user_id:d4b0c318b28a46968718dddbaf4775c0,不使用limit分页,执行计划是什么样呢?

mysql>SELECT * 
FROM
	ORDER01 
WHERE
	delete_flag = 0 
	AND user_id = 'd4b0c318b28a46968718dddbaf4775c0'
	AND display_status = 2 
ORDER BY
	order_time desc
+--------------+---------------------+----------------------+----------------------+----------------------------------+-----------------------+---------------------+----------------------------------+---------------------+----------------------+---------------------+-------------------------------+-------------------------+---------------------------+-----------------------------------+-----------------------+-------------------------+-------------------+-----------------------+------------------------+--------------------------+----------------------+---------------------+--------------------------+------------------------+------------------------+---------------------+------------------+-----------------------+-----------------------+-----------------------+
| id           | order_seq           | order_type           | order_flag           | user_id                          | user_mobile           | user_nick           | shop_id                          | shop_name           | pay_status           | pay_time            | receiver_address_id           | receiver_name           | receiver_mobile           | receiver_address                  | cancel_time           | cancel_reason           | channel           | out_channel           | out_order_no           | out_store_name           | order_time           | over_time           | display_status           | order_status           | sale_channel           | sale_mode           | remark           | delete_flag           | create_time           | update_time           |
+--------------+---------------------+----------------------+----------------------+----------------------------------+-----------------------+---------------------+----------------------------------+---------------------+----------------------+---------------------+-------------------------------+-------------------------+---------------------------+-----------------------------------+-----------------------+-------------------------+-------------------+-----------------------+------------------------+--------------------------+----------------------+---------------------+--------------------------+------------------------+------------------------+---------------------+------------------+-----------------------+-----------------------+-----------------------+
| 11153421     | 201911091339555506  |                    1 |                    1 | d4b0c318b28a46968718dddbaf4775c0 | 13718903545           | 总部-客服-补单      | 29e541d6da9b4aae8957409ca03c6670 | 清悠                |                    1 | 2019-11-09 13:40:10 | 2666265                       | 总部-客服-补单          | 13718903545               | 东城区 王府井 王府井 总部补单     |                       |                       0 |                   |                     0 | 201911091339555506     |                          | 2019-11-09 13:39:55  |                     |                        2 |                    200 |                      1 |                   1 |                  |                     0 | 2019-11-09 13:39:55   | 2019-11-09 13:40:10   |
| 7720299      | 2017101718252243    |                    1 |                    1 | d4b0c318b28a46968718dddbaf4775c0 | 13718903545           | 总部-客服-补单      | ad41dba7bf5c4b69b03e0222878cb2b0 | 蝶舞                |                    1 | 2017-10-17 18:25:26 | 2282099                       | 总部-客服-补单          | 13718903545               | 2号线; 地铁7号线 华强北 总部补单  |                       |                       0 |                   |                     0 | 2017101718252243       |                          | 2017-10-17 18:25:22  |                     |                        2 |                    200 |                      1 |                   1 |                  |                     0 | 2017-10-17 18:25:22   | 2017-10-17 18:25:22   |
| 6885081      | 20170427104933189   |                    1 |                    1 | d4b0c318b28a46968718dddbaf4775c0 | 13718903545           | 总部-客服-补单      | c6092260f92643098f7f56e68560d8c0 | 木兰花              |                    1 | 2017-04-27 10:49:39 | 2264946                       | 总部-客服-补单          | 13718903545               | 天河北商圈                        |                       |                       0 |                   |                     0 | 20170427104933189      |                          | 2017-04-27 10:49:33  |                     |                        2 |                    200 |                      1 |                   1 |                  |                     0 | 2017-04-27 10:49:33   | 2017-04-27 10:49:33   |
| 6118611      | 20161206171509550   |                    1 |                    1 | d4b0c318b28a46968718dddbaf4775c0 | 13718903545           | 总部-客服-补单      | 7a0cd4d60f52423fb757b0be1ab55be6 | 娟子                |                    1 | 2016-12-06 17:15:12 | 1904075                       | 总部-客服-补单          | 13718903545               | 广东省深圳市南山区深南大道 科技园 |                       |                       0 | helijia           |                     0 | 20161206171509550      |                          | 2016-12-06 17:15:09  |                     |                        2 |                    200 |                      1 |                   1 |                  |                     0 | 2016-12-06 17:15:09   | 2016-12-06 17:15:09   |
| 6068129      | 20161128183300861   |                    1 |                    1 | d4b0c318b28a46968718dddbaf4775c0 | 13718903545           | 总部-客服-补单      | f6f4612493654695ac4c6bac6df67672 | 美天                |                    1 | 2016-11-28 18:33:03 | 1544109                       | 总部-客服-补单          | 13718903545               | 青羊区金河路口宽窄巷子 宽窄巷子   |                       |                       0 | helijia           |                     0 | 20161128183300861      |                          | 2016-11-28 18:33:00  |                     |                        2 |                    200 |                      1 |                   1 |                  |                     0 | 2016-11-28 18:33:00   | 2016-11-28 18:33:00   |
+--------------+---------------------+----------------------+----------------------+----------------------------------+-----------------------+---------------------+----------------------------------+---------------------+----------------------+---------------------+-------------------------------+-------------------------+---------------------------+-----------------------------------+-----------------------+-------------------------+-------------------+-----------------------+------------------------+--------------------------+----------------------+---------------------+--------------------------+------------------------+------------------------+---------------------+------------------+-----------------------+-----------------------+-----------------------+
返回行数:[5],耗时:152 ms.
mysql>explain SELECT * 
FROM
	ORDER01 
WHERE
	delete_flag = 0 
	AND user_id = 'd4b0c318b28a46968718dddbaf4775c0'
	AND display_status = 2 
ORDER BY
	order_time desc
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+----------------------------------------------------+
| id           | select_type           | table           | partitions           | type           | possible_keys           | key           | key_len           | ref           | rows           | filtered           | Extra                                              |
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+----------------------------------------------------+
| 1            | SIMPLE                | ORDER01         |                      | ref            | idx_user                | idx_user      | 163               | const         | 75800          |                  1 | Using index condition; Using where; Using filesort |
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+----------------------------------------------------+
返回行数:[1],耗时:4 ms.

查询速度很快,执行计划走了user_id字段的索引。为什么会出现这样的情况呢?

查阅了相关资料,对于order by limit这样的排序,当检索到的数据较多的时候,排序消耗是很大的,这个时候由于优化器选择了有序的idx_order_time而导致执行索引选择错误。

优化办法:

1、强制使用索引idx_user;

2、创建组合索引idx_uid_ordertime(user_id,order_time)

mysql>alter table `ORDER` add index idx_uid_ordertime(user_id,order_time)
执行成功,耗时:60334 ms.
mysql>SELECT id,order_seq,user_id 
FROM
	`ORDER` 
WHERE
	delete_flag = 0 
	AND user_id = 'd4b0c318b28a46968718dddbaf4775c0' 
	AND display_status = 2 
ORDER BY
	order_time asc 
	LIMIT 0,20
+--------------+---------------------+----------------------------------+
| id           | order_seq           | user_id                          |
+--------------+---------------------+----------------------------------+
| 6068129      | 20161128183300861   | d4b0c318b28a46968718dddbaf4775c0 |
| 6118611      | 20161206171509550   | d4b0c318b28a46968718dddbaf4775c0 |
| 6885081      | 20170427104933189   | d4b0c318b28a46968718dddbaf4775c0 |
| 7720299      | 2017101718252243    | d4b0c318b28a46968718dddbaf4775c0 |
| 10319613     | 201905281103186182  | d4b0c318b28a46968718dddbaf4775c0 |
| 505498       | 2019082116584284235 | d4b0c318b28a46968718dddbaf4775c0 |
| 10840144     | 1119082315041792571 | d4b0c318b28a46968718dddbaf4775c0 |
+--------------+---------------------+----------------------------------+
返回行数:[7],耗时:86 ms.
mysql>explain SELECT id,order_seq,user_id 
FROM
	`ORDER` 
WHERE
	delete_flag = 0 
	AND user_id = 'd4b0c318b28a46968718dddbaf4775c0' 
	AND display_status = 2 
ORDER BY
	order_time asc 
	LIMIT 0,20
+--------------+-----------------------+-----------------+----------------------+----------------+----------------------------+-------------------+-------------------+---------------+----------------+--------------------+------------------------------------+
| id           | select_type           | table           | partitions           | type           | possible_keys              | key               | key_len           | ref           | rows           | filtered           | Extra                              |
+--------------+-----------------------+-----------------+----------------------+----------------+----------------------------+-------------------+-------------------+---------------+----------------+--------------------+------------------------------------+
| 1            | SIMPLE                | ORDER           |                      | ref            | idx_user,idx_uid_ordertime | idx_uid_ordertime | 163               | const         | 72772          |                  1 | Using index condition; Using where |
+--------------+-----------------------+-----------------+----------------------+----------------+----------------------------+-------------------+-------------------+---------------+----------------+--------------------+------------------------------------+
返回行数:[1],耗时:4 ms.







来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30135314/viewspace-2668314/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2015-02-10

  • 博文量
    213
  • 访问量
    207093