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。
  1. 分析可能用到的索引下的查询成本
    使用索引查询的成本为二级索引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. 提前结束

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

  1. 控制连接的深度:MySQL有一个参数可以控制连接的深度,(笼统地说就是超过多少个表就不会继续计算查询成本了);
show variables like 'optimizer_search_depth';-- 默认62
  1. 启发式规则

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

成本计算参数

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

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

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

  • 27
    点赞
  • 12
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值