MySQL - 索引

索引是什么？

索引是一种有序的数据结构。 类比书的目录，目的是为了提高数据查询的效率；

索引的分类

按数据结构分类可分为：B+tree索引、Hash索引、Full-text索引
按物理存储分类可分为：聚簇索引、二级索引(辅助索引)
按字段特性分类可分为：主键索引、普通索引、前缀索引
按字段个数分类可分为：单列索引、联合索引(复合索引、组合索引)

索引的常见数据结构

哈希表
- 对索引的key进行一次hash运算就可以定位出数据存储的位置
- 仅能满足 = 、IN；不支持范围查询
- 有哈希冲突，哈希冲突后，在key位置后面追加链表
有序数组
- 查询很快。有序排列，使用二分查找，可以快速得到结果；支持范围查询
- 更新数据很慢。需要挪动后面所有记录，成本太高
二叉树

二叉树的特点是：父节点左子树所有节点的值都小于父节点的值，右子树所有节点的值大于父节点的值。
二叉树是搜索效率最高的；但实际大多数的数据库存储并不使用二叉树；原因是，索引不止在内存中，还要写到磁盘上；二叉树只有两个节点，数据量大会导致树太高了，要很多次磁盘IO。大大影响了性能。

- 普通二叉树：如果依次插入[1,2,3,4,5,6]，普通二叉树不会旋转自平衡，导致右子树有5个节点；左子树没有节点；成了有序数组。

红黑树(平衡二叉树) - HashMap底层是红黑树+链表
1. 红黑树和二叉树类似，他的左节点比父节点小，右节点比父节点大；每个节点只有两个孩子！但红黑树是平衡的，平衡树的插入和删除时，会通过旋转操作将高度保持在logN。树的两边是平衡的；
2. 缺点同样存在，数据量大的时候，树太高了；多次磁盘IO，影响性能
B-树
1. B树首先解决了“每个节点只能有两个孩子”的问题，B树是N叉树；而且会自旋转为平衡N叉树；大大降低了树的高度，减少磁盘IO次数
2. B树的每个节点都存数据
3. 相邻节点无指针；不关联
B+树 - InnoDB用的B+树

B+树，在B树的基础上加以改进；更加符合数据库读写的使用：
首先保留了N叉树，降低树的高度。
其次，B+树的一个优化是：只有叶子节点存储数据；非叶子节点只存储索引(冗余的索引字段值)；这样一来，每层的储存空间是固定的话，就可以存更多的节点了 --> 树的高度更矮，更胖。
另一个重要优化就是：相邻的两个节点有指向指针，使用指针关联存下双方的地址值；例如范围查询时：找到A节点，就能知道下一个B节点的地址在哪里；直接去找

InnoDB的表结构

在InnoDB中，每一张表其实就是多个B+树，即一个主键索引树和多个非主键索引树
执行查询的效率，使用主键索引 > 使用非主键索引 > 不是用索引
如果不使用索引进行查询，则从主索引 B+树的叶子节点进行遍历。
每个索引在InnoDB里面对应一颗B+树。

关于B+树你应该知道的几件事

1、表存储结构

单位是：表 > 段 > 区 > 页 > 行
无论你读一行数据还是多行数据，都会将行所在的那个页读出加载到内存中；也就是说，存储空间的基本单位是页
一个页就是一个B+树的一个节点。数据库I/O操作的最小单位是页，与数据库相关的内容都会存储在页的结构里；

2、B+树的索引结构

一棵B+树种，每个节点都是一个页。新增一个节点时，就会申请一个页空间
同一层的节点之间，通过页的结构构成了一个双向链表
一个非叶子节点：包含了多个索引行，每个索引行中储存索引键和下一层页面的指针
一个叶子节点：存储了关键词和行记录，在节点内部（也就是页结构的内部）记录之间是一个单向的链表

3、B+树页节点结构

将所有的记录分成几个组，每组会存储多条记录。
页目录存储的是槽(slot)，槽相当于分组记录的索引，每个槽指针指向了不同组的最后一个记录。我们通过槽定位到组，再查看组中的记录；
页的主要作用是存储记录，在页中记录以单链表的形式进行存储。

单链表定义：链式储存，对连续空间无要求，不可随机存取，耗费一定空间存放指针。存放数据元素及指向下一个节点的指针
单链表优点是插入、删除方便，缺点是检索效率不高，最坏的情况要遍历链表所有的节点。因此页目录中提供了二分查找的方式，来提高记录的检索效率

4、B+树的检索过程

从根节点开始，逐层找到叶子节点。
找到叶子节点对应的数据页，将数据页加载到内存中，通过页目录的槽采用二分查找的方式，先找到一个粗略的记录分组。
在分组中通过链表遍历的方式进行记录的查找

