MySQL高级篇知识点——索引优化与查询优化

目录

• 1.数据准备
• • 1.1.建库建表
  • 1.2.创建相关函数
  • 1.3.创建存储过程
  • 1.4.调用存储过程
  • 1.5.删除某表上的索引
• 2.索引失效案例
• • 2.1.全值匹配
  • 2.2.最佳左前缀匹配原则
  • 2.3.主键插入顺序
  • 2.4.计算、函数、类型转换（自动或手动）导致索引失效
  • 2.5 类型转换导致索引失效
  • 2.6.范围条件右边的列索引失效
  • 2.7.不等于（!= 或者<>）索引失效
  • 2.8.is null可以使用索引，is not null无法使用索引
  • 2.9.like 以通配符 % 开头索引失效
  • 2.10.OR 前后存在非索引的列，索引失效
  • 2.11.数据库和表的字符集统一使用utf8mb4
  • 2.12.练习
• 3.关联查询优化
• • 3.1.数据准备
  • 3.2.采用左外连接
  • 3.3.采用内连接
  • 3.4.join 语句原理
  • • 3.4.1.驱动表和驱动表
    • 3.4.2.Simple Nested-Loop Join（简单嵌套循环连接）
    • 3.4.3.lndex Nested-Loop Join（索引嵌套循环连接）
    • 3.4.4.Block Nested-Loop Join（块嵌套循环连接）
    • 3.4.5.join 小结
    • 3.4.6.Hash Join
• 4.子查询优化
• 5.排序优化
• • 5.1.问题引入
  • 5.2.优化建议
  • 5.3.测试
  • 5.4.案例实战
  • 5.5.filesort 算法：双路排序和单路排序
  • • 5.5.1.双路排序 （慢）
    • 5.5.2.单路排序 （快）
    • 5.5.3.结论及引申出的问题
    • 5.5.4.优化策略
• 6.GROUP BY 优化
• 7.分页查询优化
• 8.优先考虑覆盖索引
• • 8.1.什么是覆盖索引？
  • 8.2.覆盖索引的优缺点
  • 8.3.案例
• 9.如何给字符串添加索引
• • 9.1.前缀索引
  • 9.2.前缀索引对覆盖索引的影响
• 10.索引下推
• • 10.1.什么是索引下推？
  • 10.2.索引下推的开启与关闭
  • 10.3.ICP 的使用条件
  • 10.4.案例
  • 10.5.开启/关闭索引下推的性能对比
  • • 10.5.1.准备数据
    • 10.5.2.性能对比
• 11.普通索引 vs 唯一索引
• • 11.1.查询过程
  • 11.2.更新过程
  • 11.3. change buffer的使用场景
• 12.其它查询优化策略
• • 12.1.EXISTS 和 IN 的区分
  • 12.2.COUNT(*) 与 COUNT(具体字段)效率
  • 12.3.关于 SELECT(*)
  • 12.4.LIMIT 1 对优化的影响
  • 12.5.多使用 COMMIT
• 13.淘宝数据库的主键是如何设计的？
• • 13.1 自增 ID 的问题
  • 13.2.业务字段做主键
  • • 13.2.1.选择卡号 (cardno)
    • 13.2.2.选择会员电话或身份证号
  • 13.3.淘宝的主键设计
  • 13.4.推荐的主键设计
  • • 13.4.1.非核心业务
    • 13.4.2.核心业务

本文笔记整理来自尚硅谷视频https://www.bilibili.com/video/BV1iq4y1u7vj?p=141，相关资料可在视频评论区进行获取。

（1）都有哪些维度可以进行数据库调优？简言之：
① 索引失效、没有充分利用到索引——索引建立。
② 关联查询太多 JOIN（设计缺陷或不得已的需求）——SQL 优化。
③ 服务器调优及各个参数设置（缓冲、线程数等）——调整 my.cnf。
④ 数据过多——分库分表。

（2）关于数据库调优的知识点非常分散。不同的 DBMS，不同的公司，不同的职位，不同的项目遇到的问题都不尽相同。这里分为三个章节进行细致讲解。虽然 SQL 查询优化的技术有很多，但是大方向上完全可以分成物理查询优化和逻辑查询优化两大块。
① 物理查询优化是通过索引和表连接方式等技术来进行优化，这里重点需要掌握索引的使用。
② 逻辑查询优化就是通过 SQL 等价变换提升查询效率，直白一点就是说，换一种查询写法执行效率可能更高。

1.数据准备

学员表插入 50万条数据， 班级表插入 1万条数据。

1.1.建库建表

# 建库
CREATE DATABASE atguigudb2;

USE atguigudb2;

# 建表
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.创建相关函数

# 命令开启，允许创建函数设置
SET GLOBAL log_bin_trust_function_creators=1; 

# 随机产生字符串
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 ;

# 用于随机产生多少到多少的编号
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 ;

1.3.创建存储过程

# 创建往 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 ;

# 执行存储过程，往 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 ;

1.4.调用存储过程

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

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

# 查看数据是否插入成功
SELECT COUNT(*) FROM class;

SELECT COUNT(*) FROM student;

1.5.删除某表上的索引

下面定义了一个删除某库某表上的索引的存储过程，其目的是方便后面的一些操作。

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 ;

