MySQL 索引笔记（个人）

• InnoDB也有hash索引，只不过叫自适应哈希

• 平衡树，当查询需求和插入需求差不多的时候，就很不友好，这时候就过度到了红黑树

• AVL树最长路径只要不超过最短路径的两倍即可

• InnoDB 存储引擎加载的时候，默认加载16kb

• p1、p2、p3 指向其他磁盘块的指针，p4、p5 索引列的值，data 表明当前key对应的整行数据
  

• 上面那张图是B树，因为数据存放很占内存，所以，在B+树里面，我们只在叶子节点存放整行数据，其他时候存放指针和索引列的值就行

• MySQL里面，3层B+树可以支撑千万级别的并发量

• B+树还有个特点，叶子节点之间有个双向链表

• 在建索引的时候，有几个索引就对应几棵B+树。虽然有多棵B+树，但是只有一棵携带数据的B+树

• 以主键建的索引叶子节点带的数据，其他的索引叶子节点里面存的主键，如下图：

• 所以非主键索引要检索两次，第一次查到所属主键，第二次走主键索引，查到数据，这个过程叫“回表”

• 回表就是：走一遍普通索引，还要走一遍主键索引，才能取得数据

• 在建表的时候，没有写主键，系统会选一个unique列作为主键，如果没有unique，会有一个6字节的rowid作为主键

• 很显而易见，如果回表的话，会增加IO的次数，这是我们想要避免的。那么一定要回表吗？当然不是----覆盖索引就不会回表

• 覆盖索引就是你where后面的字段刚好和你所建索引字段对应，或者是最左子集。这样在索引列就能取得你想要的信息，就不用回表。

• 这里有一个问题：select * from tbl where id=1 和 select id from tbl where id=1 ，他们的效率一样吗？个人感觉一样，老师说不一样

• 最左匹配原则：比如索引（id，name，age）必须先匹配id才能匹配name，匹配到name，才能匹配age，就是必须当前列的左边都匹配了，才能匹配当前列
  举个栗子：索引（name，age）

  1. where name=? age=?
  2. where name=?
  3. where age=?
  4. where age=? name=?

  只有3不满足最左原则，不能使用（name，age）索引，其他的都可以。可能会疑惑第4个为什么也满足最左原则，因为MySQL也有优化器，他会帮我们调整顺序，所以4实际执行结果是where name=? age=?

• 索引下推 ：
  原先是在Server层做的筛选，现在在存储引擎层做的筛选

  5.6 之前：
  先根据name去存储引擎中拿到所有数据，然后在server层对age进行数据过滤

  5.6之后：
  根据 name，age 两个列的值去获取数据，直接把数据返回

  举例子：索引(name，age)
  select * from tbl where name=“张%” and age=10
  根据前面说的“最左前缀原则”，该语句在搜索索引树的时候，只能匹配到名字第一个字是‘张’的记录，然后得到对应的主键，逐个回表，在主键索引找到相应的记录，再对比age字段的值是否符合

  但是！MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表字数。

  下面图1、图2分别展示这两种情况。
  图1：
  图2：
  
  图 1 中，在 (name,age) 索引里面我特意去掉了 age 的值，这个过程 InnoDB 并不会去看 age 的值，只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。因此，需要回表 4 次。

  图 2 跟图 1 的区别是，InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。在我们的这个例子中，只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次。

  总结：如果没有索引下推优化（或称ICP优化），当进行索引查询时，首先根据索引来查找记录，然后再根据where条件来过滤记录；在支持ICP优化后，MySQL会在取出索引的同时，判断是否可以进行where条件过滤，也就是说提前执行where的部分过滤操作，在某些场景下，可以大大减少回表次数，从而提升整体性能。

• 前面提到了MySQL的优化器：
  主要分为两种：
  CBO：基于成本的优化器
  RBO：基于规则的优化器
  大部分的优化器都是选择的CBO

• 聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据

• 非聚簇索引（二级索引），也就是普通索引：将数据存储与索引分开的结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因

• 聚簇索引是InnoDB才有的

• Using index ：索引覆盖

• Using index condition：用了索引，但是要回表找数据

• 索引匹配方式：
  全值匹配：指的是和索引中的所有列进行匹配
  匹配最左前缀：匹配最左边的几列
  匹配列前缀：可以匹配某一列的的值的开头部分
  匹配范围值：可以查找某个范围的数据
  精确匹配某一列并范围匹配另一列：可以查询第一列的全部和第二列的部分
  只访问索引的查询：查询的时候只需要访问索引，不需要访问数据行，本质上就是覆盖索引

• 哈希索引：
  基于哈希表的实现，只有精确匹配索引所有列的查询才有效。在MySQL中，只有memory 存储引擎显示的支持哈希索引
  哈希索引自身只需存储对应的hash值，所以索引的结构十分紧凑，这让哈希索引查找的速度非常快
  哈希索引有很多的限制：
  1、哈希索引只包含哈希值和行指针，而不存储字段值，索引不能使用索引中的值来避免读取行
  2、哈希索引数据并不是按照索引值顺序存储的，所以无法进行排序
  3、哈希索引不支持部分列匹配查找，哈希索引是使用索引列的全部内容来计算哈希值
  4、哈希索引支持等值比较查询，也不支持任何范围查询
  5、访问哈希索引的数据非常快，除非有很多哈希冲突，当出现哈希冲突的时候，存储引擎必须遍历链表中的所有行指针，逐行进行比较，直到找到所有符合条件的行
  6、哈希冲突比较多的话，维护的代价也会很高

---

• 索引优化的小细节

  1. 当使用索引列进行查询的时候尽量不要使用表达式，把计算放到业务层而不是数据库层
  2. 尽量使用主键查询，而不是其他索引。因为主键查询不会触发回表查询。
     这里引入一个问题：数据库的表一定要创建主键吗？答案是肯定的，如果你不是分布式的话，最好都创建主键，并且建议主键自增
  3. 使用前缀索引。就是如果你一定要为 varchar 创建索引的话，你可以把字符串的前几位用来创建索引
     对于key_len ，如果是int 的话，int是占4个字节的，但是还有一个null，所以是5
     如果是varchar 类型的话，和字符串的编码是很有关系的，utf-8(3个自己)，gbk(2)，Latin（1），还有null 要占一个字节，还有因为varchar是可变的，所以还会预留两个字节
  4. 使用索引扫描来排序
  5. union all,in,or都能够使用索引，但是推荐使用in
  6. 范围列可以用到索引
  7. 强制类型转换会全表扫描（包括隐私和显示的类型转换）
  8. 更新十分频繁，数据区分度不高的字段上不宜建立索引
  9. 创建索引的列，不允许为null，可能会得到不符合预期的结果
  10. 当需要进行表连接的时候，最好不要超过三张表，因为需要join的字段，数据类型必须一致（现在这个要求不是那么严格了，因为现在允许数据冗余）
  11. 能使用limit的时候尽量使用limit
  12. 单表索引建议控制在5个以内，单索引字段数不允许超过5个（组合索引）【这个也不是那么适用了】
  13. 创建索引的时候应该避免以下错误概念：
      索引越多越好
      过早优化，在不了解系统的情况下进行优化

• 在MyBatis 里面会把0当成null值