MySQL表碎片整理,生产景况MySQL表的保养

MySQL表碎片整理

  • 一.
    乘除碎片大小
  • 贰. 整治碎片
    • 2.1 使用alter table table_name engine =
      innodb命令举办重新整建。
    • 贰.二使用pt-online-schema-change工具也能开始展览在线整理表结构,搜集碎片等操作。
    • 贰.三 使用optimize
      table命令,整理碎片。
  • 3. 整理表碎片shell脚本

出于公司数据库中的数据量异常的大,定时对合营社的mysql数据库中的数据表进行优化操作(关于optimize的叙述如下所示),数据库中有300多张数据表,手工业去操作分明不太现实,用脚本来实施功能依旧很科学的,脚本如下:

生育景况MySQL表的掩护:check、optimize和analyze

一. 计量碎片大小

要照料碎片,首先要理解碎片的计量方法。

能够透过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必发88手机客户端 ,的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)

 

mysql手册中有关 OPTIMIZE 的叙述:

 

二. 规整碎片

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [,
tbl_name] …

 ㈠ optimize

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)

 

若是你曾经去除了表的一大学一年级些,也许只要你已经对包罗可变长度行的表(含有VAPRADOCHAKuga,
BLOB或TEXT列的表)进行了繁多改动,则应运用

        

二.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`.

 

OPTIMIZE
TABLE。被剔除的笔录被有限支撑在链接清单中,后续的INSERT操作会重新采取旧的笔录地方。您能够采纳OPTIMIZE
TABLE来再一次

        optimize能够回收空间、收缩碎片、进步I/O

二.叁 使用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)

 

接纳未采用的长空,并整理数据文件的零碎。

        方今支撑的蕴藏引擎有:InnoDB、MyASIM和AHavalC梅毒E

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

 

 

采纳方法:sh optimize.sh word

        

[[email protected]
shell]#

       
如果是Replication环境、可加NO_WRITE_TO_BINLOG(可能LOCAL、意思完全同样)、举个例子:

#!/bin/sh

        optimize local table table_name;

time_log=/opt/optimize_time

        

 

        以下是一个轻巧测试:

sum=$#

 

if [ “$sum” -eq 0 ]

[plain] 

then

[[email protected]
employees]$ ls -alh t.ibd  

echo “Error: no parameter chosed”

-rw-rw—- 1 mysql dba 24M 05-22 16:48 t.ibd  

exit 1

  

fi

未optimize前、有24M  

 

  

for i in $*;do

mysql> optimize table t;  

echo “optimize database $i starting …”

+————-+———-+———-+——————————————————————-+
 

tables=$(/usr/bin/mysql $i -udevuser -pdevuser -e “show tables” | grep
-v “Tables” > /opt/$i)

| Table       | Op       | Msg_type | Msg_text                        
                                 |  

tablelist=$(cat /opt/$i)

+————-+———-+———-+——————————————————————-+
 

 

| employees.t | optimize | note     | Table does not support optimize,
doing recreate + analyze instead |  

echo “optimize database $i starting …………….” >>
$time_log

| employees.t | optimize | status   | OK                                
                               |  

echo “$i start at $(date +[%Y/%m/%d/%H:%M:%S])” >> $time_log

+————-+———-+———-+——————————————————————-+
 

 

2 rows in set (3.82 sec)  

for list in $tablelist

  

do

–对于InnoDB的表、上面包车型大巴内容并非报错、那是MySQL会帮您映射到:alter table
table_name engine=’InnoDB’;  

echo $list

–MyISAM不会有那种状态  

/usr/bin/mysql $i -utaobao -padmin -e “optimize table $list”

  

done

[[email protected]
employees]$ ls -alh t.ibd  

 

-rw-rw—- 1 mysql dba 14M 05-22 16:49 t.ibd  

echo “$i end at $(date +[%Y/%m/%d/%H:%M:%S])” >> $time_log

    

echo >> $time_log

optimize后、剩14M  

done

 

     ㈡ check

        

        检查表或视图的有无错误

        协理表引擎有:InnoDB和MyISAM

        

        上边轻巧模拟贰个测试:

 

[plain] 

mysql> check table t;  

+————-+——-+———-+———-+  

| Table       | Op    | Msg_type | Msg_text |  

+————-+——-+———-+———-+  

| employees.t | check | status   | OK       |  

+————-+——-+———-+———-+  

1 row in set (0.63 sec)  

  

–未有不当的气象是这么的  

  

–用vim展开t.frm随便编辑两把  

  

mysql> check table t\G;  

*************************** 1. row
***************************  

   Table: employees.t  

      Op: check  

Msg_type: Error  

Msg_text: Incorrect information in file: ‘./employees/t.frm’  

*************************** 2. row
***************************  

   Table: employees.t  

      Op: check  

Msg_type: error  

Msg_text: Corrupt  

2 rows in set (0.00 sec)  

  

–报错了  

 

     ㈢ analyze

        

        用于收集优化器计算消息、和tuning相关、

        那一个命令对 MyISAM、BDB、InnoDB 存款和储蓄引擎的表有作用

        借使不想记录到binlog、也可加关键字local恐怕其它3个

 

[plain] 

mysql> analyze table t\G;  

*************************** 1. row
***************************  

   Table: employees.t  

      Op: analyze  

Msg_type: Error  

Msg_text: Incorrect information in file: ‘./employees/t.frm’  

*************************** 2. row
***************************  

   Table: employees.t  

      Op: analyze  

Msg_type: error  

Msg_text: Corrupt  

2 rows in set (0.00 sec)  

㈠ optimize optimize能够回收空间、减弱碎片、提升I/O
近期帮助的积存引擎有:InnoDB、MyASIM和A帕杰罗C…