hjr-SQL-语句的优化

语句分析

首先在查询语句前面加上explain

然后看type 列，是索引生效的不同类型

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

尽量往上优化

没索引的加上索引

索引在join on上一般都是 all

尽量在where上加上条件，某些写法会导致索引失效，或者降低到all，自己多尝试

rows 是必须检查的行数 越少越好

写法

把每个表自身的where条件查询结果写到一个括号里，查询后再join
和先join 再统一通过表的别名 where 去查询 没区别

运算

某些每join一次查询都会做的运算，可以移动到代码里，直接传一个固定值到sql中

批量

当for循环里面有插入或删除操作，可以先建一个list，然后放到for循环外面批量插入

where和on的区别

left join where和on有区别

inner join 没区别

当条件放在on后面时，无论on条件的真假，都会返回左表的值；

当条件放在where后面时，只有满足条件的记录会返回。

where 查询速度快 on慢 where返回数据少 on多

比如 学生表a 和分数表b

学生有字段 姓名 年龄 分数有 学科 分数

left join 后相当于一个新表c 拥有字段 姓名 年龄 学科 分数四个字段

如果查询条件写到where后，那么相当于对c表做筛选，

比如同样的学科为数学作为筛选条件

现在学生表有3条数据，分数表有2条数据（一条语文，一条数学）

如果我们左连接不做条件筛选那么会得到3条数据

如果我们把筛选条件写到on后面了也是三条，因为相当于先对表b做筛选，现在表a有三条，表b有一条，左连接后表c有三条数据

但是如果写到where后，原本表c应该是三条，但是要对表c做一个筛选，学科为数学，所以最后表c只有一条数据了

根据业务拆分

比如一个用户表 一个订单表

先查询用户数据

10 条，然后for遍历 这10条 然后 in 查询订单表

代替 用户表left join 订单表

派生表

虽然派生表不走索引速度慢，但是当有查询条件时只能用派生表

加筛选条件必须做派生表 条件写到后面

否则 写到 where后面 会影响join的结果

比如 用户 有 3个订单 1,2,3 返回的列表应该是用户 有 （1,2，3）订单

现在筛选条件是订单等于1 如果写到where后面

最后返回的列表会 （1）

如果先join查询出 用户 拥有的订单为（1,2，3）

再筛选条件是订单等于1 返回的用户列表是 订单为（1,2，3）

并且不用派生表 select * from (select sum(a) as ss left join… where sum(a)>xx) as table where ss >xx

只能这么写条件
sum(a)>xx

用了派生表可以这么写条件
ss >xx

不过数据量达到百万级使用派生表速度大概要慢5s，所以筛选条件的复杂度决定要不要用派生表

on条件移动到where

on后面的and条件比在where后慢很多

但是不放到on后面有时会导致返回数据变少

但是当某些特定条件下，依旧可以把on后面的条件放到where后面

比如 对某些特定筛选条件单独写一个sql的优化，

只有2个表，a左连接b，on后面的条件肯定能命中非Null数据

这个条件放到on后面或者where后都无所谓的情况

count(*)的优化

select count(*) from(select * from where xxx group by x) as xx

可以改为

select count(distinct x) as num where xxx 去掉group by

具体某个条件的优化

比如查询学生分数，条件是入学日期，其他的查询条件速度都很快，就这一个慢，我们可以把这个条件单独写一个sql，然后不同的查询条件走不同的sql

表做冗余

如果a左连接b表，a和b表数据都很多，可以先给a表做冗余字段，然后定时清洗b的数据到a的字段里

排序

当备注栏里面有filesort的时候就需要对排序优化为index sort

如果没有排序需求 还group by了，需要在group by后面加上order null 这样可以去掉filesort因为会自动对group by排序

当需要排序的时候，可以试着
建立组合索引，满足最左生效原则
或者把group by给去掉

或者把group by的条件和order by设置成一样的

多个排序条件不要一个desc 一个asc的 要保持一个方向

字符集索引失效

SHOW FULL COLUMNS FROM tabelName
有时on 条件索引莫名失效
查看字符集看条件字段是否是同一个编码

不是的话修改为统一编码
alter table <表名> change <字段名> <字段名> <类型> CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;