乐趣区

关于mysql:SELECT-COUNT-会造成全表扫描回去等通知吧

jiezi

本文曾经收录到 Github 仓库，该仓库蕴含 计算机根底、Java 根底、多线程、JVM、数据库、Redis、Spring、Mybatis、SpringMVC、SpringBoot、分布式、微服务、设计模式、架构、校招社招分享 等外围知识点，欢送 star~

Github 地址

如果拜访不了 Github，能够拜访 gitee 地址。

gitee 地址

前言

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 老本是 1，MySQL 是以页的模式读取数据的，即当用到某个数据时，并不会只读取这个数据，而会把这个数据相邻的数据也一起读到内存中，这就是有名的程序局部性原理，所以 MySQL 每次会读取一整页，一页的老本就是 1。所以 IO 的老本次要和页的大小无关
  • CPU 老本：将数据读入内存后，还要检测数据是否满足条件和排序等 CPU 操作的老本，显然它与行数无关，默认状况下，检测记录的老本是 0.2。

最全面的 Java 面试网站

实例阐明

为了依据以上两个老本来算出应用索引的最终老本，咱们先筹备一个表（以下操作基于 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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

这个表除了主键索引之外，还有另外两个索引, name_score 及 create_time。而后咱们在此表中插入 10 w 行数据，只有写一个存储过程调用即可，如下:

CREATE PROCEDURE insert_person()
begin
    declare c_id integer default 1;
    while c_id<=100000 do
    insert into person values(c_id, concat('name',c_id), c_id+100, date_sub(NOW(), interval c_id second));
    set c_id=c_id+1;
    end while;
end

插入之后咱们当初应用 EXPLAIN 来计算下统计总行数到底应用的是哪个索引

EXPLAIN SELECT COUNT(*) FROM person

从后果上看它抉择了 create_time 辅助索引，显然 MySQL 认为应用此索引进行查问老本最小，这也是合乎咱们的预期，应用辅助索引来查问的确是性能最高的！

咱们再来看以下 SQL 会应用哪个索引

SELECT * FROM person WHERE NAME >'name84059' AND create_time>'2020-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_time>'2020-05-23 14:39:18' 

-- 应用笼罩索引执行工夫: 2.0 ms
SELECT create_time FROM person force index(create_time) WHERE NAME >'name84059' AND create_time>'2020-05-23 14:39:18'

从理论执行的成果看应用笼罩索引查问比应用全表扫描执行的工夫快了一倍！阐明 MySQL 在查问前做的老本估算不准！咱们先来看看 MySQL 做全表扫描的老本有多少。

后面咱们说了老本次要 IO 老本和 CPU 老本无关，对于全表扫描来说也就是别离和聚簇索引占用的页面数和表中的记录数。执行以下命令

SHOW TABLE STATUS LIKE 'person'

能够发现

  1. 行数是 100264，咱们不是插入了 10 w 行的数据了吗，怎么算出的数据反而多了，其实这里的计算是 估算，也有可能这里的行数统计进去比 10 w 少了，估算形式有趣味大家去网上查找，这里不是本文重点，就不开展了。得悉行数，那咱们晓得 CPU 老本是 100264 * 0.2 = 20052.8。
  2. 数据长度是 5783552，InnoDB 每个页面的大小是 16 KB，能够算出页面数量是 353。

也就是说全表扫描的老本是 20052.8 + 353 = 20406。

这个后果对不对呢，咱们能够用一个工具验证一下。在 MySQL 5.6 及之后的版本中，咱们能够用 optimizer trace 性能来查看优化器生成打算的整个过程，它列出了抉择每个索引的执行计划成本以及最终的抉择后果，咱们能够依赖这些信息来进一步优化咱们的 SQL。

optimizer_trace 性能应用如下

SET optimizer_trace="enabled=on";
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_trace="enabled=off";

执行之后咱们次要察看应用 name_score，create_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
      }
    ]
}

留神看 cost：20406，与咱们之前算进去的齐全一样！这个值在以上三者算出的执行老本中最小，所以最终 MySQL 抉择了用全表扫描的形式来执行此 SQL。

实际上 optimizer trace 具体列出了笼罩索引，回表的老本统计状况，有趣味的能够去钻研一下。

从以上剖析能够看出，MySQL 抉择的执行打算未必是最佳的，起因有挺多，就比方上文说的行数统计信息不准，再比方 MySQL 认为的最优跟咱们认为不一样，咱们能够认为执行工夫短的是最优的，但 MySQL 认为的老本小未必意味着执行工夫短。

总结

本文通过一个例子深刻分析了 MySQL 的执行打算是如何抉择的，以及为什么它的抉择未必是咱们认为的最优的，这也揭示咱们，在生产中如果有多个索引的状况，应用 WHERE 进行过滤未必会选中你认为的索引，咱们能够提前应用 EXPLAIN, optimizer trace 来优化咱们的查问语句。

最初给大家分享一个 Github 仓库，下面有大彬整顿的 300 多本经典的计算机书籍 PDF，包含 C 语言、C++、Java、Python、前端、数据库、操作系统、计算机网络、数据结构和算法、机器学习、编程人生 等，能够 star 一下，下次找书间接在下面搜寻，仓库继续更新中~

Github 地址

退出移动版