【MySQL 进阶】索引基础入门与相关知识点

推荐阅读

• 学习笔记 《 深入理解 Java 虚拟机》
• 学习笔记 《 后端架构设计》
• 学习笔记 《 Java 基础知识进阶》
• 学习笔记 《 Nginx 学习笔记》
• 学习笔记 《 前端开发杂记》
• 学习笔记 《 设计模式学习笔记》
• 学习笔记 《 DevOps 最佳实践指南》
• 学习笔记 《 Netty 入门与实战》
• 学习笔记 《 高性能MYSQL》
• 学习笔记 《 JavaEE 常用框架》
• 学习笔记 《 Java 并发编程学习笔记》
• 学习笔记 《 分布式系统》
• 学习笔记 《 数据结构与算法》

---

1. 索引

MySQL 的索引所使用的数据结构和具体的存储引擎有关系，索引能够将随机IO 变成顺序IO，大大减少了数据的扫描行数以及帮助服务器排序和避免临时表的创建。

1.1 索引用处

1. 能够快速的查找和匹配Where 语句的执行
2. 从查询中消除行，一般情况下存在多个索引的情况下，MySQL 会找到最少行的索引
3. 如果表具有聚合索引，则优化器可以使用索引的的任何最左值来查找
4. 当有表接连操作的，从其他表查询数据的时候也会用到索引
5. 查找特定索引列的 MIN 或者 MAX操作的时候 (B+ 树作为多叉树，那么其也是有顺序的，所以MIN 或者MAX使用索引则非常方便)

简而言之，就是索引可以加快数据的查询

1.2 索引的分类

索引是由 MySQL 进行维护的，比如MySQL 自动维护了 PK 索引以及唯一键索引。之所以只给PK，QK 创建索引，而非为每个列创建索引，这是因为并不是索引创建的越多越好，因为在数据查询或者变动的时候，需要从磁盘中载入数据以及重新维护数据，维护索引的时候则可能会造成页的分裂和合并，则会设计磁盘IO的问题。

1. 主键索引
2. 唯一键索引
3. 普通索引
4. 全文索引
5. 组合索引(聚合索引)

1.3 主键的选择

1. 自然主键 (和业务挂钩，自增)
2. 代理主键 (和业务无关)

1.4 技术名词

1.4.1 回表

在MySQL 查询的时候首选根据其他普通索引查询到相对应行的主键ID集合，然后根据主键 ID 使用主键唯一索引中查询数据，这种查询方式称之为回表

1.4.2 覆盖索引

在如下的MySQL 语句中, 根据刚刚了解到的回表可知，在语句1 中，MySQL 首先根据name的所在的索引查询到主键ID，然后在根据主键ID，在主键索引中查询到全部的数据，但是在语句2 中并不需要其他类型的数据，在做一次回表操作显然是非常影响性能的，直接返回id即可，这种情况称之为 索引覆盖 或者 覆盖索引

-- Sql 语句 1
select * from user where name = '张三';


-- Sql 语句 2
select id from user where name = '张三';

1.4.3 最左匹配

创建联合索引的时候，可能出现最左匹配的问题。观察下面的SQL ，在下面的4条语句中，语句1，2，4 均会使用 组合所用，

-- 表创建语句
create table test (
	id int primary key auto_increment,
  name varchar(32),
  age int default 18,
  gender int default 1,
  INDEX `TEST_NAME_AGE`(name,age)
);

判断下面SQL 语句是否会走组合索引 TEST_NAME_AGE

-- 语句1
SELECT * FROM test WHERE name = 'zhangsan' and age = 18;

-- 语句2
SELECT * FROM test WHERE name = 'zhangsan';

-- 语句3
SELECT * FROM test WHERE age = 18;

-- 语句4
SELECT * FROM test WHERE age = 18 and name = 'zhangsan';

其中1是完全匹配组合索引的，语句2 因为最左匹配原则，也会使用该索引，语句3则不会，语句4 在MySQL 架构中经过优化器的优化之后则会使用组合索引。

1.4.4 索引下推

（index condition pushdown 简称 ICP）首先创建表和组合索引如下， 在MySQL 5.6 之前的版本中， 执行语句1 的时候，由于使用的了索引的最左匹配，因此会忽略age字段，首先只是用 name 字段 作为唯一条件查询，根据上面的回表操作可知会返回符合条件的行的ID(这里返回1和2)，然后在主键索引中，在根据ID查询到数据行，再校验age判断是否符合条件，这里查询到2行因此需要回表2次。

-- 表创建语句
create table test (
	id int primary key auto_increment,
  name varchar(32),
  age int default 18,
  gender int default 1,
  INDEX `TEST_NAME_AGE`(name,age)
);

INSERT INTO test VALUES (1,"zhangsan",17,1);
INSERT INTO test VALUES (2,"zhangsan",18,2);



