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

索引失效、没有充分利用索引 -- 索引建立
关联查询太多JOIN -- SQL优化
服务器调优及各个参数设置(缓冲、线程数等) -- 调整my.cnf
数据过多 -- 分库分表

SQL查询优化大体上可分为物理查询优化和逻辑查询优化

物理查询优化通过索引和表连接方式等技术进行优化
逻辑查询优化通过SQL等价变换提升效率，即换一种查询写法效率可能好点

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;

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 ;

# 用来清楚某个表的所有索引
CALL proc_drop_index("dbname","tablename");

2. 索引失效案例

都是基于成本考虑的！！！！不一定真的失效

MySQL中提高性能最有效的方式之一是对数据表设计合理的索引。索引提供了高效访问数据的方法，并且加快查询的速度。

使用索引可以快速定位表中某条记录，从而提高数据库查询速度，提高性能
如果查询时没有索引，查询语句就会扫描表中所有记录

大多数情况下使用B+树构建索引

其实用不用索引最终是优化器说了算。优化器基于开销进行优化。

2.1 最佳左前缀法则

现在是有索引：idx_age_name_classid

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name = 'a' AND  classid = 4;

并不会使用索引，因为联合索引是从左到右匹配的，先匹配age字段，若筛选条件中没有则直接不使用该索引。

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

我们在NAME字段创建一个普通索引，看看接下来两种SELECT是否能使用索引

CREATE INDEX idx_name ON student(NAME);
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';
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;

2.3 类型转换导致索引失效

name为VARCHAR类型

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

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

CREATE INDEX idx_age_classId_name ON student(age,classId,NAME);

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

只用了 age 和 classid(各占5字节)

范围条件右边指索引的位置，而不是WHERE中的位置，可以试一试下面这个查询：

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

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

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name != 'abc' ;

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

大部分情况下is not null无法使用索引，因为大部分情况下数据都是非空的，通过二级索引找到主键再回表可能还没有直接全表查询快呢

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;

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

EXPLAIN SELECT * FROM student WHERE name LIKE '%a';

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

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

创建age索引，执行语句会发现没有使用到age索引：

CREATE INDEX idx_age ON student(age);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;

因为OR前后存在非索引的列，我添加一个classid索引再试试：

CREATE INDEX idx_cid ON student(classid);

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

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

3. 关联查询优化

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条记录
DELIMITER //
CREATE PROCEDURE `insert_type`() 
BEGIN 
	DECLARE i INT DEFAULT 0 ;
	REPEAT
		SET i = i + 1;
		INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
    UNTIL i = 20
    END REPEAT;
END//
DELIMITER ;

#向图书表中添加20条记录
DELIMITER //
CREATE PROCEDURE `insert_book`() 
BEGIN 
	DECLARE i INT DEFAULT 0 ;
	REPEAT
		SET i = i + 1;
		INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
    UNTIL i = 20
    END REPEAT;
END//
DELIMITER ;
CALL insert_book();
CALL insert_type();

3.2 采用左外连接

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

一开始我们没有任何索引，InnoDB自动在被驱动表上给我们使用join bffer进行优化

我们对被驱动表添加索引后，可以避免全表扫描就不再使用join buffer了

ALTER TABLE book ADD INDEX Y ( card);

rows 优化比较明显。这是由左连接特性决定的。LEFT JOIN条件用于确定如何从右表搜索行，左边一定都有，所以 右边是我们的关键点,一定需要建立索引

我们对驱动表建立连接，但不能避免全表扫描

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

可以试着把索引Y删除再看看结果。

3.3 采用内连接

先把之前的索引删了

DROP INDEX X ON `type`;
DROP INDEX Y ON `book`;
EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card=book.card;

加上 book的索引

ALTER TABLE book ADD INDEX Y ( card);

注意看：现在type是驱动表，book是被驱动表

我们现在加上type的索引

ALTER TABLE type ADD INDEX X (card);

