关于sql语句的优化

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: <p>最近在做mysql的数据库优化以及对sql语句优化的指导,写了一点文档,这个大家共勉一下!</p><p></p><p>数据库参数进行优化所获得的性能提升全部加起来只占数据库应用系统性能提升的<span style="font-family:Verdana">40%</span><span style="font-family:宋体">左右,其余</span><span style="

最近在做mysql的数据库优化以及对sql语句优化的指导,写了一点文档,这个大家共勉一下!

数据库参数进行优化所获得的性能提升全部加起来只占数据库应用系统性能提升的40%左右,其余60%的系统性能提升全部来自对应用程序的优化。许多优化专家甚至认为对应用程序的优化可以得到80%的系统性能提升。因此可以肯定,通过优化应用程序来对数据库系统进行优化能获得更大的收益。

通常可分为两个方面: SQL语句的优化和数据库性能调优。应用程序对数据库的操作最终要表现为SQL语句对数据库的操作。而数据库性能调优是结合硬件,软件,数据量等的一个综合解决方案,这个需要测试人员进行性能测试,和开发人员配合进行性能调优。

SQL语句优化

3.1关键词优化

所有关键词都大写。如:SELECT,FORM,WHERE,AND,CREATE,TABLE等等,例如:使用mysql管理工具导出sql文件,我们可以看到大部分关键词都是大写。如下图:

解释:这是因为,ORACLE的sql的处理底层,默认就将所有的sql语句,进行大写转换。Mysql和oracle是同一家公司,不排除哪一天mysql和oracle都做的一样了。

3.2 sql语句中不能存在*

在所有的查询sql语句中,不能存在*符号。即,SELECT *FORM 。举例我们的部门表的查询。错误写法:SELECT * FROM tdepartment 正确写法:SELECT idepartmentid,scompanycode,sdepartmentname,iparentdepartmentid,sdeptposttype,sifdeleted,sleafnode,sdesc FROM tdepartment。原因:*号会检索全部字段,

*号效率低,就相当于for循环和foreach一样。用*号,sql语句查询底层会默认去字      

典库里查询公有多少个字段,然后在一个一个的取。如果不使用*,就不是去先查字典库。

3.3 COUNT(*)使用

项目中不能使用COUNT(*)sql语句。COUNT(*)全部替换成COUNT(1)。这在数据量比较小的情况下,不明显,但是在表中数据较多的情况下,效果非常明显。

3.4多用匹配查询,少用like查询

      原因,like查询会直接放弃索引。

3.5主键索引使用

      所有表的主键全是索引。应尽量使用主键查询。如:SELECT * FROM tusers ORDER BY dregistertime DESC效率低于SELECT * FROM tusers ORDER BY iuserid DESC。这是因为所有的主键都默认是索引。而注册时间不是索引字段。 

3.6第12索引排列使用

假设我们的用户表中的scompanycode,dregistertime两个字段都创建了索引。而scompanycode是第一索引。dregistertime是第二索引。那么查询时:SELECT * FROM tusers ORDER BY scompanycode,dregistertime DESC的效率高于SELECT * FROM tusers ORDER BY dregistertime,scompanycode DESC。这是因为第一索引将首先被检索。

3.7建表不要给字段设置默认值

如:`sifaudited` varchar(2) default '0' COMMENT '0:未审核;1:已审核'。默认值会在插入数据时,增加数据库底层判断是否有值情况,进行赋默认值。

3.8字段不要留null

这是因为null值占用的数据大小比较大。Null和空一般占48个字节。如:`scompanycode` varchar(16) default NULL COMMENT '公司编号(唯一识别)',对于这样的,我们一般把空值改为0,你们应该懂的。

3.9多用子查询

      子查询性能高于连接查询。子查询性能高于左联接、右连接、全连接查询。

3.10连接查询性能高于循环查询

对于部门查询,我们一般是查询根目录,然后循环查询子部门,一直循环到查询结束。性能较低。我们应该采用,连接查询。或者写函数,存储过程进行查询。

4.设计优化

