高性能MySQL之第五章 创建高性能的索引

什么是索引

索引（在MySQL中也称 “键/Key”）是在存储引擎层实现的，是在存储引擎中用于快速找到记录的一种数据结构。

索引的类型

B-Tree索引

索引概念

使用B-Tree数据结构（大多使用的是B+树结构）存储数据

B+树索引的索引结构

开始解释上面两个图：（理解了这两张图就知道底层索引是如何建立的了）
第一个图
这张图是创建表的语句，很容易理解，主要知道key(last_name,first_name,dob) 表示创建三个索引
第二个图
B+树是叶子节点存储数据的，那非叶子节点就存储索引。
第二张图的第一行就是非叶子结点，存储的就是三个索引的数据
则下面的三行就是叶子节点，存储的是真实的数据。
（这样才知道索引创建的合适，那MySQL引擎查找数据的速度才会快）

支持索引的常见引擎

Innodb引擎：根据主键引用 被索引的行¹，即：索引一般为主键
MyISAM引擎：根据数据的物理位置引用 被索引的行¹，即：索引一般为数据的物理位置

注意

索引中所有的值都是按顺序存储的，并且每个叶子页到根的距离是相同的。
适合全键值、键值范围或键前缀查找。其中键前缀查找只适用于最左前缀的查找

限制

1、如果不是按照索引的**最左列**开始查找，则无法使用索引。
2、不能跳过索引中的列（如果不指定索引，MySQL默认使用所以索引的第一列）。
	解释案例：key(last_name,first_name,dob)三个索引
		select * from x where last_name="xx"  and dob="123";
		查询语句中，先使用last_name索引，跳过了first_name索引，那就无法使用dob索引了，可以得出结论（索引的顺序非常重要）。
3、如果查询中有某个列的范围查询，则其右边的所有列都无法使用所以优化查找。
	解释案例：key(last_name,first_name,dob)三个索引
		select * from x where last_name="xx" and first_name like "j%" and dob="123";
		在查询语句中，first_name是范围查询，索引后面就无法使用dob索引查询了
		可以使用多个等于条件代替范围条件解决。

Hash索引

索引概念

基于哈希表，将所有哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

支持常见引擎

只有Memory引擎显示支持哈希索引
InnoDB引擎有一个特殊的功能叫“自适应哈希索引”

限制

1、哈希索引值包含**哈希值和行指针**，而不存储字段值，所以不能使用索引中的值来避免读取的行。
2、哈希索引数据不是按照索引值的顺序存储的，所以无法用于排序。
3、不支持部分索引列匹配查找。
4、只支持等值比较查询。

空间数据索引（R-Tree）

MyISAM支持空间索引，可以用作地理数据存储

全文索引

适用于Match against ，而不是普通的where。ES使用可以使用全文索引、模糊匹配、精确匹配

其他索引：例如分形树索引

索引种类

唯一索引 unique '索引字段'
普通索引 index '索引字段'
主键索引 primary key '索引字段' 
全文索引 fulltext index '索引字段'

查看索引

show index from tablename;

索引作用

快速定位到表的指定位置
减少了服务器扫描的数据量
帮助服务器避免了排序和临时表
将随机I/O变为顺序I/O

索引的三星系统（了解）

一星：索引将相关的记录放到一起
二星：索引中的数据顺序和查找中的排序顺序一致
三星：索引中的列包含查询中需要的全部列

索引优化（高性能的索引策略）

1）独立的列

在查询时，索引不能是表达式的一部分，例如id+1=2,需要改成id=3）
解释案例：比如：索引是actor_id
	select actor_id from actor where actor_id+1 = 5;
	从查询语句中，MySQL无法自动解析actor_id+1 = 5表达式，所以不会使用索引进行查询。
	解决：select actor_id from actor where actor_id = 6;

2）前缀索引和索引选择性（根据字段前缀建立索引）

前缀索引概念

有时索引是个很长的字符列，就会造成索引变的很大且慢，一个策略是哈希索引，另一个就是索引这个字符列的部分字符(前缀索引)，大大节约索引空间，提升索引效率，但同时会降低索引选择性²。

前缀索引的选择和建立

首先需要选择最合适前缀长度(当选择性接近0.031，就基本可用)
选择性测试：我们需要逐个判断各自的选择性

select
	count(distinct (left(city,3)))/count(*) as sel 3, # 从左边选择3个字符作为索引的选择性
	count(distinct (left(city,4)))/count(*) as sel 4,# 从左边选择4个字符作为索引的选择性
	count(distinct (left(city,7)))/count(*) as sel 7 # 从左边选择7个字符作为索引的选择性
from city_demo;
结果： sel3 = 0.0239; sel4=0.0293; sel7=0.0310

最后得出结论：
所以当前缀长度到达7时，最合适，再提升长度，选择性就没有太多意义了。
找到合适的前缀长度，就可以建立前缀索引了

	alter table city_demo add key(city(7));

前缀索引的优缺点

前缀索引能够让索引更小、更快，但是无法使用前缀索引做order by和group by 以及覆盖索引。

3）多列索引（新版能够同时使用两个单列索引进行扫描，并将结果进行合并）

