性能调优1. 深入理解Mysql索引底层数据结构与算法(新手练习)

	本文章记录自己学到知识，复习用。有理解错的地方和不足之处，望见谅和指点下。

1. 前言

‌‌‌　　索引是将Mysql数据排好序的数据结构，能加快查询效率，这点很重要。

‌‌‌　　假设有一张表T，二列七行数据。

‌‌‌　　有条查询语句

‌‌‌　　SELECT
‌‌‌　　*
‌‌‌　　FROM
‌‌‌　　T
‌‌‌　　WHERE
‌‌‌　　Col2=34

‌‌‌　　没有索引下，需要从上到下，一行行记录遍历查询比对。

‌‌‌　　每次查询就拿这行的完整数据比对，就要对磁盘进行效率低下的IO操作，因为记录在磁盘的存放位置好比一个点，不一定是紧挨着(可能挨着的位置被其它数据占用)。

‌‌‌　　可通过索引减少查询比对次数。

2. 主要的索引数据结构

2.1. 二叉树索引

　　对上面标的Col2列进行查询，可以对Col2列做索引，转换成如下图右边二叉树索引结构。

　　上面图只是大概，Mysql中二叉树的一个节点类似二元组[key，data]，key—索引字段的值，如34，value—索引字段对应这行记录在磁盘地址，如ox07。
　　
　　缺点
　　
‌‌‌　　二叉树有个特点是，右子节点的值大于父节点，而左子节点的值小于父节点。如果查找是Col1这种主键递增的字段，添加二叉树索引后，此时数据结构就跟链表一样。

‌‌‌　　查询条件是Col1=6时，查找比对次数还是6次。跟全表扫描一行行比对效率没啥区别，而且索引也是存储在磁盘上，还占用磁盘资源。

2.2. 红黑树索引

‌‌‌　　解决上面二叉树链表问题，引入红黑树索引结构。

‌‌‌　　红黑树也是二叉树，但它有自动平衡功能，查找指定节点次数比二叉树少。简单说根节点A的值是1，插入一个节点B的值是2，放节点A右边，插入一个节点C值是3，正常是放节点B右边，这边会平衡，放到节点A的左边。

‌‌‌　　缺点

‌‌‌　　数据量大的话，树的高度也很高，比如几百万数据，树的高度就有几十。查找数据是叶子节点，从根节点开始，需要查找几十次才能找到。

2.3. B-Tree索引

‌‌‌　　解决上面红黑树，数据量大情况下树高度太高，引入B-Tree索引结构。

‌‌‌　　数据结构如下。

‌‌‌　　(Max.Degree=3)一个大节点最多存储两个节点的B-Tree结构如下。

‌‌‌　　简单来说，一个大节点(对应MySql磁盘页)中包含许多小节点，每个节点(对应索引数据)是一个二元组[key,data],key表示当前索引字段的值，data表示可能是索引所在行数据在磁盘的地址或者直接就是索引字段值所在行的完整数据。

2.3.1. 特点

‌‌‌　　1. 所有索引元素(小节点)不重复(没有冗余索引)。

‌‌‌　　2. 叶节点具有相同的深度，叶节点之间没有指针。

‌‌‌　　3. 节点内还是节点之间，索引的数据从左到右递增排列，排好序。

2.4. B+Tree(B-Tree变种)索引

‌‌‌　　B+Tree是B-Tree变种，Mysql索引常使用到的数据结构，Mysql将B+Tree的叶节点间的指针从单向改成了双向(适用于Mysql下的所有引擎)。

‌‌‌　　这边B+Tree以Mysql的数据结构来说。

2.4.1. 特点

‌‌‌　　1. 非叶子节点不存储data，只存储索引字段的值，一个大节点(对应MySql的磁盘页)可以存储更多索引字段数据，拥有更多下级地址。这样可以有更多叶节点存储数据。

