mysql5.7的SQL执⾏成本计算，IO成本和CPU成本，单表查询成本，多表连接查
询成本。。。
⼀、IO成本
mysql的innodb，都是需要从磁盘中读取数据到内存，就是IO成本，每次读取磁盘，⾄少耗时0.01秒，⾄少读⼀
页，innodb⼀个页的数据存储⼤⼩是16KB  1.0没有单位，就是个⽐较值。
⼆、CPU成本
从磁盘读到数据后要放到内存中处理数据的过程，这是CPU成本。读取后并且检测可能的where这⾥的1.0和0.2被称之为成本常数。
三、单表查询成本计算步骤
3.1、根据搜索条件，出所有可能使⽤的索引，也就是EXPLAIN的possible_keys。参考《》
3.2、计算全表扫描的代价
3.3、计算使⽤不同索引执⾏查询的代价。尤其是可能的索引为多个的时候
3.4、对⽐各种执⾏⽅案的代价，出成本最低的哪⼀个
四、例⼦
4.1、有这样⼀个表：
CREATE TABLE `t_user`  (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
`username` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'user name',
`age` int(4) NOT NULL DEFAULT 20 COMMENT 'user age',
`birthday_date_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'user birthday',
`address` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`remark` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'remark something',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'create time',
`version` int(4) NOT NULL DEFAULT 0 COMMENT 'update version',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `idx_name`(`username`) USING BTREE,
INDEX `idx_age_remark`(`age`, `remark`) USING BTREE,
INDEX `idx_create_time`(`create_time`) USING BTREE,
INDEX `idx_address`(`address`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10003 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC;
4.3【表的页数据、索引数据、⾏数等信息】
SHOW TABLE STATUS like 't_user'\G
Row_format:  数据格式。
Rows 表数据⾏数，MyISAM的这个数据⾏数统计是准确的，⽽InnoDB是估值是不准确的。这⾥t_user表的预估⾏数是9964⾏。
Data_length: 该表所占⽤空间的字节数。 1589248 bytes = 1552 kb
4.4【SQL执⾏成本分析】
现在我们需要根据“三、单表查询成本计算步骤”来逐个计算成本：
4.4.1 第⼀步：计算全表扫描成本
因为mysql每⼀页存储数据⼤⼩是16KB，所以InnoDB占⽤了 1552 （t_user的Data_length值） / 16 = 97 页磁盘来存储数据。
磁盘IO 成本 = 97 页 * 1.0 (磁盘IO成本) + 1.1(微调数)  = 98.1
CPU 成本 = 9964 (t_user的Rows数据) * 0.2 + 1.0(微调数) = 1993.8
全表扫描成本 = 98.1 + 1993.8 = 2091.9
4.4.2 第⼆步：计算⾛索引的查询成本——create_time索引列查询成本
mysql规定，当读取索引扫描的时候，每当读取⼀个扫描区间或者范围区间的IO成本，和读取⼀个页⾯的IO成本，是⼀样的，都是1.0。根据4.1章节的表结构分析，我们可能⽤到的索引列是address和create_time，由于这两个列都是普通索引，我们这⾥就选择create_time索引的range去计算索引成本。那么其他where条件直接置为true:
SELECT * FROM t_user WHERE
address in ('shanghaishi', 'beijingshi')    -- true
AND create_time > '2021-04-14 07:00:00'
AND create_time < '2021-04-16 07:00:00'
AND birthday_date_time > create_time  -- true
AND remark like '%7%'  -- true
AND version = 0;  -- true
最后简化成SQL：
SELECT * FROM t_user WHERE create_time > '2021-04-14 07:00:00' AND create_time < '2021-04-16 07:00:00';
【IO成本】也就是说扫描区间只有⼀个，所以磁盘IO成本 =  1(个扫描区间) * 1.0(磁盘IO成本) = 1.0
【CPU成本】那么在两个给定的⽇期参数这⼀个区间之内，⼤概有多少记录数呢？第⼀，mysql会计算⼀个页⾯⼤概有多少记录，第⼆mysql会计算这两个区间参数⼤概隔了多少个页⾯，然后使⽤上EXPLAIN⾥的rows值⼤概有⼏条记录，这⾥参考EXPLAIN的rows值是8条。 8 * 0.2 (cpu成本) + 0.01(微调成本) = 1.61
【回表IO成本】读取完数据后，由于查询的是*所以需要回表，回表涉及IO成本，mysql认为每回表⼀个聚簇索引，都需要回表⼀个页⾯，⼀个页⾯的IO成本是1.0，参考EXPLAIN的rows结果是8条，8*1.0 = 8.0
【回表CPU成本】回表的CPU成本 8 * 0.2 = 1.6      这⼀步包含了Where条件中其他过滤条件的时间成
本
【计算出最终的⾛索引查询成本】 1.0 + 1.61 + 8.0 + 1.6 = 12.21
4.4.3 第三步：计算⾛索引的查询成本——address索引列查询成本
EXPLAIN format = json SELECT * FROM t_user WHERE address in ('shanghaishi', 'beijingshi');
【IO成本】由于这⾥ where address in ('shanghaishi', 'beijingshi')有两个参数，就代表有两个扫描区间，也就意味着访问 address 的IO成本是： 2 * 1.0 = 2.0
【CPU成本】使⽤上EXPLAIN⾥的rows值 291 * 0.2(cpu成本)  + 0.01(微调成本) = 58.21
【回表IO成本】291 * 1.0 = 291
【回表CPU成本】291 * 0.2 = 58.2      这⼀步包含了Where条件中其他过滤条件的时间成本
【计算出最终的⾛索引查询成本】 2.0 + 58.21 + 291 + 58.2 = 409.41
这⾥计算出的409.41可以使⽤EXPLAIN的json输出格式进⾏验证： EXPLAIN format = json SELECT * FROM t_user WHERE address in ('shanghaishi', 'beijingshi');  可以看到query_cost确实是：409.41
上⾯的rows_examined_per_scan的291条 = 普通EXPLAIN视图的rows 数⼀致。⽐较重要的是cost_info⾥的信息：prefix_cost = 409.41 = read_cost +
eval_cost，data_read_per_json = 145K 是指连接查询时的数据量
4.4.4 第四步：执⾏成本⽐较
根据之前三个步骤的执⾏成本计算结果进⾏⽐较：
全表扫描成本：2091.9
create_time索引列查询成本：12.21
address索引列查询成本： 409.41
所以，mysql认为使⽤create_time索引列执⾏SQL是时间成本最低的。
4.5 EXPLAIN验证
根据4.4的执⾏成本结果分析，SQL应该实际⾛create_time列索引，最后我们⽤EXPLAIN来验证下key列，你看，mysql决定⾛idx_create_time索引：
join和in哪个查询更快
EXPLAIN
SELECT * FROM t_user
WHERE address in ('shanghaishi', 'beijingshi')
AND create_time > '2021-04-14 07:00:00'
AND create_time < '2021-04-16 07:00:00'
AND birthday_date_time > create_time
AND remark like '%7%'
AND version = 0;
五、多表连接查询成本
多表连接查询成本 = ⼀次驱动表成本 + 从驱动表查出的记录数 * ⼀次被驱动表的成本
六、mysql执⾏成本详细步骤展⽰
在上⾯第四步骤详细讲述了如何⼿动计算出各个索引列的执⾏成本以及全表扫描执⾏成本，但是这个过程太繁琐了，mysql提供了更简便直观的optimizer_trace功能来详细列出了各个步骤的执⾏成本明细。
set optimizer_trace = "enabled=on,one_line=off";⾸先开启optimizer_trace的开关，默认是关闭的。这⾥的one_line是说结果集要不要⼀⾏输出来给你看，默认off就⾏，毕竟很⼤的⼀个json结果集⼀⾏显⽰出来⾁眼很难观察。
show variables like 'optimizer_trace'；然后再验证下是否打开了
执⾏⼀下以下这个query SQL，其中包含了3个索引：
SELECT * FROM mytest.t_user
WHERE address in ('shanghaishi', 'beijingshi')  -- address 是普通索引列
AND create_time > '2021-04-14 07:00:00'        -- create_time 也是普通索引列
AND create_time < '2021-04-16 07:00:00'
AND birthday_date_time > create_time
AND remark like '%7%'            -- remark是⼀个组合索引的第⼆顺序位索引：idx_age_remark(age,remark)
AND version = 0;
select * from information_schema.OPTIMIZER_TRACE;        mysql使⽤optimizer_trace来记录刚刚那个query sql，mysql是如何详细计算出各个索引的执⾏成本的。输出的json 结果集⽐较⼤，分query和trace，query列出了select sql，trace列出了详细成本计算步骤，只要分3个阶段：1）join_preparation 准备阶段    2）join_optimization 优化阶段
3）join_execution 执⾏阶段，我们关注的重点是优化阶段，我这⾥挑优化阶段的⼏个重点的讲：
substitute_generated_columns:  这是mysql为了⽅便计算什么值时临时添加的列，不在table⾥保存。
table_dependencies:  分析表的依赖信息
【index dive】在两个区间之间计算有多少条记录的⽅式，在mysql中被称为index dive。如果⼀个SQL ⽤了 IN (2万个参数，或者⼀个⼦查询SQL结果集⾮常多的)，那么mysql很有可能认为⾛全表扫描更快。参考《》
【mrr】当mysql读取⼀批⼆级索引时，会将根据这些⼆级索引拿到的主键id进⾏排好序，去回表到主
键索引拿，这个优化过程由Mysql⾃⾏控制，我们⽆法⼲预，这就是MRR技术，多范围查询技术。当然，实现这个条件⽐较苛刻。
多表连接查询参数：
rows_examined_per_scan: 321    表⽰从驱动表结果集预估有321条记录会对被驱动表进⾏扫描
rows_produced_per_join: 321    如果这两个数值⼀样，表⽰filter =100 即过滤100%的数据，但是如果这个值⽐上⼀个值少，则可能使⽤了覆盖索引等进⾏了优化，那么filter也会少于100%
filtered: 100
prefix_cost是总成本
七、执⾏成本计算优化：提前结束
⽐如abcd四个表进⾏多表连接查询，当mysql计算出按照a,b,c,d顺序计算出查询成本是100时，⼜在计算按照a,c,b,d这个顺序执⾏成本的过程中，发现仅仅ac这两个表的连接成本110就已经超越了100，就会提前结束放弃对a,c,b,d顺序的计算。多表连接成本计算的计算次数 = ⼏个表的N次⽅，四个表就是16次成本计算:  abcd,  acbd, abdc 等等各种排列。
但是如果多表连接中，表个数太多，mysql也不会穷举各种排列，mysql有⼀个表数量设定  show variables like 'Optimizer_search_depth';  -- 默认62张表。⼩于62张表的，穷举算法，⼤于62张表的，按照62种算法计算。
⼋、mysql成本cost成本参数设定
mysql有两种成本计算engine_cost引擎成本和server_cost服务端成本， show tables from mysql like '%cost%';
select * from mysql.server_cost; 查看服务端成本计算参数都有哪些，cost_value列为null值的表⽰使⽤的是mysql的默认值。
disk_temptable_create_cost  创建基于磁盘的临时表成本，默认值40
disk_temptable_row_cost    往磁盘的临时表⾥写⼊或读取⼀条数据的成本：1
key_compare_cost                两条记录做值⽐较的成本：0.1，排序操作时⽤到这个值
memory_temptable_create_cost          创建基于内存的临时表成本，默认值2
memory_temptable_row_cost              往内存中的临时表⾥写⼊或读取⼀条数据的成本：0.2
row_evaluate_cost                          读取⼀条记录过滤where条件看是否满⾜我们的搜索条件，默认成本： 0.2
备注：如果你想修改以上默认值，可以update它，如果想设置回默认值，只要设置为null即可。
select * ine_cost；存储引擎的成本
engine_name = default      表⽰对于所有引擎全部适⽤，也可能指定存储引擎⽐如innodb
device_type = 0                  存储引擎所⽤的设备类型，为了⽀持机械硬盘或者固态硬盘
cost_name = io_block_read_cost                  从磁盘⾥读取⼀个块的成本，默认值1
cost_name = memory_block_read_cost      从内存⾥读取⼀个块的成本，默认值1。从磁盘⾥读取和从内存中读取成本⼀样，是因为mysql不知道要读取的数据是在磁盘中还是在内存中，所以mysql简单的认为都是1。
备注：如果你修改了成本参数，可能会造成⼀定后果，⽐如把row_evaluate_cost调⼤，mysql会更倾向于使⽤索引，⽽不是全表扫描，⽐如你把memory_temptable_create_cost 调的⽐disk_temptable_create_cost还⼤，mysql会更倾向于从磁盘中创建临时表，⽽不是内存中。
九、mysql对于表和索引的统计
show tables from mysql like 'innodb%;
desc mysql.innodb_table_stats；表统计。database_name 数据库名；table_name 表名；last_update 最后更新时间；n_rows ⾏数；clustered_index_size 表聚簇索引占⽤页⾯数量； sum_of_other_index_sizes 其他索引占⽤页⾯数量；其中n_rows 这个预估数是mysql⽤20页（默认页数可调整）的数据平均下来的采样数量乘以多少个页得出来的预估数量，不是精确数值。
desc mysql.innodb_index_stats；索引统计。stat_name 索引名称；stat_value 不重复数量；mysql默认在数据有10%的更新量下，会对索引统计进⾏更新。我们也可以⼿动更新索引统计： analyze table t_order;  对于t_order表进⾏⼿动索引统计更新。⼿动更新统计要少做，是⼀个对表的阻塞操作，尽量让mysql默认执⾏就⾏。
end.

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。