五、MySQL 索引优化一

• 一、MySQL 操作集
• 二、MySQL 数据类型
• 三、MySQL 函数
• 四、MySQL Explain
• 五、MySQL 索引优化一
• 六、MySQL 索引优化二
• 七、SQL 优化
• 八、MySQL 锁与事务
• 九、MySQL 索引数据结构
• 十、MySQL 外键

---

一、索引优化

• 员工记录表。

DROP TABLE IF EXISTS `employees`;

CREATE TABLE `employees`
(
    `id`        int(11)     NOT NULL AUTO_INCREMENT,
    `name`      varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
    `age`       int(11)     NOT NULL DEFAULT '0' COMMENT '年龄',
    `position`  varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
    `hire_time` timestamp   NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
    PRIMARY KEY (`id`),
    -- 联合索引
    KEY `idx_name_age_position` (`name`, `age`, `position`) USING BTREE
) ENGINE = InnoDB
  AUTO_INCREMENT = 4
  DEFAULT CHARSET = utf8 COMMENT ='员工记录表';

# 插入三条数据
INSERT INTO employees(name, age, position, hire_time)
VALUES ('LiLei', 22, 'manager', NOW());
INSERT INTO employees(name, age, position, hire_time)
VALUES ('HanMeimei', 23, 'dev', NOW());
INSERT INTO employees(name, age, position, hire_time)
VALUES ('Lucy', 23, 'dev', NOW());

---

1. 全值匹配

EXPLAIN 
SELECT * FROM employees WHERE name= 'LiLei';

1. type = ref：使用 普通索引 或者 唯一索引的部分前缀。
2. key_len = 74：24 * 3 + 2

---

EXPLAIN 
SELECT * FROM employees WHERE name= 'LiLei' AND age = 22;

1. key_len = 78：(24 * 3 + 2) + 4

---

EXPLAIN 
SELECT * FROM employees 
WHERE name= 'LiLei' AND age = 22 AND position ='manager';

1. key_len = 140：(24 * 3 + 2) + 4 + (20 * 3 + 2)

---

2. 最左前缀法则

• 如果索引有多列，要遵守最左前缀法则。
  指的是 查询 从索引的 最左前列 开始，并且不跳过索引中的列。

---

• 组合索引：idx_name_age_position：(name, age, position)。
• 没有匹配到索引。

EXPLAIN 
SELECT * FROM employees 
WHERE age = 22 AND position ='manager';

---

• 没有匹配到索引。

EXPLAIN 
SELECT * FROM employees 
WHERE position = 'manager';

---

• 匹配到最左索引 name。

EXPLAIN 
SELECT * FROM employees 
WHERE name = 'LiLei';

---

3. 不在索引列上做任何操作

• 在索引列上 计算、函数、自动或手动类型转换。
  都会导致索引失效，而转向 全表扫描。

---

• 匹配到最左索引 name。

EXPLAIN 
SELECT * FROM employees WHERE name = 'LiLei';

---

• 索引失效。

EXPLAIN SELECT * FROM employees WHERE left(name, 3) = 'LiL';

---

3.1 增加一个普通索引

• 增加 入职时间（hire_time）普通索引。

# 加索引
ALTER TABLE `employees`
    ADD INDEX `idx_hire_time` (`hire_time`) USING BTREE;
    
# 删索引
ALTER TABLE `employees` DROP INDEX `idx_hire_time`;

---

• 索引失效。

EXPLAIN 
select * from employees where date(hire_time) ='2018-09-30';

---

• 索引范围查询。

EXPLAIN 
select * from employees 
where hire_time >='2018-09-30 00:00:00' 
and hire_time <='2018-09-30 23:59:59';

1. type = range：索引范围查询。
2. key_len = 4

---

4. 存储引擎 不能使用 索引范围查询 右边的索引

• 使用 普通索引 或者 唯一索引的部分前缀。

EXPLAIN 
SELECT * FROM employees 
WHERE name= 'LiLei' 
AND age = 22 
AND position ='manager';

1. type = ref：使用 普通索引 或者 唯一索引的部分前缀。
2. key_len = 140：(24 * 3 + 2) + 4 + (20 * 3 + 2)