-- 语句1
SELECT * FROM test WHERE name LIKE 'zhang%' and age = 18;

显然上面的流程是非常繁琐的，因此从5.6 开始，MySQL 执行查询索引的的时候不在忽略age字段，而是直接作为查询条件只需要回表1次即可。索引下推在非主键索引上的优化，可以有效减少回表的次数，大大提升了查询的效率。


通过SQL 语句 EXPLAIN 可以看到Extra 中标识使用了 索引下推。

> explain  SELECT * FROM test WHERE name like 'zhang%' and age = 18;

***************************[ 1. row ]***************************
id            | 1
select_type   | SIMPLE
table         | test
partitions    | <null>
type          | range
possible_keys | TEST_NAME_AGE
key           | TEST_NAME_AGE
key_len       | 104
ref           | <null>
rows          | 2
filtered      | 33.33
Extra         | Using index condition

同样的，可以通过设置变量的方式关闭索引下推

> set optimizer_switch='index_condition_pushdown=off';

关闭之后，再次EXPLAIN 可以看到 优化项中不存在 索引下推

id            | 1
select_type   | SIMPLE
table         | test
partitions    | <null>
type          | range
possible_keys | TEST_NAME_AGE
key           | TEST_NAME_AGE
key_len       | 104
ref           | <null>
rows          | 2
filtered      | 33.33
Extra         | Using where

1.5 索引列的匹配方式

-- 1. 全值匹配
select * from user where name = 'zhangsan' and age =19;

-- 2. 匹配最左前缀
select * from user where name = 'zhangsan';

-- 3. 匹配列前缀
select * from user where name = 'zhang%';

-- 4. 匹配范围值
select * from user where age > 25;

-- 5. 精确匹配A列，范围匹配B列
select * from user where name = 'zhangsan' and age > 25;

1.6 哈希索引

基于 HASH 表的索引，只有精确匹配才会查询到，也就是说支持 ‘=’ 或者 IN 或者 NOT 等操作,因此不建议做范围查找的时候，使用哈希索引。其存储结构类似于 HashMap。

1.7. 索引优化技巧

1. 单独列作为索引，不要使用表达式作为索引列

-- 错误示例
select * from user where age + 1 > 18;

-- 正确示例
select * from user where age > 17;

2. 尽量使用主键查询而非其他索引，因为使用主键索引不会造成回表操作，使用其他索引会执行若干次回表操作
3. 使用前缀索引，比如在使用LIKE 进行模糊搜索的时候，建议使用 LIKE ‘xxx%’ 的形式，而非 LIKE ‘%xxxx%’
4. 使用索引扫描来进行排序

MySQL 生成索引有两种方式： 手动执行排序 ORDER BY XXX DESC 或者由索引来生成 ，通过EXPLAIN 来看到的数据，如果 type 的类型为index，则说明使用了索引进行排序操作。因为索引(特指 B+ 树索引)本身就有索引，因此使用索引进行排序是非常有效的操作

5. Union all, in, or 都会使用索引，但建议使用 In
6. 范围列可以是索引，比如 >,< ，between 等等，但范围列后面的字段无法用到索引，最多用于一个范围列。
7. 强制类型转换会扫描全表，并不会使用到索引。下面的演示中可以看到last_name 字段类型为 VARCHAR，使用int查询的时候出现了强制类型转换，导致实际使用的key = null，而使用正确类型，未发生强转的则 key = idx_last_name

   > explain SELECT * FROM customer WHERE last_name = 1\G;
   
   ***************************[ 1. row ]***************************
   id            | 1
   select_type   | SIMPLE
   table         | customer
   partitions    | <null>
   type          | ALL
   possible_keys | idx_last_name
   key           | <null>
   key_len       | <null>
   ref           | <null>
   rows          | 599
   filtered      | 10.0
   Extra         | Using where
   
   > explain SELECT * FROM customer WHERE last_name = '1'\G;
   
   ***************************[ 1. row ]***************************
   id            | 1
   select_type   | SIMPLE
   table         | customer
   partitions    | <null>
   type          | ref
   possible_keys | idx_last_name
   key           | idx_last_name
   key_len       | 182
   ref           | const
   rows          | 1
   filtered      | 100.0
   Extra         | <null>

8. 更新频繁，数据区分不高的行，不建议使用索引，这是因为在维护索引的时候，造成IO，造成性能影响。
9. 创建索引时候，不允许列为NULL，否则可能造成意想不到的结果
10. 表连接操作最好不要超过3张，并且连接字段类型和长度必须一致
11. 能使用 Limit 的时候尽量使用 Limit
12. 单表的索引建议在 5 个以内，可能造成索引太多，IO造成性能问题
13. 组合索引的列最好不要超过 5 个
14. 创建索引的时候应该避免索引越多越好以及过早优化的错误想法

索引优化应当对数据库系统有充分的了解和认知的时候在进行优化