MySQL的B+树与索引设计优化

B+树结构

对于表：

create table T(
id int primary key, 
k varchar(64) not null, 
name varchar(64) not null,
index (k)
)engine=InnoDB default charset=utf8mb4;

B+树结构如下图所示：

从图中可以看出：
1、非叶子节点保存的都是索引值，对于主键索引，叶子节点保存了所有数据；对于二级索引，叶子节点保存的是key与key对应的主键值；
2、每个节点包含m个元素，m+1个孩子，每个元素的左孩子<这个元素的值，右孩子>=这个元素的值；
3、根节点只包含一页；
4、所有的叶子节点使用一个双向链表连接起来，叶子节点按key从小到大排列；
5、由于非叶子节点只包含key，所以B+树相比B树更加的矮胖，从而可以减少磁盘IO。由于MySQL一页的大小是16KB，索引大小为4字节时，m大概是1200左右，那么对于树高为4的索引，大概可以保存14亿多个元素。可以看出对于一个亿级别的表，合理设置索引，那么树高为4。而且树的第一层与第二层一般会缓存到内存中，那么只需要一次磁盘IO就可以找到数据所在的页；这也是为什么不用红黑树或者B树作为索引树的原因；
6、如果select语句只能使用二级索引且select需要的数据不是全部在这个二级索引上，那么就要搜索2次索引树，第一次是通过二级索引找到主键ID，然后通过主键ID在主键索引树上找到对应的元素，第二次的搜索称为回表；为此我们需要尽量使得select需要的数据都在索引树上，如果select需要的数据都在索引树上，我们称之为索引覆盖；
7、对于如上的表，如果我们需要插入数据(21,o,o1)，如果m>2，那么21需要插入20与25行的中间，从而导致25一行数据向后移动了；如果m=2，那么会导致页分裂，继而导致根节点也出现页分裂，最终树的深度就增加了1；
8、对于如上的一点，如果随机插入元素，那么会导致大量的页分裂，页分裂后会导致很多空洞，从而降低树的空间利用率，为此我们插入元素的时候，尽量按照主键索引的递增顺序插入，这也是为什么很多人建议建表的时候都添加一个自增的整形主键索引；
9、二级索引的叶子节点保存的是主键索引的key，所以主键索引应该尽可能的小，而如果用一个自增的整形主键索引，那么主键的索引的大小就是4字节或者8字节，这也是“很多人建议建表的时候都添加一个自增的整形主键索引”的另外一个原因；
10、但是什么时候不需要自增的主键呢？那就是表只需一个索引且是唯一索引的时候，这就是典型的(key,value)型数据；
11、当我们需要范围查询，只需要找到范围的上界或者下界所在的叶子节点，然后向左或者向右遍历即可，而如果使用hash作为索引的算法，那么范围查询将要遍历所有数据，为此DB一般不用hash作为索引算法；
12、既然有页分裂，那么就有页合并，当删除数据时，如果页的利用率较低，那么就会触发页合并。

索引设计原则

1、正如如上的分析，主键索引应该尽可能的小，而且需要按插入有序，主要是基于如下的原因：
A.主键索引尽可能的小，那么二级索引的叶子节点就会小很多，一页就可以放入更多的行，从而提高二级索引的IO效率与空间利用率，进而使得同样大小的缓存可以放入更多的行；主键索引树叶类似；
B.索引应该尽可能按插入有序，特别是主键索引，这样可以减少移动数据与页分裂，提高磁盘的空间利用率，进而使得同样大小的缓存可以放入更多的行。
2、尽量使用覆盖索引，这样可以减少回表查询；
3、如果where里的条件的部分字段在索引中，即使只能利用索引的一部分，5.6及5.6以后的MySQL也可以利用索引下推优化提高效率；
4、复合索引区分度高的应该放在前面，但是要以索引复用为前提的，比如表有a,b两个字段，a字段的区分度搞于b字段，但是如果建立ab的复合索引，那么就得再建立一个b的索引，此时应该优先只建立一个ba的索引，因为索引也是有维护成本的；总之如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的；这就是最左前缀原则；
5、字符串特别是比较长的字符串，应该建立前缀索引，那么建立多长的前缀索引呢？唯一的判断标准就是区分度，那么怎么判断区分度呢？还是以上面的表为例，假设我们要得name 字段加上索引，那么可以用如下的语句判断区分度：

