MySQL慢sql分析及优化

注：由于在MySQL日常查询中，查询类型的语句占慢sql的大部分，因此本文仅针对query类型的sql进行阐述，modify类型会单独分析。

为何要对慢SQL进行治理

从数据库角度看：每个SQL执行都需要消耗一定I/O资源，SQL执行的快慢，决定资源被占用时间的长短。假设总资源是100，有一条慢SQL占用了30的资源共计1分钟。那么在这1分钟时间内，其他SQL能够分配的资源总量就是70，如此循环，当资源分配完的时候，所有新的SQL执行将会排队等待。
从应用的角度看：SQL执行时间长意味着等待，在OLTP应用当中，用户的体验较差

治理的优先级上

1. master数据库->slave数据库

   • 目前数据库基本上都是读写分离架构，读在从库（slave）上执行，写在主库（master）上执行。

   • 由于从库的数据都是从主库上复制过去的，主库等待较多的，会加大与从库的复制时延。

2. 执行次数多的SQL优先治理

3. 如果有一类SQL高并发集中访问某一张表，应当优先治理。

MySQL执行原理

绿色部分为SQL实际执行部分，可以发现SQL执行2大步骤：解析，执行。
以com_query为例，dispatch_command会先调用alloc_query为query buffer分配内存，之后调用解析
解析：词法解析->语法解析->逻辑计划->查询优化->物理执行计划
检查是否存在可用查询缓存结果，如果没有或者缓存失效，则调用mysql_execute_command执行
执行：检查用户、表权限->表上加共享读锁->取数据到query cache->取消共享读锁

影响因素

如不考虑MySQL数据库的参数以及硬件I/O的影响， 则影响SQL执行效率的因素主要是I/O和CPU的消耗量
总结：

1. 数据量：数据量越大需要的I/O次数越多

2. 取数据的方式

   • 数据在缓存中还是在磁盘上

   • 是否可以通过索引快速寻址

3. 数据加工的方式

   • 排序、子查询等，需要先把数据取到临时表中，再对数据进行加工

   • 增加了I/O，且消耗大量CPU资源

解决思路

1. 将数据存放在更快的地方。

   • 如果数据量不大，变化频率不高，但访问频率很高，此时应该考虑将数据放在应用端的缓存当中或者Redis这样的缓存当中，以提高存取速度。如果数据不做过滤、关联、排序等操作，仅按照key进行存取，且不考虑强一致性需求，也可考虑选用NoSQL数据库。

2. 适当合并I/O

   • 分别执行select c1 from t1与select c2 from t1，与执行select c1,c2 from t1相比，后者开销更小。

   • 合并时也需要考虑执行时间的增加。

3. 利用分布式架构

   • 在面对海量的数据时，通常的做法是将数据和I/O分散到多台主机上去执行。

案例与小技巧

OLTP环境下通常使用innodb引擎。索引使用b+tree.

主键

MyISAM引擎表的索引当中存的是指针，指向数据的地址。而innodb引擎当中主键存的是行数据。
因此绝大多数情况下，通过主键取数据效率是最高的。
另外，通常我们在建表的时候会指定一个自增整数列作为主键，因此数据在存放时是按照id顺序存放的。
也就是说，select c1 from t1 与 select c1 from t1 order by id 是一致的。

索引

向有索引的表当中插入、更新、删除数据时，都需要对索引进行相应操作， 因此索引越多，写表效率越低。
索引列的选择需要注意列的离散度（唯一性）越高，越适合做索引。

create table t1 ( id int,
name varchar2(50),
tel int(11),
zipcode int(6),
city varchar2(10),
gender smallint(1)
);

上述表中，最不适合做索引的列就是gender。另外如果某列当中有很多的null值，也不适合做索引。
索引中存的是主键的值（表如果没有设置主键，mysql会自动为表设置一列隐藏数字列作为主键。）
因此与顺序排列的主键不同，查询索引多是随机I/O。

复合索引

如果一列无法做到唯一，可以将几列数据组合起来做成索引。这种情况就是复合索引。
例如上表，我们可以建立一条复合索引

ALTER TABLE t1 ADD INDEX t1_tel_city_zipcode (tel,zipcode,city);

这时，我们查询条件包含如下条件，都可以利用索引：
tel
tel,zipcode
tel,zipcode,city
需要注意的是，如果条件是zipcode或者tel,city是无法走索引的。
复合索引设计时，应考虑索引之间组合的顺序，将离散度最高的列放在前面

