MysqL碎片整理优化

忘是亡心i 2024-04-19 09:50 86阅读 0赞

删除数据必然会在数据文件中造成不连续的空白空间,而当插入数据时,这些空白空间则会被利用起来.于是造成了数据的存储位置不连续,以及物理存储顺序与理论上的排序顺序不同,这种是数据碎片.实际上数据碎片分为两种,一种是单行数据碎片,另一种是多行数据碎片.前者的意思就是一行数据,被分成N个片段,存储在N个位置.后者的就是多行数据并未按照逻辑上的顺序排列.当有大量的删除和插入操作时,必然会产生很多未使用的空白空间,这些空间就是多出来的额外空间.索引也是文件数据,所以也会产生索引碎片,理由同上,大概就是顺序紊乱的问题.Engine 不同,OPTIMIZE 的操作也不一样的,MyISAM 因为索引和数据是分开的,所以 OPTIMIZE 可以整理数据文件,并重排索引。这样不但会浪费空间，并且查询速度也更慢。

解决方案：（切记，一定要在夜里执行，表越大，越耗资源时间，不要频繁修复，可以几个月甚至一年修复一次，如果表频繁被更改，可以按周/月来整理。）

查看表碎片的方法

mysql> select ROW_FORMAT,TABLE_ROWS,DATA_LENGTH,INDEX_LENGTH,MAX_DATA_LENGTH,DATA_FREE,ENGINE from TABLES where TABLE_SCHEMA='test_db' and TABLE_NAME='table_name' limit 1;

![format_png][]

通过OPTIMIZE TABLE　table\_name 后再查询一下结果如下

![format_png 1][]

也可以观察Mysql的数据目录存储文件Data文件大小，如MYD

一、Innodb存储引擎清理碎片方法

ALTER TABLE tablename ENGINE=InnoDB

二、Myisam存储引擎清理碎片方法

OPTIMIZE 操作会暂时锁住表,而且数据量越大,耗费的时间也越长,它毕竟不是简单查询操作.所以把 Optimize 命令放在程序中是不妥当的,不管设置的命中率多低,当访问量增大的时候,整体命中率也会上升,这样肯定会对程序的运行效率造成很大影响.比较好的方式就是做个 Script,定期检查mysql中 information\_schema.TABLES字段,查看 DATA\_FREE 字段,大于0话,就表示有碎片.脚本多长时间运行一次,可以根据实际情况来定,比如每周跑一次.

OPTIMIZE TABLE table_name

### 1. 计算碎片大小 ###

要整理碎片，首先要了解碎片的计算方法。

可以通过`show table [from|in db_name] status like '%table_name%'`命令查看：

mysql> show table from employees status like 't1'\G
    *************************** 1. row ***************************
               Name: t1
             Engine: InnoDB
            Version: 10
         Row_format: Dynamic
               Rows: 1176484
     Avg_row_length: 86
        Data_length: 101842944
    Max_data_length: 0
       Index_length: 0
          Data_free: 39845888
     Auto_increment: NULL
        Create_time: 2018-08-28 13:40:19
        Update_time: 2018-08-28 13:50:43
         Check_time: NULL
          Collation: utf8mb4_general_ci
           Checksum: NULL
     Create_options: 
            Comment: 
    1 row in set (0.00 sec)

**碎片大小** = 数据总大小 - 实际表空间文件大小

*  **数据总大小** = `Data_length + Data_length` = 101842944
 *  **实际表空间文件大小** = `rows * Avg_row_length` = 1176484 \* 86 = 101177624
 *  **碎片大小** = (101842944 - 101177624) / 1024 /1024 = 0.63MB

**通过`information_schema.tables`的`DATA_FREE`列查看表有没有碎片：**

SELECT t.TABLE_SCHEMA,
           t.TABLE_NAME,
           t.TABLE_ROWS,
           t.DATA_LENGTH,
           t.INDEX_LENGTH,
           concat(round(t.DATA_FREE / 1024 / 1024, 2), 'M') AS datafree
    FROM information_schema.tables t
    WHERE t.TABLE_SCHEMA = 'employees'
    
    
    +--------------+--------------+------------+-------------+--------------+----------+
    | TABLE_SCHEMA | TABLE_NAME   | TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH | datafree |
    +--------------+--------------+------------+-------------+--------------+----------+
    | employees    | departments  |          9 |       16384 |        16384 | 0.00M    |
    | employees    | dept_emp     |     331143 |    12075008 |     11567104 | 0.00M    |
    | employees    | dept_manager |         24 |       16384 |        32768 | 0.00M    |
    | employees    | employees    |     299335 |    15220736 |            0 | 0.00M    |
    | employees    | salaries     |    2838426 |   100270080 |     36241408 | 5.00M    |
    | employees    | t1           |    1191784 |    48824320 |     17317888 | 5.00M    |
    | employees    | titles       |     442902 |    20512768 |     11059200 | 0.00M    |
    | employees    | ttt          |          2 |       16384 |            0 | 0.00M    |
    +--------------+--------------+------------+-------------+--------------+----------+
    8 rows in set (0.00 sec)

