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

篮之新喜  金牌会员 | 2024-5-23 23:01:14 | 显示全部楼层 | 阅读模式
打印 上一主题 下一主题
楼主

主题 871|帖子 871|积分 2623

C-10.索引优化与查询优化

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

  • 索引失效，没有充实利用到索引 -- 索引建立
  • 关联查询太多JOIN(设计缺陷或不得已的需求) -- SQL优化
  • 服务器调优及各个参数设置(缓冲，线程数等) -- 调解my.cnf
  • 数据过多 -- 分库分表
关于数据库调优的知识点非常分散。差异的DBMS，差异的公司，差异的职位，差异的项目碰到的问题都不尽相同。
虽然SQL查询优化的技能有很多，但是大方向上完全可以分成物理查询优化和逻辑查询优化两大块。

  • 物理查询优化是通过索引和表连接方式等技能来进行优化，这里重点必要掌握索引的利用。
  • 逻辑查询优化就是通过SQL等价变更提示查询服从，直白一点就是说，换一种查询写法执行服从可能更高。
1.数据准备

学院表50万条，班级表1万条。
步调1，建表。
  1. CREATE TABLE `class` (
  2. `id` INT(11) NOT NULL AUTO_INCREMENT,
  3. `className` VARCHAR(30) DEFAULT NULL,
  4. `address` VARCHAR(40) DEFAULT NULL,
  5. `monitor` INT NULL ,
  6. PRIMARY KEY (`id`)
  7. ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  9. CREATE TABLE `student` (
  10. `id` INT(11) NOT NULL AUTO_INCREMENT,
  11. `stuno` INT NOT NULL ,
  12. `name` VARCHAR(20) DEFAULT NULL,
  13. `age` INT(3) DEFAULT NULL,
  14. `classId` INT(11) DEFAULT NULL,
  15. PRIMARY KEY (`id`)
  16. #CONSTRAINT `fk_class_id` FOREIGN KEY (`classId`) REFERENCES `t_class` (`id`)
  17. ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
复制代码
步调2，设置参数。
  1. set global log_bin_trust_function_creators=1;#命令开启：允许创建函数设置
复制代码
步调3，创建函数。
  1. #随机产生字符串
  2. DELIMITER //
  4. CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
  5.     BEGIN
  6.         DECLARE chars_str VARCHAR(100) DEFAULT
  7.         'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
  8.         DECLARE return_str VARCHAR(255) DEFAULT '';
  9.         DECLARE i INT DEFAULT 0;
  10.         WHILE i < n DO
  11.         SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
  12.         SET i = i + 1;
  13.         END WHILE;
  14.         RETURN return_str;
  15.     END //
  16.    
  17. DELIMITER ;
  19. #用于随机产生多少到多少的编号 随机产生班级编号
  20. DELIMITER //
  21. CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
  22.     BEGIN
  23.         DECLARE i INT DEFAULT 0;
  24.         SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ;
  25.         RETURN i;
  26.     END //
  27. DELIMITER ;
复制代码
步调4：创建存储过程
  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;
  20. #执行存储过程，往class表添加随机数据
  21. DELIMITER //
  22. CREATE PROCEDURE `insert_class`( max_num INT )
  23.     BEGIN
  24.         DECLARE i INT DEFAULT 0;
  25.         SET autocommit = 0;
  26.         REPEAT
  27.         SET i = i + 1;
  28.         INSERT INTO class ( classname,address,monitor ) VALUES
  29.         (rand_string(8),rand_string(10),rand_num(1,100000));
  30.         UNTIL i = max_num
  31.         END REPEAT;
  32.         COMMIT;
  33.     END //
  34. DELIMITER ;
  35. #假如要删除
  36. #drop PROCEDURE insert_class;
复制代码
步调5：调用存储过程
  1. #执行存储过程，往class表添加1万条数据
  2. CALL insert_class(10000);
  4. #执行存储过程，往stu表添加50万条数据
  5. CALL insert_stu(100000,500000);
复制代码
步调6：创建删除某表上索引存储过程
  1. #创建存储过程
  2. DELIMITER //
  3. CREATE PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))
  4. BEGIN
  5.     DECLARE done INT DEFAULT 0;
  6.     DECLARE ct INT DEFAULT 0;
  7.     DECLARE _index VARCHAR(200) DEFAULT '';
  8.     DECLARE _cur CURSOR FOR SELECT index_name FROM
  9.     information_schema.STATISTICS WHERE table_schema=dbname AND table_name=tablename AND
  10.     seq_in_index=1 AND index_name <>'PRIMARY' ;
  11.     #每个游标必须使用不同的declare continue handler for not found set done=1来控制游标的结束
  12.     DECLARE CONTINUE HANDLER FOR NOT FOUND set done=2 ;
  13.     #若没有数据返回,程序继续,并将变量done设为2
  14.     OPEN _cur;
  15.     FETCH _cur INTO _index;
  16.     WHILE _index<>'' DO
  17.         SET @str = CONCAT("drop index " , _index , " on " , tablename );
  18.         PREPARE sql_str FROM @str ;
  19.         EXECUTE sql_str;
  20.         DEALLOCATE PREPARE sql_str;
  21.         SET _index='';
  22.         FETCH _cur INTO _index;
  23.     END WHILE;
  24.     CLOSE _cur;
  25.    
  26. END //
  27. DELIMITER ;
  29. #执行存储过程
  30. CALL proc_drop_index("dbname","tablename");
