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

  • 时间:
  • 浏览:5

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

mysql生成的每个执行计划都不 另有一个多多cost,和或多或少可是 关系数据库一样,同样以成本最低的执行计划来运行实际的查询:

1 row in set (0.00 sec)

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

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

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

然后 有后后你或多或少cost并都不 十分有参考意义:

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



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

PRIMARY KEY (`id`),

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

| Variable_name   | Value         |

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

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

1 row in set (0.00 sec)

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

| innodb_stats_sample_pages | 8     |

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

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

可是 们看看是都不 统计信息出来大问题:

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

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;

有另有一个多执行计划的cost是一样的,你或多或少后后还是需用有经验的DBA来参与调整;

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

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

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

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

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

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

1 row in set (0.00 sec)

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

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

CREATE TABLE `recommend` (

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

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

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

上能 都看ind_review_staus和index_review第有另有一个多字段都同为review_id,然后 朋友 的Cardinality相差还是很大的,这也是为哪些地方优化器选折 了index_review 你或多或少索引;

| Variable_name   | Value       |

| Variable_name   | Value         |

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

| test.recommend | analyze | status   | OK       |

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

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

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

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

么你的执行计划很有肯能肯能索引统计信息的不准确,是因为优化能不有助正确的选折 索引:

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

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

可是 们来手工分析一把:

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

| Variable_name             | Value |

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

1 row in set (0.00 sec)

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

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

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

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

1 row in set (0.01 sec)

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

|  1 | SIMPLE      | recommend | ref  | ind_user_id,ind_user_gmt | ind_user_id | 9       | const | 5896 | Using where; Using index; Using filesort |

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

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

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

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

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

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

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

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

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;

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

上能 都看analyze 后,索引ind_review_staus的统计信息肯能恢复了正常;这麼在来看看执行计划:

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 ;

朋友 来看有另有一个多查询:

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

| Variable_name   | Value       |

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

| Last_query_cost | 244144.278570 |

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

| Table               | Op      | Msg_type | Msg_text |

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

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

测试案例,表中的索引加进去去肯能测试:

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

| Last_query_cost | 7355.999000 |

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

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

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

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

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

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

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

第有一个查询,优化器选折 :  KEY `ind_user_id` (`user_id`,`status`,`gmt_create`,`target_id`),

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

然后 mysql索引统计信息的架构设计 默认8个page:

上能 都看优化器肯能不有助正确选折 索引了;

1 row in set (0.00 sec)

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

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

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

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

这上能 能都看优化器选折 了index_reivew你或多或少索引,然后 在回表过滤,而并这麼选折 reivew_id,status你或多或少索引来覆盖查询,这里优化器就这麼进行优化的选折 了;

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

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

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

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

| Last_query_cost | 1243.58100097 |

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

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

|  1 | SIMPLE      | recommend | ref  | index_review,ind_review_staus | index_review | 66      | const |    1 | Using where |

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

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

1 row in set (0.00 sec)

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

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

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

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

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

| Last_query_cost | 244144.278570 |

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

KEY `index_reivew` (`review_id`),

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

有后后朋友 在判断优化器否是选折 了正确的执行计划的后后,有另有一个多多情况汇报变量值,他上能 给朋友 或多或少信息,可是 每个执行计划的cost,

) ENGINE=InnoDB;

| recommend_0202 |          1 | index_review     |            1 | review_id   | A         |     210001924 |     NULL | NULL   |      | BTREE      |         |

1 row in set (0.00 sec)

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

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

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

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

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

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

上能 都看优化器优化器对比了KEY `ind_user_id`,KEY `ind_user_gmt`,选折 cost最低KEY `ind_user_id`来执行;

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