‌‌‌　　结合上面B+Tree数据结构图说，Mysql磁盘页默认16KB（不推荐改最优化了），在高度为3的B+Tree数据结构下，bigint类型字段做索引，绿色部分索引8字节+白色部分6字节，一磁盘页可以存储1170个下级路径，一个叶子节点存储的数据最多用1kb，一磁盘页16个叶节点，1170*1170*16，可以承载千万级的数据，当然不同字段类型做索引可能没法存储这么多。

‌‌‌　　MySql可通过sql语句查看的磁盘页大小。

‌‌‌

	SHOW GLOBAL STATUS like 'innodb_page_size';

‌‌‌　　2. 所有的data数据都移动到叶节点，叶节点是个二元组[key，data]，叶节点包含所有索引字段值，导致存在冗余索引。key存储索引字段的值，data存储索引字段值那行完整数据或者磁盘地址或者主键。

‌‌‌　　3. 叶子节点用指针连接，提高区间访问的性能(提高范围查找效率)，叶节点间存储上下节点的地址。

‌‌‌　　4. 大节点内和大节点之间数据，索引的数据从左到右递增排列，这就是排好序

2.4.2. 查找数据流程

‌‌‌　　1. 结合上面B+Tree数据结构来说，比如查找的，30，根节点开始，将根节点磁盘页数据，加载到内存。Mysql下根节点磁盘页数据是常驻内存的，更高版本可能所有冗余索引（非叶子节点）都常驻内存。

‌‌‌　　2. 内存中用二分算法查找，定位到15-56之间，15-56之间其实存了下个磁盘页数据的磁盘地址。

‌‌‌　　3. 根据磁盘地址，加载磁盘页数据到内存，继续二分算法查找。

‌‌‌　　4. 最后加载，叶节点磁盘页数据到内存，继续查找，找到合适的索引元素，取data数据如果data是这行完整数据就返回。如果是磁盘地址，需要到磁盘查找这行完整数据返回。如果是主键，则根据主键查找主键索引(MySql创建表的时候会根据主键做为索引构建表数据结构，叶节点的data存储是完整数据)的完整数据返回，这也叫回表查找。

‌‌‌　　5. 如果是范围查找，如，大于30，定位到30这个叶节点，因为叶节点间数据是排好序的，借助指针，按顺序就能一个个取出符合的叶节点，跟上面一样，取完整数据返回。

注意

‌‌‌　　1. 在内存中查找定位数据，比进行I/O操作从磁盘加载数据到内存，耗费的时间少很多。

‌‌‌　　2. 对于将大数据一次加载到内存查找，减少I/O操作未必就快很多。

2.5. Hash索引

‌‌‌　　Mysql除了默认的B+Tree索引，还有一种索引是Hash索引。

‌‌‌　　一列数据做Hash索引时候，先对索引字段的值做哈希运算（有MD5,CRC16等，但MySql底层有自己的实现算法）得到一个整型值（也叫哈希散列值），对应到一个桶。将索引字段的值key和对应行完整数据的磁盘地址的data数据组成二元组[key，data]丢到桶里。同个桶的元素，链表方式连接。

‌‌‌　　如下图，绿色，hash取到的整型值对应的桶，紫色，索引字段的值和对应行数据的磁盘地址或者完整数据的二元组数据。

2.5.1. 特点

‌‌‌　　1. 对索引的key进行一次hash计算就可以定位数据存储的位置，但存在hash冲突问题。

‌‌‌　　2. 很多时候Hash索引要比B+ 树索引更高效。

‌‌‌　　3. 仅能满足=，IN ，不支持范围查询（没法走索引）。

2.5.1. 查找数据流程

‌‌‌　　1. 比如查找‘Alice’的数据，hash数据结构全部加载到内存（可能按情况先加载所有桶数据，然后再加载对应桶数据链的数据），先对‘Alice’进行hash运输，定位到桶。

