SQL优化

在应用的的开发过程中，由于初期数据量小，开发人员写 SQL 语句时更重视功能上的实现，但是当应用系统正式上线后，随着生产数据量的急剧增长，很多 SQL 语句开始逐渐显露出性能问题，对生产的影响也越来越大，此时这些有问题的 SQL 语句就成为整个系统性能的瓶颈，因此我们必须要对它们进行优化，本章将详细介绍在 MySQL 中优化 SQL 语句的方法。

定位慢查询

通过慢查询可以定位到已执行完的sql中哪些sql的执行效率较低，但请注意，他是执行完之后，才有结果

开启慢查询日志

查看MySQL数据库是否开了慢查询日志和慢查询日志文件的存储位置命令

SHOW VARIABLES LIKE 'slow_query_log%' 

通过以下命令开启慢查询日志

SET GLOBAL slow_query_log = ON; // 打开慢查询日志

SET GLOBAL slow_query_log_file= 'slow.log'; //指定记录下来的sql地址

SET GLOBAL log_queries_not_using_indexes = ON;//记录没有使用索引的sql
 
SET long_query_time= 10; //设置多长时间算慢

SHOW VARIABLES LIKE 'slow_query_log_file'

查看慢查询日志

建议用mysqldumpslow查看，mysqldumpslow是MySQL提供慢查询日志分析的工具

数据展示

time：日志记录的时间

User@Host：执行的用户及主机

Query_time：执行的时间

Lock_time：锁表时间

Rows_sent：发送给请求方的记录数，结果数量

Rows_examined：语句扫描的记录条数

SET timestamp：语句执行的时间点

select....：执行的具体的SQL语句

可以通过这个工具的参数获得访问记录最多，锁定时间最长的等等数据

具体分析

SQL语句是否使用了索引，可根据SQL语句执行过程中有没有用到表的索引，可通过 explain命令分析查看检查结果的key值是否为null

在使用索引时不要只关注是否起作用，不只要创建索引，还要考虑过滤性，当过滤性好了执行速度才会提高

案例

表：student 
字段：id,name,sex,age 
造数据：insert into student (name,sex,age) select name,sex,age from student; 
SQL案例：select * from student where age=18 and name like '张%';（全表扫 描）

优化一

alter table student add index(name); //追加name索引

优化二

alter table student add index(age,name); //追加age,name索引

优化三

可以看到，index condition pushdown 优化的效果还是很不错的。再进一步优化，我们可以把名 字的第一个字和年龄做一个联合索引，这里可以使用 MySQL 5.7 引入的虚拟列来实现。
    
//为user表添加first_name虚拟列，以及联合索引(first_name,age) alter table student add first_name varchar(2) generated always as (left(name, 1)), add index(first_name, age); explain select * from student where first_name='张' and age=18;

慢查询原因总结

全表扫描：explain分析type属性all
全索引扫描：explain分析type属性index
索引过滤性不好：靠索引字段选型、数据量和状态、表设计
频繁的回表查询开销：尽量少用select *，使用覆盖索引