腾讯数据库诊断大赛题目回顾与分析

发布时间：2020-07-21 12:37:33 来源：网络阅读：3493 作者：暮回_zz 栏目：MySQL数据库

只是从个人角度分析了下这次比赛的题目，涉及到一些个人经验，学习交流而已～

贴上官方的git链接：
https://github.com/DBbrain/Diagnosis

初赛

data

order 表的数据量 2000
CREATE TABLE `order` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar ( 32) COLLATE utf8_bin NOT NULL,
  `creator` varchar(24) COLLATE utf8_bin NOT NULL,
  `price` varchar(64) COLLATE utf8_bin NOT NULL,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
);

order_item 表的数据量 499760
CREATE TABLE `order_item` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) COLLATE utf8_bin NOT NULL,
  `parent` bigint(20) NOT NULL,
  `status` int(11) NOT NULL,
  `type` varchar(12) COLLATE utf8_bin NOT NULL DEFAULT '0',
  `quantity` int(11) NOT NULL DEFAULT '1',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
);

1. select

分析

SELECT * FROM `order` o INNER JOIN order_item i  ON i.parent = o.id 
        ORDER BY o.status ASC, i.update_time DESC LIMIT  0, 20;

mysql> explain SELECT * FROM `order` o INNER JOIN order_item i 
    ->     ON i.parent = o.id  ORDER BY o.status ASC, i.update_time DESC LIMIT  0, 20;
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------------------+--------+----------+---------------------------------+
| id      | select_type | table   | partitions   | type     | possible_keys | key        | key_len   | ref                                         | rows      | filtered    | Extra                                  |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------------------+--------+----------+---------------------------------+
|  1 | SIMPLE      | i     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                            | 497839 |   100.00 | Using temporary; Using filesort |
|  1 | SIMPLE      | o     | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | sql_optimization_match.i.parent |      1 |   100.00 | NULL                            |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------------------+--------+----------+---------------------------------+

两张表 inner join，根据执行计划，驱动表选择 order_item ，同时由于排序字段来自两张表，且方向不一致，造成了写临时表，由于排序字段不能使用到索引，因此造成了外排序。

分析 join 字段

    order_item.parent = order.id

mysql> select count(distinct(parent)) from order_item;  // 区分度较低，忽略
+-------------------------+
| count(distinct(parent)) |
+-------------------------+
|                     300     |
+-------------------------+
mysql> select count(distinct(id)) from `order`; // 区分度较高，但 id 上已有主键索引
+---------------------+
| count(distinct(id))  |
+---------------------+
|                2000    |
+---------------------+
1 row in set (0.00 sec)

order_item.parent 没有索引；
order.id 有索引。

分析聚合字段

ORDER BY o.status ASC, i.update_time DESC

mysql> select count(distinct(status)) from `order`; //区分度很低
+-------------------------+
| count(distinct(status)) |
+-------------------------+
|                       2 |
+-------------------------+
mysql> select count(distinct(update_time)) from `order_item`;   // 区分度一般
+------------------------------+
| count(distinct(update_time)) |
+------------------------------+
|                        32768 |
+------------------------------+

两张表的 order by 字段排序方式不一样，可能需要用到外排序，同时原表在排序字段上没有索引。

优化

根据之前的查询计划可以看到，是对 order_item 进行了一次全表扫，之后再进行外排序。由于 sql 语义中需要对两列进行排序，因此，可以通过其他的方式，减少外排序的数据量，从而降低时耗。

order 表中的排序字段 status 仅有两个不同值，尝试去掉 status 排序字段之后，速度明显提高，此时，order by 中的 update_time 字段可以尝试增加索引，区分度也满足要求；

status 仅有两列，可以使用 union all 来代替，避免 order by 中不同的表不同的排序顺序导致无法使用索引。

这里是官方给出的建议（应该不是ML自动改写的），不过这种改写sql的方式有一定局限性，适用场景受限，如果 status 类型不是tinyint(1)，且以后如果会增加新的类型，sql需要不断改写。

