Mysql使用多列索引查询

在mysql中，如果使用多列索引进行查询，那么命中的记录是按插入的顺序还是按key的顺序返回呢？

这个问题在不同的存储引擎下，结果是不一样的，这里仅介绍InnoDB下的情形（按key的顺序返回）。

在InnoDB存储引擎下，索引分为聚集索引（对主键）和辅助索引两类（参见http://blog.codinglabs.org/articles/theory-of-mysql-index.html、http://imysql.com/2015/11/11/mysql-faq-primary-key-vs-secondary-key.shtml和http://itindex.net/detail/44489-mysql-%E7%B4%A2%E5%BC%95）。辅助索引又可分为单列索引和多列索引，当然了，聚集索引也可以是多列索引，但一般不这么做。如果开发人员未显示指定主键，那么mysql会自己挑选一个列作为主键或添加一个列作为主键。辅助索引和聚集索引都是B+树，在聚集索引上，叶节点的数据部分是一个完整的记录。而在辅助索引上，叶节点的数据部分是该记录的主键值。

在使用辅助索引进行查询时，首先通过辅助索引得到命中记录的主键值（按key的顺序，额~其实就是子树的叶节点从左到右），再拿这些主键值到聚集索引中找到对应的记录。所以，返回的结果默认情况下是按照辅助索引的key顺序，而不一定是记录的插入顺序。

类似的，在使用聚集索引进行查询时，返回的结果在默认情况下是按照主键值的递增顺序。

例如，对于表

DROP TABLE IF EXISTS testTable;CREATE TABLE testTable(ID BIGINT NOT NULL AUTO_INCREMENT COMMENT 'auto increment id',HOST_NAME VARCHAR(64) NOT NULL COMMENT 'host name',PORT VARCHAR(64) NOT NULL COMMENT 'port',TYPE INT NOT NULL COMMENT 'node type: ACTUAL or CONTAINER',LAUNCH_DATE DATE NOT NULL COMMENT 'launch date',MODIFIED TIMESTAMP NOT NULL COMMENT 'modified time',CREATED TIMESTAMP NOT NULL COMMENT 'created time',PRIMARY KEY(ID),UNIQUE KEY INDEX_WORKER_NODE(HOST_NAME,PORT,TYPE))COMMENT='DB WorkerID Assigner for UID Generator',ENGINE = INNODB;

插入的数据顺序是：

insert  into accg_test_0.testTable(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.1',3305,1,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');insert  into accg_test_0.testTable(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.1',3306,1,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');insert  into accg_test_0.testTable(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.1',3307,1,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');insert  into accg_test_0.testTable(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.1',3308,1,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');insert  into accg_test_0.testTable(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.1',3309,1,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');insert  into accg_test_0.testTable(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.1',3305,2,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');insert  into accg_test_0.testTable(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.1',3306,2,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');insert  into accg_test_0.testTable(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.1',3307,2,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');insert  into accg_test_0.testTable(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.1',3308,2,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');insert  into accg_test_0.testTable(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.1',3309,2,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');insert  into accg_test_0.testTable(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.2',3305,1,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');insert  into accg_test_0.testTable(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.2',3306,1,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');insert  into accg_test_0.testTable(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.2',3307,1,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');insert  into accg_test_0.testTable(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.2',3308,1,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');insert  into accg_test_0.testTable(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.2',3309,1,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');insert  into accg_test_0.testTable(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.2',3305,2,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');insert  into accg_test_0.testTable(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.2',3306,2,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');insert  into accg_test_0.testTable(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.2',3307,2,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');insert  into accg_test_0.testTable(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.2',3308,2,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');insert  into accg_test_0.testTable(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.2',3309,2,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');

和插入的数据顺序是：

insert  into accg_test_0.testTable_2(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.1',3305,1,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');insert  into accg_test_0.testTable_2(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.1',3305,2,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');insert  into accg_test_0.testTable_2(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.1',3306,1,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');insert  into accg_test_0.testTable_2(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.1',3306,2,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');insert  into accg_test_0.testTable_2(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.1',3307,1,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');insert  into accg_test_0.testTable_2(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.1',3307,2,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');insert  into accg_test_0.testTable_2(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.1',3308,1,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');insert  into accg_test_0.testTable_2(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.1',3308,2,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');insert  into accg_test_0.testTable_2(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.1',3309,1,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');insert  into accg_test_0.testTable_2(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.1',3309,2,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');insert  into accg_test_0.testTable_2(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.2',3305,1,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');insert  into accg_test_0.testTable_2(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.2',3305,2,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');insert  into accg_test_0.testTable_2(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.2',3306,1,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');insert  into accg_test_0.testTable_2(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.2',3306,2,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');insert  into accg_test_0.testTable_2(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.2',3307,1,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');insert  into accg_test_0.testTable_2(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.2',3307,2,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');insert  into accg_test_0.testTable_2(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.2',3308,1,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');insert  into accg_test_0.testTable_2(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.2',3308,2,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');insert  into accg_test_0.testTable_2(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.2',3309,1,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');insert  into accg_test_0.testTable_2(HOST_NAME,PORT,TYPE,LAUNCH_DATE,MODIFIED,CREATED) values('172.21.1.2',3309,2,'2017-02-01','2017-02-01 00:00:00','2017-02-01 00:00:00');

对于查询语句：

SELECT * FROM accg_test_0.testTable WHERE HOST_NAME='172.21.1.1';

返回的结果都是：

而对于查询：

SELECT * from accg_test_0.testTable where ID in (1,2,8,4,5,6);

返回的结果都是：