MySQL SQL语句优化

1.背景

一般来说，一条SQL语句的执行时间变长，可能是由于以下几个原因导致的。数据量变多，这种情况可以考虑读写分离和分库分表；关联了太多的表（SQL语句本身的问题），应该对SQL进行优化；服务器性能下降，此时就需要对MySQL本身进行调优，可以通过修改my.cnf配置文件进行优化。本文主要介绍查询SQL语句如何优化。主要包括，如何对一条具体的SQL进行性能分析以及如何优化SQL。

2.准备工作

2.1.MySQL : 5.7.21版本

2.2.创建一个database

create database optimization;

2.3.创建部门表和员工表

-- 部门表
CREATE TABLE `dept` (
	`id` INT ( 11 ) NOT NULL auto_increment,
	`deptname` VARCHAR ( 30 ) DEFAULT NULL,
	`address` VARCHAR ( 40 ) DEFAULT NULL,
	`ceo` INT NULL,
PRIMARY KEY ( `id` ) 
) ENGINE = INNODB auto_increment = 1 DEFAULT charset = utf8;

-- 员工表
CREATE TABLE `emp` (
	`id` INT ( 11 ) NOT NULL auto_increment,
	`empno` INT NOT NULL,
	`name` VARCHAR ( 20 ) DEFAULT NULL,
	`age` INT ( 3 ) DEFAULT NULL,
	`deptid` INT ( 11 ) DEFAULT NULL,
PRIMARY KEY ( `id` ) 
) ENGINE = INNODB auto_increment = 1 DEFAULT charset = utf8;

2.4.向员工表和部门表插入数据

我们通过自定义函数和存储过程的方式向部门表和员工表插入数据

2.4.1 定义一个生成随机字符串的函数

-- 定义一个产生指定长度随机字符串的函数

delimiter $$
CREATE FUNCTION random_str ( n INT ) RETURNS VARCHAR ( 255 ) BEGIN
	DECLARE source VARCHAR ( 100 ) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIGKLMNOPQRSTUVWXYZ0123456789';
	DECLARE result VARCHAR ( 255 ) DEFAULT '';
	DECLARE i INT DEFAULT 0;

	WHILE i < n DO	
		SET result = CONCAT(result, SUBSTR( source, FLOOR( 1 + RAND ()* 62 ), 1 ));
		SET i = i + 1;
	END WHILE;
	RETURN result;

END $$

2.4.2 定义一个生成随机数的函数

-- 产生b到e之间的一个随机数

delimiter $$
	CREATE FUNCTION random_num ( b INT, e INT ) RETURNS INT ( 11 ) BEGIN
	DECLARE result INT ( 11 ) DEFAULT 0;
	SET result = FLOOR(b + RAND()* ( e - b + 1 ));
	RETURN result;
END $$

2.4.3 定义一个向部门表插入数据（10000条）的存储过程

-- 向部门表中插入数的存储过程

delimiter $$
CREATE PROCEDURE insert_dept ( start_num INT, end_num INT ) BEGIN
	DECLARE i INT DEFAULT 0;
	SET autocommit = 0;
	REPEAT	
		SET i = i + 1;
		INSERT INTO dept ( deptname, address, ceo )
		VALUES(
			  random_str ( 8 ),
				random_str ( 10 ),
			  rand_num ( 1, 500000 ));
		UNTIL i = end_num
	END REPEAT;
	COMMIT;
END $$

2.4.4  定义一个向员工表插入数据（500000条）的存储过程

-- 向员工表中插入数的存储过程

delimiter $$
CREATE PROCEDURE insert_emp ( start_num INT, end_num INT ) BEGIN
	DECLARE i INT DEFAULT 0;
	SET autocommit = 0;
	REPEAT	
		SET i = i + 1;
		INSERT INTO emp ( empno, `name`, age, deptid )
		VALUES(
		    (start_num + i),
			  random_str ( 6 ),
				rand_num ( 20, 35 ),
			  rand_num ( 1, 10000 ));
		UNTIL i = end_num
	END REPEAT;
	COMMIT;
END $$

2.4.5调用存储过程，插入数据

delimiter ;
call insert_dept(0, 10000);

delimiter ;
call insert_emp(0, 500000);

3.SQL性能分析

SQL的性能分析，主要使用explain关键字，可以模拟MySQL执行指定查询SQL的过程，从而分析出SQL语句的优化点。使用explain关键字分析SQL可以得到：执行SQL时表的读取顺序、哪些索引可以被使用、哪些索引被实际使用、每张表有多少物理行被扫描等信息。主要的用法为：

explain + 查询SQL

下面使用explain关键字对一个SQL进行分析，该SQL语句用来查询员工id为123456的员工信息。得到的结果如下图：

explain SELECT
	empno,
	`name`,
	age,
	deptname,
	address,
	ceo 
FROM
	emp
	LEFT JOIN dept ON emp.deptid = dept.id 
WHERE
	emp.id = 123456;

为了后续的SQL优化，有必要注意研究下各个字段的含义以及对SQL优化的指导作用。（这些字段的含义可以参考官方文档https://dev.mysql.com/doc/refman/5.7/en/explain-output.html）

