[SQL调优] “查询SQL过滤和排序条件涉及的表字段未创建索引”引起慢查询问题，优化后执行时间从70+s下降到0.01s以下

前几天发现，线上portal “策略中心”的“证据管理”页面加载很慢。经排查发现，是由于 riskbase_core 库的 evidence 表未对 gmt_create 创建索引 和 evidence_details 表未对 refuuid 创建索引引起（因为查询条件涉及到这些字段），导致SQL执行时间要 1分钟+。
 
# 查询SQL
select d.type,d.value,e.fraud_type,e.evidence_time,e.evidence_origin,d.uuid,d.refuuid from evidence_details d inner join evidence e on d.refuuid=e.uuid order by gmt_create desc limit 0,10;

 

# 相关表结构

show create table `riskbase_core`.`evidence_details` \G
*************************** 1. row ***************************
       Table: evidence_details
Create Table: CREATE TABLE `evidence_details` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `refuuid` char(32) DEFAULT NULL,
  `type` varchar(32) NOT NULL,
  `value` varchar(100) NOT NULL,
  `partner_code` varchar(32) DEFAULT NULL,
  `app_name` varchar(32) DEFAULT NULL,
  `uuid` varchar(32) DEFAULT NULL,
  `evidence_type_displayname` varchar(32) DEFAULT NULL,
  `partnercode_display_name` varchar(32) DEFAULT NULL,
  `app_display_name` varchar(32) DEFAULT NULL,
  `evidence_time` datetime DEFAULT NULL,
  `fraud_type` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `idx_value` (`value`),
  KEY `dex_uuid` (`refuuid`)
) ENGINE=InnoDB AUTO_INCREMENT=16421615 DEFAULT CHARSET=utf8

 

show create table `riskbase_core`.`evidence` \G
*************************** 1. row ***************************
       Table: evidence
Create Table: CREATE TABLE `evidence` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `evidence_origin` varchar(32) DEFAULT NULL,
  `gmt_create` datetime DEFAULT NULL,
  `gmt_modified` datetime DEFAULT NULL,
  `uuid` char(32) NOT NULL,
  `fraud_type` varchar(32) NOT NULL,
  `evidence_time` datetime DEFAULT NULL,
  `fraud_details` text,
  `fraud_displayname` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `dex_time` (`gmt_create`)
) ENGINE=InnoDB AUTO_INCREMENT=15438435 DEFAULT CHARSET=utf8

经验教训
只要涉及到SQL查询条件（WHERE、ORDER BY）的相关字段，都应建立索引（唯一索引、联合索引）。

分析过程

1. 核实“SQL的执行时间”

select d.type,d.value,e.fraud_type,e.evidence_time,e.evidence_origin,d.uuid,d.refuuid from evidence_details d inner join evidence e on d.refuuid=e.uuid order by gmt_create desc limit 0,10;

.....

10 rows in set (1 min 11.27 sec)

哇哦，该条SQL执行时间尽然需要1分11秒，太恐怖啦！！！

 

2. 分析“该条SQL的查询执行计划”

explain select d.type,d.value,e.fraud_type,e.evidence_time,e.evidence_origin,d.uuid,d.refuuid from evidence_details d inner join evidence e on d.refuuid=e.uuid order by gmt_create desc limit 0,10;

+----+-------------+-------+--------+---------------+------+---------+-------------------------+---------+----------------------------------------------+

| id | select_type | table | type   | possible_keys | key  | key_len | ref                     | rows    | Extra                                        |

+----+-------------+-------+--------+---------------+------+---------+-------------------------+---------+----------------------------------------------+

|  1 | SIMPLE      | d     | ALL    | NULL          | NULL | NULL    | NULL                    | 6452641 | Using where; Using temporary; Using filesort |

|  1 | SIMPLE      | e     | eq_ref | uuid          | uuid | 96      | riskbase_core.d.refuuid |       1 | NULL                                         |

+----+-------------+-------+--------+---------------+------+---------+-------------------------+---------+----------------------------------------------+

