【高性能MySQL】第5章高性能的索引策略

5.3.6覆盖索引

覆盖索引：索引包含（覆盖）所有需要查询的字段的值，必须要存储索引列的值

优点：减少数据访问量，提高性能，减少I/O

限制：

不是all类型的索引都可成为覆盖索引

哈希、空间、全文索引不存储索引列的值 mysql只能用b-tree索引

EXPLAIN的Extra列Using index :使用了覆盖索引

前：

后：

 

索引条件推送

    mysql5.6 改进 索引条件推送：改善查询执行方式

    mysql利用索引（二级索引）元组和筛字段在索引中的where条件从表中提取数据记录的一种优化操作

概念：

   Index Condition Pushdown (ICP)用某一个索引对一个特定的表 从表中获取元组，单表利用索引进行扫描以获取数据

目的：

   减少完整记录（一条完整元组）读取的个数；

    对于InnoDB聚集索引无效，只能是对SECOND INDEX这样的非聚集索引有效

 //打开ICP，则Extra列中显示“Using index condition” off关闭
set optimizer_switch='index_condition_pushdown=on'; 

优化过程：

    存储引擎在访问索引的时候检查筛选字段在索引中的where条件（pushed index condition，推送的索引条件），如果索引元组中的数据不满足推送的索引条件，那么就过滤掉该条数据记录。ICP（优化器）尽可能的把index condition的处理从server层下推到storage engine层；

 

细化：对比

这个图很棒，真是一张图胜过千言万语的本图了，如果不太理解的话，看原文【源】【源】

不使用ICP的查询过程：

1）存储引擎读取下一行时，首先读取索引元组（index tuple）

        然后使用索引元组在基表中（base table）定位和读取整行数据。

2)    sever层评估where条件，如果该行数据满足where条件则使用，否则丢弃。

3)    执行1），直到最后一行数据。

 

使用ICP：

索引条件下推：

    筛选字段在索引中的where条件从server层下推到storage engine层

    减少存储引擎访问基表的次数和mysql server访问存储引擎的次数

过程：下推  细化

1)    storage engine从索引中读取下一条索引元组。

2)    storage engine使用索引元组评估下推的索引条件

        如果没有满足wehere条件，storage engine将会处理下一条索引元组（回到上一步）。

3)    如果满足下推的索引条件，storage engine通过索引元组定位基表的行和读取整行数据并返回给server层。

4)    server层评估没有被下推到storage engine层的where条件，如果该行数据满足where条件则使用，否则丢弃

注意一下ICP的使用条件：

1. 只能用于二级索引(secondary index)。
2. explain显示的执行计划中type值（join 类型）为range、 ref、 eq_ref或者ref_or_null。且查询需要访问表的整行数据，即不能直接通过二级索引的元组数据获得查询结果(索引覆盖)。
3. ICP可以用于MyISAM和InnnoDB存储引擎，不支持分区表（5.7将会解决这个问题）。

 

谢谢分享：不错的文章

https://www.cnblogs.com/zengkefu/p/5684101.html

https://www.cnblogs.com/zengkefu/p/5684101.html

https://mariadb.com/kb/en/library/index-condition-pushdown/

http://mdba.cn/2014/01/21/index-condition-pushdownicp%E7%B4%A2%E5%BC%95%E6%9D%A1%E4%BB%B6%E4%B8%8B%E6%8E%A8/

 

5.3.7使用索引扫描来做排序

生成有序结果：1、排序，2、按索引顺序扫描（explain type列为index）

如果索引不能覆盖all查询的列，还要回表、慢、I/O

可用索引排序：

关联多张表，order by引用字段来自第一张表

只有当索引列顺序和order by子句顺序一致，all列排序方向一致时（desc asc），才能使用索引对结果做排序

order by子句：最左前缀的要求（除前导列为常量时）前导列貌似是索引的第一列

一些可 不可 使用索引排序的例子 略

 

5.3.8压缩（前缀压缩）索引

MyISAM，更多索引放入内存，某些操作变慢，提性能；

先保存索引块第一个值，其他值和第一值比较=相同前缀字节数+不同后缀部分 存储

指针行采用如下类似的压缩方式
create table语句中指定PACK_KEYS控制索引压缩方式
第一个值perform 第二值performance 第二值前缀压缩后存7，ance

依赖前值、从头扫描（倒序：all查找均需扫描半个索引块），CPU密码型 正序 推荐

5.3.9冗余和重复索引

相同列建多个索引单独维护，优化器优化查询时逐个考虑：影响性能

重复索引：相同列相同顺序创建相同类型的索引，避免，见则删

 

冗余索引：

创建索引A B

    在创建A（前缀索引）：冗余，

    如再创建B A，则不是 索引B 也不是 ，其他哈希、全文索引不会是b-tree的冗余

    将索引扩展为A ID，ID主键，对InnoDB来说主键列包含在二级索引中，冗余只是对B-Tree，

尽量扩展已有索引不是创建新索引；新增索引将导致insert update delete操作变慢

解决方法：删除

怎么找到：

   写写复杂访问information_schema表的查询

   使用shlomi noach的common_schema的视图定位

    percona toolkit的pt-duplicate-key-checker分析

 

5.3.10未使用的索引

删除

怎么找到：

   percona service或mariaDB中打开userstates服务器变量，运行一段时间，查询information_schema.index_statistics找到索引使用频率

         使用percona toolkit的pt-index-usage，读取查询日志，对查询进行explain

 

5.3.11索引和锁

InnoDB在访问行时才会对其加锁，索引能够减少InnoDB访问行数，减少锁数据

5.6之前的版本在索引无法过滤无效行的时候有些 差强人意

innodb在二级索引上使用共享锁，访问主键索引需要排他锁

     不在可能使用覆盖索引，使select for update比lock in share mode 或非锁定查询慢很多

 

explain：

extra值：

  1、using where服务器将存储引擎返回行以后再应用where过滤条件

 

小结：其实就是个目录

     覆盖索引：索引条件推送  奖5.6一朵小红花

     使用索引扫描来做排序：限制条件 敲黑板

     压缩、冗余、重复、未使用：咳~过

     索引和锁：虽然字少 但是还是很重要滴，后面章节补上