MySQL通过添加索引达到优化SQL的具体操作
admin
2023-05-02 04:21:33
0次
不知道大家之前对类似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的具体操作这篇文章，大家觉得怎么样？如果想要了解更多相关，可以继续关注我们的行业资讯板块。
相关内容
热门资讯
卖“毒蛋”的人，抓到了
作者 | 何国胜 编辑 | 向现“（人）抓到了，目前案件正在侦办中。”7月28日晚间，苏州禁毒部门有...
尺素金声丨实施零关税国家达63...
海关总署发布的数据显示，今年5月1日起，我国对53个非洲建交国全面实施零关税举措，目前，我国实施零关...
职业索赔盯上基层诊所，倒逼用药...
文 | 布丁基层诊所正在被职业索赔盯上。据新京报，去年夏天，一男子走进河南南阳一家诊所，要求购买三瓶...
“西瓜我全买了”就可以肆意妄为...
拿西瓜砸了人，把瓜都买了，就能一走了之吗？事实证明，这套逻辑在法治社会行不通。7月28日晚，据海峡都...
科学家在日本广岛发现新物质，系...
在美国对日本广岛进行原子弹轰炸近81年后，科学家们在广岛的沙滩上发现了一种奇异且从未被发现过的新物质...
AI失控，反噬开始
作者 | 贺一 编辑 | 阿树近期，中国开源模型在美国频繁引发热议。7月28日，月之暗面发布Kimi...
“总统千金天价离婚”，分到43...
2026年7月24日下午，首尔高等法院，一场持续近十年的司法拉锯战终于接近尾声。法庭裁定SK集团会长...
巴基斯坦，又拿下一个历史性协议
全世界都没想到，接连的中东大战，巴基斯坦正成为最大赢家。去年以色列追杀哈马斯，空袭卡塔尔首都，阿拉伯...
汇正财经贺峰的一对一指导服务怎...
对于考虑购买证券投资顾问服务的投资者来说,'一对一指导服务怎么样'是一个重要的考量维度。需要首先...
重庆失联00后网格员龚宝冬确认...
重庆失联00后网格员龚宝冬确认遇难 【重庆失联00后网格员龚宝冬确认遇难】2026年7月29日...