MySQL 高级 - 第十一章 | 索引优化与查询优化

---

上篇：第十章、性能分析工具的利用
下篇：第十二章、数据库的设计规范
本文内容主要源于：bilibili-尚硅谷-MySQL高级篇
第十一章 索引优化与查询优化

都有哪些纬度可以举行数据库调优？简言之：

• 索引失效、没有充分利用以是 —— 索引创建
  
• 关联查询太多 JOIN（设计缺陷或不得已的需求）—— SQL 优化
  
• 服务器调优及各个参数设置（缓冲、 线程数）—— 调整 my.cnf
  
• 数据过多 —— 分库分表
  

关于数据库调优的知识点非常分散，不同 DBMS，不同的公司，不同的职位，不同的项目遇到的问题都不尽相同。
虽然 SQL 查询优化的技术许多，但是大体方向上完全可以分为 物理查询优化 和 逻辑查询优化 两大块。

• 物理查询优化是通过 索引 和 表连接方式 等技术来举行优化，这里重点必要把握索引的利用
  
• 逻辑查询优化就是通过 SQL等价变换 提拔查询效率，直白一点来讲就是，换一种实验效率更高的查询写法
  

---

11.1 数据准备

学员表插 50 万条， 班级表插 1 万条
步调1：建表

1. #班级表
2. CREATE TABLE `class` (
3. `id` INT(11) NOT NULL AUTO_INCREMENT,
4. `className` VARCHAR(30) DEFAULT NULL,
5. `address` VARCHAR(40) DEFAULT NULL,
6. `monitor` INT NULL ,
7. PRIMARY KEY (`id`)
8. ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
10. #学员表
11. CREATE TABLE `student` (
12. `id` INT(11) NOT NULL AUTO_INCREMENT,
13. `stuno` INT NOT NULL ,
14. `name` VARCHAR(20) DEFAULT NULL,
15. `age` INT(3) DEFAULT NULL,
16. `classId` INT(11) DEFAULT NULL,
17. PRIMARY KEY (`id`)
18. #CONSTRAINT `fk_class_id` FOREIGN KEY (`classId`) REFERENCES `t_class` (`id`)
19. ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

复制代码

步调2：设置参数
下令开启：允许创建函数设置

1. set global log_bin_trust_function_creators=1;   
2. # 不加global只是当前窗口有效。

复制代码

步调3：创建函数
随机产生字符串，保证每条数据都不同

1. #随机产生字符串
2. DELIMITER //
3. CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
4. BEGIN  
5. DECLARE chars_str VARCHAR(100) DEFAULT
6. 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
7. DECLARE return_str VARCHAR(255) DEFAULT '';
8. DECLARE i INT DEFAULT 0;
9. WHILE i < n DO
10. SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
11. SET i = i + 1;
12. END WHILE;
13. RETURN return_str;
14. END //
15. DELIMITER ;
17. #假如要删除
18. #drop function rand_string;

复制代码

随机产生班级编号

1. #用于随机产生多少到多少的编号
2. DELIMITER //
3. CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
4. BEGIN
5. DECLARE i INT DEFAULT 0;
6. SET i = FLOOR(from_num +RAND()*(to_num - from_num+1))  ;
7. RETURN i;
8. END //
9. DELIMITER ;
11. #假如要删除
12. #drop function rand_num;

复制代码

步调4：创建存储过程
创建往 stu 表中插入数据的存储过程

1. #创建往stu表中插入数据的存储过程
2. DELIMITER //
3. CREATE PROCEDURE insert_stu(  START INT , max_num INT )
4. BEGIN
5.         DECLARE i INT DEFAULT 0;
6.         SET autocommit = 0;   #设置手动提交事务
7.         REPEAT  #循环
8.         SET i = i + 1;  #赋值
9.         INSERT INTO student (stuno, name ,age ,classId ) VALUES
10.         ((START+i),rand_string(6),rand_num(1,50),rand_num(1,1000));
11.         UNTIL i = max_num
12.         END REPEAT;
13.         COMMIT;  #提交事务
14. END //
15. DELIMITER ;
17. #假如要删除
18. #drop PROCEDURE insert_stu;

复制代码

创建往 class 表中插入数据的存储过程

1. #执行存储过程，往class表添加随机数据
2. DELIMITER //
3. CREATE PROCEDURE `insert_class`( max_num INT )
4. BEGIN
5.         DECLARE i INT DEFAULT 0;
6.         SET autocommit = 0;  
7.         REPEAT
8.         SET i = i + 1;
9.         INSERT INTO class ( classname,address,monitor ) VALUES
10.         (rand_string(8),rand_string(10),rand_num(1,100000));
11.         UNTIL i = max_num
12.         END REPEAT;
13.         COMMIT;
14. END //
15. DELIMITER ;
17. #假如要删除
18. #drop PROCEDURE insert_class;

复制代码

步调5：调用存储过程
往 class 表添加 1 万条数据

1. #执行存储过程，往class表添加1万条数据
2. CALL insert_class(10000);

复制代码

往 stu 表添加 50 万条数据，这个时间会轻微有点长

1. #执行存储过程，往stu表添加80万条数据
2. CALL insert_stu(100000,800000);

复制代码

查询下数据是否插入成功

1. SELECT COUNT(*) FROM class;
2. SELECT COUNT(*) FROM student;

复制代码

步调6：删除某表上的索引
创建删除索引存储过程。这是为了方便我们的学习，由于我们在演示某个索引的效果时，可能必要删除其它索引，如果必要一个个手工删除，就太费劲了。

1. DELIMITER //
2. CREATE  PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))
3. BEGIN
4.    DECLARE done INT DEFAULT 0;
5.    DECLARE ct INT DEFAULT 0;
6.    DECLARE _index VARCHAR(200) DEFAULT '';
7.    DECLARE _cur CURSOR FOR  SELECT  index_name  FROM
8. information_schema.STATISTICS  WHERE table_schema=dbname AND table_name=tablename AND
9. seq_in_index=1 AND  index_name <>'PRIMARY' ;
10. #每个游标必须使用不同的declare continue handler for not found set done=1来控制游标的结束
11.    DECLARE  CONTINUE HANDLER FOR NOT FOUND set done=2 ;   
12. #若没有数据返回,程序继续,并将变量done设为2
13.     OPEN _cur;
14.     FETCH _cur INTO _index;
15.     WHILE _index<>'' DO
16.        SET @str = CONCAT("drop index " , _index , " on " , tablename );
17.        PREPARE sql_str FROM @str ;
18.        EXECUTE sql_str;
19.        DEALLOCATE PREPARE sql_str;
20.        SET _index='';
21.        FETCH _cur INTO _index;
22.     END WHILE;
23. CLOSE _cur;
24. END //
25. DELIMITER ;

