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,具体位置))
-