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-主从复制

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
目录
相关文章
|
2月前
|
存储 SQL 关系型数据库
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
|
3月前
|
关系型数据库 MySQL 数据库连接
Django数据库配置避坑指南:从初始化到生产环境的实战优化
本文介绍了Django数据库配置与初始化实战,涵盖MySQL等主流数据库的配置方法及常见问题处理。内容包括数据库连接设置、驱动安装、配置检查、数据表生成、初始数据导入导出,并提供真实项目部署场景的操作步骤与示例代码,适用于开发、测试及生产环境搭建。
119 1
|
3月前
|
人工智能 运维 关系型数据库
数据库运维:mysql 数据库迁移方法-mysqldump
本文介绍了MySQL数据库迁移的方法与技巧,重点探讨了数据量大小对迁移方式的影响。对于10GB以下的小型数据库,推荐使用mysqldump进行逻辑导出和source导入;10GB以上可考虑mydumper与myloader工具;100GB以上则建议物理迁移。文中还提供了统计数据库及表空间大小的SQL语句,并讲解了如何使用mysqldump导出存储过程、函数和数据结构。通过结合实际应用场景选择合适的工具与方法,可实现高效的数据迁移。
561 1
|
3月前
|
SQL 关系型数据库 MySQL
Go语言数据库编程:使用 `database/sql` 与 MySQL/PostgreSQL
Go语言通过`database/sql`标准库提供统一数据库操作接口,支持MySQL、PostgreSQL等多种数据库。本文介绍了驱动安装、连接数据库、基本增删改查操作、预处理语句、事务处理及错误管理等内容,涵盖实际开发中常用的技巧与注意事项,适合快速掌握Go语言数据库编程基础。
203 62
|
2月前
|
SQL 存储 关系型数据库
MySQL功能模块探秘:数据库世界的奇妙之旅
]带你轻松愉快地探索MySQL 8.4.5的核心功能模块,从SQL引擎到存储引擎,从复制机制到插件系统,让你在欢声笑语中掌握数据库的精髓!
|
2月前
|
SQL Oracle 关系型数据库
比较MySQL和Oracle数据库系统,特别是在进行分页查询的方法上的不同
两者的性能差异将取决于数据量大小、索引优化、查询设计以及具体版本的数据库服务器。考虑硬件资源、数据库设计和具体需求对于实现优化的分页查询至关重要。开发者和数据库管理员需要根据自身使用的具体数据库系统版本和环境,选择最合适的分页机制,并进行必要的性能调优来满足应用需求。
81 11
|
2月前
|
SQL 关系型数据库 MySQL
【赵渝强老师】MySQL中的数据库对象
本教程详细介绍了MySQL数据库中的常见对象,包括表、索引、视图、事件、存储过程和存储函数的创建与管理。内容涵盖表的基本操作、索引的使用、视图简化查询、事件调度功能等,并通过具体示例演示相关SQL语句的使用方法。
|
4月前
|
SQL 数据采集 关系型数据库
实现MySQL与SQL Server之间数据迁移的有效方法
总的来说,从MySQL到SQL Server的数据迁移是一个涉及到很多步骤的过程,可能会遇到各种问题和挑战。但只要精心规划、仔细执行,这个任务是完全可以完成的。
257 18
|
4月前
|
存储 关系型数据库 MySQL
【赵渝强老师】OceanBase数据库从零开始:MySQL模式
《OceanBase数据库从零开始:MySQL模式》是一门包含11章的课程,涵盖OceanBase分布式数据库的核心内容。从体系架构、安装部署到租户管理、用户安全,再到数据库对象操作、事务与锁机制,以及应用程序开发、备份恢复、数据迁移等方面进行详细讲解。此外,还涉及连接路由管理和监控诊断等高级主题,帮助学员全面掌握OceanBase数据库的使用与管理。
251 5
|
4月前
|
负载均衡 算法 关系型数据库
大数据大厂之MySQL数据库课程设计:揭秘MySQL集群架构负载均衡核心算法:从理论到Java代码实战,让你的数据库性能飙升!
本文聚焦 MySQL 集群架构中的负载均衡算法,阐述其重要性。详细介绍轮询、加权轮询、最少连接、加权最少连接、随机、源地址哈希等常用算法,分析各自优缺点及适用场景。并提供 Java 语言代码实现示例,助力直观理解。文章结构清晰,语言通俗易懂,对理解和应用负载均衡算法具有实用价值和参考价值。
大数据大厂之MySQL数据库课程设计:揭秘MySQL集群架构负载均衡核心算法:从理论到Java代码实战,让你的数据库性能飙升!

热门文章

最新文章

推荐镜像

更多