MySQL数据库4种常用优化方式,sql语句书写优化规范

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL数据库4种常用优化方式,sql语句书写优化规范

引言

实际项目中,我们的数据往往存储在数据库中,但是由于数据库本身是持久化存储,数据的查询速度受到磁盘IO瓶颈的限制,同时,随着数据量的增长,数据的查询也会因为单表数据量的巨大,影响查询速度。所以我们需要对数据库进行优化,提高系统的响应效率。

数据库优化手段

  1. 索引优化。
  2. 添加查询缓存。
  3. 优化数据库SQL语句
  4. 数据库Cluster(搭建数据库集群环境)。

优化储备知识

3.1 慢查询

慢查询就是查看我们每条sql语句查询所用的时间,

开启慢查询的目的是将我们每次查询所用的时间记录在日志里进行观察检测,如果检测到查询的时间到达超过一定的预定时间(比如查询时间超过1秒的语句),将被记录下来进行优化!

  1. 查看慢查询的信息状态

show variables like ‘%quer%’;

  1. 开启慢查询

set global slow_query_log=on;

  1. 修改慢查询最短时间为1s

set GLOBAL long_query_time = 1;

  1. 查看MySQL执行过的慢查询SQL语句。

3.2 执行计划

补充: 数据库查询优化中需要了解MySQL执行计划命令,查看sql的执行效率

进行一条查询语句,点下左上角的解释看语句解释:

索引优化

4.1 索引语法

  1. 索引的作用

加快以索引字段为条件的查询效率。

  1. 创建索引

create index 索引名字 on 表(字段)

  1. 删除索引

drop index 索引名字

  1. 查询某张表创建了哪些索引

Show index from 表名;

索引的特点

①索引会占用存储空间,虽然比较少,但是也占用了。


②MySQL会自动为primary 主键列和unique 唯一列自动增加索引。


③MySQL数据库对数据做DML操作时,需要同时做维护索引的操作。


隐含之意,就是索引会降低数据库增删改的效率。

  1. 使用原则:

一般会对sql中的where条件字段或者order by字段建立索引。

4.2 索引应用场景

实际项目中,如果从业务角度出发,发现经常会用到某个字段,对表数据进行查询,或者根据经常用到某个字段的排序,则该数据是需要增加索引的。

查询缓存

  1. 在数据库操作对象DAO处增加查询缓存。

参考Ehcache缓存,或者MyBatis+Ehcache整合方案、Hibernate二级缓存Ehcache。

  1. 需要注意的问题是,要注意数据的一致性,所以缓存思路如下:

① 只对查询结果进行缓存

缓存结果存放要以业务逻辑相关为划分单位。比如,如果是单表,则可以创建一个缓存区域,与该表对应,如果业务中涉及到表连接操作,要将查询结果缓存在这多张表的查询结果都要缓存在对应的一个缓存区域中。

  1. 当执行增删改的时候,为了避免出现脏数据。
  2. 基于以上的特点,缓存原则是对那些查询需求远大于增删改需求的数据,进行缓存。

例如:新闻网站的首页数据、电商的类别数据等。

SQL优化

尽量不要在要给在SQL语句的where子句中使用函数,这样会使索引失效。


如果已经确定查询结果只有一条数据(当表中数据的该字段是唯一的),在查询SQL末尾增加

limit 1,这样MySQL的查询执行引擎在找到1条数据之后就会停止搜索,提高效率。(mysql5.6之后有效)

例如:


select * from t_department where department_name = ‘研发部’ limit 1;


模糊查询尽量使用右模糊:‘xx%’,这样可以利用上索引,而使用‘%xx’这种模糊查询会导致该查询字段上的索引失效。


如果非要使用表连接,最好要保证连接的两个字段都是创建了索引的。


查询的时候最好用什么数据查询什么数据,避免使用select

*,原因是数据库访问往往是远程通过网络来访问,这也就意味着,查询结果也是通过网络来传输的,如果查询了额外的无用的数据,他们会额外占用网络带宽,数据传输效率也会降低。


尽量使用非空 not

null,可以使用‘’空串代替null。因为‘’不占用空间,null会占用空间的。


使用!= 或者<> 数据库会放弃索引。

  1. 在SQL语句中不要有运算,否则MySQL会放弃索引。(mysql在执行查询时,会自动过滤当前语句,当效率大于使用索引时,则默认不使用索引)

1.对于百万级以上的表数据,如果要做表连接查询,一定要对表先分页,然后对分页结果再做表连接。


2.尽量避免大事务操作,这样会降低系统的并发能力。在满足实际业务的前提下,尽量缩短事务的边界。


