MySQL_15_MySQL底层SQL查询成本计算

祖母绿宝石

已于 2022-11-30 21:33:42 修改

阅读量777

点赞数

分类专栏：服务端MySQL数据库全解析文章标签： sql mysql 数据库

于 2020-09-06 11:12:35 首次发布

本文链接：https://blog.csdn.net/qq_36963950/article/details/108428696

版权

服务端MySQL数据库全解析专栏收录该内容

16 篇文章 1 订阅

订阅专栏

文章目录

一、前言
二、select count(*) 和 select count(1) 取舍
- 2.1 针对无 where_clause子句的 COUNT(*)，MySQL 是有专门的优化，优化器会选择成本最小的辅助索引查询计数，达到性能最高
- 2.2 不同数据库的计数取舍
三、SQL 选用索引的执行成本计算(I/O成本+CPU成本)
四、尾声

一、前言

二、select count(*) 和 select count(1) 取舍

2.1 针对无 where_clause子句的 COUNT(*)，MySQL 是有专门的优化，优化器会选择成本最小的辅助索引查询计数，达到性能最高

使用以下 sql 会导致慢查询

SELECT COUNT(*) FROM SomeTable   // 没有where子句的select count(*)
SELECT COUNT(1) FROM SomeTable

原因是会造成全表扫描，但是这种说法是有问题的，实际上针对无 where_clause子句的 COUNT(*)（如上面两句，都没有where子句），MySQL 是有优化的，优化器会选择成本最小的辅助索引查询计数（金手指：辅助索引即二级索引，索引篇谈到过），其实反而性能最高。

针对这个疑问，我首先去生产上找了一个千万级别的表使用 EXPLAIN 来查询了一下执行计划

EXPLAIN SELECT COUNT(*) FROM SomeTable   // 没有where子句，explain表示分析

结果如下

在这里插入图片描述

如图所示: 发现确实此条语句在此例中用到的并不是主键索引，而是辅助索引（金手指：key表示索引名称、key_len表示索引长度，这里2就是选择最小的，ref表示引用，rows表示返回的行数，就是select count(*)，extra表示额外备注信息，这里是使用了索引，使用的索引就是key列的值），实际上在此例中我试验了，不管是 COUNT(1)，还是 COUNT()，MySQL 都会用成本最小的辅助索引查询方式来计数，也就是使用 COUNT() 由于 MySQL 的优化已经保证了它的查询性能是最好的！随带提一句，COUNT()是 SQL92 定义的标准统计行数的语法，并且效率高，所以**请直接使用COUNT()查询表的行数，不要怀疑，没有where子句的使用，使用select count(*) 效率很高**！

但有个前提，在 MySQL 5.6 之后的版本中才有这种优化。

那么这个成本最小该怎么定义呢，有时候在 WHERE 中指定了多个条件，再次执行select count(*)，为什么最终 MySQL 执行的时候却选择了另一个索引，甚至不选索引？

2.2 不同数据库的计数取舍

在不同数据库中，三个常用关系型数据库和其他数据库，如下：

(1) 在mysql中，计算行数是 count(*) > count(1) > count(id) ,因为mysql对count(*)专门做了优化，但是，其他数据库建议用count(1);
(2) oracle就是推荐count(1)，因为count(1)会比count(*)快，count(*)需要做一些字段聚合操作;
(3) sqlServer就是 count(主键) > count(1) > count(*).
(4) 其他国产数据库都是推荐使用count(1)
(5) 即使mysql，count(1)与count(*)相差也不大，所以如果做通用性软件产品，推荐使用count(1)，但是面试中问到mysql，回答count(*),这属于mysql的一个考点，另外，阿里的规范也是mysql推荐使用count(*).

三、SQL 选用索引的执行成本计算(I/O成本+CPU成本)

3.1 理论

在有多个索引的情况下，在查询数据前，MySQL 会选择成本最小原则来选择使用对应的索引，这里的成本主要包含两个方面。

（1）IO 成本: 即从磁盘把数据加载到内存的成本，默认情况下，读取数据页的 IO 成本是 1，MySQL 是以页的形式读取数据的，即当用到某个数据时，并不会只读取这个数据，而会把这个数据相邻的数据也一起读到内存中，这就是有名的程序局部性原理，所以 MySQL 每次会读取一整页，一页的成本就是 1。所以，读取的页是数量默认为1，则 IO 的成本主要和页的大小有关

（2）CPU 成本：将数据读入内存后，还要检测数据是否满足条件和排序等 CPU 操作的成本，显然它与行数有关，默认情况下，检测记录的成本是 0.2。所以，CPU成本和行数有关。

3.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
  KEY `create_time` (`create_time`)      // 又创建一个key
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

CREATE PROCEDURE insert_person()   // 创建一个存储过程
begin      // 存储过程开始
    declare c_id integer default 1;   // 声明一个int型c_id，默认值为1
    while c_id<=100000 do   // while循环 cid<=10000 
    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    // 存储过程开始

指定N条插入的时候，需要使用到循环，此时就要用到存储过程

插入之后我们现在使用 EXPLAIN 来计算下统计总行数到底使用的是哪个索引

EXPLAIN SELECT COUNT(*) FROM person   // 不带where子句的select count(*)

在这里插入图片描述

