MYSQL EXPLAIN解析一 EXTRA中的USING INDEX，USING INDEX CONDITION

最新推荐文章于 2025-01-11 19:35:04 发布

凉城凉心凉忆悲

最新推荐文章于 2025-01-11 19:35:04 发布

阅读量2k

点赞数

分类专栏：数据库

本文链接：https://blog.youkuaiyun.com/qq_35298894/article/details/109636876

版权

本文详细介绍了MYSQL EXPLAIN中USING INDEX, USING INDEX CONDITION以及USING INDEX & USING WHERE的区别，并通过实例展示了它们在查询优化中的作用。通过对历史数据表history_uint的查询优化，从近3秒的查询时间降低到0.03秒，突显了索引优化的重要性。" 130879312,8753399,Java实现字符串合并拼接,"['java', '开发语言', 'jvm']

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

using index ：使用覆盖索引的时候就会出现

using where：在查找使用索引的情况下，需要回表去查询所需的数据

using index condition：查找使用了索引，但是需要回表查询数据

using index & using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

查慢查询记录

# Time: 181020 11:53:11
# User@Host: zabbix[zabbix] @ localhost []
# Thread_id: 117743 Schema: zabbix QC_hit: No
# Query_time: 2.857652 Lock_time: 0.000108 Rows_sent: 576 Rows_examined: 1728

SELECT itemid,COUNT(*) AS count,AVG(value) AS avg,MIN(value) AS min,MAX(value) AS max,round(1786* MOD(CAST(clock AS UNSIGNED)+158820,172800)/(172800),0) AS i,MAX(clock) AS clock FROM history_uint WHERE itemid='59886' AND clock>='1539834780' AND clock<='1540007580' GROUP BY itemid,round(1786* MOD(CAST(clock AS UNSIGNED)+158820,172800)/(172800),0);

对zabbix的history_uint表进行优化，优化前结果是

优化后是

MariaDB [zabbix]> explain SELECT itemid,COUNT(*) AS count,AVG(value) AS avg,MIN(value) AS min,MAX(value) AS max,round(1746* MOD(CAST(clock AS UNSIGNED)+67286,86400)/(86400),0) AS i,MAX(clock) AS clock FROM history_uint WHERE itemid='42794' AND clock>='1531199914' AND clock<='1531286314' GROUP BY itemid,round(1746* MOD(CAST(clock AS UNSIGNED)+67286,86400)/(86400),0);
+------+-------------+--------------+-------+----------------+----------------+---------+------+------+--------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------+-------+----------------+----------------+---------+------+------+--------------------------------------------------------+
| 1 | SIMPLE | history_uint | range | history_uint_1 | history_uint_1 | 12 | NULL | 285 | Using index condition; Using temporary; Using filesort |
+------+-------------+--------------+-------+----------------+----------------+---------+------+------+--------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [zabbix]&g

最低0.47元/天解锁文章