一、索引优化

1.1、数据准备

CREATE DATABASE test23;
USE test23;

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) 
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

set global log_bin_trust_function_creators=1;

-- #随机产生字符串
DELIMITER // 

CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255) 
BEGIN 
	DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; 
	DECLARE return_str VARCHAR(255) DEFAULT ''; 
	DECLARE i INT DEFAULT 0; 
	
	WHILE i < n DO 
		SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1)); 
		SET i = i + 1; 
	END WHILE;
	
	RETURN return_str; 
END // 

DELIMITER ;

-- 随机产生班级编号
DELIMITER // 

CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11) 
BEGIN 
	DECLARE i INT DEFAULT 0; 
	
	SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ; 
	
	RETURN i; 
	
END // 

DELIMITER ;

-- 创建往stu表中插入数据的存储过程
DELIMITER // 

CREATE PROCEDURE insert_stu( START INT , max_num INT ) 
BEGIN 
	DECLARE i INT DEFAULT 0; 
	SET autocommit = 0; 
	
	REPEAT 
		SET i = i + 1;
		INSERT INTO student (stuno, name ,age ,classId ) VALUES ((START+i),rand_string(6),rand_num(1,50),rand_num(1,1000)); 
		UNTIL i = max_num 
	END REPEAT; 
	
	COMMIT;  
		
END // 

DELIMITER ;

-- 创建往class表中插入数据的存储过程
DELIMITER // 

CREATE PROCEDURE insert_class( max_num INT ) 
BEGIN 
	DECLARE i INT DEFAULT 0; 
	SET autocommit = 0; 
	
	REPEAT 
		SET i = i + 1; 
		INSERT INTO class ( classname,address,monitor ) VALUES (rand_string(8),rand_string(10),rand_num(1,100000)); 
		UNTIL i = max_num 
	END REPEAT; 
	
	COMMIT;
	
END //

DELIMITER ;

CALL insert_class(10000);
CALL insert_stu(100000,500000);

-- 删除某表上的索引
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 ;

1.2、索引失效的情景

• 全值匹配
• 最佳左前缀法则
  • MySQL可以为多个字段创建索引，一个索引可以包括16个字段。对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。如果查询条件中没有使用这些字段总第1个字段是，多列（或联合）索引不会被使用。
• 主键插入顺序
  • 对于一个使用InnoDB存储引擎的表来说，在我们没有显示的创建索引时，表中的数据实际上都是存储在聚簇索引的叶子节点的。而记录又是存储在数据页中的，数据页和记录又是按照记录主键值从小到大的顺序进行排序，所以如果我们插入的记录的主键值是依次增大的话，那我们每插满一个数据页就换到下一个数据页继续插，而如果我们插入的主键值忽大忽小的话，就比较麻烦，假设某个数据页存储的记录已经满了，如果此时插入的数据正好在这个数据页中，就会造成页面分裂和记录移位，会造成性能损耗。
• 计算、函数、类型转换（自动或手动）导致索引失效
• 范围条件右边的列索引失效
• 不等于(!= 或者<>)索引失效
• IS NULL 可以使用索引，IS NOT NULL无法使用索引
• LIKE以通配符%开头索引失效
• OR 前后存在非索引的列，索引失效
• 数据库和表的字符集统一使用utf8mb4
  • 统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的字符集进行比较前需要进行转换会造成索引失效。

-- 全值匹配
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';

CREATE INDEX index_age ON student(age);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND name='abcd';

CREATE INDEX index_age_classId ON student(age,classId);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND name='abcd';

CREATE INDEX index_age_classId_name ON student(age,classId,name);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND name='abcd';

-- 最佳左前缀法则
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND name='abcd';
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE classId=1 AND name='abcd';
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE classId=4 AND age=30 AND name='abcd';

-- 计算、函数、类型转换（自动或手动）导致索引失效
-- 函数导致索引失效
CREATE INDEX index_name ON student(name);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name LIKE 'abc%';
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(name,3) = 'abc';
-- 计算导致索引失效
CREATE INDEX index_stuno ON student(stuno);
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 = 90000;
-- 类型转换导致索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name = 123;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name = '123';

