MySQL技术内幕：InnoDB存储引擎（第5章索引与算法）
第5章索引与算法
9.1 缓冲池、顺序读取与随机读取
条件允许：
可以增大内存，最好超过实际数据量；
使用固态硬盘替换机械硬盘
-
数据库分类
数据库分为
基于磁盘的数据库系统
基于内存的数据库系统
混合型数据库系统 -
介绍（页（或块）、缓存、LRU）
基于磁盘的数据库系统一般都有缓冲池，即一块内存区域，其作用是将从磁盘上读取的指定大小数据—称为页（或块），放入缓冲池。
当再次读取时，数据库首先判断该页是否在缓冲池中，如果在则直接读取缓冲池中的页，如果不在则读取磁盘上的页。
对于写操作，数据库将页读入缓冲池，然后在缓冲池中对页进行修改，修改完成后的页一般被异步地写入磁盘上。
对于缓冲池的维护一般采用最近最少使用（Least Recently Used，LRU）算法。由此可见，缓冲池的大小决定了数据库的性能。若数据库中的数据可以完全存放于缓冲池中，则可以认为这时数据库的性能是最优的。除了同步/异步的写磁盘操作外，所有其他操作都可以在内存中完成。 -
缓存大小参数
InnoDB存储引擎来说，变量innodb_buffer_pool_size决定了缓冲池的大小；
MyISAM存储引擎来说，变量key_buffer_size决定了缓冲池的大小；
查看：
show VARIABLES where variable_name like ‘%innodb_buffer_pool_size%’; -
补充
-
硬盘构造
传统机械硬盘由磁头（head）、磁道（track）、扇区（sector）、柱面（cylinder）组成。读取时需要通过磁头的移动来定位数据，这个时间称为寻道时间（seek time）。
-
读取方式
顺序读取（sequntial read）是指顺序地读取磁盘上的页。
随机读取（random read）是指访问的页不是连续的，需要磁盘的磁头不断移动。 -
mysql根据区来管理页（一个区是连续的64个页）
这里需要注意的是，这里的“顺序”指的是逻辑上的顺序，在物理上不可能保证所有的数据都是顺序的。而为了保证顺序，数据库存储引擎一般都根据区（extent）来管理页，例如在InnoDB存储引擎中1个区是连续的64个页。因此在顺序读取数据库时，可以保证这64个页是连续的，而区与区之间的页，可能是连续的也可能是不连续的。
-
9.2 数据结构与算法
B+树索引是最为常见，也是在数据库中使用最为频繁的一种索引。
-
9.2.1 二分查找法
在B+树索引中，B+树索引只能找到某条记录所在的页，需再根据二分查找法来进一步找到记录所在页的具体位置。
-
9.2.2 二叉查找树和平衡二叉树（前者会退化，后者维护代价高）
在二叉查找树中，左子树的键值总是小于根的键值，右子树的键值总是大于根的键值。
平衡二叉树的定义如下：
首先符合二叉查找树的定义，其次必须满足任何节点的两棵子树的高度最大差为1。平衡二叉树的查询速度的确很快，但是维护一棵平衡二叉树的代价非常大，通常需要1次或多次左旋或右旋来得到经过插入或更新操作后二叉树的平衡性。
-
9.6 B+树索引的使用
9.3 B+树（Leaf Page、Index Page）
B+树由B树和索引顺序访问方法（ISAM，是不是很熟悉？对，这也是MyISAM引擎最初参考的数据结构）演化而来，但是在实现过程中几乎没有使用B树的情况了。
拆分页（split）、旋转（rotation）。
-
介绍
B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树，
在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点，
各叶子节点通过指针进行链接。 -
插入（图）
-
9.3.2 B+树的删除操作（填充因子fill factor，至少为50%）
B+树使用填充因子（fill factor）来控制树的删除变化，填充因子可设的最小值是50%。
B+树的删除操作同样必须保证删除后叶子节点中的记录依然按序排列。-
删除（图）
-
9.4 B+树索引
B+树索引可以分为聚集索引与辅助索引（非聚集索引）。
-
9.4.1 InnoDB B+树索引（IOT，6字节主键，聚集索引，辅助索引，页大小16KB）
InnoDB存储引擎是索引组织表（Index Organized Table，IOT），也就是说数据文件本身就是按照B+树方式存放数据的。其中，B+树的键值为主键，若在建立时没有显式地指定主键，则InnoDB存储引擎会自动创建一个6字节的列作为主键。因此在InnoDB存储引擎中，可以将B+树索引分为聚集索引（clustered index）和辅助索引（secondary index）。无论是何种索引，每个页的大小都为16KB，且不能更改。
-
聚集索引（主键索引不能为空）（图）
聚集索引是根据主键创建的一棵B+树，聚集索引的叶子节点存放了表中的所有记录。
-
辅助索引（标签查找）（图）
辅助索引是根据索引键创建的一棵B+树，与聚集索引不同的是，其叶子节点仅存放索引键值，以及该索引键值指向的主键。
因为辅助索引不包含行记录的所有数据，这就意味着每页可以存放更多的键值，因此其高度一般都要小于聚集索引。
注意：
若辅助索引是一个包含主键的联合索引，那么并不需要一个额外的列来存放主键值。辅助索引会选择通过联合索引中的主键进行查找。
-
-
9.4.2 MyISAM B+树索引（数据文件：MYD，索引文件：MYI，索引页大小默认为1KB）
MyISAM存储引擎其实更像一张堆表，所有的行数据都存放于MYD文件中，其B+树索引都是辅助索引，存放于MYI文件中。
-
索引分裂问题
判断条件:
PAGE_LAST_INSET(最后插入记录位置)
PAGE_DIRECTION（插入方向）
PAGE_N_DIRECTION（该方向连续插入次数）-
随机分裂（无方向）
取页中间记录为分裂点
-
顺序分裂（有方向）（图）
当往同一方向插入记录数为5时，将定位到cursor位置（待插入位置）之后还有3条记录，则以第3条记录为分裂点，否则以待插入记录为分裂点。
-
-
-
索引管理
查看表索引信息：
show index from tablename;-
alter创建（图）
-
create/drop(图)
-
-
索引解读
-
索引内容（图）
-
重要字段说明（图）
-
9.5 Cardinality（基数）
Cardinality值非常关键，表示索引中唯一只记录数量的预估值。
Cardinality/n_rows_in_table应尽可能接近1，如果非常小，那么需要考虑是否还要建这个索引
-
9.5.2 InnoDB存储引擎怎样统计Cardinality
Cardinality/n_rows_in_table应尽可能接近1，对这样的列数据建立索引
-
更新Cardinality信息的策略
1.表中1/16的数据已发生变化。
2.stat_modified_counter > 2000000000。
说明：
第一种策略为自上次统计Cardinality信息后，表中1/16的数据已经发生变化。这时需要更新Cardinality信息。
第二种策略考虑的是，如果对表中某一行数据频繁地进行更新操作，这时表中的数据实际并没有增加，发生变化的还是这一行数据，那么第一种更新策略就无法适用，故在InnoDB存储引擎内部有一个计数器stat_modified_counter，用来表示发生变化的次数，当计数器的值大于2000000000时，同样需要更新Cardinality信息。 -
Cardinality采样过程
取得B+树索引中叶节点的数量，即为A。
随机取B+树索引中的8个叶节点。统计每个页不同记录的个数，即为P1，P2，…，P8。
根据采样信息给出Cardinality的预估值：Cardinality=（P1+P2+…+P8）*A/8。
说明：
会触发Cardinality值的统计
show index from iams_dle;
-
9.6 B+树索引的使用
-
9.6.2 联合索引（最左匹配原则）
联合索引是指对表上的多个列进行索引。
-
9.6.3 覆盖索引（索引包含需要获取的值）
InnoDB存储引擎支持覆盖索引（covering index），或称索引覆盖（index coverage），即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用覆盖索引的好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。
-
9.6.4 优化器选择不使用索引的情况
当执行EXPLAIN时，会发现优化器并没有选择索引去查找数据，而是通过扫描聚集索引，也就是直接进行全表的扫描来得到数据。这种情况多发生于范围查找、JOIN操作等。
注意获取字段是否包含在索引中，判断是否需要回表查询。 -
9.6.5 INDEX HINT（USE|FORCE|IGNORE INDEX(索引列)）
显式地告诉优化器使用哪个索引。
两种情况可能需要用到INDEX HINT：
1.MySQL数据库的优化器错误地选择了某个索引，导致SQL语句运行得很慢。这种情况非常少见。
2.某SQL语句可以选择的索引非常多，这时优化器选择执行计划时间的开销可能会大于SQL语句本身。
例如：
SELECT * FROM t FORCE INDEX(a) WHERE a=1 and b=1;
9.7 Multi-Range Read（MRR）（对索引查询时有效）
MRR优化的目的就是减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问，可为IO-bound类型的SQL查询语句带来性能的极大提升。MRR优化适用于range、ref和eq_ref类型的查询。
查看：
show VARIABLES like ‘%rnd%’;
read_rnd_buffer_size 设置大小
-
好处
1.使得数据访问变得较为顺序。在查询辅助索引时，先对得到的查询结果按照主键进行排序，并按照主键排列的顺序进行书签查找。
2.减少缓冲池中页被替换的次数。
3.批量处理对键值的查询操作。 -
工作方式
1.将查询得到的辅助索引键值存放于一个缓存中，这时缓存中的数据是根据辅助索引键值排序的。
2.将缓存中的键值根据RowID进行排序。
3.根据RowID的排序顺序来访问实际的数据文件。
9.8 Index Condition Pushdown（ICP）（对索引查询时有效）
在支持ICP后，MySQL数据库会在取出索引的同时，判断是否可以进行WHERE条件的过滤，即将WHERE的部分过滤操作放在了存储引擎层。在某些查询中，ICP会大大减少上层SQL层对于记录的索取（fetch），从而提高数据库的整体性能。
优化后explain中extra显示：
using index condition
-
范围（索引上能覆盖where的判断条件）
ICP优化支持range、ref、eq_ref和ref_or_null类型的查询，当前支持MyISAM和InnoDB存储引擎。当优化器选择ICP优化时，可在执行计划的Extra列看到Using index condition提示。
若支持ICP优化，则在取出索引时，就会进行WHERE条件的过滤，然后再去获取记录，这将极大提高查询的效率。当然，WHERE可以过滤的条件是该索引可以覆盖到的范围。
9.9 T树索引（只存放指针，没有数据）
T树的好处是节点不存放数据，只存放指针，这样能减少对内存的使用，这对内存数据库来说显得尤为重要。
对于MySQL数据库的NDB Cluster内存存储引擎，在使用它时可将其视为内存数据库（从MySQL5.1开始NDB Cluster引擎可以将非索引数据存放在磁盘上，这种情况又可以视为混合存储引擎）
-
概述（图）
T树节点由3个指针、一个有序数组（array），以及控制信息组成。3个指针分别为指向父节点和左右子树的指针。有序数组保存的是数据指针，而非实际的数据。数组中的第一个数据称为最小元素（minimum element），最后一个数据称为最大元素（maximum element）。控制信息中存放了关于该T树节点的一些额外信息。
-
9.9.2 T树的查找、插入和删除操作（略）
-
9.9.3 T树的旋转（略）
9.10 哈希索引
InnoDB存储引擎支持自适应哈希索引，用户仅能开启该特性，不能对其进行人工干预。散列算法是一种常见算法，时间复杂度为O（1），并且不只存在于索引中，每个数据库应用中都存在该数据库结构。
利用哈希函数解决了直接寻址遇到的问题，同时又使用链接发解决了碰撞问题。
-
9.10.1 散列表
- 介绍碰撞（collision）、链接法（chaining）
-
9.10.2 InnoDB存储引擎中的散列算法
关键字K=space << 20 + space +offset
-
9.10.3 自适应哈希索引（DBA无法干预）
通过命令SHOW ENGINE INNODB STATUS可以看到当前自适应哈希索引的使用状况。
它是数据库系统自己创建并使用的，DBA本身并不能对其进行干预。需要注意的是，哈希索引只能用来搜素等值的查询，对于其它的查找是不能使用哈希索引的。我们只能通过参数innodb_adaptive_hash_index来禁用或启动此特性。
汇总参考
全文检索（略）
例如：
like ‘%xxx%’;
-
倒排索引（有两种形式）
全文检索通常使用倒排索引（inverted index）来实现，和B+树索引一样，也是一种索引结构，它在辅助表（auxiliary table）中存储了单词与单词自身在一个或多个文档所在位置之间的映射。通常利用关联数组实现。
-
inverted file index（图）（单词，单词所在文档ID）
-
-
full inverted index（图）（单词，（单词所在文档ID，具体位置））
-
1783
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
