首页 > 数据库 >日常工作中,你是如何优化SQL的?

日常工作中,你是如何优化SQL的?

时间:2024-10-30 21:18:38浏览次数:3  
标签:index 查询 索引 日常 SQL 优化 id

如何优化SQL

  • 加索引

  • 避免常见的索引不生效场景

  • 避免返回不必要的数据

  • 减少不必要的逻辑

  • 分批量进行思想

  • 读写分离

  • 优化sql结构

  • 分库分表

  • 性能优化分析神器—explain

  • 慢SQL排查思路

1. 加索引

很多时候,我们的慢查询,都是因为历史原因没有加索引,或者忘记加索引导致的。如果没有加索引的话,会导致全表扫描的。因此,应考虑在where的条件列,建立索引,尽量避免全表扫描。

反例:

select * from user_info where name ='张三' ;

正例:

//添加索引
alter table user_info add index idx_name (name);

2. 避免常见的索引不生效场景

我之前整理了常见的十种索引不生效的场景,大家可以看看:

  1. 隐式的类型转换,索引失效

  2. 查询条件包含or,可能导致索引失效

  3. like通配符可能导致索引失效

  4. 查询条件不满足联合索引的最左匹配原则

  5. 在索引列上使用mysql的内置函数

  6. 对索引进行列运算(如,+、-、*、/)

  7. 索引字段上使用(!=或者<>),索引可能失效

  8. 索引字段上使用is null,is not null,索引可能失效

  9. 左右连接,关联的字段编码格式不一样

  10. 优化器选错了索引

3. 避免返回不必要的数据

这个点,我在昨天的文章,其实就提到一个点,包括尽量使用limit,避免不必要的返回

其实这不仅仅是一个点,而是一种思想,就是要什么查什么,而不是返回一些不必要的数据。还有:查询SQL尽量不要使用select *,而是select具体字段。也是这种思想。

反例子:

select * from employee;

正例子:

select id,name, age from employee;
  • select具体字段,节省资源、减少网络开销。

  • select * 进行查询时,很可能就不会使用到覆盖索引了,就会造成回表查询。

4. 减少不必要的逻辑

其实,尽量用 union all 替换 union,就是这种思想。

如果我们明知道,检索结果中不会有重复的记录,推荐union all 替换 union。

因为:

如果使用union,不管检索结果有没有重复,都会尝试进行合并,然后在输出最终结果前进行排序。如果已知检索结果没有重复记录,使用union all 代替union,这样会提高效率。

5. 分批量进行思想

我们更推荐批量查询、插入、删除。

反例:

