mysql优化篇

mysql优化篇

偏向于顺序分析，从建库表开始，从库表的引擎、结构、字符集、表字段等再到后来的数据量大了怎么办等。

以下信息整理自互联网与通义千问、gemini等。相当于整理备份，供之后查阅。

1. 数据库结构优化

1.1 尽量使用InnoDB存储引擎

innodb

InnoDB是MySQL默认的存储引擎，也是MySQL中最强大的存储引擎。它具有以下优点：

• 支持事务

事务是一组操作的集合，要么全部成功，要么全部失败。InnoDB支持事务，可以保证数据的完整性。

• 支持外键

外键是用来约束两个表之间关系的字段。InnoDB支持外键，可以保证数据的完整性和一致性。

• 支持崩溃恢复

崩溃恢复是指数据库在发生崩溃后，能够恢复到崩溃前的状态。InnoDB支持崩溃恢复，可以保证数据的安全性。

• 性能优良

InnoDB的性能优良，可以满足大多数应用的需求。

因此，在大多数情况下，都应该尽量使用InnoDB存储引擎。

以下是一些具体的应用场景：

• 需要支持事务的应用

例如，金融、电商等应用都需要支持事务，以保证数据的完整性。

• 需要支持外键的应用

例如，关系型数据库都需要支持外键，以保证数据的完整性和一致性。

• 需要支持崩溃恢复的应用

例如，所有重要的应用都需要支持崩溃恢复，以保证数据的安全性。

• 对性能要求较高的应用

InnoDB的性能优良，可以满足大多数应用的需求。

当然，InnoDB也有一些缺点，例如：

• 占用空间较大

InnoDB存储引擎的表数据文件和索引文件都比较大，因此占用空间较大。

• 对CPU和内存的资源消耗较多

InnoDB存储引擎对CPU和内存的资源消耗较多，因此需要配置较好的硬件环境。

其他引擎

• MyISAM

MyISAM是MySQL中最早的存储引擎，也是MySQL5.5.5之前默认的存储引擎。MyISAM的优点是性能优良、占用空间较小，缺点是不支持事务、外键和崩溃恢复。

• Memory

Memory存储引擎将表数据存储在内存中，因此访问速度非常快。Memory存储引擎的缺点是数据易丢失，不适合存储重要的数据。

• CSV

CSV存储引擎将表数据存储为CSV格式的文件，可以方便地导入导出数据。CSV存储引擎的缺点是性能较差，不适合存储大量数据。

• Archive

Archive存储引擎用于存储历史数据，只支持插入操作，不支持更新和删除操作。

• Federated

Federated存储引擎可以将数据存储在远程服务器上，可以实现数据分布式存储。Federated存储引擎的缺点是配置复杂、性能较差。

• Blackhole

Blackhole存储引擎会丢弃所有写入的数据，可以用于测试或数据清理。

1.2 数据库和表的字符集统一使用UTF8

多种字符集，用于表示文本数据。其中最常用的两种是 UTF-8 和 GBK。

UTF-8:

• 简介: UTF-8 是一种可变长度的字符编码，使用 1 到 4 个字节来表示每个字符。它是 Unicode 的默认编码，也是互联网上最常用的字符编码。
• 优点:
  • 支持所有 Unicode 字符
  • 在不同平台之间兼容
  • 在大多数现代编程语言中都得到支持
• 缺点:
  • 占用空间比 ASCII 或 GBK 等固定长度的字符编码要多
  • 在某些情况下，处理速度可能比其他字符编码慢

GBK:

• 简介: GBK 是汉字编码的一种标准，使用 2 个字节来表示每个字符。它是简体中文环境下常用的字符编码。
• 优点:
  • 占用空间比 UTF-8 少
  • 在简体中文环境下兼容性好
  • 处理速度快
• 缺点:
  • 不支持所有 Unicode 字符
  • 在非简体中文环境下可能无法正确显示

其他字符集:

• ASCII: 使用 7 个字节来表示每个字符，只支持英语和其他西欧语言的字符。
• ISO-8859-1: 使用 8 个字节来表示每个字符，支持西欧、拉丁美洲和中东等地区的语言的字符。
• Unicode: 一种通用的字符编码，支持世界上几乎所有语言的字符。

应用场景:

• 国际化应用: 应该使用 UTF-8 编码，因为它支持所有 Unicode 字符，并在不同平台之间兼容。

