MySQL通过添加索引达到优化SQL的具体操作

不知道大家之前对类似MySQL通过添加索引达到优化SQL的具体操作的文章有无了解，今天我在这里给大家再简单的讲讲。感兴趣的话就一起来看看正文部分吧，相信看完MySQL通过添加索引达到优化SQL的具体操作你一定会有所收获的。

在慢查询日志中有一条慢SQL，执行时间约为3秒

mysql> SELECT    -> t.total_meeting_num,    -> r.voip_user_num    -> FROM    -> (    -> SELECT    -> count(*) total_meeting_num    -> FROM    -> Conference    -> WHERE    -> isStart = 1    -> AND startTime >= ADDDATE(now(), - 1)    -> AND billingcode != 651158    -> AND billingcode != 651204    -> ) t,    -> (    -> SELECT    -> count(userID) voip_user_num    -> FROM    -> (    -> SELECT    -> conferenceID,    -> userID,    -> isOnline,    -> createdTime    -> FROM    -> (    -> SELECT    -> *    -> FROM    -> ConferenceUser    -> WHERE    -> createdTime >= ADDDATE(now(), - 1)    -> AND userID > 1000    -> ORDER BY    -> userID,    -> createdTime DESC    -> ) t    -> GROUP BY    -> userID    -> ) t,    -> (    -> SELECT    -> *    -> FROM    -> Conference    -> WHERE    -> isStart = 1    -> AND startTime >= ADDDATE(now(), - 1)    -> AND conferenceName NOT LIKE 'evmonitor%'    -> ) r    -> WHERE    -> t.isOnline = 1    -> AND t.conferenceID = r.conferenceID    -> ) r;+-------------------+---------------+| total_meeting_num | voip_user_num |+-------------------+---------------+|                29 |            48 |+-------------------+---------------+1 row in set (3.01 sec)

查看执行计划

mysql> explain SELECT    -> t.total_meeting_num,    -> r.voip_user_num    -> FROM    -> (    -> SELECT    -> count(*) total_meeting_num    -> FROM    -> Conference    -> WHERE    -> isStart = 1    -> AND startTime >= ADDDATE(now(), - 1)    -> AND billingcode != 651158    -> AND billingcode != 651204    -> ) t,    -> (    -> SELECT    -> count(userID) voip_user_num    -> FROM    -> (    -> SELECT    -> conferenceID,    -> userID,    -> isOnline,    -> createdTime    -> FROM    -> (    -> SELECT    -> *    -> FROM    -> ConferenceUser    -> WHERE    -> createdTime >= ADDDATE(now(), - 1)    -> AND userID > 1000    -> ORDER BY    -> userID,    -> createdTime DESC    -> ) t    -> GROUP BY    -> userID    -> ) t,    -> (    -> SELECT    -> *    -> FROM    -> Conference    -> WHERE    -> isStart = 1    -> AND startTime >= ADDDATE(now(), - 1)    -> AND conferenceName NOT LIKE 'evmonitor%'    -> ) r    -> WHERE    -> t.isOnline = 1    -> AND t.conferenceID = r.conferenceID    -> ) r;+----+-------------+----------------+--------+----------------+----------------+---------+------+---------+---------------------------------+| id | select_type | table          | type   | possible_keys  | key            | key_len | ref  | rows    | Extra                           |+----+-------------+----------------+--------+----------------+----------------+---------+------+---------+---------------------------------+|  1 | PRIMARY     |      | system | NULL           | NULL           | NULL    | NULL |       1 |                                 ||  1 | PRIMARY     |      | system | NULL           | NULL           | NULL    | NULL |       1 |                                 ||  3 | DERIVED     |      | ALL    | NULL           | NULL           | NULL    | NULL |      18 |                                 ||  3 | DERIVED     |      | ALL    | NULL           | NULL           | NULL    | NULL |   12667 | Using where; Using join buffer  ||  6 | DERIVED     | Conference     | range  | ind_start_time | ind_start_time | 5       | NULL |     889 | Using where                     ||  4 | DERIVED     |      | ALL    | NULL           | NULL           | NULL    | NULL |   18918 | Using temporary; Using filesort ||  5 | DERIVED     | ConferenceUser | ALL    | NULL           | NULL           | NULL    | NULL | 6439656 | Using where; Using filesort     ||  2 | DERIVED     | Conference     | range  | ind_start_time | ind_start_time | 5       | NULL |     889 | Using where                     |+----+-------------+----------------+--------+----------------+----------------+---------+------+---------+---------------------------------+8 rows in set (3.04 sec)

查看索引

mysql> show index from ConferenceUser;+----------------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table          | Non_unique | Key_name              | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+----------------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| ConferenceUser |          0 | PRIMARY               |            1 | recordID     | A         |     6439758 |     NULL | NULL   |      | BTREE      |         |               || ConferenceUser |          0 | PRIMARY               |            2 | conferenceID | A         |     6439758 |     NULL | NULL   |      | BTREE      |         |               || ConferenceUser |          1 | ind_conference_userID |            1 | conferenceID | A         |      804969 |     NULL | NULL   |      | BTREE      |         |               || ConferenceUser |          1 | ind_conference_userID |            2 | userID       | A         |     3219879 |     NULL | NULL   |      | BTREE      |         |               |+----------------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+4 rows in set (0.00 sec)

在表的列上添加索引