for(User u :list){
 INSERT into user(name,age) values(#name#,#age#)   
}

正例:

//一次500批量插入,分批进行
insert into user(name,age) values
<foreach collection="list" item="item" index="index" separator=",">
    (#{item.name},#{item.age})
</foreach>

理由:

  • 批量插入性能好,更加省时间

  • 打个比喻: 假如你需要搬一万块砖到楼顶,你有一个电梯,电梯一次可以放适量的砖(最多放500),你可以选择一次运送一块砖,也可以一次运送500,你觉得哪个时间消耗大?

6. 读写分离

一般情况我们的数据库架构,都要做主从的,然后进行读写分离。主库主要负责写,和一些实时性比较高的读。而从库就负责读实时性要求不高的请求。

图片

这样的话,我们不用所有请求都到主库,大大降低了主库的压力。你试想一下,如果所有读请求都到主库,查询压力肯定很大,处理也会相对慢一点。

7. 优化sql结构、逻辑

有些时候,优化SQL结构,都能有一些预想不到的优化效果。

假设我们有个客户表和一个订单表。其中订单表有10万记录,客户表只有1000行记录。

现在要查询下单过的客户信息,可以这样写:

SELECT * FROM customers
WHERE id IN (
    SELECT customer_id FROM orders
);

in 查询会先执行内部查询部分 SELECT customer_id FROM orders,获得 orders 表(大表)中的所有 customer_id,然后在 customers 表(小表)中查找匹配的 id。

也可以这样实现:

SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

EXISTS 会逐行扫描 customers 表(即小表),对每一行 c.id,在 orders 表(大表)中检查是否有 customer_id = c.id 的记录。

因为orders表的数据量比较大,因此这里用exists效果会相对更好一点。其实这就是小表驱动大表的思想。我们也只是调整SQL结构,用exists去替换in,优化效果也是比较明显的。

8. 分库分表

如果单表的数据量很大,达到百万甚至千万级别,我们这种时候,就是加了索引,可能效果也不是很明显。这时候我们可以考虑分库分表啦~~

分库分表一般都是依赖客户号、用户Id、或者时间来拆分。但是需要注意一下,分库分表存在的一些一些问题:

  • 事务问题

  • 跨库关联JOIN

  • 排序问题

  • 分页问题

  • 分布式ID选择

9. 性能优化分析神器—explain

之前我写SQL习惯的时候,有提到explain,就是每次写完查询SQL,都用explain看一下它的执行计划。

有些面试官会单独问这个,我们可以走面试官的路,让面试官无路可走。在回答SQL优化的时候,就把这个回答了。

一般在使用explain的时候,我们要关注:type、rows、filtered、extra、key。

9.1 type

type表示连接类型,查看索引执行情况的一个重要指标。以下性能从好到坏依次:system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

  • system:这种类型要求数据库表中只有一条数据,是const类型的一个特例,一般情况下是不会出现的。

  • const:通过一次索引就能找到数据,一般用于主键或唯一索引作为条件,这类扫描效率极高,,速度非常快。

  • eq_ref:常用于主键或唯一索引扫描,一般指使用主键的关联查询

  • ref : 常用于非主键和唯一索引扫描。

  • ref_or_null:这种连接类型类似于ref,区别在于MySQL会额外搜索包含NULL值的行

  • index_merge:使用了索引合并优化方法,查询使用了两个以上的索引。

  • unique_subquery:类似于eq_ref,条件用了in子查询

  • index_subquery:区别于unique_subquery,用于非唯一索引,可以返回重复值。

  • range:常用于范围查询,比如:between ... and 或 In 等操作

  • index:全索引扫描

  • ALL:全表扫描

9.2 rows

该列表示MySQL估算要找到我们所需的记录,需要读取的行数。对于InnoDB表,此数字是估计值,并非一定是个准确值。

9.3 filtered

该列是一个百分比的值,表里符合条件的记录数的百分比。简单点说,这个字段表示存储引擎返回的数据在经过过滤后,剩下满足条件的记录数量的比例。

9.4 extra

该字段包含有关MySQL如何解析查询的其他信息,它一般会出现这几个值:

  • Using filesort:表示按文件排序,一般是在指定的排序和索引排序不一致的情况才会出现。一般见于order by语句

  • Using index :表示是否用了覆盖索引。

  • Using temporary: 表示是否使用了临时表,性能特别差,需要重点优化。一般多见于group by语句,或者union语句。

  • Using where : 表示使用了where条件过滤.

  • Using index condition:MySQL5.6之后新增的索引下推。在存储引擎层进行数据过滤,而不是在服务层过滤,利用索引现有的数据减少回表的数据。

9.5 key

该列表示实际用到的索引。一般配合possible_keys列一起看。

10. 慢SQL排查思路

如果大家平时有优化过生产的慢SQL,有自己的一套排查那一套最好哈。如果没有的话,可以在自己搞个深分页,或者因为数据量、或者因为没加索引等原因,导致的慢SQL,然后按照这个思路去排查一遍。

  • 查看慢查询日志记录,分析慢SQL

  • explain分析SQL的执行计划

  • profile 分析执行耗时

  • Optimizer Trace分析详情

  • 确定问题并采用相应的措施

标签:index,查询,索引,日常,SQL,优化,id
From: https://blog.csdn.net/zhzjn/article/details/143375325

相关文章

  • NPM 包开发与优化全面指南
    1.理解NPM包的结构1.1package.json文件:包的核心1.2理解包的入口点2.深入理解模块格式2.1CommonJS(CJS)2.2ECMAScript模块(ESM)2.3通用模块定义(UMD)3.高级包优化技术3.1TreeShaking和副作用3.2代码分割和动态导入3.3条件......
  • 【SQL】Hive/Spark SQL笔记之时间函数、环比/同比/时间比较计算
    获取当天:'${zdt.format("yyyy-MM-dd")}'//获取上月月末select'${zdt.lastMonth().format("yyyy-MM-dd")}'T-1上月末select'${zdt.addDay(-1).lastMonth().format("yyyyMMdd")}'1个小时前select'${zdt.addHour(-1)......
  • MySQL索引
    索引概述介绍索引(index)是帮助MySQL高效获取数据的数据结构(且有序)。在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。优缺点索引结构MySQL的索引是在存储引......
  • MySQL存储引擎——针对实习面试
    目录MySQL支持哪些存储引擎?MySQL存储引擎架构了解嘛?MyISAM和InnoDB有什么区别?MyISAM和InnoDB如何选择?如何查看表的存储引擎?如何修改表的存储引擎?MySQL支持哪些存储引擎?MySQL支持多种存储引擎,包括InnoDB、MyISAM、MEMORY、ARCHIVE、BLACKHOLE、FEDERATED、NDBCluste......
  • Unity的SkinnedMeshRenderer性能优化
    Unity支持两种主要的Skinning技术在Unity中,Skinning主要指的是角色的蒙皮过程,这是3D动画中的一个关键步骤,用于将3D模型的网格(皮肤)附着到骨骼上,使得模型可以根据骨骼的动作进行逼真的变形。Unity支持两种主要的Skinning技术:CPUSkinning和GPUSkinning。1.CPUSkinning......
  • Ubuntu22 mysql8 MHA高可用
    mha-manager地址:https://github.com/yoshinorim/mha4mysql-managermha-node地址:https://github.com/yoshinorim/mha4mysql-node mha有mha-manager和mha-node2个组件由于主节点有可能会被切换成从节点,而每一个从节点也有可能会被切换成主节点所以MHA对Mysql复制环境有特......
  • mybatis动态SQL
    目前项目中写动态SQL,用的都是下面的语法:@Select("<script>"+"SELECTwr.id,wr.customer_id,wr.type,wr.detailfromxxxrel"+"LEFTJOINxxxwronrel.rule_id=wr.idwhererel.entity_id=#{entityId}andwr.customer_id=#{......
  • Mysql梳理11——聚合函数
    Mysql梳理11——聚合函数Mysql梳理11——聚合函数11.1引言11.2聚合函数介绍11.2.1什么是聚合函数11.2.2聚合函数类型11.2.3聚合函数语法11.3具体聚合函数11.3.1AVG和SUM函数11.3.2MIN和MAX函数11.3.3COUNT函数11.4GROUPBY11.4.1基本使用11.4.2使用多个列......
  • SQL Server创建用户只能访问指定数据库和视图
    我们在给数据库用户赋予权限时,有时候不想让该用户看到太多过程表和过程视图,这时就需要限定用户的访问权限第一步:创建用户创建数据库连接后,进入安全性——登录名,单击右键,新建登录名,并设置默认数据库第二步:设置用户映射点击用户映射,勾选指定要访问的数据库,数据库成员身份默认......
  • MySQL敏感数据进行加密的几种方法
    使用MySQL内置的加密函数AES_ENCRYPT和AES_DECRYPT函数方法介绍:AES(AdvancedEncryptionStandard)是一种对称加密算法。在MySQL中,可以使用AES_ENCRYPT函数对数据进行加密,使用AES_DECRYPT函数进行解密。这种加密方式的特点是加密和解密使用相同的密钥。示例:......