sql优化

• sql优化
• • 一、sql的执行顺序
  • 二、sql优化基础
  • • 1.select *的避免，使用select 字段，字段
    • 2.where子句中的or谨慎用，使用union、union all
    • 3.使用varchar代替char
    • 4.使用数值替代字符串
    • 5. 避免返回大数据量，使用分页5.
    • 6.创建索引
    • 7.优化like，最左匹配原则，%加在最后边有效
    • 8.字符串相关隐式转换
    • 9.索引不宜太多
    • 10.大量重复数据索引会失效
    • 11.避免索引列使用内置函数
    • 12.避免where中对字段进行表达式操作
    • 13.尽量避免在where使用！=或者<>操作符
    • 14.去重distinct过滤的字段要少
    • 15.where使用默认值代替null
  • 二、高级sql优化
  • • 1.批量插入性能提升
    • 2.批量删除优化
    • 3.伪删除设计
    • 4.提高group by语句效率
    • 5.复合索引最左
    • 6.where和order by中常出现的字段就创建索引
    • 7.不要有超过5个以上的表连接（阿里规范中，建议多表联查三张表以下）
    • 8.inner join 、left join、right join，优先使用inner join
    • 9.in子查询的优化
    • 10.union all和union

sql优化

一、sql的执行顺序

(1) FROM  
(2)  JOIN  
(3) ON  
(4) WHERE  
(5) GROUP BY 
(6) WITH {CUBE | ROLLUP} 
(7) HAVING  
(8) SELECT 
(9) DISTINCT 
(10) ORDER BY  
(11)  

序号的每个步骤会产生一个虚拟表，如果想要记住这个觉得可能对sql写的多的人自然就记住，可以通过sql逻辑记住前置条件（比如：我是这样想的sql执行第一肯定要去拿表所以from最先，之后就是可能存在的表的拼接利用join，以及join的条件on，后面对得到初步的表进行where条件过滤，等过滤结束如果有必要分组的话按照group by进行分组，而想要group by进行相应条件再次过滤后就需要having条件，最后筛选出字段数据，删除重复对应字段，按顺序排序）

二、sql优化基础

这个是有必要去自己去写sql的时候去注意的，因为系统上线后过段时间就会出现很多问题，当然具体问题还是得具体分析，看完收货满满。
参考：https://blog.csdn.net/weixin_53601359/article/details/115553449

1.select *的避免，使用select 字段，字段

原因：

1. 当表格拥有很多字段的时候，很有可能使用select*就会用不到索引，导致全表扫描。
2. 对于只取需要的字段，可以节省资源，减少网络。

2.where子句中的or谨慎用，使用union、union all

原因：

1. 很容易索引失效导致全表扫描。

3.使用varchar代替char

原因：

1. varchar按照字段数据内容实际长度存储，而char按照声明大小存储不足补空格，空间浪费，同时字段。

4.使用数值替代字符串

• 主键：int
• 性别：0，1 tinyint等等

5. 避免返回大数据量，使用分页5.

原因：大数据量导致查询时间长，资源浪费，网络传输长。

6.创建索引

原因：索引像个目录一样可以快速查找到想要数据

ALTER TABLE `表` ADD INDEX 索引名 

7.优化like，最左匹配原则，%加在最后边有效

8.字符串相关隐式转换

返例：

select * from `表` where NAME=123

上面的123没有加单引号导致在做sql查询时候mysql做了隐式转换，转成数字进行比较。
改为：

select * from `表` where NAME='123'

9.索引不宜太多

• 索引越多会导致插入，删除效率下降。
• 同时索引本身就可以理解为一个表，占空间。
• 插入或删除导致，索引重建，重排序等。

10.大量重复数据索引会失效

原因：mysql查询优化器不走索引成本低，放弃索引。

11.避免索引列使用内置函数

反例：

EXPLAIN
SELECT * FROM student
WHERE DATE_ADD(birthday,INTERVAL 7 DAY) >=NOW();

正例：

EXPLAIN
SELECT * FROM student
WHERE  birthday >= DATE_ADD(NOW(),INTERVAL 7 DAY);

12.避免where中对字段进行表达式操作

反例：

EXPLAIN
SELECT * FROM student WHERE id+1-1=+1

正例：

EXPLAIN
SELECT * FROM student WHERE id=+1-1+1

13.尽量避免在where使用！=或者<>操作符

14.去重distinct过滤的字段要少

15.where使用默认值代替null

理由：

• 并不是说使用了is null 或者 is not null 就会不走索引了，这个跟mysql版本以及查询成本都有关
• 如果mysql优化器发现，走索引比不走索引成本还要高，就会放弃索引，这些条件 !=，<>，is null，is not null经常被认为让索引失效，其实是因为一般情况下，查询的成本高，优化器自动放弃索引的
• 如果把null值，换成默认值，很多时候让走索引成为可能，同时，表达意思也相对清晰一点

二、高级sql优化

1.批量插入性能提升

正例：

INSERT INTO student (id,NAME) VALUES(4,'name1'),(5,'name2');

理由：事务开启关闭是需要时间的。

2.批量删除优化

理由：一次性删除太多数据，可能造成锁表，会有lock wait timeout exceed的错误，所以建议分批操作。

3.伪删除设计

商品状态（state）：1-上架、2-下架、3-删除

理由：

• 这里的删除只是一个标识，并没有从数据库表中真正删除，可以作为历史记录备查
• 同时，一个大型系统中，表关系是非常复杂的，如电商系统中，商品作废了，但如果直接删除商品，其它商品详情，物流信息中可能都有其引用。
• 通过where state=1或者where state=2过滤掉数据，这样伪删除的数据用户就看不到了，从而不影响用户的使用
• 操作速度快，特别数据量很大情况下

4.提高group by语句效率

反例：先过滤后分组

select job，avg（salary） from employee  
group by job 
having job ='president' or job = 'managent';

正例：先分组后过滤

select job，avg（salary） from employee 
where job ='president' or job = 'managent' 
group by job;

5.复合索引最左

创建复合索引，也就是多个字段

ALTER TABLE student ADD INDEX idx_name_salary (NAME,salary)

满足复合索引的左侧顺序，哪怕只是部分，复合索引生效。
特例：

• 虽然违背了最左特性，但MYSQL执行SQL时会进行优化，底层进行颠倒优化

EXPLAIN
SELECT * FROM student WHERE salary=3000 AND NAME='name1'

理由：
复合索引也称为联合索引当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则
联合索引不满足最左原则，索引一般会失效，但是这个还跟Mysql优化器有关的

6.where和order by中常出现的字段就创建索引

7.不要有超过5个以上的表连接（阿里规范中，建议多表联查三张表以下）

8.inner join 、left join、right join，优先使用inner join

理由：

• 如果inner join是等值连接，返回的行数比较少，所以性能相对会好一点。
• 同理，使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少。这是mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优。

9.in子查询的优化

例子：查询所有部门的所有员工。

• 使用sql脚本

#in子查询
SELECT * FROM tb_user WHERE dept_id IN (SELECT id FROM tb_dept);
#这样写等价于：#先查询部门表
SELECT id FROM tb_dept#再由部门dept_id，查询tb_user的员工
SELECT * FROM tb_user u,tb_dept d WHERE u.dept_id = d.id

• 使用程序实现(假设表A表示某企业的员工表，表B表示部门表，查询所有部门的所有员工，很容易有以下程序实现，可以抽象成这样的一个嵌套循环)

List<> resultSet;
for(int i=0;i