MySQL索引背后的数据结构和原理

这是我看到的一篇博客，讲得非常详细，分享给大家：http://blog.codinglabs.org/articles/theory-of-mysql-index.html

Abstract: 

　　本文以MySQL数据库为研究对象，讨论与数据库索引相关的一些话题。MySQL支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同，如BTree索引， 哈希索引，全文索引等等。本文只关注BTree索引。 

索引的本质：

  　 索引本质上就是一种数据结构。

       数据库查询是数据库的最主要功能之一，我们都希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化。如顺序查找，二分查找，二叉树查找等，但这些查找都需要应用于特定的数据结构上。因此，数据库还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现查找算法。

 

目前大多数索引的本质都是B-Tree和B+Tree

　　B-Tree的特性

　　　　(1). d为一个大于1的一个正整数，成为B-Tree的度 

　　　　(2). h为一个正整数，成为B-Tree的高度 

　　　　(3). 每个非叶子节点由n-1个key和n个指针组成，其中d<=n<=2d 

　　　　(4). 每个叶子节点最少包含一个Key和两个指针，最多包含2d-1个Key和2d个指针，叶子节点的指针均为null; 

　　　　(5). 所有叶子节点具有相同的深度，为h 

　　　　(6). key和指针互相间隔，节点两端是指针 

　　　　(7).一个节点中的key从左到右非递减排列 

　　　　(8). 所有的节点组成树结构 

　　(9). 每个指针要么为null，要么指向另外一个节点 　　

　　(10). 如果某个指针在节点node最左边且不为null，则其指向节点的所有key小于v(key)，其中v(key)为node的第一个key的值 

　　(11). 如果某个指针在节点node最右边且不为Null，则其指向节点的所有Key大于v(key)，其中v(key)为node的最后一个key的值 

　　(12). 如果某个指针在节点node的左右相邻key分别是keyikeyi和keyi+1keyi+1且不为null，则其指向节点的所有key小于v(keyi+1)v(keyi+1)且大于v(keyi)v(keyi)。

由于BTree的特性，在BTree中按key检索数据的算法非常直观：首先进行根节点二分查找，如果找到则返回对应节点的data，否则对相应区间的指针指向的节点递归进行查找，直到找到节点或找到null指针，前者找到successful，后者查找失败。

查找节点个数的渐进复杂度为O(logdN)

 

B+Tree的特性：B+Tree是BTree的变种，MySQL普遍使用的是B+Tree实现其索引结构。

(1). B+Tree每个节点指针上限为2d-1而不是2d; 

(2). 内节点不存储data，只存储key; 叶子节点不存储指针 

 

带有顺序访问指针的B+Tree:

B+Tree的每个叶子节点增加了一个指向相邻叶子节点的指针， 就形成了带有顺序访问指针的B+Tree。这个优化的目的是为了提高区间访问的性能

 

为什么使用B-Tree(B+Tree)

　　  索引文件一般都是存放在磁盘中的，这样的话，索引的查找过程中就要产生磁盘I/O消耗。

 

　　   主存存取原理：

    　  当系统需要读取主存时，则将地址信号放到地址总线上传给主存，主存读到地址信号后， 解析信号并定位到指定存储单元，然后将此单元数据放到数据总线上，供其他部件读取。

         主存存取不存在I/O操作，取任何数据的时间复杂度都是一样的。

 

          磁盘存取原理：

         与主存不同，磁盘I/O存在机械运动耗费，因此磁盘I/O的时间消耗是巨大的。

         一个磁盘是由大小相同且同轴的圆形盘片组成，磁盘可以转动。磁盘的一侧有磁头支架，磁头支架上固定了一组磁头，每个磁头负责存取一个磁盘的内容。磁头不能转动，但可以沿磁盘半径方向运动。

　　 盘片被划分为一系列同心环，圆心是盘片中心，每个同心环叫做一个磁道，所有半径相同的磁道组成一个柱面。磁道被沿半径划分为一个个小段，每个段叫做一个扇区，每个扇区是磁盘的最小存储单元。

        当需要从磁盘读取数据时，系统会将数据逻辑地址传给磁道，磁盘的控制电路按照寻址将逻辑地址翻译成物理地址，即确定要都读的数据在哪个磁道，哪个扇区。

　　 为了读取这个扇区的数据，需要将磁头放到这个扇区上方，为了实现这一点，磁头需要移动对准相应磁道，这个过程叫寻道，所耗费的时间叫做寻道时间，然后磁盘旋转目标扇区转到磁头下，这个过程为旋转时间。

 

        局部性原理与磁盘预读：

       为了尽量减少磁盘I/O，磁盘往往不是按需读取，而是每次都会预读，即使只需要一字节，磁盘也会从这个位置开始，顺序向后读取一定的长度的数据放入内存。

　　局部性原理：

        当一个数据被用到时，其附近的数据也通常会马上被使用。

        程序运行期间所需要的数据通常比较集中。

 

      由于磁盘顺序读取的效率很高(不需要寻道，只需要很少的旋转时间)，因此对于具有局部性的程序来说，预读可以提高I/O效率。预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小

相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

 