-- 范围条件右边的列索引失效
CALL proc_drop_index('test23','student');
SHOW INDEX FROM student;
CREATE INDEX index_age_classId_name ON student(age,classId,name);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId>20 AND name='abc';
CREATE INDEX index_age_name_classId ON student(age,name,classId);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND name='abc' AND classId>20;

-- 不等于(!= 或者<>)索引失效
CREATE INDEX index_name ON student(name);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name = 'abc';
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name <> 'abc';

-- IS NULL 可以使用索引，IS NOT NULL无法使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;

--  LIKE以通配符%开头索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name LIKE 'ab%';
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name LIKE '%ab';

-- OR 前后存在非索引的列，索引失效
CALL proc_drop_index('test23','student');
SHOW INDEX FROM student;
CREATE INDEX index_age ON student(age);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=10 OR classId=100;
CREATE INDEX index_classId ON student(classId);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=10 OR classId=100;

二、关联查询优化

2.1、数据准备

USE test23;

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

delimiter //

CREATE PROCEDURE ready()
BEGIN
	DECLARE i INT DEFAULT 0; 
	
	WHILE i<20 DO
		INSERT INTO type(card) VALUES(FLOOR(1+(RAND()*20)));
		INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
		SET i = i+1;
	END WHILE;

END	//

delimiter ;

CALL ready();

2.2、外连接

EXPLAIN SELECT SQL_NO_CACHE * FROM type LEFT JOIN book ON type.card = book.card;
-- 添加索引
CREATE INDEX index_book_card ON book(card);
EXPLAIN SELECT SQL_NO_CACHE * FROM type LEFT JOIN book ON type.card = book.card;
CREATE INDEX index_type_card ON type(card);
EXPLAIN SELECT SQL_NO_CACHE * FROM type LEFT JOIN book ON type.card = book.card;
-- 删除被驱动表的索引
DROP INDEX index_book_card ON book;
EXPLAIN SELECT SQL_NO_CACHE * FROM type LEFT JOIN book ON type.card = book.card;

2.3、内连接

DROP INDEX index_type_card ON type;
EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card = book.card;
-- 添加索引
CREATE INDEX index_book_card ON book(card);
EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card = book.card;
CREATE INDEX index_type_card ON type(card);
-- 对于内连接来说，查询优化器可以决定谁作为驱动表，谁作为被驱动表出现的
EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card = book.card;
-- 删除被驱动表的索引
DROP INDEX index_book_card ON book;
-- 对于内连接来讲，如果表的连接条件中只能有一个字段有索引，则有索引的字段所在的表会被作为被驱动表出现
EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card = book.card;

CREATE INDEX index_book_card ON book(card);
EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card = book.card;

-- 向驱动表添加数据
INSERT INTO type(card) VALUES(FLOOR(1+(RAND()*20)));
-- 对于内连接来说，在两个表的连接条件都存在索引的情况下，会选择数据少的表作为驱动表
EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card = book.card;

2.4、JOIN语句的原理

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

2.4.1、驱动表和被驱动表

• 驱动表就是主表，被驱动表就是从表、非驱动表、
• 对于内连接，SELECT * FROM A JOIN B ON...
  • 优化器会根据查询语句做优化，决定先查哪张表。先查询的那张表就是驱动表。反之，就是被驱动表。可以通过EXPLAIN关键字查看
• 对于外连接，SELECT * FROM A LEFT JOIN B ON... 或 SELECT * FROM B RIGHT JOIN A ON...
  • 通常会认为A就是驱动表，B就是被驱动表。但也存在不一定的情况

USE test23;

CREATE TABLE a(f1 INT,f2 INT,INDEX (f1));
CREATE TABLE b(f1 INT,f2 INT);

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