复制代码

实验存储过程

1. CALL proc_drop_index("dbname","tablename");

复制代码

---

11.2 索引失效案例

MySQL 中提高性能的一个最有效的方式是对数据表 设计合理的索引。索引提供了高效访问数据的方法，而且加快查询的速度，因此索引对查询的速度有着至关重要的影响。

• 利用索引可以 快速地定位 表中的某条记载，从而提高数据库查询的速度，提高数据库的性能
  
• 如果查询时没有利用索引，查询语句就会 扫描表中的全部记载。在数据量大的环境下，如许查询的速度会很慢
  

大多数环境下都（默认）采用 B+树 来构建索引。只是空间列范例的索引利用 R-树，而且 MEMORY 表还支持 hash 索引。
实在，用不消索引，最终都是优化器说了算。优化器是基于什么的优化器？基于 cost开销(CostBaseOptimizer)，它不是基于 规则(Rule-BasedOptimizer)，也不是基于语义。怎么样开销小就怎么来。另外，SQL 语句是否利用索引，跟数据库版本、数据量、数据选择度都有关系。

---

11.2.1 全值匹配

全值匹配可以充分的利用组合索引
体系中经常出现的 sql 语句如下，当没有创建索引时，possible_keys 和 key 都为 NULL

1. # SQL_NO_CACHE表示不使用查询缓存
2. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;
3. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4;
4. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';