select count(distinct name) as L from T;
select count(distinct left(name,4)）as L4, 
count(distinct left(name,5)）as L5, 
count(distinct left(name,6)）as L6, 
count(distinct left(name,7)）as L7 from T;
L4/L，L5/L，L6/L，L7/L就可以得到区分度

当然，使用前缀索引很可能会损失区分度，所以你需要预先设定一个可以接受的损失比例，比如 5%。然后，在返回的 L4~L7 中，找出不小于 L * 95% 的值，假设这里 L6、L7 都满足，你就可以选择前缀长度为 6；
6、字符串特别是比较长的字符串，如果区分度达到要求需要很长的前缀，那么也是不合适的。但是如果倒序的区分度较高，我们可以倒序存放，然后取前缀索引。但是如果倒序的区分度达到要求也要很长，那么我们只能取hash值了，此时表中需要添加多一个hash字段且需要见索引，那么此时的where可以这样写 where hash_value=hash(‘name’) and name=’name’，这2种的缺点都是不支持范围查询；这2种方法可以根据实际情况选择；
7、在业务已经可以保证唯一性约束时，在插入数据后不会立马去读取的场景下，优先使用普通索引。因为INNODB内部对于普通索引，当插入数据时，可以有效的利用change buffer，减少随机读的发生。

哪些情况会导致索引用不上

1、假设有复合索引a,b，如果where条件是这样的 where a>1 and b=1，那么只能用到索引前缀a了，此时我们可能需要把索引改成b,a的顺序；
2、条件字段函数操作，还是上面的那个表，假设有如下的语句：

Select * from T where id+1>1;

由于条件字段有+1的操作，那么导致MySQL会无法使用索引。但是这种情况MySQL还是会根据条件与需要返回的字段判断是否使用索引树搜索；
3、隐式类型转换，还是如上的表，假如k字段全是是数字的字符串。那么有些人可能会写出如下的SQL语句

Select * from T where k > 1;

由于k字段是字符串类型而1是数值类型，为此MySQL会将k转换为数值类型再进行比较，那么也会导致索引用不上。但是如果k是int类型，如下的SQL语句

Select * from T where k > ‘1’;

由于MySQL是把字符串转换为数值类型，因此k字段并不会有任何函数操作，可以用上索引，但是MySQL多了一步需要将 ‘1’转换为1。为此这条经验告诉我们，字段类型如果是数值类型就不加引号，非字符类型就加引号，避免隐式类型转换；
4、隐式编码转换，假设有如下的表与语句

create table T(
id int primary key, 
k varchar(64) not null, 
name varchar(64) not null,
index (k)
)engine=InnoDB default charset=utf8mb4;
create table T2(
id2 int primary key, 
k2 varchar(64) not null, 
name2 varchar(64) not null,
index (k2)
)engine=InnoDB default charset=utf8;
select T.*,T2.* from T
inner join T2 on k=k2
where id =1;

那么MySQL会先再表T中搜索出id=1的那一行，然后使用字段k的值与表T2的字段进行比较，由于k是utf8mb4类型，k2是utf8类型，utf8mb4是utf8的超集，为此k2会隐式的转换为utf8mb4类型，相当于在条件字段里面有函数操作。也会导致用不上索引。但是如果把SQL改写成：

select T.*,T2.* from T2
inner join T on k=k2
where id2 =1;

那么就是拿到k2去表T匹配k=k2的行，那么会把k2转换为utf8mb4，为此可以用上索引；
5、有时明明SQL语句写的没有问题，但是MySQL却使用了错的索引了，这时很可能是由于索引统计信息不准确导致的。我们可以使用SHOW INDEX命令查看Cardinality的值

SHOW INDEX FROM City\G
*************************** 1. row ***************************
        Table: city
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: ID
    Collation: A
  Cardinality: 4188
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: city
   Non_unique: 1
     Key_name: CountryCode
 Seq_in_index: 1
  Column_name: CountryCode
    Collation: A
  Cardinality: 232
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:

如果Cardinality与实际的区分度差别很大，那么可以执行ANALYZE TABLE
T分析表，这个命令可以重新采样索引的Cardinality。但是在分析过程中会对表加读锁，为此分析期间表的修改操作会被堵塞，但是分析通常都很快就可以完成；
6、但是有时索引的统计信息也没有问题，MySQL可能还是会用错索引或者没有用索引，此时我们可能就得通过优化SQL语句，添加删除索引或者通过force index其语法如下：

SELECT * FROM T FORCE INDEX(`a`) WHERE ...

但是在代码中添加foorce index往往有滞后性，此时MySQL的查询重写就派上用场了，其语法如下

mysql> insert into query_rewrite.rewrite_rules(pattern, replacement, pattern_database) values ("select * from t where id + 1 = ?", "select * from t where id = ? - 1", "db1");
call query_rewrite.flush_rewrite_rules();