索引最左匹配原则
建立联合索引时会遵循最左匹配原则,即最左优先,在检索数据时从联合索引的最左边开始匹配
例如:
- 为user表中的name、address、phone列添加联合索引
ALTER TABLE user ADD INDEX index_three(name,address,phone); - 所以,下面的三个SQL语句都可以命中索引
SELECT * FROM user WHERE address = ‘北京’ AND phone = ‘12345’ AND name = ‘张三’;
SELECT * FROM user WHERE name = ‘张三’ AND address = ‘北京’;
SELECT * FROM user WHERE name = ‘张三’;
- 这三条SQL语句在检索时分别会使用以下索引进行数据匹配 (name,address,phone) (name,address) (name)
- 索引字段出现的顺序可以是任意的,MySQL优化器会帮我们自动的调整where条件中的顺序
- 如果联合索引中最左边的列不在查询条件中,则不会命中索引
SELECT * FROM user WHERE address = ‘北京’ ;