mysql联合索引分析测试

最新推荐文章于 2025-08-24 17:01:22 发布

原创最新推荐文章于 2025-08-24 17:01:22 发布 · 941 阅读

0 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

127 篇文章

订阅专栏

本文通过实验展示了在不同数据量下，使用联合索引查询性能的显著提升，特别是对于小数据集，联合索引效果尤为明显。同时，文章还探讨了LIMIT语句在查询中的应用及其对性能的影响。

小数据量（2W）条，走联合索引大概快10倍左右。

#联合索引
#和查询的顺序无关！MYSQL会自动重新排列
小数据量，效果明显。
#联合索引在单个查询和联合查询时速度比较如下：
#单个查询结果：2692条，用时0.117s
SELECT * FROM vlc_caomei_state 
WHERE 
city = '济南市' 
AND 
id < 1500000 
order by region 
#联合查询结果：2692条，用时0.017s
SELECT * FROM vlc_caomei_state 
WHERE 
city = '济南市' 
AND 
region='山东省' 
AND 
id < 1500000 
order by region 



大数据量查询，效果不明显，速度基本相同
#单个查询结果： 12w条，用时26 s
SELECT * FROM vlc_caomei_state 
WHERE 
city = '济南市' 
order by region 
#联合查询结果：12w 条，用时21 s
SELECT * FROM vlc_caomei_state 
WHERE 
city = '济南市' 
AND 
region='山东省' 
order by region

#limit效果

mysql> explain SELECT * FROM vlc_caomei_state
    -> WHERE
    -> city = '济南市'
    -> order by region
    -> limit 0,10;
+----+-------------+------------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table            | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+------------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | vlc_caomei_state | index | NULL          | regions | 306     | NULL |   10 | Using where |
+----+-------------+------------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain SELECT * FROM vlc_caomei_state
    -> WHERE
    -> city = '济南市'
    -> AND
    -> region='山东省'
    -> order by region
    -> limit 0,10;
+----+-------------+------------------+------+---------------+---------+---------+-------------+--------+-----------------------+
| id | select_type | table            | type | possible_keys | key     | key_len | ref         | rows   | Extra                 |
+----+-------------+------------------+------+---------------+---------+---------+-------------+--------+-----------------------+
|  1 | SIMPLE      | vlc_caomei_state | ref  | regions       | regions | 306     | const,const | 263458 | Using index condition |
+----+-------------+------------------+------+---------------+---------+---------+-------------+--------+-----------------------+
1 row in set (0.07 sec)

mysql> explain SELECT * FROM vlc_caomei_state
    -> WHERE
    -> city = '济南市'
    -> order by region ;
+----+-------------+------------------+------+---------------+------+---------+------+---------+-----------------------------+
| id | select_type | table            | type | possible_keys | key  | key_len | ref  | rows    | Extra                       |
+----+-------------+------------------+------+---------------+------+---------+------+---------+-----------------------------+
|  1 | SIMPLE      | vlc_caomei_state | ALL  | NULL          | NULL | NULL    | NULL | 3918061 | Using where; Using filesort |
+----+-------------+------------------+------+---------------+------+---------+------+---------+-----------------------------+
1 row in set (0.00 sec)

mysql> explain SELECT * FROM vlc_caomei_state
    -> WHERE
    -> city = '济南市'
    -> AND
    -> region='山东省'
    -> order by region  ;
+----+-------------+------------------+------+---------------+---------+---------+-------------+--------+-----------------------+
| id | select_type | table            | type | possible_keys | key     | key_len | ref         | rows   | Extra                 |
+----+-------------+------------------+------+---------------+---------+---------+-------------+--------+-----------------------+
|  1 | SIMPLE      | vlc_caomei_state | ref  | regions       | regions | 306     | const,const | 263458 | Using index condition |
+----+-------------+------------------+------+---------------+---------+---------+-------------+--------+-----------------------+
1 row in set (0.42 sec)

总结，大数据量，索引不明显。

http://bbs.youkuaiyun.com/topics/390747126