mysql索引数据结构图解_深入理解Mysql索引底层数据结构与算法

索引优化面试题分析

分析以下几条SQL的索引使用情况(联合索引)：

1、select * from title where emp_no = "10001" and title = "senior" and from_date = "2019-09-11"；  //可以命中

2、select * from title where title = "senior"； //不能命中

3、select * from title where emp_no > "10001"； // 可以命中

4、select * from title where emp_no > "10001" and title = "senior"；// emp_no走索引 title不走索引

5、select * from title where emp_no > "10001" order by title；//

索引到底是什么

1、索引是为了加速对表中数据行的检索而创建的一种分散存储的数据结构

2、索引存储在文件里

数据库表的相关文件(存储在本地mysql/data/数据库名/)

table_name_MyISam.frm：表结构文件

table_name_MyISam.MYD：数据文件

table_name_MyISam.MYI：索引文件

学习索引之前，先了解下磁盘存取原理

系统从磁盘读取数据到内存时是以磁盘块(block)为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。

InnoDB存储引擎中有页(Page)的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB，可通过参数innodb_page_size将页的大小设置为4K、8K、16K，在MySQL中可通过如下命令查看页的大小：

mysql> show variables like 'innodb_page_size';

而系统一个磁盘块的存储空间往往没有这么大，因此InnoDB每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小16KB。InnoDB在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

磁盘可以旋转，磁头可以如下图方向移动，需要找到某个磁道的某个扇区的数据，那么磁头需要做一个寻道操作，磁盘做一个旋转操作

1、寻道时间(速度慢，费时)

2、旋转时间(速度较快)

3、索引结构

如果需要找到第六行的数据，那么磁盘就需要从第一行第二行逐行进行查找，经历6次磁盘IO操作才能找到需要数据，性能低。

索引类型

1.普通索引：最基本的索引，它没有任何限制。

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

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

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

5.全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。

覆盖索引：如果一个索引包含所有需要查询的字段的值，称为‘覆盖索引’。即只需扫描索引而无须回表。

3.1 二叉树

二叉树具有以下性质：左子树的键值小于根的键值，右子树的键值大于根的键值。

如下图所示就是一棵二叉查找树，

对该二叉树的节点进行查找发现深度为1的节点的查找次数为1，深度为2的查找次数为2，深度为n的节点的查找次数为n，因此其平均查找次数为 (1+2+2+3+3+3) / 6 = 2.3次

会存在类似于线性链表的畸形树，等同于全表扫描，因此索引并不采用二叉查找树。

3.2 红黑树(平衡二叉查找树)

平衡二叉树(AVL树)在符合二叉查找树的条件下，还满足任何节点的两个子树的高度最大差为1。

3.3 HASH

hash索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。Memory引擎默认使用的是此种索引。

存储引擎对所有的索隐列计算出一个哈希码，将哈希码存储在索引中，同时哈希表中保存每个数据行的指针。这样，对于此种索引查找速度是非常快的。出现哈希值碰撞的话，索引会以链表的形式存放多个记录指针到同一个哈希条目中。

举个🌰：

name

age

Jane

28

Peter

20

David

30

假设使用假想的哈希函数f()，生成对应的设想值：

f('Jane') = 2323

f('Peter') = 2456

f('David') = 2400

则哈希索引的数据结构如下：

槽(slot)

值(value)

2323

指向第1行指针

2400

指向第3行指针

2456

指向第2行指针

对于select * from user where `name` = 'Jane'那么直接先算Jane的哈希值，然后根据Jane的hash值2323去找到对应的第一行数据，查询速度相对于B-Tree索引是要快，但是也有一些局限：

hash索引中只有hash值和行数的指针，因此无法直接使用索引来避免读取行，但是因为这种索引读取快，性能影响不明显。

hash索引不是按照索引值顺序存储，无法使用于排序。

不支持部分列匹配查找，这里面是使用索引列的全部内容来计算哈希值，例如(A,B)两列一起建索引，单纯使用A一列，那么就无法使用索引，B-Tree索引的话，因为支持匹配最左前缀，所以这种情况适用性偏好。

哈希索引只支持等值查询，包括=、in()、<=>，不支持where age > 10 这种范围查询。

哈希冲突很多的话，维护索引操作的代价也很高

3.4 BTREE

