登录/ 注册
 找回密码
 立即注册
关闭

CSDN热搜

程序园 精品问答 技术交流 资源下载
返回列表 发新帖
首页 › 业界区 › 安全 ›10-1 MySQL 索引优化与查询优化

10-1 MySQL 索引优化与查询优化

呼延含玉 7 天前
10-1 MySQL 索引优化与查询优化

@
目录

  • 10-1 MySQL 索引优化与查询优化
  • 1. 数据准备
  • 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

  • 3. 关联查询优化

    • 3.1 数据准备
    • 3.2采用左外连接
    • 3.3 采用内连接

  • 4.  join语句原理
  • 5. 子查询优化
  • 6. 排序优化
  • 最后：

这篇文章是我蹲在《尚硅谷》-康师傅博主家的 WiFi 上(不是)，连夜 Ctrl+C / V 俩的镇站神文。
这篇转载只是为了，跟大家分享好内容，没有任何商业用途。如果你喜欢这篇文章，请一定要去原作者 B站《尚硅谷-MySQL从菜鸟到大牛》看看，说不定还能发现更多宝藏内容呢！
1. 数据准备
  1. CREATE DATABASE dbtest4;
复制代码
学员表 插 50万 条， 班级表  插 1万 条。
步骤1：建表
  1. USE dbtest4;
  3. CREATE TABLE `class` (
  4. `id` INT(11) NOT NULL AUTO_INCREMENT,
  5. `className` VARCHAR(30) DEFAULT NULL,
  6. `address` VARCHAR(40) DEFAULT NULL,
  7. `monitor` INT NULL ,
  8. PRIMARY KEY (`id`)
  9. ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  12. CREATE TABLE `student` (
  13. `id` INT(11) NOT NULL AUTO_INCREMENT,
  14. `stuno` INT NOT NULL ,
  15. `name` VARCHAR(20) DEFAULT NULL,
  16. `age` INT(3) DEFAULT NULL,
  17. `classId` INT(11) DEFAULT NULL,
  18. PRIMARY KEY (`id`)
  19. #CONSTRAINT `fk_class_id` FOREIGN KEY (`classId`) REFERENCES `t_class` (`id`)
  20. ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
复制代码
步骤2：设置参数
命令开启：允许创建函数设置：
  1. set global log_bin_trust_function_creators=1;    # 不加global只是当前窗口有效。
复制代码
步骤3：创建函数
保证每条数据都不同。
  1. #随机产生字符串
  2. DELIMITER //
  3. CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
  4. BEGIN
  5. DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
  6. DECLARE return_str VARCHAR(255) DEFAULT '';
  7. DECLARE i INT DEFAULT 0;
  8. WHILE i < n DO
  9. SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
  10. SET i = i + 1;
  11. END WHILE;
  12. RETURN return_str;
  13. END //
  14. DELIMITER ;
  16. #假如要删除
  17. #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：创建存储过程
  1. #创建往stu表中插入数据的存储过程
  2. DELIMITER //
  3. CREATE PROCEDURE insert_stu(startt 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.   ((startt+i),rand_string(6),rand_num(1,50),rand_num(1,1000));
  11.   
  12.   UNTIL i = max_num
  13.   END REPEAT;
  14.   COMMIT; #提交事务
  15. END //
  16. DELIMITER ;
  18. #假如要删除
  19. # 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 (rand_string(8),rand_string(10),rand_num(1,100000));
  10.   UNTIL i = max_num
  11.   END REPEAT;
  12.   COMMIT;
  13. END //
  14. DELIMITER ;
  16. #假如要删除
  17. #drop PROCEDURE insert_class;
复制代码
步骤5：调用存储过程
class
  1. #执行存储过程，往class表添加1万条数据
  2. CALL insert_class(10000);
复制代码
1.png

stu
  1. #执行存储过程，往stu表添加50万条数据
  2. CALL insert_stu(100000,500000);
复制代码
2.png

步骤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 information_schema.STATISTICS WHERE table_schema = dbname AND table_name = tablename AND seq_in_index = 1 AND index_name <> 'PRIMARY';
  8.         DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 2; OPEN _cur;
  9.         FETCH _cur INTO _index;
  10.         WHILE _index <>'' DO
  11.               SET @str = CONCAT("drop index", _index,"on" , tablename);
  12.               PREPARE sql_str FROM @str ;
  13.               EXECUTE sql_str;
  14.               DEALLOCATE PREPARE sql_str;
  15.               SET _index='';
  16.               FETCH _cur INTO _index;
  17.         END WHILE;
  18.    CLOSE _cur;
  19. END //
  20. DELIMITER ;
复制代码
执行存储过程
  1. CALL proc_drop_index("dbname","tablename");
复制代码
2. 索引失效案例

2.1 索引字段：全值匹配最优

全值匹配最优：指的是我们查询的内容，过滤上都走了我们的索引，都和我们创建的索引完全匹配上了。
3.png
  1. # 1. 全值匹配我最爱
  2. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30;
  4. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND classid = 4;
  6. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND classid = 4 AND `name` = 'abcd';
  8. # 添加上索引
  9. CREATE INDEX idx_age ON student(age);
  10. CREATE INDEX idx_age_classid ON student(age,classId);
  11. CREATE INDEX idx_age_classid_name ON student(age,classId,`name`);
复制代码
2.2 索引字段：最佳左前缀法则

拓展：Alibaba《Java开发手册》
索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。
4.png

5.png
  1. SHOW INDEX FROM student;
  2. # 最佳左前缀法则
  3. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND `name` = 'abcd';
  5. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE classid = 1 AND `name` = 'abcd';
  7. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE classid = 1 AND `name` = 'abcd' AND age = 30;
复制代码
2.3 主键插入顺序

6.png

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

可这个数据页已经满了，再插进来咋办呢？我们需要把当前 页面分裂 成两个页面，把本页中的一些记录 移动到新创建的这个页中。页面分裂和记录移位意味着什么？意味着：性能损耗 ！所以如果我们想尽量 避免这样无谓的性能损耗，最好让插入的记录的 主键值依次递增 ，这样就不会发生这样的性能损耗了。 所以我们建议：让主键具有 AUTO_INCREMENT ，让存储引擎自己为表生成主键，而不是我们手动插入 ， 比如： person_info 表：
  1. CREATE TABLE person_info(
  2. id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  3. name VARCHAR(100) NOT NULL,
  4. birthday DATE NOT NULL,
  5. phone_number CHAR(11) NOT NULL,
  6. country varchar(100) NOT NULL,
  7. PRIMARY KEY (id),
  8. KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
  9. );   
复制代码
我们自定义的主键列 id 拥有AUTO_INCREMENT属性，在插入记录时存储引擎会自动为我们填入自增的 主键值。这样的主键占用空间小，顺序写入，减少页分裂。
2.4 索引字段进行了：计算、函数、类型转换(自动或手动)导致索引失效
  1. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';
复制代码
8.png
  1. # 计算、函数、类型转换(自动或手动)导致索引失效
  3. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
  5. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE '%abc';
复制代码
9.png

10.png

type为“ALL”，表示没有使用到索引，查询时间为 3.62 秒，查询效率较之前低很多。
student表的字段stuno上设置有索引
  1. CREATE INDEX idx_sno ON student(stuno);
复制代码
  1. # (索引字段)计算导致索引失效
  2. EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;
复制代码
11.png
  1. # student 表的字段 stuno上设置有索引CREATE INDEX idx_sno ON student(stuno);# (索引字段)计算导致索引失效
  2. EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;# 索引优化：对于这种索引简单运算的，我们可以优先将运算结果计算出来，再进行查询，EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno = 900000;
复制代码
12.png

2.5 索引字段类型转换导致索引失效
  1. # 未使用到索引
  2. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME=123;
  4. # 使用到索引
  5. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME='123';
复制代码
13.png

14.png

2.6 索引字段：使用了范围条件，右边的列索引失效
  1. EXPLAIN SELECT SQL_NO_CACHE * FROM student
  2. WHERE student.age = 30 AND student.classId > 20 AND student.name = 'abc';
复制代码
15.png

16.png

将范围查询条件放置语句最后：
  1. CREATE INDEX idx_age_classId_name ON student(age,NAME,classId);EXPLAIN SELECT SQL_NO_CACHE * FROM student
  2. WHERE student.age = 30 AND student.classId > 20 AND student.name = 'abc';
复制代码
17.png

应用开发中范围查询，例如：金额查询，日期查询等等一些范围查询，在创建索引时，需将这些范围查询的字段放到(索引字段的最后面)。
2.7 索引字段：不等于(!= 或者)索引失效

为 name 字段创建索引
  1. # 不等于 (!= 或者 <>) 索引失效
  2. CREATE INDEX idx_name ON student(`name`);
  4. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.`name` <> 'abc';
  6. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.`name` != 'abc';
复制代码
18.png

19.png

2.8 索引字段： is null可以使用索引，is not null无法使用索引


  • is null 可以使用索引：
  1. # is null 可以使用索引
  2. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.`name` IS NULL;
复制代码
20.png


  • is not null ：无法使用索引，索引失效
  1. # is not null ：无法使用索引，索引失效
  2. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.`name` IS NOT NULL;
复制代码
21.png

结论：最好在设计数据表的时候就将 字段设置为 NOT NULL 约束 ，比如可以将 INT 类型的字段，默认值设置为 0，将字符类型的默认值设置为空字符串"" 。
拓展：同理，在查询中使用 no like 也无法使用索引，导致全表扫描。
2.9 索引字段：使用了 like以通配符 % 开头索引失效
  1. # 索引字段当中使用了 like 以通配符 '%' 开头索引失效
  2. # 未使用到索引
  3. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.`name`LIKE '%ab';
复制代码
22.png
  1. # 索引字段当中使用了 like 以通配符 '%' 开头索引失效
  2. # 使用到索引
  3. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.`name`LIKE 'ab%';
复制代码
23.png

2.10 OR 前后存在非索引的列，索引失效

在 WHERE 子句中，如果在 OR 前的条件列进行了索引，而 OR后的条件没有进行索引，那么索引会失效，也就是说，OR 前后的两个条件中的列都时索引时，查询中才使用索引 。
因为 OR 的含义就是两个只要满足一个即可，因此只有一个条件进行了索引是没有意义的，只要有条件列没有索引，就会进行全表扫描 ，因此索引的条件列也会失效。
  1. # 创建 age 的索引
  2. CREATE INDEX idx_age ON student(age);
  4. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.`age` = 10 OR student.`name` = 'abc';
复制代码
24.png

2.11 数据库和表的字符集统一使用utf8mb4

统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不 同的 字符集 进行比较前需要进行转换会造成索引失效。
3. 关联查询优化

3.1 数据准备
  1. # 分类
  2. CREATE TABLE IF NOT EXISTS `type` (
  3. `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  4. `card` INT(10) UNSIGNED NOT NULL,
  5. PRIMARY KEY (`id`)
  6. );
  7. #图书
  8. CREATE TABLE IF NOT EXISTS `book` (
  9. `bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  10. `card` INT(10) UNSIGNED NOT NULL,
  11. PRIMARY KEY (`bookid`)
  12. );
  14. #向分类表中添加20条记录
  15. INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
  16. INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
  17. INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
  18. INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
  19. INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
  20. INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
  21. INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
  22. INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
  23. INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
  24. INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
  25. INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
  26. INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
  27. INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
  28. INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
  29. INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
  30. INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
  31. INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
  32. INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
  33. INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
  34. INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
  36. #向图书表中添加20条记录
  37. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  38. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  39. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  40. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  41. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  42. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  43. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  44. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  45. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  46. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  47. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  48. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  49. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  50. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  51. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  52. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  53. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  54. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  55. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  56. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
复制代码
3.2采用左外连接

下面开始 EXPLAIN 分析
  1. EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;
复制代码
25.png

添加索引优化
  1. # 添加索引
  2. ALTER TABLE book ADD INDEX Y ( card);   #【被驱动表】，可以避免全表扫描
  4. EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;
复制代码
26.png

可以看到第二行的 type 变为了 ref，rows 也变成了优化比较明显。这是由左连接特性决定的。LEFT JOIN 条件用于确定如何从右表搜索行，左边一定都有，所以 右边是我们的关键点,一定需要建立索引 。
  1. ALTER TABLE `type` ADD INDEX X (card); #【驱动表】，无法避免全表扫描
  2. EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;
复制代码
27.png

接着：
  1. DROP INDEX Y ON book;
  2. EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;
复制代码
28.png

3.3 采用内连接
  1. drop index X on type;
  2. drop index Y on book;（如果已经删除了可以不用再执行该操作）
复制代码
换成 inner join（MySQL自动选择驱动表）
  1. EXPLAIN SELECT SQL_NO_CACHE * FROM TYPE INNER JOIN book ON type.card=book.card;
复制代码

添加索引优化
  1. # 添加索引优化
  2. ALTER TABLE book ADD INDEX Y ( card);
  3. EXPLAIN SELECT SQL_NO_CACHE * FROM TYPE INNER JOIN book ON type.card=book.card;
复制代码
30.png
  1. ALTER TABLE TYPE ADD INDEX X (card);
  2. EXPLAIN SELECT SQL_NO_CACHE * FROM TYPE INNER JOIN book ON type.card=book.card;
复制代码
31.png

接着：
  1. DROP INDEX X ON `type`;
  2. EXPLAIN SELECT SQL_NO_CACHE * FROM TYPE INNER JOIN book ON type.card=book.card;
复制代码
32.png

对于内连接来说，如果表的连接条件中只有一个字段有索引，则有索引的字段所在的表会被作为驱动表。
接着
  1. #向图书表中添加20条记录
  2. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  3. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  4. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  5. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  6. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  7. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  8. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  9. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  10. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  11. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  12. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  13. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  14. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  15. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  16. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  17. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  18. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  19. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  20. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  21. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
复制代码
  1. DROP INDEX `Y` ON book; # 删除索引
  2. SHOW INDEX FROM book;
  4. EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON `type`.card = book.card;
复制代码
33.png

对于内连接来说：在两个表的连接条件都存在索引(都不存在索引的)的情况下，会选择小表作为驱动表，"小表驱动大表"
4.  join语句原理

https://github.com/codinglin/StudyNotes/blob/main/MySQL高级篇/MySQL索引及调优篇.md#3-关联查询优化
34.png

5. 子查询优化

MySQL从4.1版本开始支持子查询，使用子查询可以进行SELECT语句的嵌套查询，即一个SELECT查询的结 果作为另一个SELECT语句的条件。 子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作 。
子查询是 MySQL 的一项重要的功能，可以帮助我们通过一个 SQL 语句实现比较复杂的查询。但是，子 查询的执行效率不高。原因：

  • 执行子查询时，MySQL需要为内层查询语句的查询结果 建立一个临时表 ，然后外层查询语句从临时表 中查询记录。查询完毕后，再 撤销这些临时表 。这样会消耗过多的CPU和IO资源，产生大量的慢查询。
  • 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都 不会存在索引 ，所以查询性能会 受到一定的影响。
  • 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。
在MySQL中，可以使用连接（JOIN）查询来替代子查询。连接查询 不需要建立临时表 ，其 速度比子查询 要快 ，如果查询中使用索引的话，性能就会更好。
举例1：查询学生表中是班长的学生信息
  1. # 创建班级表中班长的索引
  2. CREATE INDEX idx_monitor ON class(monitor);
  4. EXPLAIN SELECT * FROM student stu1
  5. WHERE stu1.`stuno` IN (
  6. SELECT monitor
  7. FROM class c
  8. WHERE monitor IS NOT NULL
  9. )
复制代码

  • 推荐使用多表查询
  1. EXPLAIN SELECT stu1.* FROM student stu1 JOIN class c
  2. ON stu1.`stuno` = c.`monitor`
  3. WHERE c.`monitor` is NOT NULL;
复制代码
35.png

举例2：取所有不为班长的同学

  • 不推荐
  1. EXPLAIN SELECT SQL_NO_CACHE a.*
  2. FROM student a
  3. WHERE a.stuno NOT IN (
  4.         SELECT monitor FROM class b
  5.     WHERE monitor IS NOT NULL
  6. );
复制代码
36.png


  • 推荐：
  1. EXPLAIN SELECT SQL_NO_CACHE a.*
  2. FROM student a LEFT OUTER JOIN class b
  3. ON a.stuno = b.monitor
  4. WHERE b.monitor IS NULL;
复制代码
37.png

结论：尽量不要使用 NOT IN或者 NOT EXISTS，用LEFT JOIN xxx ON xx WHERE xx IS NULL替代
6. 排序优化

问题：在 WHERE 条件字段上加索引，但是为什么在 ORDER BY 字段上还要加索引呢？
回答：
在MySQL中，支持两种排序方式，分别是 FileSort 和 Index 排序。

  • Index 排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高。
  • FileSort 排序则一般在 内存中 进行排序，占用CPU较多。如果待排结果较大，会产生临时文件 I/O 到磁盘进行排序的情况，效率较低。
优化建议：

  • SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中 避免全表扫描 ，在 ORDER BY 子句 避免使用 FileSort 排序 。当然，某些情况下全表扫描，或者 FileSort 排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。
  • 尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列； 如果不同就使用联合索引。
  • 无法使用 Index 时，需要对 FileSort 方式进行调优。
测试：
删除student表和class表中已创建的索引。
  1. # 方式1
  2. DROP INDEX idx_monitor ON class;
  3. DROP INDEX idx_cid ON student;
  4. DROP INDEX idx_age ON student;
  5. DROP INDEX idx_name ON student;
  6. DROP INDEX idx_age_name_classId ON student;
  7. DROP INDEX idx_age_classId_name ON student;
复制代码
以下是否能使用到索引，能否去掉using filesort
  1. EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid;
  3. EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid LIMIT 10;
复制代码
38.png

测试2： order by 时 不使用 limit ，索引失效
  1. # 过程二: order by 时 不使用 limit ，索引失效
  2. # 创建索引
  3. CREATE INDEX idx_age_classid_name ON student (age,classid,`name`);
  4. # 不使用 limit 索引失效
  5. EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid ;
复制代码
39.png
  1. # order by  使用 limit 索引生效
  2. EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid LIMIT 100;
复制代码
40.png

测试三：order by 排序当中字段，不满足索引最左匹配原则，顺序错误，索引失效
  1. # 测试三：order by 排序当中，顺序错误，索引失效
  3. EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY classid,`name` LIMIT 100;
复制代码
41.png

测试四： order by 时，规则不一致(索引排序不一致(反序可以走索引，mysql 8.0 支持)，)，索引失效
  1. # 测试： order by 时，规则不一致(索引排序不一致(反序可以走索引，mysql 8.0 支持)，)，索引失效
  3. EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age DESC, classid DESC LIMIT 100;
复制代码
42.png
  1. # 测试： order by 时，规则不一致(索引排序不一致(反序可以走索引，mysql 8.0 支持)，)，索引失效
  3. EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age ASC, classid DESC LIMIT 100;
复制代码
43.png

最后：

“在这个最后的篇章中，我要表达我对每一位读者的感激之情。你们的关注和回复是我创作的动力源泉，我从你们身上吸取了无尽的灵感与勇气。我会将你们的鼓励留在心底，继续在其他的领域奋斗。感谢你们，我们总会在某个时刻再次相遇。”
44.gif


来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

使用道具 举报
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册
发帖
呼延含玉
7 天前

0

粉丝关注

24

主题发布

板块介绍填写区域，请于后台编辑
微信扫一扫