3.1.id

select查询的序列号，表示查询过程中select子句的执行顺序。一个id值，表示一趟查询，对于一个查询语句来说，查询的趟数越少越好。关于执行顺序，有以下规则：id相同的子句，从上到下依次执行；id不同的子句，id大的先执行。

3.2.select_type

查询类型，主要有以下几种类型：

3.3 table

显示这一个查询时关于哪张表的。

3.4.partitions

代表分区表中的命中情况，非分区表，该项为null。

3.5.type

type字段描述表的连接方式。主要有以下几种，从上到下，性能依次下降。

• system：表只有一行数据。
• const：表中最多只有一行匹配的数据，常出现于将主键或者unique索引作为查询条件的语句。(eg : SELECT * FROM tbl_name WHERE primary_key=1; )
• eq_ref：主键或非空唯一索引扫描，对于ref_table表中的每一个键值，other_table中最多只有一条记录与之匹配。(eg : SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;)
• ref：非唯一索引扫描(eg : SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;)
• fulltext：使用全文索引。
• ref or null：类似于ref，但是MySQL会额外扫描包含空值的行。(eg : SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;)
• index merge：对多个索引进行扫描然后将他们各自的结果进行合并。(eg:SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;)
• unique subquery：在类似于value IN (SELECT primary_key FROM single_table WHERE some_expr)的子查询中，替代eq_ref。其中子查询中的primary_key 是唯一索引。
• index subquery：在类似于value IN (SELECT key_column FROM single_table WHERE some_expr)的子查询，替代ref。其中子查询中的key_column 是非唯一索引。
• range: 使用索引检索指定范围的行。(eg:SELECT * FROM tbl_name WHERE key_column IN (10,20,30);)
• index: 扫描整个索引树。
• all：扫描全表，以找到匹配的行。

我们的查询语句，mysql在执行的时候会选用其中的一种。需要注意的是，应该尽量避免all类型的连接方式，这也是sql优化的主要手段。

3.6.possible_keys

显示可能应用在这张表上的索引，一个或者多个。查询涉及到的字段，若涉及索引，则该索引会被列出来，但不一定被使用。

3.7.key

实际使用的索引。

3.8.key_len

表示使用了索引的长度，单位为字节。该字段可以用来检查，sql语句是否充分的使用上了索引，该字段越大越好。

3.9.ref

显示表中的哪些列用来和索引进行比较。ref的之也有可能会是一个常数。

3.10.rows

mysql认为在查询时必须要检查的行数。显然地，该值越小越好。

3.11.filtered

表示存储引擎返回的数据在经过server过滤后还剩下多少，是一个百分比。

3.12.extra

这个字段包含MySQL解析查询地额外信息（重要）。该字段的取值比较多，这里只介绍几个比较重要的：

• Using filesort：说明MySQL会对数据使用一个外部索引进行排序，而不是按照表内的索引顺序进行读取。MySQL中无法按照索引进行排序的操作称为"文件排序"。（eg：select * from emp where id < 1000 order by deptid;其中deptid并没有建立索引）
• Using temporary：使用了临时表来保存中间结果，MySQL在对查询结果排序时使用临时表，常见于排序order by和分组group by。（eg：select count(*) from emp GROUP BY emp.deptid;其中deptid并没有建立索引）
• Using index：利用索引进行了排序或分组。
• Using where：表明使用了where过滤
• Using join buffer：使用了连接缓存
• impossible where：where子句的值总是false，根据该条件不能查到任何数据。（eg：select * from table_a where 0 > 1;）

3.13总结

通过对explain结果各个字段的学习，其中有些字段可以用来指导我们进行SQL优化，一些经验和原则可以总结如下：

1. id字段，一个id值，表示一趟查询，查询的趟数越少越好;
2. type字段，应该尽量避免全表扫描；
3. key_len字段，越大越好；
4. rows字段，越小越好；
5. extra字段，尽量避免using filesort和using temporary。

换句话说，SQL优化就是通过修改SQL，增加索引等手段，使得这些指标尽可能地满足上述的要求。

4.SQL优化实战

下面通过几个实际的例子，说明对sql的优化

4.1 列出所有年纪等于30岁的员工

1.实现

select * from emp where age = 30;

2.运行

3.分析，显然没有在age字段建立索引，索引会全表扫描。

4.优化

主要是在age字段建立索引。CREATE index idx_age on emp(age) ;执行速度有提升，但不是特别明显，主要原因是age字段存在大量重复，作为索引的效果不是非常理想。

4.2查询年龄为30岁，部门id为2300，姓名为qmoH9t的员工

1.实现

select * from emp where age = 30 and deptid = 2300 and name = 'qmoH9t';

2.运行

3.分析，使用到了age字段上的索引。

4.优化

这个查询where后面有多个过滤条件，为了进一步提高查询速度，可以在每个字段上都建立索引。但是索引毕竟也是需要占据存储空间的，而且索引越多，在修改的时候效率也越低。针对这种场景，就可以建立复合索引，它既考虑到了所有的过滤字段，也没有那么占用空间，性价比比较高。