从上面可以看出，查询第一步使用全表扫描（ALL），还涉及到临时表和文件排序（Using where; Using temporary; Using filesort）。所以，为了提高查询速度，尽量针对相关查询字段（`evidence_details`.`refuuid`、`evidence`.`uuid`、`evidence`.`gmt_create`）建立合理的索引。

 

3. 查看相应的索引是否创建

show index from `riskbase_core`.`evidence`;

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| evidence |          0 | PRIMARY  |            1 | id          | A         |     6471176 |     NULL | NULL   |      | BTREE      |         |               |

| evidence |          0 | uuid     |            1 | uuid        | A         |     6471176 |     NULL | NULL   |      | BTREE      |         |               |

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

从上面看，`evidence`.`uuid` 字段的索引已创建。还需要创建`evidence`.`gmt_create`字段的索引，创建语句如下：

CREATE INDEX `dex_time` on `riskbase_core`.`evidence` (`gmt_create`);

 

show index from `riskbase_core`.`evidence_details`;

+------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table            | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| evidence_details |          0 | PRIMARY   |            1 | id          | A         |     6452885 |     NULL | NULL   |      | BTREE      |         |               |

| evidence_details |          0 | uuid      |            1 | uuid        | A         |     6452885 |     NULL | NULL   | YES  | BTREE      |         |               |

| evidence_details |          1 | idx_value |            1 | value       | A         |     6452885 |     NULL | NULL   |      | BTREE      |         |               |

+------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

从上面看，未对`evidence_details`.`refuuid`字段创建索引。所以，需要对`evidence_details`.`refuuid`字段创建索引，创建语句如下：

CREATE INDEX `dex_uuid` on `riskbase_core`.`evidence_details` (`refuuid`);

 

4. 再次查看“该SQL的查询执行计划”

explain select d.type,d.value,e.fraud_type,e.evidence_time,e.evidence_origin,d.uuid,d.refuuid from evidence_details d inner join evidence e on d.refuuid=e.uuid order by gmt_create desc limit 0,10;                           +----+-------------+-------+-------+---------------+----------+---------+----------------------+------+-------+

| id | select_type | table | type  | possible_keys | key      | key_len | ref                  | rows | Extra |

+----+-------------+-------+-------+---------------+----------+---------+----------------------+------+-------+

|  1 | SIMPLE      | e     | index | uuid          | dex_time | 6       | NULL                 |   10 | NULL  |

|  1 | SIMPLE      | d     | ref   | dex_uuid      | dex_uuid | 97      | riskbase_core.e.uuid |    1 | NULL  |

+----+-------------+-------+-------+---------------+----------+---------+----------------------+------+-------+

从SQL查询执行计划看，相关索引都使用上了，看起来应该没问题了。现在就用SQL语句测试一下执行时间吧

 

5. 再次执行该SQL，用“执行时间”来验证优化效果

select d.type,d.value,e.fraud_type,e.evidence_time,e.evidence_origin,d.uuid,d.refuuid from evidence_details d inner join evidence e on d.refuuid=e.uuid order by gmt_create desc limit 0,10;

......

10 rows in set (0.00 sec)

好了，从“执行时间”看，问题已彻底修复了。

 

6. 看一下优化前后，页面的加载效果

# 优化前 57.41s

 

 # 优化后 1.79s

 

页面加载时间，从优化前的 57.41s 降低到 1.79s。

但可能细心的朋友已经发现，优化后 evidenceManage.json 的响应时间还需要 1.677s，而上面那条SQL的执行时间已经降低到10ms之内。那其它的时间都消耗在哪里啦？

 

其它故事

其实 evidenceManage.json 接口除了执行上面那条SQL语句，还执行了一条count(*)的SQL语句。而正是count(*) SQL语句耗时了 1.67s，哦哦，原来这里还有其它故事啊～～～

 

# 总行数统计

select count(*) from evidence_details

 

但通过对“count(*) SQL语句”的查询执行计划的分析，发现对于InnoDB引擎（14.2 InnoDB Concepts and Architecture），很难对此再优化了。

