Mysql多列索引——最左精确匹配

时间：2022-09-22 00:10:50

若mysql的一张表上有一个多列索引，那么在编写where条件时，究竟哪些真正起到作用了呢，跟顺序有关系吗?

本文参考（http://blog.codinglabs.org/articles/theory-of-mysql-index.html）

假设有如下的一张表：

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,LAUNCH_DATE,TYPE)
)COMMENT='DB WorkerID Assigner for UID Generator',ENGINE = INNODB;

对应的查询语句分别是： 

select * from testTable where PORT=3306 and Type=1
select * from testTable where PORT=3307 and HOST_NAME='172.21.1.1'
select * from testTable where PORT=3308 AND HOST_NAME='172.21.1.2' AND TYPE=2


那么究竟用到了哪些索引呢？

第一句：没用到索引Mysql多列索引——最左精确匹配，在聚集索引上从左至右依次扫描过滤；

第二句：用到了辅助索引INDEX_WORKER_NODE；

第三句：用到了辅助索引INDEX_WORKER_NODE，但是只有HOST_NAME和PORT条件是通过索引完成的，条件TYPE是依次扫描过滤完成的；


为什么呢？

因为辅助索引是B+树实现的，虽然可以指定多个列，但是每个列的比较优先级不一样，写在前面的优先比较。一旦出现遗漏，在B+树上就无法继续搜索了（通过补齐等措施解决的除外），因此是按照最左连续匹配来的。既然是在B+树上搜索，对于条件的比较自然是要求精确匹配（即"="和"IN"）。不过顺序倒是可以颠倒，因为查询优化器重排序一下就好了。

回头来看，第一句，由于缺少HOST_NAME，只能在聚集索引的叶节点上，从左至右的扫描，挨个比对；

第二句，可以直接在辅助索引上查找，被找到的子树的所有叶节点就是命中的记录；

第三句，缺少LAUNCH_DATE条件，所以只能先依据HOST_NAME和PROT在辅助索引上查找，找到的主键值作为候选记录，然后到聚集索引上读取对应记录，再比较TYPE条件是否满足。


标签：

相关文章