• 简体中文应用: 如果不需要支持其他语言的字符，可以使用 GBK 编码，因为它占用空间少，处理速度快。

• 其他语言应用: 应该使用相应的字符编码，例如，日语应用可以使用 Shift-JIS 编码，韩语应用可以使用 EUC-KR 编码。

• ASCII: 使用 7 个字节来表示每个字符，只支持英语和其他西欧语言的字符。

• ISO-8859-1: 使用 8 个字节来表示每个字符，支持西欧、拉丁美洲和中东等地区的语言的字符。

• Unicode: 一种通用的字符编码，支持世界上几乎所有语言的字符。

1.3 所有表和字段都需要添加注释

1.4 尽量控制单表数据量的大小，建议控制在500万以内

1. 性能

• 单表数据量过大，会导致数据库查询效率下降，尤其是涉及全表扫描的查询。
• 过大的数据量也会导致索引膨胀，影响数据库的性能。

2. 可维护性

• 单表数据量过大，会导致表结构变更、数据备份和恢复等操作变得更加复杂和耗时。
• 也容易造成数据冗余和不一致，降低数据库的可维护性。

3. 安全性

• 单表数据量过大，会导致数据库攻击的风险增加。
• 一旦发生数据泄露，也会造成更大的损失。

4. 成本

• 单表数据量过大，会导致数据库服务器的硬件成本和运维成本增加。

5. 技术限制

• 一些数据库对单表数据量有上限限制，例如 MySQL 的 InnoDB 存储引擎默认的最大行数为 2 亿。

当然，500万只是一个建议值，具体的阈值需要根据应用的实际情况进行调整。

以下是一些控制单表数据量大小的策略：

• 垂直拆分: 将表按字段进行拆分，将不同的字段存储在不同的表中。
• 水平拆分: 将表按某个字段进行拆分，将数据分散到多个表中。
• 使用合适的数据类型: 使用合适的数据类型可以节省存储空间。
• 定期清理数据: 定期清理不需要的数据可以减少数据量。

总结:

控制单表数据量的大小，可以提高数据库的性能、可维护性和安全性，并降低成本。

1.5 谨慎使用MySQL分区表

MySQL分区表

1. 数据结构不能随意更改

MySQL分区表的數據結構不能随意更改，否则可能导致数据丢失。例如，不能随意增加或删除分区，也不能随意更改分区字段的类型或长度。

2. 数据不能随意删除

MySQL分区表的數據不能随意删除，否则可能导致数据不一致。例如，不能直接删除分区中的数据，必须先将数据迁移到其他分区或表中。

3. 查询性能受数据量和分区方式的影响

MySQL分区表的查询性能受数据量和分区方式的影响。如果数据量过大，或者分区方式不合理，可能会导致查询性能下降。

4. 升级或迁移时可能存在兼容性问题

MySQL分区表的升级或迁移时可能存在兼容性问题。例如，在不同的MySQL版本之间升级或迁移时，可能需要对分区表进行一些调整。

因此，在使用MySQL分区表之前，需要仔细考虑应用的需求，并进行充分的测试。

以下是一些谨慎使用MySQL分区表的建议:

• 只有在确实需要提高性能或可扩展性时，才使用MySQL分区表。
• 在使用MySQL分区表之前，仔细设计分区方案。
• 对MySQL分区表进行定期维护，例如清理不必要的数据。
• 在升级或迁移MySQL数据库之前，对分区表进行备份。

MySQL分区表是一种将大表物理分割成多个较小、更易管理的部分的技术，但对外部查询而言，分区表看起来仍像是单一的逻辑表。分区表有助于改善大数据量下的查询性能，尤其是在大量数据的筛选、分组和排序操作中，通过预先定义的规则将数据分布到不同的分区中，可以减少查询扫描的数据量，提高查询速度。

RANGE分区：根据列值的范围将数据分配到不同的分区。例如，按照时间范围（如按年或月份）将订单表分区。

LIST分区：基于列值匹配一个预定义的离散值列表进行分区。例如，按照产品类别将商品销售记录分区。

HASH分区：使用用户定义的哈希函数计算列的哈希值，然后根据哈希值均匀分布数据到不同的分区。例如，基于用户ID进行哈希分区。

KEY分区：类似于HASH分区，不过它只支持计算一列或多列的哈希值，并且MySQL会使用内置的哈希函数。同样是为了实现数据的均匀分布。

