故事

Appearance

MySql索引 ​

本篇内容基于MySQL的InnoDB存储引擎。

索引的概念 ​

索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。使用索引用于快速找出在某个或多个列中有一特定值的行，所有MySQL列类型都可以被索引，==对相关列使用索引是提高查询操作速度的最佳途径==。

InnoDB存储引擎中的索引都是指BTree索引，MySQL中还有Hash索引,详见官网存储引擎索引类型

12712542020101314214516280980408.png

索引的原理 ​

内容引用自美团技术团队发表的文章MySQL索引原理及慢查询优化

索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的，如果我想找到m开头的单词呢？或者ze开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？

数据库的索引结构需要做的事:每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b+树应运而生。

b树.jpg 如上图，是一颗b+树，关于b+树的定义可以参见B+树，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

b+树的查找过程 ​

如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

b+树性质 ​

1.通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。

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

索引的类别 ​

1.Primary Key(主键索引) ​

主键索引是一种特殊的唯一索引，这个时候需要一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引

InnoDB存储引擎的表，如果建表的时候没有指定主键，则会使用第一非空的唯一索引作为聚集索引，如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个6个字节的列，该列的值会随着数据的插入自增。

在其他存储引擎或其他数据库中主键索引不一定就是聚集索引

2.Unique（唯一索引） ​

唯一索引列的值必须是唯一的，但允许有空值,如果是组合索引，则列值的组合必须是唯一的。

sql
CREATE UNIQUE index 索引名 on 表名(列名)
CREATE UNIQUE index 索引名 on 表名(列名)

3.Key（普通索引） ​

MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值

sql
CREATE index 索引名 on 表名(列名)
CREATE index 索引名 on 表名(列名)

4.组合索引 ​

指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀原则

sql
CREATE index 索引名 on 表名(列名,列名...)
CREATE index 索引名 on 表名(列名,列名...)

5.全文索引 ​

全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建

索引的基本语法 ​

  • 创建
sql
ALTER mytable ADD  [UNIQUE]  INDEX [indexName] ON 表名(列名)
ALTER mytable ADD  [UNIQUE]  INDEX [indexName] ON 表名(列名)
  • 删除
sql
DROP INDEX [indexName] ON 表名;
DROP INDEX [indexName] ON 表名;
  • 查看
sql
SHOW INDEX FROM 表名
SHOW INDEX FROM 表名
  • alter命令
sql
-- 有四种方式来添加数据表的索引：
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
 
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
 
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
 
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。
-- 有四种方式来添加数据表的索引：
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
 
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
 
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
 
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

索引的使用场景 ​

1.适合使用索引 ​

  • 频繁作为查询条件的字段
  • 多表查询中与其他表进行关联的字段,外键关系建立索引
  • 单列/组合索引的选择,在高并发的场景下适合建立组合索引
  • 查询中常用于排序的字段
  • 查询中常用于分组或统计的字段

2.不适合使用索引 ​

  • 频繁更新的字段

  • where条件中用不到的字段

  • 表记录很少

  • 重复记录非常多的表

  • 数据区分不明显的字段,例如性别栏位

索引有效场景 ​

  • 全值匹配的查询,例如根据订单id查询select * from t_order where order_id = '9999676623'

  • 匹配范围值的查询,例如 where id > '123456'

  • 最左匹配原则,如user表的username pwd创建了组合索引那么以下几种都可以命中索引

    sql
    select username from user where username='zhangsan' and pwd ='axsedf1sd'

select username from user where pwd ='axsedf1sd' and username='zhangsan'

select username from user where username='zhangsan'
    select username from user where username='zhangsan' and pwd ='axsedf1sd'

select username from user where pwd ='axsedf1sd' and username='zhangsan'

select username from user where username='zhangsan'

    而

    sql
    select username from user where pwd ='axsedf1sd'
    select username from user where pwd ='axsedf1sd'

    不能命中索引

  • 非前导模糊查询, 例如 where name like 'xiaoming%'

索引失效场景 ​

  • 负向查询会使索引失效,例如 id not in (1,2,3)
  • 在索引字段进行运算会使索引失效,例如计算,函数,类型转换
  • !=或者<>会使索引失效
  • is not null无法使用索引,但是is null可以
  • 前导模糊查询会使索引失效,例如 name like '%xiaoming',但是非前导可以
  • 字符串不加单引号会使索引失效
  • 使用组合索引时不遵循最左匹配原则会使索引失效