合肥网站代运营公司有哪些,松溪网站建设,自己做的导航网站,阳江招聘网0662前言
SELECT COUNT(*)会不会导致全表扫描引起慢查询呢#xff1f;
SELECT COUNT(*) FROM SomeTable 网上有一种说法#xff0c;针对无 where_clause 的 COUNT(*)#xff0c;MySQL 是有优化的#xff0c;优化器会选择成本最小的辅助索引查询计数#xff0c;其实反而性能…前言
SELECT COUNT(*)会不会导致全表扫描引起慢查询呢
SELECT COUNT(*) FROM SomeTable 网上有一种说法针对无 where_clause 的 COUNT(*)MySQL 是有优化的优化器会选择成本最小的辅助索引查询计数其实反而性能最高这种说法对不对呢
针对这个疑问我首先去生产上找了一个千万级别的表使用 EXPLAIN 来查询了一下执行计划
EXPLAIN SELECT COUNT(*) FROM SomeTable 结果如下 如图所示: 发现确实此条语句在此例中用到的并不是主键索引而是辅助索引实际上在此例中我试验了不管是 COUNT(1)还是 COUNT(*)MySQL 都会用成本最小 的辅助索引查询方式来计数也就是使用 COUNT(*) 由于 MySQL 的优化已经保证了它的查询性能是最好的随带提一句COUNT(*)是 SQL92 定义的标准统计行数的语法并且效率高所以请直接使用COUNT(*)查询表的行数
所以这种说法确实是对的。但有个前提在 MySQL 5.6 之后的版本中才有这种优化。
那么这个成本最小该怎么定义呢有时候在 WHERE 中指定了多个条件为啥最终 MySQL 执行的时候却选择了另一个索引甚至不选索引
本文将会给你答案本文将会从以下两方面来分析 SQL 选用索引的执行成本如何计算 实例说明
SQL 选用索引的执行成本如何计算
就如前文所述在有多个索引的情况下 在查询数据前MySQL 会选择成本最小原则来选择使用对应的索引这里的成本主要包含两个方面。 IO 成本: 即从磁盘把数据加载到内存的成本默认情况下读取数据页的 IO 成本是 1MySQL 是以页的形式读取数据的即当用到某个数据时并不会只读取这个数据而会把这个数据相邻的数据也一起读到内存中这就是有名的程序局部性原理所以 MySQL 每次会读取一整页一页的成本就是 1。所以 IO 的成本主要和页的大小有关 CPU 成本将数据读入内存后还要检测数据是否满足条件和排序等 CPU 操作的成本显然它与行数有关默认情况下检测记录的成本是 0.2。
实例说明
为了根据以上两个成本来算出使用索引的最终成本我们先准备一个表以下操作基于 MySQL 5.7.18
CREATE TABLE person ( id bigint(20) NOT NULL AUTO_INCREMENT, name varchar(255) NOT NULL, score int(11) NOT NULL, create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY name_score (name(191),score), KEY create_time (create_time)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4; 这个表除了主键索引之外还有另外两个索引, name_score 及 create_time。然后我们在此表中插入 10 w 行数据只要写一个存储过程调用即可如下:
CREATE PROCEDURE insert_person()
begin declare c_id integer default 1; while c_id100000 do insert into person values(c_id, concat(name,c_id), c_id100, date_sub(NOW(), interval c_id second)); set c_idc_id1; end while;
end 插入之后我们现在使用 EXPLAIN 来计算下统计总行数到底使用的是哪个索引
EXPLAIN SELECT COUNT(*) FROM person 从结果上看它选择了 create_time 辅助索引显然 MySQL 认为使用此索引进行查询成本最小这也是符合我们的预期使用辅助索引来查询确实是性能最高的
我们再来看以下 SQL 会使用哪个索引
SELECT * FROM person WHERE NAME name84059 AND create_time2020-05-23 14:39:18 用了全表扫描理论上应该用 name_score 或者 create_time 索引才对从 WHERE 的查询条件来看确实都能命中索引那是否是使用 SELECT *造成的回表代价太大所致呢我们改成覆盖索引的形式试一下
SELECT create_time FROM person WHERE NAME name84059 AND create_time 2020-05-23 14:39:18 结果 MySQL 依然选择了全表扫描这就比较有意思了理论上采用了覆盖索引的方式进行查找性能肯定是比全表扫描更好的为啥 MySQL 选择了全表扫描呢既然它认为全表扫描比使用覆盖索引的形式性能更好那我们分别用这两者执行来比较下查询时间吧
-- 全表扫描执行时间: 4.0 ms
SELECT create_time FROM person WHERE NAME name84059 AND create_time2020-05-23 14:39:18 -- 使用覆盖索引执行时间: 2.0 ms
SELECT create_time FROM person force index(create_time) WHERE NAME name84059 AND create_time2020-05-23 14:39:18 从实际执行的效果看使用覆盖索引查询比使用全表扫描执行的时间快了一倍说明 MySQL 在查询前做的成本估算不准我们先来看看 MySQL 做全表扫描的成本有多少。
前面我们说了成本主要 IO 成本和 CPU 成本有关对于全表扫描来说也就是分别和聚簇索引占用的页面数和表中的记录数。执行以下命令
SHOW TABLE STATUS LIKE person 可以发现 也就是说全表扫描的成本是 20052.8 353 20406。
这个结果对不对呢我们可以用一个工具验证一下。在 MySQL 5.6 及之后的版本中我们可以用 optimizer trace 功能来查看优化器生成计划的整个过程 它列出了选择每个索引的执行计划成本以及最终的选择结果我们可以依赖这些信息来进一步优化我们的 SQL。
optimizer_trace 功能使用如下
SET optimizer_traceenabledon;
SELECT create_time FROM person WHERE NAME name84059 AND create_time 2020-05-23 14:39:18;
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET optimizer_traceenabledoff; 执行之后我们主要观察使用 name_scorecreate_time 索引及全表扫描的成本。
先来看下使用 name_score 索引执行的的预估执行成本:
{ index: name_score, ranges: [ name84059 name ], index_dives_for_eq_ranges: true, rows: 25372, cost: 30447
} 可以看到执行成本为 30447高于我们之前算出来的全表扫描成本20406。所以没选择此索引执行
注意这里的 30447 是查询二级索引的 IO 成本和 CPU 成本之和再加上回表查询聚簇索引的 IO 成本和 CPU 成本之和。
再来看下使用 create_time 索引执行的的预估执行成本:
{ index: create_time, ranges: [ 0x5ec8c516 create_time ], index_dives_for_eq_ranges: true, rows: 50132, cost: 60159, cause: cost
} 可以看到成本是 60159,远大于全表扫描成本 20406自然也没选择此索引。
再来看计算出的全表扫描成本
{ considered_execution_plans: [ { plan_prefix: [ ], table: person, best_access_path: { considered_access_paths: [ { rows_to_scan: 100264, access_type: scan, resulting_rows: 100264, cost: 20406, chosen: true } ] }, condition_filtering_pct: 100, rows_for_plan: 100264, cost_for_plan: 20406, chosen: true } ]
} 注意看 cost20406与我们之前算出来的完全一样这个值在以上三者算出的执行成本中最小所以最终 MySQL 选择了用全表扫描的方式来执行此 SQL。
实际上 optimizer trace 详细列出了覆盖索引回表的成本统计情况有兴趣的可以去研究一下。
从以上分析可以看出 MySQL 选择的执行计划未必是最佳的原因有挺多就比如上文说的行数统计信息不准再比如 MySQL 认为的最优跟我们认为不一样我们可以认为执行时间短的是最优的但 MySQL 认为的成本小未必意味着执行时间短。
总结
本文通过一个例子深入剖析了 MySQL 的执行计划是如何选择的以及为什么它的选择未必是我们认为的最优的这也提醒我们在生产中如果有多个索引的情况使用 WHERE 进行过滤未必会选中你认为的索引我们可以提前使用 EXPLAIN,optimizer trace 来优化我们的查询语句。 行数是 100264我们不是插入了 10 w 行的数据了吗怎么算出的数据反而多了其实这里的计算是估算 也有可能这里的行数统计出来比 10 w 少了估算方式有兴趣大家去网上查找这里不是本文重点就不展开了。得知行数那我们知道 CPU 成本是 100264 * 0.2 20052.8。 数据长度是 5783552InnoDB 每个页面的大小是 16 KB可以算出页面数量是 353。
