MySQL 数据库有一个“慢查询日志”功能，用来记录查询时间超过某个设定值的SQL。

慢查询日志功能开启：

slow_query_log ：是否开启慢查询日志功能（必填）

SRE实战 互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。

long_query_time ：超过设定值，将被视作慢查询，并记录至慢查询日志文件中（必填）

log-slow-queries ：慢查询日志文件（不可填），自动在 \data\ 创建一个 [hostname]-slow.log 文件

配置：

set global slow_query_log = ON;   # 开启这个功能   set GLOBAL long_query_time = 1;# 设置查询“超时”时间 当我们开启这个功能时候每一条sql语句如果执行时间超过了设置的时间，就会被写入到日志文件里面 在定位到我们有问题的sql文件以后：

如何对我们的sql语句进行优化呢

1.选择最适用的字段类型（数据库中的表越小，在它上面执行的查询也就会越快）

比如一个字符串，能够确定长度，就用char（6）而不要用char（255）这样给数据库增加不必要的空间，还有varchar长度是动态可变的（它更节省空间），char的效率比varchar快。

高重复的字段将其转换为数值类型。数值类型的处理比文本类型快了很多。

对于字段比较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

2.使用连接（JOIN）来代替子查询(Sub-Queries)

  连接（JOIN）之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

  但是大量的使用join也不是好的办法：

  很多高性能的应用都会对关联查询进行分解，就是可以对每一个表进行一次单表查询，然后将查询结果在应用程序中进行关联，很多场景下这样会更高效。

3.使用UNION来替换手动创建临时表

4.多用事务：

要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样，就会造成数据的不完整，甚至会破坏数据库中的数据。

5.使用外键来保证数据的关联性。

外键可以保证每一条销售记录都指向某一个存在的客户。在这里，外键可以把customerinfo表中的customerid映射到salesinfo表中customerid，任何一条没有合法customerid的记录都不会被更新或插入到salesinfo中。

CREATE  TABLE   customerinfo( customerid   int primary key) engine = innodb;

CREATE  TABLE   salesinfo( salesid int not null,customerid  int not null, primary key(customerid,salesid),foreign key(customerid)  references  customerinfo(customerid) on delete cascade)engine = innodb;
注意例子中的参数“on delete cascade”。该参数保证当customerinfo表中的一条客户记录被删除的时候，salesinfo表中所有与该客户相关的记录也会被自动删除。如果要在MySQL中使用外键，一定要记住在创建表的时候将表的类型定义为事务安全表InnoDB类型。该类型不是MySQL表的默认类型。定义的方法是在CREATE TABLE语句中加上engine=INNODB。如例中所示。

6.对于limit分页的优化：

第一种使用有索引的列来做order by操作。 第二种传入上一次查询的id，对limit的优化，不是直接使用limit，而是首先获取到offset的id（通过这个id来做一遍过滤），然后直接使用limit size来获取数据。

7.使用索引：

负向查询不能使用索引

select name from user where id not in (1,3,4);

应该修改为:

select name from user where id in (2,5,6);

前导模糊查询不能使用索引

如:

select name from user where name like '%zhangsan'

非前导则可以:

select name from user where name like 'zhangsan%'

建议可以考虑使用 Lucene 等全文索引工具来代替频繁的模糊查询。

数据区分不明显的不建议创建索引

如 user 表中的性别字段，可以明显区分的才建议创建索引，如身份证等字段。

字段的默认值不要为 null

这样会带来和预期不一致的查询结果。

在字段上进行计算不能命中索引

select name from user where FROM_UNIXTIME(create_time) < CURDATE();

应该修改为:

select name from user where create_time < FROM_UNIXTIME(CURDATE());

最左前缀问题

如果给 user 表中的 username pwd 字段创建了复合索引那么使用以下SQL 都是可以命中索引:

select username from user where username='zhangsan' and pwd ='axsedf1sd' select username from user where pwd ='axsedf1sd' and username='zhangsan' select username from user where username='zhangsan'

但是使用

select username from user where pwd ='axsedf1sd'

是不能命中索引的。

如果明确知道只有一条记录返回

select name from user where username='zhangsan' limit 1

可以提高效率，可以让数据库停止游标移动。

不要让数据库帮我们做强制类型转换

select name from user where telno=18722222222

这样虽然可以查出数据，但是会导致全表扫描。

需要修改为

select name from user where telno='18722222222'

如果需要进行 join 的字段两表的字段类型要相同

不然也不会命中索引。

扫码关注我们
微信号：SRE实战
拒绝背锅 运筹帷幄