MySQL编程优化篇（二） SQL优化（ⅱ）索引问题

悠悠 2023-06-23 10:56 110阅读 0赞

**目录**

索引的分类

能够使用索引的典型场景

存在索引但不能使用索引的典型场景

--------------------

# 索引的分类 #

*  **B-Tree索引**：最常见的索引类型，大部分引擎都支持B树索引
 *  **HASH索引**：只有Memory引擎支持，使用场景简单
 *  **R-Tree索引（空间索引）**：空间索引是MyISAM的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍
 *  **Full-text（全文索引）**：全文索引也是 MyISAM 的一个特殊索引类型，主要用于全文索引，InnoDB从MySQL 5.6版本开始提供对全文索引的支持。
 *  MySQL目前不支持函数索引，但是能对列的前面某一部分进行索引（前缀索引）；缺点，在排序Order By和分组Group By操作的时候无法使用

# 能够使用索引的典型场景 #

如有复合索引：(a, b)

1.  **匹配全值**（Match the full value），对索引中所有列都指定具体值，即是对索引中的所有列都有等值匹配的条件，如：a = xx AND b = xx。
2.  **匹配值的范围查询**（Match a range of values），对索引的值能够进行范围查找，如：a > 1 AND a < 10。
3.  **匹配最左前缀**（Match a leftmost prefix），仅仅使用索引中的最左边列进行查找，比如在 col1 + col2 + col3 字段上的联合索引能够被包含 col1、（col1 + col2）、（col1 + col2 + col3）的等值查询利用到，可是不能够被 col2、（col2 + col3）的等值查询利用到。
4.  **仅仅对索引进行查询**（Index only query），当查询的列都是索引的字段中时，查询的效率更高，即：避免使用select \*，最好使用select a,b ...，即查询的列都是索引列，不需要回表查数据
5.  **匹配列前缀**（Match a column prefix），仅仅使用索引中的第一列，并且只包含索引第一列的开头一部分进行查找，即: `like 'a%'`。
6.  **能够实现索引匹配部分精确而其他部分进行范围匹配**（Match one part exactly and match a range on another part），如：a = ‘x’ AND b > 1AND b < 2
7.  **如果列名是索引，那么使用 column\_name is null就会使用索引**（区别于Oracle）
8.  MySQL 5.6引入了 **Index Condition Pushdown（ICP）**的特性，进一步优化了查询。Pushdown表示操作下放，某些情况下的条件过滤操作下放到存储引擎。Explain Extra：Using index condition就表示MySQL使用了 ICP 来进一步优化查询。

补充：Index Condition Pushdown（ICP），将customer\_id的过滤操作下推到存储引擎层来完成，这样能够降低不必要的IO访问

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTQxNjE5_size_16_color_FFFFFF_t_70][]

# 存在索引但不能使用索引的典型场景 #

1.  以%开头的LIKE查询不能够利用B-Tree索引，执行计划中key的值为NULL表示没有使用索引
2.  数据类型出现隐式转换的时候也不会使用索引
3.  复合索引的情况下，假如查询条件不包含索引列最左边部分，即不满足最左原则Leftmost，是不会使用复合索引的
4.  如果 MySQL 估计使用索引比全表扫描更慢，则不使用索引。在查询的时候，筛选性越高越容易使用到索引，筛选性越低越不容易使用索引。
5.  用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTQxNjE5_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/2019121816435364.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTQxNjE5,size_16,color_FFFFFF,t_70