帮助mysql提高查询效率的数据结构

优点:

  • 减少磁盘IO次数，加快数据查询速度

缺点:

  • 维护索引需要耗费数据库资源
  • 索引需要占用磁盘空间
  • 当对表的数据进行增删改的时候，因为要维护索引，速度会受到影响

索引分类

InnoDB引擎

  • 主键索引
    • 设定为主键后数据库会自动建立索引，innodb为聚簇索引；主键索引索引列值不能为空
  • 普通索引（单列索引）
    • 一个索引只包含单个列，一个表可以有多个单列索引
  • 唯一索引
    • 索引列的值必须唯一,但允许有空值。但是只能存在一个null
  • 复合索引
    • 一个索引包含多个列

MyISAM

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

基本操作

主键索引

在建表时自动创建

  1. create table t_user(id varchar(20) primary key,name varchar(20))
  3. #查看索引
  4. show index from t_user

普通索引（单列索引）

  1. #建表时创建(随表一起创建的索引，名字同列名一致)
  2. create table t_user(id varchar(20) primary key,name varchar(20),key(name))
  4. #建表后创建
  5. create index nameIndex on t_user(name);
  7. #删除索引
  8. drop index 索引名 on 表名

唯一索引

  1. create table t_user(id varchar(20) primary key,name varchar(20),unique(name))
  3. #建表后创建
  4. create unique index nameIndex on t_user(name);

复合索引

  1. create table t_user(id varchar(20) primary key,name varchar(20),age int,key(name,age))
  3. #建表后创建
  4. create index index name_ageIndex on t_user(name,age);

复合索引遵循最左前缀原则

  • 查询时要用到复合索引，必须是以最左边的为起点任何连续的索引才能匹配上
  • mysql引擎为了更好利用索引，在查询中会动态调整查询字段以便利用索引

索引底层数据结构—B+树

B+Tree是在B-Tree(B树)基础上的一种优化,使其更适合实现外存储索引结构, InnoDB存储引擎就是用B+Tree实现其索引结构。
B-Tree中每个节点中不仅包含数据的key值，还有data值。 而每一个页的存储空间是有限的,如果data数据较大时将会导致每个节点(即一个页)能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大,增大查询时的磁盘I/O次数，进而影响查询效率。
在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上,而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同

  • 非叶子节点只存储键值信息
  • 所有叶子节点之间都有一个链指针
  • 数据记录都存放在叶子节点中

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT (占用4个字节)或BIGINT (占用8个字节) ，指针类型也-般为4或8个字节,也就是说一个页(B+Tree中的一个节点)中大概存储1 6KB/(8B+8B)=1K个键值(因为是估值,为方便计算,这里的K取值为[10] ^3)。也就是说一个深度为3的B+Tree索引可以维护10^3 10^3 10^3= 10亿记录。
实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2~4层。mysql的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

mysql的底层索引实现：首先放入数据时会基于数据进行排序，再以链表的形式把数据都连接起来。同时mysql为了进一步优化，就基于B+树的结构对数据进行了页存储。默认页的大小为16kb，一个三层的B+数可以存储近10亿的数据。在查询时，如果基于主键去查询最多动用1~2次磁盘IO，顶层是常驻内存的。

聚簇索引和非聚簇索引

聚簇索引
将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据

非聚簇索引
将数据与索引分开存储,索引结构的叶子节点指向了数据对应的位置

注意:在Innodb中,在聚簇索引之上创建的索引称之为辅助索引,非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引。辅助索引叶子节点存储的不再是行的物理位置，而是主键值,辅助索引访问数据总是需要二次查找。
image.png

InnoDB

  • InnoDB使用的是聚簇索引，将主键组织到一棵B+树中, 而行数据就储存在叶子节点上即可查找到对应的叶节点，之后获得行数据。若对Name列进行条件搜索,则需要两个步骤:第一步在辅助索引B+树中检索Name,到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作,最终到达叶子节点即可获取整行数据。(重点在于通过其他键需要建立辅助索引)
  • 聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一非空的索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。如果已经设置了主键为聚簇索引又希望再单独设置聚簇索引,必须先删除主键，然后添加想要的聚簇索引，最后恢复设置主键即可。

MYISAM

MyISAM使用的是非聚簇索引， 骤簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已， 主键索引B+树的节点存储了主键,辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

image.png

聚簇索引的优势

  1. 由于行数据和聚簇索引的叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中(缓存器)，再次访问时，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的， 找到叶子节点就可以立刻将行数据返回了,如果按照主键Id来组织数据，获得数据更快
  2. 辅助索引的叶子节点，存储主键值，而不是数据的存放地址。好处是当行数据放生变化时，索引树的节点也需要分裂变化；或者是我们需要查找的数据，在上一次IO读写的缓存中没有，需要发生一次新的IO操作时，可以避免对辅助索引的维护工作，只需要维护聚簇索引树就好了。另一个好处是，因为辅助索引存放的是主键值，减少了辅助索引占用的存储空间大小。

聚簇索引注意事项

当使用主键为聚簇索引时，主键最好不要使用uuid，因为uuid的值太过离散，不适合排序且可能出现新增加记录的uuid,会插入在索引树中间的位置，导致索引树调整复杂度变大，消耗更多的时间和资源。
建议使用int类型的自增，方便排序并且默认会在索引树的末尾增加主键值,对索引树的结构影响最小。而且，主键值占用的存储空间越大，辅助索引中保存的主键值也会跟着变大，占用存储空间，也会影响到I0操作读取到的数据量。
聚簇索引的数据的物理存放顺序与索引顺序是一致的， 即:只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id,那么可以想象，不断地调整数据的物理地址、分页。如果是自增的，只需要一页一页地写，索引结构相对紧凑，磁盘碎片少,效率也高。

索引失效场景

  • 查询语句中使用LIKE关键字
    • 在查询语句中使用LIKE关键字进行查询时，如果匹配字符串的第一个字符为% ,索引不会被使用。如果%不是在第一个位置，索引就会被使用。
  • 查询语句中使用多列索引（遵循最左前缀原则）
    • 多列索引是在表的多个字段上创建一个索引， 只有查询条件中使用了这些字段中的第一个字段， 索引才会被使用
  • 查询语向中使用OR关键字
    • 查询语句只有OR关键字时，如果OR前后的两个条件的列都是索引，那么查询中将使用索引。如果OR前后有一个条件的列不是 索引，那么查询中将不使用索引。