经验教训只要涉及到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)
相关推荐
SQL调优涉及分析和改进SQL语句的执行效率,包括优化查询结构、选择合适的索引、调整表结构、使用绑定变量、避免全表扫描等策略。理解执行计划可以帮助识别性能瓶颈,通过调整执行路径、选择最佳访问方法和减少数据...
在数据库管理领域,SQL(Structured Query Language)调优是一项至关重要的技能,它涉及到优化数据库查询性能,提升系统响应速度,以及降低服务器资源消耗。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语句的执行效率直接影响到整个应用程序的响应速度和资源消耗。以下是对相关概念的详细...
通过细致地分析SQL语句执行计划和实际执行情况,可以发现潜在的问题并采取相应的优化措施。具体步骤包括: 1. **使用EXPLAIN功能**:DB2提供了强大的EXPLAIN工具,用于展示SQL语句的执行计划。这有助于理解查询如何...
Oracle的SQL优化与调优机制复杂多样,涵盖了从SQL语句的编写、执行计划的选择、到资源管理的全方位调优方法。为了深入理解Oracle SQL优化与调优机制,需要掌握以下几个核心知识点。 首先,要掌握Oracle SQL的基本...
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**: 这个...
重点讲解执行计划在SQL语句执行的生命周期中所处的位置和作用,SQL引擎如何生成执行计划以及如何获取SQL语句的执行计划,如何从各种数据源显示和查看已经生成的执行计划。 第二篇“SQL优化技术”深入分析Oracle的SQL...
第二篇“SQL优化技术”深入分析Oracle的SQL优化技术,包括逻辑优化技术和物理优化技术。 第三篇“SQL调优技术”深入剖析Oracle提供的各项调优技术。先对语句实际运行的性能统计数据进行了深度分析,介绍各项统计...
它通过分析SQL结构和执行情况,提供改善访问路径的建议,比如创建索引或调整查询结构。SQL Access Advisor同样可以创建SQL配置文件,这些文件包含了针对特定SQL语句的自定义优化器设置,以生成更优化的执行计划。 *...
性能调优是数据库管理中的一个重要方面,它涉及到对数据库操作和查询进行细致的分析和改进,目的是为了减少执行时间、降低资源消耗、优化查询计划并提供更快的数据访问速度。 提到的“学习sql的好书”则是对该书...