您的位置:威尼斯官方网站 > 威尼斯官方网站登录 > 命令查看

命令查看

发布时间:2019-11-21 16:23编辑:威尼斯官方网站登录浏览(102)

    MySQL表碎片收拾

    • 1. 划算碎片大小
    • 2. 照望碎片
      • 2.1 使用alter table table_name engine = innodb命令实行规整。
      • 2.2 使用pt-online-schema-change工具也能展开在线收拾表结构,搜罗碎片等操作。
      • 2.3 使用optimize table命令,收拾碎片。
    • 3. 整合治理表碎片shell脚本

    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.tablesDATA_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可以回笼未使用的长空。

    mysql>optimize table account;
    +--------------+----------+----------+-------------------------------------------------------------------+
    | Table        | Op       | Msg_type | Msg_text                                                          |
    +--------------+----------+----------+-------------------------------------------------------------------+
    | test.account | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
    | test.account | optimize | status   | OK                                                                |
    +--------------+----------+----------+-------------------------------------------------------------------+
    2 rows in set (0.09 sec)
    

     

    3.疏理表碎片shell脚本

    # cat optimize_table.sh

    #!/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

    输出内容
    

    # cat optimize_table_2018-08-30.log

    Begin Optimize Table at: 2018-08-30 08:43:21
    2018-08-30 08:43:21 alter table employees.departments engine=innodb ...
    2018-08-30 08:43:21 alter table employees.dept_emp engine=innodb ...
    2018-08-30 08:43:27 alter table employees.dept_manager engine=innodb ...
    2018-08-30 08:43:27 alter table employees.employees engine=innodb ...
    2018-08-30 08:43:32 alter table employees.salaries engine=innodb ...
    2018-08-30 08:44:02 alter table employees.t1 engine=innodb ...
    2018-08-30 08:44:17 alter table employees.titles engine=innodb ...
    2018-08-30 08:44:28 alter table employees.ttt engine=innodb ...
    End Optimize Table at: 2018-08-30 08:44:28

     

     

    本文由威尼斯官方网站发布于威尼斯官方网站登录,转载请注明出处:命令查看

    关键词: