InnoDB原理中：B+树索引的使用

文章目录

[隐藏]

• 索引的优点
• 什么时候使用索引
• 优化器对索引的部分影响
• 索引的使用规则
  • 一、索引本身的常识规则和B+树特性得出的基础规则
  • 二、利用聚簇索引的特性

前言：自己的对知识的复习和归纳，参考网上资料和书籍（Mysql高性能），部分概念便于理解会做简单处理。

索引的优点

• 通过索引快速定义到数据，大大减少了需要扫描的数据量。
• 避免排序和临时表（索引的有序性）
• 将随机I/O变为顺序I/O(磁盘预读取、节点大小==磁盘页、索引的有序性、以及聚簇索引)

什么时候使用索引

• 数据量大，需要索引，并且重复率低。否则，全表扫描效率更高。

优化器对索引的部分影响

目标：给定一个SQL，查找SQL最优（局部最优）的执行路径，使得用户能够更快的得到SQL的执行结果。

对索引的影响：

• 如果优化器判断使用索引的路径更长，会跳过索引，使用全表扫描；

可以使用force关键字强制使用索引

索引的使用规则

一、索引本身的常识规则和B+树特性得出的基础规则

删除不使用的索引

1.选择性：记录的重复比例越低，查询效率越高，所以将选择性高的列放在最前面；
2.所以的数据类型、长度越短越好

• 整型类型快于字符类型
• 使用内置的日期和时间类型（本质是整型）快于字符串

3.字段类型尽量设置为非空：

原则：即使要在表中储存「没有值」的字段，还是有可能不使用 NULL 的。考虑使用 0、特殊值或空字符串来代替它。
原因：Mysql难以优化引用可空列查询，它会使索引、索引统计和值更加复杂。可空列需要更多的存储空间，还需要mysql内部进行特殊处理。可空列被索引后，每条记录都需要一个额外的字节，还能导致MYisam 中固定大小的索引变成可变大小的索引。

经验教训：

• 空值匹配要使用 not null
• NOT IN子查询在有NULL值的情况下返回永远为空结果，查询容易出错
  select user_name from table_2 where user_name not in (select user_name from table_3 where id!=1) — 子查询中返回 [“congcong”,null],则查询为空；
• 如果有 Null column存在的情况下，count(Nullcolumn)需要格外注意，null 值不会参与统计。
  select CONCAT(“1”,null) from dual; — 执行结果为null。
• 如果在两个字段进行拼接：比如题号+分数，首先要各字段进行非null判断，否则只要任意一个字段为空都会造成拼接的结果为null。

4.覆盖索引：索引列就是要要查询的数据，直接返回，减少回表操作。
5.多列索引的效率 > 多个单列索引
6.索引列不支持左侧是函数
7.从左匹配：

• 对于特别长的字段，可以只索引字段的左边一部分;
• 如果是单列索引，体现在左模糊匹配，即name like ‘%xxxx’;
• 如果是多列索引，（code,name,age,）,从左边的索引开始匹配，不会跳过中间的索引列；

8.区间查询
9.多列索引中，中间某列是范围查询，则右边的列都无法使用索引
10.利用B+索引的有序性来排序,来提高ORDER BY和GROUP BY的速度

二、利用聚簇索引的特性

1.主键索引的生成要有序，插入的时候按主键索引的顺序的插入。
2.充分利用主键索引直接获取数据来提高性能。
3.InnoDB通过主键聚集数据，如果没有定义主键，InnoDB将选择一个没有空值的列创建聚簇索引，所以一定要创建主键索引。

原文出处：juejin -> https://juejin.im/post/5d21ecf86fb9a07ecd3d7f10