一:索引的类型

二:索引的优点

三:高性能索引策略

四:索引案例



1.1类型介绍

索引有很多类型,可以为不同场景提供更好的性能。在MySQL中,索引是在存储引擎层而不是服务器层实现的。所以,并没有统一的索引标准:不同存储引擎的索引的工作方式并不一样,也不是所有的存储引擎都支持所有类型的索引,即使多个存储引擎支持同一种类型的索引,其底层的实现也可能不同

 

 

1.2 B+Tree索引

存储引擎以不同的方式使用B+Tree索引,性能也各有不同,各有优劣。

例如MyISAM使用前缀压缩技术使得索引更小,但InnoDB则按照原数据格式进行存储。

再如MyISAM索引通过数据的物理位置引用被索引行,而InooDB则根据主键引用被索引的行



1.2.1 B+Tree索引数据结构

wKiom1WSEQyjIGKwAADjBorWus0298.jpg




1.2.2理解数据结构

假如有如下数据表:

CREATE TABLE people(
   last_name         varchar(50)         not null,
   first_name        varchar(50)         not null,
   dob                date                not null,
   gender            eum('m','f')        not null,
   key(last_name, first_name, dob)
)

对于每一行的数据,索引中包含了,last_name,first_name和dob列的值

wKiom1WSEWfBkvpuAAF1p7iERDw383.jpg

注意:索引对多个值进行排序的依据是CREATE TABLE语句中定义索引时列的顺序。看最后两个条目,两个人的姓和名都一样,则根据他们的出生日期来排列顺序




1.2.3 B+Tree索引对如下类型的查询有效

 

全值匹配:和索引中的所有列进行匹配,例如前面创建的索引可用于查找姓名为Allen Cuba,出生于1960-01-01的人

 

匹配最左前缀:前面创建的索引可用于查找所有姓为Allen的人,即只使用索引的第一列

 

匹配列前缀:前面创建的索引也可以只匹配某一列的值的开头部分,例如可以用于查找所有以J开头的姓的人

 

