Mysql——索引

一、什么是索引

优点：

1. 提高了数据检索的效率
2. 降低了磁盘IO的成本
3. 索引会对数据进行排序降低了排序成本

缺点：

1. 索引会占用磁盘空间
2. 索引提高了查询速度，但是增删改操作同时还需要更新索引文件速度降低。

二、InnoDB引擎的逻辑结构和物理结构

在介绍索引的分类及结构之前我们先了解一下Mysql默认存储引擎的逻辑结构和物理结构

1. 存储结构

由大到小分别是：表空间>段>区>页>行

• 表空间：所有的数据都存储在表空间中。
  • 共享表空间：InnoDB引擎有一个默认的共享表空间用来存储undolog信息、事务信息等。
  • 独立表空间：每个表对应一个独立的表空间，用来存储数据、索引等信息。
• 段：多个段组成了表空间，而一个段又管理着多个区。段有三种：
  • 数据段：存放B+树的叶子结点
  • 索引段：存放B+树的非叶子结点
  • 回滚段：存放撤销信息
• 区：由多个连续页组成，一个区的大小固定为1MB,一个区中可以存储64个页。
• 页：InnoDb磁盘管理的最小单元，每个页大小默认16K,为了保证页的连续性，每次申请4-5个区。
• 行：InnoDB存储引擎是按行存储。每个页最多可存储16KB/2-200行（16*1024/2-200行，即7992行记录）。

2. 物理结构

InnoDB引擎按照物理结构划分可以分为：内存结构、磁盘结构

1. 内存结构：包括Buffer Pool、Change Buffer、自适应hash索引（Adaptive Hash Index）、Log Buffer
   • Buffer Pool缓冲池：缓存表中数据、索引，降低磁盘IO次数。
     以页为单位存储数据，常见的管理算法是LRU算法，其中InnoDB就是采用的LRU算法并对其进行了优化。
     LRU算法又称为最近最远算法，按照5:3的比例将缓冲池分为年轻代（头部存放经常访问的数据）、老年代（尾部存放很少被使用的数据）。
   • Change Buffer写缓冲
     在辅助索引发生改变时，如果辅助索引在buffer pool里面就会直接进行修改。如果辅助索引页不在buffer pool里，则由Change Buffer先缓存这些辅助索引页的变更动作。
     change buffer既包含内存结构，也包含磁盘结构。在内存中，Change Buffer是缓冲池的一个组成部分。在磁盘上，Change Buffer是system tablespace（系统表空间）的一部分。
   • 自适应哈希索引
     InnoDB存储引擎会监控对表上各索引页的查询，如果观察到建立hash索引可以提高查询速度，则自动建立hash索引
   • Log Buffer
     在内存中，保存日志文件数据。 日志缓冲区的内容定期刷新到磁盘。事务提交后，必须将事务对数据页的修改刷(fsync)到磁盘上，才能保证事务的ACID特性。 较大的日志缓冲区使大型事务可以运行，而无需在事务提交之前将redo日志数据写入磁盘。
2. 磁盘结构：表、索引、表空间、双写缓冲区Doublewrite Buffer、重做日志 Redo Log、回滚日志 Undo Log

二、索引的分类及结构

不同的存储引擎索引的结构也不相同，以下是几种常见的存储引擎：

1. 存储引擎

在mysql服务中负责存储数据、建立索引、操作数据。

• InnoDB引擎： MySQL 5.5及之后，InnoDB设定为了 MySQL默认 存储引擎。
  • 支持事务：事务的完整提交和回滚
  • 支持外键：保证数据的完整性
  • 支持行级锁：提高了并发访问性能
• MyISAM存储引擎：
  • 不支持事务、外键、行锁
  • 支持表锁，更新操作会锁住整张表，读和写不能同时进行
  • 适用于单方面读或者写的业务，分开读写操作速度快，同时并发量不高的业务场景
• MEMORY存储引擎：
  • 数据存储在内存中，所以处理速度非常快，但是服务器关闭表数据就会被清空。
  • 支持哈希索引

2. 索引的分类

• 按照数据结构划分：B+树索引、Hash索引
  数据结构有很多种包括二叉树、红黑树、B树、B+树，为什么我们要选择B+树作为InnoDB引擎的索引，下面分别介绍下几种树

  • 二叉查找树：二叉树的特点是每个结点有两个分支，左小右大。但是如果根结点值小很容易形成单链表，这样的索引依旧是全表扫描效率低，所以我们不考虑将二叉树作为索引结构。
  • 红黑树：红黑树又叫做二叉平衡树，解决了二叉查找树一边倒的问题。但是如果插入大量数据会造成红黑树的高度过高。
    • 根结点和叶子结点都是黑色的
    • 每个结点要么是黑色要么是红色
    • 所有路径上的黑色结点数相同
  • 哈希索引：采用数组+链表的方式来存储，用hash函数计算出键值，如果出现冲突在数组冲突位置上拉出一个链表，因为hash索引是根据键值来匹配的，范围查询并不支持。
  • B树：多路自平衡搜索树，允许每个节点拥有多个子结点。每个结点包含：键值key（表中记录的主键）、数据、指针。InnoDB引擎是以页为磁盘管理的最小单位，页的大小为16KB，因为一个磁盘快的空间没有那么大，所以每次都会申请若干个连续的地址空间。
  • B+树：数据库中最常见最频繁使用的一种索引，B+树是对B树的一种优化，因为页的空间大小是固定的，B树中每个节点不仅包含 键值key，还包含数据，如果数据很大占用空间大，键值数量就会很少，那么树的层数就会增多，查询时就会增加磁盘IO影响效率。B+树优化了这一缺点，只用叶子结点来存取数据，非叶子结点只存储键值key并按照键值大小排序，增加了每个结点存储键值的数量，减少了树的层数。

• 按照物理存储划分：聚集索引、非聚集索引

  • 聚集索引：
    • 在每个表中都必须有且只有一个聚集索引
    • 默认情况下使用主键作为聚集索引，没有主键使用唯一索引作为聚集索引，如果都没有mysql会默认生成一个隐藏的row_id作为聚簇索引
    • 聚集索引的叶子结点保存了一行的数据
  • 非聚集索引（二级索引）：
    • 一个表中可以有多个二级索引，一般对非主键列建立的索引就是二级索引
    • 非聚集索引叶子结点保存的是主键
    • 二级索引在查找时会涉及到回表查询

• 按照字段特性分类：主键、唯一、普通、前缀

  • 主键索引：创建主键时系统自动创建的索引。
  • 唯一索引：索引列中的值必须是唯一的，如果是组合索引，组合列的值必须是唯一的。
  • 普通索引：无约束
  • 前缀索引：当字段类型为字符串时，有时候需要索引很长的字符串，会让索引变得很大，查询时浪费大量的磁盘io，影响查询效率。此时可以取索引前缀，截取部分字符串作为索引。

• 按照字段数量分类：单列索引、联合索引

  • 单列索引：建立在单个列上的索引为单列索引，单列索引有：普通索引，唯一索引，主键索引
  • 联合索引：一个索引包含多个列（联合索引的使用要遵循最左前缀法则）

3. 索引使用原则

最左前缀法则：对于联合索引，索引匹配时必须从索引的最左列开始，并且不能跳过中间列。

4. 索引失效场景

• 范围查询：在联合索引中使用了范围查询，范围查询后面的列会失效，尽量使用>=查询
• 模糊查询：只要前面加了百分号就会失效
• 函数:不要在索引列上做操作，函数，索引会失效 字符串：字符串类型不佳引号索引会失效
• or连接：如果前面的列有索引，后面的无，那么索引全部都会失效