mysql 索引 最左前缀原则

参考1
参考2
***左前缀法则:带头大哥不能死、中间兄弟不能断
索引法则--***左前缀法则

***左前缀法则学习和Demo演示

1 准备数据

1.1 建表
复制代码

DROP TABLE IF EXISTS staff;
CREATE TABLE IF NOT EXISTS staff (
id INT PRIMARY KEY auto_increment,
name VARCHAR(50),
age INT,
pos VARCHAR(50) COMMENT \'职位\',
salary DECIMAL(10,2)
);

复制代码

1.2 插入数据

INSERT INTO staff(name, age, pos, salary) VALUES(\'Alice\', 22, \'HR\', 5000);
INSERT INTO staff(name, age, pos, salary) VALUES(\'Bob\', 22, \'RD\', 10000);
INSERT INTO staff(name, age, pos, salary) VALUES(\'David\', 22, \'Sale\', 120000);

2 测试&Explain分析

2.1 创建索引

CREATE INDEX idx_nameAgePos ON staff(name, age, pos);

创建了一个基于 name, age, pos 三个字段的索引

2.2 索引测试

Case#1:只根据 name 字段来查询

EXPLAIN SELECT * FROM staff WHERE name = \'Alice\';

结果:

type=ref key=索引 ref=const ken_len=53

Case#2:只根据 name & age 字段来查询

EXPLAIN SELECT * FROM staff WHERE name = \'Alice\' AND age = 22;

结果:和 Case#1 差不多,但是:

key_len=58 ref=const, const

Case#3:根据 name & age & pos 来查询

EXPLAIN SELECT * FROM staff WHERE name = \'Alice\' AND age = 22 AND pos = \'HR\';

结果:索引仍然生效,同时,key_len & ref 比 Case#2 中的结果更丰富

Case#4:根据 age & pos 来查询

EXPLAIN SELECT * FROM staff WHERE age = 22 AND pos = \'HR\';

结果:没有索引,全表扫描

Case#5:根据 name & pos 来查询

EXPLAIN SELECT * FROM staff where name = \'Alice\' AND pos = \'HR\';

结果:和 Case#1 相同(说明 pos 字段没有用上索引)

3 总体分析

Case1,2,3都用了上索引,且使用索引长度依次增加(key_len=53,58,111 且 ref=1个const,2个const,3个const),符合***左前缀法则;

Case4中没有带头大哥(火车头),于是,全表扫描;

Case5中只有 name 字段使用上了索引,中间兄弟(中间车厢)age 断了,于是,后面的兄弟(车厢)pos 挂了;

4 总结

***左前缀法则:带头大哥不能死、中间兄弟不能断

当 select * from T where a = 1 and b = 3 的时候, 数据库系统可以直接从索引文件中直接二分法找到 A = 1 的记录,然后再 B = 3 的记录
但如果你 where b = 3 则需要遍历这个索引表的全部

mysql 建立多列索引(联合索引)有最左前缀的原则,即最左优先,如:

如果有一个 2 列的索引 (col1, col2),则已经对 (col1)、(col1, col2) 上建立了索引;
如果有一个 3 列索引 (col1, col2, col3),则已经对 (col1)、(col1, col2)、(col1, col2, col3) 上建立了索引;
原理

b+ 树的数据项是复合的数据结构,比如 (name,age,sex) 的时候,b+ 树是按照从左到右的顺序来建立搜索树的,比如当 (张三,20,F) 这样的数据来检索的时候,b+ 树会优先比较 name 来确定下一步的所搜方向,如果 name 相同再依次比较 age 和 sex,最后得到检索的数据;但当 (20,F) 这样的没有 name 的数据来的时候,b+ 树就不知道第一步该查哪个节点,因为建立搜索树的时候 name 就是第一个比较因子,必须要先根据 name 来搜索才能知道下一步去哪里查询 比如当 (张三, F) 这样的数据来检索时,b+ 树可以用 name 来指定搜索方向,但下一个字段 age 的缺失,所以只能把名字等于张三的数据都找到,然后再匹配性别是 F 的数据了, 这个是非常重要的性质,即索引的最左匹配特性。(这种情况无法用到联合索引)

mysql 查询优化器

如果建的索引是 (name, cid)。而查询的语句是 cid=1 AND name=’小红’。为什么还能利用到索引?

当按照索引中所有列进行精确匹配(“=” 或 “IN”)时,索引可以被用到,并且 type 为 const。理论上索引对顺序是敏感的,但是由于 MySQL 的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引,所以 MySQL 不存在 where 子句的顺序问题而造成索引失效
注意事项

范围查询 mysql 会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。范围列可以用到索引,但是范围列后面的列无法用到索引。即,索引最多用于一个范围列,因此如果查询条件中有两个范围列则无法全用到索引 like 语句的索引问题 如果通配符 % 不出现在开头,则可以用到索引,但根据具体情况不同可能只会用其中一个前缀 在 like “value%” 可以使用索引,但是 like “%value%” 不会使用索引,走的是全表扫描 不要在列上进行运算 如果查询条件中含有函数或表达式,将导致索引失效而进行全表扫描 例如 select * from user where YEAR(birthday) < 1990 可以改造成 select * from users where birthday <’1990-01-01′ 索引不会包含有 NULL 值的列 只要列中包含有 NULL 值都将不会被包含在索引中,复合索引中只要有一列含有 NULL 值,那么这一列对于此复合索引就是无效的。所以在数据库设计时不要让字段的默认值为 NULL 尽量选择区分度高的列作为索引,区分度的公式是 count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是 1,而一些状态、性别字段可能在大数据面前区分度就是 0。一般需要 join 的字段都要求区分度 0.1 以上,即平均 1 条扫描 10 条记录 覆盖索引的好处 如果一个索引包含所有需要的查询的字段的值,我们称之为覆盖索引。覆盖索引是非常有用的工具,能够极大的提高性能。因为,只需要读取索引,而无需读表,极大减少数据访问量

链接:https://www.jianshu.com/p/9b3406bcb199

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/zgggws.html