3.Where子句多条件and连接的情况,要把精确条件放在最先执行的位置,提高效率。Oracle中是从右至左。MySQL中是从左至右。

MySql集群环境之------读写分离、主从复制

搭建MySQL的集群环境来优化查询

详细的使用搭建步骤可参考我之前文章:

Centos 7使用MyCat搭建 MySQL-读写分离

Centos 7搭建MySQL-主从复制

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
3天前
|
SQL Oracle 数据库
使用访问指导(SQL Access Advisor)优化数据库业务负载
本文介绍了Oracle的SQL访问指导(SQL Access Advisor)的应用场景及其使用方法。访问指导通过分析给定的工作负载,提供索引、物化视图和分区等方面的优化建议,帮助DBA提升数据库性能。具体步骤包括创建访问指导任务、创建工作负载、连接工作负载至访问指导、设置任务参数、运行访问指导、查看和应用优化建议。访问指导不仅针对单条SQL语句,还能综合考虑多条SQL语句的优化效果,为DBA提供全面的决策支持。
25 11
|
13天前
|
关系型数据库 MySQL 数据库
Python处理数据库:MySQL与SQLite详解 | python小知识
本文详细介绍了如何使用Python操作MySQL和SQLite数据库,包括安装必要的库、连接数据库、执行增删改查等基本操作,适合初学者快速上手。
87 15
|
6天前
|
SQL 关系型数据库 MySQL
数据库数据恢复—Mysql数据库表记录丢失的数据恢复方案
Mysql数据库故障: Mysql数据库表记录丢失。 Mysql数据库故障表现: 1、Mysql数据库表中无任何数据或只有部分数据。 2、客户端无法查询到完整的信息。
|
13天前
|
关系型数据库 MySQL 数据库
数据库数据恢复—MYSQL数据库文件损坏的数据恢复案例
mysql数据库文件ibdata1、MYI、MYD损坏。 故障表现:1、数据库无法进行查询等操作;2、使用mysqlcheck和myisamchk无法修复数据库。
|
16天前
|
SQL 存储 缓存
MySQL进阶突击系列(02)一条更新SQL执行过程 | 讲透undoLog、redoLog、binLog日志三宝
本文详细介绍了MySQL中update SQL执行过程涉及的undoLog、redoLog和binLog三种日志的作用及其工作原理,包括它们如何确保数据的一致性和完整性,以及在事务提交过程中各自的角色。同时,文章还探讨了这些日志在故障恢复中的重要性,强调了合理配置相关参数对于提高系统稳定性的必要性。
|
15天前
|
SQL 关系型数据库 MySQL
MySQL 高级(进阶) SQL 语句
MySQL 提供了丰富的高级 SQL 语句功能,能够处理复杂的数据查询和管理需求。通过掌握窗口函数、子查询、联合查询、复杂连接操作和事务处理等高级技术,能够大幅提升数据库操作的效率和灵活性。在实际应用中,合理使用这些高级功能,可以更高效地管理和查询数据,满足多样化的业务需求。
49 3
|
17天前
|
SQL 关系型数据库 MySQL
MySQL导入.sql文件后数据库乱码问题
本文分析了导入.sql文件后数据库备注出现乱码的原因,包括字符集不匹配、备注内容编码问题及MySQL版本或配置问题,并提供了详细的解决步骤,如检查和统一字符集设置、修改客户端连接方式、检查MySQL配置等,确保导入过程顺利。
|
19天前
|
SQL 存储 关系型数据库
MySQL进阶突击系列(01)一条简单SQL搞懂MySQL架构原理 | 含实用命令参数集
本文从MySQL的架构原理出发,详细介绍其SQL查询的全过程,涵盖客户端发起SQL查询、服务端SQL接口、解析器、优化器、存储引擎及日志数据等内容。同时提供了MySQL常用的管理命令参数集,帮助读者深入了解MySQL的技术细节和优化方法。
|
20天前
|
SQL Oracle 关系型数据库
SQL(MySQL)
SQL语言是指结构化查询语言,是一门ANSI的标准计算机语言,用来访问和操作数据库。 数据库包括SQL server,MySQL和Oracle。(语法大致相同) 创建数据库指令:CRATE DATABASE websecurity; 查看数据库:show datebase; 切换数据库:USE websecurity; 删除数据库:DROP DATABASE websecurity;
|
23天前
|
SQL 存储 BI
gbase 8a 数据库 SQL合并类优化——不同数据统计周期合并为一条SQL语句
gbase 8a 数据库 SQL合并类优化——不同数据统计周期合并为一条SQL语句
下一篇
DataWorks