江苏龙网

  1. 主页 > 生活百科 > >

要不要走索引？MySQL 的成本分析

mysql

谈到索引失效，大家可能都能列举出几个场景，比如：后模糊查询、条件中带函数、索引中断等等 。今天我想和你分享另一个场景：索引成本分析 。
我先用一个具体的例子来描述一下这个场景 。
案例场景假设现在我们有一张人物表，建表语句如下：
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,  PRIMARY KEY (`id`),) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;并创建两个索引：
KEY `name_score` (`name`,`score`) USING BTREE,KEY `create_time` (`create_time`) USING BTREE然后插入 10 万条数据：
create DEFINER=`root`@`%` 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));    -- 需要注意，因为使用的是now()，所以对于后续的例子，使用文中的SQL你需要自己调整条件，否则可能看不到文中的效果    set c_id=c_id+1;    end while;end数据插入后，我们用下面的 SQL 进行查询：
explain select * from person where NAME>'name84059' and create_time>'2020-01-24 05:00:00'

要不要走索引？MySQL 的成本分析

文章插图
 
通过上面的执行计划可以看到：type=All，说明是全表扫描 。
接着我们把 create_time 条件中的 5 点改为 6 点：
explain select * from person where NAME>'name84059' and create_time>'2020-01-24 06:00:00'
要不要走索引？MySQL 的成本分析

文章插图
 
执行计划显示：type=range，key=create_time，走了 create_time 索引，而不是 name_score 联合索引 。
看到这里，你是不是很诧异？接下来，我们就一起来分析一下这背后的原因 。
原因分析MySQL 在查询数据之前，会先对可能的方案做执行计划，然后依据成本决定走哪个执行计划 。这里的成本，包括 IO 成本和 CPU 成本：
  • • IO 成本，是从磁盘把数据加载到内存的成本 。默认情况下，读取数据页的 IO 成本常数是 1（也就是读取 1 个页成本是 1） 。
  • • CPU 成本，是检测数据是否满足条件和排序等 CPU 操作的成本 。默认情况下，检测记录的成本是 0.2 。
MySQL 维护了表的统计信息，可以使用下面的命令查看：
SHOW TABLE STATUS LIKE 'person'
要不要走索引？MySQL 的成本分析

文章插图
 
从图中可以看到，总行数是 100086 行，由于 MySQL 的统计信息是一个估算，这里多了 86 行是正常的 。CPU 成本是 100086*0.2=20017 左右 。
数据长度是 4734976 字节 。对于 InnoDB 来说，4734976 就是聚簇索引占用的空间，等于聚簇索引的页数量 * 每个页面的大小 。InnoDB 每个页面的大小是 16KB，大概计算出页的数量是 289，因此 IO 成本是 289 左右 。
所以，全表扫描的总成本是 20306 左右 。
在 MySQL 5.6 及之后的版本中，我们可以使用 optimizer trace 功能查看优化器生成执行计划的整个过程 。
SET optimizer_trace="enabled=on";explain select * from person where NAME >'name84059' and create_time>'2020-01-24 05:00:00';select * from information_schema.OPTIMIZER_TRACE;SET optimizer_trace="enabled=off";对于按照 create_time>'2020-01-24 05:00:00’ 条件走全表扫描的 SQL，我从 OPTIMIZER_TRACE 的执行结果中，摘出了几个重要片段来重点分析：
1、使用 name_score 对 name84059<name 条件进行索引扫描需要扫描 25362 行，成本是 30435 。
{  "index": "name_score",  "ranges": [    "name84059 < name"  ],  "rows": 25362,  "cost": 30435,  "chosen": false,  "cause": "cost"}


推荐阅读


上一篇：SpringBoot将文件夹打包成ZIP并下载

下一篇：计算机网络-ensp实现VPN