SQL优化

最新推荐文章于 2024-06-11 11:21:23 发布

LBL1220

最新推荐文章于 2024-06-11 11:21:23 发布

阅读量226

点赞数

CC 4.0 BY-SA版权

文章标签： sql 数据库 mysql

本文链接：https://blog.youkuaiyun.com/oldboy1999/article/details/125951698

MySQL 专栏收录该内容

27 篇文章

订阅专栏

本文探讨了SQL查询中如何通过选择具体字段、利用唯一索引、减少or操作、优化like和函数使用、理解索引失效原因等技巧，有效提升查询性能。涉及创建索引、联合索引策略、避免冗余和正确使用数据类型等内容，帮助开发者优化数据库查询性能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1）查询select尽量具体到某字段，而不是 select *

用 select * 可能会回表查询，增加资源开销。

2）确定查询字段（普通索引）只有一条记录时，使用limit 1
如果查询字段是唯一索引，就不用加了，因为已经确定是一条了。

3）减少where条件句中or的使用。

 or 的前后字段中如果一个有索引，一个没有索引，那么很可能会全表扫描，不走索引。
因为Mysql的优化器会判断，无论如何都要全表扫描，因此放弃使用索引额外的开销。

这里是有关索引失效的文章链接。
可能导致索引失效的原因

4）like语句后的%不能放在开头，不然很可能索引失效

反面教材
select id from test where name like '%k';

百分号放在中间或字符最后，还是会走索引的。

5）尽量不在索引列的查询中使用MySQL的内置函数，否则索引可能失效

6）避免在where子句中使用数学运算式子，否则索引失效

反面教材
select name from test where id+2=10;

正确的语句是select name from test where id=8;

7）对于辅助索引的字段，不要使用下列的“不等于”号，不走索引

!=  
<>  
not in

但是如果字段是聚集索引，上述语句会走索引

8）联合索引中注意尽量遵循最左匹配原则，可能索引失效，也可能走索引

创建联合索引（k1,k2,k3)相当于创建了3个索引(k1) (k1,k2) (k1,k2,k3)
如果查询(k2) (k3) (k2,k3)可能不走索引

搭建测试环境
CREATE TABLE `user1`( 
`id` INT(11) NOT NULL AUTO_INCREMENT, 
`userId` VARCHAR(32) NOT NULL,  
`age` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_userid_age`(`userId`,`age`) USING BTREE
)ENGINE=INNODB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

在这里插入图片描述
下面是各种情况下是否走索引的执行计划分析

8-1）如果where子句后跟单独的age，一般不走索引，但也可能走索引。
下面是我的执行计划分析结果。

DESC SELECT * FROM user1 WHERE age>10;

在这里插入图片描述

8-2）当我们建议还是遵循最左匹配原则，因为Mysql的不同优化器也是影响索引执行的因素。

DESC SELECT * FROM user1 WHERE userId=10 AND age=10;

在这里插入图片描述

9）查询中where和order by子句后的字段应建立索引（联合索引）

10）使用覆盖索引提高查询效率

id是主键  name是普通索引  
select id,name from test where age like '2%';

11）删除冗余和重复索引

建立联合索引（k1,k2）就相当于建立索引（k1）和（k1,k2）
如果再单独对k1字段建立索引，就会重复，会影响性能

12）当修改或删除的数据量过大，应该分批操作

反面教材
delete * from test where id<20000;

正面教材
delete * from test where id<10000;
delete * from test where id>10000 and id<20000;

一次性删除大量数据，会造成CPU利用率过高，甚至有锁等待问题，影响其他事务对行内容的查询。

13）如果可能，尽量将null换成默认值，走索引的可能性更高。
使用null走不走索引和mysql的查询成本有关，优化器会选择成本更低的方式

14）索引不是越多越好，5个以内最好。

索引的建立需要空间，维护也是有成本的。
数据量越大，索引的构建也增加了查询的资源消耗。

15）如果能用数字型字段，就不用字符型字段。

相较而言，数字型字段的查询和连接性能更高，存储开销更小。

16）注意隐式转换导致的可能的索引失效

如果是字符串类型的字段，where后的子句需用单引号括起来，进行字符类型匹配

17）养成分析SQL语句的执行计划的习惯

desc     SQL语句
explain  SQL语句

参考文章：
https://mp.weixin.qq.com/s?__biz=Mzg3NzU5NTIwNg==&mid=2247487972&idx=1&sn=cd035a7fcd7496658846ab9f914be2db&chksm=cf21cecdf85647dbc53e212bf1a2b95d0eb2bffe08dc0141e01f8a9b2088abffc385a2ef584e&token=1495321435&lang=zh_CN&scene=21#wechat_redirect