mysql-索引

(Updated: )
mysql

mysq索引

索引是什么？索引本质上是对数据的目录

1.索引的分类

我们可以从四个方面对索引进行分类：

  • 按[数据结构]划分：B+Tree 、Hash、Full-text索引
  • 按[物理存储]划分：聚簇索引、二级索引
  • 按[字段特性]划分：主键索引、唯一索引、普通索引、前缀索引
  • 按[字段个数]划分：单列索引、联合索引

1.1 B+Tree

我们知道mysql底层有三种执行引擎：

Innodb Memory MyISAM
✅ ✅ ✅
三种执行引擎都是支持B+Tree索引的

接下来我们详细介绍一下该索引

Innodb默认的索引就是B+Tree，在创建表时有三种策略：

  1. 如果主键，会默认将主键作为索引值
  2. 如果没有主键，则会选择第一个不包含null的字段作为索引值
  3. 在以上两者都没有的情况下，则会创建一个隐式自增的字段作为索引值

B+树是一个多叉树，其节点可以分为叶子节点和非叶子节点，其中叶子节点存放具体数据，非叶子节点存放索引

B+树在数据量很大(千万级别)，仍能保持三～四层树高，因此能保证较低的磁盘IO开销

其中叶子节点还需要区分主键索引和二级索引，主键🆚二级：

  • 主键索引：叶子节点中的val包含了完整的行数据
  • 二级索引：叶子节点的val只包含了主键值

这就代表这二级索引往往需要回表操作：

1
select * from user where age = 10 and salary > 20000

对于这一行sql语句而言，假设我们建立了二级索引(age, salary)我们可以知道，这个索引是会生效的(索引失效是后话)，对于该二级索引，对应的B+树结构应该是：

1
2
key：（age， salary）
val：（id）

在这种情况下，select要求的是所有字段的数据，二级索引找到后还需要根据主键id进行回表查找，返回整个行数据

但是不是所有二级索引都需要回表的，假如我们把上述的sql改成

1
select age, salary from user where age = 10 and salary > 20000

这种情况下就不需要回表了，因为二级索引查找到后已经能够返回所有数据了。

B+Tree 🆚 BTree：

  • 区别一：B+Tree的非叶子节点只存放索引，这样可以让树更加扁平，降低磁盘io开销
  • 区别二：B+Tree的叶子节点之间由双向链表组织，在范围查询时可以直接通过这个链表遍历，而BTree只能通过树的遍历方式

1.2 联合索引的最左匹配原则

对于联合索引而言，存在最左匹配原则 

1
2
3
4
5
6
7
8
9
# 对于索引(a, b, c)而言
select * from user where a = 1 and b = 1 and c = 1;
select * from user where a = 1 and c = 1;
select * from user where a = 1 and b = 1;
# 这些都是生效的
select * from user where b = 1 and c = 1;
select * from user where b = 1;
select * from user where c = 1;
# 这些都是不生效的

具体原因分析：
对于联合索引而言，索引键排序是按照设定的联合索引顺序依次比较的，也就是说对于联合索引(a, b, c)是在a相同的情况下比较b，b相同的情况下比较c，这里和字符串比较有些相近

因此我们可以认为b和c这两个字段是局部有序的

接下来我们介绍索引失效的情况：

首先我们需要理解where是怎么完成过滤的，如where a = 1 and b = 2，我们会先找出所有满足a = 1的记录(比如10条)，接下来我们再在这十条里找b = 2的记录

因此对于(a, b)这个联合索引

1
select * from user where a = 1 and b = 2

首先通过索引先找到a = 1的位置，因为对于a = 1的情况下，b 也是有序的，可以根据索引继续快速定位b

1
select * from user where a > 1 and b = 2

先根据a > 1快速定位a，但是由于a > 1的情况下 b并不是有序的，比如(2, 3), (3, 2), (4, 1) 因此无法根据索引快速定位b，因此我们称该联合索引部分命中

