mysql的sql优化实用

sql优化

一、sql的执行顺序

(1) FROM <left_table> 
(2) <join_type> JOIN <right_table> 
(3) ON <join_condition> 
(4) WHERE <where_condition> 
(5) GROUP BY <group_by_list>
(6) WITH {CUBE | ROLLUP} 
(7) HAVING <having_condition> 
(8) SELECT 
(9) DISTINCT 
(10) ORDER BY <order_by_list> 
(11) <TOP_specification> <select_list>

序号的每个步骤会产生一个虚拟表,如果想要记住这个觉得可能对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<B.length;i++) {
  for(int j=0;j<A.length;j++) {
    if(A[i].id==B[j].id) {
      resultSet.add(A[i]);
      break;
    }
  }
}

10.union all和union

  • union和union all的区别是,union会自动去掉多个结果集合中的重复结果,而union all则将所有的结果全部显示出来,不管是不是重复
  • union:对两个结果集进行并集操作,不包括重复行,同时进行默认规则的排序
  • union在进行表链接后会筛选掉重复的记录,所以在表链接后会对所产生的结果集进行排序运算,删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录,最常见的是过程表与历史表UNION
  • 3
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值