type为index，表示是使用了索引，key为create_time表示选择了 create_time 辅助索引，显然 MySQL 认为使用此索引进行查询成本最小，这也是符合我们的预期，使用辅助索引来查询确实是性能最高的！

我们再来看以下 SQL 会使用哪个索引

SELECT * FROM person WHERE NAME >'name84059' AND create_time>'2020-05-23 14:39:18' // 使用有where子句的select *

在这里插入图片描述
type为all，表示是使用了全表扫描，key为null表示没有使用索引，但是，从 WHERE 的查询条件来看确实都能命中索引（因为where子句中有create_time），那是否是使用 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'

from 后面加上 force index(create_time) 可以强制走指定的索引字段

小结：从实际执行的效果看使用覆盖索引查询比使用全表扫描执行的时间快了一倍！说明 MySQL 在查询前做的成本估算不准！为什么Mysql查询前的成本估算会不准确？
回答：因为 SQL 认为的最优跟我们认为不一样，我们可以认为执行时间短的是最优的，但 MySQL 认为的成本小未必意味着执行时间短。Mysql选择的这个全表扫描，理论上是总成本最小的（IO成本+CPU成本），只是执行时间上慢了。

接下来，我们先来看看，MySQL 做全表扫描的成本有多少。

3.3 理论：MySQL 做全表扫描的成本有多少，SHOW TABLE STATUS LIKE ‘person’ 构造全表扫描

前面我们说了成本主要 IO 成本和 CPU 成本有关，对于全表扫描来说也就是分别和聚簇索引占用的页面数（IO成本）和表中的记录数（CPU成本）。执行以下命令

SHOW TABLE STATUS LIKE 'person'

在这里插入图片描述

可以发现

行数是 100264，我们不是插入了 10 w 行的数据了吗，怎么算出的数据反而多了，其实这里的计算是估算，也有可能这里的行数统计出来比 10 w 少了，估算方式有兴趣大家去网上查找，这里不是本文重点，就不展开了。得知行数，那我们知道 CPU 成本是 100264 * 0.2 = 20052.8（金手指：上面说了，CPU成本默认为0.2）。

Data_length 数据长度是 5783552 字节，就是5783552B，InnoDB 每个页面的大小是 16 KB，5783552 / 16*1024 可以算出页面数量是 353，然后一个页面大小为 1，所以页面数量 353 * 单位大小 1 = IO 成本 353。

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

3.4 实践：optimizer_trace 工具，计算索引成本和全表扫描成本，彻底解释为什么Mysql要选择全表扫描

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

optimizer_trace 功能使用如下

SET optimizer_trace="enabled=on";   // 打开 optimizer_trace 
SELECT create_time FROM person WHERE NAME >'name84059' AND create_time > '2020-05-23 14:39:18';      // 有where子句，查询select create_time
SELECT * FROM information_schema.OPTIMIZER_TRACE;   // 没有where子句，查询select * 
SET optimizer_trace="enabled=off";  // 用完了，就关闭 optimizer_trace

执行之后我们主要观察使用 name_score，create_time 索引及全表扫描的成本。

第一，先来看下使用 name_score 索引执行的的预估执行成本:

{
    "index": "name_score",  // 使用name_score索引
    "ranges": [
      "name84059 <= name"     // 范围，就是where中的条件  NAME >'name84059'
    ],
    "index_dives_for_eq_ranges": true,   
    "rows": 25372,      // 返回行数   NAME >'name84059'
    "cost": 30447      // 总成本（io成本+cpu成本）
}

可以看到执行成本为 30447，高于我们之前算出来的全表扫描成本：20406。所以没选择此索引执行

注意：这里的 30447 是查询二级索引的 IO 成本和 CPU 成本之和，再加上回表查询聚簇索引的 IO 成本和 CPU 成本之和。

第二，再来看下使用 create_time 索引执行的的预估执行成本:

{
    "index": "create_time",  // 使用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 对于 select count(*) from table-name 的特有优化。对于开发者而言，当使用mysql的时候，使用 select count(*) from table-name ，当使用其他数据库的时候，使用 select count(*) from table-name .

第二个是通过一个例子深入剖析了 MySQL 的执行计划是如何选择的，以及为什么它的选择未必是我们认为的最优的，这也提醒我们，在生产中如果有多个索引的情况，使用 WHERE 进行过滤未必会选中你认为的索引，我们可以提前使用 EXPLAIN, optimizer trace 来优化我们的查询语句。即Mysql的默认选择并不是时间成本最小的，所以，还是需要程序员的自己的智慧（即：使用覆盖索引，避免mysql拒绝回表而拒绝索引，采用全表扫描）。

祖母绿宝石

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
1
评论
MySQL_15_MySQL底层SQL查询成本计算

文章目录一、前言二、select count(*) 和 select count(1)针对无 where_clause子句的 COUNT(*)，MySQL 是有优化的，优化器会选择成本最小的辅助索引查询计数，达到性能最高理论：SQL 选用索引的执行成本如何计算(全文重点，面试考点)实践：SQL 选用索引的执行成本如何计算(全文重点，面试考点)继续深入，MySQL 做全表扫描的成本有多少，SHOW TABLE STATUS LIKE 'person' 构造全表扫描optimizer_trace 工具，计算索
复制链接

扫一扫