匹配范围值:前面创建的索引可用于查找姓在Allen和Barrymore之间的人。(这里只使用了索引的第一列

 

精确匹配某一列并范围匹配另外一列:前面创建的索引可用于查找所有姓为Allen,并且名字是字母K开头(比如Kim)的人。即第一列last_name全匹配,第二列first_name范围匹配

 

只访问索引的查询:B+Tree通常可以支持“只访问索引的查询”,即查询只需要访问索引,而无须访问数据行。



因为索引树中的节点是有序的,所以除了按值查找之外,索引还可以用于查询中的ORDER BY操作。一般来说,如果B+Tree可以按照某种方式查找到值,那么也可以按照这种方式用于排序。所以,如果ORDER BY 子句满足前面列出的几种查询类型,则这个索引也可以满足对应的排序需求




1.2.4 B+Tree索引的限制

 

1.2.4.1如果不是按照索引的最左列开始查找,则无法使用索引。例如上面例子中的索引无法用于查找名字为Kim的人,也无法查找某个特定生日的人,因为这两列都不是最左数据列。类似的,也无法查找姓氏以某个字母结尾的人

 

1.2.4.2不能跳过索引中的列。也就是说,前面所述的索引无法用于查找姓为Allen并且在某个特定日期出生的人。如果不指定名(first_name),则MySQL只能使用索引的第一列

 

1.2.4.3如果查询中有某个列的范围查询,则其右边所有列都无法使用索引优化查找。例如有查询WHERE last_name='Allen' ANDfirst_name LIKE 'K%' AND dob='1930-07-12',这个查询只能使用索引的前两列,因为这里LIKE是一个范围条件(但是服务器可以把其余列用于其它目的)。如果范围查询列值数量有限,那么可以通过使用多个等于条件来代替范围条件。尽可能将范围查询放到最后,因为范围查询后的列不能使用索引




2.1索引的优点

索引可以让服务器快速的定位到表的指定位置。但是这并不是索引的唯一作用,到目前为止

 

可以看到,根据创建索引的数据结构不同,索引也有一些其它的附加作用

最常见的B+Tree索引,按照顺序存储数据,所以MySQL可以用来做ORDER BY 和GROUPBY操作。因为数据是有序的,所以B+Tree也就会将相关的列值都存储在一起。最后,因为索引中存储了实际的列值,所以某些查询只使用索引就能够完成全部查询。据此特性,总结下来索引有如下三个优点:

1.索引大大减少了服务器需要扫描的数据量

2.索引可以帮助服务器避免排序和临时表

3.索引可以将随机I/O变为顺序I/O




Lahdenmaki和Leach提出,如何评价一个索引是否适合某个查询的“三星系统”:

A.索引将相关的记录放到一起则获得一星;

B.如果索引中的数据顺序和查找中的排序顺序一致则获得两星;

C.如果索引中的列包含了查询中需要的全部列则获得三星;




3.高性能的索引策略

正确的创建索引和使用索引是实现高性能查询的基础。现在讨论一下如何真正的发挥这些索引的优势

 

3.1独立的索引

通常看到一些查询不当的使用索引,或者使MySQL无法使用已有的索引。如果查询中的列不是独立的,则MySQL就不会使用索引。“独立的列”是指索引列不能是表达式的一部分,也不能是函数的参数

 

例1:下面这个查询就无法使用user_id列的索引

mysql> SELECT user_id FROM tb1 WHERE user_id+1 = 5;

很容易看出WHERE中的表达式起始等价于user_id=4,但是MySQL无法自动解析这个方程式,这完全是用户行为。我们应该养成简化WHERE条件的习惯,始终将索引列单独放在比较符号的一侧



例2:下面这个查询就无法使用date_col列的索引

mysql> SELECT ... WHERE TO_DAYS(CUURRENT_DATE) - TO_DAYS(date_col) <= 10

解决方法:在程序中获取当前时间,减去10天,加入最后的值存储在last_date_val中

mysql> SELECT ... WHERE date_col <= last_date_val



3.2前缀索引和索引选择性

有时候需要索引很长的字符列,这会让索引变得很慢。通常可以索引开始的部分字符,这样可以大大节约索引空间,从而提高索引效率。但这样也会降低索引的选择性。


索引选择性是指:不重复的索引值(也称为基数)和数据表的记录总数(#T)的比值,范围从1/#T到1之间。索引的选择性越高,则查询效率越高,因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1,这是最好的索引选择性,性能也最好

 

理解索引选择:每行记录都有一个索引值,如果前缀过少,那么索引值重复的可能性就越大,这样在查询的时候查询到的数据就越多,过滤掉的数据就越少,所以要找一个合理的前缀。

 

合理前缀的诀窍在于:选择足够长的前缀以保证较高的选择性,同时又不能太长(以便节约空间)。前缀应该足够长,以使得前缀索引的选择性接近于索引整个列。换句话说,前缀的“基数”应该接近于完整列的“基数”



3.2.1如何选择合理前缀的长度

mysql> CREATE TABLE tb( 
    -> city VARCHAR(50) NOT NULL 
    -> ); 
Query OK, 0 rows affected (0.26 sec)


mysql> SELECT COUNT(*) AS cnt, city_name FROM city GROUP BY city_name  
       ORDER BY cnt DESC LIMIT 10;

wKiom1WSExGRd5CCAACrdQakpA4784.jpg


可以看到,上面每个值都出现了5-7次,现在查找到最频繁出现的城市,先从3个前缀字母开始



mysql> SELECT COUNT(*) AS cnt, LEFT(city_name,3) AS pref FROM city  
     GROUP BY pref ORDER BY cnt DESC LIMIT 10;

wKiom1WSE0exPCeEAACEcYYoOCk265.jpg

每个前缀都比原来的城市出现的次数更多,因此唯一前缀比唯一城市要少的多。然后增加前缀的长度,知道这个前缀的选择性接近完整列的选择性。


mysql> SELECT COUNT(*) AS cnt, LEFT(city_name,7) AS pref FROM city 
      GROUP BY pref ORDER BY cnt DESC LIMIT 10;

wKioL1WSFW7x24VIAACGzV-o-A8032.jpg



计算合适前缀的长度的方法就是计算完整列的选择性,并使前缀的选择性接近于完整列的选择性。下面是如何计算完整列的选择性:


mysql> SELECT COUNT(DISTINCT city_name)/COUNT(*) FROM city;

wKiom1WSE9aR2ytSAACnZqRYNFs678.jpg

通常来说(尽管也有例外情况),这个例子中如果前缀的选择性能够接近于0.4317,基本上就可用了,可以在一个查询中针对不同前缀长度计算。

wKioL1WSFcHBWKvjAAHhOPOz4Ao061.jpg

显示长度为7的时候,再增加前缀长度,选择性已经没有提升了(或者某些时候提升幅度很小了)




3.2.2创建前缀索引

mysql> ALTER TABLE city ADD [KEY|INDEX] city_name_index(city_name(7));

前缀索引是一种能使索引更小、更快的有效方法,但另一方面也有其缺点:MySQL无法使用前缀索引做ORDER BY 和 GROUP BY ,也就无法使用前缀索引做覆盖扫描




3.3多列索引

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

CREATE TABLE t(
    c1 INT,
    c2 INT,
    c3 INT,
    KEY(c1),
    KEY(c2),
    KEY(c3)
);

这种索引策略,一般是由于人们听到一些说,“把WHERE条件里面的列都建上索引”导致的。实际上是非常错误的,这样一来最好的情况下也只能是“一星”索引,其性能比起真正最有的索引可能差几个数量级。有时候如果无法设计一个“三星”索引,那么不如忽略掉WHERE子句,集中精力优化索引列的顺序,或者创建一个全覆盖索引

 

 

在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。MySQL5.0和更新版本引入了一种叫“索引合并”的策略,一定程度上可以使用表上的多个单列索引来定位指定的行。

 

更早版本的MySQL只能使用其中某一个单列索引,然后这种情况下没有哪一个独立的单列索引是非常有效的。例如:表film_actor在字段film_id和actor_id上各有一个单列索引,但对于下面这个查询WHERE条件,这两个单列索引都不是好的选择


mysql> SELECT film_id,actor_id FROM film_actor WHERE actor_id=1OR film_id=1;

在老的MySQL版本中,MySQL对这个查询会使用全表扫描。除非改写成如下的两个查询。

mysql> SELECT film_id,actor_id FROM film_actor WHERE actor_id=1  
-> UNION ALL 
-> SELECT film_id,actor_id FROM film_actor WHERE film_id=1 AND actor_id<>1;




3.4选择合适的索引列顺序

3.4.1例子分析

mysql> SELECT * FROM payment WHERE staff_id = 2 AND customer_id = 584;

是创建一个(staff_id, customer_id)索引还是应该颠倒顺序呢?可以做一些查询来确定这个表中值的分布情况,并确定哪个列的选择性更高。

mysql> SELECT SUM(staff_id=2),SUM(customer_id=584) FROM payment;

wKiom1WSLdzSk0RpAADdFqYfkxg181.jpg

mysql> SELECT COUNT(DISTINCT(staff_id))/COUNT(*) AS staff_id ,
              COUNT(DISTINCT(customer_id))/COUNT(*) AS customer_id ,
              COUNT(*) 
      FROM payment;

wKiom1WSLhiy_4SQAADxTNmsqYQ085.jpg







wKiom1WSLwKx5lvLABL1dzOLnB4167.jpg