三、创建高性能的索引

马上就要到国庆节了，好是期待呀。最近一直忙成狗，急需一个长假调整一下自己的心境和状态

期待.jpg

今天我们要说的是索引相关的知识，这也是数据库的一个重点章节。赶紧准备好你的笔，跟着我一起勾画重点吧，听说这里要考哦~~~

索引的作用

1. 可以快速根据索引查找指定的记录
2. 可以根据索引对记录进行排序,可以用来order by 和group by
3. 可以将随机IO转变为顺序IO,索引是有顺序的，先根据索引顺序查询，然后根据查找到的关键值定位记录

三星系统

1. 索引将相关的记录放在一起，获得一星
2. 如果索引中的数据顺序和查找中的排列顺序一致则获得两星
3. 如果索引中的列包含查询中全部列则获得三星
   需要注意的是索引并不总是最好的工具，只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时，索引才是有效的
   一般情况下：
   小表：全表扫描
   中表（数据量还不是很大）：索引优化
   大表（数据量超级大）：高级技术比如分区等

索引类型

mysql中索引类型有很多，索引的实现方式是通过存储引擎实现的，而不是服务器层实现的

B-TREE索引

一般我们没有特别指明索引类型的时候，说的索引应该就是B-TREE索引，它使用B-TREE数据结构来存储数据的
因为索引是由存储引擎实现的，所以不同的存储引擎会通过不同的方式来使用B-TREE索引，MYISAM使用前缀压缩技术使得索引更小，同时采用物理位置引用被索引的行（也就是说，通过索引直接就可以找到对应的数据行记录）INNODB则按照原数据格式进行存储，同时根据主键引用被所以的行（也就是说通过索引首先会找到行的主键索引，然后通过主键索引找到具体的行）
B-TREE索引意味着所有存储的数据记录都是有顺序的

b-tree数据结构.png

根据表的数据大小，B-TREE树层级深度也将不同，其中每一个节点页都包含了一个值以及左边小于该值的子节点页指针和大于该值的右节点页指针，也就是规定了该值的上线和下限，而叶子页的指针指向的是具体的数据，而不是其他的节点页
在索引中，顺序是非常重要的一个因素，索引对多个值进行排序的依据就是按照create table语句中定义索引时列的顺序来实现的

B-TREE索引能使用的类型

全值匹配：所有列进行匹配
匹配最左前缀：匹配索引的第一列
匹配列前缀：匹配某一列的值开头的部分
匹配范围值：索引第一列范围查找
精确匹配第一列，范围匹配另外一列
因为索引树中的节点是有顺序的，所以除了按值查找之外，还可以对数据进行order by排序操作，但是使用B-TREE索引也有一定的限制：
如果不是按照索引的最左列开始查找，将无法使用索引
不能跳过索引中的列
如果查询中有某个列的范围查询，则其后面的列都将无法使用索引进行查询

hash索引

mysql索引是在存储引擎层实现的，并没有统一的标准，不同的存储引擎实现的索引方式是不同的
对于hash索引，只能精确匹配所有列的值，因为存储引擎将会把生成hash索引的所有列的值用来构建hash code
在mysql中，只有memory引擎显示支持hash索引，这也是它默认的索引类型，memory引擎同时也是支持非唯一hash索引的，当出现hash冲突时，通过链表的方式解决冲突问题
hash索引基于hash表实现的，在它其中并不保存实际的值，而是保存hashcode->行的指针的键值对方式

