SQL优化

1. 记录慢SQL 

开启慢SQL日志：

SET GLOBAL slow_query_log=1;

查询慢SQL日志状态及存储位置：

show variables like '%slow_query_log%';

设置慢查询阈值(默认为10s):

set global long_query_time =1;

除输出日志外还可以将慢查询输出的MySQL的系统表中的slow_log表：

SET GLOBAL log_output ='table,file';

以上在控制台全局的设置在数据库重启后就会失效，如果要长久生效，就要在配置文件进行修改。

2. 分析慢SQL

直接打开log日志

或者直接查找slow_log表：

利用EXPLAIN对慢SQL进行分析：

explain select * from order_detail where name like '%蛙';

主要关注type和key字段

type：表示查询时使用的访问方法或策略，常见值包括ALL、index、range等，一般着重优化ALL全表扫描。

key:表示查询时所使用的索引。

3.SQL优化(SQL语句优化和索引优化)

实例表

创建普通联合索引：

ALTER TABLE user ADD INDEX `idx_username_name_age` (`username`, `name`, `age`);

1. 索引全覆盖：是指在数据库查询过程中，所需的所有数据都可以从索引中直接获取，而无需访问表中的实际数据行。例如：

SELECT name, age FROM user WHERE username = 'Tom';

需要查询的name和age字段都可以直接从idx_username_name_age索引中直接获取。

2. 遵循最左前缀

SELECT * FROM user WHERE name = 'Qwen' AND username = 'laowang' AND age = 30 

A_B_C复合索引，如果查询条件是A_C,则只有A索引生效，如果是A_B,则A和B都生效，如果是B_C,则索引失效。

3. 索引列上少计算：

不在索引列上做任何操作(计算、函数、类型转换)，会导致索引失效转向全表扫描。

例如: 

where Year(date) = 2025   where (Varchar)2 = (Integer)2

4. 范围之后全失效

还是A_B_C的复合索引，如果 where A = a AND B > b AND C = c，因为范围查找后后续字段无序，所以C索引失效。 

5. Like百分写最右

where name = '%xx'，索引失效，where name='xx%'，索引生效，因为百分写前面相当于要对全部数据查询在确定是否包含xx，百分写前面可以根据name索引确定xx开头的，所以可以使用索引。

6. 覆盖索引不写星

写星会导致查询时回表

7. 不等空值还有or会导致索引失效

如果语句中提示存在possible_key，可以进行优化，使用FORCE INDEX(索引名)

explain SELECT name, age FROM user FORCE INDEX (idx_username_name_age) WHERE username != 'Tom';

or导致失效是因为数据库不能支持直接合并不同索引，所以优化器选择全表扫描。

可以使用Union ALL代替(当name和username都有索引时)

explain select * from user where name='汤姆' or username='Rose';
explain (select * from user where name = '汤姆')union ALL(select * from user where username='Rose');

8 字符串不加单引号索引失效

explain select * from user where name='1000';
explain select * from user where name= 1000;

其实就是类型转化，需要对每一个name都转化为Integer，所以选择全表扫描。

SQL语句优化：

1. 避免使用select *

查询时需要将星号解析成表的所有字段然后再查询，增加解析器的成本

select * 查询一般不走覆盖索引会产生大量的回表查询

在实际应用中通常只需要使用某几个字段，其他不需要使用的字段也查询出来浪费CPU和内存资源

2. 小表驱动大表

3.当子查询是小表，查询是大表，可以用连接查询代替子查询使索引生效。

4. 为group by字段添加索引

5. 将循环插入改变为动态SQL批量插入(控制在500以内)

6. 利用limit进行分页查询，对于大数据量的表限制分页查询范围。因为offset越大，查询越慢

对于limit 10000 10，需要先查询1万条数据才得到后面10条数据。改进 :

where id>=10000 limit 10 # 直接定位到一万的位置

7. 在不要求去重的情况下使用Union all 替代Union

8. join的表不宜过多，一般控制在3个以下