可以尝试推动业务改造，在重新优化索引。

另外，sql改写之后，给出的索引建议是增加联合索引（update_time,parent），上面分析可以看到，parent的区分度较低，这里增加联合索引或者只给 update_time 增加索引，性能相差不多。

# sql 改写
SELECT o.*,i.* FROM  (
    ( SELECT o.id, i.id item_id FROM  `order_1` o 
            INNER JOIN order_item i ON i.parent =o.id
            WHERE  o.status = 0
            ORDER  BY i.update_time DESC LIMIT  0, 20)
    UNION ALL
    (SELECT o.id, i.id item_id FROM  `order_1` o
            INNER JOIN order_item i ON i.parent =o.id
            WHERE  o.status = 1
            ORDER  BY i.update_time DESC LIMIT  0, 20)
    ) tmp
    INNER JOIN `order_1` o ON tmp.id = o.id
    INNER JOIN order_item i ON tmp.item_id = i.id
    ORDER  BY o.status ASC,
    i.update_time DESC
    LIMIT  0, 20

# 增加索引
alter table order_item add index `item_idx_1` (`update_time`,`parent`);

# 执行计划
+----+-------------+------------+------------+--------+---------------+------------+---------+---------------------------------+------+----------+---------------------------------+order_item i ON tmp.ite 
| id | select_type | table      | partitions | type   | possible_keys | key        | key_len | ref                             | rows | filtered | Extra                           |
+----+-------------+------------+------------+--------+---------------+------------+---------+---------------------------------+------+----------+---------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL          | NULL       | NULL    | NULL                            |   40 |   100.00 | Using temporary; Using filesort |
|  1 | PRIMARY     | o          | NULL       | eq_ref | PRIMARY       | PRIMARY    | 8       | tmp.id                          |    1 |   100.00 | NULL                            |
|  1 | PRIMARY     | i          | NULL       | eq_ref | PRIMARY       | PRIMARY    | 8       | tmp.item_id                     |    1 |   100.00 | NULL                            |
|  2 | DERIVED     | i          | NULL       | index  | NULL          | item_idx_1 | 12      | NULL                            |   20 |   100.00 | Using index                     |
|  2 | DERIVED     | o          | NULL       | eq_ref | PRIMARY       | PRIMARY    | 8       | sql_optimization_match.i.parent |    1 |    10.00 | Using where                     |
|  3 | UNION       | i          | NULL       | index  | NULL          | item_idx_1 | 12      | NULL                            |   20 |   100.00 | Using index                     |
|  3 | UNION       | o          | NULL       | eq_ref | PRIMARY       | PRIMARY    | 8       | sql_optimization_match.i.parent |    1 |    10.00 | Using where                     |
+----+-------------+------------+------------+--------+---------------+------------+---------+---------------------------------+------+----------+---------------------------------+

总结

1. 对区分度极低的字段如果有排序、范围比较等操作，可以转换为 union all；
2. 对排序字段，尝试使用索引避免 filesort，如果不可避免，在 filesort 之前尝试减少排序的数据量；

2. update

分析

update `order` set create_time = now()
    where id in (select parent from order_item where type = 2 );

# 执行计划
mysql> explain update `order_1` set create_time = now() where id in (select parent from order_item where type = 2 );
+----+--------------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type        | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+--------------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | UPDATE             | order_1    | NULL       | index | NULL          | PRIMARY | 8       | NULL |   2000 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | order_item | NULL       | ALL   | NULL          | NULL    | NULL    | NULL | 496836 |     1.00 | Using where |
+----+--------------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+

update 的条件是 in 子查询的方式，explain 中注意到 select type 为 DEPENDENT SUBQUERY ，表示先做外查询，外查询匹配到的行数为N，那么接下来会进行N次子查询，效率极低。

对子查询通常的做法是转换为连表查询 join。

优化

1.最简单的子查询->join 的优化操作

update `order` o  inner join (select parent from `order_item` where type = 2) tmp on o.id = tmp.parent  set create_time = now();

