（十一）索引优化与查询优化(上)

最新推荐文章于 2023-04-16 17:05:47 发布
最新推荐文章于 2023-04-16 17:05:47 发布
阅读量374 收藏
点赞数 1
分类专栏： MySQL高级 文章标签： 数据库 sql java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_51295655/article/details/123037207
版权

这里写自定义目录标题

都有哪些维度可以进行数据库调优?

简言之︰

  • 索引失效、没有充分利用到索引 —― 索引建立
  • 关联查询太多JOIN(设计缺陷或不得已的需求) —― SQL优化
  • 服务器调优及各个参数设置（缓冲、线程数等) —― 调整my.cnf
  • 数据过多 一一 分库分表

关于数据库调优的知识点非常分散。不同的DBMS，不同的公司，不同的职位，不同的项目遇到的问题都不尽相同。这里我们分为三个章节进行细致讲解。

虽然SQL查询优化的技术有很多，但是大方向上完全可以分成物理查询优化和逻辑查询优化两大块。

  • 物理查询优化是通过索引和表连接方式等技术来进行优化，这里重点需要掌握索引的使用。
  • 逻辑查询优化就是通过SQL等价变换提升查询效率，直白一点就是说，换一种查询写法执行效率可能更高。

一、数据准备

学员表插 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表中插入数据的存储过程 
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

#执行存储过程，往class表添加1万条数据 

CALL insert_class(10000);

student

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

步骤6：删除某表上的索引

创建存储过程

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 ;

执行存储过程,这里并不需要直接执行，是为了之后需要使用时才调用

CALL proc_drop_index("dbname","tablename");

二、索引失效案例

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

  • 使用索引可以快速地定位表中的某条记录，从而提高数据库查询的速度，提高数据库的性能
  • 如果查询时没有使用索引，查询语句就会扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢。

大多数情况下都（默认）采用B+树来构建索引。只是空间列类型的索引使用R-树，并且MEMORY表还支持 hash索引。

其实，用不用索引，最终都是优化器说了算。优化器是基于什么的优化器?基于cost开销(CostBaseOptimizer)，它不是基于规则(Rule-BasedOptimizer)，也不是基于语义。怎么样开销小就怎么来。另外, SOL语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。

2.1 全值匹配我最爱

系统中经常出现的sql语句如下:

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

建立索引前执行:(关注执行时间)

mysql> SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';
Empty set, 1 warning (0.16 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) ;

建立索引后执行:

mysql> SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';
Empty set,1 warning (8.01 sec)

可以看到，创建索引前的查询时间是0.16秒，创建索引后的查询时间是0.01秒，索引帮助我们极大的提高了查询效率。

2.2 最佳左前缀法则

在MySQL建立联合索引时会遵守最佳左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

举例1:

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

在这里插入图片描述

举例2:

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

在这里插入图片描述
举例3:索引idx_age_classid_name还能否正常使用?

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

这里最左前缀不是联合索引的第一个但是因为中间是and,底层优化器会进行排序，就会使用到联合索引。如下图结果,记住只看索引就好了，and语句可能会被优化器重排

在这里插入图片描述
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

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

在这里插入图片描述
虽然可以正常使用，但是只有部分被使用到了。

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

在这里插入图片描述

完全没有使用上索引。

