MySQL查询成本
MySLQ会将优化后的语句分别计算成本,取最优SQL执行。MySQL查询成本分为:
- IO成本:将磁盘数据加载到内存的成本。在MySQL的InnoDB引擎中就是一个叶子页。默认成本1.0.
- CPU成本:数据读取检测是否复核搜索条件。默认成本0.2.
基于成本的优化步骤
- 根据搜索条件,分析可能用到的索引。
- 计算全表扫描的查询成本。
- 计算使用每个索引的查询成本。
- 对比各种执行方案,取查询成本最低的方案。
单表查询计算成本计算
准备环境:
CREATE TABLE `product_order` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`order_number` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`order_desc` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`order_status` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`create_time` datetime NOT NULL,
`expire_time` datetime NOT NULL,
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `u_idx_three`(`create_time`, `order_status`, `expire_time`) USING BTREE,
INDEX `idx_number`(`order_number`) USING BTREE,
INDEX `idx_expire_time`(`expire_time`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10004 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = COMPACT;
创建一个表,批量生成10000+数据,其中有三个索引:
- idx_number二级索引
- idx_expire_time二级索引
- create_time + order_status + expire_time联合索引
成本分析:
要分析的SQL:
SELECT
*
FROM
product_order o
WHERE
o.order_number IN ( "9671332", "9504815", "2890999" )
AND o.expire_time < '2024-06-01' AND o.expire_time > '2022-06-01'
AND o.order_status = '0'
AND o.order_desc LIKE '%这是备注%';
分析过程:
- 分析可能用到的索引,order_number或者expire_time。
- 全表扫描成本分析
show TABLE status like 'product_order'; -- 查看系统文件,其中两个属性, rows(预估行数):9907, data_length(数据大小):1589248
全表扫描成本为:
- IO成本:在InnoDB存储引擎中,一个叶子页的大小即一次IO,一个叶子大小为16KB,所以IO成本为:(data_length/16/1024) * 1 + 微调值(默认1.1)。
- CPU成本:rows * 0.2 + 微调值(默认1.0)。
- 在本SQL中的全表扫描成本为:1589248/16/1024 * 0.1 + 1.1 + 9907 * 0.2 + 1.0 = 1993.2。
- 分析可能用到的索引下的查询成本
使用索引查询的成本为二级索引IO成本 + 回表操作的成本。
使用expire_time索引查询成本:
- 二级索引成本:如果使用到了索引且索引可以锁定某些范围也就是B+树子节点的数据范围,每有一个范围IO成本+1,因为o.expire_time < ‘2024-06-01’ AND o.expire_time > ‘2022-06-01’ 可以确定一个范围,所以IO成本为1;
- 回表成本:
- 找到数据最左边数据,然后找到最右边的数据,这个过程成本忽略不计。
- 计算范围内数据量,MySQL会进行估算(此处为2721条)。 cpu成本就为2721*0.2+0.01(微调值) = 544.21
- 每次回表是一次IO,所以IO成本为2721*1 = 2721
- 得到数据后过滤除使用索引的其他条件,这里是CPU成本,2721*0.2 = 544.2
- 使用expire_time索引查询成本为2721 + 544.21 + 544.2 = 3809.41,比全表扫描成本还高,所以不会使用这个二级索引,原因是回表的数据量高达2000+。
使用order_number索引的查询成本:
- 二级索引成本:in条件相当于三个叶子页,所以IO成本为 3;
- 回表成本:同上面逻辑 30.2+0.01(微调值) + 31 + 3*0.2 = 4.21
- 总成本 3+ 4.21 = 7.21
验证:通过explain format = json + select语句查看SQL的查询成本:7.21
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "7.21"
},
"table": {
"table_name": "o",
"access_type": "range",
"possible_keys": [
"idx_number",
"idx_expire_time"
],
"key": "idx_number",
"used_key_parts": [
"order_number"
],
"key_length": "34",
"rows_examined_per_scan": 3,
"rows_produced_per_join": 0,
"filtered": "1.67",
"index_condition": "(`my-test`.`o`.`order_number` in ('9671332','9504815','2890999'))",
"cost_info": {
"read_cost": "7.20",
"eval_cost": "0.01",
"prefix_cost": "7.21",
"data_read_per_join": "54"
},
"used_columns": [
"id",
"order_number",
"order_desc",
"order_status",
"create_time",
"expire_time"
],
"attached_condition": "((`my-test`.`o`.`expire_time` < '2024-06-01') and (`my-test`.`o`.`expire_time` > '2022-06-01') and (`my-test`.`o`.`order_status` = '0') and (`my-test`.`o`.`order_desc` like '%这是备注%'))"
}
}
}
总结
在使用二级索引的时候,如果需要回表,回表的成本是及其高的,所以MySQL一般会使用通过二级索引检索出来数据量少的索引,因为需要回表的数据量少,成本低。
Optimizer Trace(优化器追踪)
查看Optimizer Trace是否开启,默认关闭。
show variables like 'Optimizer Trac';-- 查看
开启Optimizer Trace,注意开启是session级别的。
set optimizer_trace 'enabled = on';
查看分析内容,会看到一个json串
select * from information_schema.OPTIMIZER_TRACE;
json串中的参数
- join_preparation:准备阶段,expanded_query:优化后的SQL语句。
- join_optimization:分析阶段,original_condition:索引处理。analyzing_range_alternatives:分析索引,查看成本
- considered_execution_plans:执行阶段
两表连接查询成本计算
两表连接查询的成本=单次查询驱动表的成本+多次查询被驱动表的成本。其中单次查询驱动表的成本称为驱动表扇出(fanout);被驱动表的查询次数取决于驱动表的扇出。
单词驱动表的删除是MySQL根据一定的算法进行猜测。因为如果驱动表的扇出可以根据驱动表的where条件确定范围,是可以计算的,如果where条件无法确定范围只能猜测。
多表连接查询成本计算
再多表连接查询时候,如果使用内连接查询多个表,可作为驱动表的表很多,而且连接顺序也很多。怎么办?
- 提前结束
如果计算的成本大于前面计算过的执行计划成本,会结束运算。
- 控制连接的深度:MySQL有一个参数可以控制连接的深度,(笼统地说就是超过多少个表就不会继续计算查询成本了);
show variables like 'optimizer_search_depth';-- 默认62
- 启发式规则
mysql根据以往SQL经验计算查询成本。
成本计算参数
因为每个服务器的IO效率和CPU效率不同,可以通过调节来提高效率。这里的参数就是我们计算IO和CPU开销的成本计算参数。一般不建议调整,因为不了解MySQL计算成本的底层逻辑。
如果未设置,使用默认值。
select * from mysql.server_cost;
select * from mysql.engine_cost;