直接两级反转，由于是内连接，驱动和被驱动没有特别的要求，所以查询优化器会根据情况决定谁驱动谁

删除索引Y：

DROP INDEX Y ON book;

对于内连接来讲，如果表的连接条件中只能有一个字段有索引，则有索引的字段所在的表会被作为被驱动表出现。

先把索引X添加回去，向type再添加20条数据看看

ALTER TABLE `book` ADD INDEX Y (card);
CALL insert_type();
EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card=book.card;

对于内连接来说，在两个表的连接条件都存在索引的情况下，会选择小表作为驱动表。小表驱动大表

3.4 join语句原理

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

3.4.1 驱动表和被驱动表

驱动表：主表，被驱动表：从表、非驱动表

EXPLAIN的结果里驱动表在被驱动表上面

对于内连接来说

SELECT * FROM A JOIN ON ...

A 一定是驱动表吗？不一定，优化器会根据查询语句进行优化，决定先查哪张表(驱动表)

对于外连接来说

SELECT * FROM A LEFT/RIGHT JOIN B 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);

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

结果b是驱动表

3.4.2 Simple-Nested-Loop Join 简单嵌套循环连接

从驱动表A中一条条取数据和整个表B进行匹配

开销统计：

外表(A)扫描次数：1次
内表(B)扫描次数：驱动表A的行数
读取记录数：A(读取A的行数)+B*A(A的每一条记录和整个B的每一行匹配)

3.4.3 Index Nested-Loop Join 索引嵌套循环

优化思路主要是为了减少内层表数据的匹配次数，所以要求被驱动表上有索引。通过外层表匹配条件直接与内层表索引进行匹配，大大减少了对内层表的匹配次数。

开销统计：与上一个循环进行对比

外表(A)扫描次数：1次
内表(B)扫描次数：0次，通过索引匹配
读取记录数：A(读取A的行数)+B(match)：B表索引匹配到的总数目
JOIN比较次数：A * Index(Height)：A的所有记录进索引搜索， Index(Height)为B+树的高度
回表记录数：if possible：如果不是主键索引要回表

3.4.4 Block Nested-Loop Join 块嵌套循环连接

如果有索引则使用索引方式进行join。但若是没有索引或索引失效了，被驱动表要扫描的次数太多了。每次访问被驱动表，表中的记录加载到内存，然后从驱动表中取一条记录与之匹配，匹配结束后清除内存。然后再从驱动表中加载一条记录，把被驱动表的记录再加载到内存...大大增加了IO次数，为了减少被驱动表的IO次数，就出现了该方法。

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

注意：

这里缓存的不只是需要匹配的列，SELECT想查询的列也会缓存，所以查询的时候尽量别查不必要的字段

在一个N个Join关联的SQL中会分配N-1个 join buffer

内表扫描次数：used_column_size即注意里说的缓存，*A为一整张表需要进入join buffer的缓存，/join_buffer_size表示需要批量匹配的次数，+1为向上取整，即可能最后join buffer里面还有一小块。
查看block_nested_loop状态，默认开启

SHOW VARIABLES LIKE '%optimizer_switch%';

join_buffer_size：默认为256K

3.4.5 join小结

整体效率比较：INLJ>BNLJ>SNLJ
LEFT JOIN 时，选择小表作为驱动表，大表作为被驱动表。减少外层循环的次数。
INNER JOIN 时，MySQL会自动将小结果集的表选为驱动表。选择相信MySQL优化策略。
永远使用小结果集驱动大结果集，本质就是减少外层循环的数量，小的度量单位是表的行数*每行大小
为被驱动表匹配的条件增加索引，减少内层表的循环匹配(INLJ)
需要JOIN 的字段，数据类型保持绝对一致。
增大 join_buffer_size
减少驱动表不必要的字段查询(字段越少，join buffer缓存的数据越多)
衍生表建不了索引

3.4.6 Hash Join

