MySQL查询成本

MySQL查询成本

MySLQ会将优化后的语句分别计算成本，取最优SQL执行。MySQL查询成本分为：

1. IO成本：将磁盘数据加载到内存的成本。在MySQL的InnoDB引擎中就是一个叶子页。默认成本1.0.
2. CPU成本：数据读取检测是否复核搜索条件。默认成本0.2.

基于成本的优化步骤

1. 根据搜索条件，分析可能用到的索引。
2. 计算全表扫描的查询成本。
3. 计算使用每个索引的查询成本。
4. 对比各种执行方案，取查询成本最低的方案。

单表查询计算成本计算

准备环境：

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+数据，其中有三个索引：

1. idx_number二级索引
2. idx_expire_time二级索引
3. 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 '%这是备注%';

分析过程：

1. 分析可能用到的索引，order_number或者expire_time。
2. 全表扫描成本分析

show TABLE status like 'product_order'; -- 查看系统文件，其中两个属性， rows（预估行数）:9907， data_length（数据大小）:1589248

全表扫描成本为：

1. IO成本：在InnoDB存储引擎中，一个叶子页的大小即一次IO，一个叶子大小为16KB，所以IO成本为：(data_length/16/1024) * 1 + 微调值(默认1.1)。
2. CPU成本：rows * 0.2 + 微调值（默认1.0）。
3. 在本SQL中的全表扫描成本为：1589248/16/1024 * 0.1 + 1.1 + 9907 * 0.2 + 1.0 = 1993.2。

3. 分析可能用到的索引下的查询成本
   使用索引查询的成本为二级索引IO成本 + 回表操作的成本。

使用expire_time索引查询成本：

1. 二级索引成本：如果使用到了索引且索引可以锁定某些范围也就是B+树子节点的数据范围，每有一个范围IO成本+1，因为o.expire_time < ‘2024-06-01’ AND o.expire_time > ‘2022-06-01’ 可以确定一个范围，所以IO成本为1；
2. 回表成本：
   1. 找到数据最左边数据，然后找到最右边的数据，这个过程成本忽略不计。
   2. 计算范围内数据量，MySQL会进行估算（此处为2721条）。 cpu成本就为2721*0.2+0.01（微调值） = 544.21
   3. 每次回表是一次IO，所以IO成本为2721*1 = 2721
   4. 得到数据后过滤除使用索引的其他条件，这里是CPU成本，2721*0.2 = 544.2
3. 使用expire_time索引查询成本为2721 + 544.21 + 544.2 = 3809.41，比全表扫描成本还高，所以不会使用这个二级索引，原因是回表的数据量高达2000+。

使用order_number索引的查询成本：

1. 二级索引成本：in条件相当于三个叶子页，所以IO成本为 3;
2. 回表成本：同上面逻辑 30.2+0.01（微调值） + 31 + 3*0.2 = 4.21
3. 总成本 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串中的参数

1. join_preparation:准备阶段，expanded_query:优化后的SQL语句。
2. join_optimization:分析阶段，original_condition:索引处理。analyzing_range_alternatives:分析索引，查看成本
3. considered_execution_plans:执行阶段

两表连接查询成本计算

两表连接查询的成本=单次查询驱动表的成本+多次查询被驱动表的成本。其中单次查询驱动表的成本称为驱动表扇出（fanout);被驱动表的查询次数取决于驱动表的扇出。

单词驱动表的删除是MySQL根据一定的算法进行猜测。因为如果驱动表的扇出可以根据驱动表的where条件确定范围，是可以计算的，如果where条件无法确定范围只能猜测。

多表连接查询成本计算

再多表连接查询时候，如果使用内连接查询多个表，可作为驱动表的表很多，而且连接顺序也很多。怎么办？

1. 提前结束

如果计算的成本大于前面计算过的执行计划成本，会结束运算。

2. 控制连接的深度：MySQL有一个参数可以控制连接的深度，（笼统地说就是超过多少个表就不会继续计算查询成本了）；

show variables like 'optimizer_search_depth';-- 默认62

3. 启发式规则

mysql根据以往SQL经验计算查询成本。

成本计算参数

因为每个服务器的IO效率和CPU效率不同，可以通过调节来提高效率。这里的参数就是我们计算IO和CPU开销的成本计算参数。一般不建议调整，因为不了解MySQL计算成本的底层逻辑。

如果未设置，使用默认值。

select * from mysql.server_cost;
select * from mysql.engine_cost;