结论:MySQL可以为多个字段创建索引，一个索引可以包括16个字段。对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。如果查询条件中没有使用这些字段中第1个字段时，多列(或联合）索引不会被使用。

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

2.3 主键插入顺序

对于一个使用InnoDB存储引擎的表来说，在我们没有显式的创建索引时，表中的数据实际上都是存储在聚簇索引的叶子节点的。而记录又是存储在数据页中的，数据页和记录又是按照记录主键值从小到大的顺序进行排序，所以如果我们插入的记录的主键值是依次增大的话，那我们每插满一个数据页就换到下一个数据页继续插，而如果我们插入的主键值忽大忽小的话，就比较麻烦了，假设某个数据页存储的记录已经满了，它存储的主键值在1~100之间:
在这里插入图片描述
如果此时再插入一条主键值为 9的记录，那它插入的位置就如下图：
在这里插入图片描述
可这个数据页已经满了，再插进来咋办呢？我们需要把当前 页面分裂 成两个页面，把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着什么？意味着：性能损耗！所以如果我们想尽量避免这样无谓的性能损耗，最好让插入的记录的 主键值依次递增，这样就不会发生这样的性能损耗了。

所以我们建议：让主键具有 AUTO_INCREMENT ，让存储引擎自己为表生成主键，而不是我们手动插入 ，比如：person_info表：

CREATE TABLE person_info( 
id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
name VARCHAR(100) NOT NULL, 
birthday DATE NOT NULL, 
phone_number CHAR(11) NOT NULL, 
country varchar(100) NOT NULL, 
PRIMARY KEY (id), 
KEY idx_name_birthday_phone_number (name(10), birthday, phone_number) 
);

我们自定义的主键列 id 拥有 AUTO_INCREMENT 属性，在插入记录时存储引擎会自动为我们填入自增的主键值。这样的主键占用空间小，顺序写入，减少页分裂。

2.4 计算、函数、类型转换(自动或手动)导致索引失效

1.这两条sql哪种写法更好

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';

2.创建索引

CREATE INDEX idx_name ON student(NAME);

3.第一种：索引优化生效

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';

mysql> SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%'; 
+---------+---------+--------+------+---------+
| id | stuno | name | age | classId | 
+---------+---------+--------+------+---------+
| 5301379 | 1233401 | AbCHEa | 164 | 259 | 
| 7170042 | 3102064 | ABcHeB | 199 | 161 |
| 1901614 | 1833636 | ABcHeC | 226 | 275 | 
| 5195021 | 1127043 | abchEC | 486 | 72 | 
| 4047089 | 3810031 | AbCHFd | 268 | 210 | 
| 4917074 | 849096 | ABcHfD | 264 | 442 | 
| 1540859 | 141979 | abchFF | 119 | 140 | 
| 5121801 | 1053823 | AbCHFg | 412 | 327 | 
| 2441254 | 2373276 | abchFJ | 170 | 362 | 
| 7039146 | 2971168 | ABcHgI | 502 | 465 | 
| 1636826 | 1580286 | ABcHgK | 71 | 262 | 
| 374344 | 474345 | abchHL | 367 | 212 | 
| 1596534 | 169191 | AbCHHl | 102 | 146 | 
					... 
| 5266837 | 1198859 | abclXe | 292 | 298 | 
| 8126968 | 4058990 | aBClxE | 316 | 150 | 
| 4298305 | 399962 | AbCLXF | 72 | 423 | 
| 5813628 | 1745650 | aBClxF | 356 | 323 | 
| 6980448 | 2912470 | AbCLXF | 107 | 78 |
| 7881979 | 3814001 | AbCLXF | 89 | 497 | 
| 4955576 | 887598 | ABcLxg | 121 | 385 | 
| 3653460 | 3585482 | AbCLXJ | 130 | 174 | 
| 1231990 | 1283439 | AbCLYH | 189 | 429 | 
| 6110615 | 2042637 | ABcLyh | 157 | 40 | 
+---------+---------+--------+------+---------+
401 rows in set, 1 warning (0.01 sec)

4.第二种：索引优化失效

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';

在这里插入图片描述

mysql> SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc'; 
+---------+---------+--------+------+---------+
| id | stuno | name | age | classId |
+---------+---------+--------+------+---------+
| 5301379 | 1233401 | AbCHEa | 164 | 259 | 
| 7170042 | 3102064 | ABcHeB | 199 | 161 | 
| 1901614 | 1833636 | ABcHeC | 226 | 275 | 
| 5195021 | 1127043 | abchEC | 486 | 72 | 
| 4047089 | 3810031 | AbCHFd | 268 | 210 | 
| 4917074 | 849096 | ABcHfD | 264 | 442 | 
| 1540859 | 141979 | abchFF | 119 | 140 | 
| 5121801 | 1053823 | AbCHFg | 412 | 327 | 
| 2441254 | 2373276 | abchFJ | 170 | 362 | 
| 7039146 | 2971168 | ABcHgI | 502 | 465 | 
| 1636826 | 1580286 | ABcHgK | 71 | 262 | 
| 374344 | 474345 | abchHL | 367 | 212 | 
| 1596534 | 169191 | AbCHHl | 102 | 146 | 
				... 
| 5266837 | 1198859 | abclXe | 292 | 298 | 
| 8126968 | 4058990 | aBClxE | 316 | 150 | 
| 4298305 | 399962 | AbCLXF | 72 | 423 | 
| 5813628 | 1745650 | aBClxF | 356 | 323 | 
| 6980448 | 2912470 | AbCLXF | 107 | 78 | 
| 7881979 | 3814001 | AbCLXF | 89 | 497 | 
| 4955576 | 887598 | ABcLxg | 121 | 385 | 
| 3653460 | 3585482 | AbCLXJ | 130 | 174 | 
| 1231990 | 1283439 | AbCLYH | 189 | 429 | 
| 6110615 | 2042637 | ABcLyh | 157 | 40 | 
+---------+---------+--------+------+---------+
401 rows in set, 1 warning (3.62 sec)

type为“ALL”，表示没有使用到索引，查询时间为 3.62秒，查询效率较之前低很多。

再举例：

  • student表的字段stuno上设置有索引
CREATE INDEX idx_sno ON student(stuno);
  • 索引优化失效:(假设: student表的字段stuno上设置有索引)
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;

运行结果：
在这里插入图片描述
你能看到如果对索引进行了表达式计算，索引就失效了。这是因为我们需要把索引字段的取值都取出来，然后依次进行表达式的计算来进行条件判断，因此采用的就是全表扫描的方式，运行时间也会慢很多，最终运行时间为2.538秒。

EXPLAIN SELECT id, stuno, name FROM student WHERE SUBSTRING(name, 1,3)='abc';
  • 索引优化生效：
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno = 900000;

运行时间为0.039秒。
再举例：

  • student表的字段name上设置有索引
CREATE INDEX idx_name ON student(NAME);

我们想要对name的前三位为abc的内容进行条件筛选，这里我们来查看下执行计划:

  • 索引优化失效:
EXPLAIN SELECT id, stuno, name FROM student WHERE SUBSTRING(name, 1,3)='abc';

在这里插入图片描述

EXPLAIN SELECT id, stuno, NAME FROM student WHERE NAME LIKE 'abc%';

在这里插入图片描述
你能看到经过查询重写后，可以使用索引进行范围检索，从而提升查询效率。

2.5 类型转换导致索引失效

下列哪个sql语句可以用到索引。（假设name字段上设置有索引）

# 未使用到索引 
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;

在这里插入图片描述

# 使用到索引 
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123';

在这里插入图片描述

  • name=123发生类型转换，索引失效。

结论:设计实体类属性时，一定要与数据库字段类型相对应。否则，就会出现类型转换的情况。

2.6 范围条件右边的列索引失效

ALTER TABLE student DROP INDEX idx_name; 
ALTER TABLE student DROP INDEX idx_age; 
ALTER TABLE student DROP INDEX idx_age_classid; 

EXPLAIN SELECT SQL_NO_CACHE * FROM student 
WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;

在这里插入图片描述
那么索引 idx_age_classid_name这个索引还能正常使用么?

  • 不能，范围右边的列不能使用。
  • 比如: (<)(=)(>)(>=）和between 等如果这种sql出现较多，应该建立:
create index idx_age_name_classid on student(age,name,classid);
  • 将范围查询条件放置语句最后：
EXPLAIN SELECT SQL_NO_CACHE * FROM student 
WHERE student.age=30 AND student.name = 'abc' AND student.classId>20 ;

在这里插入图片描述

应用开发中范围查询，例如:金额查询，日期查询往往都是范围查询。应将查询条件放置where语句最后。(创建的联合索引中，务必把范围涉及到的字段写在最后)

2.7 不等于(!= 或者<>)索引失效

  • 为name字段创建索引
CREATE INDEX idx_name ON student (NAME);
  • 查看索引是否失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name <> 'abc' ;

在这里插入图片描述

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

  • is null 可以触发索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;

在这里插入图片描述

  • is not null 不可以触发索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;

在这里插入图片描述

结论:最好在设计数据表的时候就将字段设置为NOT NULL 约束，比如你可以将INT类型的字段，默认值设置为o。将字符类型的默认值设置为空字符串()。
拓展:同理，在查询中使用not like也无法使用索引，导致全表扫描。

其实不一定 is not null 不会使用索引而是根据成本，可以看一下这个文章 is null 深究

2.9 like以通配符%开头索引失效

在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引就不会起作用只有“%”不在第一个位置，索引才会起作用。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE '%bc';

在这里插入图片描述

拓展：Alibaba《Java开发手册》
【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

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

在WHERE子句中，如果在OR前的条件列进行了索引，而在OR后的条件列没有进行索引，那么索引会失效。也就是说，OR前后的两个条件中的列都是索引时，查询中才使用索引。

因为OR的含义就是两个只要满足一个即可，因此只有一个条件列进行了索引是没有意义的，只要有条件列没有进行索引，就会进行全表扫描，因此索引的条件列也会失效。

查询语句使用OR关键字的情况:

# 未使用到索引 
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;

在这里插入图片描述
因为classid字段上没有索引，所以上述查询语句没有使用索引。

#使用到索引 
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR name = 'Abel';

在这里插入图片描述
因为age字段和name字段上都有索引，所以查询中使用了索引。你能看到这里使用到了index_merge，简单来说index_merge就是对age和name分别进行了扫描，然后将这两个结果集进行了合并。这样做的好处就是避免了全表扫描。

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

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

2.12 小结

在这里插入图片描述

一般性建议:

  • 对于单列索引，尽量选择针对当前query过滤性更好的索引
  • 在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
  • 在选择组合索引的时候，尽量选择能够包含当前query中的where子句中更多字段的索引。
  • 在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面。

总之，书写SQL语句时，尽量避免造成索引失效的情况。

三、关联查询优化

3.1 数据准备

#分类
CREATE TABLE IF NOT EXISTS `type` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
);
#图书
CREATE TABLE IF NOT EXISTS `book` (
`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`bookid`)
);

#向分类表中添加20条记录
INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));

#向图书表中添加20条记录
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

3.2 采用左外连接

下面开始 EXPLAIN 分析

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

在这里插入图片描述
结论：type 有All

添加索引优化

ALTER TABLE book ADD INDEX Y ( card); #【被驱动表】，可以避免全表扫描 

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

在这里插入图片描述
可以看到第二行的 type 变为了 ref，rows 也变成了优化比较明显。这是由左连接特性决定的。LEFT JOIN条件用于确定如何从右表搜索行，左边一定都有，所以 右边是我们的关键点,一定需要建立索引 。

ALTER TABLE `type` ADD INDEX X (card); #【驱动表】，无法避免全表扫描 

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

在这里插入图片描述

接着：

DROP INDEX Y ON book; 

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

在这里插入图片描述

3.3 采用内连接

drop index X on type; 

drop index Y on book;（如果已经删除了可以不用再执行该操作）

换成 inner join（MySQL自动选择驱动表）

EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card=book.card;

在这里插入图片描述
添加索引优化

ALTER TABLE book ADD INDEX Y ( card); 

EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card=book.card;

在这里插入图片描述

ALTER TABLE type ADD INDEX X (card); 


EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card=book.card;

结论：对于内连接来说，查询优化器可以决定谁作为驱动表，谁作为被驱动表出现的
在这里插入图片描述
接着：

DROP INDEX X ON `type`; 

EXPLAIN SELECT SQL_NO_CACHE * FROM TYPE INNER JOIN book ON type.card=book.card;

结论：对于内连接来讲，如果表的连接条件中只能有一个字段有索引，则有索引的字段所在的表会被作为被驱动表出现。
在这里插入图片描述
接着：

ALTER TABLE `type` ADD INDEX X (card); 

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、驱动表和被驱动表

驱动表就是主表，被驱动表就是从表、非驱动表。

  • 对于内连接来说
select *  from A JOIN B ON.....

A一定是驱动表吗?不一定，优化器会根据你查询语句做优化，决定先查哪张表。先查询的那张表就是驱动表，反之就是被驱动表。通过explain关键字可以查看。

  • 对于外连接来说:
SELECT * FROM A LEFT JOIN B ON ...
#或
SELECT * FROM B RIGHT JOIN A ON ...

通常，大家会认为A就是驱动表，B就是被驱动表。但也未必。测试如下:

CREATE TABLE a(f1 INT, f2 INT, INDEX(f1))ENGINE=INNODB;

CREATE TABLE b(f1 INT, f2 INT)ENGINE=INNODB;


INSERT INTO a VALUES(1,1),(2,2),(3,3),(4,4),(5,5),(6,6);

INSERT INTO b VALUES(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);


#测试1
EXPLAIN SELECT * FROM a LEFT JOIN b ON(a.f1=b.f1) WHERE (a.f2=b.f2);

#测试2
EXPLAIN SELECT * FROM a LEFT JOIN b ON(a.f1=b.f1) AND (a.f2=b.f2);

#测试3
EXPLAIN SELECT * FROM a JOIN b ON(a.f1=b.f1) WHERE (a.f2=b.f2);

上面三个测试结果如下
一
在这里插入图片描述
二
在这里插入图片描述

三

在这里插入图片描述

2、Simple Nested-Loop Join(简单嵌套循环连接)

算法相当简单，从表A中取出一条数据1，遍历表B，将匹配到的数据放到result…以此类推，驱动表A中的每一条记录与被驱动表B的记录进行判断:
在这里插入图片描述
可以看到这种方式效率是非常低的，以上述表A数据100条，表B数据1000条计算，则 A * B= 10万次 开销统计如下:
在这里插入图片描述

3、Index Nested-Loop Join（索引嵌套循环连接)

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

在这里插入图片描述
驱动表中的每条记录通过被驱动表的索引进行访问，因为索引查询的成本是比较固定的，故mysql优化器都倾向于使用记录数少的表作为驱动表（外表)。
在这里插入图片描述
如果被驱动表加索引，效率是非常高的，但如果索引不是主键索引，所以还得进行一次回表查询相比，被驱动表的索引是主键索引，效率会更高。

我们来看一下这个语句：

EXPLAIN SELECT * FROM t1 STRAIGHT_JOIN t2 ON (t1.a=t2.a);

如果直接使用join语句，MySQL优化器可能会选择表t1或t2作为驱动表，这样会影响我们分析SQL语句的执行过程。所以，为了便于分析执行过程中的性能问题，我改用 straight_join 让MySQL使用固定的连接方式执行查询，这样优化器只会按照我们指定的方式去join。在这个语句里，t1 是驱动表，t2是被驱动表。
在这里插入图片描述
可以看到，在这条语句里，被驱动表t2的字段a上有索引，join过程用上了这个索引，因此这个语句的执行流程是这样的：

  1. 从表t1中读入一行数据 R；
  2. 从数据行R中，取出a字段到表t2里去查找；
  3. 取出表t2中满足条件的行，跟R组成一行，作为结果集的一部分；
  4. 重复执行步骤1到3，直到表t1的末尾循环结束

这个过程是先遍历表t1，然后根据从表t1中取出的每行数据中的a值，去表t2中查找满足条件的记录。在形式上，这个过程就跟我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引，所以我们称之为“Index Nested-Loop Join”，简称NLJ。

它对应的流程图如下所示：

在这里插入图片描述
在这个流程里：

  1. 对驱动表t1做了全表扫描，这个过程需要扫描100行；
  2. 而对于每一行R，根据a字段去表t2查找，走的是树搜索过程。由于我们构造的数据都是一一对应
    的，因此每次的搜索过程都只扫描一行，也是总共扫描100行；
  3. 所以，整个执行流程，总扫描行数是200。

引申问题1：能不能使用join?

引申问题2：怎么选择驱动表？

比如：N扩大1000倍的话，扫描行数就会扩大1000倍；而M扩大1000倍，扫描行数扩大不到10倍。

两个结论：

  1. 使用join语句，性能比强行拆成多个单表执行SQL语句的性能要好；
  2. 如果使用join语句的话，需要让小表做驱动表。

4、Block Nestied-Loop Join(块嵌套循环连接)

如果存在索引，那么会使用index的方式进行join，如果join的列没有索引，被驱动表要扫描的次数太多了。每次访问被驱动表，其表中的记录都会被加载到内存中，然后再从驱动表中取一条与其匹配，匹配结束后清除内存，然后再从驱动表中加载一条记录，然后把被驱动表的记录在加载到内存匹配，这样周而复始，大大增加了I0的次数。为了减少被驱动表的Io次数，就出现了Block Nested-Loop Join的方式。

不再是逐条获取驱动表的数据，而是一块一块的获取，引入了join buffer缓冲区，将驱动表join相关的部分数据列(大小受join buffer的限制)缓存到 join buffer中，然后全表扫描被驱动表，被驱动表的每一条记录一次性和 joinbuffer 中的所有驱动表记录进行匹配(内存中操作)，将简单嵌套循环中的多次比较合并成一次，降低了被驱动表的访问频率

注意:
这里缓存的不只是关联表的列,select后面的列也会缓存起来。
在一个有N个join关联的sql中会分配N-1个join buffer。所以查询的时候尽量减少不必要的字段，可以让joinbuffer中可以存放更多的列。

在这里插入图片描述

在这里插入图片描述

参数设置:

  • block_nested_loqp

    通过show variables like '%optimizer_switch%查看block_nested_loop状态。默认是开启的。

  • join_buffer_size

    驱动表能不能一次加载完，要看join buffer能不能存储所有的数据，默认情况下join_buffer_size=256k。

mysql> show variables like 'join_buffer%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.01 sec)

join_buffer_size的最大值在32位系统可以申请4G，而在64位操做系统下可以申请大于4G的Join Buffer空间(64位Windows除外，其大值会被截断为4GB并发出警告)。

5 、小结

  • 整体效率比较:INLJ > BNLJ > SNLJ
  • 保证被驱动表的JOIN字段已经创建了索引
  • 需要JOIN 的字段，数据类型保持绝对一致。
  • LEFT JOIN 时，选择小表作为驱动表， 大表作为被驱动表 。减少外层循环的次数。
  • INNER JOIN 时，MySQL会自动将 小结果集的表选为驱动表 。选择相信MySQL优化策略。
  • 能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数)
  • 不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用 JOIN 来代替子查询。
  • 衍生表建不了索引
  • 增大join buffer size的大小(一次缓存的数据越多，那么内层包的扫表次数就越少)

3：什么叫作“小表(小结果集)”？

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

6、Hash Join（8.0新特性）

从MySQL的8.0.20版本开始将废弃BNLJ，因为从MySQL8.0.18版本开始就加入了hash join默认都会使用hash join

  • Nested Loop:

    对于被连接的数据子集较小的情况，Nested Loop是个较好的选择。

  • Hash Join是做大数据集连接时的常用方式，优化器使用两个表中较小(相对较小)的表利用Join Key在内存中建立散列表，然后扫描较大的表并探测散列表，找出与Hash表匹配的行。

    • 这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成本之和。
    • 在表很大的情况下并不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要求有较大的临时段从而尽量提高I/o的性能。
    • 它能够很好的工作于没有索引的大表和并行查询的环境中，并提供最好的性能。大多数人都说它是Join的重型升降机。Hash Join只能应用于等值连接(如WHERE A.COL1 =B.COL2)，这是由Hash的特点决定的。

对比图

在这里插入图片描述

四、子查询优化

MySQL从4.1版本开始支持子查询，使用子查询可以进行SELECT语句的嵌套查询，即一个SELECT查询的结果作为另一个SELECT语句的条件。 子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作 。

子查询是 MySQL 的一项重要的功能，可以帮助我们通过一个 SQL 语句实现比较复杂的查询。但是，子查询的执行效率不高。

原因：
① 执行子查询时，MySQL需要为内层查询语句的查询结果 建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再 撤销这些临时表 。这样会消耗过多的CPU和IO资源，产生大量的慢查询。

② 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都 不会存在索引，所以查询性能会受到一定的影响。

③ 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

在MySQL中，可以使用连接（JOIN）查询来替代子查询。连接查询 不需要建立临时表，其 速度比子查询要快 ，如果查询中使用索引的话，性能就会更好。

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

五、排序优化

5.1 排序优化

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

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

优化建议：

  1. SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中避免全表扫描 ，在 ORDER BY 子句避免使用 FileSort 排序 。当然，某些情况下全表扫描，或者 FileSort 排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。
  2. 尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列；
    如果不同就使用联合索引。
  3. 无法使用 Index 时，需要对 FileSort 方式进行调优

5.2 测试

#删除student和class表中的非主键索引

CALL proc_drop_index('atguigudb2','student');
CALL proc_drop_index('atguigudb2','class');

SHOW INDEX FROM student;
SHOW INDEX FROM class;

以下是否能使用到索引，能否去掉using filesort

过程一：

EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid;

在这里插入图片描述

EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid LIMIT 10;

在这里插入图片描述

过程二:order by时不limit，索引失效

#创建索引  
CREATE  INDEX idx_age_classid_name ON student (age,classid,NAME);


#不限制,索引失效
EXPLAIN  SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid;

在这里插入图片描述

#增加limit过滤条件，使用上索引了。
EXPLAIN  SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid LIMIT 10;

在这里插入图片描述

过程三：order by时顺序错误，索引失效

#创建索引age,classid,stuno
CREATE  INDEX idx_age_classid_stuno ON student (age,classid,stuno); 

#以下哪些索引失效?
EXPLAIN  SELECT * FROM student ORDER BY classid LIMIT 10;//失效

EXPLAIN  SELECT * FROM student ORDER BY classid,NAME LIMIT 10; //失效 

EXPLAIN  SELECT * FROM student ORDER BY age,classid,stuno LIMIT 10; //有用

EXPLAIN  SELECT * FROM student ORDER BY age,classid LIMIT 10;//有用

EXPLAIN  SELECT * FROM student ORDER BY age LIMIT 10;//有用

过程四：order by时规则不一致, 索引失效 （顺序错，不索引；方向反，不索引）

#没用
EXPLAIN  SELECT * FROM student ORDER BY age DESC, classid ASC LIMIT 10;
#没用
EXPLAIN  SELECT * FROM student ORDER BY classid DESC, NAME DESC LIMIT 10;
#没用
EXPLAIN  SELECT * FROM student ORDER BY age ASC,classid DESC LIMIT 10; 
#用了
EXPLAIN  SELECT * FROM student ORDER BY age DESC, classid DESC LIMIT 10;

结论:ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序

过程五：无过滤，不索引

#用到了，但是是用于age判断的
EXPLAIN  SELECT * FROM student WHERE age=45 ORDER BY classid;
#用到了，但是是用于age判断的
EXPLAIN  SELECT * FROM student WHERE  age=45 ORDER BY classid,NAME; 
#没用到
EXPLAIN  SELECT * FROM student WHERE  classid=45 ORDER BY age;
#用到了
EXPLAIN  SELECT * FROM student WHERE  classid=45 ORDER BY age LIMIT 10;

小结

INDEX a_b_c(a,b,c) 

order by 能使用索引最左前缀
- ORDER BY a
- ORDER BY a,b
- ORDER BY a,b,c
- ORDER BY a DESC,b DESC,c DESC 

如果WHERE使用索引的最左前缀定义为常量，则order by 能使用索引
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b = const ORDER BY c
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b > const ORDER BY b,c 

不能使用索引进行排序
- ORDER BY a ASC,b DESC,c DESC /* 排序不一致 */
- WHERE g = const ORDER BY b,c /*丢失a索引*/
- WHERE a = const ORDER BY c /*丢失b索引*/
- WHERE a = const ORDER BY a,d /*d不是索引的一部分*/
- WHERE a in (...) ORDER BY b,c /*对于排序来说，多个相等条件也是范围查询*/

5.3 案例实战

ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序。

执行案例前先清除student上的索引，只留主键：

DROP INDEX idx_age ON student; 
DROP INDEX idx_age_classid_stuno ON student; 
DROP INDEX idx_age_classid_name ON student; 

#或者 
call proc_drop_index('atguigudb2','student');

场景:查询年龄为30岁的，且学生编号小于101000的学生，按用户名称排序

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;

在这里插入图片描述
查询结果如下：

mysql> SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ; 
+---------+--------+--------+------+---------+
| id | stuno | name | age | classId | 
+---------+--------+--------+------+---------+
| 922 | 100923 | elTLXD | 30 | 249 | 
| 3723263 | 100412 | hKcjLb | 30 | 59 | 
| 3724152 | 100827 | iHLJmh | 30 | 387 | 
| 3724030 | 100776 | LgxWoD | 30 | 253 | 
| 30 | 100031 | LZMOIa | 30 | 97 | 
| 3722887 | 100237 | QzbJdx | 30 | 440 | 
| 609 | 100610 | vbRimN | 30 | 481 | 
| 139 | 100140 | ZqFbuR | 30 | 351 | 
+---------+--------+--------+------+---------+
8 rows in set, 1 warning (3.16 sec)

结论：type 是 ALL，即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须的。

优化思路：

方案一: 为了去掉filesort我们可以把索引建成

#创建新索引 
CREATE INDEX idx_age_name ON student(age,NAME);

方案二: 尽量让where的过滤条件和排序使用上索引

建一个三个字段的组合索引：

DROP INDEX idx_age_name ON student; 

CREATE INDEX idx_age_stuno_name ON student (age,stuno,NAME); 

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;

mysql> SELECT SQL_NO_CACHE * FROM student
-> WHERE age = 30 AND stuno <101000 ORDER BY NAME ; 
+-----+--------+--------+------+---------+
| id | stuno | name | age | classId | 
+-----+--------+--------+------+---------+
| 167 | 100168 | AClxEF | 30 | 319 | 
| 323 | 100324 | bwbTpQ | 30 | 654 | 
| 651 | 100652 | DRwIac | 30 | 997 | 
| 517 | 100518 | HNSYqJ | 30 | 256 | 
| 344 | 100345 | JuepiX | 30 | 329 | 
| 905 | 100906 | JuWALd | 30 | 892 | 
| 574 | 100575 | kbyqjX | 30 | 260 | 
| 703 | 100704 | KJbprS | 30 | 594 | 
| 723 | 100724 | OTdJkY | 30 | 236 | 
| 656 | 100657 | Pfgqmj | 30 | 600 | 
| 982 | 100983 | qywLqw | 30 | 837 | 
| 468 | 100469 | sLEKQW | 30 | 346 | 
| 988 | 100989 | UBYqJl | 30 | 457 | 
| 173 | 100174 | UltkTN | 30 | 830 | 
| 332 | 100333 | YjWiZw | 30 | 824 | 
+-----+--------+--------+------+---------+
15 rows in set, 1 warning (0.00 sec)

结果竟然有 filesort的 sql 运行速度， 超过了已经优化掉 filesort的 sql，而且快了很多，几乎一瞬间就出现了结果

结论：

  1. 两个索引同时存在，mysql自动选择最优的方案。（对于这个例子，mysql选择idx_age_stuno_name）。但是， 随着数据量的变化，选择的索引也会随之变化的 。
  2. 当【范围条件】和【group by 或者 order by】的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。

思考：这里我们使用如下索引，是否可行？

DROP INDEX idx_age_stuno_name ON student; 

CREATE INDEX idx_age_stuno ON student(age,stuno);

5.4 filesort算法：双路排序和单路排序

双路排序 （慢）

  • MySQL 4.1之前是使用双路排序 ，字面意思就是两次扫描磁盘，最终得到数据， 读取行指针和order by列 ，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
  • 从磁盘取排序字段，在buffer进行排序，再从 磁盘取其他字段 。

取一批数据，要对磁盘进行两次扫描，众所周知，IO是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。

单路排序 （快）

从磁盘读取查询需要的 所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出， 它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间， 因为它把每一行都保存在内存中了。

结论及引申出的问题

  • 由于单路是后出的，总体而言好过双路
  • 但是用单路有问题
    • 在sort_buffer中，单路比多路要多占用很多空间，因为单路是把所有字段都取出,所以有可能取出的数据的总大小超出了sortLbuffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序(创建tmp文件，多路合并)，排完再取sort_buffer容量大小，再排…从而多次I/O。
    • 单路本来想省一次I/O操作，反而导致了大量的I/0操作，反而得不偿失。化策略

优化策略

  1. 尝试提高 sort_buffer_size

    • 不管用哪种算法，提高这个参数都会提高效率，要根据系统的能力去提高，因为这个参数是针对每个进程(connection)的1M-8M之间调整。MySQL5.7，InnoDB存储引擎默认值是1048576字节，1MB。
    • show variables like '%sort_buffer_size%'
    • 在这里插入图片描述

  2. 尝试提高 max_length_for_sort_data

    提高这个参数，会增加用改进算法的概率。

    show variables like '%max_length_for_sort_data%'

    但是如果设的太高，数据总容量超出sort_ buffer. size的概率就增大, 明显症状是高的磁盘I/0活动和低的处理器使用率。如果需要返回的列的总长度大于max_ length_for_ sort_ data, 使用双路算法，否则使用单路算法。1024-8192字节之间调整

  3. Order by 时select * 是一个大忌。最好只Query需要的字段。

当Query的字段大小总和小于max_length_for_sort_data，而且排序字段不是TEXT|BLOB类型时，会用改进后的算法一—单路排序，否则用老算法―多路排序。

两种算法的数据都有可能超出sort_buffer_size的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/o，但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size。

小怪吖
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
索引优化与查询优化
main_Scanner01的博客
03-27 1238
都有哪些维度可以进行数据库调优？简言之: 索引失效、没有充分利用到索引——索引建立 关联查询太多JOIN(设计缺陷或不得已的需求)——SQL优化 服务器调优及各个参数设置(缓冲、线程数等)——调整my.cnf。 数据过多——分库分表 关于数据库调优的知识点非常分散。不同的DBMS，不同的公司，不同的职位，不同的项目遇到的问题都不尽相同。这里我们分为三个章节进行细致讲解。 虽然SQL查询优化的技术有很多，但是大方向上完全可以分成物理查询优化和逻辑查询优化两大块。 物理查询优化是通过索引和表连接方式等技术
（十一）索引优化与查询优化(下)
小怪的博客
02-21 335
索引优化与查询优化六、 GROUP BY优化七、优化分页查询八、优先考虑覆盖索引8.1 什么是覆盖索引？8.2 覆盖索引的利弊九、如何给字符串添加索引9.1 前缀索引9.2 前缀索引对覆盖索引的影响十、索引下推10.1 使用前后的扫描过程10.2 ICP的使用条件10.3 ICP使用案例十一、普通索引 vs 唯一索引11.1 查询过程11.2 更新过程11.3 change buffer的使用场景十二、其它查询优化策略12.1 EXISTS 和 IN 的区分12.2 COUNT(*)与COUNT(具体字段)
pgsql常用的sql函数总结
最新发布
qq_37823919的博客
04-16 4895
【代码】pgsql常用的sql函数总结。
PostgreSQL 性能优化创建正确的索引具有不确定性
sql server的专栏
04-11 953
索引在数据库的查询中起到的作用毋庸置疑，但时常有人提出索引的建立的问题，to be or not to be 的问题。问题1 索引建立后，就不再变动了 ？大多数的问题是在于索引建立后并不能一直良好的工作，主要有以下几个问题1 重复功能的索引，让查询无法把握或者在管理人员不知情的情况下，走了其他的索引，索引并不能有效的工作，并成为负担。2 索引在PG的数据改变变化导...
PostgreSQL索引（一）
NOSaac的博客
09-10 8951
PostgreSQL索引（一）
MySQL高级知识-查询与索引优化分析
02-25
手写手写SQL顺序机读顺序总结-SQL解析顺序SQL解析SQLJOINs七种JOIN图解实验：练习1、A、...你可以简单理解为“排好序的快速查找结构”一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存
MYSQL索引优化与查询优化
06-21
在实际应用中，MySQL的索引优化和查询优化不仅涉及到技术层面，也与数据库设计和业务逻辑密切相关。通过对这些知识的深入理解和熟练运用，开发者可以有效地提升数据库的性能，从而提高整个应用程序的用户体验。通过...
MySQL索引原理及慢查询优化
12-14
总的来说，优化MySQL的慢查询需要结合索引原理、查询优化技巧、数据库设计和运维实践。通过对业务需求的理解，我们可以制定合适的索引策略，改进SQL语句，从而提升数据库的整体性能，满足高并发、大数据量的业务需求...
MySQL查询优化–索引优化
12-14
MySQL查询优化中的索引优化是提高数据库性能的关键技术之一。索引的作用在于加快数据检索速度，减少磁盘I/O操作，提升查询效率。在给定的例子中，我们首先创建了一个名为`article`的表，包含`id`（主键）、`author_...
PostgreSQL中的索引
aqin1012的博客
10-03 650
含义 索引是一个单独的、存储在磁盘上的数据结构，用于对数据库中一行或多行的值进行排序，用于快速找出在某个列中某一特定值的行。 分类 B-tree CREATE INDEX index_name ON table_name USING BTREE(column); Hash CREATE INDEX index_name ON table_name USING hash(column); GiST 不是单独的一种索引类型，而是一种架构，可以在这种架构上实现很多不同的索引策略。 GIN..
索引+索引的类型+创建索引
zhou920786312的博客
05-27 1934
索引 1在关系数据库中,索引是一种与表有关的数据库结构，它是除了表以外的另一个重要模式对象。 2索引建立在表的一列或多列上的辅助对象，目的是提高表中数据的访问速度. 3索引是表示数据的另一种方式，它提供的数据顺序不同于数据在磁盘上的物理存储顺序。 4索引的特殊作用是在表内重新排列记录的物理位置 索引的类型 B树索引，反向键索引，位图索引，基于函数的索引，簇索引，全局索引，局部索引等.创建
PLSQL索引
Ufindm的博客
04-08 823
1.什么是索引 索引是数据库对象之一，英语加快数据的查找，类似于书籍的目录。 2.如何创建索引 创建BTREE索引 CREATE INDEX index_name on table_name(colum1,colum2…); 创建位图索引 CREATE BITMAP INDEX index_name on table_name(colum1,colum2…); 3.修改索引 alter inde...
plsql添加索引
热门推荐
二黑的博客
02-12 2万+
一开始通过将一张表的数据update到另外一张表，在此过程中发现速度很慢，于是建立了一个中间表，在中间表上加上一个索引，此时导数据的速度就快了很多。 第一步：需要对表"linshibiao"创建索引。 可以通过PLSQL界面左边的对象—&gt;Table—&gt;linshibiao—&gt;右击—&gt;点击编辑 （也可以在SQL编辑页面直接右击表linshibiao—&gt;编辑，有时候...
plsql查询表索引
weixin_42064877的博客
04-14 3056
--主键约束 SELECT USER_CONS_COLUMNS.CONSTRAINT_NAME AS 约束名, USER_CONS_COLUMNS.TABLE_NAME AS 表名, USER_CONS_COLUMNS.COLUMN_NAME AS 列名, USER_CONS_COLUMNS.POSITION AS 位置 FROM USER_CONSTRAINTS JOIN USER_CONS_COLUMNS ON (USER_CONSTRAINTS.CONSTRAINT
PostgreSQL常用索引
PostgreSQL China
09-22 5884
作者：阎书利 索引其实就是一种数据结构，将数据库中的数据以一定的数据结构算法进行存储。当表数据量越来越大时查询速度会下降，建立合适的索引能够帮助我们快速的检索数据库中的数据，快速定位到可能满足条件的记录，不需要遍历所有记录。 索引自身也占用存储空间、消耗计算资源，创建过多的索引将对数据库性能造成负面影响（尤其影响数据导入的性能，建议在数据导入后再建索引）。因此，仅在必要时创建索引。postgresql里的所有索引都是“从属索引”，也就是索引在物理上与它描述的表文件分离。索引是一种数据库对象，每个索引在pg_
SQL索引详解
Mengxinxiaobaia的博客
11-23 8165
一、什么是索引 索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的特殊数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到需要的内容 二、聚焦索引和非聚焦索引 区别 聚焦索引：该索引中键值的逻辑顺序决定了表中相应行的物理顺序（物理） 非聚集索引：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同（逻辑） 例子 一本字典的目录有字母目录和偏旁笔画排序，汉字的顺序是按字母排序的，..
第10章_索引优化与查询优化
12-24 1103
理解方式一：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做覆盖索引。**理解方式二：**非聚簇复合索引的一种形式，它包括在查询里的SELECT、JOIN和WHERE子句用到的所有列（即建索引的字段正好是覆盖查询条件中所涉及的字段）。简单说就是，索引列+主键包含SELECT 到 FROM之间查询的列。
存储过程之新增或者删除索引
lixinkuan的博客
03-06 1630
1、如果索引存在，则删除索引 -- [新增]如果索引存在，则删除索引 drop procedure if exists proc_del_index_if_exist; delimiter $$ create procedure proc_del_index_if_exist(in p_tablename varchar(200),in p_idxname varchar(200)) begi...
mysql删除unique 报错： MySQL Cannot drop index needed in a foreign key constraint
Miss_Audrey的博客
06-13 4960
创建好了class表 create table class ( cid int not null unique auto_increment, caption varchar(20) not null, grade_id int not null unique, foreign key (grade_id) references class_grade(gid...
MongoDB深度探索：聚合操作与索引优化实战
"深入解析MongoDB聚合与索引：提升数据库效能的关键策略" MongoDB作为一款流行的文档型数据库，它的聚合操作和索引机制是数据库性能优化的重要工具。聚合操作允许开发人员对数据进行复杂的处理，而索引则能显著提高...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值