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条件是否满足。