Mysql索引B+树，索引优化

索引是帮助Mysql高效获取数据的排好序的数据结构

Mysql底层数据结构

• B+Tree (B-Tree变种)
• 非叶子节点不存data，只存储索引（冗余），可以放更多的索引。
• 非叶子节点包含所有索引字段
• 叶子节点用指针连接，提高区间访问的性能。
  

B-Tree 特点

• 叶结点具有相同的深度，叶节点的指针为空
• 所有索引元素不重复
• 节点中的数据索引从左到右递增排列
  

存储引擎

－99% InnoDB， 早期MyISAM

• MylSAM 索引文件和数据文件是分离的（非聚集）
  
  

mylasm存储引擎磁盘文件	用途
tb_myIsam.MYI	index, 存储的索引， b+树
tb_myIsam.MYD	data, 存储的数据
tb_myIsam.frm	表结构信息

InnoDB存储引擎磁盘文件	用途
order_.frm	表结构信息
order_.ibd	data, 存储的数据

InnoDB存储引擎实现（聚集）

• 表数据文件本身就是按B+Tree组织的一个索引结构的文件
• 聚集索引：叶节点包含了完整的数据记录,(只会存储一份，其他的普通索引，子节点的data项会存储主键)
  
• 为什么建议InnoDB引擎表必须建主键，并且推荐使用整形的自增主键？
  ① 表数据文件本身就是按B+Tree组织的一个索引结构的文件
  ② 如果没有建主键，mysql会选择唯一的列，作为构建B+树存储结构的 列。
  ③ 如果没有找到唯一的列，mysql会新增一个伪列，作为构建B+树存储结构的 列。类似oracle的RowNum
• 为什么非主键索引结构叶子节点存储的是主键值？
  ①（一致性和节省空间）
  ② 普通索引叶子节点存主键值，再到聚集索引里去找该条记录。
  

Hash索引

• mysql 索引可以设置为B+Tree(99.99%), 或者Hash
• 对索引的key进行一次hash计算就可以定位出数据存储的位置
• 很多时候hash索引要比B+树索引更高效
• 仅能满足 “=”,“IN” 不支持范围查询
• hash冲突问题

联合索引

• 联合索引的底层存储结构长什么样？
  

覆盖索引

• 如果我们的查询结果集能通过普通索引树快速的查询到，不需要在回表查询。

Explain工具介绍

• 在使用Explain关键字可以模拟优化器执行Sql语句，分析你的查询语句或是结构的性能瓶颈
• 在select 语句之前增加explain关键字，Mysql会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行这条Sql。

索引设计原则

1. 代码先行，索引后上
   不知大家一般是怎么给数据表建立索引的，是建完表马上建立索引吗？
   这其实是不对的，一般应该等到主体业务功能开发完毕，把涉及到该表相关的sql拿出来分析后，再建立索引。
2. 联合索引尽量覆盖条件
   比如设计一个或者两三个联合索引（尽量少建单值索引），让每一个联合索引都尽量去包含sql语句里的where,order by , group by的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则。
3. 不要在小基数字段上建立索引
   索引基数是指这个字段在表里总共有多少个不同的值，比如一张表总共100万行记录，其中有个性别字段，其值不是男就是女，那么该字段的基数就是2
   如果对这种小基数字段建立索引的话，还不如全表扫描，因为你的索引树里就包含男和女两种值，根本没法进行快速的二分查找，那用索引就没有太大的意义了。
   一般建立索引，尽量使用那些基数比较大的字段，就是值比较多的字段，那么才会发挥出B+树快速二分查找的优势来。
4. 长字符串我们可以采用前缀索引
   尽量对字段类型较小的列设计索引，比如说 tinyint之类的，因为字段类型较小的话，占用磁盘空间也会比较I笑傲，此时你在搜索的时候性能也会比较好一点。

• 当然，这个所谓的字段类型小一点的列，也不是绝对的，很多时候你就是要针对varchar(255)这种字段建立索引，哪怕多占用一些磁盘空间也是有必要的。
  对于这种varchar(255)的大字段可能会比较占用磁盘空间，可以稍微优化下，比如针对这个字段的前20个字符建立索引，就是说，对这个字段里的每个值的前20个字符放到索引树里，类似于 key index (name(20) ,age,position)。此时，你在where条件里搜索的时候，如果是根据name字段来搜索，那么此时就会先到索引树里根据name字段的前20个字符去搜索，定位到匹配前20个字符的前缀匹配的数据之后，再回到聚簇索引提取出来完整的name字段值进行比对。
• 但是假如你要是order by name，那么此时你的name因为索引树里仅仅包含了前20个字符，所以这个排序是没法用上索引的，group by 也是同理。

5. where 与 order by 冲突时，优先where

• 在where和order by 出现索引涉及冲突时，到底是针对where去设计索引，还是针对order by设计索引？到底是让where 去用上索引，还是让order by用上索引？
• 一般这种时候往往都是让where条件去使用索引来快速筛选出来一部分指定的数据，接着再进行排序。
• 因为大多数情况基于索引进行where筛选往往可以最快速度筛选出你要的少部分数据，然后做排序的成本可能会小很多。

6. 基于慢sql查询做优化
   可以根据监控后台的一些慢sql，针对这些慢sql查询做特定的索引优化

对于关联SQL的优化

1. 关联字段加索引，让mysql做join操作时，尽量选择NLJ算法
2. 小表驱动大表，写多表连接sql时，如果明确知道哪张表示小表，可以使用straight_join写法固定俩捏驱动方式，省去mysql优化器自己判断的时间。
3. straight_join 只适用于inner join，并不适用于left join，right join 。（因为left join, right join 已经代表了表的执行顺序）
4. 尽可能让优化器去判断，因为大部分情况下mysql优化器比人智能，使用straight_join一定要慎重，因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。
5. 对于小表定义的明确
   在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。