CREATE index idx_deptid_age_name on emp(deptid, age, name) ;

4.3列出自己的掌门比自己年龄小的人员的姓名、年龄、ceo的姓名和ceo的年龄

1.实现

-- 列出所有年龄低于自己门派平均年龄的人员
SELECT
	`name`,
	age,
	a.avg_age avg_age 
FROM
	emp
	LEFT JOIN ( SELECT avg( age ) avg_age, deptid FROM emp GROUP BY deptid ) a ON emp.deptid = a.deptid 
WHERE
	age < avg_age;

2.运行。虽然结果正确，但是用了5分半，这个也实在慢的令人发指。

  

3.使用EXPLAIN分析下。果然，一共进行三次查询，每次都是全表扫描（type=ALL），而且都没有使用到索引（key为NULL）,extra字段也出现了using filesort和using temporary的情况。

4.优化

两个表进行关联查询，例如A  join B on A.value = B.value，其中A表成为驱动表，B表称为被驱动表。实现的时候需要对A表进行全表扫描，每扫描A表中的一条数据，需要拿着这个数据去跟B表中的数据做匹配。由于驱动表无论如何都要被全表扫描，所以我们希望驱动表的数据小于被驱动表。同时，被驱动表关于关联字段是建立了索引的（否则每一次匹配都要对被驱动表进行扫描）。针对这道题目，显然子查询得到的虚拟表数据更少，因此将虚拟表放在驱动表的位置，将emp表放在被驱动表的位置。当然，也可以直接使用inner join，mysql会自动帮我们做优化，自动决定驱动表和被驱动表。同时需要对emp表的deptid字段建立索引。

建索引

CREATE index idx_deptid on emp(deptid) ;

left join改为left join(或者交换两个表的位置)

-- 列出所有年龄低于自己门派平均年龄的人员
SELECT 
	`name`,
	age,
	a.avg_age avg_age 
FROM
	emp
INNER JOIN ( SELECT avg( age ) avg_age, deptid FROM emp GROUP BY deptid ) a ON emp.deptid = a.deptid 
WHERE
	age < avg_age;

执行一下，可以明显地看到执行时间已经从300多秒优化到了2秒多。

使用explain字段分析一下。从分析结果来看，首先执行的是SELECT avg( age ) avg_age, deptid FROM emp GROUP BY deptid，由于在deptid字段建立了索引，此时的group by可以使用索引，type从ALL变为index。其次，驱动表也变成了数据量较小的a表（虚拟表）。最后，驱动表在关联被驱动表的时候，由于在被驱动表的关联字段建立索引，关联时不会再对被驱动表进行全表扫描。

4.4 列出至少有2个年龄大于40岁的成员的门派

1.实现

SELECT
	b.deptname,
	count(b.id)
FROM
	emp a
	INNER JOIN dept b ON a.deptid = b.id 
WHERE
	a.age > 34
GROUP BY b.id
HAVING count(b.id) >= 2

2.运行，效果还不错。

3.分析下。虽然连接使用的是inner join，但是mysql并没有选择较小的dept表作为驱动表，主要原因是dept表的关联字段可以使用到主键，索引mysql在这里将dept表作为了被驱动表。

4.优化。

为了将dept表作为驱动表，在查询的时候使用STRAIGHT_JOIN直接指定驱动表。同时在emp的deptid字段上建立索引。

SELECT
	b.deptname,
	count(b.id)
FROM
	dept b
	STRAIGHT_JOIN emp a ON a.deptid = b.id 
WHERE
	a.age > 34
GROUP BY b.id
HAVING count(b.id) >= 2

CREATE index idx_deptid on emp(deptid) ;

执行

再分析。从结果上看，优化后反而变慢了。explain看下执行计划，在优化后，数据量少的dept表确实变成了驱动表，而且被驱动表也用上了索引。但是为什么还会变慢呢？主要原因就在于这个索引上。在优化之前，虽然驱动表是数据比多的emp表，但是被驱动表的关联字段是唯一索引。而优化后的被驱动表的关联字段deptid并不是唯一索引，这就导致优化后反而效率降低了。所以，还是得具体情况具体分析。

5.总结

MySQL优化是一个非常具有挑战性的事，不仅需要对MySQL的理论特别熟悉，而且还要经过长时间的锻炼。本文只是对查询的SQL语句的优化，做了一点小小的尝试。关于MySQL查询语句的优化还有常用的一些原则，现在总结如下：

• 在作为查询条件或者连接条件的字段上建立索引
• 针对复合索引，为了提高索引的匹配长度，要遵循最佳左前缀法则
• 不要在索引字段上进行任何操作（函数、计算等），这样会使索引失效
• mysql中在使用!= 或<>是，索引失效
• is not null无法使用索引，但是is null可以
• like 以通配符开头，索引失效
• 对于关联查询，保证被驱动表的关联字段已经建立索引
• 将数据量较小的表作为驱动表
• 能够直接使用关联查询的，尽量不要使用子查询
• 对于子查询尽量不要使用not in 或者not exists