从MySQL 8.0.20开始废弃了BNLJ，默认为hash join

Nested Loop：对于被连接的数据子集较小的情况下是个较好的选择
Hash join 做大数据集连接时常用方式，优化器用两个表中较小的表利用 join key在内存中建立散列表，然后扫描较大的表并探测散列值，找出与Hash表匹配的行。

4. 子查询优化

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

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

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

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

举例：子查询替换成JOIN

创建班长表中班长的索引：

什么？你问我是哪张表？就是文章开头的那个

CREATE INDEX idx_monitor ON class(monitor);

运用子查询查询班长信息：

EXPLAIN SELECT * FROM student stu1
WHERE stu1.`stuno` IN (
SELECT monitor
FROM class c
WHERE monitor IS NOT NULL
);

还记得我们说的大部分情况下 IS NOT NULL不能用索引吗？

没错，这次举例主要是给你看看能用索引的情况的，转换只是顺带的事。

由于子查询中SELECT 只查询 monitor 而我们WHERE的条件也是monitor，形成了索引覆盖，即不需要回表，在二级索引的B+树上查询到直接返回就好了。

替换成 JOIN：

EXPLAIN SELECT stu1.* FROM student stu1 JOIN class c 
ON stu1.`stuno` = c.`monitor`
WHERE c.`monitor` IS NOT NULL;

5. 排序优化

5.1 排序优化

问题：在 WHERE 条件字段上加索引，但是为什么在 ORDER BY 字段上还要加索引呢？

回答：MySQL中支持两种排序方式:FileSort和Index

Index排序中，索引可以保证数据的有序性，不需要再进行排序,效率更高
FileSort排序一般在内存中进行排序，占用CPU较多，如果待排序结果较大，会产生历史文件IO到磁盘进行排序的情况，效率更拉

优化建议：

SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中避免全表扫描，在 ORDER BY 子句 避免使用 FileSort 排序 。
尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列；如果不同就使用联合索引。
无法使用 Index 时，需要对 FileSort 方式进行调优。

5.2 实战案例

先清除所有索引

call proc_drop_index('你的库名','student');

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

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

没有索引，用的是 filesort

查询看看速度

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

0.154s，其实也不慢，但type是最差的 ALL，并且还用了说要避免的filesort,还是优化下吧

对age和name的联合索引

CREATE INDEX idx_age_name ON student(age,NAME);

只用了联合索引的 age，已经没有使用filesort了

查询下看看：也是快了不少

我们把那个索引删了，重新创建一个

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

发现 filesort又回来了：

运行下试试：

结果发现有 filesort的运行速度超过了已经优化掉filesort的查询。

原因：

所有的排序都是在条件过滤后才执行的。所以如果过滤大部分数据的话，剩下几百几千条数据进行排序其实不少很消耗性能。相对的stuno<101000这个条件筛选掉的记录特别多，所以索引放这个字段上性价比极高。

结论：

两个索引同时存在，mysql自动选择最优的方案。但是随着数据量的变化，选择的索引也会随之变化的

2.当【范围条件】和【group by 或者 order by】的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。

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

双路排序（慢）

MySQL 4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据。从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。

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

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

结论及引申出的问题

由于单路是后面出的，总体而言好过双路
但是用单路有问题
- 在sort_buffer中，单路比多路要多占用很多空间，甚至有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序，排完再取sort_buffer容量大小，再排......从而多次I/O。
- 单路本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。

优化策略

1. 尝试提高 sort_buffer_size

SHOW VARIABLES LIKE '%sort_buffer_size%';

默认情况下InnoDB的 sort_buffer_size 为 1M，我们可以自行更改

2. 尝试提高 max_length_for_sort_data

SHOW VARIABLES LIKE '%max_length_for_sort_data%';

如果需要返回的记录的总长度 大于 max_length_for_sort_data则用双路排序，否则用单路

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

