Shulou(Shulou.com)06/01 Report--

In MySQL, we often use variable-length text data types such as VARCHAR, TEXT, BLOB, and so on. However, when we use these data types, we have to do some extra work-- MySQL data table defragmentation.

Every time MySQL removes a line from your list, the space is left blank. A large number of deletions over a period of time will make this empty space more than the space used to store the contents of the list.

When MySQL scans data, the object it scans is actually the upper limit of the capacity requirement of the list, that is, the part of the area where the data is written at its peak. If a new insert is made, MySQL will try to take advantage of these empty areas, but will still not be able to fully occupy them.

1. Or look at the space occupied by a table, as well as the size of the fragment.

Select table_name,engine,table_rows,data_length+index_length length,DATA_FREE from information_schema.tables where TABLE_SCHEMA='test'

Or

Select table_name,engine,table_rows,data_length+index_length length,DATA_FREE from information_schema.tables where data_free! = 0

+-+

| | table_name | engine | table_rows | length | DATA_FREE | |

+-+

| | curs | InnoDB | 0 | 16384 | 0 | |

| | t | InnoDB | 10 | 32768 | 0 | |

| | T1 | InnoDB | 9 | 32768 | 0 | |

| | tn | InnoDB | 7 | 16384 | 0 | |

+-+

Name of the table_name table

Engine: storage engine for tables

Number of rows that exist in the table_rows table

Size of the data_length table (table data + index size)

DATA_FREE: the size of the table fragment

The above units are all byte bytes

Defragment:

The defragmentation process will lock the edge and try to do the operation during the business trough.

1. Myisam storage engine collects fragments

Optimize table aaa_safe,aaa_user,t_platform_user,t_user

2. Innodb storage engine collects fragments

Alter table t engine=innodb

:

1.MySQL officials recommend that defragmentation should not be done frequently (hourly or daily), but only once a week or once a month, depending on the actual situation.

While 2.OPTIMIZE TABLE is running, MySQL locks the table.

4. By default, use OPTIMIZE TABLE or on the data table of the InnoDB engine directly

Script reclaims innodb table fragments

#! / bin/bash

DB=test

USER=root

PASSWD=root123

HOST=192.168.2.202

MYSQL_BIN=/usr/local/mysql/bin

D_ENGINE=InnoDB

$MYSQL_BIN/mysql-h$HOST-u$USER-p$PASSWD $DB-e "select TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA='" $DB "'"; "| grep-v" TABLE_NAME "> tables.txt

For t_name in `cat tables.txt`

Do

Echo "Starting table $t_name."

Sleep 1

$MYSQL_BIN/mysql-h$HOST-u$USER-p$PASSWD $DB-e "alter table $t_name engine='" $D_ENGINE "'"

If [$?-eq 0]

Then

Echo "shrink table $t_name ended." > > con_table.log

Sleep 1

Else

Echo "shrink failed!" > > con_table.log

Fi

Done

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.