MySQL 数据库优化,看这篇就够了

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: 数据库优化一方面是找出系统的瓶颈,提高MySQL数据库的整体性能,而另一方面需要合理的结构设计和参数调整,以提高用户的相应速度,同时还要尽可能的节约系统资源,以便让系统提供更大的负荷.

1、优化一览图

30.jpg

2、优化


笔者将优化分为了两大类,软优化和硬优化,软优化一般是操作数据库即可,而硬优化则是操作服务器硬件及参数设置.

2.1 软优化


2.1.1 查询语句优化


1、首先我们可以用EXPLAIN或DESCRIBE(简写:DESC)命令分析一条查询语句的执行信息.
2.例:

31.png

显示:

32.jpg

其中会显示索引和查询数据读取数据条数等信息.

2.1.2 优化子查询


在MySQL中,尽量使用JOIN来代替子查询.因为子查询需要嵌套查询,嵌套查询时会建立一张临时表,临时表的建立和删除都会有较大的系统开销,而连接查询不会创建临时表,因此效率比嵌套子查询高.

2.1.3 使用索引


索引是提高数据库查询速度最重要的方法之一,关于索引可以参高笔者一文,介绍比较详细,此处记录使用索引的三大注意事项:
1、LIKE关键字匹配'%'开头的字符串,不会使用索引.
2、OR关键字的两个字段必须都是用了索引,该查询才会使用索引.
3、使用多列索引必须满足最左匹配.

2.1.4 分解表


对于字段较多的表,如果某些字段使用频率较低,此时应当,将其分离出来从而形成新的表,

2.1.5 中间表


对于将大量连接查询的表可以创建中间表,从而减少在查询时造成的连接耗时.

2.1.6 增加冗余字段


类似于创建中间表,增加冗余也是为了减少连接查询.

2.1.7 分析表,检查表,优化表


分析表主要是分析表中关键字的分布,检查表主要是检查表中是否存在错误,优化表主要是消除删除或更新造成的表空间浪费.

1、分析表: 使用 ANALYZE 关键字,如ANALYZE TABLE user;


33.jpg

  1. Op:表示执行的操作.
  2. Msg_type:信息类型,有status,info,note,warning,error.
  3. Msg_text:显示信息.

2、检查表: 使用 CHECK关键字,如CHECK TABLE user [option]


option 只对MyISAM有效,共五个参数值:

  1. QUICK:不扫描行,不检查错误的连接.
  2. FAST:只检查没有正确关闭的表.
  3. CHANGED:只检查上次检查后被更改的表和没被正确关闭的表.
  4. MEDIUM:扫描行,以验证被删除的连接是有效的,也可以计算各行关键字校验和.
  5. EXTENDED:最全面的的检查,对每行关键字全面查找.

3、优化表:使用OPTIMIZE关键字,如OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE user;


LOCAL|NO_WRITE_TO_BINLOG都是表示不写入日志.,优化表只对VARCHAR,BLOB和TEXT有效,通过OPTIMIZE TABLE语句可以消除文件碎片,在执行过程中会加上只读锁.

2.2 硬优化


2.2.1 硬件三件套


1、配置多核心和频率高的cpu,多核心可以执行多个线程.
2、配置大内存,提高内存,即可提高缓存区容量,因此能减少磁盘I/O时间,从而提高响应速度.

3、配置高速磁盘或合理分布磁盘:高速磁盘提高I/O,分布磁盘能提高并行操作的能力.


2.2.2 优化数据库参数


优化数据库参数可以提高资源利用率,从而提高MySQL服务器性能.MySQL服务的配置参数都在my.cnf或my.ini,下面列出性能影响较大的几个参数.

  • key_buffer_size:索引缓冲区大小
  • table_cache:能同时打开表的个数
  • query_cache_size和query_cache_type:前者是查询缓冲区大小,后者是前面参数的开关,0表示不使用缓冲区,1表示使用缓冲区,但可以在查询中使用SQL_NO_CACHE表示不要使用缓冲区,2表示在查询中明确指出使用缓冲区才用缓冲区,即SQL_CACHE.
  • sort_buffer_size:排序缓冲区


传送门:更多参数https://www.mysql.com/cn/why-mysql/performance/index.html


2.2.3 分库分表


因为数据库压力过大,首先一个问题就是高峰期系统性能可能会降低,因为数据库负载过高对性能会有影响。另外一个,压力过大把你的数据库给搞挂了怎么办?所以此时你必须得对系统做分库分表 + 读写分离,也就是把一个库拆分为多个库,部署在多个数据库服务上,这时作为主库承载写入请求。然后每个主库都挂载至少一个从库,由从库来承载读请求。

34.jpg

2.2.4 缓存集群


