Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

MySQL optimizes the specific operation of SQL by adding indexes

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.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report