再谈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()

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 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

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