单键索引是否会一个sql走多个？

当你的sql可以匹配到多个单间索引的时候，大家一般都认为只走一个索引。 mysql> \s -------------- mysql Ver 14.14 Distrib 5.1.48, for Win32 (ia32) Connection id: 13 Current database: pk_test Current user: root@localhost SSL: Not in use Using delimiter: ; Server version: 5.1.48-community MySQL Community Server (GPL) Protocol version: 10 Connection: localhost via TCP/IP Server characterset: latin1 Db characterset: utf8 Client characterset: latin1 Conn. characterset: latin1 TCP port: 3306 Uptime: 43 min 42 sec Threads: 2 Questions: 2604 Slow queries: 0 Opens: 23 Flush tables: 1 Open tables: 2 Queries per second avg: 0.993 -------------- ======================================== 首先下面两个表是在同一个库里。 CREATE TABLE `t_carmodelparamvalue2` ( `ModelParamValueID` int(11) NOT NULL AUTO_INCREMENT, `ParameterID` int(11) NOT NULL, `ParamValueID` int(11) DEFAULT NULL, `ModelID` int(11) NOT NULL, `ValueText` varchar(512) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL, `SeriesID` int(11) DEFAULT NULL, PRIMARY KEY (`ModelParamValueID`), KEY `ModelID` (`ModelID`), KEY `SeriesID` (`SeriesID`), KEY `ak_paramvalue_parameterid` (`ParameterID`) ) ENGINE=InnoDB AUTO_INCREMENT=1254098 DEFAULT CHARSET=gb2312; mysql> select count(*) from `t_carmodelparamvalue2`; +----------+ | count(*) | +----------+ | 500 | +----------+ 1 row in set (0.00 sec) mysql> explain select * from T_CarModelParamValue2 where modelid = 276666 and ParameterID = 180; +----+-------------+-----------------------+------+-----------------------------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------------------+------+-----------------------------------+---------+---------+-------+------+-------------+ | 1 | SIMPLE | T_CarModelParamValue2 | ref | ModelID,ak_paramvalue_parameterid | ModelID | 4 | const | 1 | Using where | +----+-------------+-----------------------+------+-----------------------------------+---------+---------+-------+------+-------------+ 1 row in set (0.00 sec) =================================================== 但是，当数据超过百万级的时候呢？ CREATE TABLE `t_carmodelparamvalue` ( `ModelParamValueID` int(11) NOT NULL AUTO_INCREMENT, `ParameterID` int(11) NOT NULL, `ParamValueID` int(11) DEFAULT NULL, `ModelID` int(11) NOT NULL, `ValueText` varchar(512) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL, `SeriesID` int(11) DEFAULT NULL, PRIMARY KEY (`ModelParamValueID`), KEY `ModelID` (`ModelID`), KEY `SeriesID` (`SeriesID`), KEY `ak_paramvalue_parameterid` (`ParameterID`) ) ENGINE=InnoDB AUTO_INCREMENT=1253591 DEFAULT CHARSET=gb2312; mysql> select count(*) from `t_carmodelparamvalue`; +----------+ | count(*) | +----------+ | 1189500 | +----------+ 1 row in set (2.73 sec) mysql> explain select * from T_CarModelParamValue where modelid = 276666 and ParameterID = 180; +----+-------------+----------------------+-------------+-----------------------------------+-----------------------------------+---------+------+---- --+-----------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------------------+-------------+-----------------------------------+-----------------------------------+---------+------+------+-----------------------------------------------------------------+ | 1 | SIMPLE | T_CarModelParamValue | index_merge | ModelID,ak_paramvalue_parameterid | ModelID,ak_paramvalue_parameterid | 4,4 | NULL |1 | Using intersect(ModelID,ak_paramvalue_parameterid); Using where | +----+-------------+----------------------+-------------+-----------------------------------+-----------------------------------+---------+------+------+-----------------------------------------------------------------+ 1 row in set (0.00 sec) 走的却是两个。
看表现的结论是，当数据量达到百万的时候就会走多个。

其实，是，当一个表做过大量的insert、select或者转换引擎等操作时，就会出现这个问题。

清空那个百万级的表之后还是会先走多个引擎。