‌‌‌　　2. 遍历链表，比对是否是符合的节点。如果节点的索引字段值存储了对应行的完整数据，不需要从磁盘去取数据。如果节点的索引字段值存储了对应行的磁盘地址，则需要I/O操作从磁盘取出对应行完整数据。主要一次I/O操作在根据磁盘地址取完整数据。

‌‌‌　　3. 根据磁盘I/O操作的次数，hash索引效率应该比B+Tree高。但是hash索引仅能满足 “=” ，“IN” ，不支持范围查询，导致该索引不好用。

3. 数据库存储引擎

3.1. 前言

‌‌‌　　存储引擎是用来形容数据库表的存储，数据库可以设置存储引擎，最终也是作用于表存储结构。

‌‌‌　　通过查看表DDL语句可以看出表的存储引擎（这边用的Navicat）。
‌‌‌　　Mysql主要用到存储引擎有MyISAM存储引擎和 InnoDB存储引擎。

‌‌‌　　Mysql使用存储引擎时候，会通过主键索引，构建表数据结构。‌‌‌

‌ ‌‌‌　　确认主键索引流程：有默认设置的主键则做为索引。没有主键，会从表第一列开始选择，找到一列里头数据都不相等的，当作索引将表数据组织成B+Tree。如果都没有符合的，则会创建隐藏列当做主键索引，这一列的数据都唯一的。

‌‌

3.2. 结合存储引擎查看表在磁盘的存储文件

‌‌‌　　MySql的数据存储在指定的Data文件夹中。

‌‌‌　　1. 一个库对应一个文件夹。

‌‌‌　　2. 一个表对应，数据库文件夹下，同名的一些文件(有多个同名，后缀不一样的也是同一个表的文件，存储数据，索引，表结构等)。

‌‌‌　　存储引擎MyISAM下：

‌‌‌　　.frm—表数据结构相关信息存储 Mysql8开始合并到.sdi文件，没有.frm文件。

‌‌‌　　.MYD—MY存储引擎下的数据文件。

‌‌‌　　.MYI—MY存储引擎下索引文件。

‌‌‌　　存储引擎InnoDB下：

‌‌‌　　.frm—存储表数据结构相关信息。

‌‌‌　　.ibd—存储索引和表数据。

‌‌‌　　Mysql8开始上面这些文件都合并到.ibd文件中。

4.3. 聚集索引和非聚集索引(稀疏索引)

‌‌‌　　聚集索引：简单说索引数据结构的data存储了索引字段值对应行的完整数据，如B+Tree下叶节点data存储了索引字段值对应行的完整数据。

‌‌‌　　非聚集索引：文件和数据文件是分离的，简单说索引数据结构的data存储的不是索引字段值对应行的非完整数据。如B+Tree下叶节点data存储了，索引字段值对应行完整数据的磁盘地址或者该行主键值，需要根据磁盘地址找到完整数据或者根据主键值回表查找。

‌‌‌　　回表查找：就是根据主键值，从主键索引组织的表数据，根节点开始，按照主键索引查找，找到完整的行数据。

4.4. MyISAM存储引擎

‌‌‌　　1. 早期的存储引擎，MyISAM索引文件和数据文件是分离的（非聚集索引）。

‌‌‌　　2. 对于MyISAM存储引擎存储的表，主键索引和非主键索引（辅助索引或者叫普通索引），都属于非聚集索引。数据结构都是使用B+Tree，这边叶节点指针是单向的，然后叶节点的data存储是磁盘地址，如下：

‌‌‌4.4.1. 查找数据流程

‌‌‌　　假设Col1做为索引，表名为T，查询语句：

‌‌‌　　SELECT
‌‌‌　　*
‌‌‌　　FROM
‌‌‌　　T
‌‌‌　　WHERE
‌‌‌　　T.Col1='15'

‌‌‌　　MYI文件存储以Col1的B+Tree结构索引的数据，下图左边树。

‌‌‌　　MYD文件会存储表的数据，下图右边蓝色部分。

