索引类型
我们知道,索引的实现有很多种,在Mysql中,索引是在引擎中实现的,所以没有统一的索引类型标准。
B-Tree索引
关于这方面的索引原理,参考我之前写的文章伸缩自如的ElasticSearch——数据库索引原理。
哈希索引
基于哈希表的实现,只有精确匹配索引所有列的查询才有效。
哈希索引只适用于某些特定的场合,比如数据仓库应用中有一种经典的“星型”schema,需要关联很多查找表,哈希索引就非常适合表的需求。
其他一些索引我们不多做介绍了。
索引是最好的解决方案吗?
索引并不总是最好的解决方案,一般来说,对于非常小的表,大部分情况全表扫描更加有效。
高性能的索引策略
独立的列
我们通常会看到一些查询不当的使用索引,或者使得Mysql无法使用已有的索引。
如果查询中的列不是独立的,那么Mysql就不会使用索引。
什么是独立的列呢?独立的列是指,索引列不能是表达式的一部分,也不能是函数的参数。
比如下面这个查询无法使用user_id列的索引:
select user_id from user_info where user_id+1=5;
Mysql无法自动解析这个方程user_id+1=5
,所以不会使用索引。
下面是另一种错误:
select date_col where to_days(date_col)<=10;
我们应当始终将索引列单独放在比较符号的一侧。
前缀索引和索引选择性
有时候需要索引很长的字符列,这会让索引变得大且慢。一个策略是模拟哈希索引,但有时候这样做还不够,有个办法和之前学习的blob
和text
的排序很像:索引开始的部分字符。
这种前缀索引的方式,可以大大节约索引的空间,但是会降低索引选择性。索引选择性是指,不重复的索引和数据表总数目的比值,当然可以知道,1:1的时候,前缀索引的能完全区分字符列。
Mysql规定:对于blob
和text
还有很长的varchar
类型,必须使用前缀索引。
为了找出最佳的前缀索引的长度,可以使用下面的方式计算索引选择性:
left(city,3)
是取的city这个列的前3个字符。我们可以看到,当前缀长度达到6的时候,再增加前缀长度,选择性提升的幅度已经很小了(越接近1越好)。
既然找到了最合适的前缀长度,下面演示如何创建前缀索引:
ALTER TABLE sakila.city_demo
ADD INDEX `city_index`(`city`(6));
多列索引
多列索引不是为每个列创建独立的索引,也不能按照错误的顺序创建多列索引。
先来看第一个问题,为什么不是为多个列创建独立的索引。
在多个列上创建独立的单索引,大部分情况下并不能提高Mysql的查询性能。
我们来看看一个例子。
select film_id,actor_id from film_actor
where actor_id=1 or film_id =1;
在Mysql老版本里,会使用全表扫描。
不过在Mysql5.0之后的版本中,引入了一种叫“索引合并”的策略,一定程度上可以使用表上的多个单索引。不过,这种情况更多的时候说明,表上的索引建立的很糟糕。
再来看另一个问题,索引列的顺序。正确的顺序依赖于使用该索引的查询是什么样的,并且需要考虑如何更好的满足排序和分组的需要。
在一个多列B-Tree索引中,索引列的顺序意味着索引首先按照左列进行排序,然后是第二列…
这样,索引才可以按照升序和降序进行扫描,以满足order by
、group by
、distinct
等要求。
由此可见索引的顺序至关重要。一个重要的经验法则是:将索引选择性最高的列放在最前列。
覆盖索引
这节的内容,之前也在伸缩自如的ElasticSearch——数据库索引原理中提到了,不做赘述。
覆盖查询都具有以下两个特点:
- 查询中的所有字段都属于一个索引;
- 查询所返回的所有字段也都属于同一索引内。
覆盖索引能够避免Innodb表进行索引的二次查找,避免了对主键的二次查询。另外,可以把随机IO变为顺序IO加快查询效率。
使用索引扫描来做排序
Mysql有两种方式生成有序的结果:
- 排序操作
- 按索引顺序扫描
只有当索引的列顺序与Order by子句的顺序完全一致时,并且排序的方向只能是一种(asc/desc)Mysql才能使用索引来做排序(最左前缀原则)。
如果查询需要关联多张表,那么只有当Order by子句全是第一个表的列时,才能使用索引排序。
范围查询不会走索引查询(不是不会走索引),比如user_id in (x,x)
以及 where id>1
最左前缀原则举例:比如 index(id,name,telephone),那么,order by id或者order by id,name都是满足最左前缀的,但是 order by name,telephone是不满足的。
重复索引和冗余索引
重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免这样创建重复索引,发现以后也应该立即移除。
有时会在不经意间创建了重复的索引,例如下面的代码:
CREATE TABLE test(
ID INT NOT NULL PRIMARY KEY,
A INT NOT NULL,
B INT NOT NULL,
UNIQUE(ID),
INDEX(ID)
) ENGINE=InnoDB;
一个经验不足的用户可能是想创建一个主键,先加上唯一限制,然后再加上索引以供查询使用。事实上,MySQL的唯一限制和主键限制都是通过索引实现的,因此,上面的写法实际上在相同的列上创建了三个重复的索引。通常并没有理由这样做,除非是在同一列上创建不同类型的索引来满足不同的查询需求。
冗余索引和重复索引有一些不同。如果创建了索引(A,B),再创建索引(A)就是冗余索引,因为这只是前一个索引的前缀索引。因此索引(A,B)也可以当做索引(A)来使用(这种冗余只是对B-Tree索引来说的)。但是如果再创建索引(B,A),则不是冗余索引,索引(B)也不是,因为B不是索引(A,B)的最左前缀。另外,其他不同类型的索引(例如哈希索引或者全文索引)也不会是B-Tree索引的冗余索引,而无论覆盖的索引列是什么。
上图两个例子,index(a)是index(a,b)的前缀索引,但是如果ab索引的值很大,就有必要建立一个index(a)加快查询。
而第二个index(a,id)则是完全没有必要的,因为index(a)会自动添加一个主键信息,相当于是index(a,id),但没有必要建立index(a,id)。
索引优化
MRR
有时候尽管有了索引,但是还会出现不走索引的情况发生,多见于范围查找、JOIN链接操作等情况。
比如下面的语句:
SELECT * FROM order_details_table WHERE order_id>100000 AND order_id<102000
(order_id不是主键)
假如这张表有index(order_id),index(order_id,product_id)的辅助索引(非聚集索引),但是仍然可能不会走非聚集索引。通过explain命令,用户可能会发现优化器选择了PRIMARY聚集索引,相当于走了表扫描,而不是索引扫描。
原因在于用户要选择的数据是整行信息,而order_id索引不是覆盖索引,所以在order_id查询到指定数据后,还需要一次聚集索引来查找整行数据的信息,因此变成了磁盘上离散的操作。如果访问的数据量很小,可能还会使用非聚集索引index(order_id)来做,但是当超过某一个阈值(一般是20%左右),优化器会选择使用聚集索引来查找数据,减少IO次数。
如果用户使用的磁盘是固态硬盘,随机IO速度非常快,有足够信心来确认非聚集索引会有更好的性能,可以选择force index(order_id)
关键字来做查找。
在MYSQL5.6开始支持Multi-Range Read(MRR)优化,用来解决上面的问题:
- MRR在查询非聚集索引的时候,会根据得到的查询结果,按照主键进行排序,然后再进行聚集索引查找(书签查找),这样做还可以减少缓冲池里页被替换的次数。
ICP
在Mysql 5.6开始,Index Condition Pushdown(ICP)在进行索引查询的时候,会在索引取出的同时进行where条件判断来过滤,也就是将WHERE的部分过滤操作放在了存储引擎层。
比如下面的查询语句,有联合索引index(zip_code,last_name)
SELECT * FROM people WHERE zipcode='94043' AND last_name LIKE '%lee%'
如果支持ICP优化,那么在索引取出后,就会进行WHERE条件的过滤。如果不启用ICP,会在取出全部zipcode='94043'
的数据后,才做WHERE条件的过滤。