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

---

上篇：第十章、性能分析工具的使用

本文内容主要源于：bilibili-尚硅谷-MySQL高级篇

第十一章 索引优化与查询优化

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

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

关于数据库调优的知识点非常分散，不同 DBMS，不同的公司，不同的职位，不同的项目遇到的问题都不尽相同。

虽然 SQL 查询优化的技术很多，但是大体方向上完全可以分为 物理查询优化 和 逻辑查询优化 两大块。

• 物理查询优化是通过 索引 和 表连接方式 等技术来进行优化，这里重点需要掌握索引的使用
• 逻辑查询优化就是通过 SQL等价变换 提升查询效率，直白一点来讲就是，换一种执行效率更高的查询写法

---

11.1 数据准备

学员表插 50 万条， 班级表插 1 万条

步骤1：建表

#班级表
CREATE TABLE `class` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`className` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
`monitor` INT NULL ,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

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

步骤2：设置参数

命令开启：允许创建函数设置

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

步骤3：创建函数

随机产生字符串，保证每条数据都不同

#随机产生字符串
DELIMITER //
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN  
DECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO 
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1)); 
SET i = i + 1;
END WHILE;
RETURN return_str;
END //
DELIMITER ;

#假如要删除
#drop function rand_string;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

随机产生班级编号

#用于随机产生多少到多少的编号
DELIMITER //
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN 
DECLARE i INT DEFAULT 0; 
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1))  ;
RETURN i; 
END //
DELIMITER ;

#假如要删除
#drop function rand_num;
1
2
3
4
5
6
7
8
9
10
11
12

步骤4：创建存储过程

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

#创建往stu表中插入数据的存储过程
DELIMITER //
CREATE PROCEDURE insert_stu(  START INT , max_num INT )
BEGIN 
	DECLARE i INT DEFAULT 0; 
	SET autocommit = 0;   #设置手动提交事务
	REPEAT  #循环
	SET i = i + 1;  #赋值
	INSERT INTO student (stuno, name ,age ,classId ) VALUES
	((START+i),rand_string(6),rand_num(1,50),rand_num(1,1000)); 
	UNTIL i = max_num 
	END REPEAT; 
	COMMIT;  #提交事务
END //
DELIMITER ;

#假如要删除
#drop PROCEDURE insert_stu;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

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

#执行存储过程，往class表添加随机数据
DELIMITER //
CREATE PROCEDURE `insert_class`( max_num INT )
BEGIN 
	DECLARE i INT DEFAULT 0; 
	SET autocommit = 0;  
	REPEAT 
	SET i = i + 1; 
	INSERT INTO class ( classname,address,monitor ) VALUES
	(rand_string(8),rand_string(10),rand_num(1,100000)); 
	UNTIL i = max_num 
	END REPEAT; 
	COMMIT;
END //
DELIMITER ;

#假如要删除
#drop PROCEDURE insert_class;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

步骤5：调用存储过程

往 class 表添加 1 万条数据

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

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

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

查询下数据是否插入成功

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

步骤6：删除某表上的索引

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

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

执行存储过程

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

---

11.2 索引失效案例

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

• 使用索引可以 快速地定位 表中的某条记录，从而提高数据库查询的速度，提高数据库的性能
• 如果查询时没有使用索引，查询语句就会 扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢

大多数情况下都（默认）采用 B+树 来构建索引。只是空间列类型的索引使用 R-树，并且 MEMORY 表还支持 hash 索引。

其实，用不用索引，最终都是优化器说了算。优化器是基于什么的优化器？基于 cost开销(CostBaseOptimizer)，它不是基于 规则(Rule-BasedOptimizer)，也不是基于语义。怎么样开销小就怎么来。另外，SQL 语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。

---

11.2.1 全值匹配

全值匹配可以充分的利用组合索引

系统中经常出现的 sql 语句如下，当没有建立索引时，possible_keys 和 key 都为 NULL

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

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

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

接下来我们建立索引

CREATE INDEX idx_age ON student(age);

CREATE INDEX idx_age_classid ON student(age,classId);

CREATE INDEX idx_age_classid_name ON student(age,classId,NAME);
1
2
3
4
5

建立索引后执行，发现使用到了联合索引，且耗时较短 0.00s

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';
+----+-------------+---------+------------+------+----------------------------------------------+----------------------+---------+-------------------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys                                | key                  | key_len | ref               | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------------------------------------+----------------------+---------+-------------------+------+----------+-------+
|  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  |
+----+-------------+---------+------------+------+----------------------------------------------+----------------------+---------+-------------------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)

mysql> SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';
Empty set, 1 warning (0.00 sec)
1
2
3
4
5
6
7
8
9
10

从上面三个索引中，优化器会根据查询的条件选择它认为比较合适的索引，比如上述 SQL 中有通过 age、classId、name 作为查询条件，而 idx_age_classid_name 这个联合索引正好是由这三个字段组成的，所以选择了该索引。

注意： 上面的索引可能不生效哦，在数据量较大的情况下，我们进行全值匹配 SELECT *，优化器可能经过计算发现，我们使用索引查询所有的数据后，还需要对查找到的数据进行回表操作，性能还不如全表扫描。

---

10.2.2 最佳左前缀法则

在 MySQL 建立联合索引时会遵守最佳左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

1. 下面的 SQL 将使用索引 idx_age

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

2. 下面的 sql 不会使用索引，因为我没有创建 classId 或者 name 的索引

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

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

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

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

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

答案是会的，因为优化器会执行优化，会调整查询条件的顺序，不过在开发过程中我们还是要保持良好的开发习惯。

思考：删去索引 idx_age_classid 和 idx_age，只保留 idx_age_classid_name，执行如下 sql，会不会使用索引？

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

答案是 会，但是只会用一部分。看看执行结果

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

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/菜鸟追梦旅行/article/detail/689647