索引列长度

InnoDB单列索引长度不能超过767bytes，复合索引是3072。

mysql> CREATE TABLE tb ( 
a varchar(255) DEFAULT NULL, 
b varchar(255) DEFAULT NULL, 
c varchar(255) DEFAULT NULL, 
d varchar(255) DEFAULT NULL, 
e varchar(255) DEFAULT NULL, 
KEY a (a,b,c,d,e) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

我们知道InnoDB一个page的默认大小是16k。由于是Btree组织，要求叶子节点上一个page至少要包含两条记录（否则就退化链表了）。
所以一个记录最多不能超过8k。又由于InnoDB的聚簇索引结构，一个二级索引要包含主键索引，因此每个单个索引不能超过4k （极端情况，pk和某个二级索引都达到这个限制）。
由于需要预留和辅助空间，扣掉后不能超过3500，取个“整数”就是(1024*3)。

单列索引限制

上面有提到单列索引限制767，起因是256×3-1。这个3是字符最大占用空间（utf8）。但是在5.5以后，开始支持4个字节的uutf8。255×4>767, 于是增加了一个参数叫做 innodb_large_prefix。
这个参数默认值是OFF。当改为ON时，允许列索引最大达到3072。
索引列的长度应考虑尽可能短，以提高读写效率。基于此规则可以引申出
整型数值类型优于字符类型，例如手机号码
日期字段，date类型，timestamp类型优于字符类型
字符集选择
latin1 ->gbk ->utf8
对于某些很长，但又必须建索引的列，可以考虑建立前缀索引。

利用索引

假设有一张500w的表，以自增列id 为主键，取id为400w到401w的数据，你会怎么做？
通常的做法是使用limit m，n ，这里提供一种思路：通过id定位到第400w条数据，再往后取1w条。

mysql> select id,c1 from t1 limit 4000000,10000;
......
| 4009998 | 4009998 |
| 4009999 | 4009999 |
| 4010000 | 4010000 |
+---------+---------+
10000 rows in set (0.84 sec)

mysql> reset query cache;
Query OK, 0 rows affected (0.00 sec)

mysql> select id,c1 from t1 where id > 4000000 limit 10000;
......
| 4009998 | 4009998 |
| 4009999 | 4009999 |
| 4010000 | 4010000 |
+---------+---------+
10000 rows in set (0.00 sec)

避免隐式转化

where 条件时，变量的类型应当与列的类型匹配。如果不匹配，数据库需要额外将变量类型转换，称为隐式转化。

mysql> desc t1;
+-------+--------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+----------------+
| id | int(8) | NO | PRI | NULL | auto_increment |
| c1 | int(8) | YES | | NULL | |
+-------+--------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> select id,c1 from t1 where c1='4398724';
+---------+---------+
| id | c1 |
+---------+---------+
| 4398724 | 4398724 |
+---------+---------+
1 row in set (0.99 sec)

mysql> reset query cache;
Query OK, 0 rows affected (0.00 sec)

mysql> select id,c1 from t1 where c1=4398825;
+---------+---------+
| id | c1 |
+---------+---------+
| 4398825 | 4398825 |
+---------+---------+
1 row in set (0.98 sec)

同理
where子句的查询条件里有！=，MySQL将无法使用索引。
where子句使用了Mysql函数的时候，索引将无效，比如：

select * from tb where left(name, 4) = 'xxx';

使用LIKE进行搜索匹配的时候，这样索引是有效的：

select * from tbl1 where name like 'xxx%';

而like '%xxx%' 时索引无效

字段长度

MySQL数字分为tinyint、smallint、int、bigint

• bigint
  从 -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807) 的整型数据（所有数字）。存储大小为 8 个字节。

• int
  从 -2^31 (-2,147,483,648) 到 2^31 - 1 (2,147,483,647) 的整型数据（所有数字）。存储大小为 4 个字节。int 的 SQL-92 同义字为 integer。

• smallint
  从 -2^15 (-32,768) 到 2^15 - 1 (32,767) 的整型数据。存储大小为 2 个字节。

• tinyint
  从 0 到 255 的整型数据。存储大小为 1 字节。

为什么要这么复杂， 所有的数字类型都设置为bigint不就可以了么？我们来做一个实验

