MySQL索引

MySQL数据库中两个主要的索引是B+tree索引和哈希索引。

B+tree数据结构的特点：

所有关键字信息都在叶子节点中
叶子节点可以按关键字大小进行排序
所有的数据都保存在叶子节点中
B+tree的索引是有顺序的双向链表，检索速度极快

聚簇索引和普通索引

MySQL的B+tree索引可以分为两大类，聚集索引和非聚集索引（普通索引）。InnoDB引擎表都是根据索引组织表，聚集索引其实是一种索引组织形式，索引键值的逻辑顺序决定了表数据行的物理存储顺序。聚集索引叶子结点存放表中所有行数据记录的信息，所以经常说数据即索引，索引即数据。

普通索引在叶子节点中并不包含所有行的数据记录，只是会在叶子结点存有自己本身的键值和主键的值。在检索数据时，通过普通索引叶子节点上的主键来获取到想要查找的行数据记录。

主键索引和唯一索引

主键索引其实就是聚集索引，每张表中有且仅有一个主键，可以由表中的一个或者多个字段组成。

主键索引必须满足非空和唯一，另外使用自增的主键索引可以极大的提高数据的存取效率。

alter table table_name add primary key(column)

唯一索引要求所在的列不允许出现重复值但允许有NULL值，同时一张表中可以存在多个唯一索引

alter table table_name add unique (column)

覆盖索引

MySQL只需要通过索引就可以返回查询所需要的数据，不需要回表。在执行计划中的extra列就会显示Using index关键字

mysql> EXPLAIN SELECT empno,ename from emp WHERE ename = 'WARD'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: NULL
         type: ref
possible_keys: idx_ename
          key: idx_ename
      key_len: 33
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

使用覆盖索引必须要列出不需要回表即可查询的字段

前缀索引

对于BLOB、TEXT或者很长的VARCHAR类型的列，为这些列的前几个字符建立索引，这样的索引称为前缀索引。缺点就是不能在order by或group by中使用它们，同时也不能用于覆盖索引。

alter table table_name add key(column_name(prefix_length))

具体建立索引的前缀长度prefix_length需要根据实际列中数据的特点指定

联合索引

联合索引又称复合索引，是在表中两个或两个以上的列上建立的索引。利用索引中的附加列，可以缩小检索的范围。

create index idx_ename_job_ename on emp (job, ename)

联合索引的使用过程中，必须要满足最左前缀原则，一般把选择性高的放在前面。

-- 可以使用联合索引
EXPLAIN SELECT * from emp WHERE job = 'SALESMAN' and ename = 'JONES'
-- 不能使用联合索引  可以转为union的写法
EXPLAIN SELECT * from emp WHERE job = 'SALESMAN' or ename = 'JONES'
-- 不能使用联合索引
EXPLAIN SELECT * from emp WHERE ename = 'JONES'
-- 可以使用联合索引  extra:Using index for group-by
EXPLAIN SELECT job from emp GROUP BY JOB
-- 可以使用联合索引  extra:Using index; Using temporary; Using filesort
EXPLAIN SELECT ename from emp GROUP BY ename
-- Using index
EXPLAIN SELECT ename,JOB from emp GROUP BY JOB,ename
-- Using index; Using temporary; Using filesort
EXPLAIN SELECT ename,JOB from emp GROUP BY ename,JOB

哈希索引

哈希索引采用hash算法，将键值计算为哈希值，特别注意的是哈希索引只能用于等值查询，不能进行排序、模糊查找、范围查询等。检索时不需要向B+tree逐级查找，只需一次计算立刻可以定位到数据位置，查询速度极快。

ICP、MRR和BKA

ICP

ICP是MySQL使用索引检索表数据的一种优化方式，全称 Index Condition Pushdown。

在MySQL5.6之前，存储引擎会通过遍历索引定位基表的中的行，然后返回给server层，再去为这些数据行进行where后的条件过滤

在MySQL5.6之后（支持ICP），如果where条件可以使用索引，MySQL会把这部分过滤操作放到存储引擎层，存储引擎通过索引过滤，把满足的行从表中读出

ICP减少了引擎层访问基表数据的次数和server层访问存储引擎的次数

show variables like '%optimizer_switch%'

结果：

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on

可以通过数据库执行命令来选择开启或者关闭ICP

set optimizer_switch='index_condition_pushdown=on|off'

当使用ICP优化时，执行计划的extra列会显示Using index condition的关键字提示

MRR

MRR全称Multi-Range Read Optimization，也是MySQL5.6之后才有的特性。

在上面说过MySQL普通索引获取数据的方式，先是通过索引页的叶子节点找到对应的主键，在通过主键找到对应的行记录。如果一张表的某一个字段的索引有一些重复的值，那么根据这个字段去做where条件时，每次取到的主键值可能不是按照顺序的，那么随机I/O的行为将会发生。

MRR的原理就是把普通索引的叶子节点上找到的主键值的集合存储到read_rnd_buffer中，然后在该buffer中对主键值进行排序，利用排好序的主键值集合访问表中具体的行记录，变随机I/O为顺序I/O，降低查询的I/O开销。

-- 默认开启参数
mrr=on 和 mrr_cost_based=on

mrr_cost_based表示通过基于成本的算法来确定开启mrr特性，on表示查询优化器自行判断是否使用MRR，设置为off则表示强制开启MRR。

默认的参数表示查询优化器使用基于成本的算法选择是否使用MRR，如果优化后发现代价过高就会不使用该项优化。

可以通过数据库执行命令来选择开启或者关闭MRR

set global optimizer_switch='mrr=on|off,mrr_cost_based=on|off'

当使用MRR优化时，执行计划的extra列会显示Using MRR的关键字提示

在生产环境中，read_rnd_buffer的值可以在4~8M之间调整

MRR原理图

BKA

BKA全称Bathched Key Access，它是提高表join性能的算法，其作用是在读取被join表的记录时使用顺序I/O。

原理：对于多表的join语句，当MySQL使用索引访问第二个join表时，使用一个join buffer来收集第一个操作对象生成的相关列值。BKA构建好key，批量传递给引擎层做索引查找。key是通过MRR接口提交给查询引擎的，这样一来使得MRR查询更加高效。

BKA原理图

-- optimizer_switch参数中的batched_key_access来控制BKA，默认是关闭的
batched_key_access=off
-- 想要开启该参数，必须在强制开启MRR的基础上才可以
set global optimizer_switch='mrr=on,mrr_cost_based=off'
set global optimizer_switch='batched_key_access=on'

当BKA被使用时，执行计划的extra会显示Using join buffer(Bathched Key Access)的关键字提示。

MRR与BKA之间的关系如下图所示

MRR与BKA之间的关系

存储引擎的上端是MRR，范围查询(range access)中MySQL将扫描到的数据存入read_rnd_buffer，对其按照主键进行排序，然后使用这些排序好的主键进行回表，随机读变为顺序读。

在BKA中，被连接表使用ref、eq_ref索引扫描的方式时，第一个表中扫描到的键值放到join_buffer中，然后调用MRR接口进行排序和顺序访问并且通过join条件得到数据，这样连接条件也变成了顺序对比。

MRR和BKA原理参考MariaDB文档： https://mariadb.com/kb/en/multi-range-read-optimization/