联合索引真的存在吗？

[lumengabc]

#用时：16s
SELECT * FROM vlc_caomei_state 
WHERE 
city = '太原市' 
AND region='山西省' 
order by region 

#用时：16s
SELECT * FROM vlc_caomei_state 
WHERE 
region='山西省' 
AND 
city = '太原市' 
order by region 

网上说联合索引按顺序执行才会走索引，但我测试结果发现列的位置怎么换，用时都差不多啊。
#索引

  PRIMARY KEY (`id`),
  KEY `time` (`time`) USING BTREE,
  KEY `version` (`version`) USING BTREE,
  KEY `versions` (`version_1`,`version_2`,`version_3`,`version_4`),
  KEY `regions` (`region`,`city`)
) ENGINE=InnoDB AUTO_INCREMENT=5399557 DEFAULT CHARSET=latin1;

[benluobo]

你换列当然没有意义 mysql查询分析器会重新排列你的where里面的顺序 所以当你用AND的时候，前后的顺序对于查询分析器是没有区别的 如果你要测试联合索引，你可以假设只对region和city进行查询测试

[lumengabc]

好的，谢谢！

[lumengabc]

我测试发现结果为几万条大数据量，用联合索引效果不明显。 结果为几千条的小数据量效果明显。

[ACMAIN_CHM]

贴出你的 explain SELECT * FROM vlc_caomei_state WHERE city = '太原市' AND region='山西省' order by region ; show index from vlc_caomei_state ; 估计是索引没创建正确。

[卖水果的net]

用 and 连接的N个条件，跟顺利没有什么关系，优化器自己去调整。

[soldierluo]

建这么多key

[lumengabc]

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 | 60900 | Using index condition |
+----+-------------+------------------+------+---------------+---------+---------+-------------+-------+-----------------------+
1 row in set (0.02 sec)

mysql> show index from vlc_caomei_state ;
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+-----------
----+
| Table            | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comm
ent |
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+-----------
----+
| vlc_caomei_state |          0 | PRIMARY  |            1 | id          | A         |     3918061 |     NULL | NULL   |      | BTREE      |         |
    |
| vlc_caomei_state |          1 | time     |            1 | time        | A         |     3918061 |     NULL | NULL   | YES  | BTREE      |         |
    |
| vlc_caomei_state |          1 | version  |            1 | version     | A         |          96 |     NULL | NULL   | YES  | BTREE      |         |
    |
| vlc_caomei_state |          1 | versions |            1 | version_1   | A         |        1342 |     NULL | NULL   | YES  | BTREE      |         |
    |
| vlc_caomei_state |          1 | versions |            2 | version_2   | A         |        1342 |     NULL | NULL   | YES  | BTREE      |         |
    |
| vlc_caomei_state |          1 | versions |            3 | version_3   | A         |        1342 |     NULL | NULL   | YES  | BTREE      |         |
    |
| vlc_caomei_state |          1 | versions |            4 | version_4   | A         |        4703 |     NULL | NULL   | YES  | BTREE      |         |
    |
| vlc_caomei_state |          1 | regions  |            1 | region      | A         |          78 |     NULL | NULL   | YES  | BTREE      |         |
    |
| vlc_caomei_state |          1 | regions  |            2 | city        | A         |         782 |     NULL | NULL   | YES  | BTREE      |         |
    |
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+-----------
----+
9 rows in set (0.00 sec)

mysql>

[lumengabc]

引用 4 楼 ACMAIN_CHM 的回复:

这是我的表数据。

[lumengabc]

#用时：28s
SELECT * FROM vlc_caomei_state   
WHERE   
city = '济南市'     
order by region  

#用时：24s
SELECT * FROM vlc_caomei_state   
WHERE   
city = '济南市'   
AND   
region='山东省'   
order by region  

好像知道原因了。多数据量，不加limit的话两条语句时间差不多

#用时：++++(太慢)
SELECT * FROM vlc_caomei_state   
WHERE   
city = '济南市'     
order by region  
limit 0,10;
#用时：0.1s
SELECT * FROM vlc_caomei_state   
WHERE   
city = '济南市'   
AND   
region='山东省'   
order by region  
limit 0,10;