MySQL核心之sql语句优化

MySQL核心之SQL语句优化

一、概述

我们知道平时工作中，如果发现一个接口响应很慢。除开网络环境，代码逻辑问题，更大可能性是出现在数据库语言优化问题。怎么样来定位SQL的问题呢？

首先，准备一个表和表的数据，来显示出优化语句的差异

建表语句

create table test (
	id int(7) zerofill auto_increment not null,
	phone varchar(20),
    sex varchar(10),
	password varchar(20),
	createtime datetime,
	primary key (id)
)DEFAULT CHARSET=utf8;

这里准备一个百万数据用来测试，方法很多，shell脚本，代码脚本都可以实现，大家可以自行解决。

test表中输入了999999条数据

二、MySql慢日志开启与问题定位

首先，我们针对上述test进行一次查询，where子句选择id字段

再来一次，where子句选择phone字段

我们明显发现，查询相同一条数据，语法结构的不同，时间相差几十倍；例句第一条sql语句查询快的原因在于id字段是主键，自带索引。

对于有些SQL语句，如果查询的慢，我们怎样去定位问题呢？

• SQL慢日志查询

输入show variables like 'slow%';

其中slow_query_log值为OFF即为关闭状态；slow_query_log_file值为日志路径。

开启可使用set global slow_query_log = on;进行开启

也可以自定义日志的路径set global slow_query_log_file = '路径'

• 慢日志的时间临界值

慢需要有个标准，多少时间以上的才会被记录到日志内。

可以使用show variables like 'long%';进行查询

使用set long_query_time = 0.2进行设置

可以进行测试select * from test where phone = '15070766725';使用tail实时查看慢日志

• 永久设置慢日志配置项

需要注意的是重启MySql服务会让在交互界面设置的慢查询恢复2到默认。

永久设置的方法：修改配置文件vim /etc/my.cnf

[mysqld]
# 开启慢日志
slow_query_log = 1
# 慢日志时间临界值
long_query_time = 0.1
# 慢日志保存路径
slow_query_log_file =/usr/local/mysql/cqtq_slow.log

修改后重启mysql服务即可生效。

三、MySql语句优化

• 避免使用select * from，尽量精确到想要的结果字段
  • 说明：1）增加查询分析器解析成本。2）增减字段容易与resultMap配置不一致。3）无用字段增加网络消耗，尤其是text类型的字段。
• 尽量避免条件使用or
• 数据量大时，加上limit，限制行数
  • 说明：避免数据量过大消耗性能
• 不要使用count(列名)来替代count(*)
  • 说明：count(*)是SQL92定义的标准统计行数的语法，跟数据库无关，跟NULL和非NULL无关。
• 如果存储的字符串长度几乎相等，使用char定长字符串类型
• varchar是可变长字符串，不预先分配存储空间，长度不要超过5000，如果存储长度大于此值，定义字段类型为text，独立出来一张表，用主键来对应，避免影响其它字段索引效率。
• 三表以上尽量不使用join查询