经验教训只要涉及到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)
相关推荐
- **自动索引优化(Auto Indexing)**:Oracle 19C引入了机器学习算法,能自动识别并创建或删除索引,以提升SQL查询性能。 - **Automatic Performance Monitoring**:提供更详尽的性能监控工具,帮助DBA快速定位问题...
在数据库管理领域,SQL(Structured Query Language)调优是一项至关重要的技能,它涉及到优化数据库查询性能,提升系统响应速度,以及降低服务器资源消耗。SQL调优对于任何处理大量数据的应用程序来说都是不可或缺...
"实战MySQL慢SQL调优" 在数据库性能优化中,慢SQL查询是最常见的...慢SQL查询的调优需要考虑多方面的因素,包括索引的使用、查询语句的优化、数据库结构的优化和缓存的使用。只有通过综合的优化,才能达到最佳的性能。
Oracle SQL 性能调优是一个非常重要的课题,它涉及到 Oracle 数据库的性能优化,包括 SQL 语句优化、索引使用优化、多表关联查询操作优化、SQL 执行计划优化等。下面是Oracle SQL 性能调优的详细知识点: 1. Oracle...
Oracle 19C SQL调优是数据库管理员(DBA)日常工作中不可或缺的一部分,它涉及到优化SQL查询性能,提高数据库系统的整体效率。本指南针对Oracle 19C版本,提供了丰富的调优策略和技术,旨在帮助DBA们更好地管理和维护...
SQL调优涉及到多个方面,包括查询优化、索引优化、存储优化等,旨在提高数据检索速度,减少资源消耗,提升整体应用性能。本篇文章将围绕“SQL调优与案例”这一主题,结合MySQL性能调优,深入探讨相关知识点。 1. **...
Oracle执行计划和SQL调优是数据库管理中至关重要的主题,主要涉及如何优化SQL查询以提高数据库性能。在Oracle数据库系统中,SQL语句的执行效率直接影响到整个应用程序的响应速度和资源消耗。以下是对相关概念的详细...
**PostgreSQL 慢 SQL 调优手册** 1、**Create Index Directly** 创建合适的索引是优化查询性能的关键。为经常出现在 WHERE 子句中的列创建索引可以显著提升查询速度。例如,如果你的查询频繁地过滤某个特定列,如 ...
通过细致地分析SQL语句执行计划和实际执行情况,可以发现潜在的问题并采取相应的优化措施。具体步骤包括: 1. **使用EXPLAIN功能**:DB2提供了强大的EXPLAIN工具,用于展示SQL语句的执行计划。这有助于理解查询如何...
例如耽搁查询 的排序、连接、扫描和GROUP BY字句同时执行,SQL SERVER根据系统的负载情况决定最优的并行等级,复杂的需要消耗大量的CPU的查询最适合并行处理。但是更新操作UPDATE,INSERT, DELETE还不能并行处理。 ...
Oracle的SQL优化与调优机制复杂多样,涵盖了从SQL语句的编写、执行计划的选择、到资源管理的全方位调优方法。为了深入理解Oracle SQL优化与调优机制,需要掌握以下几个核心知识点。 首先,要掌握Oracle SQL的基本...
本篇文章将从背景知识、SQL 调优和工具介绍三个方面对 ORACLE 执行计划和 SQL 调优进行概述。 背景知识 在 ORACLE 中,每个表都有一个 Rowid 伪列,该列不是用户定义的,而是系统自己给加上的。Rowid 是唯一的,...
ORACLE执行计划和SQL调优 ORACLE执行计划是指数据库管理系统在执行SQL语句时,数据库管理系统如何选择执行计划,以便高效地执行SQL语句。执行计划是指数据库管理系统在执行SQL语句时,选择的访问路径和执行顺序。...
- SQL 优化器、重写器(辅助 SQL 调优)。SQL optimizer and rewriter. - SQL 优化器、重写器(辅助 SQL 调优)。SQL optimizer and rewriter. - SQL 优化器、重写器(辅助 SQL 调优)。SQL optimizer and rewriter. - ...
针对SQL执行慢的问题,本资料集合了DB2存储过程调优的方法和实例,旨在提供解决策略。 首先,我们要了解REORG 和 REORGCHK以及RUNSTATS这三种DB2命令,它们在数据库维护中起着至关重要的作用。 1. **REORG**: 这个...
- **索引优化**:根据查询模式创建索引,并定期检查和调整索引结构。 - **提高硬件配置**:增加内存容量,升级网络设备,提升系统整体性能。 - **合理组织查询**:简化查询逻辑,避免不必要的计算或数据转换。 - **...
- **索引优化**:合理的索引可以显著提高查询效率,特别是在窗口函数涉及的表结构中。可以通过分析查询计划来确定是否需要添加、修改或删除索引。 - **查询优化**:通过调整WHERE子句、JOIN条件等方式来减少不必要的...