mysql> explain update `order` o  inner join (select parent from `order_item` where type = 2) tmp on o.id = tmp.parent  set create_time = now() \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: order_item
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 497839
     filtered: 10.00
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: UPDATE
        table: o
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: sql_optimization_match.order_item.parent
         rows: 1
     filtered: 100.00
        Extra: NULL
2 rows in set (0.00 sec)

转换后的速度相比之前已经快了很多，没有了dependence subquery，不过还是秒级，驱动表选择了 order_item 表，但是基本是一次全表扫，意味着要使用 49w 行的数据和 order 表进行 join，开销还是很大。

看最原始的慢update，修改的只有 order 表，条件的话只需要 id 在对 order_item 的子查询范围内即可，重复的 parent 对于 update 毫无意义，因此，可以对parent字段进行一次聚合（group by），由于子查询中有 order_item.type = 2 的条件，因此，可以对 type 字段同时进行聚合。

由于 order_item 仅有主键索引，对 order_item 表的等值判断条件和聚合操作使用索引最佳，因此，可以建立联合索引，索引顺序优先等值操作。

额外注意一点，我们要创建索引，索引字段类型和 sql 中的等值类型是否一致。

1. 优化连表查询

增加索引：
    alter table `order_item` add index idx_1(type,parent);

sql 优化：
    update `order` o inner join (
        select parent from `order_item` 
                where type = '2' group by type, parent) i 
        on o.id = i.parent set create_time = now();

mysql> explain update `order` o inner join (    select  parent from `order_item` where type = '2' group by type, parent ) i on o.id = i.parent set create_time = now()\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 571
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: UPDATE
        table: o
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: i.parent
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: order_item
   partitions: NULL
         type: range
possible_keys: idx_1
          key: idx_1
      key_len: 46
          ref: NULL
         rows: 571
     filtered: 100.00
        Extra: Using where; Using index for group-by

优化之后，使用了 order_item 表做了驱动表，同时这里也使用到了上面建立的索引 idx_1，之后生成的临时表和 order 表进行 join。由于 group by 的原因，order_item 生成的结果集数量更少，因此被选为了驱动表。

另外需要注意的是在 group by 这里使用了两列，这个是为了使用 idx_1 索引（尽管 groiup by parent 和 group by type,parent 的返回结果行数都是一样的，但是执行计划还是有很大差距）

优化之后的执行时间在毫秒级。

总结

1. 驱动表的选择，始终是小表驱动大表，驱动表会走全表扫，所以通常索引都是在被驱动表上增加；
2. 如果执行计划中出现了 DEPENDENT SUBQUERY，一定会对 sql 的执行效率有影响（同时 DEPENDENT SUBQUERY 还会潜在地造成一定程度的锁放大）， in + 子查询 方式很容易引起，可以将子查询优化为 join 操作；
3. 对于 join 连表查询，进行连表的数据越少，执行效率就越高，因此，在不改变sql语义的前提下，尽量使参加 join 的数据量减少;
4. 关于索引顺序： 等值条件 > group by  > order by
5. 注意索引字段类型和 sql 中的的判断条件中的数据类型是否一致。

决赛

data

区分度的计算过程省略，这里直接给出区分度好坏

由于某些表的行数较多，区分度的计算使用的是统计前5000行中 distinct 的值（生成环境中也可以这样做，可以降低计算区分度带来的额外开销），极端情况下部分小表可能会造成误判，但行数极少的表加索引的意义也不是很大。

# customer 数据量 1,200,000
CREATE TABLE `customer` (
  `custkey` int(11) NOT NULL,       // 区分度 OK
  `name` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,  // 区分度 OK
  `address` varchar(40) NOT NULL,
  `nationkey` int(11) NOT NULL,     // 区分度较低
  `phone` char(15) NOT NULL,        // 区分度 OK
  `acctbal` decimal(15,2) NOT NULL,
  `mktsegment` char(10) NOT NULL,   // 区分度较低
  `comment` varchar(117) NOT NULL,
  PRIMARY KEY (`custkey`),
  KEY `idx_nationkey` (`nationkey`)
);

