MySQL调优总结

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: MySQL调优总结![image.png](https://ucc.alicdn.com/pic/developer-ecology/iwqh2q6uom5gu_3e21e3fe298246b1ba6ea816a7006f8b.png)

一. 表设计与数据类型

尽量遵循数据库设计三大范式,一些场景可适当建立冗余字段,减少连接查询,但这样这样会破坏第三范式,视情况而定。

尽量不要设置null类型字段,null类型需要额外的字段来存储,count不会统计,一些场景也会使索引失效。

使用UNSIGNED无符号类型,可提高正数的数量上限,在一些不需要存储负数的情况下,将类型设置无符号数。

没有太大的必要使用BigDecimal类型,可用bigint,将小数方法10^n存入,这样可以避免浮点数计算不准确和DECIMAL精确计算代价高的问题。

表中的列不要太多,如果列太多而实际使用的列又很少的话,有可能会导致CPU占用过高。

把IP地址存成 UNSIGNEDINT,IP地址转换成int类型正好是int的有符号取整范围

设置固定长度的字段会提高性能,因为MySQL搜寻得会更快一些,因为这些固定的长度是很容易计算下一个数据的偏移量的,所以读取的自然也会很快。而如果字段不是定长的,那么,每一次要找下一条的话,需要程序找到主键。但是固定长度的字段会浪费一些空间

url 可以将数据倒序存入数据库,或者字符串进行crc32哈希处理

分库分表

二. 索引

创建适当数量的索引,在创建表的时候就创建好索引,尽量避免在表中有大量数据的时候创建索引。

合适的场景创建覆盖索引

在区分度高的字段上建立索引

控制单个索引的长度,如key(name(8))

尽量使用自增id

在数据不会重复的列上建立唯一索引

三. SQL

每一条sql都尽量使用explain查看执行计划,防止慢sql出现

遵循符合索引最左匹配原则

尽量不要在索引列上做表达式运算,或使用函数,会使索引失效

尽量不要使用多个范围查询,会使索引失效

查看执行计划,如果索引的使用优化器优化有问题,可以强制使用想要适应的索引force index

or查询用union优化代替,如果是相同字段的or,优化为in查询

数据类型出现隐式转换的时候不会命中索引,特别是当列类型是字符串,一定要将字符常量值用引号引起来。

like查询尽量使用左匹配

join查询,on连接的字段上一定要建立索引

避免select *,避免返回无用字段

insert批量插入,不要单条插入

当仅需要获取一条数据时,使用limit 1

四. 程序层面

使用数据库连接池连接数据库

使用缓存对查询数据做缓存,如MyBatis的一二级缓存、redis缓存

五. innodb参数

max_connections:允许客户端并发连接的最大数量,默认值是151,一般将该参数设置为300-1000


max_connect_errors:如果客户端尝试连接的错误数量超过这个参数设置的值,则服务器不再接受新的客户端连接。可以通过清空主机的缓存来解除服务器的这种阻止新连接的状态,通过FLUSH HOSTS或mysqladmin flush-hosts命令来清空缓存。这个参数的默认值是100,一般将该参数设置为100000。


innodb_buffer_pool_size 缓存索引和行数据,在专属mysql服务器上,可设置为内存的80%左右,可减少索引存储在磁盘上,减少IO


innodb_thread_concurrency(5.7版本中已废除) InnoDB内核中允许的线程数,这个值取决于,硬件,应用,OS scheduler properties,设置的太高会线程上下文切换频繁,降低效率,所以不要设置的太高。推荐4核4线程(4C4T)值设置为4-8即可,最大不建议超过线程数*2


innodb_write_io_threads: 限制写相关的线程,缺省值为4


innodb_read_io_threads: 限制写相关的线程,缺省值为4


还有很多其他相关参数,可视情况设置。


六. 架构

主从架构,读写分离。

参考如下:

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
6月前
|
存储 关系型数据库 MySQL
2024年Java秋招面试必看的 | MySQL调优面试题
随着系统用户量的不断增加,MySQL 索引的重要性不言而喻,对于后端工程师,只有在了解索引及其优化的规则,并应用于实际工作中后,才能不断的提升系统性能,开发出高性能、高并发和高可用的系统。 今天小编首先会跟大家分享一下MySQL 索引中的各种概念,然后介绍优化索引的若干条规则,最后利用这些规则,针对面试中常考的知识点,做详细的实例分析。
351 0
2024年Java秋招面试必看的 | MySQL调优面试题
|
6月前
|
SQL 监控 关系型数据库
MySQL性能调优:监控和优化
MySQL性能调优:监控和优化
138 1
|
6月前
|
存储 关系型数据库 MySQL
Mysql高可用|索引|事务 | 调优
Mysql高可用|索引|事务 | 调优
|
17天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
85 1
|
2月前
|
存储 SQL 关系型数据库
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
MySQL调优主要分为三个步骤:监控报警、排查慢SQL、MySQL调优。 排查慢SQL:开启慢查询日志 、找出最慢的几条SQL、分析查询计划 。 MySQL调优: 基础优化:缓存优化、硬件优化、参数优化、定期清理垃圾、使用合适的存储引擎、读写分离、分库分表; 表设计优化:数据类型优化、冷热数据分表等。 索引优化:考虑索引失效的11个场景、遵循索引设计原则、连接查询优化、排序优化、深分页查询优化、覆盖索引、索引下推、用普通索引等。 SQL优化。
541 15
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
|
18天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第26天】数据库作为现代应用系统的核心组件,其性能优化至关重要。本文主要探讨MySQL的索引策略与查询性能调优。通过合理创建索引(如B-Tree、复合索引)和优化查询语句(如使用EXPLAIN、优化分页查询),可以显著提升数据库的响应速度和稳定性。实践中还需定期审查慢查询日志,持续优化性能。
47 0
|
2月前
|
存储 缓存 关系型数据库
【MySQL调优】如何进行MySQL调优?一篇文章就够了!
MySQL调优主要分为三个步骤:监控报警、排查慢SQL、MySQL调优。 排查慢SQL:开启慢查询日志 、找出最慢的几条SQL、分析查询计划 。 MySQL调优: 基础优化:缓存优化、硬件优化、参数优化、定期清理垃圾、使用合适的存储引擎、读写分离、分库分表; 表设计优化:数据类型优化、冷热数据分表等。 索引优化:考虑索引失效的11个场景、遵循索引设计原则、连接查询优化、排序优化、深分页查询优化、覆盖索引、索引下推、用普通索引等。 SQL优化。
【MySQL调优】如何进行MySQL调优?一篇文章就够了!
|
5月前
|
SQL 算法 关系型数据库
【MySQL】专栏合集,从基础概念到调优
【MySQL】专栏合集,从基础概念到调优
42 0
|
3月前
|
关系型数据库 MySQL Java
面试官:说说MySQL调优?
面试官:说说MySQL调优?
88 5
面试官:说说MySQL调优?
|
3月前
|
SQL 关系型数据库 MySQL
Mysql原理与调优-事务与MVCC
【8月更文挑战第19天】