背景:手动制造了一张百万级别的表,根据时间筛选做个sum统计。由于不合理的索引设计,导致之前查询时间为9s,经过索引优化后只需100ms,下面就是见证奇迹的时刻!
这次优化的是一张report表,表里面有百万数据量。
1、查看表结构:
mysql> desc report;
+---------------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+----------------------+------+-----+---------+-------+
| date | date | NO | | NULL | |
| hour | smallint(6) unsigned | NO | | NULL | |
| app | varchar(32) | YES | | NULL | |
| name | varchar(32) | YES | MUL | NULL | |
| request_num | bigint(15) unsigned | NO | | 0 | |
+---------------+----------------------+------+-----+---------+-------+
13 rows in set (0.01 sec)
2、查看目前表中的索引:
目前该表的索引名称是index_date_app,这是一个联合索引。
mysql> show index from report;
+--------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| report | 1 | index_date_app | 1 | name | A | 22 | NULL | NULL | YES | BTREE | | |
| report | 1 | index_date_app | 2 | app | A | 299 | NULL | NULL | YES | BTREE | | |
| report | 1 | index_date_app | 3 | date | A | 36505 | NULL | NULL | | BTREE | | |
+--------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
3、使用优化前的代码查看执行计划:
通过查看执行计划发现:我们的索引已经发生效了。
-> SELECT
-> name,
-> sum(request_num) request_num
-> FROM report
-> WHERE 1=1
-> AND date >= '2019-09-05'
-> AND date <= '2019-09-11'
-> AND name IN ('ccc','tdd')
-> GROUP BY name;
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | report | NULL | index | index_date_app | index_date_app | 201 | NULL | 1174157 | 11.11 | Using where |
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
4、查看执行的效率
mysql> SELECT
-> name,
-> sum(request_num) request_num
-> FROM report
-> WHERE 1=1
-> AND date >= '2019-09-05'
-> AND date <= '2019-09-11'
-> AND name IN ('ccc','tdd')
-> GROUP BY name;
+----------+-------------+
| pmp_name | request_num |
+----------+-------------+
| tdd | 73660911 |
+----------+-------------+
1 row in set (9.26 sec)
实验发现:依然执行了9秒钟,百万级别的表还不加其他join已经执行的很慢了,因此需要优化索引;
那么查询慢的原因是什么呢?
通过之前几篇博客内容提到过联合索引最左匹配的原则(如果不清楚,建议看看基础,点我),咱们联合索引的顺序是name,app,date;但咱们查询条件里面是date以及name,并没有app,因此,根据最走匹配原则,咱们这次查询只使用到了name上的索引,并没有使用用到date索引,为此查询的速度很慢。那现在调整索引顺序:date,name,app,这样查询的时候,就会使用(date,name)联合索引,查询效率肯定提升,来,再试一把!
mysql> alter table report drop index index_date_app;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table report add index index_date_app(date,pmp_name,app);
Query OK, 0 rows affected (2.97 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from report;
+--------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| report | 1 | index_date_app | 1 | date | A | 512 | NULL | NULL | | BTREE | | |
| report | 1 | index_date_app | 2 | name | A | 3018 | NULL | NULL | YES | BTREE | | |
| report | 1 | index_date_app | 3 | app | A | 42369 | NULL | NULL | YES | BTREE | | |
+--------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
5、索引优化后查看查询所需要的时间
mysql> SELECT
-> name,
-> sum(request_num) request_num
-> FROM report
-> WHERE 1=1
-> AND date >= '2019-09-05'
-> AND date <= '2019-09-11'
-> AND binary pmp_name IN ('ccc','tdd')
-> GROUP BY pmp_name;
+----------+-------------+
| pmp_name | request_num |
+----------+-------------+
| tdd | 73660911 |
+----------+-------------+
1 row in set (0.01 sec)
经过查询优化后,咱们发现,查询出来时间只需要0.01s,之前查询时间是9秒,优化效果是不是很明显呢?
最近开始优化各种查询效率,更多的优化查询都会分享出来和大家一起学习!