MySQL索引原理及如何高效利用索引

2017-01-13 10:46:44来源:oschina作者:醉公子人点击



##**1、索引基础**
要理解MySQL索引是如何工作的,最简单的方式就是去看看一本书的“索引”部分:如果想在一本书中找个某个特定的主题,一般会先看书的“索引”,然后通过索引找到对应的页码。
在MySQL中,存储引擎也是使用类似的方法使用索引,在查询时,先在索引中找到对应值,然后根据匹配的索引记录找到对应的数据行。
##**2、索引的类型**
> 索引有很多种,可以为不同的场景提供更好的性能。在MySQL中,索引是在存储引擎层而不是在服务层实现的。不同的存储引擎有不同的索引实现方式。
###2.1B-Tree索引
当人们谈到索引时,如果没有特别说明的话,一般指的都是B-Tree索引(** 实际上很多存储引擎使用的是B+Tree**),它使用B-Tree数据结构来实现存储数据。大多数MySQL引擎都支持这种索引。
存储引擎以不同的方式使用B-Tree索引,性能也各有不同,各有优劣。例如:MyISAM使用前缀压缩技术使得索引更小,但InnDB则按照原数据格式进行压缩。(传送门:[MyISAM与InnoDB区别](http://blog.csdn.net/xifeijian/article/details/20316775))
B+Tree结构如下
![B+Tree结构](https://static.oschina.net/uploads/img/201701/06165642_4jIF.jpg "在这里输入图片标题")
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 awhere 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;//无法使用索引

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台