MySQL性能优化 - 行格式(row format)

原创

爱我所爱

修改于 2024-11-26 20:43:03

2090

文章被收录于专栏：MySQLMySQL

今日推荐： Spring AI 再更新：如何借助全局参数实现智能数据库操作与个性化待办管理

文章地址：https://cloud.tencent.com/developer/article/2464797

推荐语: AI的大潮已经来临，我们怎么用AI来提高生产力，写出更好的程序，Spring AI是一个与时俱进的工具。这篇文章演示了怎么用工具来搭建一个简易版的个人助理系统，怎么让AI的灵活性进入到业务场景，给用户提供更智能的服务，或许读完该文会有所启示。

下面开始今天对于怎样选择mysql的行格式的介绍

MySQL中行格式对于一张表的存储空间，I/O性能，更新和查询性能有显著影响。本文将演示如何查看和更改一张表的行格式，并对更改行格式的表做简单性能测试。演示的数据库为MySQL官方实例数据库employees

首先我们可以测试一个表在当前row format的时候的性能

MacBook-Pro:~ hongyan$ mysqlslap --concurrency=25 --iterations=5 --query="select * from salaries" --user=username --create-schema=employees --password=password  --no-drop --verbose 
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
	Average number of seconds to run all queries: 29.060 seconds
	Minimum number of seconds to run all queries: 26.110 seconds
	Maximum number of seconds to run all queries: 35.127 seconds
	Number of clients running queries: 25
	Average number of queries per client: 1

查看一下当前表的行格式

mysql> select ROW_FORMAT from information_schema.TABLES where TABLE_SCHEMA = "employees" and TABLE_NAME = "salaries";
+------------+
| ROW_FORMAT |
+------------+
| Dynamic    |
+------------+
1 row in set (0.01 sec)

修改当前表的行格式

mysql> alter table employees row_format = Compact;
Query OK, 0 rows affected (0.99 sec)
Records: 0  Duplicates: 0  Warnings: 0

再测试一下修改行格式以后得性能

MacBook-Pro:~ hongyan$ mysqlslap --concurrency=25 --iterations=5 --query="select * from salaries" --user=username --create-schema=employees --password=password  --no-drop --verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
	Average number of seconds to run all queries: 27.956 seconds
	Minimum number of seconds to run all queries: 27.032 seconds
	Maximum number of seconds to run all queries: 29.431 seconds
	Number of clients running queries: 25
	Average number of queries per client: 1

查看系统默认的行格式

mysql> show variables like 'innodb_default_row_format';
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| innodb_default_row_format | dynamic |
+---------------------------+---------+
1 row in set (0.02 sec)

从以上查询可以看到该innodb engine来说目前默认的row format是dynamic

不同行格式的优劣势

整体而言, Compact和Dynamic格式对于大多数应用是推荐的选择，而Compressed格式适用于存储有限的场景。Redundant格式已逐渐被淘汰, Key-Value适合于特定的NoSQL的需求。

Compact - 主要是对存储进行了优化，如可变长度数据列的存储优化，使用位图来表示NULL值，减少了空间开销，但是对于大字段不如Dynamic格式
Dynamic - 适合于大字段，对于BLOB和TEXT类型的数据，可以减少I/O和页开销，但是更新操作导致页分裂可能会影响性能，适合
Compressed - 使用压缩算法减少磁盘占用，适合大批量数据，但是压缩和解压缩增加了CPU开销，也会使I/O读取变复杂，可能会影响读取和写入性能。
Key-Value - 在MySQL 8.0及以后得版本可用，提供NoSQL对于非关系型数据存储的能力，适用于需要大规模数据存储和快速检索，但是缺少关系型数据库的特性，比如说复杂查询和对于事务的支持。

创建表格时指定表格的行格式

mysql> CREATE TABLE example (
    ->     id INT AUTO_INCREMENT PRIMARY KEY,
    ->     name VARCHAR(100)
    -> ) ENGINE=InnoDB ROW_FORMAT=COMPACT;
Query OK, 0 rows affected (0.01 sec)

mysql> select ROW_FORMAT from information_schema.TABLES where TABLE_SCHEMA = "employees" and TABLE_NAME = "example";
+------------+
| ROW_FORMAT |
+------------+
| Compact    |
+------------+
1 row in set (0.01 sec)

修改引擎的默认行格式

mysql> set GLOBAL innodb_default_row_format = "COMPACT";
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'innodb_default_row_format';
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| innodb_default_row_format | compact |
+---------------------------+---------+
1 row in set (0.01 sec)

更改之后所有新创建的innodb的表都会采用compact作为默认行格式

可以根据具体的业务需求，如字段类型，需不需要事务，对于存储空间的要求来选定适合的行格式优化存储和查询的性能。

由于更改行格式会影响性能，特别是负载比较高的环境，所以最好先在开发环境中进行测试。

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

mysql