覆盖索引、索引下推（ICP）及其他优化策略

目录

覆盖索引

覆盖索引的使用

覆盖索引的利弊

索引下推

使用前后对比（理论上）

索引下推案例分析

ICP的开启/关闭

ICP的开启/关闭案例分析

ICP的使用条件 

其他优化策略

1.Exists和In的区分

2.count(*) 与count(具体字段)效率

3.关于select(*) 

4.LIMIT1对优化的影响

实际开发推荐的主键设计

非核心业务

核心业务

认识UUID

改造UUID

---

覆盖索引

如何理解覆盖索引：

• 理解方式一：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到部分列的数据，毕竟索引叶子节点存储了他们索引的数据，一个索引包含了满足查询结果的数据就叫做覆盖索引。
• 理解方式二：非聚簇索引的一种形式，简单来说就是：索引列+主键包含select到from之间查询的列。

覆盖索引的使用

• 举例1：

SHOW INDEX FROM student;

EXPLAIN SELECT * FROM student WHERE age <> 20;

EXPLAIN SELECT age,NAME FROM student WHERE age <> 20;

结果：还是沿用本专栏的数据表student，执行第一条sql语句查询到该表的二级索引有idx_age_name 当执行第二条sql语句时索引失效了，执行第三条sql语句时索引命中

• 举例2： 

EXPLAIN SELECT * FROM student WHERE NAME LIKE '%abc';

EXPLAIN SELECT id,age FROM student WHERE NAME LIKE '%abc';

结果：和上述结果一样，当需要返回的字段都存在于二级索引上的叶子节点上时索引命中，而需要query的列只有一部分字段存在二级索引叶子节点上时，索引失效。

覆盖索引的利弊

好处：

        1.避免Innodb表进行索引的二次查询（回表）

        Innodb是以聚簇索引的顺序来存储的，对于Innodb来说，二级索引在叶子节点中所保存的是行的主键信息，如果是用二级索引查询数据，在查找相应的键值后，还需通过主键进行二次查询才能获取我们真实所需要的数据。在覆盖索引中，二级索引的键值中可以获取所要的数据，避免了对主键的二次查询，减少了IO操作，提升了查询效率

        2.可以把随机IO变成顺序IO加快查询效率

        由于覆盖索引是按键值的顺序存储的，对于IO密集型的范围查找来说，对比随机从磁盘读取每一行的数据IO要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的IO转变成索引查找的顺序IO。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段

弊端：

索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这就是业务DBA，或者称为业务数据架构师的工作。

索引下推

Index Condition Pushdown(ICP)是MySQL5.6中的新特性，是一种在存储引擎层使用索引过滤数据的优化方式。

使用前后对比（理论上）

• 启用ICP前，存储引擎会遍历索引以定位基表中的行，并将他们返回给MySQL服务器，由MySQL服务器评估where后面的条件是否保留行
• 启用ICP后，如果部分where条件可以仅使用索引中的列进行筛选，则MySQL服务器会把这部分where条件放到存储引擎筛选。然后，存储引擎通过使用索引条目来筛选数据，并且只有在满足这一条件是才从表中读取行。好处是ICP可以减少存储引擎必须访问基表的次数和MySQL服务器必须访问存储引擎的次数，但是，ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据比例。

索引下推案例分析

• 举例1：

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';

 执行结果：当使用索引条件下推时，Explain<