三、MySQL调优

MySQL调优

mysql调优可以从三个方面考虑：

• 架构调优
• MySQL调优
• 硬件和操作系统调优

这三个方面的调优效果和成本如下图所示：

慢查询优化

慢查询是一条sql语句执行时间超出了MySQL配置的慢查询阈值时间，被MySQL认为是慢查询语句，会被记录到慢查询日志文件中

慢查询相关sql：

通过 show variables like 'long_query_time';这条sql语句可以查看MySQL配置的慢查询时间阈值。想要修改阈值，可以通过 set GLOBAL long_query_time=1;这条sql语句将阈值修改成1秒

慢查询语句写入日志文件中是需要将配置开关打开的，通过 show variables like 'slow_query_log';查看开关是否打开。也可以通过 set GLOBAL slow_query_log=1;来将开关打开

通过 show variables like '%slow_query_log_file%';可以查看慢查询的日志文件路径

慢查询日志：

慢查询日志中，一条sql语句信息是以 # Time 开头，以sql语句结尾。

慢查询日志信息：

• Query_time：sql语句执行花费的时间
• Lock_time：sql语句等待获取锁花费的时间
• Rows_sent：sql语句返回的行数
• Rows_examined：sql语句执行时扫描的行数
• timestamp：sql语句开始执行的时间戳

执行计划

执行计划是一条sql语句在MySQL中执行时，被优化器优化后的执行步骤。通过在sql语句前加上explain，可以看到执行计划

explain结果列解析

id列

每条select语句都有一个自己的id，explain的id列有几种情况：

• 如果id相同，select语句是从上往下执行
• 如果id不同，select语句是id大的先执行
• 如果上面两种情况都有，先执行id大的，再同级从上往下执行
• 如果id列显示null，最后执行。表示结果集，不需要使用它来进行查询

如果id相同，select语句是从上往下执行：

explain
select users.name, orders.total_price, products.price
from users
inner join orders on users.id = orders.user_id
inner join products on orders.product_id = products.id;

上述sql语句是users、orders和products三张表关联查询。从explain返回的结果看，sql语句先执行的是users的查询，然后是orders表的查询，最后是products表的查询

如果id不同，select语句是id大的先执行：

explain
select * from orders where product_id = (select id from products where products.price = 10);

从explain返回的结果看，sql语句先执行的是products表的查询，然后是orders表的查询

如果上面两种情况都有，先执行id大的，再同级从上往下执行：

explain
select orders.*
from (select id from products) as temp inner join orders on temp.id = orders.product_id;

从explain返回的结果看，sql语句先执行的是products表的查询，然后是orders表的查询，最后是查询products表得到的衍生表的查询

如果id列显示null，最后执行。表示结果集，不需要使用它来进行查询：

explain
select id from users
union
select id from products;

从explain返回的结果看，sql语句先执行的是products表的查询，然后是users表的查询，最后是查询users表和products表得到的结果集。这个结果集不需要进行查询，直接返回

还有一种情况是优化器会对sql语句进行优化，会将多个select语句合并成一个：

explain
select * from users where id in (select user_id from orders where id = 1);

这种情况就是优化器觉得这条sql语句的子查询效率不如优化后的sql语句，所以进行了sql优化

想看优化后的sql语句，在sql语句后面加上show warnings;语句

explain
select * from users where id in (select user_id from orders where id = 1);
show warnings;

将Message列中的数据复制出来，粘贴到连接数据库的客户端里看下（这里用的是navicat）

这个就是优化后的sql，只有一条select语句了

select_type列

simple：

简单select，不包含union和子查询

explain
select users.name, orders.total_price, products.price
from users
inner join orders on users.id = orders.user_id
inner join products on orders.product_id = products.id;

primary：

复杂查询中最外层查询，比如使用union或者union all时，id为1的记录select_type通常是primary

explain
select id from users
union
select id from products;

subquery：

指在select后面出现的子查询，结果不依赖外部查询

explain
select orders.*, (select name from products where id = 1) from orders;

dependent subquery：

指在select后面出现的查询语句，结果依赖外部查询

explain
select orders.*, (select name from products where products.id = orders.user_id) from orders;

derived：

派生表，在from子句的查询语句中，表示从外部数据源中推导出来的

set session optimizer_switch='derived_merge=off';  # 关闭MySQL5.7对衍生表合并优化

explain
select * from (select user_id from orders where id = 1) as temp;

union：