mysql> alter table ConferenceUser add index index_createdtime(createdTime);    Query OK, 6439784 rows affected (38.46 sec)Records: 6439784  Duplicates: 0  Warnings: 0查看索引mysql> show index from ConferenceUser;+----------------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table          | Non_unique | Key_name              | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+----------------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| ConferenceUser |          0 | PRIMARY               |            1 | recordID     | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               || ConferenceUser |          0 | PRIMARY               |            2 | conferenceID | A         |     6439794 |     NULL | NULL   |      | BTREE      |         |               || ConferenceUser |          1 | ind_conference_userID |            1 | conferenceID | A         |      715532 |     NULL | NULL   |      | BTREE      |         |               || ConferenceUser |          1 | ind_conference_userID |            2 | userID       | A         |     3219897 |     NULL | NULL   |      | BTREE      |         |               || ConferenceUser |          1 | index_createdtime     |            1 | createdTime  | A         |     6439794 |     NULL | NULL   |      | BTREE      |         |               |+----------------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+5 rows in set (0.00 sec)

再次执行时间缩短为0.17秒

mysql> SELECT    -> t.total_meeting_num,    -> r.voip_user_num    -> FROM    -> (    -> SELECT    -> count(*) total_meeting_num    -> FROM    -> Conference    -> WHERE    -> isStart = 1    -> AND startTime >= ADDDATE(now(), - 1)    -> AND billingcode != 651158    -> AND billingcode != 651204    -> ) t,    -> (    -> SELECT    -> count(userID) voip_user_num    -> FROM    -> (    -> SELECT    -> conferenceID,    -> userID,    -> isOnline,    -> createdTime    -> FROM    -> (    -> SELECT    -> *    -> FROM    -> ConferenceUser    -> WHERE    -> createdTime >= ADDDATE(now(), - 1)    -> AND userID > 1000    -> ORDER BY    -> userID,    -> createdTime DESC    -> ) t    -> GROUP BY    -> userID    -> ) t,    -> (    -> SELECT    -> *    -> FROM    -> Conference    -> WHERE    -> isStart = 1    -> AND startTime >= ADDDATE(now(), - 1)    -> AND conferenceName NOT LIKE 'evmonitor%'    -> ) r    -> WHERE    -> t.isOnline = 1    -> AND t.conferenceID = r.conferenceID    -> ) r;+-------------------+---------------+| total_meeting_num | voip_user_num |+-------------------+---------------+|                29 |            52 |+-------------------+---------------+1 row in set (0.17 sec)

查看执行计划

mysql> explain SELECT    -> t.total_meeting_num,    -> r.voip_user_num    -> FROM    -> (    -> SELECT    -> count(*) total_meeting_num    -> FROM    -> Conference    -> WHERE    -> isStart = 1    -> AND startTime >= ADDDATE(now(), - 1)    -> AND billingcode != 651158    -> AND billingcode != 651204    -> ) t,    -> (    -> SELECT    -> count(userID) voip_user_num    -> FROM    -> (    -> SELECT    -> conferenceID,    -> userID,    -> isOnline,    -> createdTime    -> FROM    -> (    -> SELECT    -> *    -> FROM    -> ConferenceUser    -> WHERE    -> createdTime >= ADDDATE(now(), - 1)    -> AND userID > 1000    -> ORDER BY    -> userID,    -> createdTime DESC    -> ) t    -> GROUP BY    -> userID    -> ) t,    -> (    -> SELECT    -> *    -> FROM    -> Conference    -> WHERE    -> isStart = 1    -> AND startTime >= ADDDATE(now(), - 1)    -> AND conferenceName NOT LIKE 'evmonitor%'    -> ) r    -> WHERE    -> t.isOnline = 1    -> AND t.conferenceID = r.conferenceID    -> ) r;+----+-------------+----------------+--------+-------------------+-------------------+---------+------+-------+---------------------------------+| id | select_type | table          | type   | possible_keys     | key               | key_len | ref  | rows  | Extra                           |+----+-------------+----------------+--------+-------------------+-------------------+---------+------+-------+---------------------------------+|  1 | PRIMARY     |      | system | NULL              | NULL              | NULL    | NULL |     1 |                                 ||  1 | PRIMARY     |      | system | NULL              | NULL              | NULL    | NULL |     1 |                                 ||  3 | DERIVED     |      | ALL    | NULL              | NULL              | NULL    | NULL |    20 |                                 ||  3 | DERIVED     |      | ALL    | NULL              | NULL              | NULL    | NULL | 12682 | Using where; Using join buffer  ||  6 | DERIVED     | Conference     | range  | ind_start_time    | ind_start_time    | 5       | NULL |   879 | Using where                     ||  4 | DERIVED     |      | ALL    | NULL              | NULL              | NULL    | NULL | 18951 | Using temporary; Using filesort ||  5 | DERIVED     | ConferenceUser | range  | index_createdtime | index_createdtime | 4       | NULL | 31455 | Using where; Using filesort     ||  2 | DERIVED     | Conference     | range  | ind_start_time    | ind_start_time    | 5       | NULL |   879 | Using where                     |+----+-------------+----------------+--------+-------------------+-------------------+---------+------+-------+---------------------------------+8 rows in set (0.18 sec)

看完MySQL通过添加索引达到优化SQL的具体操作这篇文章，大家觉得怎么样？如果想要了解更多相关，可以继续关注我们的行业资讯板块。