‌‌‌　　查找流程：跟前面说到B+Tree一样，先从MYI索引的根节点开始一路找到符合节点，根据节点记录的数据磁盘地址，回表查找从MYD找到这行完整数据。

4.5. InnoDB存储引擎

‌‌‌　　 目前MySql主要使用的存储引擎基本取代了MyISAM存储引擎，8.0原本MyISAM存储引擎的表都替代为InnoDB存储引擎。

‌‌‌　　主键索引：对于InnoDB存储引擎的表，主键索引组织的表数据结构默认使用的是B+Tree数据结构，叶节点间指针是双向的，且是聚集索引(叶节点的data存储了对应行的完整数据)。如下图：

‌‌‌　　非主键索引(普通索引)：使用的也是B+Tree数据结构，叶节点的data存储是主键值。如下：

‌‌‌　　对于一张表， InnoDB存储引擎下只有一个聚集索引。

4.5. MyISAM存储引擎和InnoDB存储引擎区别

‌‌‌　　1. InnoDB存储引擎组织表，使用的主键索引是聚集索引(聚簇索引)。MyISAM存储引擎主键索引是非聚集索引。

‌‌‌　　2. MyISAM存储引擎不支持聚集索引。

‌‌‌　　3. ‌‌‌ 两者使用的B+Tree构建表数据结构，叶节点间有区别。MyISAM存储引擎叶节点间指针单向的，InnoDB存储引擎是双向的。

5. 联合索引

‌‌‌　　这边以B+Tree数据结构来说。

‌‌‌　　1. 联合索引也叫复合索引，多个字段共同组织成一个索引。

‌‌‌　　2. 联合主键索引：假设有三个字段做联合索引，其中一个是主键。

‌‌‌　　3. 联合索引数据结构：B+Tree数据结构下的联合索引，数据也是排好序的。（联合主键索引下，叶节点不存在合并并列情况，因为是主键是唯一的）。结构如下

‌‌‌　　4. 一张表不推荐做太多单值索引。sql建议2-3字段做联合索引。二八法则，一个联合索引解决80%问题，剩下新建索引解决20%。

‌‌‌　　5. 不同引擎下，联合索引(非主键联合索引)的叶节点的data存储数据不一样，MyISAM存储引擎下应该存的是磁盘地址，InnoDB存储引擎存的是主键。如果是联合主键所以，data应该存的是完整数据。

5.1. 联合索引的排好序