select语句被union或者union all连接，那么第一个select语句被标记为primary，后面的select语句都是union。如果union在from子句中，那么第一个select语句被标记为derived。union会对结果进行去重，所以会比union all多一个临时表。

explain
select * from (
select id from products where price = 10
union
select id from orders where user_id in (1,2)
union
select id from users where name = '张三' ) as temp;

最后一个UNION RESULT就是union的临时表，如果是union all，不会有这个

dependent union：

当union作为子查询时，其中第一个select被标记为dependent subquery，后面select被标记为dependent union

explain
select * from orders where id in (
select id from products where price = 10
union
select id from orders where user_id = 2
union
select id from users where name = '张三');

union result：

使用union时，最后会对结果进行去重，需要临时表

explain
select id from users
union
select id from products;

type、possible_key和key列

• type列：查询所使用的访问类型。效率从高到低是：system>const>eq_ref>ref>fulltext>ref or null>range>index>all，一般保证range级别，最好达到ref级别
• possible_key列：表示查询中可能使用到的某个或者多个索引
• key列：表示查询中实际使用到的索引

const级别：

基于主键或唯一索引查看某一行

explain
select * from orders where id = 1;

eq_ref级别：

基于主键或唯一索引连接两个表，被驱动表的类型是eq_ref

explain
select users.* from users inner join orders on users.id = orders.id;

ref级别：

基于辅助索引连接两个表或者通过辅助索引与常量进行等值匹配

explain
select users.* from users inner join orders on users.id = orders.user_id;

explain
select * from orders where user_id = 1;

range级别：

使用主键索引或者辅助索引进行范围查询

explain
select * from orders where user_id > 3;

index：

扫描整个辅助索引才能拿到数据

explain
select user_id from orders;

all：

扫描整个表才能拿到数据，即扫描整个聚集索引

explain
select * from users;

NULL：

MySQL在优化过程中分解sql语句就可以得到结果，甚至不用查询表

explain
select min(id) from users;

key_len列

表示当优化器决定使用某个索引进行查询时，索引使用的最大长度（主要用于联合索引）

联合索引可以通过此字段判断出具体使用了联合索引中的哪些索引列

key_len的计算规则：

字符串：

• char(n)：n个字节
• varchar(n)：如果是utf-8就是3n+2个字节，加的2个字节用来存储字符串的长度。如果是utf8mb4就是4n+2个字节

数值类型：

• tinyint：1个字节
• smallint：2个字节
• int：4个字节
• bigint：8个字节

时间类型：

• date：3个字节
• timestamp：4个字节
• datetime：8个字节

如果字段允许为null，需要加上1个字节用来记录是否为null

ref列

表示将哪个字段或者常量和key列所使用的字段进行比较

当使用索引列等值查询时，与索引进行等值匹配的对象信息

常量：

explain
select * from users where name = '张三' and email = 'zhangsan@example.com';

字段：

explain
select users.* from users inner join orders on users.id = orders.id;

函数：

explain
select users.* from users inner join orders on users.id = trim(orders.id);

rows列和filtered列

• rows列：全表扫描时表示需要扫描表的行数值；索引扫描时表示扫描索引的行数值；值越小越好
• filtered列：表示符合查询条件的数据占扫描的数据的百分比

Extra列：

sql执行查询的一些额外信息

• Using index：使用辅助索引可以查询所需的数据，一般是覆盖索引，不需要回表操作
• Using where：不通过索引查询所需数据，就是查询过程中没有用到索引
• Using index condition：表示查询列不被索引覆盖。where条件是索引范围查询，过滤后需要回表查需要的数据
• Using temporary：表示需要使用临时表来处理查询，例如union
• Using filesort：当查询中包含order by操作而且无法利用索引完成排序。数据较少在内存排序，数据较多在磁盘中排序
• Select tables optimized away：使用聚合函数来访问某个索引值

索引的高性能使用策略

• sql语句在使用索引时，不要对索引列进行操作，否则无法使用索引
• sql语句在使用联合索引时，用上的索引列越多越好
• sql语句在使用索引时，满足最左前缀原则
• sql语句在写时，范围条件放在最后
• 尽量使用覆盖索引
• 尽量不要使用不等于、or以及is not null，因为可能会导致全表扫描
• 字符串的查询条件要加单引号，否则查询前会进行数据类型转换，导致无法使用索引
• sql语句使用模糊查询，like后面的字符串，前面一定要有值，这样才满足最左前缀原则
• sql语句排序尽量都是联合索引列
• count用某一列时，如果这个列中有为null的，那么不计入统计。所以最好还是使用count(*)统计全表数据量