共计 2855 个字符,预计需要花费 8 分钟才能阅读完成。
索引笼罩
在之前《大白话 mysql 之深入浅出索引原理 – 上》这篇文章中提到过,mysql 的 innodb 引擎通过搜寻树形式实现索引,索引类型分为主键索引和二级索引(非主键索引),主键索引树中,叶子结点保留着主键即对应行的全副数据;而二级索引树中,叶子结点保留着索引值和主键值,当应用二级索引进行查问时,须要进行回表操作。如果咱们当初有如下表构造。
CREATE TABLE `user_table` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`password` varchar(255) DEFAULT NULL,
`age` int(11) unsigned Not NULL,
PRIMARY KEY (`id`),
key (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
执行语句 (A) select id from user_table where username = '张三'
时,因为 username 索引树的叶子结点上保留有 username 和 id 的值,所以通过 username 索引树查找到 id 后,咱们就曾经失去所需的数据了,这时候就不须要再去主键索引上持续查找了。
执行语句(B) select password from user_table where username = '张三'
时,流程如下
- username 索引树上找到
username='张三'
对应的主键 id。 - 通过回表在主键索引树上找到满足条件的数据。
由下面可知,当 sql 语句的所求查问字段(select 列)和查问条件字段(where 子句)全都蕴含在一个索引树中,能够间接应用索引查问而不须要回表。这就是笼罩索引,通过应用笼罩索引,能够缩小搜寻树的次数,是罕用的性能优化伎俩。
例如下面的语句 B 是一个高频查问的语句,咱们能够建设 (username,password) 的联结索引,这样,查问的时候就不须要再去回表操作了,能够进步查问效率。当然,增加索引是有保护代价的,所以增加时也要衡量一下。
最左前缀准则
联结索引的多个字段中,只有当查问条件为联结索引的第一个字段时,查问能力应用该索引。
持续以下面的例子来阐明,为了进步语句 B 的执行速度,咱们增加了一个联结索引(username,password), 特地留神这个联结索引的程序,如果咱们颠倒下程序改成(password,username), 这样查问能应用这个索引吗?答案是不能的!
咱们晓得 B + 树中的各个节点是有程序的,在联结索引中是依据索引的第一个字段进行排序构建索引树的(当第一个字段雷同时,按第二个字段进行排序)。所以只有当查问条件为联结索引的第一个字段时,查问能力应用该索引。
索引能够依据字段值最左若干个字符进行含糊查问。
当初,假如咱们有一下三种查问情景:
- 查出用户名的第一个字是“张”结尾的人的明码。即查问条件子句为 “where username like ‘ 张 %'”
- 查处用户名中含有“张”字的人的明码。即查问条件子句为 “where username like ‘% 张 %'”
- 查出用户名以“张”字结尾的人的明码。即查问条件子句为 “where username like ‘% 张 '”
以上三种状况下,只有第 1 种可能应用(username,password)联结索引来放慢查问速度。
语句
select id, username from user_table where username like '% 张 %'
是否应用到 (username) 索引?答案是能够的,因为查问的所有字段 (id, username) 在二级索引(username)中都存在,二级索引树比主键索引树小很多,所以会间接遍历二级索引。值得注意的是,这里是遍历整个索引树,而不是在索引树中疾速定位数据。
前缀索引
当初,咱们有一个须要依据 email 字段查找用户信息的需要,当然咱们能够间接给 email 字段创立一个索引,但咱们认真想想,有必要为整个 email 字段创立索引吗?
其实没必要的,因为邮箱地址是有一个格局的,都是 ”xxxx@xxx.com”, 所以其实 email 字段的前面几位区分度不高。这时为整个 email 字段创立索引很节约空间,咱们能够创立前缀索引,将字段的前几个字符作为索引即可。mysql 中应用 ADD KEY (column_name (prefix_length))
为字段创立前缀索引。
适合的前缀索引长度
前缀索引设计的好坏在于抉择适合的前缀索引长度。如果抉择太长,会造成索引空间的节约;如果抉择太短,会导致索引树大量反复的 key,索引成果不现实。
当执行 select * from user_table where email = '1111aaaa@xx.com'
时,通过搜寻前缀索引树,会搜到 4 个 1111 结尾的数据结点,并将这 4 个进行回表查问,筛出满足条件的 row1。所以,前缀索引长度抉择过短,会减少回表查问的行数,影响查问效率。
确定前缀索引的长度,咱们能够通过比拟 count(distinct column_name)
和count(distinct LEFT(column_name, prefix_length))
的值。两者靠近示意 prefix_length 比拟正当。
前缀索引的毛病
因为前缀索引是取前几个字符去排序构建的索引树,不保障残缺字段的排序,因而前缀索引无奈用于对字段排序(order by column_name)。
前缀索引没有残缺的字段信息,匹配到后必须回表查问能力确定查问后果。所以没法利用索引笼罩来进步查问性能。
索引下推
对于 user_table 表,咱们当初有(username,age)联结索引
如果当初有一个需要,查出名称中以“张”结尾且年龄小于等于 10 的用户信息,语句 C 如下:”select * from user_table where username like ‘ 张 %’ and age > 10″.
语句 C 有两种执行可能:
1、依据(username,age)联结索引查问所有满足名称以“张”结尾的索引,而后回表查问出相应的全行数据,而后再筛选出满足年龄小于等于 10 的用户数据。过程如下图。
2、依据(username,age)联结索引查问所有满足名称以“张”结尾的索引,而后间接再筛选出年龄小于等于 10 的索引,之后再回表查问全行数据。过程如下图。
显著的,第二种形式须要回表查问的全行数据比拟少,这就是 mysql 的索引下推, 在索引遍历过程中,对索引中蕴含的字段先做判断,间接过滤掉不满足条件的记录,缩小回表次数。
索引下推个性是 mysql5.6 引入的,默认启用,咱们也能够通过批改零碎变量 optimizer_switch 的 index_condition_pushdown
标记来管制
SET optimizer_switch = 'index_condition_pushdown=off';
写在最初
喜爱本文的敌人,欢送关注公众号「会玩 code」,专一大白话分享实用技术