MySQL数据库优化大全方法汇总

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:


随着数据和负载增加,MySQL数据库会日渐缓慢,性能越来越差,用户体验也随之变差,所以数据库性能优化十分紧迫,本文分享MySQL数据库优化大全:


MySQL数据库优化


本文先模拟一下数据库访问流程,然后逐步的优化这些环节,可以从减少数据访问(减少磁盘访问)、返回更少数据(减少网络传输或磁盘访问)、减少交互次数(减少网络传输)、减少服务器CPU开销(减少CPU及内存开销)和利用更多资源(增加资源)几个方面来提升数据库性能。


可以从以下及方面优化MySQL数据库:


一:数据库结构优化



  • 1)范式优化:表的设计合理化(符合3NF),比如消除冗余(节省空间);
  • 2)反范式优化:比如适当加冗余等(减少join)
  • 3)拆分表:分区将数据在物理上分隔开,不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样,当对这个表进行查询时,只需要在表分区中进行扫描,而不必进行全表扫描,明显缩短了查询时间,另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O,一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。对数据量大的时时表可采取此方法,可按月自动建表分区。

二:优化SQL语句



  • 1)应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描;
  • 2)应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:

select id from t where num is null

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


select id from t where num=0


  • 3)很多时候用exists代替in是一个好的选择;
  • 4)用Where子句替换HAVING子句,因为HAVING只会在检索出所有记录之后才对结果集进行过滤;
  • 5)迅速的定位执行速度慢的语句、开启慢查询、设置慢查询时间、启用慢查询日志、通过mysqldumoslow工具对慢日志进行分类汇总;
  • 6)析SQL语句,通过explain分析查询、通profiling可以得到更详细的信息;
  • 7)创建索引(主键索引/唯一索引/全文索引/普通索引);
  • 8)避免Select * (不查询多余的列与行);
  • 9)使用视图(经常被查询的列数据,并且这些数据不被经常的修改,删除);

三:分表技术(水平分割、垂直分割)、分区技术


如果遇到大表的情况下,SQL语句优化已经无法继续优化了,我们可以考虑分表和分区,目的就是减少数据库的负担,提高数据库的效率,通常点来讲就是提高表的增删改查效率。


分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表,我们可以称为子表,每个表都对应三个文件,MYD数据文件,.MYI索引文件,.frm表结构文件。这些子表可以分布在同一块磁盘上,也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的子表名,然后去操作它。


分区和分表相似,都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表,而分区是将数据分段划分在多个位置存放,可以是同一块磁盘也可以在不同的机器。分区后,表面上还是一张表,但数据散列到多个位置了。app读写的时候操作的还是大表名字,db自动去组织分区的数据。



  • 1)都能提高mysql的性能,在高并发状态下都有一个良好的表现;
  • 2)分表和分区不矛盾,可以相互配合的,对于那些大访问量,并且表数据比较多的表,我们可以采取分表和分区结合的方式,访问量不大,但是表数据很多的表,我们可以采取分区的方式等;
  • 3)分表技术是比较麻烦的,需要手动去创建子表,app服务端读写时候需要计算子表名。采用merge好一些,但也要创建子表和配置子表间的union关系;
  • 4)表分区相对于分表,操作方便,不需要创建子表。

四:读写分离


MySQL读写分离可以参考:阿里云MySQL读写分离详解,本文来说说抛开阿里云来实现读写分离的方法:



  • 方法一:php程序上自己做逻辑判断,写php代码的时候,自己在程序上做逻辑判读写匹配。select,insert、update、delete做正则匹配,根据结果选择写服务器(主服务器)。如果是select操作则选择读服务器(从服务器器) mysql_connect('读写的区分')
  • 方法二:MySQL中间件,基本的原理是让主数据库处理写操作(insert、update、delete),而从数据库处理查询操作(select)。而数据库的一致性则通过主从复制来实现。所以说主从复制是读写分离的基础。

五:存储过程 [模块化编程,可以提高速度]