create table t1 ( c1 smallint);
create table t2 ( c1 bigint);

delimiter $$　　
drop procedure if exists wk;
create procedure wk()　　　　　　
begin
declare i int;　　　　　　　　　　
set i = 1;　　　　　
while i < 32767 do 　　　　　　　　
insert into t1 (c1) values (i);
insert into t2 (c1) values (i);
set i = i +1;
end while;
commit;
end $$　
delimiter ;　　　　　　　　　
call wk();

select c1 from t1;
......
| 32765 |
| 32766 |
 +-------+
 32766 rows in set (0.01 sec)

select c1 from t2;
......
| 32765 |
| 32766 |
+-------+
32766 rows in set (0.02 sec)

可以发现同样的数据，从t2表中取出的耗时较长。因为我们为t2表设置了较宽的列长，意味着将数据swap进query cache需要更多的I/O。因此建议在设置字段宽度的时候，在保持长度够用的情况下，尽量使用较短宽度的数据类型，以节省空间。同理，使用varchar类型替换char类型，并避免使用blob、clob这样的长字段。

使用join替代in

SELECT * FROM customerinfo 
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo );
SELECT * FROM customerinfo 
LEFT JOIN salesinfo 
ON customerinfo.CustomerID=salesinfo.CustomerID 
WHERE salesinfo.CustomerID IS NULL;

假设2张表CustomerID字段都有索引，使用join的效率要高于使用in，因为join不需要建立临时表。

explain使用方法

explain就是SQL的执行计划，通过执行计划，我们可以了解sql的执行当中的一些细节。
使用方法为在SQL语句前加explain
得到结果如下：

mysql> explain select id,c1 from t1 where c1=4398825;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 4992210 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

各列功能如下：

• id: 按照sql语法解析后分层后的编号，可能重复

• select_type：

  • SIMPLE，简单的select查询,不使用union及子查询

  • PRIMARY，最外层的select查询

  • UNION，UNION 中的第二个或随后的 select 查询,不依赖于外部查询的结果集

  • DEPENDENT UNION，UNION 中的第二个或随后的 select 查询,依赖于外部查询的结果集

  • SUBQUERY，子查询中的第一个 select 查询,不依赖于外部查询的结果集

  • DEPENDENT SUBQUERY，子查询中的第一个 select 查询,依赖于外部查询的结果集

  • DERIVED，用于 from子句里有子查询的情况。 MySQL会递归执行这些子查询, 把结果放在临时表里。

  • UNCACHEABLE SUBQUERY，结果集不能被缓存的子查询,必须重新为外层查询的每一行进行评估。

  • UNCACHEABLE UNION，UNION 中的第二个或随后的 select 查询,属于不可缓存的子查询

• table：涉及的表，如果SQL中表有赋别名，这里出现的是别名

• type：

  • system，从系统表读一行。这是const联接类型的一个特例。

  • const，表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次!

  • eq_ref，查询条件为等于

  • ref，条件查询不等于

  • ref_or_null，同ref(条件查询),包含NULL值的行。

  • index_merge，索引联合查询

  • unique_subquery，利用唯一索引进行子查询

  • index_subquery，用非唯一索引进行子查询

  • range，索引范围扫描

  • index，索引全扫描

  • ALL，全表扫描。

• possible_keys：可能使用的索引

• key：sql中使用的索引

• key_len：索引长度

• ref：使用哪个列或常数与key一起从表中选择行。

• rows：显示MYSQL执行查询的行数，简单且重要，数值越大越不好，说明没有用好索引

• Extra：该列包含MySQL解决查询的详细信息。

  • Distinct,去重，返回第一个满足条件的值

  • Not exists 使用not exists查询

  • Range checked for each record,有索引，但索引选择率很低

  • Using filesort,有序查询

  • Using index,索引全扫描

  • Using index condition,索引查询

  • Using temporary,临表表检索

  • Using where,where条件查询

  • Using sort_union,有序合并查询

  • Using union,合并查询

  • Using intersect,索引交叉合并

  • Impossible WHERE noticed after reading const tables,读取const tables,查询结果为空

  • No tables used,没有使用表

  • Using join buffer (Block Nested Loop),使用join buffer(BNL算法)

  • Using MRR(Multi-Range Read ) 使用辅助索引进行多范围读