错误描述：表行如下，表中数据为空，进行执行分析时候，发现优化器没有按照预定那样走第二条索引，很奇怪
mysql> show create table answer_survey_info\G *************************** 1. row *************************** Table: answer_survey_info Create Table: CREATE TABLE `answer_survey_info` ( `answerId` bigint(64) NOT NULL AUTO_INCREMENT, `surveyId` bigint(64) NOT NULL, `questionId` bigint(64) NOT NULL, `uin` varchar(255) DEFAULT NULL, `userNumber` varchar(128) DEFAULT NULL, `selectId` bigint(64) DEFAULT NULL, `answerText` varchar(5000) DEFAULT NULL, `emailAddress` varchar(64) DEFAULT NULL, `ip` varchar(64) DEFAULT NULL, `createTime` datetime DEFAULT NULL, PRIMARY KEY (`answerId`), KEY `surveyId_questionId_selectId_index` (`surveyId`,`questionId`,`selectId`), KEY `surveyId_uin_index` (`uin`,`surveyId`) ) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> mysql> mysql> mysql> mysql> show full columns from answer_survey_info; +--------------+---------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +--------------+---------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+ | answerId | bigint(64) | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | | | surveyId | bigint(64) | NULL | NO | MUL | NULL | | select,insert,update,references | | | questionId | bigint(64) | NULL | NO | | NULL | | select,insert,update,references | | | uin | varchar(255) | utf8_general_ci | YES | MUL | NULL | | select,insert,update,references | | | userNumber | varchar(128) | utf8_general_ci | YES | | NULL | | select,insert,update,references | | | selectId | bigint(64) | NULL | YES | | NULL | | select,insert,update,references | | | answerText | varchar(5000) | utf8_general_ci | YES | | NULL | | select,insert,update,references | | | emailAddress | varchar(64) | utf8_general_ci | YES | | NULL | | select,insert,update,references | | | ip | varchar(64) | utf8_general_ci | YES | | NULL | | select,insert,update,references | | | createTime | datetime | NULL | YES | | NULL | | select,insert,update,references | | +--------------+---------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+ 10 rows in set (0.00 sec) mysql> mysql> mysql> mysql>
检查是否又是索引字符集的问题，由于是复合索引，使用他的左前缀的行作为索引行，发现都能够正常运行
mysql> explain select * from answer_survey_info where uin=‘15953‘; +----+-------------+--------------------+------+--------------------+--------------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------------+------+--------------------+--------------------+---------+-------+------+-------------+ | 1 | SIMPLE | answer_survey_info | ref | surveyId_uin_index | surveyId_uin_index | 768 | const | 1 | Using where | +----+-------------+--------------------+------+--------------------+--------------------+---------+-------+------+-------------+ 1 row in set (0.00 sec) mysql> mysql> mysql> explain select * from answer_survey_info where surveyId=10006; +----+-------------+--------------------+------+------------------------------------+------------------------------------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------------+------+------------------------------------+------------------------------------+---------+-------+------+-------+ | 1 | SIMPLE | answer_survey_info | ref | surveyId_questionId_selectId_index | surveyId_questionId_selectId_index | 8 | const | 1 | | +----+-------------+--------------------+------+------------------------------------+------------------------------------+---------+-------+------+-------+ 1 row in set (0.00 sec) mysql> mysql>
将第二索引的两行作为where判断，希望能走第二条索引（有个前提条件，第二条复合索引的右边行刚好是第一条索引的左前缀），结果如下，很是吃惊
mysql> explain select * from answer_survey_info where uin=‘15953‘ and surveyId=10006; +----+-------------+--------------------+------+-------------------------------------------------------+------------------------------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------------+------+-------------------------------------------------------+------------------------------------+---------+-------+------+-------------+ | 1 | SIMPLE | answer_survey_info | ref | surveyId_questionId_selectId_index,surveyId_uin_index | surveyId_questionId_selectId_index | 8 | const | 1 | Using where | +----+-------------+--------------------+------+-------------------------------------------------------+------------------------------------+---------+-------+------+-------------+ 1 row in set (0.00 sec)
由于数据的分布会影响优化器做出执行计划，所以考虑往里面放上一些数据，下面的存储过程往里面插入了10w条数据
mysql> delimiter $$ mysql> create procedure test() -> begin -> declare num int; -> set num=1; -> while num <= 100000 do -> insert into answer_survey_info values (null,FLOOR(RAND() * 10000),FLOOR(RAND() * 10000),FLOOR(RAND() * 10000),FLOOR(RAND() * 10000),FLOOR(RAND() * 10000),FLOOR(RAND() * 10000),FLOOR(RAND() * 10000),FLOOR(RAND() * 10000),now()); set num=num+1; -> end while; -> end; -> $$ Query OK, 0 rows affected (0.00 sec) mysql> call test(); -> $$ Query OK, 1 row affected (10.28 sec)
再次进行查询，现在已经正常了
mysql> explain select * from answer_survey_info where uin=‘2‘ and surveyId=1680; +----+-------------+--------------------+------+-------------------------------------------------------+--------------------+---------+-------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------------+------+-------------------------------------------------------+--------------------+---------+-------------+------+-------------+ | 1 | SIMPLE | answer_survey_info | ref | surveyId_questionId_selectId_index,surveyId_uin_index | surveyId_uin_index | 776 | const,const | 1 | Using where | +----+-------------+--------------------+------+-------------------------------------------------------+--------------------+---------+-------------+------+-------------+ 1 row in set (0.00 sec) mysql> mysql> mysql>
原文：http://www.cnblogs.com/magicaltravel/p/7245961.html