再谈MySQL的optimize table

MySQL的optimize table是个比较特殊的语句，之前写过这篇《小白学习MySQL - InnoDB支持optimize table？》，对该语句进行了些许讲解。

碰巧看到知乎上的JokerJason老师，写了一篇《MySQL：Optimize Table》的文章，用了一些代码，测试了optimize table对锁、ibd文件等的影响，还是比较直观，可以学习了解。

简介

在 MySQL 数据库中，OPTIMIZE TABLE 是一个重要的命令，用于优化表的性能和空间利用。通过重新组织表的存储结构，去除碎片，OPTIMIZE TABLE 可以帮助提高查询性能、减少存储空间占用以及减少数据碎片。本文将深入探讨 OPTIMIZE TABLE 的原理、用法、示例以及对 .ibd 文件的影响。

什么情况下使用

使用delete语句删除数据时，delete语句只会将记录的位置或者数据页标记为"可复用"，但是数据库磁盘文件的大小不会改变，即表空间不会被回收，此时使用该命令可以释放空间，压缩数据文件。

原理

执行 OPTIMIZE TABLE 命令的过程包括以下几个关键步骤：

• 创建临时表：MySQL 首先会创建一个与原表结构相同的临时表。
• 复制数据到临时表：将原表中的数据复制到临时表中。
• 优化表：在数据复制的过程中，MySQL 会对数据进行整理和重组，去除碎片，提高数据的连续性。
• 重命名表：当数据复制完成并且表被优化后，MySQL 会删除原表，然后将临时表重命名为原表的名称。

通过这个过程，OPTIMIZE TABLE 可以帮助数据库重新组织表的存储结构，提高查询性能和减少存储空间占用。

示例

• 创建测试数据，

"""
测试optimize table 是否长期锁表
"""
import pymysql




def main():
    conn = pymysql.connect(host="localhost",
                           user="root",
                           password="root",
                           database="test_optimize")
    cursor = conn.cursor()
    sql_create = """
    create table if not exists `person` (
        `id` int primary key auto_increment,
        `name` varchar(255),
        `marks` smallint,
        `create_at` datetime,
        `update_at` datetime
    ) engine=InnoDB default charset=utf8;
    """
    cursor.execute(sql_create)


    sql_insert = """insert into `person` (`name`, `marks`, `create_at`)
    values (%s, %s, now())
    """
    for i in range(195854, 100000000):
        insert_data = (i, 1)
        cursor.execute(sql_insert, insert_data)


        if i % 2 == 0:
            sql_update = """update `person` set create_at = now(), update_at = now() limit 1"""
            cursor.execute(sql_update)
        if i%999 == 0:
            print(i)


        i+=1
    conn.commit()
    cursor.close()
    conn.close()




if __name__ == '__main__':
    main()

开启三个线程执行update操作,在MySQL客户端执行optimize table 命令，查看update是否有明显延时。

import concurrent
import datetime
import time
from concurrent.futures import ThreadPoolExecutor


import pymysql




def main():
    conn = pymysql.connect(host="localhost",
                           user="root",
                           password="root",
                           database="test_optimize")
    cursor = conn.cursor()


    for i in range(0, 10000000):
        if i % 2 == 0:
            sql_update = """update `person` set create_at = now(), update_at = now() limit 1"""
            cursor.execute(sql_update)
            conn.commit()
        print("{}, time: {}".format(i, datetime.datetime.now()))
        i += 1


    cursor.close()
    conn.close()




if __name__ == '__main__':
    # 使用ThreadPoolExecutor创建一个线程池
    with concurrent.futures.ThreadPoolExecutor(max_workers=3) as executor:
        # 提交任务到线程池
        futures = [executor.submit(main) for _ in range(3)]


        # 异步执行任务，并在每个任务完成后立即处理结果
        for future in concurrent.futures.as_completed(futures):
            result = future.result()
            print(result)

执行脚本，并在MySQL客户端执行optimize table person。

MySQL root@(none):test_optimize> optimize table person
+----------------------+----------+----------+-------------------------------------------------------------------+
| Table                | Op       | Msg_type | Msg_text                                                          |
+----------------------+----------+----------+-------------------------------------------------------------------+
| test_optimize.person | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test_optimize.person | optimize | status   | OK                                                                |
+----------------------+----------+----------+-------------------------------------------------------------------+


2 rows in set
Time: 8.854s

这里执行optimize table时间大概有9s，如果整个过程加锁，则update操作会有明显的延迟。这里与MySQL的版本有关系，5.6及以下版本，的确会在整个过程加锁。

更详细的信息可以通过MySQL官网查看，

https://dev.mysql.com/doc/refman/8.3/en/optimize-table.html

查看ibd文件的变化

执行前，

root@fresh-vdh510:/var/lib/mysql/test_optimize# du -sh person.ibd 
49M person.ibd

执行后，

root@fresh-vdh510:/var/lib/mysql/test_optimize# du -sh person.ibd 
15M person.ibd

ibd文件

MySQL中的.ibd文件是InnoDB存储引擎用来存储表数据和索引的物理文件。每个使用InnoDB存储引擎的表，如果启用了独立表空间（每表一个文件），则会有一个对应的.ibd文件来存储该表的所有数据和所有的索引结构。

具体来说，.ibd文件包含以下内容：

• 表的数据行记录
• 表的所有索引，包括主键索引和其他辅助索引
• 用于实现多版本并发控制（MVCC）的信息，这是InnoDB为了支持事务处理和高并发场景而采用的技术。

在InnoDB中，表可以被组织为不同的表空间类型：

• 系统表空间：所有表共享一个大的表空间文件（ibdata1等）。
• 独立表空间：每个表及其索引分别存储在各自的.ibd文件中。

管理.ibd文件的方法可能包括：

• 备份与恢复：直接复制.ibd文件作为备份的一种方式，但在恢复时需要注意一致性问题，通常建议使用数据库级的备份和恢复方法。
• 表空间操作：如收缩表空间、优化表碎片等高级管理任务。

需要注意的是，在进行任何涉及.ibd文件的操作时，应当确保数据库服务已停止或者相关表处于只读状态，以防止数据不一致或损坏。同时，直接操作.ibd文件而不通过MySQL服务器API可能会导致数据丢失或不可预测的行为。

也就是说在执行optimize的时候，因为其本质是创建了临时表，所以会生成一个新的ibd文件，在临时表覆盖原表之后，原ibd文件也会被删除。

注意事项

使用OPTIMIZE TABLE 作用于InnoDB和MYISM引擎时（只有这两个引擎可用），在MySQL5.7和MySQL8.0及以上版本的时候，因为数据库支持online DDL，可以并发执行。所以执行这个语句的时候，会有一小段时间锁表，之后DML语句是可以执行的。

使用OPTIMIZE TABLE 需要创建一个临时表，所以需要在此之前保证磁盘容量足够。

如果您认为这篇文章有些帮助，还请不吝点下文章末尾的"点赞"