‌‌‌　　根据索引建的先后顺序，比如先后建name字段，age字段，position字段索引。那么排序索引数据时候，节点间先比较name字段值，再到age字段，再到position字段(字符串是根据字符一个个比较，结合字符集编号，可能使用的是ASCII字符集）。如果name字段能比较出大小，则不需要再进行后续比较，否则就到第二个字段以此类推。如果字段值比较都相同则合并并列在一起(不是像上面叶节点间那样靠在一起，有点特殊了解就行)。

‌‌‌　　如果并列下，查找数据，找到这些并列节点数据时候，则根据它们data的主键回表查找或者磁盘地址，找到完整数据区分是否是符合的数据。

5.2. 联合索引的最左前缀原理(最左列原理最合适)

‌‌‌　　有三条sql语句，联合索引是（name,age,position）查询条件都是索引的字段。只有第一条语句触发了联合索引。

‌‌‌

‌‌‌　　根据最左前缀原理。要使联合索引生效，查询条件第一个必须是联合索引最左边的索引，然后第二个查询条件，必须是联合索引第二个索引以此类推。

‌‌‌　　如查询条件只有一个要触发联合索引，则必须是联合索引字段最左边一个索引。

‌‌‌　　查询条件刚好是，联合索引的全部索引，即使顺序不对，Mysql优化器会优化排序，触发索引，建议条件语句还是手动排好序。

‌‌‌　　原因：因为数据结构是按索引字段先后比对排好序的，查找数据。如果直接从第二个age索引进行范围查找，数据结构就不是排好序的，不好查找。

7. 面试题目

1. B+Tree跟B-Tree区别，为啥Mysql要选择B+Tree？

‌‌‌　　1. B+Tree树高度小，查找数据次数少。B+Tree节点的，data都移动到叶节点。导致一个磁盘页可以存储很多索引，叶节点可以存储更多的数据。

‌‌‌　　前面以BigInit做为主键索引说过，B+Tree下，树高度为3下可以存储千万级数据。B-Tree存储千万级下，树高度就是16的n次方=千万级，n就是树的高度(一个磁盘页16kb，一个小节点最多1kb够了，所以一个磁盘页有16个小节点)，树高度太高，影响查找效率。

‌‌‌　　2. B+Tree范围查找效率更高。B+Tree叶子节点间有指针，更好支持范围查找，叶节点里头和叶节点间都是是排好序的，查找一个范围数据，借助指针可以横向顺序取出相邻叶节点里头符合的数据。B-Tree查找数据范围超过一个叶节点包含的数据，则要从根节点开始，再次查找到别的叶节点。

2. 单纯索引下，聚集索引和非聚集索引哪个查找更快?

‌‌‌　　聚集索引更快，定位到叶节点时候，可以直接取出完整数据，不需要根据磁盘地址在I/O操作取数据。

3. 为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？

‌‌‌　　1. 有主键好处：一开始组织表数据效率更高。

‌‌‌　　InnoDB存储引擎的表，在创建表时候，会将表数据组织成B+Tree结构。会根据主键做索引进行组织。

‌‌‌　　没有主键，会从表第一列开始选择，找到一列里头数据都不相等的，当作索引将表数据组织成B+Tree。

‌‌‌　　如果都没有符合的，则会创建隐藏列，这一列会存储各不同的rowid当做索引组织。

‌‌‌　　2. 用整型主键好处：查找数据时候，比值更方便，占用空间少。

‌‌‌　　用uuid字符串则要逐位转成字符集(如转成ASCII字符集)的编号比值。还有就是整型占用空间少。

‌‌‌　　3. 用自增主键好处：新增数据时候，B+Tree数据结构下，组织表数据效率更高。

‌‌‌　　B+Tree对于构建的索引的列，叶节点会根据索引数据排好序。B+Tree上一个父节点索引数据，是下个子节点的磁盘页中最小节点的值。

如下图

‌‌‌　　如果插入非自增数据9，节点存储不下，会先分裂节点，然后平衡树

‌‌‌　　如果插入自增数据13，节点存储不下，会在右边创建新的节点：

　两者效率相比，自增更快。

4. 为什么InnoDB非主键索引结构，B+Tree下，叶子节点存储的是主键值？ (一致性和节省存储空间)或者为什么只用一个聚集索引。

‌‌‌　　1. 对于InnoDB非主键索引(辅助索引即普通索引，二级索引)，叶节点的data存储的数据，是当前列所在行的主键值。

‌‌‌　　2. 这也涉及目的：一个是节约空间，另外数据一致性问题，如果所有索引叶节点的索引字段都能存储完整数据，插入一条新数据就要保证所有索引字段存储的数据同步修改。 所以InnoDB一张表，只有一个聚集索引。

5. 为什么最左前缀原理，设置条件查询时候，要按索引顺序设置查询条件才生效？

‌‌‌　　1. B+Tree数据结构下的，联合索引的索引数据的data。它们是按联合索引的索引创建顺序先后，比对来排好序数据。比如有个普通联合索引排好序数据如下：

使用下面图片中的第二条sql或者第三条查询语句，联合索引是不生效，都需要一个个叶节点比对，相当于全表扫描。

再比如，第二条sql语句，改成age>30进行范围查找，age索引字段值就不是排好序的。

6. MyISAM存储引擎和InnoDB存储引擎索引数据区别？

‌‌‌　　1. 组织表数据主键索引，MyISAM是非聚集索引，InnoDB是聚集索引。