EXPLAIN SELECT * FROM a JOIN b ON a.f1 = b.f1 WHERE a.f2=b.f2;
EXPLAIN SELECT * FROM a LEFT JOIN b ON a.f1=b.f1 WHERE a.f2=b.f2;
EXPLAIN SELECT * FROM a LEFT JOIN b ON a.f1 = b.f1 AND a.f2 = b.f2;

2.4.2、Simple Nested-Loop Join（简单嵌套循环连接）

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

开销统计	SNLJ
外表扫描次数	1
内标扫描次数	A
读取记录数	A+A*B
JOIN比较次数	B*A
回表读取记录次数	0

2.4.3、Index Nested-Loop Join（索引嵌套循环连接）

  Index Nested-Loop Join 其优化的思路主要是为了减少内层表数据的匹配次数，所以要求被驱动表上必须有索引才行。通过外层表匹配条件直接与内层表索引进行匹配，避免和内层表的每条记录去进行比较，这样极大的减少了对内层表的匹配次数。驱动表中的每条记录通过被驱动表的索引进行访问，因为索引查询的成本是比较固定的，故MySQL优化器都倾向于使用记录数少的表作为驱动表（外表）如果被驱动表添加索引，效率是非常高的，但如果索引不是主键索引，所以还得进行一次回表查询。相比，被驱动表得索引是主键索引，效率会更高。

开销统计	INLJ
外表扫描次数	1
内标扫描次数	0
读取记录数	A+B(match)
JOIN比较次数	A*Index(Height)
回表读取记录次数	B(match)(if possible)

2.4.3、Block Nested-Loop Join（块嵌套循环连接）

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

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

开销统计	BNLJ
外表扫描次数	1
内标扫描次数	A*used_column_size/join_buffer_size+1
读取记录数	A+B(Aused_column_size/join_buffer_size)
JOIN比较次数	B*A
回表读取记录次数	0

2.4.5、小结

• 整体效率比较：INLJ > BNLJ > SNLJ
• 永远用小结果集驱动大结果集，其本质就是减少外层循环的数据数量
  • 小的度量单位指的是 表行数*每行大小
• 为被驱动表匹配的条件增加索引，减少内层表的循环匹配次数
• 增大join buffer size的大小，一次缓存的数据越多，那么内层表的扫描次数就越少
• 减少驱动表不必要的字段查询，字段越少，join buffer所缓存的数据就越多

2.4.6、MySQL的新特性

  Hash Join是做大数据集连接时的常用方式，优化器使用两个表中较小（相对较小）的表利用Join Key在内存中建立散列表，然后扫描较大的表并探测散列表，找出与Hash表匹配的行。这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成本之和。在表很大的情况下，并不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要求有较大的临时段从而尽量提高I/O的性能。它能够很好的工作于没有索引的大表和并行查询的环境中，并提供最好的性能。Hash Join只能应用于等值连接，这是由Hash的特点决定的。

类别	Nested Loop	Hash Join
使用条件	任何条件	等值连接
相关资源	CPU、磁盘I/O	内存、临时空间
特点	当由高选择性索引或进行限制性搜索时效率比较高，能够快速返回第一次的搜索结果	当缺乏索引或者索引条件模糊时，Hash Join比Nested Loop有效。在数据仓库环境下，如果表的记录数多，效率高
缺点	当索引丢失或者查询条件限制不够时，效果很低；当表的记录数多时，效率低	为建立哈希表，需要大量内存。第一次的结果返回较慢

三、子查询优化

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

• 执行子查询时，MySQL 需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表 。这样会消耗过多的CPU和IO资源，产生大量的慢查询。
• 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。
• 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

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

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

四、排序优化

  在 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 方式进行调优。

USE test23;

CALL proc_drop_index('test23', 'student');
CALL proc_drop_index('test23', 'class');
SHOW INDEX FROM student;
SHOW INDEX FROM class;

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

