数据库索引知识到MySQL InnoDB

前言

本文聊聊数据库中的索引，涉及索引基础数据结构，分类。以及使用索引的缺点。

索引就像一本书的目录，商场里面各个楼层指示图，让我们不需要自己无目的的找，而是能够很快的找到自己想要的。

1. 索引的基础知识

题外话： 数据结构和算法在一般的工作职位中可能遇不到，但是可以看到在源码级、中间件级别还是打交道比较多的。数据结构和算法肯定是一名程序员的基本功的，对于理解源码和设计思想还是很有帮助的。

二叉树查找树： 左子树比根节点小，右子树比根节点大。极端情况下的插入会形成"链状结构",造成倾斜,这样查找时的时间复杂度很高，完全是遍历级别的。为了平衡有了AVL 平衡二叉树。

平衡二叉树： 特殊二叉树，每次插入时都会将其调整成一个平衡的树。平衡就是左右子树高度差不大于1。但是另一个问题就是一个节点只有两个子节点，能否存更多的节点？从而让树的高度变低呢，让一层节点更多，也就是这棵树更矮更胖？那就是B树

B树： 也叫B-树。简单理解的话就是让一层的节点更多了。当然也是有规则的。一个m阶的B-Tree有如下特性

1、每个结点最多m个子结点。

2、除了根结点和叶子结点外，每个结点最少有m/2（向上取整）个子结点。

3、如果根结点不是叶子结点，那根结点至少包含两个子结点。

4、所有的叶子结点都位于同一层。

5、每个结点都包含k个元素（关键字），这里m/2≤k<m，这里m/2向下
取整。

6、每个节点中的元素（关键字）从小到大排列。

7、每个元素（关键字）字左结点的值，都小于或等于该元素（关键字）。右结点的值都大于或等于该元素（关键字）。

单纯的B-树，节点就是一个普通的数字。但是在数据库使用B-树的存储中是把节点以key-value 的形式，然后再从上到下找的时候找到了关键字，也就找到了数据。但是B-Tree不太适合范围查找？那么适合范围查找的B+Tree是怎么样的呢？

B+树： B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构。主要区别就是数据都放在了叶子节点上，非叶子节点只有key的信息。非叶子节点也能在叶子节点中找到。叶子节点上有了链指针相连，方便范围查找。根据局部性原理，范围查找需要也是很多的。

总结B-Tree和B++Tree:

操作系统从磁盘读取数据到内存是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理： 当一个数据被用到时，其附近的数据也通常会马上被使用。
  预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k）。

• B-Tree因为非叶子结点也保存具体数据，所以在查找某个关键字的时候找到即可返回。而B+Tree所有的数据都在叶子结点，每次查找都得到叶子结点。所以在同样高度的B-Tree和B+Tree中，B-Tree查找某个关键字的效率更高。
• 由于B+Tree所有的数据都在叶子结点，并且结点之间有指针连接，在找大于某个关键字或者小于某个关键字的数据的时候，B+Tree只需要找到该关键字然后沿着链表遍历就可以了，而B-Tree还需要遍历该关键字结点的根结点去搜索。

2. MySQL InnoDB 索引

聚簇索引 ？ 非聚簇索引？

聚簇索引：由自增主键建成的索引。自增主键构成关键词做key，数据value是一行的数据。那如果一个表没有主键会怎么样？在innodb中，如果一个表没有主键，那默认会找建了唯一索引的列，如果也没有，则会生成一个隐形的字段作为主键

非聚簇索引：其他字段构成的索引，比如用户名，通过用户名做关键词key，数据value则是数据的聚集索引的key。所以通过非聚集索引查找的过程是先找到该索引key对应的聚集索引的key，然后再拿聚集索引的key到主键索引树上查找对应的数据，这个过程称为回表！

其中复合索引、前缀索引、唯一索引，都是属于非聚簇索引。聚簇索引在InnoDB中就是自增主键构成的索引。这个聚簇索引，在Mysql中是没有语句来另外生成的。

MyISAM的存储引擎，在叶子节点数据value中存放的不是记录，而是记录的地址。而且数据的存储不是按主键顺序存放的，按写入的顺序存放。

也就是说innodb引擎数据在物理上是按主键顺序存放，而MyISAM引擎数据在物理上按插入的顺序存放。并且MyISAM的叶子结点不存放数据，所以非聚集索引的存储结构与聚集索引类似，在使用非聚集索引查找数据的时候通过非聚集索引树就能直接找到数据的地址了，不需要回表，这比innodb的搜索效率会更高

3. MySQL 中的索引的使用

• 主键索引
  • 使用方式：PRIMARY KEY（主键索引）
• 覆盖索引
  • 不需使用回表操作的索引。查询列表⾥只包含索引列。
  • 比如在username上建立了索引，执行的sql语句就是select id from user where username = "XX"; 上面介绍了主键id就是叶子节点的value，这样少量回表的操作，非常高效。
• 唯一索引
  • 使用方式：UNIQUE(唯一索引)
  • 索引列的值必须唯一，但允许有空值
• 单列索引
  • 为某个字段创建索引
  • INDEX(普通索引)
• hash索引
  • InnoDB 不支持手动创建，但是InnoDB会有自调优创建。可通过配置是否开启。
  • 适用场景：使用随机生成的字符id，比如UUID
• 联合索引
  • 使用方式：ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 )
  • 为多个字段创建索引，重点是最左匹配。所谓最左原则指的就是如果你的 SQL 语句中用到了联合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个联合索引去进行匹配，值得注意的是，当遇到范围查询(>、<、between、like)就会停止匹配。
• 全文索引
  • 使用方式：FULLTEXT(全文索引)
  • 关键字时都可以搜索到这条记录.那么只能使用like关键字.而对于like我们知道,当不是用左边前缀搜索的时候,无法命中索引,因此对于这条语句select * from articles where content like '%贯彻%',MySQL只能进行全表扫描,逐一进行匹配.这样的效率极其低下.而全文索引呢,通过建立倒排索引,可以极大的提升检索效率.

4. 索引有那些缺点

• 空间上的代价
  • 每建⽴⼀个索引都要为它建⽴⼀棵B+树，每⼀棵B+树的每⼀个节点都是⼀个数据⻚，⼀个⻚默认会占⽤16KB的存储空间，⼀棵很⼤的 B+树由许多数据⻚组成，会是⼀⽚很大的存储空间
• 时间上的代价
  • 每次对表中的数据进⾏增、删、改操作时，都需要去修改各个B+树索引。

tips：一个表建的索引尽量不要超过5个、尽量不要在重复数据多的列上建索引。尽量使用覆盖索引。

参考博客

• https://www.cnblogs.com/sujing/p/11110292.html