mysql数据库SQL优化技巧十一点

2023年 8月 11日 79.6k 0

一、查询尽量不要用*,尽量写明要查询的列名。

反例

select * from pay_history

正例

select name,pay_time,order_no,money from pay_history

理由

节省资源、网络开销

可能会用到覆盖索引,减少回表,提高查询效率

二、避免在where中使用or来连接条件

反例

select name,pay_time,order_no,money from pay_history where money > 100 or name like '李%'

正例

select name,pay_time,order_no,money from pay_history where money >100union all select name,pay_time,order_no,money from pay_history where name like '李%' 

理由

使用or会导致索引失效,从而导致全表扫描。

三、尽量使用数值替代字符串类型

正例

主键(id):优先使用int、tinyint

性别(sex):女(0)男(1),mysql推荐使用tinyint

理由

因为引擎在处理查询和连接时会逐个比较字符串中每一个字符

数值类型比较一次就可以了

字符会降低查询和连接的性能,并会增加存储开销

四、where中使用默认值代替null

反例

select name,pay_time,order_no,money from pay_history where money is not null

正例

select name,pay_time,order_no,money from pay_history where money > 0

理由

如果mysql优化器发现,走索引比不走索引成本高的时候,就会放弃索引,这些条件!=,,is null,is not null经常会让索引失效;

一般情况下,查询成本高时优化器就会放弃索引;

如果把null值,换成默认值,很多时候让走索引成为可能,同时思路也相对清晰一些;

五、避免在where子句中使用!=或操作符

反例

select name,pay_time,order_no,money from pay_history where money != 100select name,pay_time,order_no,money from pay_history where money 100

理由

使用!=和 很可能让索引失效;

应避免在where子句中使用!=、操作符,否则会进行全表扫描;

如果实在没办法,可以适当使用。

六、inner join 、left join、right join

三种连接如果结果集相同,优先使用inner join,如果使用left join,左表尽量使用小表;

inner join 内连接,保留两个表中完全匹配的结果集;

left join 会返回左表中所有数据,即使在右表中没有匹配的记录;

right join 会返回右表中所有数据,即使在左表中没有匹配的记录;

理由

inner join,等值连接,返回的行数比较少,性能相对好一些

left join,使用左连接时,左表数据结果尽量少一些,条件尽量放到左表处理,返回的结果行数可能比较少;

这是 mysql优化原则,就是小表驱动大表,小的数据集驱动大数据集,使性能更好;

七、提高group by语句的效率

反例

先分组,再过滤

select name,pay_time,order_no,sum(money) from pay_history group by name having name like '李%' or name like '张%';

正例

先过滤,再分组

select name,pay_time,order_no,sum(money) from pay_history where name like '李%' or name like '张%' group by name; 

理由

在分组前,将结果集过滤完毕。

八、操作delete、update语句,加个limit或者循环分批次删除

降低写错sql的代价

清空数据可不是小事情,添加limit,即便删除错了也是丢失了部分数据,可以通过binlog快速恢复;

SQL效率可能更高

sql中加了limit 1 ,如果命中第一条记录就return,没有limit的话,还会继续执行扫描表;

避免长事务

delete执行时,如果 money加了索引,mysql会将所有相关的行加写锁和间隙锁,所有执行相关行都会被锁住,如果删除的数据量很大,会直接影响部分功能无法使用;

数据量大,CPU超负荷

如果要删除的数据量很大时,不加limit限制,会把造成cpu爆表,导致越删除越慢;

锁表

如果删除太多数据,可能造成锁表,会有lock wait timeout exceed 的错误,所以分批次操作;

九、避免在索引列上使用内置函数

反例

select name,pay_time,order_no,sum(money) from pay_history WHERE DATE_ADD(pay_time,INTERVAL 7 DAY) >=NOW();

正例

select name,pay_time,order_no,sum(money) from pay_history WHERE pay_time >= DATE_ADD(=NOW(),INTERVAL 7 DAY);

十、优化like语句

反例

select name,pay_time,order_no,sum(money) from pay_history WHERE name like '%李'; 

select name,pay_time,order_no,sum(money) from pay_history WHERE name like '%李%'; 

正例

select name,pay_time,order_no,sum(money) from pay_history WHERE name like '李%'; 

理由

如果必须使用模糊查询,避免使用全模糊查询,即like '李%',是会使用索引的;

左模糊‘%李’无法直接使用索引;

十一、使用explain分析你的SQL执行计划

type

system:

表仅有一行

const:

表最多一行数据配合,主键查询时触发较多;

eq_ref:

对于每个来自前面的表的行组合,从该表中读取一行。这可能最好的连接来行,除了const类型;

range:

只检索给定范围的行,使用一个索引来选择行。当使用=、、>、>= 、 eq_ref > ref > range > index > all

实际sql优化中,最好达到ref或range级别。

Extra常用关键字

Using index:

只从索引树中获取信息,而不需要回表查询;

Using where:

where子句用于限制哪一行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行,如果Extra不为Using where并且表联接类型为ALL或index,查询可能有一些错误,需回表查询;

Using temporary:

mysql常建一个临时表来容纳结果,典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时.

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论