B-/B+Tree索引的性能分析：

    B-Tree检索一次最多需要访问h个节点，数据库系统的设计者巧妙的利用了磁盘预读原理，将一个节点的大小设置为等于一个页的大小，这样每个节点只需要一次I/O就可以完全载入。    

    每次新建节点时，直接申请一个页的空间，这样就可以保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对其的，就实现了一个node只需要一次I/O

    B-Tree中一次检索最多需要h-1次I/O，这是因为根节点常驻内存。渐进复杂度O(h) = O(log d N).

    而红黑树,h明显要深得多。

 

    B+Tree更适合外存索引，原因和内节点得出度d有关。d越大，索引性能越好，而出度得上线取决于节点内key和data的大小。

    B+Tree内节点去掉了data域，因此可以拥有更大的出度，拥有更好的性能。

 

MySQL索引实现

 

    MyISAM索引实现：非聚集索引

    MyISAM的索引文件仅仅保存数据记录的地址。索引文件和数据文件是分开的。

    在MyISAM中，主索引和辅助索引在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。 

 

    Innodb索引：聚集索引

    InnoDB的数据文件本身就是索引文件，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键。

    InnoDB的主索引的key就是主键，这就要求InnoDB要求表必须要有主键，如果没有显示指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB生成一个隐含字段作为主键。

    InnoDB的辅助所以data域存储的是记录主键的值而不是地址。

    这就使得主键索引非常高效，而辅助索引需要检索两遍索引，首先检索辅助索引获得主键，然后用主键到主键索引中检索获得记录。

    InnoDB数据文件本身是一颗B+Tree, 非单调的字段作为主键在InnoDB中不是一个好主意，因为InnoDB数据文件本身就是一颗B+Tree，非单调的主键会造成在插入新纪录时，数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效。

 

 

最左前缀原理和相关优化:

　　

  查看有那些索引：

SHOW INDEX FROM employees.`titles`;     

 table: 表名

 Non_unique: 索引能否包含重复次,如果可以则为1

 Key_name: 索引名字

 Seq_in_index: 索引的序列号

 Column_name: 索引的名字

 Collation: 列以什么方式存储在MySQL中，A升序或null无分类

 Cardinality: 索引中唯一值的数目的估计值。通过运行ANALYZE TABLE 或 myisamchk -a可以更新。

 Sub_part：如果列只是被部分编入索引，则为被编入索引的字符的数目，若是整列被编入索引，则为null

 Packed: 指示关键字如何被压缩。如果没有被压缩，则为null

 Null：如果该列含有null,则显示为yes，反之则为null

 inidex_type: 用的索引方法，包括BTree,FullText,HASH,RTree

 

1. 全列匹配：

EXPLAIN SELECT * FROM employees.`titles` WHERE emp_no = '10001' AND title = 'Senior Engineer' AND from_date = '1986-06-26';

 

当按照索引列中所有列进行精确查询是，索引可以被用到。理论上索引对顺序是敏感的，但由于MySQL查询优化器会自动调整where子句的条件顺序以使用适合的索引，where中的条件顺序可以颠倒。

 

2. 最左前缀匹配

EXPLAIN SELECT * FROM employees.`titles` WHERE emp_no = '10001';

 

key_len为4，只有索引第一列被用到。

 

3. 查询条件用到了索引中列的精确匹配，但是中间某个条件未提供。

explain select * from employees.`titles` where emp_no = '10001' and from_date = '1986-06-26';

 

因为title为提供，索引只用到了索引的第一列，但是由于title存在而无法和左前缀连接，因此需要对结果进行扫描过滤，如果想让from_data也使用索引而不是where过滤，可以增加一个辅助索引。

除此之外，还可以使用一种称之为“隔离列”的优化方法。如果此列值的种类比较少的情况下，可以考虑用“IN”来填补这个坑形成最左前缀。

 

EXPLAIN SELECT * FROM employees.titles

WHERE emp_no='10001'

AND title IN ('Senior Engineer', 'Staff', 'Engineer', 'Senior Staff', 'Assistant Engineer', 'Technique Leader', 'Manager')

AND from_date='1986-06-26';

 

此次查询说明索引全部被用上了。

 

 

 

4. 查询条件没有指定索引第一列

不是最左前缀，索引失效

5. 匹配某列的前缀字符

EXPLAIN SELECT * FROM employees.`titles` WHERE emp_no = '10001' AND title LIKE '%Senior'; 

如果匹配符不出现在开头，则可以

 

6. 范围查询

EXPLAIN SELECT * FROM employees.titles WHERE emp_no < '10010' AND title='Senior Engineer'

 

范围列可以用到索引，必须是最左前缀，但是范围列后面的列无法用到索引。索引最多用于一个范围列，如果查询条件中有两个范围列，则无法全用到索引。

7. 查询条件中含有函数或表达式

如果查询条件中含有函数或表达式，则MySQL不会为这些列使用索引。

 

索引选择性与前缀索引：

    一般两种情况下不建议使用索引：

1. 表记录比较少，如果表记录只有几百条记录或者几千条记录，那就没有必要建立索引。记录数不超过2000不需要建索引。
2. 索引的选择性较低。Selectivity = 不重复的索引值/表记录数，显然所以的选择性越高，索引价值就越大。

        

如果频繁按员工姓名查找，而姓名太长了，这时候可以考虑前缀索引，当前缀长度合适时，可以做到既可以使得前缀索引的选择性接近全列索引，又可以减少索引key而减少开销。

 

InnoDB的主键选择与插入优化：

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

如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

转载于:https://www.cnblogs.com/lfdingye/p/7400768.html