Home > database, sql优化 > 执行计划错误—索引统计信息的不准确

执行计划错误—索引统计信息的不准确


mysql在生成执行计划的时候,需要根据索引的统计信息进行一个估算,计算出成本最低的索引;
但是mysql索引统计信息的采集默认8个page:
root@test 08:48:52>show global variables like ‘%samp%’;
+—————————+——-+
| Variable_name             | Value |
+—————————+——-+
| innodb_stats_sample_pages | 8     |
+—————————+——-+
5.1估算rows estimate的算法存在bug http://bugs.mysql.com/bug.php?id=53761 ,
么你的执行计划很有可能由于索引统计信息的不准确,导致优化不能够正确的选择索引:
测试案例,表中的索引添加由于测试:
CREATE TABLE `recommend` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘自增主键’,
`status` tinyint(3) unsigned DEFAULT ‘0’ COMMENT ‘推荐菜状态’,
`user_id_kb` int(10) unsigned DEFAULT ‘0’ COMMENT ‘用户id’,
`user_id` bigint(20) unsigned DEFAULT ‘0’ COMMENT ‘用户id’,
`review_id` varchar(32) NOT NULL COMMENT ‘点评id’,
`target_id` varchar(32) NOT NULL COMMENT ‘店铺id’,
`recommend` varchar(32) NOT NULL COMMENT ‘内容’,
`gmt_create` datetime NOT NULL COMMENT ‘记录创建时间’,
`gmt_modified` datetime NOT NULL COMMENT ‘记录最后修改时间’,
PRIMARY KEY (`id`),
KEY `ind_review_staus` (`review_id`,`status`),
KEY `index_reivew` (`review_id`),
KEY `ind_user_id` (`user_id`,`status`,`gmt_create`,`target_id`),
KEY `ind_user_gmt` (`user_id`,`gmt_create`)
) ENGINE=InnoDB;

我们来看一个查询:

select status from recommend  where review_id=’00000015bf5445a88462ba047aab114c’ and status=1;

root@test 10:27:00>explain select status from recommend  where review_id=’00000015bf5445a88462ba047aab114c’ and status=1;
+—-+————-+—————-+——+——————————-+————
| id | select_type | table          | type | possible_keys                 | key          | key_len | ref   | rows | Extra       |
+—-+————-+—————-+——+——————————-+————
|  1 | SIMPLE      | recommend | ref  | index_review,ind_review_staus | index_review | 66      | const |    1 | Using where |
+—-+————-+—————-+——+——————————-+————
1 row in set (0.00 sec)
这里可以看到优化器选择了index_reivew这个索引,然后在回表过滤,而并没有选择reivew_id,status这个索引来覆盖查询,这里优化器就没有进行优化的选择了;
那我们看看是不是统计信息出来问题:
root@test 10:27:52>show  index from recommend;
+—————-+————+——————+————–+————-+———
| Table          | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+—————-+————+——————+————–+————-+———
| recommend_0202 |          0 | PRIMARY          |            1 | id          | A         |     2301924 |     NULL | NULL   |      | BTREE      |         |
| recommend_0202 |          1 | index_review     |            1 | review_id   | A         |     2301924 |     NULL | NULL   |      | BTREE      |         |
| recommend_0202 |          1 | ind_review_staus |            1 | review_id   | A         |         210 |     NULL | NULL   |      | BTREE      |         |
| recommend_0202 |          1 | ind_review_staus |            2 | status      | A         |       210 |     NULL | NULL   | YES  | BTREE      |         |
+—————-+————+——————+————–+————-+———

可以看到ind_review_staus和index_review第一个字段都同为review_id,但是他们的Cardinality相差还是很大的,这也是为什么优化器选择了index_review 这个索引;
那我们来手工分析一把:
root@test 10:31:08>analyze table recommend
+———————+———+———-+———-+
| Table               | Op      | Msg_type | Msg_text |
+———————+———+———-+———-+
| test.recommend | analyze | status   | OK       |
+———————+———+———-+———-+

root@test 10:31:27>show  index from recommend;
+—————-+————+——————+————–+————-+———
| Table          | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+—————-+————+——————+————–+————-+———
| recommend_0202 |          0 | PRIMARY          |            1 | id          | A         |     2219712 |     NULL | NULL   |      | BTREE      |         |
| recommend_0202 |          1 | ind_review_staus |            1 | review_id   | A     |   2219712 |     NULL | NULL   |      | BTREE      |         |
| recommend_0202 |          1 | ind_review_staus |            2 | status      | A        |     2219712 |     NULL | NULL   | YES  | BTREE      |         |
| recommend_0202 |          1 | index_reivew     |            1 | review_id   | A         |     2219712 |     NULL | NULL   |      | BTREE      |         |
+—————-+————+——————+————–+————-+———
可以看到analyze 后,索引ind_review_staus的统计信息已经恢复了正常;那么在来看看执行计划:
root@test 10:31:35>explain select status from recommend  where review_id=’00000015bf5445a88462ba047aab114c’ and status=1;
+—-+————-+—————-+——+——————————-+————
| id | select_type | table          | type | possible_keys                 | key              | key_len | ref         | rows | Extra                    |
+—-+————-+—————-+——+——————————-+————
|  1 | SIMPLE      | recommend | ref  | ind_review_staus,index_reivew | ind_review_staus | 68      | const,const |    1 | Using where; Using index |
+—-+————-+—————-+——+——————————-+————

