3、Mysql的B+树索引

最新推荐文章于 2023-05-16 08:00:00 发布

a little daily

最新推荐文章于 2023-05-16 08:00:00 发布

阅读量176

点赞数

分类专栏： mysql相关那些事儿

本文链接：https://blog.csdn.net/a1290123825/article/details/105283038

版权

mysql相关那些事儿专栏收录该内容

4 篇文章 0 订阅

订阅专栏

当我们聊数据存储方案改进的时候，我们应该考虑哪些问题？找一个锚点

B+树的什么特性让Mysql独爱用它作为索引数据的存储结构？
B+树索引如何去分类？分类的依据是什么？（聚集索引、非聚集索引）
应用中去构建B+树索引列的依据是什么？如何统计Cardinality？构建哪些索引（联合索引）（Cardinality）
InnoDB搜索引擎的查询优化器如何去抉择使用索引or不使用索引进行全表扫描？
MySQL查询优化器做了哪些索引优化？（覆盖索引、INDEX HINT、Multi-Range Read[缩写为MRR]、Index Condition Pushdown[缩写为ICP]）

为什么选用B+树作为索引数据的存储结构？

在一组数据中查询某项记录，无非顺讯查找、二分折半查找、二叉查找树、平衡二叉树、B树、B+树这样的一个演化过程；平均检索效率呈递增趋势；

B+树定义：是为磁盘或者其他直接存取辅助设备设计的一种平衡查找树，在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点，各叶子节点通过指针进行链接。B+树索引在数据库中的一个特点就是高扇出性。例如：InnoDB存储引擎中，每页的大小为16KB，因此数据库中B+树的高度一般都在2~4层，这意味着查找某一键值最多只需要2到4次IO操作；一般的磁盘查询时间也就0.02~0.04秒（按100次IO每秒计算）；

B+树索引分为聚集索引和辅助索引（非聚集索引），区别仅在于存放数据的内容：

聚集索引：根据主键创建了一棵B+树，叶子节点存放了表中的所有记录；全表扫描；
辅助索引：根据索引键创建一棵B+树，叶子节点存放索引键值、以及该索引键值指向的主键；要书签查找，高度一般小于聚集索引；

也就是说，如果通过辅助索引查找数据，当找到辅助索引的叶子节点后，可能还需要根据主键值查找聚集索引来得到数据，这种查找被称作书签查找（bookmark lookup）。因为辅助索引不包含整条数据，这也意味着辅助索引占用空间小，可以存放更多的键值，其高度一般小于聚集索引；

如何选定数据列添加索引？

一般的经验是，在访问表中很少一部分行时使用B+树索引才有意义。对于如性别、类型、地区字段，他们的取值范围很小，称为低选择性；而像用户ID这种取值返回很广，几乎没有重复的字段，即高选择性，此种字段使用B+树索引是最合适的。。那么问题来了，怎么看索引是否是高选择性的呢？

可以通过SHOW INDEX语句中的Cardinality列来观察。该列值表示索引中唯一只记录数量的预估值；预估值，而非准确值。如果这个值很小，不应该作为索引值；这个值是搜索引擎通过采样预估统计出来的一个值；

Mysql优化器如何选择是否使用索引&使用哪个索引？

B+树索引创建后，对该索引的使用应该只是通过该索引取得表中小部分数据。这时建立B+树索引才是有意义的，否则即使建立了Mysql优化器也能选择不使用索引；

联合索引：

我们可以创建单个列的索引，也可以创建2个列或2个以上列的联合索引；本质上联合索引还是一颗B+树，不同的是键值数量不是1，而是大于等于2。。例如，a、b两个列组成的联合索引，默认按a列进行排序，叶子节点存放（a,b）两个键值对的不同数据值。a是有序的、b是无序的，所以直接按b条件查询无法用到该索引；但是可以对第二个键值进行排序。。

联合索引特性：

可以对第二个键值进行排序。例如： select * from table where a='xxx' order by b desc；

覆盖索引：

InnoDB支持覆盖索引（covering index）也称索引覆盖（index covering）,即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用覆盖索引的好处：

