老司机总结的12条 SQL 优化方案(非常实用)(二)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
云数据库 RDS PostgreSQL,高可用系列 2核4GB
简介: 老司机总结的12条 SQL 优化方案(非常实用)(二)

索引的优化/如何避免索引失效

1.最佳左前缀法则

如果索引了多列,要遵守最左前缀法则,指的是查询从索引的最左前列开始并且不跳过索引中的列。Mysql查询优化器会对查询的字段进行改进,判断查询的字段以哪种形式组合能使得查询更快,所有比如创建的是(a,b)索引,查询的是(b,a),查询优化器会修改成(a,b)后使用索引查询。

2.不在索引列上做任何操作

1.计算:对索引进行表达式计算会导致索引失效,如 where id + 1 = 10,可以转换成 where id = 10 -1,这样就可以走索引

2.函数:select * from t_user where length(name)=6; 此语句对字段使用到了函数,会导致索引失效

从 MySQL 8.0 开始,索引特性增加了函数索引,即可以针对函数计算后的值建立一个索引,也就是说该索引的值是函数计算后的值,所以就可以通过扫描索引来查询数据。

alter table t_user add key idx_name_length ((length(name)));

(自动/手动)类型转换

  • (字符串类型必须带''引号才能使索引生效)字段是varchar,用整型进行查询时,无法走索引,如select * from user where phone = 13030303030

Mysql 在执行上述语句时,会把字段转换为数字再进行比较,所以上面那条语句就相当于:select * from user where CAST(phone AS signed int) = 13030303030; CAST 函数是作用在了 phone 字段,而 phone 字段是索引,也就是对索引使用了函数!所以索引失效

  • 字段是int,用string进行查询时,mysql会自动转化,可以走索引,如:select * from user where id = '1'

MySQL 在遇到字符串和数字比较的时候,会自动把字符串转为数字,然后再进行比较。以上这条语句相当于:select * from user where id = CAST(“1” AS signed int),索引字段并没有用任何函数,CAST 函数是用在了输入参数,因此是可以走索引扫描的。

3.存储引擎不能使用索引中范围条件右边的列。

如这样的sql: select * from user where username='123' and age>20 and phone='1390012345',其中username, age, phone都有索引,只有username和age会生效,phone的索引没有用到。

4.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))

select age from user,减少select *

5.mysql在使用负向查询条件(!=、<>、not in、not exists、not like)的时候无法使用索引会导致全表扫描。

你可以想象一下,对于一棵B+树,根节点是40,如果你的条件是等于20,就去左面查,你的条件等于50,就去右面查,但是你的条件是不等于66,索引应该咋办?还不是遍历一遍才知道。

6.is null, is not null 也无法使用索引,在实际中尽量不要使用null(避免在 where 子句中对字段进行 null 值判断) 不过在mysql的高版本已经做了优化,允许使用索引

对于null的判断会导致引擎放弃使用索引而进行全表扫描。

7.like 以通配符开头(%abc..)时,mysql索引失效会变成全表扫描的操作。

所以最好用右边like ‘abc%’。如果两边都要用,可以用select username from user where username like '%abc%',其中username是必须是索引列,才可让索引生效

假如index(a,b,c), where a=3 and b like ‘abc%’ and c=4,a能用,b能用,c不能用,类似于不能使用范围条件右边的列的索引

对于一棵B+树索引来讲,如果根节点是字符def,假如查询条件的通配符在后面,例如abc%,则其知道应该搜索左子树,假如传入为efg%,则应该搜索右子树,如果通配符在前面%abc,则数据库不知道应该走哪一面,就都扫描一遍了。

8.少用or,在 WHERE 子句中,如果在 OR 前的条件列是索引列,而在 OR 后的条件列不是索引列,那么索引会失效。

select * from t_user where id = 1 or age = 18;
        -- id有索引,name没有,此时没法走索引

因为 OR 的含义就是两个只要满足一个即可,因此只有一个条件列是索引列是没有意义的,只要有条件列不是索引列,就会进行全表扫描。

