目录
索引的分类
-
B-Tree索引:最常见的索引类型,大部分引擎都支持B树索引
-
HASH索引:只有Memory引擎支持,使用场景简单
-
R-Tree索引(空间索引):空间索引是MyISAM的一个特殊索引类型,主要用于地理空间数据类型,通常使用较少,不做特别介绍
-
Full-text(全文索引):全文索引也是 MyISAM 的一个特殊索引类型,主要用于全文索引,InnoDB从MySQL 5.6版本开始提供对全文索引的支持。
-
MySQL目前不支持函数索引,但是能对列的前面某一部分进行索引(前缀索引);缺点,在排序Order By和分组Group By操作的时候无法使用
能够使用索引的典型场景
如有复合索引:(a, b)
-
匹配全值(Match the full value),对索引中所有列都指定具体值,即是对索引中的所有列都有等值匹配的条件,如:a = xx AND b = xx。
-
匹配值的范围查询(Match a range of values),对索引的值能够进行范围查找,如:a > 1 AND a < 10。
-
匹配最左前缀(Match a leftmost prefix),仅仅使用索引中的最左边列进行查找,比如在 col1 + col2 + col3 字段上的联合索引能够被包含 col1、(col1 + col2)、(col1 + col2 + col3)的等值查询利用到,可是不能够被 col2、(col2 + col3)的等值查询利用到。
-
仅仅对索引进行查询(Index only query),当查询的列都是索引的字段中时,查询的效率更高,即:避免使用select *,最好使用select a,b ...,即查询的列都是索引列,不需要回表查数据
-
匹配列前缀(Match a column prefix),仅仅使用索引中的第一列,并且只包含索引第一列的开头一部分进行查找,即:
like 'a%'
。 -
能够实现索引匹配部分精确而其他部分进行范围匹配(Match one part exactly and match a range on another part),如:a = ‘x’ AND b > 1AND b < 2
-
如果列名是索引,那么使用 column_name is null就会使用索引(区别于Oracle)
-
MySQL 5.6引入了 Index Condition Pushdown(ICP)的特性,进一步优化了查询。Pushdown表示操作下放,某些情况下的条件过滤操作下放到存储引擎。Explain Extra:Using index condition就表示MySQL使用了 ICP 来进一步优化查询。
补充:Index Condition Pushdown(ICP),将customer_id的过滤操作下推到存储引擎层来完成,这样能够降低不必要的IO访问
存在索引但不能使用索引的典型场景
-
以%开头的LIKE查询不能够利用B-Tree索引,执行计划中key的值为NULL表示没有使用索引
-
数据类型出现隐式转换的时候也不会使用索引
-
复合索引的情况下,假如查询条件不包含索引列最左边部分,即不满足最左原则Leftmost,是不会使用复合索引的
-
如果 MySQL 估计使用索引比全表扫描更慢,则不使用索引。在查询的时候,筛选性越高越容易使用到索引,筛选性越低越不容易使用索引。
-
用or分割开的条件,如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到