可以看到优化器已经能够正确选择索引了;


有时候我们在判断优化器是否选择了正确的执行计划的时候,有一个状态变量值,他可以给我们一些信息,就是每个执行计划的cost,
mysql生成的每个执行计划都有一个cost,和其他很多关系数据库一样,同样以成本最低的执行计划来运行实际的查询:

第一个查询强制走:  KEY `ind_user_gmt` (`user_id`,`gmt_create`)
root@test 10:52:19>explain select target_id from recommend force index(ind_user_gmt) where user_id=44312518  order by gmt_Create limit 10;
+—-+————-+—————-+——+—————+————–+———+—
| id | select_type | table          | type | possible_keys | key          | key_len | ref   | rows | Extra       |
+—-+————-+—————-+——+—————+————–+———+—
|  1 | SIMPLE      | recommend | ref  | ind_user_gmt  | ind_user_gmt | 9       | const | 6130 | Using where |
+—-+————-+—————-+——+—————+————–+———+—
1 row in set (0.00 sec)

root@test 10:53:09>show status like ‘%last_query_cost%’;
+—————–+————-+
| Variable_name   | Value       |
+—————–+————-+
| Last_query_cost | 7355.999000 |
+—————–+————-+
1 row in set (0.00 sec)

第二个查询,优化器选择:  KEY `ind_user_id` (`user_id`,`status`,`gmt_create`,`target_id`),
root@test 10:53:20>explain select target_id from recommend where user_id=44312518  order by gmt_Create limit 10 ;
+—-+————-+—————-+——+————————–+————-+—
| id | select_type | table          | type | possible_keys            | key         | key_len | ref   | rows | Extra                                    |
+—-+————-+—————-+——+————————–+————-+—
|  1 | SIMPLE      | recommend | ref  | ind_user_id,ind_user_gmt | ind_user_id | 9       | const | 5896 | Using where; Using index; Using filesort |
+—-+————-+—————-+——+————————–+————-+—
1 row in set (0.00 sec)

root@test 10:53:49>show status like ‘%last_query_cost%’;
+—————–+————-+
| Variable_name   | Value       |
+—————–+————-+
| Last_query_cost | 1243.586097 |
+—————–+————-+
1 row in set (0.00 sec)
可以看到优化器优化器对比了KEY `ind_user_id`,KEY `ind_user_gmt`,选择cost最低KEY `ind_user_id`来执行;
但是有时候这个cost并不是十分有参考意义:
root@test 10:54:51>explain select target_id from recommend force index(ind_user_id) where user_id=0  order by gmt_Create limit 10;
+—-+————-+—————-+——+—————+————-+———+—-
| id | select_type | table          | type | possible_keys | key         | key_len | ref   | rows    | Extra                                    |
+—-+————-+—————-+——+—————+————-+———+—-
|  1 | SIMPLE      | recommend | ref  | ind_user_id   | ind_user_id | 9       | const | 1158435 | Using where; Using index; Using filesort |
+—-+————-+—————-+——+—————+————-+———+—-
1 row in set (0.00 sec)

root@test 10:55:42>show status like ‘%last_query_cost%’;
+—————–+—————+
| Variable_name   | Value         |
+—————–+—————+
| Last_query_cost | 244144.278570 |
+—————–+—————+
1 row in set (0.00 sec)



root@test 10:57:25>explain select target_id from recommend  where user_id=0  order by gmt_Create limit 10;
+—-+————-+—————-+——-+————————–+————–+-
| id | select_type | table          | type  | possible_keys            | key          | key_len | ref  | rows | Extra       |
+—-+————-+—————-+——-+————————–+————–+-
|  1 | SIMPLE      | recommend | index | ind_user_id,ind_user_gmt | ind_user_gmt | 17      | NULL |   20 | Using where |
+—-+————-+—————-+——-+————————–+————–+———+——+——+————-+
1 row in set (0.01 sec)

root@test 10:57:27>show status like ‘%last_query_cost%’;
+—————–+—————+
| Variable_name   | Value         |
+—————–+—————+
| Last_query_cost | 244144.278570 |
+—————–+—————+
1 row in set (0.00 sec)
两个执行计划的cost是一样的,这个时候还是需要有经验的DBA来参与调整;

 

 

Categories: database, sql优化 Tags:
  1. No comments yet.
  1. No trackbacks yet.
You must be logged in to post a comment.