Mysql-sql语句性能调优

勿以浮沙筑高台

---

Mysql性能调优
版本：Mysql_8

确定执行慢的sql

慢日志

在mysql中有一个慢日志，当查询的时间超过一定时间后会将执行慢的SQL语句记录到慢日志当中。

日志文件名为：slow_query_log
使用下面命令可以查询是否开启，默认是关闭的。

SHOW VARIABLES LIKE ‘%slow_query_log%’;

开启：

SET GLOBAL slow_query_log =1

设定查询超过多少时间的记录到慢日志

set global slow_launch_time=1 #最少为1秒

性能优化方案

子查询

因为关联的时候，如果内表有1000条，外表有10条，这个时候查询每条外表的数据会传递到内表进行查询，也就是1000*10=10000次。所以有很大的开销.

下面这条SQL语句就是全链接的方式

select name,id,age from student where id in (select id， from stu_score where score>=90)

优化：使用join，left join，right join 进行半链接优化。就是只用一半的表去链接，而不是双向的去链接数据。

select name，id，age from student stu join (select id from stu_score where score>=90) stusc on stu.id = stusc.id

select *

select *会破坏索引

派生子查询

和子查询一样。经历避免无用临时表的创建

select * from (select * from student where id >10) where id=21

优化后：

selec * from student where id=21 and id>10

IN查询

这里执行查询后，用explain查看类型会发现type为range。

Select * from student_score where (student_id,subject_id) in ((1,1), (1,2), (1,3), (1,4));

优化后：type=ref，因为括号里的结果要么是true要么是false因此更快

explain select * from student_score where (student_id=1 and subject_id=1) or (student_id=1 and subject_id=2) or (student_id=1 and subject_id=3)

UPDATE/DELETE IN子查询优化

子查询进行了相互匹配

delete from t1 where t1.id in(select id from t2 where id<5);

优化：

delete from t1 join (select id from t2 where id<5) tmp on t1.id=tmp.id;

Like "%xxxx%"优化

在MySQL中, like 'xxx%‘子句可以用到索引，但’%xxx%'不行。

select * from student where name like ‘%king%’;

优化后：

select * from student where name like ‘king%’;

其中name字段建立了主键(聚集索引), 从索引中能够取得name列，不必读取数据行。如果select后的字段正好是索引，那么就用到了覆盖索引。通过覆盖索引，可以减少I/O, 提高性能。 优化后的代码如下：

select * from student a join (select id from student where name like ‘%king%’) b on a.id=b.id.

Limit优化

当查询数量超过百分之20时的时候会进行全表扫描

select * from student limit 999 20;

优化：使用范围查询先将查询的部分排除掉，如果id是主见则加上order by使用主键

select * from student id >999 order by id limit 20

注意查询字段类型匹配

注意查询字段类型匹配，减少类型的转换，

select id from student where id =“1”；

优化：

select id from student where id = 1;

尽量使用union all不要使用union

union all不创建临时表，这在执行大的联合查询中会减少I/O开销，提升查询速度。但对union语句和在最外层使用order by的语句无效。 如下SQL不会产生临时表。

(Select id from t1 order by id) union (select id from sbtest where k=0 order by id);

如下查询会产生临时表

(Select id from t1 order by id) union all (select id from sbtest where k=0 order by id) order by id desc;

Or优化

Or查询会让索引走range扫描类型：

select id from student where id = 1 or id 2；

优化：使用union all

(select id from student id = 1) union all (select id from student id = 2)

Count(*)优化

我们直接使用count(*)是走的唯一索引，

select count(*) from student

优化：后面加个聚集索引的判断

select count(id) from student id>0

ON DUPLICATE KEY UPDATE优化

ON DUPLICATE KEY UPDATE一般执行插入的时候如果主键冲突了，mysql执行更新语句，如果没有冲突则执行插入语句。

INSERT INTO student(name, age, id) VALUES(‘zhangsan’,‘12’,1)
ON DUPLICATE KEY UPDATE age=1, name=“张三”

尽量避免使用having

having会全文检索到内存里再次进行筛选。

select name from student group by id having id >40 limit 3

优化后:

select name from student where id>40 group by id limit 3

不要在where当中使用函数

一旦在where中使用了函数，索引就会失效

Select create_time from aa where Date(create_time)>curdate()

优化后：

Select create_time from aa where create_time > DATE_FORMAT(curdate(), ‘%Y-%m-%d’)

注意查询的数据量

当数据量大于百分之20的时候，会进行全文检索，因此我们一般查询都会加个范围查找不要全部查询。

Select count(id) from t1 where datetime between '2018-03-15 00:00:00’and '2018-03-16 23:59:59’and state=0;

合理建立索引

当索引包含的是平均分布的数据集时，进行全表扫描比读取索引更快。比如数据表有个字段是性别，它有两个平均分布的值（男和女）。通过这两个值分别可以读取大约50%的记录，这种情况进行全表扫描更快，因此不适合在性别上建立索引。

order by 索引优化

当查询有条件的进行索引时，最好建立索引和order by的联合索引。

select id，name from student where id >123 order by age asc;

这个情况id 和 age最好建立联合索引，这样可以避免using firesort排序。 此外，order by子句后的字段顺序要一致，即要么全部升序，要么全部降序。

小表驱动大表

leftjon的时候使用小表去驱动大表进行查询