MYSQL优化杂谈三，基于索引的优化

四种基本类型的mysql索引

• B-Tree 索引

B-Tree索引是Mysql数据库中使用最为频繁的索引类型，除了存储引擎之外的所有存储引擎都支持B-Tree.
B-Tree索引的物理文件大多都是以Balance Tree的结构来存储，也就是所有实际需要的数据节点都存放于Tree的leaf Node；
而且到任何一个Leaf Node的最短路径的长度都是完全相同的；

【Innodb-Primary Key索引存放】
在Innodb中，Clustered形式存放的PK索引叶子节点存放的是表的实际数据，不仅仅包含主键的数据，还包含其他字段的数据；
整个数据以主键值有序的排列；

【Innodb-Secondary Index索引存放】
在Innodb中，Secondary Index的叶子节点存放的是查询数据的主键信息；
所以，在Innodb中通过Secondary Index查找响应的索引键索引到叶子节点后，再通过叶子节点中存放的主键信息来索引响应的
数据行；

【MyISAM-Secondary Index索引存放】
MyISAM中的主键索引和非主键索引的差别非常小，只不过是主键的索引键是一个唯一且非空的键而已；
只不过叶子节点上存放的是对应数据的数据行信息（如Row number）,但并不会存放主键的键值信息。

• Hash 索引

主要是Memory存储引擎使用，而且是Memory存储引擎默认的索引类型；
Hash索引是将索引键用过Hash运算之后，将Hash运算结果的Hash值和所对应的行指针信息存放于一个Hash表中；
Hash检索效率非常高，索引的检索可以一次定位，所以hash索引的效率要远高于B-Tree;

【hash索引的缺陷】
1.不能使用范围查询
2.hash索引无法在组合索引中使用部分索引；
3.无法避免表扫描，在hash索引中，存在非唯一索引；
  所以获取满足某个Hash键值的记录条数，都无法直接从Hash索引中直接完成查询，
  还是要通过访问表中的实际数据进行相应的比较而得到相应的结果；
4.Hash索引无排序，因为hash运算后的值不一定与运算之前一样；所以在使用hash索引有排序的情况，无法通过索引
  避免排序操作；
5.Hash索引遇到大量Hash值相等的情况后，性能不一定比B-Tree索引高；

• Fulltext 索引

只有Myisam支持全文索引（Fulltext）,也并不是所有的数据类型都支持全文索引，只有char，varchar,TEXT这三种类型的列
可以使用Full-text索引；
Full-text索引主要是来替代效率地下的LIKE "%***%"操作。

• R-Tree 索引

R-Tree索引是用来解决空间数据检索的问题的；

使用索引的益处

• 加快检索，降低检索过程中的数据读取量
• 降低数据的排序成本（索引中的索引数据都是按照索引键键值进行排序后存放的）

使用索引的弊端

• 降低写性能，增加了IO量和调整索引所致的计算量（在更新列操作时，需要额外的也更新对应的索引数据）
• 带来存储空间资源消耗的增长（增加索引，同时也增加了占用空间）

增加索引的几条基本判断策略

• 较频繁的作为查询条件的字段应该创建索引；
• 唯一性太差的字段不适合单曲创建索引，及时频繁作为查询条件；

经验之谈：当一条Query所返回的数据超过了全表的15%的时候，就不应该再使用索引扫描来完成这个Query了~

• 更新非常频繁的字段不适合创建索引
• 不会出现在where字句中的字段不应该创建索引

单键索引还是组合索引

• 单键与组合之间的利弊

1.在更新操作时，组合索引比起单键索引带来的效率下降更多一些，因为组合索引涉及多个字段，在更新操作时
带来的附加成本比单键索引要高；
2.但在多个组合条件查询时，组合索引过滤数据相对单键索引更多，需要访问的记录数相对较少；
3.创建多个单键索引与创建一个多字段的组合索引，通常情况下，组合索引的效果更好一些；因为mysql的Query Optimizer大多数情况下只选择一个索引；

• 选择索引的基本建议

1.对于单键索引，尽量选择针对当前Query过滤性更好的索引；
2.在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中排列越靠前越好；
3.在选择组合索引的时候，尽量选择可以能够包含前Query的Where子句中更多字段的索引
4.尽可能分析信息和调整Query的写法来达到选择合适索引的目的而减少通过使用Hint认为控制索引的选择，因为这会使后期维护成本增加，同事增加潜在风险；

Mysql中索引的限制

• MyISAM存储引擎索引键长总和不能超过1000字节
• BLOB和TEXT类型的列只能 创建前缀索引
• Mysql目前不支持函数索引
• 使用不等于的时候mysql无法使用索引
• 过滤字段使用了函数运算后，mysql无法使用索引
• Join语句中Join条件字段类型不一致的时候mysql无法使用索引
• 使用Like条件，以通配符开始（“%ddd”）mysql无法使用索引
• 使用非等值查询的时候，mysql无法使用hash索引
• 组合索引，在遇到范围查询时将会停止后续的索引操作