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

2024-01-24 17:50
文章标签 mysql 校验 优化 方式 database 实际操作

本文主要是介绍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。

相关文章

如何突破底层思维方式的牢笼

如何突破底层思维方式的牢笼

我始终认为，牛人和普通人的根本区别在于思维方式的不同，而非知识多少、阅历多少。 在这个世界上总有一帮神一样的人物存在。就像读到的那句话：“人类就像是一条历史长河中的鱼，只有某几条鱼跳出河面，看到世界的法则，但是却无法改变，当那几条鱼中有跳上岸，进化了，改变河道流向，那样才能改变法则。”  最近一段时间一直在不断寻在内心的东西，同时也在不断的去反省和否定自己的一些思维模式，尝试重
阅读更多...
uniapp接入微信小程序原生代码配置方案（优化版）

uniapp接入微信小程序原生代码配置方案（优化版）

uniapp项目需要把微信小程序原生语法的功能代码嵌套过来，无需把原生代码转换为uniapp，可以配置拷贝的方式集成过来 1、拷贝代码包到src目录 2、vue.config.js中配置原生代码包直接拷贝到编译目录中 3、pages.json中配置分包目录，原生入口组件的路径 4、manifest.json中配置分包，使用原生组件 5、需要把原生代码包里的页面修改成组件的方
阅读更多...
idea lanyu方式激活

idea lanyu方式激活

访问http://idea.lanyus.com/这个地址。根据提示将0.0.0.0 account.jetbrains.com添加到hosts文件中，hosts文件在C:\Windows\System32\drivers\etc目录下。点击获得注册码即可。
阅读更多...
mysql索引四（组合索引）

mysql索引四（组合索引）

单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引；组合索引，即一个索引包含多个列。 因为有事，下面内容全部转自：https://www.cnblogs.com/farmer-cabbage/p/5793589.html 为了形象地对比单列索引和组合索引，为表添加多个字段： 　　 CREATE TABLE mytable( ID INT NOT NULL, use
阅读更多...
mysql索引三（全文索引）

mysql索引三（全文索引）

前面分别介绍了mysql索引一（普通索引）、mysql索引二（唯一索引）。 本文学习mysql全文索引。 全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用【分词技术】等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。 在MySql中，创建全文索引相对比较简单。例如：我们有一个文章表（article），其中有主键ID（
阅读更多...
mysql索引二（唯一索引）

mysql索引二（唯一索引）

前文中介绍了MySQL中普通索引用法，和没有索引的区别。mysql索引一（普通索引） 下面学习一下唯一索引。 创建唯一索引的目的不是为了提高访问速度，而只是为了避免数据出现重复。唯一索引可以有多个但索引列的值必须唯一，索引列的值允许有空值。如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该使用关键字UNIQUE，把它定义为一个唯一索引。 添加数据库唯一索引的几种
阅读更多...
mysql索引一（普通索引）

mysql索引一（普通索引）

mysql的索引分为两大类，聚簇索引、非聚簇索引。聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引则不同。聚簇索引能够提高多行检索的速度、非聚簇索引则对单行检索的速度很快。         在这两大类的索引类型下，还可以降索引分为4个小类型：         1，普通索引：最基本的索引，没有任何限制，是我们经常使用到的索引。         2，唯一索引：与普通索引
阅读更多...
【服务器运维】MySQL数据存储至数据盘

【服务器运维】MySQL数据存储至数据盘

查看磁盘及分区 [root@MySQL tmp]# fdisk -lDisk /dev/sda: 21.5 GB, 21474836480 bytes255 heads, 63 sectors/track, 2610 cylindersUnits = cylinders of 16065 * 512 = 8225280 bytesSector size (logical/physical)
阅读更多...
以canvas方式绘制粒子背景效果，感觉还可以

以canvas方式绘制粒子背景效果，感觉还可以

这个是看到项目中别人写好的，感觉这种写法效果还可以，就存留记录下 就是这种的背景效果。如果想改背景颜色可以通过canvas.js文件中的fillStyle值改。 附上demo下载地址。 https://download.csdn.net/download/u012138137/11249872
阅读更多...
vue同页面多路由懒加载-及可能存在问题的解决方式

vue同页面多路由懒加载-及可能存在问题的解决方式

先上图，再解释 图一是多路由页面，图二是路由文件。从图一可以看出每个router-view对应的name都不一样。从图二可以看出层路由对应的组件加载方式要跟图一中的name相对应，并且图二的路由层在跟图一对应的页面中要加上components层，多一个s结尾，里面的的方法名就是图一路由的name值，里面还可以照样用懒加载的方式。 页面上其他的路由在路由文件中也跟图二是一样的写法。 附送可能存在
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2