InnoDB为什么用B+树数据结构存储数据？

B+树够矮够胖，能有效的减少磁盘IO次数，提高性能。
B+树能够很好的配合磁盘读写特性，减少单次查询的磁盘访问次数。

索引设计原则

代码先行，索引后上
- 一般应该等主体业务都开发完毕，把涉及的该表相关的SQL都要拿出来，分析之后再建索引；
- 根据实际情况，加上未来预期；建立合适的索引
联合索引尽量覆盖条件
- 尽量少建单值索引
- 联合索引要考虑最左前缀原则
不要在小基数字段上建立索引
- 比如 sex、学历字段；数据值重复率高，索引意义不大
- 索引是有序的数据结构；我们要根据索引的优势来使用
长字符串我们可以采取前缀索引
- 例如 qq邮箱(前8位)、身份证号(后8位)
- 缺点：增加扫描次数，而且不能使用联合索引；优点是节省空间。
  - 我们查询email = zhangsan2@emial.com 这条记录；表中数据如下：
```
 zhangsan1@email.com
 zhangsan2@email.com
 zhangsan3@email.com
 zhangsan4@email.com
```
  - 使用普通索引 (email整个字符串的索引结构)：执行顺序是这样的
    1. 到二级索引树中，找到满足索引值是 " email = zhangsan2@email.com" 这条记录，取得主键ID = 2；
    2. 到主键索引树中，找到ID = 2的行，判断email的值是正确的，将这行记录加入结果集；
    3. 取二级索引树上刚刚查到的位置的下一条记录(叶子节点双向指针)，发现已不满足 " email = zhangsan2@email.com " 这条记录了，循环结束；
    4. 这个过程中，只需要回表一次，所以系统认为只扫描了一行；
  - 使用前缀索引（即 email(6) 索引结构）
    1. 到二级索引树中，找到满足索引值是 ”zhangs“这条记录，取得主键ID = 1；
    2. 到主键索引树中，找到ID = 1的行，判断email的值不是 zhangsan2@email.com ，将这行记录丢弃；
    3. 取二级索引树上刚刚查到的位置的下一条记录，发现仍是zhangs，取得ID = 2；再到主键索引中取整行数据，然后判断，这次值对了，将这行记录加入结果集；
    4. 重复上一次，直到在二级索引树上取到的值不是 zhangs 时，循环结束；
    5. 在这个过程中，要回表4次取数据判断，也就是扫描了4行；
where 与 order by 选择索引冲突时，优先使用where
- 时常会遇到 where后面字段如果走了索引，order by 字段就无法走索引的情况
- 这个时候，建议选择where 字段走索引；因为where执行顺序在前，可以快速把数据量由大缩小；性能价值比order by要高
基于慢SQL查询做优化

索引优化

覆盖索引-联合索引
1. 覆盖索引的最左前缀原则；
  1. 在建覆盖索引时，按照最左前缀原则安排索引内的字段顺序。
  2. 覆盖索引是按照从左到右的字段排序的。
  3. 索引的核心就是有序。如果你跳过最左字段，直接查其它字段；索引就不是有序的，只能全表扫描；
2. 索引下推：MySQL 5.6 引入的索引下推优化（index condition pushdown）
  - MySQL 会在索引遍历的过程中，对索引中包含的字段，先做判断；直接过滤掉不满足条件的记录，减少回表次数。
3. 使用覆盖索引可以覆盖我们的查询需求，进而可以减少树的搜索次数，显著提高查询性能；使用覆盖索引是一个常用的性能优化手段
Order By 与 Group By 优化
- Order By 排序
  1. MySQL支持两种方式的排序 filesort 和 index
    - Using index是指MySQL扫描索引本身完成排序。Using index 是扫描二级索引
    - Using filesort 是扫描主键索引，全表扫描
    - index效率高，filesort效率低
  2. Order By 满足两种情况会使用 index排序。
    1. Order By语句使用索引最左前列。
    2. 使用where子句和 order by子句条件列组合满足索引最左前列。
    3. 尽量在索引列上完成排序，遵循索引建立时的最左前缀原则(创建索引列的顺序)
    4. 如果order by的条件不在索引列上，就会产生 Using filesort：文件排序
      1. filesort文件排序方式
        
        单路排序：全字段排序
        
        是一次性取出满足条件的所有行字段数据，到sort buffer中进行排序
        
        双路排序(回表排序模式)：rowid 排序
        
        首先根据条件取出相应的排序字段和可以直接定位数据的行ID
        
        然后在 sort buffer中进行排序，排序结束后，需要根据行ID回表取其它需要的字段
        
        如果选择哪种文件排序方式：
        
        MySQL 比较系统变量** max_length_for_sort_data** 的大小和需要查询的字段总大小来判断使用哪种排序模式
        
        字段总大小 < max_length_for_sort_data，使用单路排序
        
        字段总大小 > max_length_for_sort_data，使用双路排序
