MySQL kill使用案例

浅浅的花香味﹌ 2022-12-07 04:26 138阅读 0赞

MySQL数据库版本 8.0.17

### 文章目录 ###

*  前言
 *  一.查询表大小
 *  二.碎片整理
 *  参考文献:

# 前言 #

业务有需求，将一张大表 1000w+数据，需要从Oracle数据库全量同步到MySQL  
前DBA用python写的脚本，每天全量同步一次，先delete再insert  
现在表的空间已经差不多2G了，需要进行清理

# 一.查询表大小 #

select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from information_schema.TABLES where table_schema='load_data' and table_name='tab_test';

查看表大小

mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from information_schema.TABLES where table_schema='load_data' and table_name='tab_test';
    +-----------+
    | data      |
    +-----------+
    | 2050.02MB |
    +-----------+
    1 row in set (0.04 sec)

查看表有多少数据量

mysql> select count(*) from tab_test;
    +----------+
    | count(*) |
    +----------+
    |  5177608 |
    +----------+
    1 row in set (16.78 sec)

**500多w数据，居然占了2G的空间**

# 二.碎片整理 #

alter table tab_test engine=innodb;

居然遇到表锁的问题了

**居然有一个进程被描述为killed 此时重新执行依旧卡住,显示在等元数据锁**

mysql> SELECT * FROM information_schema.INNODB_TRX\G
    *************************** 1. row ***************************
                        trx_id: 421158938794160
                     trx_state: RUNNING
                   trx_started: 2020-08-19 09:30:23
         trx_requested_lock_id: NULL
              trx_wait_started: NULL
                    trx_weight: 0
           trx_mysql_thread_id: 177411
                     trx_query: NULL
           trx_operation_state: NULL
             trx_tables_in_use: 0
             trx_tables_locked: 0
              trx_lock_structs: 0
         trx_lock_memory_bytes: 1136
               trx_rows_locked: 0
             trx_rows_modified: 0
       trx_concurrency_tickets: 0
           trx_isolation_level: REPEATABLE READ
             trx_unique_checks: 1
        trx_foreign_key_checks: 1
    trx_last_foreign_key_error: NULL
     trx_adaptive_hash_latched: 0
     trx_adaptive_hash_timeout: 0
              trx_is_read_only: 0
    trx_autocommit_non_locking: 0
    *************************** 2. row ***************************
                        trx_id: 421158938789840
                     trx_state: RUNNING
                   trx_started: 2020-08-18 18:00:58
         trx_requested_lock_id: NULL
              trx_wait_started: NULL
                    trx_weight: 0
           trx_mysql_thread_id: 174386
                     trx_query: select count(*) from tab_test
           trx_operation_state: counting records
             trx_tables_in_use: 1
             trx_tables_locked: 0
              trx_lock_structs: 0
         trx_lock_memory_bytes: 1136
               trx_rows_locked: 0
             trx_rows_modified: 0
       trx_concurrency_tickets: 0
           trx_isolation_level: REPEATABLE READ
             trx_unique_checks: 1
        trx_foreign_key_checks: 1
    trx_last_foreign_key_error: NULL
     trx_adaptive_hash_latched: 0
     trx_adaptive_hash_timeout: 0
              trx_is_read_only: 1
    trx_autocommit_non_locking: 1
    2 rows in set (0.00 sec)

-- 也可以这样拼接select 语句
    mysql> select concat('KILL ',id,';') from information_schema.processlist where user='recording_user' and db = 'load_data';
    +------------------------+
    | concat('KILL ',id,';') |
    +------------------------+
    | KILL 174386;           |
    | KILL 177571;           |
    | KILL 177572;           |
    +------------------------+
    3 rows in set (0.02 sec)

**找了一圈也没有找到回滚的，只能申请晚上重启mysql实例了**

**难道mysql 8.0版本修复了这个问题，delete+insert的居然没用**

mysql> alter table tab_test engine innodb;
    Query OK, 0 rows affected (1 min 36.81 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from information_schema.TABLES where table_schema='load_data' and table_name='tab_test';
    +-----------+
    | data      |
    +-----------+
    | 2204.02MB |
    +-----------+
    1 row in set (0.00 sec)
    
    mysql> select version();
    +-----------+
    | version() |
    +-----------+
    | 8.0.17    |
    +-----------+
    1 row in set (0.00 sec)

# 参考文献: #

1.[https://blog.csdn.net/m0\_37827567/article/details/82979767?utm\_source=blogxgwz5][https_blog.csdn.net_m0_37827567_article_details_82979767_utm_source_blogxgwz5]

[https_blog.csdn.net_m0_37827567_article_details_82979767_utm_source_blogxgwz5]: https://blog.csdn.net/m0_37827567/article/details/82979767?utm_source=blogxgwz5