2020-02-10数据库19 分钟读完 (大约 2907 个字)

MySQL/面试题：如何调优 SQL

todo：应该参考陈阳的课程做一个系统的回答

SQL 的调优大致步骤如下：

根据慢日志定位慢查询SQL

MySql 中有很多系统变量，可以通过查询一些变量来获得 MySql 的一些配置信息。我们可以通过以下模糊查询语句来获得包含 query (SQL 查询)有关的系统变量。

1	SHOW VARIABLES LIKE '%query%';

其中我们关心的变量有三个：long_query_time，slow_query_log，slow_query_log_file。slow_query_log的取值有ON和OFF，表示是否开启慢查询日志，默认是OFF，不开启慢查询日志记录。long_query_time决定了一条查询需要超过多少时间才会被认为是慢查询，单位是秒。slow_query_log_file则指定慢查询日志的存放位置。

我们需要把slow_query_log的值改为ON，同时把long_query_time改为1，这样如果一条 SQL 语句执行超过1秒，就会被认为是慢查询，被记录到慢查询日志中。

1 2	SET GLOBAL slow_query_log=ON; SET GLOBAL long_query_time=1;

需要注意的是，通过上述语句修改后，再次通过SHOW VARIABLES LIKE '%query%';语句查询系统变量可能发现没有改变，这时需要断开并重新连接数据库。上述试用语句修改的 MySQL 系统变量，在数据库重启后会失效，如果需要永久修改这些系统变量，需要在 MySQL 的配置文件中修改 (MySQL 在 Windows 下的配置文件为my.ini，在 Linux 下的配置文件为 my.cnf)。接下来的语句是查询慢查询的发生次数：

1	SHOW STATUS LIKE '%slow_queries%';

该语句只能查询当前连接中发生的慢查询次数，每次断开并重新连接数据库，这个变量都会清零，目前慢查询发生的次数为0。

使用 explain 等工具分析 SQL 的执行

如果在慢查询日志中发现了慢查询对应的语句，可以在 SQL 语句前加上 explain，然后，便能够输出语句执行的相关信息，方便分析 SQL 语句的瓶颈。这里不展开讲解 explain 的每个字段，而只提及两个关键字段：type 和 extra。

如果 type 字段的值为 all，表示该 SQL 语句会进行全表扫描，需要进行优化。而 extra关键字中如果出现了 Using filesort 和 Using temporary，则意味着 MySQL 不能使用索引，效率会收到重大影响，应尽可能对此进行优化。

extra 项	说明
Using filesort	表示 MySQL 会对结果试用一个外部索引排序，而不是从表里按索引次序读到相关内容，可能在内存或者磁盘上进行排序，MySQL 中无法利用索引完成的排序操作称为"文件排序"
Using temporary	表示 MySQL 在对查询结果排序时试用临时表，常见于排序 order by 和分组查询 group by

修改 SQL 或者让SQL 尽量走索引

根据分析结果，给对应列添加索引，或者尝试试用其他带有索引的列来满足业务需求。

SQL 慢查询分析 Demo(仅供参考)

首先准备一个大数据量的表，使用如下语句构造一张表:

CREATE TABLE `person_info_large` (  
    `id` INT (7) NOT NULL AUTO_INCREMENT,  
    `account` VARCHAR (10),   
    `name` VARCHAR (20),  
    `area` VARCHAR (20),  
    `title` VARCHAR (20), 
    `motto` VARCHAR (50),
    PRIMARY KEY (`id`),  
    UNIQUE(`account`),
    KEY `index_area_title`(`area`,`title`) 
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

然后创建一张内存表:

CREATE TABLE `person_info_memory` (  
    `id` INT (7) NOT NULL AUTO_INCREMENT,  
    `account` VARCHAR (10),   
    `name` VARCHAR (20),  
    `area` VARCHAR (20),  
    `title` VARCHAR (20), 
    `motto` VARCHAR (50),
    PRIMARY KEY (`id`),  
    UNIQUE(`account`),
    KEY `index_area_title`(`area`,`title`)
) ENGINE = MEMORY AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

创建一个能够返回随机字符串mysql自定义函数:

CREATE DEFINER=`root`@`%` FUNCTION `rand_string`(n INT) RETURNS varchar(255) CHARSET utf8
BEGIN
DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
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()*12 + RAND()*50),1));
SET i = i +1;
END WHILE;
RETURN return_str;
END

创建一个批量往小表里灌数据的存储过程:

CREATE DEFINER=`root`@`%` PROCEDURE `add_person_info_large`(IN n int)
BEGIN    
  DECLARE i INT DEFAULT 1;  
    WHILE (i <= n ) DO  
      INSERT into person_info_large  (account,name,area,title, motto) VALUEs (rand_string(10),rand_string(20),rand_string(20) ,rand_string(20),rand_string(50));  
            set i=i+1;  
    END WHILE;  
END

调用存储过程，插入100万条数据(由于在自己电脑上插入 100 万条数据的时间太长，我这里插入约 10 万条数据):