存储过程是SQL语句和控制语句的预编译集合,保存在数据库中,可有应用程序调用执行,而且允许用户声明变量、逻辑控制语句及其他强大的编程功能。包含逻辑控制语句和数据操作语句,可以接收参数、输出参数、返回单个或多个结果值及返回值。


使用存储过程的优点:模块化程序设计,只需创建一次,以后即可调用该存储过程任意次;执行速度快,效率高;减少网络流量;具有良好的安全性。


六:对mysql配置优化 [配置最大并发数, 调整缓存大小]


MySQL数据库优化大全方法汇总
sqlpeizhi


七:MySQL服务器硬件升级


MySQL服务器硬件升级本文就不多赘述了,也可以使用云数据库,参考:阿里云MySQL云服务器详解


八:定时的去清除不需要的数据,定时进行碎片整理


  • 1)查看表碎片的方法

select ROW_FORMAT,TABLE_ROWS,DATA_LENGTH,INDEX_LENGTH,MAX_DATA_LENGTH,DATA_FREE,ENGINE from TABLES where TABLE_SCHEMA='test_db' and TABLE_NAME='table_name' limit 1;

  • 2)Innodb存储引擎清理碎片方法:

ALTER TABLE tablename ENGINE=InnoDB

  • 3)Myisam存储引擎清理碎片方法:

OPTIMIZE TABLE table_name

注意:MySQL碎片整理尽量选择业务不繁忙时清理,一个月清理一次即可。


相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
9天前
|
SQL Java 关系型数据库
MYSQL--JDBC优化
MYSQL--JDBC优化
|
3天前
|
SQL 缓存 监控
✅系统日活递增,如何优化提升大规模数据库
数据库性能优化涵盖硬件升级(如SSD、内存)、数据库设计简化、SQL查询优化、索引管理、缓存利用(如Redis)、负载均衡(读写分离、集群)、分区分片、备份恢复策略及性能监控。综合调整这些方面可提升系统性能和可用性。[MySQL索引设计][1]和[SQL优化实践][2]是深入学习的好资源。
|
5天前
|
SQL 缓存 关系型数据库
MySQL慢查询优化实践问答
MySQL慢查询优化实践问答
|
8天前
|
缓存 关系型数据库 MySQL
如何优化MySQL 8.0的性能?
【6月更文挑战第14天】如何优化MySQL 8.0的性能?
30 5
|
6天前
|
关系型数据库 MySQL 数据库
mysql索引优化
【6月更文挑战第16天】mysql索引优化
9 2
|
9天前
|
SQL 关系型数据库 MySQL
MYSQL————DDL方法使用(包含在数据库,以及具体数据库表格的一些操纵)
MYSQL————DDL方法使用(包含在数据库,以及具体数据库表格的一些操纵)
|
8天前
|
存储 Cloud Native 物联网
数据库技术前沿探索:架构、优化与行业实践
一、引言 在信息化和数字化的浪潮中,数据库技术作为企业核心竞争力的关键要素,其重要性不言而喻
|
10天前
|
缓存 监控 关系型数据库
mysql优化
【6月更文挑战第12天】mysql优化
18 3
|
14天前
|
数据可视化 Linux 数据库
探索 Linux 命令 `db_tuner`:优化数据库性能的利器
`db_tuner` 是一个假设的 Linux 工具,用于数据库性能优化,提供状态检查、查询分析、配置调优、索引建议及报告生成等功能。虽然不是真实命令,但类似工具如 `mysqltuner` 可实现这些功能。通过安装和运行 `db_tuner`,DBA 可以提升数据库性能,解决瓶颈问题。
|
11天前
|
SQL 数据管理 关系型数据库
数据管理DMS产品使用合集之误删了数据库后,恢复方法有哪些
阿里云数据管理DMS提供了全面的数据管理、数据库运维、数据安全、数据迁移与同步等功能,助力企业高效、安全地进行数据库管理和运维工作。以下是DMS产品使用合集的详细介绍。

热门文章

最新文章