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;
步调2：设置参数
下令开启：允许创建函数设置
set global log_bin_trust_function_creators=1;
# 不加global只是当前窗口有效。
步调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;
随机产生班级编号
#用于随机产生多少到多少的编号
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;
步调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;
创建往 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;
步调5：调用存储过程
往 class 表添加 1 万条数据
#执行存储过程，往class表添加1万条数据
CALL insert_class(10000);
往 stu 表添加 50 万条数据，这个时间会轻微有点长
#执行存储过程，往stu表添加80万条数据
CALL insert_stu(100000,800000);
查询下数据是否插入成功
SELECT COUNT(*) FROM class;
SELECT COUNT(*) FROM student;
步调6：删除某表上的索引
创建删除索引存储过程。这是为了方便我们的学习，由于我们在演示某个索引的效果时，可能必要删除其它索引，如果必要一个个手工删除，就太费劲了。
DELIMITER //
CREATEPROCEDURE `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 FORSELECTindex_nameFROM
information_schema.STATISTICSWHERE table_schema=dbname AND table_name=tablename AND
seq_in_index=1 ANDindex_name <>'PRIMARY' ;
#每个游标必须使用不同的declare continue handler for not found set done=1来控制游标的结束
DECLARECONTINUE 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 ;
实验存储过程
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
# 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';
此时实验 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)
接下来我们创建索引
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);
创建索引后实验，发现利用到了团结索引，且耗时较短 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)
从上面三个索引中，优化器会根据查询的条件选择它认为比较合适的索引，比如上述 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';
2. 下面的 sql 不会利用索引，由于我没有创建 classId 大概 name 的索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classId=4 AND student.name='abcd';
3.下面的 sql 查询就是遵守这一原则的精确打开方式
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age = 30 AND student.classId=4 AND student.name='abcd';
思考：下面sql会不会利用索引呢？
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.classId=4 AND student.age = 30 AND student.name='abcd';
答案是会的，由于优化器会实验优化，会调整查询条件的顺序，不过在开发过程中我们还是要保持良好的开发习惯。
思考：删去索引 idx_age_classid 和 idx_age，只保留 idx_age_classid_name，实验如下 sql，会不会利用索引？
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHEREstudent.age = 30 AND student.name='abcd';
答案是 会，但是只会用一部分。看看实验效果
https://img-blog.csdnimg.cn/img_convert/4a53fd8f2b51d1dce6a64c112aa1f338.png
利用了 idx_age_classid_name，但是 key_len 是 5，也就是说只利用了 age 部分的排序，由于 age 是 int 范例，4 个字节加上 null 值列表一共 5 个字节。由于 B+树 是先按照 age 排序，再按照 classid 排序，最后按照 name 排序，因此不能跳过 classId 的排序直接就利用 name 的排序。
页:
[1]