SQL优化的步骤和方法

目录

前言

一、优化sql语句的步骤

1.1通过show status 命令了解各种sql的执行频率

1.2定位执行效率较低的sql语句

1.2.1慢日志

1.2.2show processlist

1.2.3explain分析sql语句

1.2.4采取相应措施，创建合适索引

二、sql优化的方法

1.使用索引优化

2.查看索引的使用情况

3.两个简单实用的优化方法

3.1定期分析表和检查表

3.2定期优化表

3.常用的sql优化

3.1大批量插入数据

3.2优化insert语句

3.3优化group by语句

3.4or条件优化

---

---

前言

这几天看到群里高手发的sql优化的基本思路步骤：增加过滤条件，创建合适索引，简化复杂sql，减少io扫描。

下面将对sql优化的步骤和方法进行介绍

---

一、优化sql语句的步骤

1.1通过show status 命令了解各种sql的执行频率

show global/session status like 'Com_%';

show global/session status like 'Innodb_rows%';//Innodb存储引擎下的累加。对于更新操作的技术，不论提交还是回滚都进行累加。

1.2定位执行效率较低的sql语句

1.2.1慢日志

慢日志是优化必会看的日志，定位那些执行效率比较低的sql语句。

1.2.2show processlist

慢查询日志在查询结束之后才能记录，所以在执行效率出问题的时候慢查询日志不能定位问题，之后问题出现之后查询结束之后才能记录到慢日志。可以使用show processlist查看当前MySQL的线程。包括线程的状态和是否锁表，可以实时的查看sql的执行情况，同时对一些锁表操作进行优化。

可以看到哪个数据库的等待时间。

kill线程

干掉mysql连接线程的两种方法_清除数据库连接线程的命令-CSDN博客

1.2.3explain分析sql语句

以前文章写过explain的详细分析，可以翻我以前的文章。

1.2.4采取相应措施，创建合适索引

二、sql优化的方法

1.使用索引优化

创建合适索引：如何创建高效的索引-CSDN博客

导致索引失效的情况：导致索引失效的情况汇总（持续更新）_selecte*索引失效-CSDN博客

表连接的优化：JOIN算法原理 及 优化-CSDN博客

2.查看索引的使用情况

show global/session status like 'Handler_read%';

主要看两个值，一个是Handler_read_key，一个是Handler_read_rnd_next。

Handler_read_key：如果该属性比较大，则证明数据库索引使用率比较好。

Handler_read_rnd_next：如果该属性比较大，则证明数据库全表扫描比较多，需要进行优化，这个属性的意思就是 在数据文件中读下一行的请求数。

3.两个简单实用的优化方法

3.1定期分析表和检查表

analyze table table_name

check table table_name

3.2定期优化表

optimize table table_name；

在innodb中optimize会报错，使用alter table table_name engine=innodb;

3.常用的sql优化

3.1大批量插入数据

（1）对于Innodb来说批量插入可以关闭唯一性校验，提高导入效率。

        set unique_checks = 0，在导入结束后执行set unique_checks = 1;

（2）也可以关闭自动提交（mysql的事务是自动提交的），可以关闭自动提交，导入结束后再执行set autocommit = 1，开启自动提交。也可以增加导入效率。

3.2优化insert语句

尽量一次插入多值

insert into table_name values（1，2),（2,3),.....

3.3优化group by语句

group by是分组的子句，但是默认是排序的，也就是默认有order by子句跟着。

但是有时候用户可以避免排序带来的小号，可以指定order by null禁止排序。

select * from table_name group by col_name order by null；

3.4or条件优化

在两个列上都建上索引。where子句中使用 or 来连接条件的各种情况_sql where or-CSDN博客

---