复制代码
2.索引失效案例

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

  • 利用索引可以快速定位表中的某条数据，从而提高数据库查询的数据，提高数据库的性能。
  • 如果查询没有利用索引，查询语句就会扫描表中的所有记录。在数据量大的情况下，这样查询的速率会很慢。
大多数情况下（默认）采用B+树来构建索引。只是空间列类型的索引利用R-树，并且MEMORY表还支持hash索引。
其实，用不用索引，最终都是优化器说了算。优化器是基于什么的优化器？基于cost开销(CostBaseOptimizer)，它不是基于规则(Rule-BaseOptimizer)，也不是基于语义。怎样开销小，就怎么来。另外，SQL语句是否利用索引，跟数据库版本、数据量、数据选择度都有关系。
2.1 全值匹配
  1. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;
  2. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4;
  3. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';
复制代码
建立索引前执行
  1. mysql> SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';
  2. Empty set, 1 warning (0.09 sec)
复制代码
建立索引
  1. #创建索引
  2. CREATE INDEX idx_age ON student(age);
  4. CREATE INDEX idx_age_classid ON student(age,classId);
  6. CREATE INDEX idx_age_classid_name ON student(age,classId,NAME);
复制代码
建立索引后执行
  1. mysql> SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';
  2. Empty set, 1 warning (0.00 sec)
复制代码
2.2 最佳左前缀规则

在MySQL建立联合索引时，会遵守最佳左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。
  1. #1.只能使用上 idx_age索引
  2. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name = 'abcd' ;
  4. #2.不能使用上索引
  5. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classid=1 AND student.name = 'abcd';
  7. #3.使用idx_age_classid_name索引
  8. EXPLAIN SELECT SQL_NO_CACHE * FROM student
  9. WHERE classid=4 AND student.age=30 AND student.name = 'abcd';
复制代码
原因分析：
由于在构建联合索引对应的B+树时，每个页(包罗叶子和非叶子)中，的一条数据存储的列的值的顺序，是定义索引的顺序，所以只有where子句中，存在联合索引的最左侧的列，才可能利用的上该联合索引。
对于上述第一条SQL的分析，为什么只能用idx_age索引，也是同理，对于idx_age_classid_name索引，age列和name列中有个classid列，如果只出现了age列，和name列，就会导致中间断了。
也就是说，联合索引(a,b,c)，最佳顺序就是 a = ? and b = ? and c = ?。这样是最完美的利用联合索引的方式，但是只有a，c的话，就会导致无法利用该索引。由于利用a = ? 确定几条数据后，必须利用b列去过滤数据了，但是此时where子句中没有b的条件，所以就无法利用此索引树，去确定满足后面条件的数据了，非要利用此索引的话，可能会导致回表次数过多，执行服从低。
对于第三条SQL，能利用上idx_age_classid_name索引的分析，由于有查询优化器的存在，虽然在where子句中写的条件的顺序是，先classid 后age后name，但是查询优化器，会做一个操作，尝试将条件子句的顺序，转换成联合索引定义的顺序，从而完美的利用的联合索引。
  1. #删除这两个索引
  2. DROP INDEX idx_age ON student;
  3. DROP INDEX idx_age_classid ON student;
  5. #再次执行该语句
  6. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name = 'abcd' ;
  7. #这里不展示具体执行结果，直接说结论
  8. #使用上了idx_age_classid_name索引，但是key_len的长度是5
  9. #而age列是int类型，且可以为null 4 + 1刚好是5B
  10. #也就是说，该条语句只使用了联合索引的age列进行过滤，然后根据对应的id值，进行回表，在use where进行过滤。符合上面的原因分析
