In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-04-08 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
I don't know if you have any knowledge about the specific operation of MySQL by adding indexes to optimize SQL. Today, I'm here to talk about it briefly. If you are interested, let's take a look at the body. I believe you will gain something after reading the specific operation of MySQL by adding an index to optimize SQL.
There is a slow SQL in the slow query log, and the execution time is about 3 seconds
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)
View the execution plan
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 | | 1 | 1 | PRIMARY | | system | NULL | 1 | | 3 | DERIVED | | ALL | NULL | 18 | | | 3 | DERIVED | | ALL | 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 | 18918 | Using temporary; Using filesort | 5 | DERIVED | ConferenceUser | ALL | 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.04sec)
View Index
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)
Add an index on the column of a table
Mysql > alter table ConferenceUser add index index_createdtime (createdTime); Query OK, 6439784 rows affected (38.46 sec) Records: 6439784 Duplicates: 0 Warnings: 0 View Index 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 | | 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)
The second execution time is reduced to 0.17 seconds.
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)
View the execution plan
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 | 1 | | 1 | PRIMARY | | system | NULL | 1 | | 3 | DERIVED | | ALL | NULL | NULL | | | 20 | 3 | DERIVED | | ALL | 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 | 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)
After reading the article that MySQL optimizes the specific operation of SQL by adding indexes, what do you think? If you want to know more about it, you can continue to follow our industry information section.
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 251
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.