mysql 是如何利用索引的_MySQL索引原理及如何高效利用索引

##1、索引基础 要理解MySQL索引是如何工作的，最简单的方式就是去看看一本书的“索引”部分：如果想在一本书中找个某个特定的主题，一般会先看书的“索引”，然后通过索引找到对应的页码。

在MySQL中，存储引擎也是使用类似的方法使用索引，在查询时，先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。

##2、索引的类型

索引有很多种，可以为不同的场景提供更好的性能。在MySQL中，索引是在存储引擎层而不是在服务层实现的。不同的存储引擎有不同的索引实现方式。

###2.1 B-Tree索引 当人们谈到索引时，如果没有特别说明的话，一般指的都是B-Tree索引(** 实际上很多存储引擎使用的是B+Tree**)，它使用B-Tree数据结构来实现存储数据。大多数MySQL引擎都支持这种索引。

存储引擎以不同的方式使用B-Tree索引，性能也各有不同，各有优劣。例如：MyISAM使用前缀压缩技术使得索引更小，但InnDB则按照原数据格式进行压缩。(传送门：MyISAM与InnoDB区别)

B+Tree结构如下

B+Tree的详细结构以及查找过程详见网上，这里不做介绍。

** 可以使用B+Tree索引的查询类型：**

全指匹配：

匹配最左前缀：

匹配列前缀：

匹配范围值：

精确匹配某一列并范围匹配另外一列：

只访问索引的查询：

** 关于B+Tree索引的限制：**

如果不是按照索引的最左列开始查找，则无法使用索引。

不能跳过索引中的列。

如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。

###2.2 哈希索引 暂时没了解过，略，以后用到了再补充。

###2.3 空间数据索引 暂时没了解过，略，以后用到了再补充。

###2.4 全文索引 暂时没了解过，略，以后用到了再补充。

##3、高性能索引策略

###3.1 独立的列

“独立的列”指的是索引列不能是表达式的一部分，也不能是函数的参数。

例如：下面这个查询无法使用id的索引：

mysql> select * from user where id +1=5;

凭肉眼就能判断出来的，但是MySQL无法自动解析这个方程式。所以我们应该简化WHERE条件习惯，始终讲索引列单独放在比较符号的一侧。

###3.2 前缀索引和索引的选择性

对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。

下面演示一下如何创建前缀索引：

mysql> alert table sakila.city_demo add key (city(7));

###3.3 多列索引

很多人对多列索引有误解，一个常见的错误就是，为每个列创建独立的索引或者按照错误的顺序创建多列索引。

如，表film_actor在字段film_id和actor_id上各有一个单列索引。但对于下面这个查询WHERE条件，这两个单列索引都不是很好的选择；

mysql> select film_id,actor_id from sakila.film_actor where actor_id = 1 OR film_id =1;

在老的MySQL中，对这个查询会使用全表扫描。除非改写成如下的SQL：

mysql> select film_id,actor_id from askila.film_actor where actor_id = 1

union all

select film_id,actor_id from askila.film_actor where film_id = 1

and actor_id <> 1;

##4、MySQL中能够使用索引的场景

全值匹配

对索引列指定具体值进行查询。

匹配值的范围查询

对索引的值进行范围查找。

匹配最左前缀

仅仅使用索引中最左边列进行查询。比如在col1+col2+col3字段上的联合索引能够被包含col1、(col1+col2)、(col1+col2+col3)的等值查询利用到，不能够被col2、(col2+col3)的等值查询利用。

仅仅对索引进行查询

查询的列都在索引的字段中。

匹配列前缀

仅仅使用索引的第一列，并且只包含索引第一列的开头一部分进行查询。例如：查询出title是以“最美丽的”开头的所有数据，也就是SQL中的“%最美丽的”。

能够实现索引匹配部分精确而其他部分进行范围匹配

例如：表A 索引列为col1，则select * from a where col1=123 and col2>10 ;

##5、MySQL中存在索引但不能使用索引的场景

以%开头的LIKE查询不能够利用B+Tree索引

数据类型出现隐式转换的时候也不会使用索引

如索引字段为字符串类型，则在查询时候一定得把双引号包起来。如col1为索引字段且类型为字符串，用col1 = 123将不会使用索引，用col1 = '123' 则可以使用索引。

复合索引的情况下，假如查询条件不包含索引列最左边部分，即不满足最左原则，无法使用索引

比如在col1+col2+col3字段上的联合索引能够被包含col1、(col1+col2)、(col1+col2+col3)的等值查询利用到，不能够被col2、(col2+col3)的等值查询利用

如果MySQL估计使用索引比全表扫描更慢，则不使用索引

查询以“S”开头的标题的电影，需要返回记录比例很大，MySQL预估使用索引还不如全表扫描快。这个可以通过explain来分析。

用OR条件隔开的条件，如果or前面的条件中有列的索引，而后面的列中没有索引，则涉及的索引都不会用到

表A的索引为col1，select * From A where col1 =123 OR col2 = 456；//无法使用索引