# nation 数据量 25
CREATE TABLE `nation` (
  `nationkey` int(11) NOT NULL,     // 区分度 OK
  `name` char(25) NOT NULL,
  `regionkey` int(11) NOT NULL,
  `comment` varchar(152) DEFAULT NULL,
  PRIMARY KEY (`nationkey`),
  KEY `idx_4_0` (`name`)
);

# orders 数据量12,000,000
CREATE TABLE `orders` (
  `orderkey` int(11) NOT NULL,
  `custkey` int(11) NOT NULL,       // 区分度 OK
  `orderstatus` varchar(1) NOT NULL,
  `totalprice` decimal(15,2) NOT NULL,      // 区分度 OK
  `orderdate` date NOT NULL,
  `orderpriority` char(15) NOT NULL,
  `clerk` char(15) NOT NULL,                // 区分度 OK
  `shippriority` int(11) NOT NULL,
  `comment` varchar(79) NOT NULL,
  PRIMARY KEY (`orderkey`)
);

# region 数据量 5
CREATE TABLE `region` (
  `regionkey` int(11) NOT NULL,
  `name` varchar(25) NOT NULL,
  `comment` varchar(152) DEFAULT NULL,
  PRIMARY KEY (`regionkey`)
);

1. select

分析

select c.custkey, c.phone, sum(o.totalprice) totalprice
    from nation n 
    inner join customer c on c.nationkey = n.nationkey
    inner join orders o on o.clerk = c.name
    where n.name = "CHINA" and c.mktsegment = "HOUSEHOLD" and c.phone like "28-520%"
    group by c.custkey, c.phone;

# 执行计划
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------+
|  1 | SIMPLE      | n     | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |       25 |    10.00 | Using where; Using temporary; Using filesort       |
|  1 | SIMPLE      | c     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  1189853 |     0.11 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | o     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10963843 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------+

三张表【customer c】；【nation n】；【orders o】

customer

    where条件：
             c.mktsegment = "HOUSEHOLD"：区分度较低，放弃
        ✔  c.phone like "28-520%"：区分度较好，考虑添加索引
    聚合条件：
             group by c.custkey： 区分度较好，但是已经是主键，放弃
        ✔  c.phone：同where，考虑添加
    join 条件：
            c.nationkey = n.nationkey：区分度较低，放弃
        ✔ o.clerk = c.name：区分度较高，考虑添加索引

    advice：
        add index `dx_1_0`(name);
        add index `idx_1_1` (phone);

nation

    数据量 25，不考虑添加索引

    nation 表可以考虑增加索引 add index `idx_1_0`(name);  但意义不大

orders

    join 条件：
        ✔ o.clerk = c.name：区分度较高，考虑添加索引

    advice：
        add index `idx_1_0` (clerk)

优化

按上述分析增加三条索引后，执行计划如下

+----+-------------+-------+------------+--------+----------------+---------+---------+-------------------+----------+----------+---------------------------------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys  | key     | key_len | ref               | rows     | filtered | Extra                                                               |
+----+-------------+-------+------------+--------+----------------+---------+---------+-------------------+----------+----------+---------------------------------------------------------------------+
|  1 | SIMPLE      | c     | NULL       | range  | dx_1_0,idx_1_1 | idx_1_1 | 45      | NULL              |       46 |    10.00 | Using index condition; Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | n     | NULL       | eq_ref | PRIMARY        | PRIMARY | 4       | dbaas.c.nationkey |        1 |    10.00 | Using where                                                         |
|  1 | SIMPLE      | o     | NULL       | ALL    | idx_1_0        | NULL    | NULL    | NULL              | 10963843 |    10.00 | Range checked for each record (index map: 0x2)                      |
+----+-------------+-------+------------+--------+----------------+---------+---------+-------------------+----------+----------+---------------------------------------------------------------------+

总结

1. 在 inner join 的情况下，我们无法判断出驱动表，因此，我们会选择在合适的字段上都添加索引；
2. 在 sql 中的条件类型较多时，选择把等值条件和聚合条件添加为组合索引，join 条件单独增加索引；
3. 如果数据量过少，增加索引意义不大，可以不考虑；
4. dbrain 给出的是组合索引，两者相比，性能基本一致；