当Query的字段大小总和小于max_length_for_sort_data，而且排序字段不是TEXT|BLOB类型时，会用改进后的算法―-单路排序，否则用老算法―-多路排序。
两种算法的数据都有可能超出sort_buffer_size的容量，超出之后，会创建tmp文件进行合并排序，导致多次/o，但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size。

6. 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这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。

7. 优化分页查询

从第200000条记录的位置取10个，你说气不气人

EXPLAIN SELECT * FROM student LIMIT 2000000, 10;

优化思路一

在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。

EXPLAIN SELECT * FROM student t,
(SELECT id FROM student ORDER BY id LIMIT 2000000,10) a
WHERE t.id = a.id;

优化思路二

该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询。

EXPLAIN SELECT * FROM student WHERE id > 2000000 LIMIT 10;

8. 优先考虑覆盖索引

8.1 什么是覆盖索引？

简单说就是， 索引列+主键 包含 SELECT 到 FROM之间查询的列 。

8.2 覆盖索引的利弊

好处：

1. 避免Innodb表进行索引的二次查询（回表）

2. 可以把随机IO变成顺序IO加快查询效率

由于覆盖索引是按键值的顺序存储的，对于I0密集型的范围查找来说，对比拿取二级索引的数据后回表查询数据(数据可能不在一起)随机从磁盘读取每一行的数据I0要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的IO转变成索引查找的顺序I0。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

弊端：

索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。

9. 索引条件下推

Index Condition Pushdown(ICP)是MySQL 5.6中新特性，是一种在存储引擎层使用索引过滤数据的优化方式。ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数。

还记不记得我们在分析EXPLAIN各字段意义的时候，Extra字段里有一个Using index condition，没错，这就是用到了索引下推

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%b';

有一个关于key1的索引，但是由于LIKE'%b'导致后半部分索引失效，但由于我们两个筛选条件都是关于索引key1的，可以在找到key1>'z'的条件后，回表前先顺带检查一下key1是不是LIKE'%b'。这就是索引下推

9.1 使用前后的扫描过程

在不使用ICP索引扫描的过程：

storage层：只将满足index key条件的索引记录对应的整行记录取出，返回给server层

server 层：对返回的数据，使用后面的where条件过滤，直至返回最后一行。

使用ICP扫描的过程：

storage层：首先将index key条件满足的索引记录区间确定，然后在索引上使用index filter进行过滤。将满足的index filter条件的索引记录才去回表取出整行记录返回server层。不满足index filter条件的索引记录丢弃，不回表、也不会返回server层。

server 层：对返回的数据，使用table filter条件做最后的过滤。

使用前后的成本差别

使用前，存储层多返回了需要被index filter过滤掉的整行记录

使用ICP后，直接就去掉了不满足index filter条件的记录，省去了他们回表和传递到server层的成本。

ICP的 加速效果 取决于在存储引擎内通过 ICP筛选 掉的数据的比例。

9.2 ICP的开启/关闭

SET optimizer_switch = ON/OFF

9.3 ICP使用案例

