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

  • 时间:
  • 浏览:3

1 row in set (0.00 sec)

| Variable_name   | Value         |

| Variable_name   | Value       |

1 row in set (0.00 sec)

KEY `ind_user_gmt` (`user_id`,`gmt_create`)

但会 有之后这俩cost三种是十分有参考意义:

+—-+————-+—————-+——-+————————–+————–+———+——+——+————-+

|  1 | SIMPLE      | recommend | ref  | ind_user_id   | ind_user_id | 9       | const | 1158435 | Using where; Using index; Using filesort |

+—————–+————-+

| Last_query_cost | 7355.999000 |

第另一六个查询强制走:  KEY `ind_user_gmt` (`user_id`,`gmt_create`)

+—-+————-+—————-+——+————————–+————-+—

| id | select_type | table          | type | possible_keys | key         | key_len | ref   | rows    | Extra                                    |

+—————————+——-+

| Last_query_cost | 244144.278570 |

|  1 | SIMPLE      | recommend | index | ind_user_id,ind_user_gmt | ind_user_gmt | 17      | NULL |   20 | Using where |

+—-+————-+—————-+——+——————————-+————

`gmt_create` datetime NOT NULL COMMENT ‘记录创建时间’,

| Last_query_cost | 244144.278570 |

+———————+———+———-+———-+

5.1估算rows estimate的算法居于bug http://bugs.mysql.com/bug.php?id=53761 ,

另另一六个们看看是都不 统计信息出来问題:

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;

亲们来看另一六个查询:

1 row in set (0.00 sec)

| recommend_0202 |          0 | PRIMARY          |            1 | id          | A         |     2219712 |     NULL | NULL   |      | BTREE      |         |

+—————–+————-+

root@test 10:31:27>show  index from recommend;

第六个查询,优化器取舍:  KEY `ind_user_id` (`user_id`,`status`,`gmt_create`,`target_id`),

| 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         |     260 1924 |     NULL | NULL   |      | BTREE      |         |

+—————–+—————+

+—-+————-+—————-+——+—————+————–+———+—

root@test 10:57:27>show status like ‘%last_query_cost%’;

root@test 10:27:52>show  index from recommend;

root@test 10:55:42>show status like ‘%last_query_cost%’;

+—-+————-+—————-+——-+————————–+————–+-

测试案例,表中的索引去掉 机会测试:

| id | select_type | table          | type | possible_keys                 | key              | key_len | ref         | rows | Extra                    |

+———————+———+———-+———-+

有之后亲们在判断优化器是算是取舍了正确的执行计划的之后,有另一六个具体情况变量值,他还可不可不能否 给亲们这俩信息,就是我每个执行计划的cost,

`gmt_modified` datetime NOT NULL COMMENT ‘记录最后修改时间’,

| Last_query_cost | 1243.5860 97 |

+—-+————-+—————-+——+—————+————–+———+—

还可不可不能否 看完ind_review_staus和index_review第另一六个字段都同为review_id,但会 亲们的Cardinality相差还是很大的,这也是怎么会会 会 优化器取舍了index_review 这俩索引;

1 row in set (0.00 sec)

+—————–+————-+

还可不可不能否 看完analyze 后,索引ind_review_staus的统计信息机会恢复了正常;越来越在来看看执行计划:

+—————–+—————+

| test.recommend | analyze | status   | OK       |

+—————-+————+——————+————–+————-+———

`user_id` bigint(20) unsigned DEFAULT ‘0’ COMMENT ‘用户id’,

| Table          | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+—-+————-+—————-+——+——————————-+————

KEY `index_reivew` (`review_id`),

KEY `ind_user_id` (`user_id`,`status`,`gmt_create`,`target_id`),



mysql在生成执行计划的之后,需要根据索引的统计信息进行另一六个估算,计算出成本最低的索引;

另一六个执行计划的cost是一样的,这俩之后还是需要有经验的DBA来参与调整;

1 row in set (0.00 sec)

+—-+————-+—————-+——+——————————-+————

+—-+————-+—————-+——+——————————-+————

| id | select_type | table          | type | possible_keys                 | key          | key_len | ref   | rows | Extra       |

+—————————+——-+

mysql生成的每个执行计划都不 另一六个cost,和这俩这俩这俩关系数据库一样,同样以成本最低的执行计划来运行实际的查询:

| recommend_0202 |          1 | ind_review_staus |            2 | status      | A         |       210 |     NULL | NULL   | YES  | BTREE      |         |

