MYSQL优化方式-附实际操作校验

2024-01-24 17:50

本文主要是介绍MYSQL优化方式-附实际操作校验,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

本文优化内容主要针对MySQL,有些会进行实际操作进行检验
参考
SQL优化最干货总结(2020最新版)
设计索引的原则是什么?怎么避免索引失效?

总结三点

  • 最大化利用索引
  • 尽可能避免全表扫描
  • 减少无效数据的查询

本文所用例:mainten表
SELECT count(*) FROM or_mainten
在这里插入图片描述
已有索引
在这里插入图片描述

1、避免出现select *

使用select * 取出全部列,会让优化器无法完成索引覆盖扫描这类优化,会影响优化器对执行计划的选择,也会增加网络带宽消耗,更会带来额外的I/O,内存和CPU消耗。
建议:只查询业务需要的字段

2、对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引

注意:只有单单order by 是无法使用到索引的
在这里插入图片描述
在这里插入图片描述

3、尽量避免在字段开头模糊查询,会导致数据库引擎放弃索引进行全表扫描。如下:

SELECT * FROM t WHERE username LIKE '%陈%'
优化方式:尽量在字段后面使用模糊查询。如下:
SELECT * FROM t WHERE username LIKE '陈%'

实际操作测试:
在这里插入图片描述
在这里插入图片描述
如果需求是要在前面使用模糊查询,

  • 使用MySQL内置函数INSTR(str,substr) 来匹配,作用类似于java中的indexOf(),查询字符串出现的角标位置
  • 使用FullText全文索引,用match against 检索
  • 数据量较大的情况,建议引用ElasticSearch、solr,亿级数据量检索速度秒级
  • 当表数据量较少(几千条儿那种),别整花里胡哨的,直接用like ‘%xx%’。

4、应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描

在这里插入图片描述
在这里插入图片描述

5、应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:

下图REAL_MAINTEN_PERSON和status都在索引INDEX_REAL_MAINTEN_PERSON中,但是在or情况下索引失效
在这里插入图片描述

在这里插入图片描述
解决:为or条件中的每个列都建立索引,但是和索引不能建立太多相违背
在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

6、应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描,如:

select id from t where num/2=100

应改为:

select id from t where num=100*2

实际操作
选择int字段
在这里插入图片描述
添加索引
在这里插入图片描述

在这里插入图片描述在这里插入图片描述

7、不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

实际操作
添加索引
在这里插入图片描述
索引失效
在这里插入图片描述
索引正常应用
在这里插入图片描述

8、应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:

select id from t where substring(name,1,3)='abc'
-name以abc开头的id

应改为:

select id from t where name like 'abc%'

实际操作
在这里插入图片描述

9、最左前缀匹配原则

在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。
实际操作
在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

10、选择区分度大的列作为索引

