mysql查询优化器选择索引的工作原理

最新推荐文章于 2024-04-22 08:54:44 发布

把学习作为兴趣

最新推荐文章于 2024-04-22 08:54:44 发布

阅读量476

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/AllTheWayQ2Q/article/details/126727859

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

一、mysql是如何选择索引的？

其实是通过查询优化器，对执行的sql进行分析，最后找到执行成本最低的策略执行

二、怎么判定是执行成本最低的策略？

这里所说的成本，其实就是io成本和cpu成本

io成本：主要是MySQL读取数据时，将数据从磁盘读取到内存中的消耗，读取数据单位是数据页，每页16KB，读取数据页的成本常数记作1（1页的成本为1）
cpu成本：查询数据是否满足条件或排序条件所消耗的cpu执行成本。默认情况下，检测记录成本常数记录为0.1(mysql版本是 8.0.27，在5.7时，他的常数是0.2)

三、验证计算方法是否mysql一致

查询MySQL本身为block表维护的统计数据

show table status like 'block'

得到如下结果：

其中关键指标就是图中圈中的数据：数据总行数rows=537868，数据总长度data_length=452771840字节，上面的数据总行数可能并不等于表中实际总行数，这是因为这个值是估算后的值，但并不影响计算成本。

所以全表扫描的成本为：537868*0.1+452771840/1024/16*1=53786.8+27635=81421.8

添加上索引，我们在测试一下mysql是否是按照上述的成本结果做选择：

现在已经在block表上添加了parent_id和created_by索引，先测试一下使用两个条件，MySQL的查询优化器会怎么选择

EXPLAIN 
SELECT * FROM block WHERE parent_id > '56ac64a1-0ddd-4bbc-9568-c61ce53f1029' AND created_by < 'd2aeb7db-2293-4cb4-91c9-5e6c6826e46b'

结果如下图：

它的type=all，表示是选择了全表扫描的方式，说明查询优化器估算全表扫描的方式成本比走其他索引要低，我们可以使用optimizer_trace功能查询优化器执行的过程，由于此功能默认是关闭的，我们得先打开它，执行完成后再关闭，若已开启，先关闭再开启

查询optimizer_trace功能是否开启：

select trace from `information_schema`.`optimizer_trace`;

查询上述语句的查询执行计划：

SET optimizer_trace='enabled=on'; 
SELECT * FROM block WHERE parent_id > '56ac64a1-0ddd-4bbc-9568-c61ce53f1029' AND created_by < 'd2aeb7db-2293-4cb4-91c9-5e6c6826e46b'; 
SELECT * FROM information_schema.optimizer_trace; 
SET optimizer_trace='enabled=off';

结果如下（只摘抄了trace关键部分数据）：

"analyzing_range_alternatives":{
            "range_scan_alternatives":[
                    {
                        "index":"idx_block_created_by",
                        "ranges":[
                            "created_by &lt; d2aeb7db-2293-4cb4-91c9-5e6c6826e46b"
                        ],
                        "index_dives_for_eq_ranges":true,
                        "rowid_ordered":false,
                        "using_mrr":true,
                        "index_only":false,
                        "in_memory":0.00259679,
                        "rows":158070,
                        "cost":124842,
                        "chosen":false,
                        "cause":"cost"
                    },
                    {
                        "index":"idx_block_parent_id",
                        "ranges":[
                            "56ac64a1-0ddd-4bbc-9568-c61ce53f1029 &lt; parent_id"
                        ],
                        "index_dives_for_eq_ranges":true,
                        "rowid_ordered":false,
                        "using_mrr":true,
                        "index_only":false,
                        "in_memory":0.00257792,
                        "rows":268934,
                        "cost":175419,
                        "chosen":false,
                        "cause":"cost"
                    }
                ],
                "analyzing_roworder_intersect":{
                            "usable":false,
                            "cause":"too_few_roworder_scans"
                        }
                    }
          }

由上面的执行结果可见，使用idx_block_created_by索引，查询器估算成本在124842，使用idx_block_parent_id，估算成本在175419

"range_analysis":{
        "table_scan":{
            "rows":537868,
            "cost":81385.9
        }
 }

全表扫描的成本为：81385.9，与我们上面计算的结果基本一致，所以最后选择了全表扫描

"best_access_path":{
    "considered_access_paths":[
        {
            "rows_to_scan":537868,
            "access_type":"scan",
            "resulting_rows":537868,
            "cost":81383.8,
            "chosen":true
        }
    ]
}

其实mysql查询优化器的选择，也不一定是完全正确，若我们想要执行器使用指定的索引，也可以使用force index(xxx)，指定索引执行

SELECT
	* 
FROM
	block FORCE INDEX(idx_block_parent_id)
WHERE
	parent_id > '56ac64a1-0ddd-4bbc-9568-c61ce53f1029' 
	AND created_by < 'd2aeb7db-2293-4cb4-91c9-5e6c6826e46b';

参考文章：你们一般都是怎么进行SQL调优的？MySQL在执行时是如何选择索引的？ - 纪莫 - 博客园