辅助索引不包含整个行记录的所有信息，故其大小要远小于聚集索引，一次可以减少大量的IO操作。
对于某些统计问题，辅助索引小于聚集索引，可以减少IO操作。例如：select count(*) from table，不会选择聚集索引进行统计，表中存在辅助索引的情况下，辅助索引叶子节点包含了主键，优化器为了减少IO；使用索引覆盖特性，从辅助索引的数据中进行统计。
(a,b)这样的联合索引，一般不可以直接选择b列作为查询条件。但是对于统计操作，如果是覆盖索引，优化器会选择其进行索引查找；例如：select count(*) from table where b>='xxx' and b<='xxxx' ；

如果有使用覆盖索引，sql的EXPLAIN执行计划中exra字段提示Using index；

不使用索引查找的情况：

当我们要通过范围、join等操作，查询数据的整行信息时，没有办法使用的覆盖索引的情况、且查询的数据量超过占整个表的20%左右时，就会使用PRIMARY聚集索引进行全表扫描；例如：select * from table where a>'xxx' and a<'xxxx';

原因：首先查询整行信息，覆盖索引无法做到，因此对a索引检索到指定数据后，还需要进行一次书签查找来获取整行信息。虽然a索引是有序的，但书签查找是无序的，因此变为了磁盘的随机读取；如果访问的数据量很小，那么优化器会选择辅助索引，但是当访问数据量占整个表的数据的很大一部分（一般是20%左右）,优化器会选择通过聚集索引来查找数据。

如果用户使用的是固态硬盘，随机读取的性能也是可以接受的，索引可以使用关键字FORCE INDEX来强制使用某个索引进行范围整行数据查找；例如：select * from table FORCE INDEX(a) where a>'xxx' and a<'xxxx'；

针对Mysql查询优化器的改进

INDEX HINT索引提示:

index hints可以人为显示的告诉mysql优化器要如何完成select，比如强制走某(些)索引或忽略某(些)索引。
其中，被指定的索引必须要有索引名。以下两种情况可能需要用到INDEX HINT:

MySQL数据库的优化器错误的选择了某个索引，导致SQL运行很慢。这个在最新版的数据库版本中非常少见。优化器在绝大部分情况下工作的非常有效和正确。
某些SQL语句可以选择的索引非常多，这时优化器选择执行计划时间的开销可能会大于SQL语句本身例如优化器分析Range查询本身就是比较耗时的操作。这时DBA或开发人员分析最优的索引选择，通过index hint来强制使优化器不进行各个路径的成本分析直接选择指定的索引来完成查询

官方提供的语法为：

index_hint:

  USE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
  | IGNORE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
  | FORCE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)

例如：

测试用表：

mysql> CREATE TABLE hints_test(col1 int, 
   -> col2 int, 
   -> col3 int, 
   -> KEY idx_1(col1),
   -> KEY idx_2(col2),
   -> KEY idx_3(col3)); 
Query OK, 0 rows affected (0.09 sec)

随机插入一些数据：

mysql> SELECT * FROM hints_test;
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    1 |    2 |    3 |
|    2 |    2 |    3 |
|    2 |    3 |    3 |
|    3 |    3 |    5 |
|    3 |    1 |    2 |
|    2 |    1 |    1 |
|    2 |    3 |    3 |
|    4 |    4 |    4 |
|    6 |    5 |    3 |
+------+------+------+
9 rows in set (0.00 sec)

正常select（注，explain结果部分省略，下同）

mysql> EXPLAIN SELECT col1, col2, col3 
    -> FROM hints_test 
    -> WHERE col1=1 AND col2=2\G
*************************** 1. row ***************************
        table: hints_test
         type: ref
possible_keys: idx_1,idx_2
          key: idx_1
      key_len: 5
          ref: const

加一个复合索引

mysql> ALTER TABLE hints_test ADD INDEX idx_1_2(col1,col2);
Query OK, 0 rows affected (0.57 sec)
Records: 0  Duplicates: 0  Warnings: 0

①指定使用idx_1_2索引

mysql> EXPLAIN SELECT col1, col2, col3 
    -> FROM hints_test 
    -> USE INDEX (idx_1_2) 
    -> WHERE col1=1 AND col2=2\G