复制代码
结论：MySQL可以为一张表的多个字段创建索引，一个索引可以包罗16个字段。对于多列索引，过滤条件要利用索引必须按照索引的建立顺序，依次满足，一但跳过某个字段，索引后面的字段都无法利用。如果查询条件中没有利用这些字段中第1字段时，联合索引将失效。也即不会被利用。
扩展Alibaba《Java开发手册》
索引文件具有B-Tree的最左匹配特性，如果左边的之值未确定，那么无法利用该索引。
2.3 主键插入顺序

对于一个利用InnoDB存储引擎的表来说，在我们没有显式的创建索引时，表中的数据实际上都是存储在聚簇索引的叶子节点的。而记录又是存储在数据页中的，数据页和记录又是按照记录主键值从小到大的顺序进行排序，所以如果我们插入的记录的主键值是依次增大的话，那我们每插满一个数据页就换到下一个数据页继续插，而如果我们插入的主键值忽大忽小的话，就比较麻烦了，假设某个数据页存储的记录已经满了，它存储的主键值在1~100之间:

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

可这个数据页已经满了，再插进来咋办呢？我们必要把当前页面分裂成两个页面，把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着什么？意味着： 性能损耗 ！所以如果我们想只管避免这样无谓的性能损耗，最好让插入的记录的主键值依次递增，这样就不会发生这样的性能损耗了。所以我们建议：让主键具有AUTO_INCREMENT，让存储引擎自己为表天生主键，而不是我们手动插入 ，
我们自定义的主键列 id 拥有AUTO_INCREMENT属性，在插入记录时存储引擎会自动为我们填入自增的主键值。这样的主键占用空间小，顺序写入，减少页分裂。
2.4 计算、函数导致索引失效
  1. CREATE INDEX idx_name ON student(`name`);
  3. #此语句比下一条要好！（能够使用上索引）
  4. #执行结果使用上索引 idx_name
  5. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
  7. #未使用索引 type列的值是ALL 全表扫描
  8. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';
  9. #原因，在使用完函数后，MySQL只能根据函数的结果，去和给定的值，对比。所以无法使用上索引
复制代码
  1. CREATE INDEX idx_sno ON student(stuno);
  3. #未使用索引 type列的值是ALL 全表扫描
  4. EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;
  5. #原因，在进行计算后，MySQL只能根据计算的结果，去和给定的值，对比。所以无法使用上索引
  7. #执行结果使用上索引 idx_sno
  8. EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno = 900000;
复制代码
2.5 类型转换导致索引失效
  1. #无法使用idx_name索引 type列是ALL 全表扫描
  2. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME = 123;
  3. #相当于，MySQL，对于类型不匹配的，会尝试使用隐式的函数转换成目标类型，这样就会导致无法使用索引。
  5. #使用idx_name索引
  6. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME = '123';
复制代码
2.6 范围条件右边的列索引失效

右边，是指在联合索引的列的右边的列，而不是where子句中的右边的列无法被利用。
  1. SHOW INDEX FROM student;
  3. #根据数据库和表名删除除主键外的索引
  4. CALL proc_drop_index('atguigudb3','student');
  5. #创建联合索引
  6. CREATE INDEX idx_age_classId_name ON student(age,classId,`name`);
  8. #会使用上idx_age_classId_name 但是key_len的长度是10
  9. #age和classId都是int类型，都可以为null 所以是 4 + 1 + 4 + 1=10B 也就是在联合索引中，未使用上name列
  11. #1.
  12. EXPLAIN SELECT SQL_NO_CACHE * FROM student
  13. WHERE student.age=30 AND student.classId > 20 AND student.name = 'abc' ;
  15. #创建一个age,name,classId的索引
  16. CREATE INDEX idx_age_name_cid ON student(age,`name`,classId);
  18. #可以使用idx_age_name_cid索引
  19. EXPLAIN SELECT SQL_NO_CACHE * FROM student
  20. WHERE student.age=30 AND student.name = 'abc' AND student.classId>20;
  21. #此时执行第一条语句，也会使用idx_age_name_cid，因为mysql会自动改变条件条件子句的顺序
