Mysql 索引优化

前驱知识

1.Explain关键词：Mysql优化之Explain

2.联合索引结构：

现有 people 表数据如下：

如下面 SQL 语句建立索引：

create index idx_lnameFnameDob on people(last_name, first_name, dob);

其索引结构为：

对于表中的每一行数据，联合索引包含了 last_name,，first_name 和 dob 列的值。对于相同 last_name 的值，则根据 first_name 来排序，对于相同的 last_name 与 first_name，则根据 dob 来排序。

常见的索引优化方法

现有如下职工表：

对 name，age，pos 建立联合索引：

ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(name,age,pos);

1.违背最左前缀法则

最左前缀法则：使用联合索引进行查询，查询应从索引的第一列开始，并且不跳过索引中的列。

1. 针对name，name and age，name and age and pos查询：

通过 explain 我们不难看出，三个查询都使用了索引index_staffs_nameAgePos，但是 key_len 不同，ref 列中的 const 数量也不同，这是因为每次查询精度变高了使用到索引的地方变多，这只是个铺垫，重点在下方。

2. 针对age and pos，pos，name and pos查询：

可以看到对 age and pos，pos 的查询没有使用索引，这是因为联合索引 index_staffs_nameAgePos 是根据 name 属性建立的，根据最左前缀法则，没有从对索引第一列 name 开始查询，因此无法使用该索引。

对 name and pos 的查询使用到了索引，但通过观察可以看出，ref 列只有一个 const，按照最左前缀法则，该查询跳过了中间列 age，因此对pos上的查询无法使用上该索引。

2.在索引上做任何操作（计算、函数、类型转换）

1. 在索引上进行函数操作

使用LEFT(name, 4)获取 name 属性的左边四位字符，可以看出搜索结果没有错误，但是通过 explain 分析，不使用 left 的 SQL使用了索引，使用了 left 的 SQL 没有使用索引。

2. 在索引上进行类型转换

name 属性是字符（varchar）类型，不加引号时，存储引擎认为2000时整数型，赋值给 name 会自动转换成字符型，正因为这个类型转换，导致索引失效。

3.范围查找右边的属性索引失效

key_length 表示索引中使用的字节数，可以看出第二个查询索引使用的更充分。这两个查询的唯一不同在于第一个查询对 age 进行范围查找，第二个查询对 pos 进行查找。而范围查找会导致右边的属性失效，因此第一个查询会导致联合索引对 pos （age 右边的属性）的检索失效。而 pos 右侧属性为空，因此不会导致索引失效。

结合联合索引的结构图，我们可以进一步分析：

第一个查询中肯定使用上了联合索引中的 name 属性，查找到所有的 July 叶子节点，再使用 age 属性，查找到所有满足范围查找条件的数据，根据联合索引建立的原理，对相同 age 值的pos 值进行排序，因此 age 范围查找之后的 pos 不满足有序性，因此联合索引对 pos 的检索失效。

4.尽量使用覆盖索引，减少 Select *

覆盖索引：Select 的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。

第二个查询的 Extra 列多了 Using Index，表示使用了覆盖索引。两句SQL的输出分别为：

第一句SQL多了add_time属性，由于索引中不包含 add_time 属性，因此 Mysql 根据联合索引找到这条数据的主键 id 时，需要返回主键索引再查找一次，这个过程也称为回表。回表会降低 Mysql 性能。所以在编写SQL语句时，尽量减少 Select * 的使用，减少回表的次数。

5.不等于（ != 或 <>）无法使用索引

索引无法对不等于起作用，不等于会导致全表扫描。

6.判断是否为空（ is null 或 is not null）无法使用索引

7.使用连接词OR会导致索引失效

Optimizer（查询优化器）很难优化连接词 or，可以使用 union 进行替代。只有在 or 无需索引，且数据量较小时可以考虑使用。

8.通配符开头的 LIKE 查询导致索引失效

1. 通配符（%）放在字符串左边会导致索引失效

第一二句 SQL 无法使用索引，第三四句 SQL 使用了索引。可以看出通配符放在最左边无法使用，放在中间或右边可以使用索引。

MySQL 不能在索引中执行 LIKE 操作。这是底层存储引擎 API 的限制。但 MySQL 可以在索引中做最左前缀匹配的 LIKE 比较，因为该操作可以转换成简单的比较操作，但是如果是通配符开头的 LIKE 查询，存储引擎就无法做比较匹配。这种情况下，MySQL 服务器只能全表扫描。

2. 通过覆盖索引实现优化

由于 Select 选中的列与 WHERE 过滤条件都可以被索引覆盖，因此优化器话选择通过索引查找。