*************************** 1. row ***************************
        table: hints_test
         type: ref
possible_keys: idx_1_2
          key: idx_1_2
      key_len: 10
          ref: const,const

②忽略目前表中的三个索引

mysql> EXPLAIN SELECT col1, col2, col3 
    -> FROM hints_test 
    -> IGNORE INDEX (idx_1_2,idx_1,idx_2) 
    -> WHERE col1=1 AND col2=2\G  
*************************** 1. row ***************************
          table: hints_test
           type: ALL
  possible_keys: NULL
            key: NULL
        key_len: NULL
            ref: NULL

再次正常select：
此时col2走了索引，而order by col3没有走索引。

mysql> EXPLAIN SELECT col1, col2, col3  
    -> FROM hints_test 
    -> WHERE col2=2 ORDER BY col3\G        
*************************** 1. row ***************************
          table: hints_test
           type: ref
  possible_keys: idx_2
            key: idx_2
        key_len: 5
            ref: const

③忽略idx_2索引，此时全表扫描：

mysql> EXPLAIN SELECT col1, col2, col3 
    -> FROM hints_test 
    -> IGNORE INDEX (idx_2) 
    -> WHERE col2=2 
    -> ORDER BY col3\G                            
*************************** 1. row ***************************
         table: hints_test
          type: ALL
 possible_keys: NULL
           key: NULL
       key_len: NULL
           ref: NULL

④强制对order by语句使用idx_3索引：

mysql> EXPLAIN SELECT col1, col2, col3 FROM hints_test 
    -> FORCE INDEX FOR ORDER BY (idx_3) 
    -> IGNORE INDEX (idx_2) 
    -> WHERE col2=2 
    -> ORDER BY col3\G
*************************** 1. row ***************************
          table: hints_test
           type: index
  possible_keys: NULL
            key: idx_3
        key_len: 5
            ref: NULL

同理，除了WHERE和ORDER BY，可以同样对GROUP BY、JOIN操作进行USE、IGNORE、FORCE三种HINTS。
写法为：(USE, FORCE, IGNORE) and by scope (FOR JOIN, FOR ORDER BY, FOR GROUP BY).

Multi-Range Read (MRR):

Mysql数据库5.6版开始支持Multi-Range Read(MRR)优化。MRR的目的是减少磁盘的随机访问，并且将随机访问转换为较为顺序的访问，可为IO-bound类型的SQL查询语句带来性能的极大提升。MRR适用于range、ref、eq_ref类型的查询。

相关案例参考：https://www.cnblogs.com/vadim/p/7403544.html

Index Condition Pushdown(ICP):

Index Condition Pushdown (ICP)是MySQL用索引去表里取数据的一种优化。如果禁用ICP，引擎层会穿过索引在基表中寻找数据行，然后返回给MySQL Server层，再去为这些数据行进行WHERE后的条件的过滤。ICP启用，如果部分WHERE条件能使用索引中的字段，MySQL Server 会把这部分下推到引擎层。存储引擎通过使用索引条目，然后推索引条件进行评估，使用这个索引把满足的行从表中读取出。ICP能减少引擎层访问基表的次数和MySQL Server 访问存储引擎的次数。总之是 ICP的优化在引擎层就能够过滤掉大量的数据，这样无疑能够减少了对base table和mysql server的访问次数。关于到底是哪个filer下推，可以看SQL中的where条件，在数据库中提取与应用浅析

ICP的优化用于range, ref, eq_ref, and ref_or_null访问方法，当这些需要访问全表的行。这个策略可以用于INNODB和MyISAM表。

a little daily

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
3、Mysql的B+树索引

当我们聊数据存储方案改进的时候，我们应该考虑哪些问题？找一个锚点B+树的什么特性让Mysql独爱用它作为索引数据的存储结构？ B+树索引如何去分类？分类的依据是什么？（聚集索引、非聚集索引）应用中去构建B+树索引列的依据是什么？如何统计Cardinality？构建哪些索引（联合索引）（Cardinality） InnoDB搜索引擎的查询优化器如何去抉择使用索引or不使用索引进行全表扫描...
复制链接

扫一扫