MySQL高级第九篇：覆盖索引和索引条件下推等相关策略

一、覆盖索引

1. 什么是覆盖索引?

• 解释一：

  • 索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。
  • 毕竟索引叶子节点存储了它们索引的数据，当能通过读取索引就可以得到想要的数据时，那就不需要读取行了。
    一个索引包含了满足查询结果的数据就叫做覆盖索引。

• 解释二：

  • 非聚簇复合索引的一种形式，它包括在查询里的SELECT、JOIN和WHERE子句用到的所有列（即建索引的字段正好是覆盖查询条件中所涉及的字段）。
    简单说就是，索引列＋主键包含SELECT到FROM之间查询的列。

举例：

# id为主键，创建一个age,NAME联合索引
CREATE INDEX idx_age_name ON student (age,NAME);

# 前边我们学习过，不等于会使索引失效，但是下边会打破这个规则，因为优化器根据执行成本选择到底使不使用

# 这条语句就用不到索引，因为 SELECT * ，反之会回表操作，就没有必要使用索引了
EXPLAIN SELECT * FROM student WHERE age <> 20;

# 这条就可以，因为查询的字段刚好就是索引字段，不需要回表
EXPLAIN SELECT age,NAME FROM student WHERE age <> 20;

2. 覆盖索引的好处

避免lnnodb表进行索引的二次查询（回表）

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

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

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

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

二、索引条件下推

1. 举例：

# key1是索引，如下查询
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';

• 按照我们之前的学习，like以通配符%开头索引失效，应该是先用索引查询条件 key1 > ‘z’，然后进行回表，在查到的记录中在查找条件 key1 LIKE ‘%a’
• 其实并不是的，优化器会 先用索引查询条件 key1 > 'z'，不回表，继续在这些索引中过滤条件 key1 LIKE '%a'，最后只回表一次，这就是索引条件下推。

ICP 默认是开启的，可以选择手动关闭

2. ICP的使用条件

• 如果表访问的类型为range、ref、eq＿ref 和 ref＿or＿null 可以使用ICP
• ICP可以用于 InnoDB 和 MyISAM 表，包括分区表 InnoDB 和 MyISAM 表
• 对于InnoDB表，ICP仅用于二级索引。ICP的目标是减少全行读取次数，从而减少I／O操作。
• 当SQL使用覆盖索引时，不支持ICP。因为这种情况下使用ICP不会减少 I/O。
• 相关子查询的条件不能使用ICP。

三、其他相关策略

1. EXISTS 和 IN的选择

# 
SELECT * FROM A WHERE cc IN（SELECT cc FROM B）

SELECT * FROM A WHERE EXISTS(SELECT cc FROM B WHERE B．cc＝A．cc)

• 当 A 小于 B 时，使用 EXISTS，因为 EXISTS 执行相当于一个嵌套循环，每次拿 A 的一条数据，去 B 里边循环比较。
• 当 B小于 A 时，使用 IN，因为 B 表较小，IN先计算出 IN 中语句的结果，然后外层直接精确匹配。

总结就是：小表驱动大表

2. COUNT(*) ，COUNT(1) 和 COUNT(字段)

• COUNT(*) 和 COUNT(1)
  • 这两本质上没有什么区别，执行效率相差不多。
  • 如果是在 MyISAM 中，统计表行数只需要O(1)复杂度，因为每张 MyISAM 表都有一个 meta 存储了row_count。
  • 如果是InnoDB，需要全表扫描，因为 它支持事物，采用行级锁和MVCC机制，无法维护row_count，时间复杂度是O(n)。
• COUNT(字段)
  • 在 InnoDB 中，要尽量采用二级索引，因为聚簇索引包含信息较多。
  • 对于COUNT(*) 和 COUNT(1)，其实系统会自动选择较小的二级索引来统计。

3. 关于SELECT(*)

• 不建议使用：
  • MySQL在解析的过程中，会通过查询数据字典将 “ * ” 按序转换成所有列名，这会大大的耗费资源和时间。
  • 无法使用覆盖索引。

4. 关于LIMIT 1

• 针对的是会扫描全表的SQL语句，如果你可以确定结果集只有一条，那么加上 LIMIT 1 的时候，当找到一条结果的时候就不会继续扫描了，这样会加快查询速度。
• 如果数据表已经对字段建立了唯一索引，那么可以通过索引进行查询，不会全表扫描的话，就不需要加上LIMIT 1了。

5. 关于 COMMIT

• 只要有可能，在程序中尽量多使用COMMIT，这样程序的性能会得到提高，需求也会因为COMMIT所释放的资源而减少。