复制代码

此时实验 SQL ，数据查询速度会比较慢，耗时 0.28s

1. mysql> SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';
2. Empty set, 1 warning (0.28 sec)

复制代码

接下来我们创建索引

1. CREATE INDEX idx_age ON student(age);
3. CREATE INDEX idx_age_classid ON student(age,classId);
5. CREATE INDEX idx_age_classid_name ON student(age,classId,NAME);

复制代码

创建索引后实验，发现利用到了团结索引，且耗时较短 0.00s

1. mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';
2. +----+-------------+---------+------------+------+----------------------------------------------+----------------------+---------+-------------------+------+----------+-------+
3. | id | select_type | table   | partitions | type | possible_keys                                | key                  | key_len | ref               | rows | filtered | Extra |
4. +----+-------------+---------+------------+------+----------------------------------------------+----------------------+---------+-------------------+------+----------+-------+
5. |  1 | SIMPLE      | student | NULL       | ref  | idx_age,idx_age_classid,idx_age_classid_name | idx_age_classid_name | 73      | const,const,const |    1 |   100.00 | NULL  |
6. +----+-------------+---------+------------+------+----------------------------------------------+----------------------+---------+-------------------+------+----------+-------+
7. 1 row in set, 2 warnings (0.00 sec)
9. mysql> SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';
10. Empty set, 1 warning (0.00 sec)

复制代码

从上面三个索引中，优化器会根据查询的条件选择它认为比较合适的索引，比如上述 SQL 中有通过 age、classId、name 作为查询条件，而 idx_age_classid_name 这个团结索引正好是由这三个字段构成的，以是选择了该索引。
   注意： 上面的索引可能不见效哦，在数据量较大的环境下，我们举行全值匹配 SELECT *，优化器可能经过盘算发现，我们利用索引查询全部的数据后，还必要对查找到的数据举行回表操作，性能还不如全表扫描。
  

---

10.2.2 最佳左前缀法则

在 MySQL 创建团结索引时会遵守最佳左前缀匹配原则，即最左优先，在检索数据时从团结索引的最左边开始匹配。
1. 下面的 SQL 将利用索引 idx_age

1. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name='abcd';

复制代码

2. 下面的 sql 不会利用索引，由于我没有创建 classId 大概 name 的索引

1. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classId=4 AND student.name='abcd';

复制代码

3.下面的 sql 查询就是遵守这一原则的精确打开方式

1. EXPLAIN SELECT SQL_NO_CACHE * FROM student
2. WHERE student.age = 30 AND student.classId=4 AND student.name='abcd';

复制代码

思考：下面sql会不会利用索引呢？

1. EXPLAIN SELECT SQL_NO_CACHE * FROM student
2. WHERE student.classId=4 AND student.age = 30 AND student.name='abcd';

复制代码

答案是会的，由于优化器会实验优化，会调整查询条件的顺序，不过在开发过程中我们还是要保持良好的开发习惯。
思考：删去索引 idx_age_classid 和 idx_age，只保留 idx_age_classid_name，实验如下 sql，会不会利用索引？

1. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE  student.age = 30 AND student.name='abcd';

复制代码

答案是 会，但是只会用一部分。看看实验效果

利用了 idx_age_classid_name，但是 key_len 是 5，也就是说只利用了 age 部分的排序，由于 age 是 int 范例，4 个字节加上 null 值列表一共 5 个字节。由于 B+树 是先按照 age 排序，再按照 classid 排序，最后按照 name 排序，因此不能跳过 classId 的排序直接就利用 name 的排序。