hash索引数据结构.png
因此使用hash索引能快速的定位到某一行记录，但是它也存在某些限制：
hash索引只包含hash值与行指针，而不存储字段值，所以不能使用索引中的值来避免读取行
hash索引数据并不是按照索引值顺序存储的，也就无法使用排序
hash索引也不支持部分索引列匹配查找，因为hashcode是通过所有hash列生成出来的
hash值只支持等值比较查询，包括=，in(),<=>(通过a <=> null，可以得出a为null的记录) 不支持任何范围查询
访问hash索引的数据非常快，除非有很多hash冲突，当出现冲突时，存储引擎只能逐行进行查找
如果hash冲突很多时，维护起来代价也很高，应该避免在选择性比较低的列上建立hash索引
innodb引擎有一个特殊的功能叫做“自适应hash索引”，当innodb注意到某些索引值被频繁的引用，它会在内存中基于B-TREE索引之上再建立一个hash索引
如果某些存储引擎不支持hash索引，我们需要创建自定义的hash索引，创建一个伪hash索引列，通过CRC32()对需要hash的列值计算hash，并在该列上创建索引
对于hash索引查找，需要在where条件语句中加上hashcode比较和列值比较，这样是为了解决hash索引带来的冲突

select url from t_urls where url_code = crc32(‘http://www.baidu.com’) and url = ‘http://www.baidu.com’;

这里如果发生了hash冲突，则根据url列值进行查找
上面创建伪hashcode索引列采用的是crc32算法，生成一个32位的数字，但是通常64位数字hash冲突会更少，可以自己定义一个算法：

select conv(right(md5('http://www.baidu.com'), 16), 16, 10);

如果语句中的索引列不是独立的，那么这条语句就不能使用该列索引，也就是说索引列不能作为表达式的一部分或者不能作为函数的参数

select acter_id from actor where acter_id +1 = 5;
select ... where to_days(current_date) – to_days(date_col)<= 10

对于长度很长的列，创建索引时可以采用类似hash索引那样的，自己建一个伪hashcode列，手动维护这个列，通过列值计算该列对应的数字值并作为hash索引
以url列举例，如果直接使用url，则整个列字段的字符串太长，占据太多空间，我们选择为url创建一个url_code,用来计算crc32(url)得到的数字

create table urls {
    id int unsigned not null auto_increment,
    url varchar(255) not null,
    url_code int unsigned not null default 0
    primary key(id)
}

在插入或者更新url时，通过触发器重新计算url_code的值

delimiter //
create trigger urls_insert_trigger before insert on urls for each row begin 
set new.url_code = crc32(new.url);
end;
//
create trigger urls_update_trigger before update on urls for each row begin 
set new.url_code = crc32(new.url);
end;
//
delimiter;

通过伪hashcode列与该列值来精确查询某一条记录

select * from urls where url_code = crc32(‘http://www.baidu.com’) and url = ‘http://www.baidu.com’;

全文索引

全文索引是一种特殊类型的索引，它查找的是文本中的关键字，而不是直接比较索引中的值，它与其他几种类型的索引匹配方式完全不一样，它存在许多需要注意的细节：如停用词、词干、复数、布尔搜索等，更加类似于搜索引擎要干的事情

前缀索引

通过比较列选择性和索引选择性来决定前缀的长度，对于mysql来说，不允许对text/blob列全值进行索引，但是我们可以通过在查询时指定使用前缀来优化此类查询，比如排序时，避免磁盘临时表排序
选择性：不重复的索引值和数据表记录总数的比值

select count(*) as count, city as city from t_city group by city order by city desc limit 10;

上面这条语句记录了每一个城市出现的重复次数

select count(*) as count, left(city, 3) as pref from t_city group by pref order by pref desc limit 10;

还有一种选择方式：计算列平均选择性，并使前缀选择性接近列选择性

select count(distinct city) / count(*) from t_city;
select count(distinct left(city, 3)) / count(*) from t_city

前缀索引的创建方式

alter table sakila.city_demo add key city(7)
这样就在sakila.city_demo表中创建了一个city前缀索引，索引长度为7个字符，
使用前缀索引的缺点是：前缀索引不能用来做order by 和group by操作，也无法用于作覆盖扫描

后缀索引

还有一种是“反向索引”，针对像url这种类型的字符串列而言的，使用后缀来进行索引效果更佳，但是mysql本身并不支持后缀索引这种方式，所以我们可以通过将保存的url字符串反向存入数据库并创建前缀索引的方式来实现所谓的后缀索引

选择合适的索引顺序

在B-TREE索引中，索引列的顺序意味着索引从最左列进行排序，经验法则告诉我们可以将选择性高的放在前面，当不需要考虑排序和分组时，将选择性高的索引列放在前面通常是非常好的
我们需要对多个列计算每个列对应的选择性，然后做出决策

select count(distinct staff_id) / count(*) as staff_id_selectivity,
count(distinct custom_id) /count(*) as custom_id_selectivity, count(*) from payment \G;

列选择性.png

根据查询结果来看，应该将custom_id放在索引列staff_id前面
顺序的索引会造成的潜在问题：
在高并发工作时，innoDB按主键顺序插入可能会引起明显的间隙锁争用

聚簇索引

聚簇索引其实是一种数据结构，保存了B-TREE索引和数据行，数据表中的数据记录都保存在叶子页上,但是节点页只包含了索引列
聚簇表示数据行与相邻的键值紧凑的存储在一起，

聚簇索引数据结构.png

在innoDB数据库中，通过主键索引列来聚簇数据记录，也就是说，在innoDB聚簇索引中，节点页上保存的是行主键，如果没有主键列，innoDB会选择一个非空索引代替，如果也没有这样的索引，innoDB会创建一个隐式的主键来进行聚簇
在innodb中，没有被用来做聚簇的索引，被称为是二级索引，在索引中保存的并不是物理行的位置，而是行记录的主键，需要根据二级索引找到行主键之后再到聚簇B-TREE中查找指定的行记录
myisam引擎主键与其他索引实现相同，主键只是一个名称为PRIMARY的非空索引。
myisam存储数据就是按照数据的插入顺序保存的，表存储结构的叶子节点上保存了当前索引列值和物理行所在的位置
innodb通过B-TREE结构保存数据表行的所有列记录，二级索引通过保存主键值，在根据主键值在B-TREE结构中查找物理行数据信息

聚集的数据有哪些优点

1. 可以把相关的数据保存在一起，这样在查找记录时可以从磁盘上读取少量的页就能查到结果
2. 访问数据更快，聚簇索引将索引和数据都保存在同一个B-TREE中，因此从聚簇索引获取数据比非聚簇索引获取数据要快
3. 使用覆盖索引扫描的查询，可以直接使用页节点的主键值，无需再根据主键查找数据
   聚簇索引的缺点
   聚簇索引最大限度的提高了I/O密集型应用的性能，但如果数据全部都放在内存中，那么访问的顺序就没那么重要了
4. 插入速度严重依赖于插入顺序，按照主键的顺序插入是加载数据到INNODB表中速度最快的方式
5. 更新聚簇索引列的代价很高，因为会强制每一个被更新的行移动到新的位置
   4． 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题
6. 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏的时候，或者页分裂导致数据存储不连续的时候
7. 二级索引可能比想象的大，因为二级索引的叶子节点包含了引用行的主键列
8. 二级索引访问需要两次索引查找，找主键、找数据

延迟查询

对于某些查询，可以通过延迟查询来优化
explain select * from products where actor = ‘sean carrey’ and title like ‘%apollo%’\G
其中actor 与title 列建立了索引
这里无法对查询进行索引覆盖，因为查询的列为全部列，不存在任何一个索引可以覆盖所有列
改为延迟加载，添加索引覆盖列(actor, title, prod_id)

explain select * from products inner join (
select prod_id from products where actor = ‘sean carrey’ and title like ‘%apollo%’)as t1 on (t1.prod_id = products.prod_id)

上面子查询采用索引覆盖，过滤prod_id,然后根据prod_id再到记录中查找

覆盖索引

如果一个索引包含所有需要查询的字段的值，那么我们就称之为覆盖索引

覆盖索引的好处

1. 索引条目通常远小于数据行大小，所以如果只需要读取索引，那mysql就会极大的减少数据访问量
2. 因为索引是按照列值顺序存储的，所以顺序查询会比随机从磁盘读取数据的I/O要少的多
3. 一些存储引擎如MYISAM在内存中只缓存索引，数据则依赖于具体OS来缓存，因此访问数据意味着还需要一次系统调用，采用覆盖索引则减少了这样的系统调用
4. 针对INNODB的聚簇索引，覆盖索引可以杜绝二级索引根据主键值查找数据行记录
   覆盖索引必须要存储索引列的值，而hash索引、空间索引、全文索引都不存储索引列的值，所以mysql只能使用b-tree索引做覆盖索引
   当发起一个索引覆盖查询时，通过explain分析语句会看到extra Using index,这里的extra表示的是检索数据的方式，需要与type进行区分，type index表示在对结果集进行排序时使用到了索引
   如果查询的列没有被索引覆盖，也就是无法使用索引覆盖查询时，explain查询分析出来extra Using where
   对于下面这条语句：

explain select * from products where actor=’seny carrey’ and title like ‘%apollo%’\G

存在两个问题导致它无法使用覆盖索引：

1. 没有任何一个索引能够覆盖这个查询，因为从表中选择了所有列，而没有任何索引覆盖了所有列
2. mysql不能在索引中执行like操作，只是允许使用左前缀匹配的方式和一些简单的值比较，上面的查询语句可以通过延迟关联来解决：

select * from product inner join(
    select prod_id from product where actor=’seny carrey’ and title like ‘apollo%’
) as t1 on t1.prod_id = product.prod_id\G

使用索引扫描做排序

排序有两种方式：直接通过排序、按索引顺序扫描,如果explain出来的结果中的type为index，则表示使用到了索引扫描来做排序
orderby子句的列顺序必须与索引列定义的顺序完全一致（也就是说按照多个列进行排序，要么都升序，要么都降序），因为mysql是按照索引顺序来组织记录顺序的，而order by 如果打破了这种规则那么就必须使用文件排序
如果查询关联多张表，则只有当order by子句引用的字段全部为第一个表，才能使用索引做排序
还有一种情况就是如果索引前导列（where语句或者join子句中包含的索引第一列）设置为常量时，就可以使用索引进行排序,比如：
(rental_date,inventory_id,customer_id)为一个组合索引，则语句

select rental_id,staff_id from sakila.rental where rental_date=’2005-05-25’ order by inventory_id,customer_id

可以使用索引进行排序，虽然order by 子句不满足索引的最左前缀要求，也可以用于查询排序，因为索引第一列被设置成为了常量

下面列出不能使用索引做排序的查询

1. 使用两种不同的排序方向，但是索引列都是正序排列
   where rental_date=2005-05-25’ order by inventory_id desc,customer_id asc;
2. 引用不存在与索引中的列
   where rental_date=2005-05-25’ order by inventory_id,staff_id
3. where与order by中的列无法组合成索引的最左前缀
   where rental_date=’2005-05-25’ order by customer_id
4. 查询在索引列的第一列为范围查询条件，所以mysql无法使用其他的索引列
   where rental_date > ‘2005-05-25’ order by inventory_id,customer_id
5. 索引列上存在多个等值条件，对于查询来说其实就相当于范围查询
   where rental_date = ‘2005-05-25’ and inventory_id in(1,2) order by customer_id

压缩（前缀压缩）索引

myisam使用前缀压缩索引减少索引的大小，从而让更多的索引能放入内存，默认只压缩字符串，但是也可以配置压缩整数
myisam压缩每个索引块的方法是，先完全保存索引块的第一个值，然后将其他值和第一个值进行比较得到相同的前缀的字节数和不同的后缀，把这部分存储起来即可，比如：索引块中第一个值为perform，第二个值为performance，那么第二个值的前缀压缩后存储的是7,ance这样的形式
前缀索引无法通过二分查找只能从头开始扫描，正序的扫描速度还不错，但反序就不是很好了

冗余索引和重复索引

重复索引，具有相同类型、按照相同顺序的索引，应该避免，发现后立即删除
冗余索引，(A,B)为索引，再创建索引(A)就是冗余索引，因为A索引只是AB索引的前缀索引，因此索引(AB)也可以当做(A)来算
默认情况下在创建innodb二级索引时，主键索引已经默认添加到该索引上了，例如(A, ID)其中id为主键索引
冗余索引必须是相同的类型，其他类型的索引，比如hash索引或者全文索引页不会是B-TREE索引的冗余索引

索引和锁

索引可以让查询锁定更少的行，innodb只有在访问行的时候才会对其加锁，而索引能够减少innodb访问的行数，从而减少锁的数量，但这只有在存储引擎层过滤掉所有不需要的行时才有效

支持多种过滤条件

在有更多不同值的列上创建索引的选择性会更好，在检索时，我们可以将查询用的多的列加入到索引中，对于索引前缀列不需要进行条件过滤时，通过in指定列值，IN的方式对查询检索是有效的，但是对order by则是无效的，比如存在(sex,country)这样的索引，当我们需要使用到该索引时，但又不需要对性别做出限制，那么我们可以通过and sex in (‘m’,’f’)的方式让mysql选择该列索引

避免多个范围条件

针对这两种查询语句：

select actor_id from actor where actor_id > 45;
select actor_id from actor where actor_id in (1,4,49);

这两种查询语句的执行效率是不同的，对于范围查询，mysql是无法使用范围列后面的其他索引列了，但是对于多个等值条件查询，则没有这个限制

维护索引和表

找到并修复索引表

通过check table来检查是否发生了表损坏，并通过repair table来修复表；但是如果存储引擎不支持该命令，也可以通过alter table 重建表来达到修复目的
alter table innodb_tbl ENGINE=INNODB

更新索引统计信息

查询优化器通过两个API来了解存储引擎的索引值分布，通过这两个API的结果来决定使用哪个索引进行查询优化
records_in_range();传入两个边界值计算之间的记录数
info();返回各种类型的数据包括索引基数（通过show index from table）
如果统计信息不准确，那么定会影响到查询优化器的优化策略，通过analyze table重新生成统计信息

数据碎片类型

行碎片：数据行被存储在多个地方的多个片段中
行间碎片：逻辑上顺序的页，在磁盘上不是顺序的
剩余空间碎片：数据页中大量的空余空间
通过optimize table 或者导出再导入的方式来重新整理数据，对于不支持该命令的存储引擎，可以通过alter table tablename engine=<engine>来进行优化
每种存储引擎实现索引统计信息的方式不同，所以需要进行analyze table的频率也不同：

1. memory引擎根本不存储索引统计信息
2. myisam引擎将索引统计信息存储在磁盘中，analyze table需要进行一次全索引扫描来计算索引基数
3. 直到mysql5.5，innodb也不在磁盘存储索引统计信息，而是通过随机的索引访问进行评估，并将估算结果存在内存中

mysql执行状态

通过show full processlist来查看mysql当前处在哪一个状态
sleep 线程正等待客户端发起查询请求
locked 在mysql服务层里，该线程正在等待表锁
Analyzing and statistics 线程正在搜集存储引擎的统计信息，并生成查询执行计划
query 线程正在查询
Copying to tmp table [on disk],线程正在执行查询，并将结果复制到一个临时表中，这种状态要么是在group by操作，要么是在文件排序操作，如果这个状态后面还有on disk ，则表示mysql正在把一个内存临时表放到磁盘
sorting result 线程正在进行排序
Sending data 这个状态有多重可能，有可能是线程之间在进行数据传输，或者正在生成结果集，或者向客户端返回数据