| Variable_name             | Value |

| recommend_0202 |          1 | index_review     |            1 | review_id   | A         |     260 1924 |     NULL | NULL   |      | BTREE      |         |

但会 mysql索引统计信息的挂接默认8个page:

| recommend_0202 |          1 | index_reivew     |            1 | review_id   | A         |     2219712 |     NULL | NULL   |      | BTREE      |         |

+—-+————-+—————-+——+—————+————-+———+—-

| innodb_stats_sample_pages | 8     |

PRIMARY KEY (`id`),

1 row in set (0.00 sec)

还可不可不能否 看完优化器机会不能正确取舍索引了;

root@test 10:53:20>explain select target_id from recommend where user_id=44312518  order by gmt_Create limit 10 ;

root@test 10:27:00>explain select status from recommend  where review_id=’00000015bf5445a88462ba047aab114c’ and status=1;

+—————–+————-+

+———————+———+———-+———-+

`status` tinyint(3) unsigned DEFAULT ‘0’ COMMENT ‘推荐菜具体情况’,

+—————-+————+——————+————–+————-+———

| recommend_0202 |          1 | ind_review_staus |            2 | status      | A        |     2219712 |     NULL | NULL   | YES  | BTREE      |         |

| id | select_type | table          | type | possible_keys | key          | key_len | ref   | rows | Extra       |

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;

) ENGINE=InnoDB;

root@test 10:53:49>show status like ‘%last_query_cost%’;

`user_id_kb` int(10) unsigned DEFAULT ‘0’ COMMENT ‘用户id’,

+—-+————-+—————-+——+——————————-+————

+—————–+————-+

`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘自增主键’,

| Table               | Op      | Msg_type | Msg_text |

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

+—————–+—————+

root@test 08:48:52>show global variables like ‘%samp%’;

+—————-+————+——————+————–+————-+———

+—————-+————+——————+————–+————-+———

`recommend` varchar(32) NOT NULL COMMENT ‘内容’,

+—————-+————+——————+————–+————-+———

+—-+————-+—————-+——+————————–+————-+—

| recommend_0202 |          1 | ind_review_staus |            1 | review_id   | A     |   2219712 |     NULL | NULL   |      | BTREE      |         |

|  1 | SIMPLE      | recommend | ref  | ind_user_gmt  | ind_user_gmt | 9       | const | 6160 | Using where |

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

+—————–+—————+

`review_id` varchar(32) NOT NULL COMMENT ‘点评id’,

+—-+————-+—————-+——+——————————-+————

1 row in set (0.01 sec)

么你的执行计划很有机会机会索引统计信息的不准确,原因优化还可不可不能否 够正确的取舍索引:

还可不可不能否 看完优化器优化器对比了KEY `ind_user_id`,KEY `ind_user_gmt`,取舍cost最低KEY `ind_user_id`来执行;

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

+—-+————-+—————-+——-+————————–+————–+-

root@test 10:31:08>analyze table recommend

| recommend_0202 |          1 | ind_review_staus |            1 | review_id   | A         |         210 |     NULL | NULL   |      | BTREE      |         |

root@test 10:53:09>show status like ‘%last_query_cost%’;

+—-+————-+—————-+——+—————+————-+———+—-

|  1 | SIMPLE      | recommend | ref  | ind_review_staus,index_reivew | ind_review_staus | 68      | const,const |    1 | Using where; Using index |

+—————————+——-+

| Variable_name   | Value       |

+—————–+—————+

`target_id` varchar(32) NOT NULL COMMENT ‘店铺id’,

| Variable_name   | Value         |

root@test 10:57:25>explain select target_id from recommend  where user_id=0  order by gmt_Create limit 10;

root@test 10:31:35>explain select status from recommend  where review_id=’00000015bf5445a88462ba047aab114c’ and status=1;

KEY `ind_review_staus` (`review_id`,`status`),

+—-+————-+—————-+——+—————+————-+———+—-

CREATE TABLE `recommend` (

+—-+————-+—————-+——+————————–+————-+—

+—-+————-+—————-+——+—————+————–+———+—

1 row in set (0.00 sec)

+—————–+————-+

这里还可不可不能否 看完优化器取舍了index_reivew这俩索引,但会 在回表过滤,而并越来越取舍reivew_id,status这俩索引来覆盖查询,这里优化器就越来越进行优化的取舍了;

1 row in set (0.00 sec)