CREATE INDEX index_age_classId_name ON student(age,classId,name);
-- ORDER BY 时不使用 LIMIT 索引失效（需要回表操作）
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classId;
-- ORDER BY 时不使用 LIMIT 索引可以使用（不需要回表操作）
EXPLAIN SELECT SQL_NO_CACHE age,classId FROM student ORDER BY age,classId;
-- 增加 LIMIT 条件，使用上索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classId LIMIT 10;
-- ORDER BY 时规则不一致，索引失效（顺序错，不索引；方向反，不索引）
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY classId,age;
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age ASC,classId DESC LIMIT 10;
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age DESC,classId DESC LIMIT 10;

4.1、filesort算法

  排序的字段若如果不在索引上，曾filesort会有两种算法：双路排序和单路排序

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

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

  在sort_buffer中，单路比多路多占用很多空间，因为单路是把所有字段都取出来，所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再去sort_buffer容量大小，在排……从而多次I/O。单路本来向省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。

优化策略

• 尝试提高sort_buffer_size
  • 不管使用哪种算法，提高这个参数都会提高效率，要根据系统的能力去提高，因为这个参数是针对每个进程（connection）的1M~8M之间调整。
• 尝试提高max_length_for_sort_data
  • 提高这个参数，会增加用改进算法的概率。但是如果设置的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率。如果需要返回列的总长度大于max_length_for_sort_data，使用双路算法，否则使用单路算法。
• ORDER BY时SELECT * 是一个大忌，最好只Query需要的字段
  • 当Query的字段大小总合小于max_length_for_sort_data，而且排序字段不是TEXT|BLOB类型时，会用改进后的算法——单路排序，否则用老算法——多路排序

五、GROUP BY优化

• GROUP BY 使用索引的原则几乎跟 ORDER BY 一致 ，GROUP BY 即使没有过滤条件用到索引，也可以直接使用索引。
• GROUP BY 先排序再分组，遵照索引建的最佳左前缀法则
• 当无法使用索引列，增大max_length_for_sort_data和sort_buffer_size参数的设置
• WHERE 效率高于 HAVING，能写在 WHERE 限定的条件就不要写在 HAVING 了
• 减少使用 ORDER BY，能不排序就不排序，或将排序放到程序端去做。
• 包含了ORDER BY、GROUP BY、DISTINCT 这些查询的语句，WHERE 条件过滤出来的结果集请保持在1000行以内，否则 SQL 会很慢。

六、覆盖索引

6.1、覆盖索引概述

  索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那它不需要读取行了。一个索引包含了满足查询结果的数据就叫做覆盖索引。覆盖索引是非聚簇复合索引的一种形式，它包括在查询里面的 SELECT、JOIN 和 WHERE 子句用到的所有列（即建索引的字段正好是覆盖查询条件中所涉及的字段）。简单说就是索引列 + 主键 包含 SELECT 到 FROM 之间查询的列。

USE test23;

CALL proc_drop_index('test23', 'student');
CREATE INDEX index_age_name ON student(age,name);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age <> 20;
EXPLAIN SELECT SQL_NO_CACHE age,name FROM student WHERE age <> 20;

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

6.2、覆盖索引的利弊

• 好处
  • 避免Innodb表进行索引的二次查询（回表）
    • InnoDB是以聚簇索引的顺序来存储的，对于InnoDB来说，二级索引在叶子节点中所保存的是行的主键信息，如果使用二级索引查询数据，在查找到相应的键值后，还需要主键进行二次查询才能获取我们真实所需要的数据。
    • 在覆盖索引中，二级索引的键值中可以获取所要的数据，避免了对主键的二次查询，减少了IO操作，提升了查询效率。
  • 可以把随机IO变成顺序IO加快查询效率。
    • 由于覆盖索引是按键值的顺序存储的，对于IO密集型的范围查找来说，对比随机从磁盘读取每一行的数据IO要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的IO转变成索引查找的顺序IO。
• 缺点
  • 索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。

七、索引条件下推

