不知道大家之前对类似MySQL通过添加索引达到优化SQL的具体操作的文章有无了解，今天我在这里给大家再简单的讲讲。感兴趣的话就一起来看看正文部分吧，相信看完MySQL通过添加索引达到优化SQL的具体操作你一定会有所收获的。
10年积累的网站建设、
成都网站设计经验，可以快速应对客户对网站的新想法和需求。提供各种问题对应的解决方案。让选择我们的客户得到更好、更有力的网络服务。我虽然不认识你，你也不认识我。但先
网站设计制作后付款的网站建设流程，更有
南召免费网站建设让你可以放心的选择与我们合作。
在慢查询日志中有一条慢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的具体操作这篇文章，大家觉得怎么样？如果想要了解更多相关，可以继续关注我们的行业资讯板块。
另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。
文章标题：MySQL通过添加索引达到优化SQL的具体操作-创新互联
本文来源：
http://cdkjz.cn/article/dgdphi.html
