ToB企服应用市场:ToB评测及商务社交产业平台

标题: MySQL-10.索引优化与查询优化 [打印本页]

---

作者: 篮之新喜    时间: 2024-5-23 23:01
标题: MySQL-10.索引优化与查询优化
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

---

欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)

Powered by Discuz! X3.4