2. select

分析

select * from (
    select custkey, orderdate, sum(totalprice) as totalprice
        from orders group by custkey, orderdate
    ) o
    where orderdate = "2019-08-01"

# 执行计划
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+----------+----------+---------------------------------+
| id | select_type | table      | partitions | type | possible_keys | key         | key_len | ref   | rows     | filtered | Extra                           |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+----------+----------+---------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ref  | <auto_key0>   | <auto_key0> | 3       | const |       10 |   100.00 | NULL                            |
|  2 | DERIVED     | orders     | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  | 10963843 |   100.00 | Using temporary; Using filesort |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+----------+----------+---------------------------------+

仅涉及到一张表，group by 用到了 filesort，sql 看上去并不复杂，但是却产生了驱动表。

查看sql，发现select * from (子查询)，多余的嵌套，可以考虑去掉，sql 可以改写为

 select custkey, orderdate, sum(totalprice) as totalprice
        from orders where orderdate = "2019-08-01" group by custkey, orderdate;

索引分析

    where 条件：
        ✔ orderdate = "2019-08-01"：区分度较高，考虑增加索引

    聚合条件：
        ✔  group by custkey, orderdate：两个字段区分区都较高，考虑增加索引

    advice：
        等值条件 优先于 聚合条件
        add index `idx_2_0` (orderdate, custkey)

优化

使用优化后的 sql，增加联合索引，执行计划为
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | orders | NULL       | ref  | idx_2_0       | idx_2_0 | 3       | const |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+

如果是增加两个单独的索引，
    add index `idx_2_1` (custkey);
    add index `idx_2_2` (orderdate);
执行计划为
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+--------------------------------------------------------+
| id | select_type | table  | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra                                                  |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+--------------------------------------------------------+
|  1 | SIMPLE      | orders | NULL       | ref  | idx_2_2       | idx_2_2 | 3       | const |    1 |   100.00 | Using index condition; Using temporary; Using filesort |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+--------------------------------------------------------+

使用到了 filesort，只有custkey可以使用索引，因此建议联合索引。

结论

1. 单张表的sql如果执行计划出现 filesort 等需要关注，频繁嵌套的子查询，会对性能有一定影响，可以考虑 sql 重写；
2. 关于加索引，等值条件要优先于聚合、join等条件；

3. select

分析

select c.custkey, sum(o.totalprice) totalprice from customer c
        left join orders o on o.custkey = c.custkey
        where c.phone like "33-64%" and c.name like concat("Customer#00003", "%")
        group by c.custkey

在已经有前两条 sql 增加的索引前提下，执行计划为
+----+-------------+-------+------------+-------+------------------------+---------+---------+------+----------+----------+---------------------------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys          | key     | key_len | ref  | rows     | filtered | Extra                                                               |
+----+-------------+-------+------------+-------+------------------------+---------+---------+------+----------+----------+---------------------------------------------------------------------+
|  1 | SIMPLE      | c     | NULL       | range | PRIMARY,dx_1_0,idx_1_1 | idx_1_1 | 45      | NULL |      552 |     1.63 | Using index condition; Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | o     | NULL       | ALL   | NULL                   | NULL    | NULL    | NULL | 10963843 |   100.00 | Using where; Using join buffer (Block Nested Loop)                  |
+----+-------------+-------+------------+-------+------------------------+---------+---------+------+----------+----------+---------------------------------------------------------------------+

customer 表已经使用到了索引，是否需要增加其它索引稍后分析；
order 表是走了全表扫，扫描 12,000,000 行数据，可能缺少索引；

两张表【customer c】；【order o】

customer

where条件：
         c.phone like "33-64%"：第一条 select 已经添加过索引
         c.name like concat("Customer#00003", "%")：第一条 select 已经添加过索引
聚合条件：
         group by c.custkey： 区分度较好，但是已经是主键，放弃
