mysql查询成本比较

源代码 · 2024-9-13 10:34:08

1.工具

1.mysql：8.0.25
2.msyqlworkbench
2.成本定义

执行sql查询所需要花费的代价
3.查看成本的方式

执行一条示例语句，如下：
select sql_no_cache suser.id,suser.name ,srole.name from sys_user suser inner join sys_user_role surole on suser.id=surole.user_id inner join sys_role srole on surole.role_id=srole.id;

sql_no_cache:告诉mysql服务器不缓存这条语句的执行结果

执行完上面的sql语句后，再执行以下语句查看查询成本：
show status like 'last_query_cost';执行结果截图如下：

不过，workbench可以直接在执行计划中展示查询成本，截图如下：

从执行计划中可以看到：
1.执行计划的第一步是查询stole表，而且是全表查询；
2.执行计划的第二部是查询surole表，也是全表查询；
3.执行计划的第三部是查询suser表，通过聚集索引查询，所以精确查找出一条匹配的数据；
4.srole表和surole表通过hash join关联查询数据，最终查出12条匹配的数据,然后和suer表的查询结果进行嵌套循环查询，前台循环查询的成本计算公式很简单，就是将潜逃的字查询的查询成本进行累加求和；
5.sql语句中，suser表是主表，然后依次关联surole表和srole表。但是，执行计划是先查询srole表，再查询surole表，最后查询suser表，两者顺序不同；
6.这是mysql优化器最终选择的它认为最优的执行计划；
4.sql的第二种写法

上面的sql可以用另一种写法，然后我们再看看新写法的查询成本
以下是新的写法：
select straight_join suser.id,suser.name ,srole.name from sys_user suser inner join sys_user_role surole on suser.id=surole.user_id inner join sys_role srole on surole.role_id=srole.id;

straight_join: 让mysql优化器按照sql的join顺序来查询数据
现在我们再看一下查询成本及执行计划：

从上图可知：
1.现在的sql查询数据的顺序和执行计划是一致的；
2.最终查询成本是42.05，比优化器选择的执行计划的成本要高很多；

5.总结

1.从sql语句和执行计划可以看出，suser表全表只有12数据，srole表全表有4条数据，surole表全表有30条数据，如果suser表和srole表之间有关联字段的话，就能让这两张表做hash join关联查询，最后在与surole表做潜逃循环查询，这样的话，成本能比现在更低，但是，实际上，suser表和srole表之间并没有关联字段，所以这种假设不成立，感觉是在说废话...;
2.大多数情况下，优化器选择的执行计划都是查询成本最低的；
6.说明：

1.执行成本：执行成本为42.05的意思是，mysql认为大概需要做42个数据页的随机查找才能完成查询；
2.执行成本来源：执行成本是根据一系列的统计信息得来的，包括：每个表活着索引的页面个数、索引的基数（索引中不同值的数量）、索引和数据行的长度、索引分布情况；
3.优化器在评估成本的时候不会评估任何层面的缓存，包括mysql服务器内部的缓存，它假设读取任何数据都需要一次磁盘I/O；
7.mysql优化器在哪些情况戏会选择错误的（非最优的）执行计划

统计信息不准确。mysql服务器依赖存储引擎提供的统计信息来评估成本，但是有的存储引擎提供的信息是准确的，比如myisam,有的则不准确，比如innodb。
执行计划中的成本估算不等同于实际执行的成本。即使统计信息准确，优化器给出的执行计划也可能不是最优的。有时候某个查询虽然需要读取更多的数据页，但是这些数据页都是顺序读活着已经在内存中，导致它的成本会更低。mysql并不知道哪些数据页是在内存中，哪些数据页是在磁盘上，所以查询在实际执行过程中的物理I/O次数是无从得知的。
mysql的最优和我们想要的最优可能不同。我们想要的最优的执行计划必然是能让查询最快的，但mysql是基于成本模型选择最优的执行计划。
mysql并不考虑查询兵法执行的情况。
mysql并不都是基于成本的优化，有时也会基于一些固定的规则。比如，存在全文搜索的match()子句，当有全文索引的时候，优化器就会选择全文索引来执行查询，即使用别的索引和where条件的查询会更快。
mysql不会考虑不受其控制的操作的成本。比如我么自定义的函数及存储过程。

mysql查询成本比较

浏览过的版块

所属分类: 问答交流

新帖推荐: 30日

推荐作品