SLTechnology News&Howtos › Database ›

MySQL optimizes the specific operation of SQL by adding indexes

Shulou Source: shulou.com Published: 2022-06-01 18:36:21 10月22日 Update

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.