并不是所有索引对查询都有效,如一表中有字段性别sex,男女几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。
原因是当索引列有大量数据重复时,MySQL还有一个查询优化器,查询优化器发现某个值出现在表的数据行中的百分比很高的时候,它一般会忽略索引,进行全表扫描。
惯用的百分比界线是"30%"。(匹配的数据量超过一定限制的时候查询器会放弃使用索引(这也是索引失效的场景之一哦)。

11、索引并不是越多越好

索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。
一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。

12、尽量使用数字型字段

若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。
这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了

13、尽可能的使用 varchar 代替 char

因为首先变长字段存储空间小,可以节省存储空间,
其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。

14、为频繁查询的字段建立索引

我们在建立索引的时候,要为那些经常作为查询条件的字段建立索引,这样能够提高整个表的查询速度。
但是查询条件一般不是一个字段,所以一般是建立的联合索引比较多。
另外查询条件中一般会有like这样的模糊查询,如果是模糊查询请最好遵守最左前缀查询原则。

15、避免为"大字段"建立索引

这个可以换句话说:就是尽量使用数据量小的字段作为索引。
举个例子来说,假设有两个这样的字段,一个是varchar(5),一个是varchar(200),这种情况下优先选择为varchar(5)的字段建立索引,因为MySQL在维护索引的时候是会将字段值一起维护的,那这样必然会导致索引占用更多的空间,另外在排序的时候需要花费更多的时间去对比。
那假如就要为varchar(100)建立索引呢?那就取部分数据,例如 address 类型为varchar(200),在建立索引的时候可以这么写:

CREATE INDEX tbl_address ON dual(address(20));

16、避免隐式转换

假设字段 age 类型为 int,那我们一般是这么查询的

SELECT * FROM student WHERE age=15
上面这种情况是能使用到索引的,但是如果你这么写
SELECT * FROM student WHERE age='15'
那这种情况是使用不到索引的,也就是age列情的索引是失效的。

MySQL中实际操作发现并不会失效的,未在其它数据库进行验证

1、应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:

select id from t where num is null

可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:

select id from t where num=0

在MySQL中实际操作发现并不会放弃使用索引
在这里插入图片描述

2、尽量避免使用in 和not in,会导致引擎走全表扫描。如下:

SELECT * FROM t WHERE id = 1 OR id = 3

实际操作
在这里插入图片描述

这篇关于MYSQL优化方式-附实际操作校验的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/640492

相关文章

SpringBoot3实现Gzip压缩优化的技术指南

《SpringBoot3实现Gzip压缩优化的技术指南》随着Web应用的用户量和数据量增加,网络带宽和页面加载速度逐渐成为瓶颈,为了减少数据传输量,提高用户体验,我们可以使用Gzip压缩HTTP响应,... 目录1、简述2、配置2.1 添加依赖2.2 配置 Gzip 压缩3、服务端应用4、前端应用4.1 N

Java枚举类实现Key-Value映射的多种实现方式

《Java枚举类实现Key-Value映射的多种实现方式》在Java开发中,枚举(Enum)是一种特殊的类,本文将详细介绍Java枚举类实现key-value映射的多种方式,有需要的小伙伴可以根据需要... 目录前言一、基础实现方式1.1 为枚举添加属性和构造方法二、http://www.cppcns.co

MySQL双主搭建+keepalived高可用的实现

《MySQL双主搭建+keepalived高可用的实现》本文主要介绍了MySQL双主搭建+keepalived高可用的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,... 目录一、测试环境准备二、主从搭建1.创建复制用户2.创建复制关系3.开启复制,确认复制是否成功4.同

Spring Boot + MyBatis Plus 高效开发实战从入门到进阶优化(推荐)

《SpringBoot+MyBatisPlus高效开发实战从入门到进阶优化(推荐)》本文将详细介绍SpringBoot+MyBatisPlus的完整开发流程,并深入剖析分页查询、批量操作、动... 目录Spring Boot + MyBATis Plus 高效开发实战:从入门到进阶优化1. MyBatis

MyBatis 动态 SQL 优化之标签的实战与技巧(常见用法)

《MyBatis动态SQL优化之标签的实战与技巧(常见用法)》本文通过详细的示例和实际应用场景,介绍了如何有效利用这些标签来优化MyBatis配置,提升开发效率,确保SQL的高效执行和安全性,感... 目录动态SQL详解一、动态SQL的核心概念1.1 什么是动态SQL?1.2 动态SQL的优点1.3 动态S

使用Sentinel自定义返回和实现区分来源方式

《使用Sentinel自定义返回和实现区分来源方式》:本文主要介绍使用Sentinel自定义返回和实现区分来源方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录Sentinel自定义返回和实现区分来源1. 自定义错误返回2. 实现区分来源总结Sentinel自定

Mysql表的简单操作(基本技能)

《Mysql表的简单操作(基本技能)》在数据库中,表的操作主要包括表的创建、查看、修改、删除等,了解如何操作这些表是数据库管理和开发的基本技能,本文给大家介绍Mysql表的简单操作,感兴趣的朋友一起看... 目录3.1 创建表 3.2 查看表结构3.3 修改表3.4 实践案例:修改表在数据库中,表的操作主要

Java进行文件格式校验的方案详解

《Java进行文件格式校验的方案详解》这篇文章主要为大家详细介绍了Java中进行文件格式校验的相关方案,文中的示例代码讲解详细,感兴趣的小伙伴可以跟随小编一起学习一下... 目录一、背景异常现象原因排查用户的无心之过二、解决方案Magandroidic Number判断主流检测库对比Tika的使用区分zip

Springboot处理跨域的实现方式(附Demo)

《Springboot处理跨域的实现方式(附Demo)》:本文主要介绍Springboot处理跨域的实现方式(附Demo),具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不... 目录Springboot处理跨域的方式1. 基本知识2. @CrossOrigin3. 全局跨域设置4.

springboot security使用jwt认证方式

《springbootsecurity使用jwt认证方式》:本文主要介绍springbootsecurity使用jwt认证方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地... 目录前言代码示例依赖定义mapper定义用户信息的实体beansecurity相关的类提供登录接口测试提供一