### 2. 整理碎片 ###

#### 2.1 使用`alter table table_name engine = innodb`命令进行整理。 ####

root@localhost [employees] 14:27:01> alter table t1   engine=innodb;
    
     Query OK, 0 rows affected (5.69 sec)
     Records: 0  Duplicates: 0  Warnings: 0
    
     root@localhost [employees] 14:27:15> show table status like 't1'\G
     *************************** 1. row ***************************
               Name: t1
             Engine: InnoDB
            Version: 10
         Row_format: Dynamic
               Rows: 1191062
     Avg_row_length: 48
        Data_length: 57229312
    Max_data_length: 0
       Index_length: 0
          Data_free: 2097152
     Auto_increment: NULL
        Create_time: 2018-08-28 14:27:15
        Update_time: NULL
         Check_time: NULL
          Collation: utf8mb4_general_ci
           Checksum: NULL
     Create_options: 
            Comment: 
     1 row in set (0.00 sec)

#### 2.2 使用pt-online-schema-change工具也能进行在线整理表结构，收集碎片等操作。 ####

[root@mysqldb1 14:29:29 /root]
     # pt-online-schema-change --alter="ENGINE=innodb" D=employees,t=t1 --execute
     Cannot chunk the original table `employees`.`t1`: There is no good index and the table is oversized. at /opt/percona-toolkit-3.0.11/bin/pt-online-schema-change line 5852.
    
     需要表上有主键或唯一索引才能执行
    
     [root@mysqldb1 14:31:16 /root]
    # pt-online-schema-change --alter='engine=innodb' D=employees,t=salaries --execute
    No slaves found.  See --recursion-method if host mysqldb1 has slaves.
    Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
    Operation, tries, wait:
      analyze_table, 10, 1
      copy_rows, 10, 0.25
      create_triggers, 10, 1
      drop_triggers, 10, 1
      swap_tables, 10, 1
      update_foreign_keys, 10, 1
    Altering `employees`.`salaries`...
    Creating new table...
    Created new table employees._salaries_new OK.
    Altering new table...
    Altered `employees`.`_salaries_new` OK.
    2018-08-28T14:37:01 Creating triggers...
    2018-08-28T14:37:01 Created triggers OK.
    2018-08-28T14:37:01 Copying approximately 2838426 rows...
    Copying `employees`.`salaries`:  74% 00:10 remain
    2018-08-28T14:37:41 Copied rows OK.
    2018-08-28T14:37:41 Analyzing new table...
    2018-08-28T14:37:42 Swapping tables...
    2018-08-28T14:37:42 Swapped original and new tables OK.
    2018-08-28T14:37:42 Dropping old table...
    2018-08-28T14:37:42 Dropped old table `employees`.`_salaries_old` OK.
    2018-08-28T14:37:42 Dropping triggers...
    2018-08-28T14:37:42 Dropped triggers OK.
    Successfully altered `employees`.`salaries`.

#### 2.3 使用optimize table命令，整理碎片。 ####

运行`OPTIMIZE TABLE`， InnoDB创建一个新的.ibd具有临时名称的文件，只使用存储的实际数据所需的空间。优化完成后，InnoDB删除旧.ibd文件并将其替换为新文件。如果先前的.ibd文件显着增长但实际数据仅占其大小的一部分，则运行OPTIMIZE TABLE可以回收未使用的空间。

### 3. 整理表碎片shell脚本 ###

#!/bin/sh  
    socket=/tmp/mysql3306.sock
    time=`date +"%Y-%m-%d"` 
    SQL="select concat(d.TABLE_SCHEMA,'.',d.TABLE_NAME) from information_schema.TABLES d where d.TABLE_SCHEMA = 'employees'"
    
    optimize_table_name=$(/usr/local/mysql/bin/mysql -S $socket -e "$SQL"|grep -v "TABLE_NAME")
    
    echo "Begin Optimize Table at: "`date +"%Y-%m-%d %H:%M:%S"`>/tmp/optimize_table_$time.log
    
    for table_list in $optimize_table_name  
    do  
    
    echo `date +"%Y-%m-%d %H:%M:%S"` "alter table $table_list engine=innodb ...">>/tmp/optimize_table_$time.log
    /usr/local/mysql/bin/mysql -S $socket -e  "alter table $table_list engine=innoDB" 
    
    done 
    echo "End Optimize Table at: "`date +"%Y-%m-%d %H:%M:%S"`>>/tmp/optimize_table_$time.log

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWFnZXMyMDE3LmNuYmxvZ3MuY29tL2Jsb2cvNjc2MzcyLzIwMTcxMi82NzYzNzItMjAxNzEyMjYxMDQ3NTIyMjgtODEwOTAyNDk1LnBuZw?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWFnZXMyMDE3LmNuYmxvZ3MuY29tL2Jsb2cvNjc2MzcyLzIwMTcxMi82NzYzNzItMjAxNzEyMjYxMDQ3MzkzMjItMTkwNTM4NjAzOS5wbmc?x-oss-process=image/format,png