目录
- 概述
- 列的散列性
- 最左匹配原则
- 联合索引选择原则
- 覆盖索引
- 匹配列前缀原则
概述
这是关于php进阶到架构之Mysql进阶学习的第四篇文章:mysql索引详解
第一篇:mysql共享锁及排它锁第二篇:mysql事务及隔离级别第三篇:mysql底层BTree与B+Tree实现原理第四篇:mysql索引详解
列的散列性
什么是列的散列性呢?
比如user(用户表)中的username字段,该列的散列性是多少呢?
第一步:查询该列不重复的个数
countA = select count(distinct username) from user
第二步:查询表格的行数countB = select count(*) from user
第三步:username的散列值
username字段的散列值 = countA/countB
散列性值越大,作为索引的效果越好。
比如user表的status状态值取0或1,为何status不做索引呢?
原因就是status的散列值比较低。
最左匹配原则
对索引中关键字进行对比,一定是从左往右依次进行,且不可跳过比如下面的user表
CREATE TABLE `user` (`id` INT (11) NOT NULL AUTO_INCREMENT,`name` VARCHAR (255) DEFAULT NULL,`score` INT (11) DEFAULT NULL,PRIMARY KEY (`id`),KEY `name_score` (`name`, `score`)) ENGINE = INNODB AUTO_INCREMENT = 8 DEFAULT CHARSET = utf8
user表中id为主键索引,name_score(name,score)为多列索引
执行下面的sql语句
explain SELECT * FROM `user` WHERE score=100
索引类型为index。你可能会疑问最左匹配原则,应该不使用索引才对,为何使用索引了呢?
index:这种类型表示是mysql会对整个该索引进行扫描。
要想用到这种类型的索引,对这个索引并无特别要求,只要是索引,或者某个复合索引的一部分,mysql都可能会采用index类型的方式扫描。
但是呢,缺点是效率不高,mysql会从索引中的第一个数据一个个的查找到最后一个数据,直到找到符合判断条件的某个索引。
explain SELECT * FROM `user` WHERE score=100 AND name='tom'
运行结果如下:
type为ref,表示使用了索引name_score(name,score)
大家可能会疑问查询条件WHERE score=100 AND name='tom'是先查询score然后查询name,不是不满足最左原则吗?
其实WHERE score=100 AND name='tom'和WHERE name='tom' AND score=100本质是一样的,mysql内部的优化器会
将WHERE score=100 AND name='tom'
调整为WHERE name='tom' AND score=100
联合索引选择原则
- 经常用的列优先 【最左匹配原则】
- 选择性(离散度)高的列优先【离散度高原则】
- 宽度小的列优先【最少空间原则】
覆盖索引
如果查询列可通过索引节点中的关键字直接返回,则该索引称之为覆盖索引。覆盖索引可减少数据库IO,将随机IO变为顺序IO,可提高查询性能
例:
create index name_score on user(name,score);select name,score from user where name = "gofor";
在索引的子节点命中了 返回的列,就不需要再继续往B+Tree的底部叶子节点 读取数据了,减少了IO操作,提高了查询速度。
这就是为什么不建议select * from table 的原因,需要什么列就查什么列,可能会命中覆盖索引,这样就会极大的提高查询效率。
匹配列前缀原则
匹配列前缀可用到索引 like xxx%,like %xxx%、like %xxx大部分情况用不到索引(覆盖索引除外);