20|单表查询:如何评估单表访问成本?
俊达
你好,我是俊达。
上一讲中我们介绍了优化器的工作原理,并介绍了全表扫描和索引范围扫描的成本评估方法。在这一讲中,我们继续来学习单表查询的其他几种访问路径:REF、覆盖索引、MRR、Index Merge。最后,我们还将通过一个真实的业务场景,来讨论怎么给业务创建一个合适的索引。
测试表
这一讲中,我们依然会使用 18 讲开头创建的那个测试表,这个表的表结构和统计信息情况如下:
mysql> show create table tab\G
*************************** 1. row ***************************
Table: tab
Create Table: CREATE TABLE `tab` (
`id` int NOT NULL,
`a` int NOT NULL,
`b` int NOT NULL,
`c` int NOT NULL,
`padding` varchar(7000) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_abc` (`a`,`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
mysql> select * from mysql.innodb_table_stats where table_name = 'tab'\G
*************************** 1. row ***************************
database_name: rep
table_name: tab
last_update: 2024-02-26 17:37:12
n_rows: 9913
clustered_index_size: 161
sum_of_other_index_sizes: 17
新功能上线,你的历史笔记已初始化为私密笔记,是否一键批量公开?
批量公开的笔记不会为你同步至部落
0/2000
1. Index Dive是InnoDB用来估算索引区间内记录数的机制,通过扫描索引页面并计算平均记录数来估算区间内的记录数,但在特殊情况下会进行估算修正。
2. 优化器对REF访问的成本设置了上限worst_seeks,取决于表扫描的IO成本的3倍和读取表中10%记录数的成本,同时也设置了下限min_worst_seek为读取2条主键记录的成本。
3. 通过表统计信息中的总行数和聚簇索引大小,可以计算出REF访问的总成本。
4. 8.0版本的优化器对于order by limit的情况,做了一些额外的考虑,当可以使用某个索引的有序性来避免排序,并且limit的数量比较少,少到扫描索引区间的成本比表扫描更低时,优化器会选择使用这个索引。
5. 如果查询使用了覆盖索引,那么执行过程中不需要回表,这种情况下,成本的计算方式有一些变化。
6. 大版本升级后,可能会由于优化器内部实现的变化,导致SQL的执行计划发生变化,因此在升级前需要使用真实业务场景做好测试。
7. 通过优化器的成本评估方法,可以更好地理解和评估单表查询中不同访问路径的成本,从而优化查询性能.
8. Index Union大致按如下步骤执行: 1. 按索引条件读取索引记录 2. 对多个索引的记录合并,去重。 3. 回表查询数据 如果第一步从索引中取到记录已经按ROWID顺序排列,则可以直接进行合并去重,这种情况下执行计划Extra显示“Using union”。
9. Index sort_union的执行方式和Index union类似,但是使用sort_union时,从各个索引获取到的ROWID需要先排序,增加了排序的成本。
10. 如何创建高效的索引?在面对一个具体的业务场景时,需要考虑如何创建合适、高效的索引,以及如何降低查询的代价。
仅可试看部分内容,如需阅读全部内容,请付费购买文章所属专栏
《MySQL 运维实战课》,新⼈⾸单¥59
立即购买
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。