索引合并：一定程度上可以使用锁哥单列索引来定位指定的行。
这种算法有三种变种：or条件的联合(union)、and条件的相交(intersection)、组合前两种情况的联合及相交
    案例：
    	select film_id,action_id from film_action where actor_id=1 and film_id=1; //film_id和action_id各有一个单列索引
    在explain中可以看到Extra:union(primary,idx_fx_film_id)

4）选择合适的索引列顺序（适用于B-Tree索引）

索引的顺序依赖于使用该索引的查询语句，同时考虑更好的满足排序和分组的需要。
根据经验法则（将选择性最高的列放到索引最前列），经验法则考虑的是全局基数和选择性。
案例：

求select * from payment where staff=2 and customer_id=584;
分析：select sum(staff=2),sum(customer_id=584) from payment;
*****************************************************************
sum(staff=2):7992 #staff=2的数据有7992条
sum(customer_id=584):30 #customer_id=584的数据有30条

建立索引
条件1：key(staff,customer_id) ，索引是staff在前
  需要从7992个数据中选customer_id=584的数据
条件2：key(customer_id,staff)，索引是customer_id在前
  只需要从30个数据中选staff=2的数据。
所以肯定选(customer_id,staff)作为索引。

5）聚簇索引

聚簇索引不是一种单独的索引类型，而是一种数据存储方式
术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。
聚簇索引存放方法：***
  叶子页包含了行的全部数据(主键列、事务ID，MVVC的回滚指针、剩余列)
  (非叶子节点)节点页包含索引列和指向下级节点的指针

优点

1. 可以把相关数据保存在一起。
   解释：电子邮箱，根据用户id(作为索引)聚集数据，通过数据页获取用户id的全部邮件。如果没有使用聚簇索引，那每个有邮件都会导致一次磁盘IO。
2. 数据访问更快。索引和数据保存在同一个B-Tree中。
3. 使用覆盖索引扫描的查询可以直接使用叶节点中的主键值。

缺点

1. 插入速度严重依赖于插入顺序。
2. 更新聚簇索引列的代价很高。会强制InnoDB将每个被更新的行移动到新的位置。
3. 聚簇索引可能导致全表扫描变慢。由于页分裂导致数据存储不连续。
4. 二级索引可能比想象的更大。因为二级索引包含了引用行的主键列。
5. 二级索引访问需要两次索引查找，而不是一次。

InnoDB和MyISAM的数据分布不同

InnoDB的二级索引的叶子结点存储的是主键值，作用：使用主键值作为指针会让二级索引占用更多的空间，但好处是InnoDB在移动行时无需更新索引中的这个“指针”。

6）覆盖索引

一个索引包含所有需要查询的字段的值，就称“覆盖索引”。

优点

1. MySQL查询数据时，只需要读取索引就可以获取数据。极大的减少了数据的访问量。
2. 索引是按照列值顺序存储的
3. InnoDB中，聚簇索引的二级索引使用的是覆盖索引，避免对主键索引进行二次查询。

限制

1. 覆盖索引必须存储索引列的值，哈希索引、空间索引、全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引。
2. 只有查询的属性都是索引时，才能使用覆盖索引。
   解释案例：key(store_id,film_id) 两个索引

select store_id,film_id from inver;  可以使用覆盖索引
select * from inver;   * 有索引外的字段，无法使用覆盖索引
****************************************************
解决：使用延迟关联（延迟对列的访问）
select * from inver 
join(
	select store_id,film_id from inver  使用覆盖索引
)as t1 on t1.store_id=inver.store_id;
这样在查询的第一阶段MySQL使用了覆盖索引。

7）压缩索引（前缀压缩）

MyISAM使用前缀压缩减少索引的大小，将更多的索引可以放在内存中，极大的提高了性能。
默认只压缩字符串，MyISAM对行指针也采用类似前缀压缩的方式。
优点：
减少索引空间
缺点：
扫描需要随机查找，使查找效率降低。

8）冗余和重复索引

概念

重复索引：相同的列上按照相同的顺序创建的相同类型的索引。
冗余索引：多余的索引（符合最左前缀列）。

重复索引案例：

unique(id),index(id)  建立了两个相同的索引，表示重复索引

冗余索引案例：

unique(A,B),index(A) 就是冗余索引
unique(B,A), index(A) 不是冗余索引

解决冗余索引和重复索引的方法

删除这些索引
查看冗余和重复索引

select * from sys.schema_redundant_indexes;

也可以使用一些第三方工具

9）未使用的索引

这些索引完全是累赘，也是可以删除索引
查看为使用的索引

select * from sys.schema_unused_indexes;

10）索引和锁

InnoDB只有在访问行时才会对其加锁，而索引能够减少InnoDB访问的行数，从而减少锁的数量。
InnoDB在服务端过滤掉行后，就会释放锁。
InnoDB在二级索引上使用的是共享锁，在访问主键索引时，需要排它锁。

---

1. 数据行 ↩︎ ↩︎

2. 不重复的索引值和数据表的的记录总数的比值。
   通俗讲就是，能准确找到索引的概率。
   解释：
   如果索引是全局唯一的字符，则选择性为1（只有一个），但我们需要减少字符长度，那就有可能造成有重复的索引，所以索引的选择性就降低了。 ↩︎