mysql联合索引分析测试

来源：互联网发布：js点击改变再点击还原编辑：程序博客网时间：2024/05/21 09:31

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

#联合索引#和查询的顺序无关！MYSQL会自动重新排列小数据量，效果明显。#联合索引在单个查询和联合查询时速度比较如下：#单个查询结果：2692条，用时0.117sSELECT * FROM vlc_caomei_state WHERE city = '济南市' AND id < 1500000 order by region #联合查询结果：2692条，用时0.017sSELECT * FROM vlc_caomei_state WHERE city = '济南市' AND region='山东省' AND id < 1500000 order by region 大数据量查询，效果不明显，速度基本相同#单个查询结果： 12w条，用时26 sSELECT * FROM vlc_caomei_state WHERE city = '济南市' order by region #联合查询结果：12w 条，用时21 sSELECT * 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.csdn.net/topics/390747126

0 0