01-深入理解mysql索引底层数据结构与算法（tuling）

01 索引

(1) 索引是帮助MySQL高效获取数据的排好序的数据结构。（容易忽略的点：排好序）（形象点就是教科书的目录）
(2) 索引存储在文件里（也就是说有I/O操作）

note：上图创建的数据库来自《MySQL必知必会》(存放在mysql-5.7.22-winx64\data\mysql_learning)。Linux中存放在：

# 查看mysql的一些路径
mysql> show variables like '%dir%';
+-----------------------------------------+-------------------------------------------+
| Variable_name                           | Value                                     |
+-----------------------------------------+-------------------------------------------+
| basedir                                 | /usr/local/software/mysql/                |
| binlog_direct_non_transactional_updates | OFF                                       |
| character_sets_dir                      | /usr/local/software/mysql/share/charsets/ |
| datadir                                 | /usr/local/software/mysql/data/           |
| ignore_db_dirs                          |                                           |
| innodb_data_home_dir                    |                                           |
| innodb_log_group_home_dir               | ./                                        |
| innodb_max_dirty_pages_pct              | 75.000000                                 |
| innodb_max_dirty_pages_pct_lwm          | 0.000000                                  |
| innodb_tmpdir                           |                                           |
| innodb_undo_directory                   | ./                                        |
| lc_messages_dir                         | /usr/local/software/mysql/share/          |
| plugin_dir                              | /usr/local/software/mysql/lib/plugin/     |
| slave_load_tmpdir                       | /tmp                                      |
| tmpdir                                  | /tmp                                      |
+-----------------------------------------+-------------------------------------------+
15 rows in set (0.00 sec)

[root@ddhhdd xdclass]# pwd
/usr/local/software/mysql/data/xdclass
[root@ddhhdd xdclass]# ls
account.frm  account.ibd  command.frm  command.MYD  command.MYI  db.opt  dept.frm  dept.ibd  employee.frm  employee.ibd  salgrade.frm  salgrade.ibd  test.frm  test.ibd

(3) 索引结构

这里说说在几种数据结构中，MySQL为什么选择hash和B+Tree？

• 二叉树
• 红黑树
• hash
• BTree

(3.1) 遍历（没有索引）

首先，如果数据没有索引，那么我们读取数据是这样的：

note：这里有一篇硬盘存取原理文章，看完更容易理解： https://www.cnblogs.com/leezhxing/p/4420988.html

(3.2) 二叉树

上面我们发现读取数据特别耗时，那有没有比较节时的数据结构，我们可以看看二叉树：

note：查一次就是进行一次磁盘IO，如果遍历，则找Col1的6需要6次磁盘IO；如果建立二叉树索引，则找Col2的89只要2次磁盘IO。(二叉树的节点都是存放在磁盘中的，节点可能存放了两个值，key-value，比如key为89，value为89所在行的磁盘文件指针0x77。)

note：这里介绍一个动态演示数据结构的网址：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

(3.3) 二叉树与红黑树的比较

从上面我们发现，红黑树相比较于二叉树又进步了一些，但红黑树还是有些问题：那就是数据量大的话，红黑树的深度会很深，也就是说深度不可控，这样一来查找数据还是会很耗时。

note：红黑树的高度近似于\(h≈log_{2}n\)，假设\(n=100万\)数据，则\(h≈log_{2}100万≈20\)。

(3.4) B树与B+树

(1) B树

对于红黑树，一个节点只存储一个索引元素，可以给每个节点分配更大的磁盘存储空间，让其存储多个索引元素。即扩大横向广度，来减小纵向深度。

从上面看，我们发现BTree又进步了一些，查询速度提高，存储容量也没影响到。当然有人可能会这样想，那我们为什么不把数据全部都存在一个节点，这样深度不就是1了吗?

当然不行了！Java拿取数据一般是这样的：Java程序 -> CPU -> 内存 -> 硬盘，而内存与硬盘的交互是有大小限制的，所以不能把所有数据都放在一个节点来获取。

note：Linux默认一次磁盘IO是一页4KB，即8个512B的扇区；由于Linux内核中还有一套复杂的预读取的策略，所以在实际中，可能比8更多的扇区来一起被传输到内存中。

mysql默认的一页的大小是16KB，即一个节点的大小，一次磁盘IO效率非常高。

mysql> show global status like 'InnoDB_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)

(2) B+树

看到这里，我们知道一个大节点是16K，而大节点内\(小节点的个数=16K÷小节点的容量\)，也就是说，只要我们每个的小节点的data容量越小，那么可以存的小节点也就可以更多。

• 非叶子节点不存储data，只存储索引（冗余），可以放更多的索引；
• 叶子节点包含所有索引字段；
• 叶子节点用指针连接，提高区间访问的性能；

B+Tree通过把data不放在非叶子节点来增加度（小节点），使得深度一般是2~4，从而减少查询次数。并且，叶子节点之间会有指针，数据又是递增的，这使得我们范围查找可以通过指针连接查找，而不再从上面节点往下一个个找。

假设存储的索引为bigint(8B)，磁盘指针大概6B。则第1层能放1个节点，第2层可以放\(16K÷14B=1170\)个节点，第3层可以放\(1170×1170=136,8900\)个节点，假设data+索引值=1K，则叶子节点中的一个节点可以放\(16K÷1K=16\)个索引，所以一个3层B+树可以存\(1170×1170×16=2190,2400\)，已经是千万级别的数据量了。大于4层，则要考虑分库分表了。

结论：B+Tree既减少查询次数又提供了很好的范围查询。

(3.5) hash

从上面我们发现，相比较于红黑树，hash可以固定“深度”，且映射到磁盘存储引用，这样查找数据直接告诉磁盘数据在哪，查找数据很快，但是hash还是有些不足：那就是不能范围查找，比如我们查找Col1>1的数据。当然如果我们查询范围很小的话，我们也可以选择hash数据结构，因为它查找数据很快，这也是mysql的索引方法除了B+Tree还有hash。(hash索引也不能很好的支持order by、like等，等值查询效率会很高)

02 MyISAM索引实现(非聚集)

MyISAM索引文件和数据文件是分离的（结构.frm + 数据.MYD + 索引.MYI 三个文件），那MyISAM的索引是什么样的？

03 InnoDB索引实现(聚集)

• 数据文件本身就是索引文件（数据&索引.ibd + 结构.frm两个文件）
• 表数据文件本身就是按B+Tree组织的一个索引结构文件
• 聚集索引-叶节点包含了完整的数据记录

Q：为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？

无特殊需求下InnoDB建议使用与业务无关的自增ID作为主键。

InnoDB引擎使用聚集索引，数据记录本身被存于主键索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）。

1、如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。

2、 如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置。此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

InnoDB存储引擎采用了聚集（clustered）的方式，因此每张表的存储都是按主键的顺序进行存放。如果没有显式地在表定义时指定主键，InnoDB存储引擎会为每一行生成一个6字节的ROWID，并一次作为主键。

Q：为什么非主键索引结构叶子节点存储的是主键值？

1、节省存储空间：指向主键的节点，不用再存储一份相同的数据；

2、一致性：如果我们修改索引15的数据，那只要修改主键索引的data，而如果非主键索引的data也存一份数据的话，那得修改两份，所以非主键索引结构叶子节点存储主键值的话就可以保持数据一致性。

04 联合索引

相较于联合索引，建立单值索引需要更多的存储空间（一个单值一棵树）；

按字段顺序，逐个进行比较，如果相同则比较下一个字段。

note：上图是以主键联合索引为例（因为如果是非主键联合索引，data存的是主键）。