必须要or前后的字段都有索引,查询才能使用上索引(分别使用,最后合并结果type = index_merge

微信图片_20220907100309.png

9.在组合/联合索引中,将有区分度的索引放在前面

如果没有区分度,例如用性别,相当于把整个大表分成两部分,查找数据还是需要遍历半个表才能找到,使得索引失去了意义。

10.使用前缀索引

短索引不仅可以提高查询性能而且可以节省磁盘空间和I/O操作,减少索引文件的维护开销,但缺点是不能用于 ORDER BY 和 GROUP BY 操作,也不能用于覆盖索引。

比如有一个varchar(255)的列,如果该列在前10个或20个字符内,可以做到既使前缀索引的区分度接近全列索引,那么就不要对整个列进行索引。为了减少key_len,可以考虑创建前缀索引,即指定一个前缀长度,可以使用count(distinct leftIndex(列名, 索引长度))/count(*) 来计算前缀索引的区分度。

11.SQL 性能优化 explain 中的 type:至少要达到 range 级别,要求是 ref 级别,如果可以是 consts 最好。

  • consts:单表中最多只有一个匹配行(主键或者唯一索引),在优化阶段即可读取到数据。
  • ref:使用普通的索引
  • range:对索引进行范围检索。

当 type=index 时,索引物理文件全扫,速度非常慢。

基于 Spring Boot + MyBatis Plus + Vue & Element 实现的后台管理系统 + 用户小程序,支持 RBAC 动态权限、多租户、数据权限、工作流、三方登录、支付、短信、商城等功能。

项目地址:https://github.com/YunaiV/ruoyi-vue-pro

二、数据库表结构的优化:使得数据库结构符合三大范式与BCNF

https://blog.csdn.net/qq_35642036/article/details/82809974

基于微服务的思想,构建在 B2C 电商场景下的项目实战。核心技术栈,是 Spring Boot + Dubbo 。未来,会重构成 Spring Cloud Alibaba 。

项目地址:https://github.com/YunaiV/onemall

三、系统配置的优化

四、硬件的优化

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
相关文章
|
8月前
|
SQL 关系型数据库 MySQL
MySQL进阶突击系列(07) 她气鼓鼓递来一条SQL | 怎么看执行计划、SQL怎么优化?
在日常研发工作当中,系统性能优化,从大的方面来看主要涉及基础平台优化、业务系统性能优化、数据库优化。面对数据库优化,除了DBA在集群性能、服务器调优需要投入精力,我们研发需要负责业务SQL执行优化。当业务数据量达到一定规模后,SQL执行效率可能就会出现瓶颈,影响系统业务响应。掌握如何判断SQL执行慢、以及如何分析SQL执行计划、优化SQL的技能,在工作中解决SQL性能问题显得非常关键。
|
5月前
|
SQL 存储 自然语言处理
SQL的解析和优化的原理:一条sql 执行过程是什么?
SQL的解析和优化的原理:一条sql 执行过程是什么?
SQL的解析和优化的原理:一条sql 执行过程是什么?
|
7月前
|
SQL 关系型数据库 MySQL
如何优化SQL查询以提高数据库性能?
这篇文章以生动的比喻介绍了优化SQL查询的重要性及方法。它首先将未优化的SQL查询比作在自助餐厅贪多嚼不烂的行为,强调了只获取必要数据的必要性。接着,文章详细讲解了四种优化策略:**精简选择**(避免使用`SELECT *`)、**专业筛选**(利用`WHERE`缩小范围)、**高效联接**(索引和限制数据量)以及**使用索引**(加速搜索)。此外,还探讨了如何避免N+1查询问题、使用分页限制结果、理解执行计划以及定期维护数据库健康。通过这些技巧,可以显著提升数据库性能,让查询更高效流畅。
|
8月前
|
SQL 关系型数据库 MySQL
基于SQL Server / MySQL进行百万条数据过滤优化方案
对百万级别数据进行高效过滤查询,需要综合使用索引、查询优化、表分区、统计信息和视图等技术手段。通过合理的数据库设计和查询优化,可以显著提升查询性能,确保系统的高效稳定运行。
281 9
|
8月前
|
SQL 存储 关系型数据库
【SQL技术】不同数据库引擎 SQL 优化方案剖析
不同数据库系统(MySQL、PostgreSQL、Doris、Hive)的SQL优化策略。存储引擎特点、SQL执行流程及常见操作(如条件查询、排序、聚合函数)的优化方法。针对各数据库,索引使用、分区裁剪、谓词下推等技术,并提供了具体的SQL示例。通用的SQL调优技巧,如避免使用`COUNT(DISTINCT)`、减少小文件问题、慎重使用`SELECT *`等。通过合理选择和应用这些优化策略,可以显著提升数据库查询性能和系统稳定性。
274 9
|
9月前
|
SQL Oracle 关系型数据库
如何在 Oracle 中配置和使用 SQL Profiles 来优化查询性能?
在 Oracle 数据库中,SQL Profiles 是优化查询性能的工具,通过提供额外统计信息帮助生成更有效的执行计划。配置和使用步骤包括:1. 启用自动 SQL 调优;2. 手动创建 SQL Profile,涉及收集、执行调优任务、查看报告及应用建议;3. 验证效果;4. 使用 `DBA_SQL_PROFILES` 视图管理 Profile。
|
10月前
|
SQL Oracle 数据库
使用访问指导(SQL Access Advisor)优化数据库业务负载
本文介绍了Oracle的SQL访问指导(SQL Access Advisor)的应用场景及其使用方法。访问指导通过分析给定的工作负载,提供索引、物化视图和分区等方面的优化建议,帮助DBA提升数据库性能。具体步骤包括创建访问指导任务、创建工作负载、连接工作负载至访问指导、设置任务参数、运行访问指导、查看和应用优化建议。访问指导不仅针对单条SQL语句,还能综合考虑多条SQL语句的优化效果,为DBA提供全面的决策支持。
235 11
|
9月前
|
SQL 分布式计算 Java
Spark SQL向量化执行引擎框架Gluten-Velox在AArch64使能和优化
本文摘自 Arm China的工程师顾煜祺关于“在 Arm 平台上使用 Native 算子库加速 Spark”的分享,主要内容包括以下四个部分: 1.技术背景 2.算子库构成 3.算子操作优化 4.未来工作
965 0
|
11月前
|
SQL 存储 BI
gbase 8a 数据库 SQL合并类优化——不同数据统计周期合并为一条SQL语句
gbase 8a 数据库 SQL合并类优化——不同数据统计周期合并为一条SQL语句
|
11月前
|
SQL 数据库
gbase 8a 数据库 SQL优化案例-关联顺序优化
gbase 8a 数据库 SQL优化案例-关联顺序优化

热门文章

最新文章