`

[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)尽量设计为MyISAM15.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)

  

  • 大小: 203.6 KB
  • 大小: 200.3 KB
0
0
分享到:
评论

相关推荐

    ORACLE执行计划和SQL调优

    SQL调优涉及分析和改进SQL语句的执行效率,包括优化查询结构、选择合适的索引、调整表结构、使用绑定变量、避免全表扫描等策略。理解执行计划可以帮助识别性能瓶颈,通过调整执行路径、选择最佳访问方法和减少数据...

    sql调优 sql调优

    在数据库管理领域,SQL(Structured Query Language)调优是一项至关重要的技能,它涉及到优化数据库查询性能,提升系统响应速度,以及降低服务器资源消耗。SQL调优对于任何处理大量数据的应用程序来说都是不可或缺...

    Oracle Sql性能调优.ppt

    Oracle SQL 性能调优是一个非常重要的课题,它涉及到 Oracle 数据库的性能优化,包括 SQL 语句优化、索引使用优化、多表关联查询操作优化、SQL 执行计划优化等。下面是Oracle SQL 性能调优的详细知识点: 1. Oracle...

    ORACLE 19C SQL调优指南 中文版 Oracle DBA

    Oracle 19C SQL调优是数据库管理员(DBA)日常工作中不可或缺的一部分,它涉及到优化SQL查询性能,提高数据库系统的整体效率。本指南针对Oracle 19C版本,提供了丰富的调优策略和技术,旨在帮助DBA们更好地管理和维护...

    SQL调优与案例

    SQL调优涉及到多个方面,包括查询优化、索引优化、存储优化等,旨在提高数据检索速度,减少资源消耗,提升整体应用性能。本篇文章将围绕“SQL调优与案例”这一主题,结合MySQL性能调优,深入探讨相关知识点。 1. **...

    ORACLE执行计划和SQL调优.pptx

    Oracle执行计划和SQL调优是数据库管理中至关重要的主题,主要涉及如何优化SQL查询以提高数据库性能。在Oracle数据库系统中,SQL语句的执行效率直接影响到整个应用程序的响应速度和资源消耗。以下是对相关概念的详细...

    DB2 SQL性能调优秘笈

    通过细致地分析SQL语句执行计划和实际执行情况,可以发现潜在的问题并采取相应的优化措施。具体步骤包括: 1. **使用EXPLAIN功能**:DB2提供了强大的EXPLAIN工具,用于展示SQL语句的执行计划。这有助于理解查询如何...

    高清完整版 Oracle 高性能SQL引擎剖析SQL优化与调优机制详解

    Oracle的SQL优化与调优机制复杂多样,涵盖了从SQL语句的编写、执行计划的选择、到资源管理的全方位调优方法。为了深入理解Oracle SQL优化与调优机制,需要掌握以下几个核心知识点。 首先,要掌握Oracle SQL的基本...

    ORACLE执行计划和SQL调优.ppt

    ORACLE执行计划和SQL调优 ORACLE执行计划是指数据库管理系统在执行SQL语句时,数据库管理系统如何选择执行计划,以便高效地执行SQL语句。执行计划是指数据库管理系统在执行SQL语句时,选择的访问路径和执行顺序。...

    SQL optimizer and rewriter. - SQL 优化器、重写器(辅助 SQL 调优)

    - SQL 优化器、重写器(辅助 SQL 调优)。SQL optimizer and rewriter. - SQL 优化器、重写器(辅助 SQL 调优)。SQL optimizer and rewriter. - SQL 优化器、重写器(辅助 SQL 调优)。SQL optimizer and rewriter. - ...

    DB2性能调优资料,解决SQL执行慢的问题

    针对SQL执行慢的问题,本资料集合了DB2存储过程调优的方法和实例,旨在提供解决策略。 首先,我们要了解REORG 和 REORGCHK以及RUNSTATS这三种DB2命令,它们在数据库维护中起着至关重要的作用。 1. **REORG**: 这个...

    Oracle 高性能SQL引擎剖析:SQL优化与调优机制详解

    重点讲解执行计划在SQL语句执行的生命周期中所处的位置和作用,SQL引擎如何生成执行计划以及如何获取SQL语句的执行计划,如何从各种数据源显示和查看已经生成的执行计划。 第二篇“SQL优化技术”深入分析Oracle的SQL...

    Oracle 高性能SQL引擎剖析SQL优化与调优机制详解

    第二篇“SQL优化技术”深入分析Oracle的SQL优化技术,包括逻辑优化技术和物理优化技术。 第三篇“SQL调优技术”深入剖析Oracle提供的各项调优技术。先对语句实际运行的性能统计数据进行了深度分析,介绍各项统计...

    自我管理数据库-自动的sql调优

    它通过分析SQL结构和执行情况,提供改善访问路径的建议,比如创建索引或调整查询结构。SQL Access Advisor同样可以创建SQL配置文件,这些文件包含了针对特定SQL语句的自定义优化器设置,以生成更优化的执行计划。 *...

    T-SQL性能调优秘笈 基于SQL Server 2012窗口函数

    性能调优是数据库管理中的一个重要方面,它涉及到对数据库操作和查询进行细致的分析和改进,目的是为了减少执行时间、降低资源消耗、优化查询计划并提供更快的数据访问速度。 提到的“学习sql的好书”则是对该书...

Global site tag (gtag.js) - Google Analytics