（建议：有count(*)相关的操作，数据表的存储引擎（ENGINE）尽量设计为MyISAM（15.2 The MyISAM Storage Engine），除非该表涉及事务操作！）

 

总结

针对 InnoDB 存储引擎：

• 索引（index）查询类型的查询要快于范围（range）查询类型
• 二级索引（dex_uuid）类型的查询要快于主键索引（PRIMARY）类型

 

参考

[MySQL FAQ系列] 为何 InnoDB 表 select count(*) 很慢 -- 叶金荣(yejr)

[InnoDB系列] InnoDB 表如何更快得到 count(*) 结果 -- 叶金荣(yejr)

 

分析过程

mysql> explain select count(*) from evidence_details \G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: evidence_details

         type: index

possible_keys: NULL

          key: dex_uuid

      key_len: 97

          ref: NULL

         rows: 6479241

        Extra: Using index

1 row in set (0.00 sec)

 

mysql> select count(*) from evidence_details \G

*************************** 1. row ***************************

count(*): 7640484

1 row in set (1.67 sec)

 

mysql> explain select count(*) from evidence_details where id >= 0 \G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: evidence_details

         type: range

possible_keys: PRIMARY

          key: PRIMARY

      key_len: 8

          ref: NULL

         rows: 3239629

        Extra: Using where; Using index

1 row in set (0.00 sec)

 

mysql> select count(*) from evidence_details where id >= 0 \G

*************************** 1. row ***************************

count(*): 7640505

1 row in set (2.51 sec)

 

mysql> explain select count(id) from evidence_details \G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: evidence_details

         type: index

possible_keys: NULL

          key: dex_uuid

      key_len: 97

          ref: NULL

         rows: 6479287

        Extra: Using index

1 row in set (0.00 sec)

 

mysql> select count(id) from evidence_details \G

*************************** 1. row ***************************

count(id): 7640530

1 row in set (1.83 sec)

 

mysql> explain select count(id) from evidence_details where id >= 0 \G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: evidence_details

         type: range

possible_keys: PRIMARY

          key: PRIMARY

      key_len: 8

          ref: NULL

         rows: 3239652

        Extra: Using where; Using index

1 row in set (0.00 sec)

 

mysql> select count(id) from evidence_details where id >= 0 \G

*************************** 1. row ***************************

count(id): 7640547

1 row in set (2.64 sec)

 

mysql> explain select count(`uuid`) from evidence_details \G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: evidence_details

         type: index

possible_keys: NULL

          key: uuid

      key_len: 99

          ref: NULL

         rows: 6479323

        Extra: Using index

1 row in set (0.00 sec)

 

mysql> select count(`uuid`) from evidence_details \G

*************************** 1. row ***************************

count(`uuid`): 7640564

1 row in set (2.20 sec)

 

mysql> explain select count(`uuid`) from evidence_details where id >= 0 \G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: evidence_details

         type: range

possible_keys: PRIMARY

          key: PRIMARY

      key_len: 8

          ref: NULL

         rows: 3239705

        Extra: Using where

1 row in set (0.00 sec)

 

mysql> select count(`uuid`) from evidence_details where id >= 0 \G

*************************** 1. row ***************************

count(`uuid`): 7640657

1 row in set (3.13 sec)

 

mysql> explain select count(`refuuid`) from evidence_details \G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: evidence_details

         type: index

possible_keys: NULL

          key: dex_uuid

      key_len: 97

          ref: NULL

         rows: 6479554

        Extra: Using index

1 row in set (0.00 sec)

 

mysql> select count(`refuuid`) from evidence_details \G

*************************** 1. row ***************************

count(`refuuid`): 7640812

1 row in set (2.09 sec)

 

mysql> explain select count(`refuuid`) from evidence_details where id >= 0 \G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: evidence_details

         type: range

possible_keys: PRIMARY

          key: PRIMARY

      key_len: 8

          ref: NULL

         rows: 3239794

        Extra: Using where

1 row in set (0.00 sec)

 

mysql> select count(`refuuid`) from evidence_details where id >= 0 \G

*************************** 1. row ***************************

count(`refuuid`): 7640852

 

1 row in set (3.31 sec)