join 条件：
         o.custkey = c.custkey：区分度较好，但是已经是主键，放弃

advice：
    无建议

order

join 条件：
        o.custkey = c.custkey：区分度较高，考虑增加索引

advice：
        add index `idx_3_0` (custkey)

优化

增加索引之后的执行计划为：

+----+-------------+-------+------------+-------+------------------------+---------+---------+-----------------+------+----------+---------------------------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys          | key     | key_len | ref             | rows | filtered | Extra                                                               |
+----+-------------+-------+------------+-------+------------------------+---------+---------+-----------------+------+----------+---------------------------------------------------------------------+
|  1 | SIMPLE      | c     | NULL       | range | PRIMARY,dx_1_0,idx_1_1 | idx_1_1 | 45      | NULL            |  552 |     1.63 | Using index condition; Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | o     | NULL       | ref   | idx_3_0                | idx_3_0 | 4       | dbaas.c.custkey |   13 |   100.00 | NULL                                                                |
+----+-------------+-------+------------+-------+------------------------+---------+---------+-----------------+------+----------+---------------------------------------------------------------------+

增加索引之后。扫描 order 表的行数已经大大减少，执行效率也提升很高

总结

1. 关于 【using where】, 【using index】, 【using index condition】; 【Using where &&Using index】的区别（为什么总结这个呢，我这边的话是建立了两个库，数据和基本的表结构是一致的，但是其中一个库中表的索引是按照我自己分析的情况加上去的，另一个库是官方给出的建议，发现在执行效率都很高的情况下，两者执行计划的 extra 内容有所区别，本想 google 解决，但是看了排名前三的博客，两篇的内容是一样的，和第三篇的解释完全不同，自己尝试了下，这里给出结论，最后附上测试流程）：

4. select

分析

select c.custkey, c.phone from nation n
        inner join customer c on c.nationkey = n.nationkey
        where n.name = "CHINA" and exists (
            select 1 from orders o where o.custkey = c.custkey and o.orderdate = "1998-08-11");

在上面已有的索引前提下，执行计划为

+----+--------------------+-------+------------+------+-----------------+---------+---------+-----------------------+---------+----------+----------------------------------------------------+
| id | select_type        | table | partitions | type | possible_keys   | key     | key_len | ref                   | rows    | filtered | Extra                                              |
+----+--------------------+-------+------------+------+-----------------+---------+---------+-----------------------+---------+----------+----------------------------------------------------+
|  1 | PRIMARY            | n     | NULL       | ref  | PRIMARY,idx_1_0 | idx_1_0 | 75      | const                 |       1 |   100.00 | Using index                                        |
|  1 | PRIMARY            | c     | NULL       | ALL  | NULL            | NULL    | NULL    | NULL                  | 1189853 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
|  2 | DEPENDENT SUBQUERY | o     | NULL       | ref  | idx_2_0,idx_3_0 | idx_2_0 | 7       | const,dbaas.c.custkey |       1 |   100.00 | Using index                                        |
+----+--------------------+-------+------------+------+-----------------+---------+---------+-----------------------+---------+----------+----------------------------------------------------+

看到了 DEPENDENT SUBQUERY，在 in/exists +子查询的条件下。经常会出现，有什么危害上面有解释，出现了这个东西，就要想办法改写 sql。既然是 exists + 子查询，那么优化策略就是改写为 join。

最通俗的改写方式：先全部 inner join，最后加 where 条件

    select c.custkey, c.phone from nation n
        inner join customer c on c.nationkey = n.nationkey
        inner join orders o on o.custkey = c.custkey
      where n.name = "CHINA" and o.orderdate = "1998-08-11";

官方给出的 sql 比较复杂，但做的事情差不多，多考虑了一点试图使用 group by 来减少 join 的数据量，给出官方答案，这里不多解释它【不过这里去掉 group by 会更好】