2.索引失效案例

（1）MySQL 中提高性能的一个最有效的方式是对数据表设计合理的索引。索引提供了高效访问数据的方法，并且加快查询的速度，因此索引对查询的速度有着至关重要的影响。
① 使用索引可以快速地定位表中的某条记录，从而提高数据库查询的速度，提高数据库的性能。
② 如果查询时没有使用索引，查询语句就会扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢。

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

2.1.全值匹配

（1）系统种经常会出现类似如下的 SQL 语句：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=746;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=746 AND NAME = 'BJPYyu';

在建立索引之前执行（主要关注消耗的时间，为 0.15s）：

（2）建立索引的 3 条语句如下：

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);

① 先建立索引 idx_age，然后执行上述语句，根据结果可以看出，索引 idx_age 已被使用，且消耗的时间为 0.03s，查询速度更快了。

② 再建立索引 idx_age_classid，同样执行上述语句，根据结果可以看出，索引 idx_age_classid 已被使用，而之前建立的索引 idx_age 未被使用，并且消耗的时间更短了。其原因在于上述 SQL 语句中的查询条件与索引列中的顺序更加匹配，简单来说：
1）索引 idx_age 可以匹配 WHERE age=30 AND classId=4 AND NAME = ‘abcd’ 中的 age；
2）而索引 idx_age_classid 则可以匹配 age 和 classId，因此使用该索引可以实现更快的查找。

③ 最后建立索引 idx_age_classid_name，同样执行上述语句，根据结果可知，索引 idx_age_classid_name 已被使用，而之前建立的索引 idx_age 和 idx_age_classid 未被使用，即失效了。同理，可以分析原因：索引 idx_age_classid_name 中的列 age、classId、NAME 与 WHERE age=30 AND classId=4 AND NAME = ‘abcd’ 完全匹配（包括字段名和顺序），这也称为全值匹配，这种情况下我们比较希望见到的。

2.2.最佳左前缀匹配原则

（1）MySQL可以为多个字段创建索引，一个索引可以包括16个字段。在 MySQL 建立联合索引时会遵守最佳左前缀匹配原则：
① 最左优先，在检索数据时从联合索引的最左边开始匹配。
② 对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。
③ 如果查询条件中没有使用这些字段中第 1 个字段时，多列（或联合）索引将会失效。

（2）举例
① 目前 student 表上有 4 个索引。

SHOW INDEX FROM student;

② 下面 SQL 语句中的查询条件涉及的字段依次为：age、name。

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

根据上面的分析可知，索引 idx_age 被使用了，因为 (age, name) 中的 age（从左往右看）正好可以和 idx_age 中的索引列 age 匹配，因此可以使用该索引。

③ 下面 SQL 语句中的查询条件涉及的字段依次为：classid、name。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE classid=1 AND name = 'abcd';

根据上面的分析可知，该语句上没有索引被使用，因为根据最佳左前缀匹配原则，(classid、name) 无法与已有的任何索引进行匹配（即使优化器对字段顺序进行调整之后）。

④ 下面 SQL 语句中的查询条件涉及的字段依次为：classid、age、name。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE classid=4 AND age=30 AND name = 'abcd'; 

根据上面的分析可知，索引 idx_age_classid_name 被使用了，尽管第一眼看起来，没有一个索引的索引列是以 classid 开始，(classid, age, name) 并不能与已有的索引匹配，但是 MySQL 中的优化器会对字段顺序进行一定的调整，看能否匹配上已有的索引，而如果将原本的 (classid, age, name) 调整为 (age, classid, name)，那么此时就可以匹配上索引 idx_age_classid_name！

④ 先删除索引 idx_age 和 idx_age_classid：

DROP INDEX idx_age ON student;
DROP INDEX idx_age_classid ON student;

下面 SQL 语句中的查询条件涉及的字段依次为：age、name。

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

根据上面的分析可知，索引 idx_age_classid_name 被使用了，但是 key_len = 5，即实际使用到的索引长度只有 5 个字节，涉及的所索引列为 age，索引并未被充分利用（有关 key_len 的介绍可以参考MySQL高级篇知识点——性能分析工具的使用这篇文章的 6.4.7节）。

其原因在于索引 idx_age_classid_name 的索引列依次为 (age, classid, name)，而上述查询条件涉及的字段依次为 (age、name)，根据最佳左前缀匹配原则，该索引的索引列 classid 被跳过后，其自身以及后面的字段都无法被使用！

2.3.主键插入顺序

（1）对于一个使用 InnoDB 存储引擎的表来说，在我们没有显示的创建索引时，表中的数据实际上都是存储在 聚簇索引 的叶子节点的。而记录又存储在数据页中的，数据页和记录又是按照记录 主键值从小到大 的顺序进行排序，所以如果我们 插入 的记录的 主键值是依次增大 的话，那我们每插满一个数据页就换到下一个数据页继续插，而如果我们插入的主键值忽小忽大的话，则可能会造成 页面分裂 和 记录移位。

（2）假设某个数据页存储的记录已经满了，它存储的主键值在 1~100 之间：

如果此时再插入一条主键值为 9 的记录，那它插入的位置就如下图：