聊聊MySQL中的索引
关于MySQL中的索引使用
索引是数据库优化中最常用也是最重要的手段之一，通过索引通常可以帮助用户解决大多数的SQL性能问题。
索引的存储分类：
1、B-Tree索引：最常见的索引类型，大部分引擎都支持B树引擎
2、HASH索引：只有Memory引擎支持，使用场景简单
3、R-Tree索引（空间索引）：MyISAM的一个特殊索引类型，主要用于地理看见数据类型，通常使用较少。
4、Full-text（全文索引）：MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从MySQL5.6版本开始提供对全文索引的支持。
MySQL目前不支持函数索引，但是能对列的前面某一部分进行索引。这个特性可以大大缩小索引文件的大小，但前缀索引也有缺点，在排序Order By和分组Group By操作的时候无法使用。
create index idx_title on film(title(10));
MyISAM、InnoDB、Memory三个常见引擎支持的索引类型比较：
| 索引 | MyISAM引擎 | InnoDB引擎 | Memory引擎 |
|---|---|---|---|
| B-Tree索引 | 支持 | 支持 | 支持 |
| HASH索引 | 不支持 | 不支持 | 支持 |
| Full-text索引 | 支持 | 5.6版本开始支持 | 不支持 |
| R-Tree索引 | 支持 | 不支持 | 不支持 |
比较常用到的索引就是B-Tree索引和Hash索引。Hash索引相对简单，只有Memory/Heap引擎支持Hash索引。Hash索引适用于Key-Value查询，通过Hash索引要比通过B-Tree索引查询更迅速；Hash索引不适用范围查询，例如<、>、<=、>=这类操作。如果使用Memory/Heap引擎并且where条件中不使用“=”进行索引列，那么不会用到索引。Memory/Heap引擎只有在“=”的条件下才会使用索引。
B-Tree索引是最常见的索引，构造类似二叉树，能根据键值提供一行或者一个行集的快速访问，通常只需要很少的读操作就可以找到正确的行。不过，需要注意B-Tree索引中的B 不代表二叉树（binary），而是代表平衡树（balanced）。B-Tree索引并不是一棵二叉树。
MySQL中可以使用索引的常见场景：
1、匹配全值（match the all value），即where field = '**'
2、匹配值的范围（match the range），即where field <（<=）、>（>=）
3、匹配最左前缀（Match a leftmost prefix） ，仅仅使用索引中的最左边列进行查找，比如在 col1 + col2 + col3 字段上的联合索引能够被包含 col1、（col1 + col2）、（col1 + col2 + col3）的等值查询利用到，可是不能够被 col2、（col2 + col3）的等值查询利用到。最左匹配原则可以算是MySQL中B-Tree索引使用的首要原则。
4、仅仅对索引进行查询（Index only query），当查询的列都在索引的字段中时，查询的效率更高
5、匹配列前缀（Match a column prefix），仅仅使用索引中的第一列，并且只包含索引第一列的开头一部分进行查找。
6、能够实现索引匹配部分精确而其他部分进行范围匹配（Match one part exactly and match a range on another part） 。
7、如果列名是索引，那么使用 column_name is null就会使用索引。
存在索引但是不能使用索引的常见场景：
1、以%开头的LIKE查询不能够利用B-Tree索引，执行计划中key的值为NULL表示没有使用索引。一般都推荐使用全文索引（Fulltext）来解决类似的全文检索问题。或者考虑利用 InnoDB 的表都是聚簇表的特点，采取一种轻量级别的解决方式：一般情况下，索引都会比表小，扫描索引要比扫描表更快（某些特殊情况下，索引比表更大，不在本例讨论范围内），而InnoDB表上二级索引idx_last_name实际上存储字段last_name还有主键actor_id，那么理想的访问方式应该是首先扫描二级索引 idx_last_name获得满足条件 last_name like '%NI%'的主键 actor_id列表，之后根据主键回表去检索记录，这样访问避开了全表扫描演员表actor产生的大量IO请求。
2、数据类型出现隐式转换的时候也不会使用索引，特别是当列类型是字符串，那么一定记得在 where 条件中把字符常量值用引号引起来，否则即便这个列上有索引，MySQL 也不会用到，因为MySQL默认把输入的常量值进行转换以后才进行检索。例如，演员表actor中的姓氏字段last_name是字符型的，但是SQL语句中的条件值1是一个数值型值，因此即便存在索引idx_last_name，MySQL也不能正确地用上索引，而是继续进行全表扫描。
3、复合索引的情况下，假如查询条件不包含索引列最左边部分，即不满足最左原则Leftmost，是不会使用复合索引的。
4、如果 MySQL 估计使用索引比全表扫描更慢，则不使用索引。
5、用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到
