AI 助理
备案控制台
开发者社区 数据库 文章 正文

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

2016-03-25 2137
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
云数据库 RDS MySQL，高可用系列 2核4GB
简介：

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来参与调整；
文章标签：
云数据库 RDS MySQL 版
索引
关系型数据库
MySQL
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL，然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
玄惭
目录
相关文章
snowofsummer
|
9月前
分区表统计信息收集
分区表统计信息收集
snowofsummer
61 1
阿里云RDS内核团队
|
4月前
|
SQL 算法 关系型数据库
浅析MySQL优化器统计信息
本文基于MySQL 8.0.34版本的源代码，详细介绍了MySQL中统计信息的计算和更新机制。文章首先概述了`records_per_key`统计信息在代价估计和Join Reorder算法中的重要性，接着了InnoDB统计信息的存储和计算方法，包括表级和索引级的统计信息。文章还介绍了统计信息的采样算法，特别是重要性采样在减少估计方差中的应用。此外，文章讨论了统计信息的更新时机，包括手动更新和自动更新。最后，文章简要介绍了直方图和其它统计信息，如表在内存中的占比估计，并通过实例展示了如何使用optimizer trace来分析查询优化过程。希望本文能帮助读者更好地理解MySQL的优化器。
阿里云RDS内核团队
526 6
1100237741946300
|
6月前
|
SQL 移动开发 Oracle
SQL查询连续六天数据记录的技巧与方法
在数据库查询中，实现针对连续几天（如连续六天）的数据筛选是一个常见且稍具挑战性的任务
1100237741946300
274 0
小数志
|
SQL 关系型数据库 MySQL
MySQL查询连续打卡信息？
最近多次看到用SQL查询连续打卡信息问题，自己也实践一波。抛开问题本身，也是对MySQL窗口函数和自定义变量用法的一种练习。
小数志
376 0
MySQL查询连续打卡信息？
余二五
|
SQL 索引
SQL调优之收集统计信息，重建索引 .
余二五
1500 0
德哥
|
SQL 算法 关系型数据库
PostgreSQL 空间类型统计信息(Statistics, Cardinality, Selectivity, Estimate)不准确导致SQL执行计划不准(包含、相交查询)的优化实践
德哥
2042 0
杰克.陈
|
SQL 算法 索引
SQL Server 执行计划利用统计信息对数据行的预估原理二（为什么复合索引列顺序会影响到执行计划对数据行的预估）
原文:SQL Server 执行计划利用统计信息对数据行的预估原理二（为什么复合索引列顺序会影响到执行计划对数据行的预估）   本文出处：http://www.cnblogs.com/wy123/p/6008477.html  　　关于统计信息对数据行数做预估，之前写过对非相关列（单独或者单独的索引列）进行预估时候的算法，参考这里。
杰克.陈
1066 0
范大脚脚
|
SQL Go 索引
使用过滤统计信息解决基数预估错误
范大脚脚
849 0
技术小美
|
SQL 大数据 程序员
求一个表中某个分类前几条的记录(这样做效率其实不高，只是做记录一下)
技术小美
1141 0
李晓晖
|
存储 索引 容器
基于R树索引的点面关系判断以及效率优化统计
文章版权由作者李晓晖和博客园共有，若转载请于明显处标明出处：http://www.cnblogs.com/naaoveGIS/ 1.背景 在之前的博客中，我分别介绍了基于网格的空间索引（http://www.cnblogs.com/naaoveGIS/p/5148185.html）以及四叉树和网格结合的联合索引（http://www.cnblogs.com/naaoveGIS/p/6641449.html），要解决的问题均是判断一个点落在了面图层中的哪个面要素中。
李晓晖
1672 0

热门文章

最新文章

  • 1
    Oracle使用expdp/impdp实现全库导入导出的整体流程
  • 2
    Python实现阿里云域名DDNS支持ipv4和ipv6
  • 3
    Tensorflow车牌识别完整项目（含完整源代码及训练集）
  • 4
    linux误删除了文件
  • 5
    系统架构技能之设计模式-组合模式
  • 6
    php判断上传文件是不是图片，如果是，返回图片格式
  • 7
    用户手册概要
  • 8
    Android -- 与WEB交互在同一个会话Session中通信
  • 9
    青岛城市圈：青岛 OSCER 技术交流会(1月12日)
  • 10
    springMVC对异常处理的支持
  • 1
    一维信号的小波变换与重构算法matlab仿真
    15
  • 2
    基于ADRC自抗扰算法的UAV飞行姿态控制系统simulink建模与仿真
    12
  • 3
    《深度剖析：边缘计算与AI融合下，生成对抗网络部署的变革与展望》
    11
  • 4
    《深度剖析：生成对抗网络如何实现图像风格的细腻逼真迁移》
    12
  • 5
    《深度剖析：生成对抗网络如何攻克文本生成的逻辑与语义难题》
    19
  • 6
    《深度剖析：生成对抗网络中生成器与判别器的高效协作之道》
    13
  • 7
    《深度揭秘：生成对抗网络如何重塑遥感图像分析精度》
    10
  • 8
    低代码：真能“0代码”开发应用吗？
    18
  • 9
    算法系列之回溯算法
    16
  • 10
    基于阿里云Serverless Kubernetes（ASK）的无服务器架构设计与实践
    19

    • 相关电子书

    更多
  • RowKey与索引设计：技巧与案例分析
  • 低代码开发师（初级）实战教程
  • 阿里巴巴DevOps 最佳实践手册
    • 下一篇
    通义万相：视觉生成大模型再进化