MySQL还支持子分区（SUBPARTITIONING），即在每个分区内部再进行一次分区，形成嵌套分区结构

MySQL 分区表 与 Sharding-JDBC 分区

MySQL 分区表 和 Sharding-JDBC 分区 都是将数据分散到多个物理存储单元的技术，但两者之间存在一些关键差异：

1. 分区粒度:

• MySQL 分区表是在 数据库层面 进行分区，一个表可以划分成多个分区，每个分区存储一部分数据。
• Sharding-JDBC 分区是在 逻辑层面 进行分区，多个表可以组成一个逻辑表，每个表存储一部分数据。

2. 数据存储:

• MySQL 分区表的所有分区都存储在 同一个数据库实例 中。
• Sharding-JDBC 分区的各个表可以存储在 不同的数据库实例 中。

3. 数据访问:

• MySQL 分区表对外表现为一个单表，用户无需感知分区的细节，由数据库内部进行路由。
• Sharding-JDBC 需要用户在应用程序代码中显式指定要访问的分片，例如通过 /* sharding_hint=order_id=123 */ 这样的注释。

4. 适用场景:

• MySQL 分区表适用于 单数据库实例 下的数据量大、需要提高查询性能的场景。
• Sharding-JDBC 适用于 多数据库实例 下的数据量超大、需要提高数据库可扩展性的场景。

以下表格总结了 MySQL 分区表和 Sharding-JDBC 分区的关键区别：

特性	MySQL 分区表	Sharding-JDBC 分区
分区粒度	数据库层面	逻辑层面
数据存储	同一个数据库实例	不同的数据库实例
数据访问	无需感知分区	显式指定分片
适用场景	单数据库实例、数据量大、提高查询性能	多数据库实例、数据量超大、提高可扩展性

drive_spreadsheet导出到 Google 表格

总结:

MySQL 分区表和 Sharding-JDBC 分区都是行之有效的数据分片技术，选择哪种技术取决于具体的应用场景。

建议:

• 如果您的应用只有一个数据库实例，并且需要提高查询性能，可以考虑使用 MySQL 分区表。
• 如果您的应用需要使用多个数据库实例，并且需要提高数据库可扩展性，可以考虑使用 Sharding-JDBC 分区。

1.6 经常一起使用的列放到一个表中

1.7 禁止在表中建立预留字段

1.8 禁止在数据库中存储文件（比如图片）这类大的二进制数据

2. 优化SQL语句

• 尽量使用SELECT column1, column2，避免使用*
• 尽量使用WHERE子句来过滤数据，避免使用ORDER BY子句来过滤数据
• 尽量使用JOIN子句来连接表，避免使用子查询
• 尽量使用索引来加速查询

3. 分表技术

• 水平分割：将表按某个字段进行分割，将数据分散到多个表中
• 垂直分割：将表按字段进行分割，将不同的字段存储在不同的表中

4. 分区技术

• 将表按某个字段进行分区，将数据分散到多个分区中

5. 读写分离

• 将读写操作分离到不同的服务器上

6. 存储过程

• 使用存储过程可以将复杂的SQL语句封装起来，提高执行效率

7. 对mysql配置优化

• 配置最大并发数
• 调整缓存大小

8. MySQL服务器硬件升级

• 升级CPU、内存、磁盘等硬件

9. 定期清理数据库

• 定期清理不需要的数据
• 定期进行碎片整理

10. 使用MySQL官方提供的优化工具

• MySQL官方提供了很多优化工具，比如mysqldumpslow、mysqltuner等

以下是一些具体的优化技巧

• 使用合适的索引

索引是MySQL中最重要的性能优化技术之一。索引可以帮助MySQL快速找到数据，从而提高查询效率。

• 优化连接查询

连接查询是MySQL中常见的查询类型。连接查询的性能优化主要包括以下几个方面：

* 尽量减少连接表的数量
* 尽量使用索引来连接表
* 尽量使用合适的数据类型

• 优化排序

排序是MySQL中常见的操作。排序的性能优化主要包括以下几个方面：

* 尽量使用索引来排序
* 尽量使用合适的排序算法

• 优化缓存

MySQL的缓存可以提高数据库的性能。缓存的性能优化主要包括以下几个方面：

* 调整缓存大小
* 监控缓存的使用情况