如果用户量越来越大,此时你可以不停的加机器,比如说系统层面不停加机器,就可以承载更高的并发请求。然后数据库层面如果写入并发越来越高,就扩容加数据库服务器。
通过分库分表是可以支持扩容机器的,如果数据库层面的读并发越来越高,就扩容加更多的从库。
但是这里有一个很大的问题:数据库其实本身不是用来承载高并发请求的,所以通常来说,数据库单机每秒承载的并发就在几千的数量级,而且数据库使用的机器都是比较高配置,比较昂贵的机器,成本很高。
如果你就是简单的不停的加机器,其实是不对的。所以在高并发架构里通常都有缓存这个环节,缓存系统的设计就是为了承载高并发而生。所以单机承载的并发量都在每秒几万,甚至每秒数十万,对高并发的承载能力比数据库系统要高出一到两个数量级。
你完全可以根据系统的业务特性,对那种写少读多的请求,引入缓存集群。具体来说,就是在写数据库的时候同时写一份数据到缓存集群里,然后用缓存集群来承载大部分的读请求。这样的话,通过缓存集群,就可以用更少的机器资源承载更高的并发。



35.jpg

结语


一个完整而复杂的高并发系统架构中,一定会包含:各种复杂的自研基础架构系统。各种精妙的架构设计.因此一篇小文顶多具有抛砖引玉的效果,但是数据库优化的思想差不多就这些了.

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
6天前
|
SQL 关系型数据库 MySQL
深入解析MySQL的EXPLAIN:指标详解与索引优化
MySQL 中的 `EXPLAIN` 语句用于分析和优化 SQL 查询,帮助你了解查询优化器的执行计划。本文详细介绍了 `EXPLAIN` 输出的各项指标,如 `id`、`select_type`、`table`、`type`、`key` 等,并提供了如何利用这些指标优化索引结构和 SQL 语句的具体方法。通过实战案例,展示了如何通过创建合适索引和调整查询语句来提升查询性能。
55 9
|
1月前
|
SQL 关系型数据库 MySQL
大厂面试官:聊下 MySQL 慢查询优化、索引优化?
MySQL慢查询优化、索引优化,是必知必备,大厂面试高频,本文深入详解,建议收藏。关注【mikechen的互联网架构】,10年+BAT架构经验分享。
大厂面试官:聊下 MySQL 慢查询优化、索引优化?
|
4天前
|
SQL Oracle 数据库
使用访问指导(SQL Access Advisor)优化数据库业务负载
本文介绍了Oracle的SQL访问指导(SQL Access Advisor)的应用场景及其使用方法。访问指导通过分析给定的工作负载,提供索引、物化视图和分区等方面的优化建议,帮助DBA提升数据库性能。具体步骤包括创建访问指导任务、创建工作负载、连接工作负载至访问指导、设置任务参数、运行访问指导、查看和应用优化建议。访问指导不仅针对单条SQL语句,还能综合考虑多条SQL语句的优化效果,为DBA提供全面的决策支持。
27 11
|
11天前
|
缓存 关系型数据库 MySQL
MySQL 索引优化以及慢查询优化
通过本文的介绍,希望您能够深入理解MySQL索引优化和慢查询优化的方法,并在实际应用中灵活运用这些技术,提升数据库的整体性能。
51 18
|
9天前
|
缓存 关系型数据库 MySQL
MySQL 索引优化以及慢查询优化
通过本文的介绍,希望您能够深入理解MySQL索引优化和慢查询优化的方法,并在实际应用中灵活运用这些技术,提升数据库的整体性能。
17 7
|
9天前
|
缓存 关系型数据库 MySQL
MySQL 索引优化与慢查询优化:原理与实践
通过本文的介绍,希望您能够深入理解MySQL索引优化与慢查询优化的原理和实践方法,并在实际项目中灵活运用这些技术,提升数据库的整体性能。
32 5
|
1月前
|
SQL 关系型数据库 MySQL
MySQL慢查询优化、索引优化、以及表等优化详解
本文详细介绍了MySQL优化方案,包括索引优化、SQL慢查询优化和数据库表优化,帮助提升数据库性能。关注【mikechen的互联网架构】,10年+BAT架构经验倾囊相授。
MySQL慢查询优化、索引优化、以及表等优化详解
|
24天前
|
SQL 存储 BI
gbase 8a 数据库 SQL合并类优化——不同数据统计周期合并为一条SQL语句
gbase 8a 数据库 SQL合并类优化——不同数据统计周期合并为一条SQL语句
|
24天前
|
SQL 数据库
gbase 8a 数据库 SQL优化案例-关联顺序优化
gbase 8a 数据库 SQL优化案例-关联顺序优化
|
28天前
|
关系型数据库 MySQL Java
MySQL索引优化与Java应用实践
【11月更文挑战第25天】在大数据量和高并发的业务场景下,MySQL数据库的索引优化是提升查询性能的关键。本文将深入探讨MySQL索引的多种类型、优化策略及其在Java应用中的实践,通过历史背景、业务场景、底层原理的介绍,并结合Java示例代码,帮助Java架构师更好地理解并应用这些技术。
27 2