- Group By
  - Gruop By 与 order by很类似，实质是先排序后分组，遵循索引创建顺序的最左前缀法则。
  - 对于Group By的优化：如果分组时不需要排序，可以加上 order by null 禁止排序，可以跳过排序；
  - 注意：where高于having，能写在where中的限定条件就不要去having限定；
分页查询优化
1. 最高效的分页是：根据自增且连续的主键排序的分页查询
  
  下面语句看似只查询了 5 条记录，实际这条 SQL 是先读取 90001 条记录，然后抛弃前 90000 条记录，然后读到后面 5 条想要的数据
```
mysql> select * from employees limit 90000,5;
```
  如果主键是递增并且连续的话，则上面SQL可以改为：按照主键去查询从第 90001开始的五行数据
```
mysql> select * from employees id > 90000  limit 5; -- 这个语句必须保证主键是连续递增的，且结果是按主键排序的
```
2. 还有一种是根据非主键字段排序的分页查询
  
  这条SQL，没有走name索引；
  
  原因是：扫描整个索引并查找到没索引的行(可能要遍历多个索引树)的成本比扫描全表的成本更高，所以优化器放弃使用索引。
  
  使用了filesort 文件排序；
```
mysql>  select * from employees ORDER BY name limit 90000,5;
```
  - 知道了没有走索引排序的原因，怎么优化呢？其实关键是让排序时返回的字段尽可能少
  - 优化思路是：可以先让排序和分页操作查出主键(不用回表)，然后根据主键查到对应的记录
  - 这样排序时就会使用 Using index排序
Join 表关联的优化
1. MySQL表关联有两种常见算法；
  
  能否用上被驱动表的索引，对join语句的性能影响很大。
  
  下面两种算法是由能否用上被驱动表的索引决定的；
  1. Index Nested-Loop Join(索引join) NLJ
    1. 如果可以用到被驱动表的索引，则会选择NLJ算法
    2. NLJ算法过程类似代码的嵌套循环：
      1. 首先遍历驱动表，一次取一行数据；从行数据中取到关联字段a
      2. 去被驱动表中，查询索引字段a对应的行数据，然后取出两表的结果合集；
      3. 在这个join过程中，对驱动表使用了全表扫描，对被驱动表使用了索引树搜索
  2. Block Nested-Loop Join（分块join） BNL
    1. 不能使用被驱动表的索引，只能使用BNL算法，这样的语句尽量不要使用join
    2. BNL算法过程为：
      1. 把驱动表的数据读取到线程内存 join buffer中，如果是select * ，则会把整张表的数据放到内存中；
        
        如果内存不够，会分段存放：先放第一段数据，到被驱动表中遍历查找结果；把合集结果存下，清空join buffer后，在将第二段数据放入，重复上述动作，直至数据取完；
      2. 扫描被驱动表，把被驱动表的每一行取出来，跟join buffer作对比，满足条件的，作为结果集的一部分返回。
      3. 在这个join过程中，对驱动表使用了全表扫描（只不过在内存中，内存不足就会分段），对被驱动表同样使用了全表扫描；扫描行数次数大幅增加，不推荐使用；
  3. 上面两种算法都还有优化空间： MySQL 5.6版本开始引入：Batched Key Access(BKA) 算法
    1. 本质是对 NLJ算法的优化；
    2. NLJ 算法执行的逻辑是：从驱动表 t1，一行行地取出 a 的值，再到被驱动表 t2 去做 join。也就是说，对于表 t2 来说，每次都是匹配一个值。这时，MRR 的优势就用不上了。
      
      BKA的方法就是，从表 t1 里一次性地多拿些行出来，一起传给表 t2。
    3. BKS算法优化是MySQL已经内置支持的，建议默认使用
      
      如果要使用 BKA 优化算法的话，你需要在执行 SQL 语句之前，先设置 set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
      
      其中，前两个参数的作用是要启用 MRR。这么做的原因是，BKA 算法的优化要依赖于 MRR。
2. join驱动表的选择
  - 小表驱动大表
  - join的关联字段，用大表的索引！
In的用法： in 操作能避免则避免；控制在1000个之内
count(*) 查询优化
1. count的语义：
  - count()是一个聚合函数，对于返回的结果集，一行行地判断：参数如果不是null，累积值就加1，否则不加。最后返回累积值
    - count(*)、count(主键id)、count(1) 都表示满足条件的结果集总行数。
    - 而count(字段)，则表示返回满足条件的数据行里面，参数“字段”不为null的总个数。
  - count(*) 在InnoDB引擎中，会遍历全表，虽然结果准确，但是会导致性能问题。
    - 扫描全表的原因是：由于事务、多版本并发(MVCC)存在，应该返回多少行，提前是不确定的；得实时全表扫描
