面试必问：一文弄懂MySQL数据库索引之底层数据结构和索引类型

前言

在工作和面试中，我们总是离不开SQL语句，而SQL语句跟索引是息息相关的。
在工作中，会有一些慢SQL执行速度很慢，需要优化，怎么优化呢？先看有没有索引，然后看有没有使用到索引。
如果没有使用索引，SQL就会全表扫描，速度会变慢。
这篇文章主要是介绍索引、底层数据结构。

一、索引

索引是将查找速度变快的数据结构。
索引是帮助MySQL高效获取数据的排好序的数据结构。
举个栗子：索引好比是一本书的目录，能加快数据库的查询速度。

1.1作用

快速查询、更新表中数据。

1.2特点

数据结构、排序、查找数据变快

1.3使用

1.3.1创建索引

在创建表的时候添加索引

CREATE TABLE mytable(  
    ID INT NOT NULL,   
    name VARCHAR(16) NOT NULL,  
    INDEX [indexName] (name(length))  
); 

在创建表以后添加索引

-- 修改表结果添加索引
ALTER TABLE my_table ADD [UNIQUE] INDEX index_name(column_name);
或者
CREATE INDEX index_name ON my_table(column_name);

1.3.2删除索引

DROP INDEX [indexName] ON mytable; 
或者
ALTER TABLE table_name DROP INDEX index_name;

1.3.3查看表中的索引

SHOW INDEX FROM tablename

1.3.4查看SQL语句使用索引的情况

explain SELECT * FROM table_name WHERE id='123';

二、数据结构

2.1Hash表

Hash表，在Java中的HashMap，TreeMap就是Hash表结构，以键值对的方式存储数据。我们使用Hash表存储表数据Key可以存储索引列，Value可以存储行记录或者行磁盘地址。Hash表在等值查询时效率很高，时间复杂度为O(1)；但是不支持范围快速查找，范围查找时还是只能通过扫描全表方式。

2.2二叉树(Binary Search Trees)

2.2.1特点

每个节点最多有2个分叉，左子树和右子树数据顺序左小右大。
注：左子树所有结点的值小于它的父结点的值，右子树所有结点的值大于它的父结点的值。

2.2.2不适合的场景和存在的问题

当二叉树为单边增长的数据链时，二叉树退化为链表,查询效率很低。

2.3红黑树(Red-Black Trees)

2.3.1特点

红黑树也叫平衡二叉树，在二叉树的基础上，对树的高度进行了限制，从而减少了查找比较的次数，提高查询效率。
采用二分法思维，除了具备二叉树的特点，最主要的特征是树的左右两个子树的层级最多相差1。在插入删除数据时通过左旋/右旋操作保持二叉树的平衡，不会出现左子树很高、右子树很矮的情况。在Java中的HashMap有用到红黑树。

2.3.2不适合的场景

因为数据量非常大的情况下（五百万数据），红黑树会非常高，假如查找的数在最底层的叶子结点，那么查找速度会因为磁盘IO的次数增加而增加，查询效率会很慢。

2.3.3存在的问题

1.时间复杂度和树高相关。树有多高就需要检索多少次，每个节点的读取，都对应一次磁盘 IO 操作。树的高度就等于每次查询数据时磁盘 IO 操作的次数。磁盘每次寻道时间为10ms，在表数据量大时，查询性能就会很差。（1百万的数据量，log2n约等于20次磁盘IO，时间20*10=0.2s）
2.红黑树不支持范围查询快速查找，范围查询时需要从根节点多次遍历，查询效率不高。

2.4B树(B Trees)

MySQL的数据是存储在磁盘文件中的，查询处理数据时，需要先把磁盘中的数据加载到内存中，磁盘IO 操作非常耗时，所以我们优化的重点就是尽量减少磁盘 IO 操作。访问二叉树的每个节点就会发生一次IO，如果想要减少磁盘IO操作，就需要尽量降低树的高度。那如何降低树的高度呢？在红黑树的基础上，进行改造。
存更多的索引元素，并且树的高度控制在一定范围内。

2.4.1 特点

1.叶节点具有相同的高度，叶节点的指针为空
2.所有索引元素不重复
3.节点中的数据索引从左到右递增排序

2.5B+树(B+ Trees)

在B树的基础上进行改造。

2.5.1特点

1.非叶子节点不存储data,只存储索引，可以放更多的索引
2.叶子节点包含所有索引字段
3.叶子节点用指针连接，提高区间访问的性能

2.5.2B树和B+树的区别

B树：非叶子节点和叶子节点都会存储数据。
B+树：只有叶子节点才会存储数据，非叶子节点至存储键值。叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。

2.5.3B+树相比B树的优势

1.单一节点存储更多的元素，使得查询的IO次数更少；
2.所有查询都要查找到叶子节点，查询性能稳定；
3.所有叶子节点形成有序链表，便于范围查询。

2.6数据结构模拟网站

这里有一个可以模拟以上五种数据类型的网页。
https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

三、索引类型（七种）

3.1普通索引Key

索引列中的值不用唯一的，允许为空值。
补充：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值。

3.2唯一索引Unique

索引列中的值必须是唯一的，但是允许为空值。

3.3主键索引Primary Key

索引列中的值必须是唯一的，不允许有空值。
补充：不能重复，不能为null，主键索引是特殊的唯一索引。
聚簇索引:
InnoDB存储引擎的表会存在主键（唯一非null），如果建表的时候没有指定主键，则会使用第一个非空的唯一索引作为聚集索引，否则InnoDB会自动帮你创建一个不可见的、长度为6字节的row_id用来作为聚集索引。

3.4全文索引FULLTEXT

索引列中的值不用唯一的，允许为空值。
但是只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。 MyISAM和InnoDB中都可以使用全文索引。
大文本、消息、url解决模糊查询（like）

3.5空间索引SPATIAL

MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。
空间索引是对空间数据类型的字段建立的索引，空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING和POLYGON。MySQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类似的语法创建空间索引，创建空间索引的列必须声明为NOT NULL。

3.6前缀索引

在文本类型如CHAR,VARCHAR,TEXT类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。

3.7其他索引

按照索引列数量分类

3.7.1单列索引

一个索引只包含单个列

3.7.2组合索引(联合索引)

索引列中值的组合必须唯一。
组合索引指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。
组合索引的使用，需要遵循最左前缀匹配原则（最左匹配原则）。
一般情况下在条件允许的情况下使用组合索引替代多个单列索引使用。