1、度degree：节点的数据存储个数

2、叶节点具有相同的深度

3、叶节点的指针为空

4、节点中的数据key从左到右依次排列

首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值，data为一行记录中除主键外的数据。对于不同的记录，key值互不相同。

一棵m阶的B-Tree有如下特性：

1. 每个节点最多有m个孩子。

2. 除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子。

3. 若根节点不是叶子节点，则至少有2个孩子

4. 所有叶子节点都在同一层，且不包含其它关键字信息

5. 每个非终端节点包含n个关键字信息(P0,P1,…Pn, k1,…kn)

6. 关键字的个数n满足：ceil(m/2)-1 <= n <= m-1

7. ki(i=1,…n)为关键字，且关键字升序排序。

8. Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki，但都大于k(i-1)

可以把度设置无限增大吗？

：每次磁盘IO传输的数据是页(4kb)的整数倍，且有上限。每次磁盘IO读取时将会当前节点的全部数据加载到内存，如果节点数据过大，同时也会增加磁盘IO次数

B+Tree(B-Tree变种)

1、非叶子节点不存储data，只存储key，可以增大度

2、叶子节点不存储指针

3、顺序访问指针，提高区间访问的性能

4、叶子节点之间存在指针。在利用范围查找时，可以通过指针找到，无须从非叶子节点重新找

B+Tree索引的性能分析

MyISAM索引实现(非聚集)

必须清楚：存储引擎是在表级别的，而不是数据库级别(默认InnoDB)

MyISAM索引文件和数据文件是分离的

通过.MYI 索引文件找到文件指针，再用文件指针去.MYD找到数据

这里需要注意，叶子节点存储的是文件指针，并非数据本身。主键索引和非主键索引都是这样的。[在InnoDB中存在差异]

InnoDB索引实现(聚集)

主键索引

1、数据文件本身就是索引文件

table_name_InnoDB.frm：表结构文件

table_name_InnoDB.ibd：数据文件

2、表数据文件本身就是按B+Tree组织的一个索引结构文件，这个索引结构文件是主键索引，这也是为什么InnoDB必须有主键的原因。

若不设立主键，InnoDB会首先选择表中某个可以作为主键的列作为主键，若不存在这样的列，那么会在后台默认帮我们生成一个整型主键。

为什么推荐使用整型的自增主键？

自增主键是连续的，因此在磁盘上存储也会是连续存储的。在存储数据的时候，就不用移动磁头(寻道)，只需旋转磁盘。

在查询某个范围的数据时，因为数据是连续存储，所以在查询时基本上只寻道一次，就能查询出这个范围的数据。

为什么不用uuid？

uuid是字符串，在B+Tree中是ASCII码比较大小，整型的速度更快；

uuid浪费空间；

在插入数据时造成节点分裂，还有可能造成磁盘IO操作，效率较低；

3、聚集索引-叶节点包含了完整的数据记录

非主键索引

叶子节点不存储数据，存储主键索引的值

为什么这么设计？主键索引中维护了一份数据，非主键索引再存储一份会存在一致性/分布式的问题；节省存储空间

联合索引的原则：最左前缀原则

MySQL中的索引可以以一定顺序引用多列，这种索引叫作联合索引。如User表的name和city加联合索引就是(name,city)，而最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。如下：

select * from user where name=xx and city=xx ; ／／可以命中索引

select * from user where name=xx ; // 可以命中索引

select * from user where city=xx ; // 无法命中索引

这里需要注意的是，查询的时候如果两个条件都用上了，但是顺序不同，如 city= xx and name ＝xx，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的。

由于最左前缀原则，在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面。ORDER BY子句也遵循此规则。

MySQL如何为表字段添加索引？

1. 添加PRIMARY KEY(主键索引)

ALTERTABLE`table_name`ADDPRIMARY KEY( `column`)

2. 添加UNIQUE(唯一索引)

ALTERTABLE`table_name`ADDUNIQUE( `column`)

3. 添加INDEX(普通索引)

ALTERTABLE`table_name`ADDINDEXindex_name ( `column`)

4. 添加FULLTEXT(全文索引)

ALTERTABLE`table_name`ADDFULLTEXT ( `column`)

5. 添加多列索引

ALTERTABLE`table_name`ADDINDEXindex_name ( `column1`, `column2`, `column3`)