4.1 日志模块,新增队列,当日志达到100条或者200500条的时候,我们采用批量插入n条,减少磁盘的io次数。这样可以延长磁盘的寿命,同时对数据的插入也有了明显的提高。

5.数据库引擎使用

5.1   ENGINE = innodb

    Innodb数据库引擎是对外键,事务进行过优化。我们对创建所有的表都使用innodb引擎。这是错误的,应该对每一个表的用途对应一个不同的数据库引擎。

5.2   ENGINE = MyISAM

MyISAM类型不支持事务处理等高级处理。MyISAM类型的表强调的是性能,其执行数度比InnoDB类型更快,但是不提供事务支持。MyISAM类型的二进制数据文件可以在不同操作系统中迁移。也就是可以直接从Windows系统拷贝到linux系统中使用。这个是默认类型,它是基于传统的ISAM类型,ISAMIndexed Sequential Access Method (有索引的 顺序访问方法的缩写,它是存储记录和文件的标准方法.与其他存储引擎比较,MyISAM具有检查和修复表格的大多数工具. MyISAM表格可以被压缩,而且它们支持全文搜索.它们不是事务安全的,而且也不支持外键。如果事物回滚将造成不完全回滚,不具有原子性。如果执行大量 的SELECTMyISAM是更好的选择。这个类型东海们项目使用的多。最常用的引擎之一。

5.3   ENGINE = BDB

BDB:可替代InnoDB的事务引擎,支持COMMITROLLBACK和其他事务特性。

5.4   ENGINE = Memory

Memory:将所有数据保存在RAM中,在需要快速查找引用和其他类似数据的环境下,可提供极快的访问。

5.5   ENGINE = Merge

Merge:允许MySQL DBA或开发人员将一系列等同的MyISAM表以逻辑方式组合在一起,并作为1个对象引用它们。对于诸如数据仓储等VLDB环境十分适合。

5.6    ENGINE = Archive

Archive:为大量很少引用的历史、归档、或安全审计信息的存储和检索提供了完美的解决方案。

5.7    ENGINE = Federated

 Federated:能够将多个分离的MySQL服务器链接起来,从多个物理服务器创建一个逻辑数据库。十分适合于分布式 环境或数据集市环境。

5.8    ENGINE =Cluster/NDB

Cluster/NDBMySQL的簇式数据库引擎,尤其适合于具有高性能查找要求的应用程序,这类查找需求还要求具有最高的正常工作时间和可用性

5.9    Other:其他存储引擎包括CSV(引用由逗号隔开的用作数据库表的文件),Blackhole(用于临时禁止对数据库的应用程序输入),以及Example引擎(可为快速创建定制的插件式存储引擎提供帮助)。

6.表字段设计

  6.1对于类型限制。是否删除字段,如:`sifdeleted` varchar(2) default '0' COMMENT '0:正常;1:已删除',使用int(1)类型标识,不要使用varchar(2)多占用空间。

     6.2 对于字段长度限制,如手机号11位,我们就没有必要设计更多位数。公司编号可以只设定8位。用户名限制32位等等。

     6.3 少用外键限制

         我们可以使用代码限制。如:级联删除,级联新增,修改等等操作。最好不要设计外键,外键对新增数据不利。

     6.4  少用约束,如:唯一约束。

 6.5  少用自动增长

      在圆通主键没有自动增长,而是使用uuidjava自动生成。考虑到我们数据表数据较少,少用。

 6.6  对于内容较少的表,没有必要创建索引。因为索引浪费空间。

 6.7  表分区使用

      对于日志表,我们可以使用表分区。表分区之后,对于查询效率有很高的提升。默认有时间分区,大小分区,类型分区等等。

 6.8  对表的内容进行限制,如:日志表可以限制条数。再创建表时。我们使用MAX_ROWS进行限制。

7.其他请遵守建表规则

   如:三范式等。

好吧就到这里,欢迎大家关注我的个人博客!

如有疑问,请加qq群:135430763 共同学习!

点击文档下载:
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
3天前
|
SQL 缓存 Java
sql优化方法
sql优化方法
22 0
|
3天前
|
SQL 存储 关系型数据库
一文搞懂SQL优化——如何高效添加数据
**SQL优化关键点:** 1. **批量插入**提高效率,一次性建议不超过500条。 2. **手动事务**减少开销,多条插入语句用一个事务。 3. **主键顺序插入**避免页分裂,提升性能。 4. **使用`LOAD DATA INFILE`**大批量导入快速。 5. **避免主键乱序**,减少不必要的磁盘操作。 6. **选择合适主键类型**,避免UUID或长主键导致的性能问题。 7. **避免主键修改**,保持索引稳定。 这些技巧能优化数据库操作,提升系统性能。
294 4
一文搞懂SQL优化——如何高效添加数据
|
3天前
|
SQL 存储 关系型数据库
SQL优化之Explain详解(mysql)
`Explain`是MySQL中用于分析SQL查询执行计划的工具。它可以帮助我们了解MySQL如何执行SQL语句,包括如何使用索引、预计的行数以及查询的顺序。以下是`Explain`输出的关键列及其含义的简要摘要: 1. **id**:查询的序列号,表示查询中的子句层次,id越大优先级越高。 2. **select_type**:表示查询的类型,如SIMPLE(简单查询)、PRIMARY(主查询,多表查询中的第一个查询)、SUBQUERY(子查询)、DERIVED(派生表)或UNION(UNION操作的查询部分)。 3. **table**:查询涉及的表名,如果是子查询,可能显示为衍生表
36 0
|
3天前
|
SQL 关系型数据库 MySQL
项目中遇到一张900w的数据表把原先要花费17s执行的SQL优化到300ms经验加100哈哈哈
项目中遇到一张900w的数据表把原先要花费17s执行的SQL优化到300ms经验加100哈哈哈
25 1
|
2天前
|
SQL 存储 关系型数据库
【MySQL】SQL 优化
【MySQL】SQL 优化
20 0
|
3天前
|
SQL 缓存 关系型数据库
一次sql改写优化子查询的案例
在生产环境中,一个MySQL RDS实例遭遇了高CPU使用率问题,原因是执行了一条复杂的UPDATE SQL语句,该语句涉及一个无法缓存的子查询(UNCACHEABLE SUBQUERY),导致子查询需要针对每一行数据重复执行,极大地影响了性能。SQL语句的目标是更新一行数据,但执行时间长达30秒。优化方法是将子查询转换为内连接形式,优化后的语句执行时间降低到毫秒级别,显著减少了CPU消耗。通过示例数据和执行计划对比,展示了优化前后的时间差异和执行效率的提升。
|
3天前
|
存储 SQL 关系型数据库
掌握高性能SQL的34个秘诀🚀多维度优化与全方位指南
掌握高性能SQL的34个秘诀🚀多维度优化与全方位指南
|
3天前
|
SQL 存储 关系型数据库
【MySQL系列笔记】SQL优化
SQL优化是通过调整数据库查询、索引、表结构和配置参数等方式,提高SQL查询性能和效率的过程。它旨在减少查询执行时间、减少系统资源消耗,从而提升数据库系统整体性能。优化方法包括索引优化、查询重写、表分区、适当选择和调整数据库引擎等。
233 3
|
3天前
|
存储 SQL 缓存
30个业务场景的SQL优化
这些优化策略和示例可以帮助改善 `SQL` 查询的性能和效率。在实践中,需要综合考虑数据库设计、`SQL` 编写、服务器配置等多方面因素,选择合适的优化方法,并进行充分的测试和验证。以上 30 个经验是 V 哥在实际经验中总结的内容,当然,业务场景不同,具体的优化策略也会不同,按实际情况处理,这不就是程序员要做的事情么。
|
3天前
|
SQL 存储 算法
clickhouse SQL优化
clickhouse 是 OLAP 数据库,但其具有独特的索引设计,所以如果拿 MySQL 或者其他 RDB 的优化经验来优化 clickhouse 可能得不到很好的效果,所以特此单独整理一篇文档,用于有 SQL 优化需求的同学,本人接触 clickhouse 时间也不长,难免有不足的地方,如果大家发现错误,还请不吝指正。