准备数据：

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 `zip_last_first` (`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', '000001', '六', '赵', '天津市');

我们有这个KEY zip_last_first (zipcode,lastname,firstname)，对zipcode、lasatname、address进行一次explain：

EXPLAIN SELECT * FROM people
WHERE zipcode='000001' AND lastname LIKE '%张%' AND address LIKE '%北京市%';

首先可以看到语句中zipcode和lastname刚刚好满足索引条件，但是lastname以通配符开头导致索引失效，所以只用了联合索引中的zipcode(key_len = 63= VARCHAR长度20 * 一个字符占字节数3 + 3NULL和变长需要的空间)。由于索引中有lastname，可以在回表前先检查下是否满足LIKE '%张%'条件再回表。

8.4 开启和关闭ICP对比

创建存储过程，向people表中添加1000000条数据，测试ICP开启和关闭状态下的性能：
主要目的就是插入很多00001的数据，这样查询的时候我为了在存储引擎做过滤，减少IO，也为了减少缓冲池的作用。

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', '六', '赵', '天津市');  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT; 
END //
DELIMITER ;

CALL insert_people(1000000);

SELECT   * FROM people WHERE zipcode='000001' AND lastname LIKE '%张%';

不用索引下推可以用上面的关闭方法或直接在查询语句中：

SELECT /*+ no_icp (people) */ * FROM people WHERE zipcode='000001' AND lastname LIKE '%张%';

慢多了

9.5 小结

ICP用于二级索引再次筛选掉不能用索引的有关条件，从而减少回表的IO操作。

10. 其它查询优化策略

10.1 EXISTS 和 IN 的区分

索引是个前提，其实选择与否还会要看表的大小。你可以将选择的标准理解为小表驱动大表。

10.2 COUNT(*)与COUNT(具体字段)效率

环节1：COUNT(*)和COUNT(1)本质上并没有区别（二者执行时间可能略有差别，不过你还是可以把它俩的执行效率看成是相等的）。

环节2： 如果是MyISAM存储引擎，统计数据表的行数只需要O(1)的复杂度，这是因为每张MyISAM的数据表都有一个meta信息存储了row_count值，而一致性则是由表级锁来保证的。

如果是InnoDB存储引擎，因为InnoDB支持事务，采用行级锁和MVCC机制，所以无法像MyISAM一样，维护一个row_count变量，因此需要采用扫描全表，是O(n)的复杂度，进行循环+计数的方式来完成统计。

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

如果有多个二级索引，会使用key_len小的二级索引进行扫描。当没有二级索引的时候，才会采用主键索引来进行统计。

10.3 关于SELECT(*)

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

① MySQL 在解析的过程中，会通过查询数据字典将"*"按序转换成所有列名，这会大大的耗费资源和时间。

② 无法使用覆盖索引

10.4 LIMIT 1 对优化的影响

针对的是会扫描全表的 SQL 语句，如果你可以确定结果集只有一条，那么加上LIMIT 1的时候，当找到一条结果的时候就不会继续扫描了，这样会加快查询速度。

如果数据表已经对字段建立了唯一索引，那么可以通过索引进行查询，不会全表扫描的话，就不需要加上LIMIT 1了。

10.5 多使用COMMIT

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

COMMIT 所释放的资源：

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

11. 淘宝数据库，主键如何设计的？

聊一个实际问题：淘宝的数据库，主键是如何设计的？

大部分人的回答如此自信：用8字节的 BIGINT 做主键，而不要用INT。错！

这样的回答，只站在了数据库这一层，而没有 从业务的角度 思考主键。主键就是一个自增ID吗？用自增做主键，架构设计上可能 连及格都拿不到 。

11.1 自增ID的问题

自增ID除了简单外几乎都是缺点：

1. 可靠性不高
存在自增ID回溯的问题，这个问题直到最新版本的MySQL 8.0才修复。

2. 安全性不高
对外暴露的接口可以非常容易猜测对应的信息。比如：/User/1/这样的接口，可以非常容易猜测用户ID的值为多少，总用户数量有多少，也可以非常容易地通过接口进行数据的爬取。

3. 性能差
自增ID的性能较差，需要在数据库服务器端生成。

4. 交互多
业务还需要额外执行一次类似 last_insert_id() 的函数才能知道刚才插入的自增值，这需要多一次的网络交互。在海量并发的系统中，多1条SQL，就多一次性能上的开销。

5. 局部唯一性
最重要的一点，自增ID是局部唯一，只在当前数据库实例中唯一，而不是全局唯一，在任意服务器间都是唯一的。对于目前分布式系统来说，这简直就是噩梦。

11.2 业务字段做主键

会员卡号
卡号可能存在重复使用的情况。比如，张三不再到商家的门店消费了(退还了会员卡)。但是商家不想让这个会员卡空着，就把卡号是10000001的会员卡发给了王五。
从系统设计的角度看，这个变化只是修改了会员信息表中的卡号是“10000001”这个会员信息，并不会影响到数据一致性。因此，从 信息系统层面 上看是没问题的。
但是从使用 系统的业务层面 来看，就有很大的问题，会对商家造成影响。
比如，我们有一个销售流水表（trans），记录了所有的销售流水明细。2020 年 12 月 01 日，张三在门店购买了一本书，消费了 89 元。那么，系统中就有了张三买书的流水记录。
而会员卡变为王五的后，该记录却变成王五的了。
选择会员电话或身份证号
电话可能会变，身份证用户可能不愿意给

11.3 淘宝的主键设计

从上图可以发现，订单号不是自增ID。我们详细看下上述3个订单号：

1550672064762308113
1481195847180308113
1431156171142308113

订单号是19位的长度，且订单的最后5位都是一样的，都是08113。且订单号的前面14位部分是单调递增的。

大胆猜测，淘宝的订单ID设计应该是：

订单ID = 时间 + 去重字段 + 用户ID后6位尾号

11.4 推荐的主键设计

非核心业务 ：对应表的主键自增ID，如告警、日志、监控等信息。

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

这里推荐最简单的一种主键设计：UUID。

UUID的特点： 全局唯一，占用36字节，数据无序，插入性能差。

MySQL数据库的UUID组成如下所示：

UUID = 时间+UUID版本（16字节）- 时钟序列（4字节） - MAC地址（12字节）

我们以UUID值e0ea12d4-6473-11eb-943c-00155dbaa39d举例：

为什么UUID是全局唯一的？

在UUID中时间部分占用60位，时间维度发生重复的概率为1/100ns。

时钟序列是为了避免时钟被回拨导致产生时间重复的可能性。MAC地址用于全局唯一。

为什么UUID占用36个字节？

UUID根据字符串进行存储，设计时还带有无用"-"字符串，因此总共需要36个字节。

为什么UUID是随机无序的呢？

因为UUID的设计中，将时间低位放在最前面，而这部分的数据是一直在变化的，并且是无序。

改造UUID

若将时间高低位互换，则时间就是单调递增的了，也就变得单调递增了。MySQL8.0可以更换时间低位和时间高位的存储方式，这样UUID就是有序的UUID了。

MySQL 8.0还解决了UUID存在的空间占用的问题，除去了UUID字符串中无意义的"-"字符串，并且将字符串用二进制类型保存，这样存储空间降低为了16字节。

SELECT UUID() FROM DUAL;

SET @uuid = UUID();

SELECT @uuid,uuid_to_bin(@uuid),uuid_to_bin(@uuid,TRUE);

有序UUID性能测试

我们来做一个测试，插入1亿条数据，每条数据占用500字节，含有3个二级索引，最终的结果如下所示：

从上图可以看到有序UUID是最快的，而且在实际业务使用中有序UUID在业务端就可以生成。还可以进一步减少SQL的交互次数。另外，虽然有序UUID相比自增ID多了8个字节，但实际只增大了3G的存储空间，还可以接受。

如果不是MySQL8.0 肿么办？

手动赋值字段做主键！

比如，设计各个分店的会员表的主键，因为如果每台机器各自产生的数据需要合并，就可能会出现主键重复的问题。

可以在总部 MySQL 数据库中，有一个管理信息表，在这个表中添加一个字段，专门用来记录当前会员编号的最大值。

门店在添加会员的时候，先到总部 MySQL 数据库中获取这个最大值，在这个基础上加 1，然后用这个值作为新会员的“id”，同时，更新总部 MySQL 数据库管理信息表中的当前会员编号的最大值。

这样一来，各个门店添加会员的时候，都对同一个总部 MySQL 数据库中的数据表字段进行操作，就解决了各门店添加会员时会员编号冲突的问题

进阶篇(10) 索引优化与查询优化