---

• 索引范围查询。

EXPLAIN SELECT * FROM employees 
WHERE name= 'LiLei' AND age > 22 AND position ='manager';

1. type = range：索引范围查询。
2. key_len = 78：(24 * 3 + 2) + 4

---

5. 尽量使用 覆盖索引

• 只访问索引的查询（索引列包含所有查询列）。
  减少 select * 语句。

---

• Using index：使用到覆盖索引。

EXPLAIN SELECT name,age FROM employees 
WHERE name= 'LiLei' AND age = 23 AND position ='manager';

---

• 没有使用到 覆盖索引，产生回表查询。

EXPLAIN SELECT * FROM employees 
WHERE name= 'LiLei' AND age = 23 AND position ='manager';

---

6. 索引失效一、不等于（!= 或 <>）

• 全表扫描。

EXPLAIN 
SELECT * FROM employees 
WHERE name != 'LiLei';

1. type = ALL：即全表扫描。

---

7. 索引失效二、is null、is not null

• 无法使用索引。

EXPLAIN SELECT * FROM employees WHERE name is null;

---

8. 索引失效三、like 以通配符开头

• MySQL 查询时，索引失效 会变成 全表扫描。

EXPLAIN 
SELECT * FROM employees 
WHERE name like '%Lei';

---

8.1 解决索引失效三、索引覆盖

• 索引覆盖。

EXPLAIN 
SELECT name,age,position 
FROM employees 
WHERE name like '%Lei%';

1. 使用覆盖索引，查询字段 必须是建立 覆盖索引 字段。
2. 如果不能使用 覆盖索引，则可能需要借助搜索引擎。
   

---

9. 索引失效四、字符串没加引号

EXPLAIN 
SELECT * FROM employees 
WHERE name = '1000';

1. type = ref：使用 普通索引 或者 唯一索引的部分前缀。

---

EXPLAIN SELECT * FROM employees WHERE name = 1000;

1. Extra = Using where：使用 where 语句来处理结果，查询的列未被索引覆盖。

---

10. 不用 OR 或 IN

• OR 或 IN 查询时，MySQL 不一定使用索引。
  MySQL 内部优化器，会根据 检索比例、表大小 等多个因素。
  整体评估是否使用索引。【详见范围查询优化】

---

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' or name = 'HanMeimei';

1. type = ALL：全表扫描。

---

11. 范围查询优化

---

11.1 增加一个普通索引

• 新增 年龄（age）普通索引。

# 加索引
ALTER TABLE `employees` 
	ADD INDEX `idx_age` (`age`) USING BTREE;

# 删索引
ALTER TABLE `employees` DROP INDEX `idx_age`;

---

• 没有使用到索引。

explain select * from employees where age >= 1 and age <= 2000;

• 上面SQL，可能是由于单次查询数据量过大，导致 优化器 最终选择不走索引。

1. MySQL 内部优化器，会根据 检索比例、表大小 等多个因素。
2. 整体评估是否使用索引。

---

• 优化：可以将大的范围，拆分成多个小范围。

explain select * from employees where age >= 1 and age <= 22;
explain select * from employees where age >= 23 and age <= 2000;

---

二、索引优化总结

• index(a，b，c) 组合索引。

where 语句	是否用到索引
where a = 3	Y，使用到 a
where a = 3 and b = 5	Y，使用到 a，b
where a = 3 and b = 5 and c = 4	Y，使用到 a，b，c
where b = 5 where b = 5 and c = 4 where c = 4	N
where a = 3 and c = 4	Y，使用到 a（c 不可以，因为中间 b 断了）
where a = 3 and b > 5 and c = 4	Y，使用到 a，b（c 不能用在范围之后，b 断了）
where a = 3 and b like “kk%” and c = 4	Y，使用到 a，b，c
where a = 3 and b like “%kk” and c = 4	Y，使用到 a
where a = 3 and b like “%kk%” and c = 4	Y，使用到 a
where a = 3 and b like “k%kk%” and c = 4	Y，使用到 a，b，c

---

• like "KK%" 相当于 = 常量。
• "%KK" 和 "%KK%" 相当于范围。

---