7.1、索引条件下推概述

  Index Condition Pushdown(ICP) 是 MySQL 5.6 中新特性，是一种在存储引擎使用索引过滤数据的优化方式。如果没有 ICP，存储引擎会遍历索引以定位基表中的行，并将它们返回给 MySQL 服务器，由 MySQL 服务器评估 WHERE 后面的条件是否保留行。启动 ICP 后，如果部分 WHERE 条件可以仅使用索引中的列进行筛选，则 MySQL 服务器会把这部分 WHERE 条件放到存储引擎筛选。然后，存储引擎通过使用索引条目来筛选数据，并且只有在满足这一条件时才能表中读取行。ICP 可以减少存储引擎必须访问基表的次数和 MySQL 服务器必须访问存储引擎的次数。但是，ICP 的加速效果取决于在存储引擎内通过 ICP 筛选掉的数据的比例。

7.2、ICP的开启/关闭

  默认情况下启动索引条件下推。可以通过设置系统变量 optimizer_swith 控制 iindex_condition_pushdown 。当使用索引条件下推是 EXPLAIN语句输出结果中 Extra 列结果内容显示为 Using index condition.

-- 打开索引下推
SET optimizer_switch = 'index_condition_pushdown=on';
-- 关闭索引下推
SET optimizer_switch = 'index_condition_pushdown=off';

7.3、ICP使用案例

USE test23;

CREATE TABLE people(
	id INT NOT NULL auto_increment,
	zipcode VARCHAR(20) COLLATE utf8_bin DEFAULT NULL,
	firstname VARCHAR(20) COLLATE utf8_bin DEFAULT NULL,
	lastname VARCHAR(20) COLLATE utf8_bin DEFAULT NULL,
	address VARCHAR(50) COLLATE utf8_bin DEFAULT NULL,
	PRIMARY KEY(id),
	KEY index_zipcode_lastname_firstname(zipcode,lastname,firstname)
) ENGINE = INNODB auto_increment = 5 DEFAULT CHARSET = utf8mb3 COLLATE = utf8_bin;

INSERT INTO people 
VALUES('1','000001','三','张','北京市'),
			('2','000002','四','李','上海市'),
			('3','000003','五','王','广州市'),
			('4','000004','六','赵','深圳');
			
EXPLAIN SELECT * FROM people WHERE zipcode = '000001' AND lastname LIKE '%张%' AND address LIKE '%北京%';

7.4、ICP的使用条件

• 如果表访问的类型为 range、ref、eq_ref 和 ref_or_null 可以使用 ICP
• ICP 可以用于 InnoDB 和 MyISAM 表，包括分区表 InnoDB 和 MyISAM 表
• 对于 InnoDB 表，ICP 仅用于 二级索引。ICP的目标是减少全行读取次数，从而减少I/O操作
• 当 SQL 使用覆盖索引是，不支持 ICP。因为这种情况下使用 ICP 不会减少 I/O
• 相关子查询的条件不能使用 ICP

7.5、开启和关闭ICP的性能对比

delimiter //