我们可以简单理解成范围查询处会中断联合索引，只有等值查询才可以继续使用索引

我们再来看一种情况

1
select * from user where a >= 1 and b = 2

这种情况下也是用到了索引，因为a = 1的时候可以根据索引快速定位b

1
select * from user where name like 'j%' and b = 2

这种情况下也是能命中索引，其实这里的原理和=相同，第一条记录是’j‘在这种情况下b是有序的因此也能使用索引

这里可以和索引下推起来思考，索引下推就是弥补这种情况出现的

1.3 索引区分度

1
区分度 = distinct(column) / count(*)

比如性别只有男/女，因此它的区分度特别低，一般不考虑做索引

2. 索引的建立

那么什么时候我们需要索引，什么时候我们不需要索引呢？

索引的缺点：

  • 需要占用物理空间，数量越大，空间越大
  • 创建索引和维护索引需要消耗时间，这个时间随着数据量增大而增大
  • 会降低表的增删改效率，当每次增删改索引时，B+树需要动态维护

那么什么时候我们需要创建索引呢？

  • 字段具有唯一性特征
  • 经常用于WHERE的字段，如果能提高查询效率可以添加索引
  • 需要GROUP BY和ORDER BY的字段，这样就不用再排序了

什么时候不需要索引？

  • 表的数据量不大
  • 字段的区分度不高
  • 表需要经常进行更新

3. 优化索引的方法

  • 前缀索引优化
    顾名思义就是根据字符串的前几个字符建立索引，而不是全部，这样能够减少一个索引的内存，可以有效提高索引的查询效率
  • 覆盖索引优化
    建立覆盖索引，减少二级索引的回表次数
  • 主键索引自增
    尽量使用自增的字段建立主键索引，这样每次插入一条新数据的时候都是，追加操作，不用重新移动数据，加入使用非自增的主键，有可能导致新的数据插入到某个数据页的中间，导致页分裂，降低效率
  • 防止索引失效
    尽量避免索引失效的查询，具体可见索引失效的常见

4. 索引失效场景

首先我们介绍一下MyISAM和InnoDB两种引擎的B+树区别：

  1. MyISAM的B+树存放的是数据的地址
  2. InnoDB的B+树存放的是实际数据

  • 使用左模糊匹配
    我们知道，B+树的索引是根据索引值进行大小比较的，如果采用左模糊匹配，则无法利用索引的有序性

  • 对索引值进行了表达式或者函数
    索引是对字段的原始值建立的，一旦对索引值进行了表达式或者函数的计算之后，它就无法

  • 对索引发生了隐式变化
    Mysql中的字符串与数字进行比较时，会自动将字符转化为数字然后进行比较，因此这里与索引字段表达式或函数计算一般，发生失效

    1
    2
    select * from user where phone = 12345678910;
    select * from user where id = "1";

    这里两条sql语句哪一条会发生索引失效？答案是第一条，因为会把varchar的phone做一次隐式转化，导致索引失效了

  • 不遵循索引最左匹配原则
    字面意思，上文已经介绍过了

  • WHERE子句中的OR
    其实这个比较好理解

    1
    select * from user where age = 15 or name = "Li Lei";

    简单理解就是，如果只有一个age有索引的话对这个结果没有用，因为根据name还是要全表扫描，只有两个字段都有索引不会使得索引失效

深度分页问题

1
select * from user order by id limit 0, 10;

对于这么一条sql语句我们称之为分页查询，它的底层原理是什么？

  • 根据主键索引, 找到对应的行数据，全部返回给server层
  • server层跳过前offset条记录，读取n条记录

因此我们可以发现一旦offset变得很大的时候，他会带来很大的开销，同时如果更为一下情况

1
select * from user order by user_name limit 60000, 10；

我们可以给user_name建立二级索引，但是这样相比与主键而言又多了回表的开销，因此性能非常低

如何解决？

  • 尽量从业务层面减少深度分页的情况
  • 每次从上次的最大下标处开始select 比如 where id > ?