mysql5.6 函数索引_聊聊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前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到