CREATE PROCEDURE insert_people(max_num INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	
	SET autocommit = 0;
	
	REPEAT
		SET i = i + 1;
		INSERT INTO people(zipcode,firstname,lastname,address) VALUES('000001','sakura','kinomoto','友枝町');
		UNTIL i = max_num  
	END REPEAT;

	COMMIT;
END //

delimiter ;

CALL insert_people(1000000);

-- 打开profiling工具
SET profiling = 1;
-- 默认打开索引下推
SELECT * FROM people WHERE zipcode = '000001' AND lastname LIKE '%张%';
-- 不使用索引下推
SET optimizer_switch = 'index_condition_pushdown=off';
SELECT * FROM people WHERE zipcode = '000001' AND lastname LIKE '%张%';

SHOW profiles;

八、其它查询优化策略

8.1、EXISTS 和 IN 的区分

  索引是个前提，其实选择与否还是要看表的大小，我们可以将选择的标准理解为小标驱动大表。在这种方式下效率是最高的。

SELECT * FROM A WHERE cc IN (SELECT cc FROM B);
SELECT * FROM A WHERE EXISTS(SELECT cc FROM B WHERE B.cc = A.cc);

  当 A 小于 B 时，用 EXISTS。因为 EXISTS 的实现，相当于外表循环，实现的逻辑类似于：

for i in A
	for j in B
		if j.cc == i.cc 
			then ...

  当 B 小于 A 时用 IN，因为实现的逻辑类似于：

for i in B
	for j in A
		if j.cc == i.cc 
			then ...

  哪个表小就用哪个表来驱动，A 表小就用 EXISTS，B 表小就用 IN。

8.2、COUNT(*) 和 COUNT(具体字段) 效率

  前提：统计的具体字段非空

  COUNT(*) 和 COUNT(1) 都是对所有结果进行 COUNT，COUNT(*) 和 COUNT(1) 本质上并没有什么区别（二者执行时间可能略有差别）。如果有 WHERE 子句，则是对所有符合筛选条件的数据进行统计；如果没有 WHERE 子句，则是对数据表的数据行数进行统计。

  如果是 MyISAM 存储引擎，统计数据表的行数只需要 O(1) 的复杂度，这是因为每张 MyISAM 的数据表都有一个 meta 信息存储了 row_count 值，而一致性则由表级锁来保证。如果是 InnoDB 存储引擎 ，因为支持事务，采用行级锁和 MVCC 机制，所以无法像 MyISAM 的数据表一样，维护一个 row_count 变量，因此需要采用扫描全表，是O(N)的复杂度，进行循环 + 计数的方式来完成统计。

  在 InnoDB 引擎中，如果采用 COUNT(具体字段) 来统计数据行数，要尽量采用二级索引。因为主键采用的索引是聚簇索引，聚簇索引包含的信息多，明显会大于二级索引（非聚簇索引）。对于 COUNT(*) 和 COUNT(1) 来说，它们不需要查找具体的行，只是统计行数，系统会自动采用占用空间更小的二级索引来进行统计。如果有多个二级索引，会使用 key_len 小的二级索引进行扫描。当没有二级索引的时候，才会采用主键索引来进行统计。

8.3、关于 SELECT(*)

  在表查询中，建议明确字段，不要使用 * 作为查询的字段列表，推荐使用SELECT <字段列表> 查询。原因：

• MySQL 在解析的过程中，会通过 查询数据字典 将"*"按序转换成所有列名，这会大大的耗费资源和时间
• 无法使用覆盖索引

8.4、LIMIT 1 对优化的影响

  针对的是会扫描全表的 SQL 语句，如果你可以确定结果集只有一条，那么加上 LIMIT 1 的时候，当找到一条结果的时候就不会继续扫描了，这样会加快查询速度。如果数据表已经对字段建立了唯一索引，那么可以通过索引进行查询，不会全表扫描的话，就不需要加上 LIMIT 1 了。

8.5、多使用COMMIT

  只要有可能，在程序中尽量多使用 COMMIT，这样程序的性能得到提高，需求也会因为 COMMIT 所释放的资源而减少。

COMMIT 所释放的资源：

• 回滚段上用于恢复数据的信息
• 被程序语句获得的锁
• redo / undo log buffer 中的空间
• 管理上述 3 种资源中的内部花费

九、主键如何设计

9.1、自增 ID 的问题

• 可靠性不高
  • 存在自增ID回溯的问题
• 安全性不高
  • 对外暴露的接口可以非常容易猜测对应的信息。
• 性能差
  • 自增ID的性能较差，需要在数据库服务器端生成。
• 交互多
  • 业务还需要额外执行一次类似 last_insert_id() 的函数才能知道刚才插入的自增值，这需要多一次的网络交互。在海量并发的系统中，多1条SQL，就多一次性能上的开销。
• 局部唯一性
  • 自增ID是局部唯一，只在当前数据库实例中唯一，而不是全局唯一，在任意服务器间都是唯一的。

9.2、推荐的主键设计

• 非核心业务：对应表的主键自增ID
• 核心业务 ：主键设计至少应该是全局唯一且是单调递增。全局唯一保证在各系统之间都是唯一的，单调递增是希望插入时不影响数据库性能。