复制代码
应用开发中范围查询，例如：金额查询，日期查询往往都是范围查询。应该把查询条件放在where语句末了。(创建的联合索引中，务必把范围涉及到的字段写在末了)
2.7 不等于(!= 或 )索引失效
  1. CREATE INDEX idx_name ON student(NAME);
  3. #不能使用idx_name索引 type列的值是ALL
  4. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name <> 'abc' ;
  6. #不能使用idx_name索引 type列的值是ALL
  7. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name != 'abc' ;
复制代码
2.8 is null可以利用索引，is not null无法利用索引
  1. #可以使用idx_age_classId_name索引
  2. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;
  4. #无法使用索引 type列的值是ALL
  5. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;
复制代码
结论:最好在设计数据表的时候就将字段设置为 NOT NULL约束，比如你可以将INT类型的字段默认值设置为0。将字符类型的默认值设置为空字符串('‘)。
拓展:同理，在查询中利用not like 也无法利用索引，导致全表扫描。
2.9 like以通配符%开头索引失效

在利用LIKE关键字进行查询的sql中，如果匹配字符串"%"开头的，索引就会失效。只有"%"不在第一个位置，索引才会起作用。
  1. #使用idx_name索引
  2. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE 'ab%';
  4. #无法使用idx_name索引
  5. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE '%ab%';
复制代码
拓展：Alibaba《Java开发手册》
【强制】页面搜索严禁左模糊或者全模糊，如果必要请走搜索引擎来办理。
2.10 OR前后存在非索引的列，索引失效

在WHERE子句中，如果在OR前的条件列进行了索引，而在OR后的条件列没有进行索引，那么索引会失效。也就是说，OR前后的两个条件中的列都是索引时，查询中才利用索引。
由于OR的含义就是两个只要满足一个即可，因此只有一个条件列进行了索引是没有意义的，只要有条件列没有进行索引，就会进行全表扫描，因此索引的条件列也会失效。
查询语句利用OR关键字的情况:
  1. CALL proc_drop_index('atguigudb3','student');
  3. CREATE INDEX idx_age ON student(age);
  5. #type列的值是ALL
  6. EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;
  8. CREATE INDEX idx_cid ON student(classid);
  9. #建立该索引后，执行，type 值是index_merge索引合并
复制代码
2.11 数据库和表的字符集统一利用utf8mb4

统一利用utf8mb4( 5.5.3版本以上支持)兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。差异的字符集进行比较前必要进行转换会造成索引失效。
2.12 建议

一般性建议:

  • 对子单列索引，只管选择针对当前query过滤性更好的索引。
  • 在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
  • 在选择组合索引的时候，只管选择能够包罗当前query中的where子句中更多字段的索引。
  • 在选择组合索引的时候，如果某个字段可能出现范围查询时，只管把这个字段放在索引次序的末了面。
总之，书写SQL语句时，只管避免造成索引失效的情况。
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. );
  8. #图书
  9. CREATE TABLE IF NOT EXISTS `book` (
  10. `bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  11. `card` INT(10) UNSIGNED NOT NULL,
  12. PRIMARY KEY (`bookid`)
  13. );
  15. #向分类表中添加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)));
  35. INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
  37. #向图书表中添加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)));
  57. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
复制代码
3.2 左外连接
  1. mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card
复制代码

指向结果看，type都是ALL
添加索引优化
  1. CREATE INDEX idx_book_card ON book(card); #被驱动表建立索引，避免全表扫描
复制代码
  1. mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;
复制代码

可以看到第二行的 type 变为了 ref，rows 也酿成了1优化比较显着。这是由左连接特性决定的。LEFT JOIN条件用于确定如何从右表搜索行，左边一定都有，所以右边是我们的关键点,一定必要建立索引。这是由于，在外连接中的特性是，左外连接中，左表是主表，左表中的数据是一定要保存的，所以，就必须对左表进行全表扫描。而从表的连接字段建立索引的话，就可以利用索引，去优化利用主表的数据，在从表中查询的这一步调。
  1. CREATE INDEX idx_type_card ON `type`(card); #驱动表的连接列，建立索引
复制代码
  1. mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;
复制代码

从结果看，type表虽然也利用了，索引，但是rows的行数是20，也就是说，照旧相称于扫描了全表，不过利用索引优化了这一步。
  1. DROP INDEX idx_book_card ON book;#移除被驱动表card列索引
复制代码
  1. mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;
复制代码

被驱动表book变回了ALL全表扫描。
3.3 采用内连接
  1. DROP INDEX idx_type_card ON type;#移除type表的card列索引
复制代码
利用inner join，内连接，没有主从表之分。由select查询优化器自己根据查询成本，选择驱动表和被驱动表。
  1. mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card=book.card;
复制代码
  1. CREATE INDEX idx_book_card ON book(card);#book表添加card列索引，优化
  3. EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card=book.card;
复制代码
  1. CREATE INDEX idx_type_card ON type(card);#type表添加card列索引，优化
  3. EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card=book.card;
复制代码
  1. DROP INDEX idx_type_card ON book;
  3. EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card=book.card;
复制代码

结论：对于内连接来讲，如果表的连接条件中只能有一个字段有索引，则有索引的字段所在的表会被作为被驱动表出现。
  1. #再次添加book表card列的索引
  2. CREATE INDEX idx_book_card ON book(card);
  4. #向type表中添加数据（20条数据）
  5. INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
  6. INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
  7. INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
  8. INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
  9. INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
  10. INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
  11. INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
  12. INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
  13. INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
  14. INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 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)));
  26. EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;
复制代码

结论：对于内连接来说，在两个表的连接条件都存在索引的情况下，会选择小表作为驱动表。“小表驱动大表”。
3.4 JOIN语句原理

join方式连接多个表，本质就是各个表之间数据的循环匹配。MySQL5.5版本之前，MySQL只支持一种表间关联方式，就是嵌套循环(Nested Loop Join)。如果关联表的数据量很大，则join关联的执行时间会非常长。在MySQL5.5以后的版本中，MySQL通过引入BNLJ算法来优化嵌套执行。
1.驱动表和被驱动表


  • 内连接
  1. SELECT * FROM A JOIN B ON ...
复制代码
A一定是驱动表吗？不一定，优化器会根据你查询语句做优化，决定先查哪张表。先查询的表就是驱动表，反之就是被驱动表。利用explain关键字查看。

  • 外连接
  1. SELECT * FROM A LEFT JOIN B ON ...
  2. #或
  3. SELECT * FROM A RIGHT JOIN B ON ...
复制代码
  1. #4)JOIN的底层原理
  3. CREATE TABLE a(f1 INT, f2 INT, INDEX(f1))ENGINE=INNODB;
  5. CREATE TABLE b(f1 INT, f2 INT)ENGINE=INNODB;
  8. INSERT INTO a VALUES(1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
  10. INSERT INTO b VALUES(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);
  13. #测试1
  14. EXPLAIN SELECT * FROM a LEFT JOIN b ON(a.f1=b.f1) WHERE (a.f2=b.f2);
复制代码

结论，从结果看，b是驱动表，a是被驱动表。这是底层将sql语句改写成内连接，这是由于WHERE (a.f2=b.f2) a,b都是只有两个字段，都相等就是内连接。所以，外连接也不一定主表就是驱动表。当然很少上述这种情况。
2.Simple Nested-Loop Join(简单嵌套循环连接)

在连接条件上都无索引的情况下，算法相称简单，从表A中取一条数据，遍历B表，将匹配成功的记录，当到暂时表，以此类推，驱动表A的每一张表与被驱动表进行判断。

这种方式服从很低，上述表A数据100条，B数据1000条计算，A*B=10万次。
A代表A表的条数，B代表B表的条数(条数是指，满足ON条件且满足WHERE条件的行数)
开销统计SNLJ外表扫描次数1内表扫描次数A读取记录数A + B*AJOIN比较次数B * A回表读取记录次数0当然mysql不会利用这中方式进行表的连接，后面出现了Nested-Loop Join优化算法。
3.Index Nested-Loop Join(索引嵌套循环连接)

Index Nested-Loop Join其优化的思绪主要是为了减少内层表数据的匹配次数，所以要求被驱动表上的连接条件列上必须有索引才行。通过外层表匹配条件直接与内层表索引进行匹配，避免和内层表的每条记录去进行比较，这样极大的减少了对内层表的匹配次数。

驱动表中的每条记录通过被驱动表的索引进行访问，由于索引查询的成本是比较固定的，故mysql优化器都倾向于利用记录数少的表作为驱动表（外表)。
A代表A表的条数，B代表B表的条数(条数是指，满足ON条件且满足WHERE条件的行数)
开销统计SNLJINLJ外表扫描次数11内表扫描次数A0读取记录数A + B*AA + B(匹配)JOIN比较次数B * AA*Index(索引数的层数)回表读取记录次数0B(匹配的记录条数)4.Block Nested-Loop Join(块嵌套循环连接)

如果存在索引，那么会利用index的方式进行join，如果join的列没有索引，被驱动表要扫描的次数太多了。每次访问被驱动表，其表中的记录都会被加载到内存中，然后再从驱动表中取一条与其匹配，匹配竣事后清除内存，然后再从驱动表中加载一条记录，然后把被驱动表的记录在加载到内存匹配，这样周而复始，大大增加了IO的次数。为了减少被驱动表的IO次数，就出现了Block Nested-Loop Join的方式。
不再是逐条获取驱动表的数据，而是一块一块的获取，引入了join buffer缓冲区，将驱动表join相关的部分数据列(大小受join buffer的限制)缓存到join buffer中，然后全表扫描被驱动表，被驱动表的每一条记录一次性和join buffer中的所有驱动表记录进行匹配(内存中操作)，将简单嵌套循环中的多次比较合并成一次，降低了被驱动表的访问频率。
注意:
这里缓存的不只是关联表的列, select后面的列也会缓存起来。
在一个有N个join关联的sql中会分配N-1个join buffer。所以查询的时候只管减少不必要的字段，可以让join buffer中可以存放更多的列。

驱动表中的每条记录通过被驱动表的索引进行访问，由于索引查询的成本是比较固定的，故mysql优化器都倾向于利用记录数少的表作为驱动表（外表)。
A代表A表的条数，B代表B表的条数(条数是指，满足ON条件且满足WHERE条件的行数)
开销统计SNLJINLJBNLJ外表扫描次数111内表扫描次数A0(A *  used_column_size) / join_buffer_size + 1(如果能整除不加1)读取记录数A + B*AA + B(匹配)A + B * (A * used_column_size / join_buffer_size)JOIN比较次数B * AA*Index(索引数的层数)B * A回表读取记录次数0B(匹配的记录条数)0参数设置

  • block_nested_loop
  1. SHOW VARIABLES LIKE '%optimizer_switch%';#指令查看默认是开启的
复制代码

  • join_buffer_size
驱动表能不能一次加载完，要看join buffer能不能存储所有的数据，默认情况下join_buffer_size=256k。
  1. mysql> SHOW VARIABLES LIKE 'join_buffer_size';
  2. +------------------+--------+
  3. | Variable_name    | Value  |
  4. +------------------+--------+
  5. | join_buffer_size | 262144 |
  6. +------------------+--------+
  7. 1 row in set (0.01 sec)
复制代码
join_buffer_size的最大值在32位系统可以申请4G，而在64位操做系统下可以申请大于4G的Join Buffer空间(64位Windows除外，其大值会被截断为4GB并发出警告)。
5.Join小结

1、团体服从比较:INLJ > BNLJ > SNLJ
2、永久用小结果集驱动大结果集(其本质就是减少外层循环的数据数量) (小的度量单位指的是表行数*每行大小)
[code]select t1.b, t2.* from t1 straight_join t2 on (t1.b=t2.b) where t2.id

本帖子中包含更多资源

您需要 登录 才可以下载或查看，没有账号？立即注册

x
回复

使用道具 举报

0 个回复

倒序浏览
返回列表

快速回复

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

篮之新喜

金牌会员
这个人很懒什么都没写!

楼主热帖

标签云

存储 挺好的 服务器
微信订阅号
微信服务号
微信客服
小程序
H5
关于我们 商务合作 网站地图
©Copyright 2022-2024 杭州祥升科技有限公司 版权所有 浙ICP备20004199号
快速回复 返回顶部 返回列表