SELECT `t1`.`custkey`, `t1`.`phone` FROM 
        ( SELECT * FROM `dbaas`.`nation` AS `t` WHERE `t`.`name` = 'CHINA' ) AS `t0`
    INNER JOIN `dbaas`.`customer` AS `t1` 
        ON `t0`.`nationkey` = `t1`.`nationkey`
    INNER JOIN (
        SELECT `t2`.`custkey` FROM `dbaas`.`orders` AS `t2` 
            WHERE `t2`.`orderdate` = '1998-08-11' GROUP BY `t2`.`custkey` ) AS `t5` 
        ON `t1`.`custkey` = `t5`.`custkey`

索引建议的话这里就没有太多了，条件字段已经都有了相应的索引。

优化

优化后的执行计划如下：

+----+-------------+-------+------------+--------+-----------------+---------+---------+-----------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys   | key     | key_len | ref             | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+-----------------+---------+---------+-----------------+------+----------+-------------+
|  1 | SIMPLE      | n     | NULL       | ref    | PRIMARY,idx_1_0 | idx_1_0 | 75      | const           |    1 |   100.00 | Using index |
|  1 | SIMPLE      | o     | NULL       | ref    | idx_2_0,idx_3_0 | idx_2_0 | 3       | const           |   20 |   100.00 | Using index |
|  1 | SIMPLE      | c     | NULL       | eq_ref | PRIMARY         | PRIMARY | 4       | dbaas.o.custkey |    1 |    10.00 | Using where |
+----+-------------+-------+------------+--------+-----------------+---------+---------+-----------------+------+----------+-------------+

可以看到两次join的驱动表分别选择了n和o，ref 也是 const，性能要比 DEPENDENT SUBQUERY 这种要好太多了

总结

1. 并不是所有的复杂 join 都要使用 group by，和数据分布有关，如果 group by 并不能显著降低 join 行数的话， 没有必要；

胡思乱想

mysql 的查询优化器相对来说是一个比较复杂的逻辑，期待它可以更好工作的前提是sql的写法要合理，同时也要有恰当的索引。

我们对 sql 的优化，通常是先去考虑优化sql，再根据优化后的 sql 增加所需索引。（在实际数据库开发过程中，尤其是 2B 的服务提供端，我们会优先在不需要业务改动的情况下增加所需索引尝试解决慢查询的问题，如果增加索引不能解决问题，那么就需要业务进行相应改造）

首先，关于sql改写，这个要考虑的比较多，因为mysql的优化器、执行器做了太多的事情，靠AI可以自动改写优化的的话，不敢想象。。。（DBA又要有一波人下岗了）人工的话根据经验吧，根据执行计划中的异常点去考虑改进，比如子查询改写为 join 等，像预赛题目中关于order by status 改写为 union all 的做法，确实有一定的效果，但是并不是一个通用的方法，这里就太灵活了；
其次，相比之下，在已知sql和表结构的情况下，依靠AI给出索引建议还是更加让人有真实感，索引的话有一些通用的规则，网上介绍的很多了，自己瞎写了点，看看就好

1. 找出所有条件字段，计算字段区分度，区分度很低的字段没有必要加索引，数据量很少的字段一样，加上意义也不大；
2. 条件的话优先级  等值 > 聚合（group/order by） > join ，同一优先级根据区分度建立联合索引；
3. 在聚合条件下，如果聚合后的行数太多，回表量太大的情况下，mysql 可能不会使用这些索引；
4. 驱动表不需要考虑什么索引，驱动表的数据必然都在join的结果集中，对于 inner join 这种无法确定驱动表的，可以考虑在两张表的合适字段上都添加索引

继续扯。。。

现在大家都在往云上迁业务，云上的db智能诊断难以避免是以后的刚需。AI 可以做到哪一步，不清楚，以后的事情谁知道呢。

向AI问一下细节

腾讯数据库诊断大赛题目回顾与分析

初赛

data

1. select

分析

优化

总结

2. update

分析

优化

总结

决赛

data

1. select

分析

优化

总结

2. select

分析

优化

结论

3. select

分析

优化

总结

4. select

分析

优化

总结

胡思乱想

猜你喜欢

最新资讯

相关推荐

相关标签