CALL add_person_info_large(1000000);
-- 若遇数据冲突没法到达100万的情况，通过变换唯一键值的方式来插入数据
-- insert into person_info_large(account,name,area,title,motto) select concat(substring(account, 2),'a'),concat(substring(name, 2),'a'),area,title,motto from person_info_memory;
-- insert into person_info_large(account,name,area,title,motto) select concat(substring(account, 2),'b'),concat(substring(name, 2),'b'),area,title,motto from person_info_memory;
`

然后将内存表的数据移动到person_info_large中:

1	insert into person_info_large(account,name,area,title,motto) select account,name,area,title,motto from person_info_memory;

到这一步，数据表创建好了。开启 MySQL 的慢查询，然后执行如下查询语句：

1	SELECT name FROM person_info_large order by name desc;

返回结果显示 SQL 执行时间 23.231 秒，非常慢。

接着使用SHOW STATUS LIKE '%slow_queries%';查询慢查询发生的次数：

可以看到有一条慢查询，这时在 Linux 上使用 vim 查看慢查询日志，可以看到实际的执行时间和执行语句如下：

这时使用explain SELECT name FROM person_info_large order by name desc;来分析该语句:

可以看到该语句使用了全表扫描，并使用了外部排序，原因是查询的字段name不是索引，并且对该字段使用了 order by。可以根据业务使用带有索引的字段 account，或者给 name 字段加上索引。实际测试给 name 字段加上索引后，执行时间缩短为 21.690 秒（使用 account 字段提升会更明显），使用 explain 分析如下：

分析显示该语句走的是索引 idx_name，extra 字段也显示 Using index。

题外话：执行 count(id) 竟然不是通过 id 索引来统计数量

在上面创建的 person_info_large 表中，主键为 id，我们执行SELECT COUNT(id) FROM person_info_large;，执行时间为 0.096S，并使用 explain 来分析该语句，发现虽然该语句虽然使用了索引，但是使用的索引不是 id，而是 account。

这是由于主键 id 是聚集索引，在聚集索引的B+树的叶子节点在物理存储上除了存储索引，还会存储实际的数据本身，数据量大。而 account 属于非聚集唯一索引，在叶子节点上只会存储索引，和主键的值(或者数据地址的指针)，数据量小，一次 IO 能够加载更多数据。因此在这里遍历非聚集索引的效率要更高。我们可以通过SELECT COUNT(id) FROM person_info_large force INDEX(PRIMARY);来强制使用主键索引，执行时间为 0.136 S，比上面的时间慢。因此，主键索引未必比其他索引的速度要快，MySQL 优化器会根据一定的规则分析 SQL 语句，选择最优的索引来执行。但是 MySQL 优化器有时根据规则分析所采用的索引也会不尽人意，这时我们也可以根据经验或者测试结果，强制使用某个索引来提升效率。

联合索引的最左匹配原则：

在使用 where 条件筛选数据时，如果使用了联合索引，会根据联合索引创建时的顺序，从左边开始匹配索引，一直向右匹配，直到遇到范围查询(>、< 、between、like)，或者非索引字段，就会停止匹配
= 和 in 可以乱序，MySQL 的查询优化器会帮你优化成符合最左匹配原则的索引

例子

在 person_info_large 表中有联合索引index_area_title，顺序为area, title，执行如下三条语句都会使用联合索引

1
2
3

EXPLAIN SELECT * FROM person_info_large where area='j2QqOJUAJzLctOFKQRQ0' and title='j3FuV4gMOsuSiGkJ0MFn';
-- EXPLAIN SELECT * FROM person_info_large where area='j2QqOJUAJzLctOFKQRQ0' and title < 'j3FuV4gMOsuSiGkJ0MFn';
-- EXPLAIN SELECT * FROM person_info_large where title='j3FuV4gMOsuSiGkJ0MFn' and area='j2QqOJUAJzLctOFKQRQ0';

分析结果如下：

可以看到执行该语句使用的是联合索引，使用 = 和 in 的条件字段，索引字段顺序可以乱序，MySQL 优化器会帮我们调整为符合最左匹配原则的顺序，然后再去判断范围查询。如果换成如下两条语句：

1 2	EXPLAIN SELECT * FROM person_info_large where title='j3FuV4gMOsuSiGkJ0MFn' -- EXPLAIN SELECT * FROM person_info_large where title < 'j3FuV4gMOsuSiGkJ0MFn' and area='j2QqOJUAJzLctOFKQRQ0';

分析结果如下：

执行该语句使用的是全表扫描，而没有使用联合索引。因为创建联合索引时的顺序是 area -> title，因此在查询时直接过滤title的条件，而没有使用 area 字段的条件(或者 area 字段使用了范围查询)，则 MySQL 在执行该语句时不会使用联合索引。

总结

如果一个表中有联合索引 (a,b,c,d)，使用条件 a = 3 and b = 4 and c > 5 and d = 6 或者 b = 4 and a = 3 and c > 5 and d = 6 ，则 a、b、c 字段都可以用到索引，d 字段用不到索引。

联合索引最左匹配原则的成因

在 MySQL中，会为每个索引生成一棵 B+ 树，联合索引也不例外。在联合索引中，MySQL 首先会根据联合索引的第一个字段的顺序生成 B+ 树，在叶子节点中再根据联合索引的后续字段的顺序对数据进行排序存储(该联合索引是非聚集索引，此处叶子节点存储的数据是对应的主键)，因此数据是先根据联合索引的第一个字段来排序，在第一个字段相同的情况下，再根据联合索引的第二个字段来排序，以此类推。而单独从联合索引第二字段来看，数据是无序的。