2. 字段有索引
  - count(*)≈count(1)>count(字段)>count(主键 id)
  - 为何count(字段) 比 count(主键id)还快？
    
    count(字段)走二级索引，叶子节点只存主键id； count(主键id)走主键索引，叶子节点存了整行数据；所以 count(字段)更快
3. 字段无索引
  - count(*)≈count(1)>count(主键 id)>count(字段)
4. 常见优化方法
  1. 查询MySQL自己维护的总行数
  2. ```
  show table status
```
3. 将总数维护到Redis里（有事务版本问题，数据不准确）
4. 增加数据库计数表（数据是准确的）
普通索引和唯一索引应该怎么选择？
1. 对比查询：
  1. 两个索引的查询类似，都是从二级索引树搜索到主键ID，然后到主键索引树的叶子节点找到数据；将数据所在的页加载到内存中；
  2. 区别是：
    1. 普通索引查到第一条数据后，还要取下一条记录，直到数据不满足条件后结束；
    2. 由于定义了唯一性，唯一索引查到第一条数据后，就会停止继续检索；
      - 这个区别带来的性能差距，微乎其微，可以忽略不计
      - 原因是：
      - 当读一条记录时，不是将这条记录从磁盘查出；而是将数据所在的页一起加载到内存；
      - 数据从磁盘加载到内存的单位是页；页的大小默认是16K，可以存近千个Key；所以普通索引要找下一条记录时，直接在页中取下一条即可；只需要多一次指针寻址和计算，在内存中，损耗可以忽略不计；
      - 特殊情况，你查的那条记录就在页的最后一条，要取下一条记录，必须要读下一数据页，这个操作会复杂写；但是这种情况发生的概率会很低。所以计算平均损耗时，仍可以认为这个操作成本对于CPU来说可以忽略不计。
    3. 普通索引和唯一索引的查询能力是没差别的
2. 理解 change buffer
  1. 理解描述
    
    当需要更新一个数据页时，如果数据页在内存中就直接更新，
    
    而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。
    
    在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。
  2. change buffer 减少了随机磁盘访问，对更新有加速作用；
  3. **change buffer使用的是 buffer pool中的一部分内存。**他是可以持久化的数据。他在内存中有拷贝，也会被写入磁盘上。
  4. 将change buffer 写入磁盘的过程称为 merge，merge时刻才是数据更新的真正时刻
    1. 访问这个数据页会触发merge
    2. 系统有后台线程定期merge
    3. 在数据库正常关闭(shot down)的过程中，也会执行merge操作；
  5. change buffer 的使用场景：
    1. 只限用于普通索引，不适用于唯一索引
    2. 对于写多读少的业务来说，change buffer的使用效果最高；merge的收益最大；
    3. 如果一个业务是更新之后立马查询，更新记录了change buffer立马又要从磁盘访问这个数据页，会触发merge过程。这样随机磁盘IO的次数不会减少，反而增加了维护change buffer的代价；所以对于这种业务，反而起到副作用；
    4. 我们要根据业务场景，灵活运用！
3. 对比更新
  1. 更新操作的记录在内存中时
    - 普通索引：会直接更新这个值，语句执行结束
    - 唯一索引：判断到没有冲突后，更新这个值，语句执行结束
    - 二者的区别只是一个判断，只会耗费微小的CPU时间。这不是我们关注的重点
  2. 更新操作的记录不在内存中时
    - 普通索引：将更新记录在 change buffer中，然后语句执行结束
    - 唯一索引：需要将数据读到内存中，判断到没有冲突后，更新这个值，然后语句执行结束；
    - 区别是唯一索引的唯一性，导致无法使用change buffer；必须从磁盘读到内存，这个成本非常高；
4. 结论：
  - 这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。业务可以接受的前提下，从性能的角度出发，我建议你尽量选择普通索引。 如果业务代码层面没法做唯一性保证，那么选择数据库的唯一索引是没问题的；
  - 普通索引和 change buffer 的配合使用，对于数据量大的表的更新优化还是很明显的。

MySQL - 02索引

MySQL - 索引

索引是什么？

索引的分类

索引的常见数据结构

InnoDB的表结构

关于B+树你应该知道的几件事

1、表存储结构

2、B+树的索引结构

3、B+树 页节点结构

4、B+树的检索过程

InnoDB为什么用B+树数据结构存储数据？

索引设计原则

索引优化

3、B+树页节点结构