mysql avgrowlength_innodb数据库 OPTIMIZE TABLE 提示Table does not support optimize, doing recreate + anal...

innodb数据库 OPTIMIZE TABLE 提示Table does not support optimize, doing recreate + analyze instead 分类：

Mysql 2014-09-24 14:09

2559人阅读

评论(0)

收藏

举报 Table does not support optimize, doing recreate + analyze instead 提要： 1.MySQL官方建议不要经常(每小时或每天)进行碎片整理，一般根据实际情况，只需要每周或者每月整理一次即可。 2.OPTIMIZE TABLE只对MyISAM，BDB和InnoDB表起作用，尤其是MyISAM表的作用最为明显。此外，并不是所有表都需要进行碎片整理，一般只需要对包含上述可变长度的文本数据类型的表进行整理即可。 3.在OPTIMIZE TABLE运行过程中，MySQL会锁定表。 4.默认情况下，直接对InnoDB引擎的数据表使用OPTIMIZE TABLE，可能会显示「 Table does not support optimize, doing recreate + analyze instead」的提示信息。这个时候，我们可以用mysqld --skip-new或者mysqld --safe-mode命令来重启MySQL，以便于让其他引擎支持OPTIMIZE TABLE。 那么怎么使用--skip-new呢？ 先看ps看一下mysql的进程吧! [plain]

view plain copy print ? ps aux | grep mysqld

root

2359

0.0

0.0 108300

1308 pts/2

S

13:09

0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql

mysql

3028 46.2 56.5 5771800 4554616 pts/2 Sl

13:09

10:13 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/122-db.err --open-files-limit=65535 --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock --port=3306

root

3355

0.0

0.0 103240

872 pts/2

S+

13:31

0:00 grep mysqld

第一个是守护进程，第二个就是mysql的原始进程。我们只要在这个进程最后追加--skip-new即可。 [plain]

view plain copy print ? /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/122-db.err --open-files-limit=65535 --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock --port=3306 --skip-new

先关闭mysqld，然后执行该命令。 不要关闭该命令，重新开启一个ssh窗口。 [plain]

view plain copy print ? mysql -uroot -pxxxxx

mysql> use hr_resume_center;

Database changed

mysql> show create table hr_business_from_0\G

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

Table: hr_business_from_0

Create Table: CREATE TABLE `hr_business_from_0` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`resume_id` bigint(20) unsigned NOT NULL COMMENT '简历ID',

`from_id` smallint(4) unsigned DEFAULT NULL COMMENT '来源类型: 与hr_dict_from 来源网站关联',

`link_id` varchar(50) DEFAULT NULL COMMENT '来源网站简历标识',

`add_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '添加记录时间',

`sys_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '更新记录时间',

`version` smallint(4) unsigned NOT NULL DEFAULT '0' COMMENT '妙招简历版本，默认都为最新的版本',

PRIMARY KEY (`id`),

KEY `resume_id` (`resume_id`),

KEY `sys_time` (`sys_time`)

) ENGINE=InnoDB AUTO_INCREMENT=5853666 DEFAULT CHARSET=utf8 COMMENT='简历来源表'

1 row in set (0.00 sec)

mysql> show index from hr_business_from_0;

+--------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table

| Non_unique | Key_name

| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+--------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| hr_business_from_0 |

0 | PRIMARY

|

1 | id

| A

|

5802629 |

NULL | NULL

|

| BTREE

|

|

|

| hr_business_from_0 |

1 | resume_id |

1 | resume_id

| A

|

5802629 |

NULL | NULL

|

| BTREE

|

|

|

| hr_business_from_0 |

1 | sys_time

|

1 | sys_time

| A

|

290131 |

NULL | NULL

|

| BTREE

|

|

|

+--------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

3 rows in set (0.99 sec)

mysql> optimize table hr_business_from_0 ;

Query OK, 5853665 rows affected (2 min 30.36 sec)

Records: 5853665

Duplicates: 0

Warnings: 0

mysql> show index from hr_business_from_0;

+--------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table

| Non_unique | Key_name

| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+--------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| hr_business_from_0 |

0 | PRIMARY

|

1 | id

| A

|

5898310 |

NULL | NULL

|

| BTREE

|

|

|

| hr_business_from_0 |

1 | resume_id |

1 | resume_id

| A

|

5898310 |

NULL | NULL

|

| BTREE

|

|

|

| hr_business_from_0 |

1 | sys_time

|

1 | sys_time

| A

|

842615 |

NULL | NULL

|

| BTREE

|

|

|

+--------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

3 rows in set (0.00 sec)

hr_business_from_0为innodb数据，经过优化Cardinality发生的变化。查询速度提成了10倍以上。 附加： 如果你觉得这样一个表优化速度太慢的话，这里有一键优化脚本： [plain]

view plain copy print ? #!/bin/sh

echo -n "MySQL username: " ; read username

echo -n "MySQL password: " ; stty -echo ; read password ; stty echo ; echo

mysql -u $username -p"$password" -NBe "SHOW DATABASES;" | grep -v 'lost+found' | while read database ; do

mysql -u $username -p"$password" -NBe "SHOW TABLE STATUS;" $database | while read name engine version rowformat rows avgrowlength datalength maxdatalength indexlength datafree autoincrement createtime updatetime checktime collation checksum createoptions comment ; do

if [ "$datafree" -gt 0 ] ; then

fragmentation=$(($datafree * 100 / $datalength))

echo "$database.$name is $fragmentation% fragmented."

mysql -u "$username" -p"$password" -NBe "OPTIMIZE TABLE $name;" "$database"

fi

done

done

,Table does not support optimize, doing recreate + analyze instead 提要： 1.MySQL官方建议不要经常(每小时或每天)进行碎片整理，一般根据实际情况，只需要每周或者每月整理一次即可。 2.OPTIMIZE TABLE只对MyISAM，BDB和InnoDB表起作用，尤其是MyISAM表的作用最为明显。此外，并不是所有表都需要进行碎片整理，一般只需要对包含上述可变长度的文本数据类型的表进行整理即可。 3.在OPTIMIZE TABLE运行过程中，MySQL会锁定表。 4.默认情况下，直接对InnoDB引擎的数据表使用OPTIMIZE TABLE，可能会显示「 Table does not support optimize, doing recreate + analyze instead」的提示信息。这个时候，我们可以用mysqld --skip-new或者mysqld --safe-mode命令来重启MySQL，以便于让其他引擎支持OPTIMIZE TABLE。 那么怎么使用--skip-new呢？ 先看ps看一下mysql的进程吧! [plain]

view plain copy print ? ps aux | grep mysqld

root

2359

0.0

0.0 108300

1308 pts/2

S

13:09

0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql

mysql

3028 46.2 56.5 5771800 4554616 pts/2 Sl

13:09

10:13 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/122-db.err --open-files-limit=65535 --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock --port=3306

root

3355

0.0

0.0 103240

872 pts/2

S+

13:31

0:00 grep mysqld

第一个是守护进程，第二个就是mysql的原始进程。我们只要在这个进程最后追加--skip-new即可。 [plain]

view plain copy print ? /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/122-db.err --open-files-limit=65535 --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock --port=3306 --skip-new

先关闭mysqld，然后执行该命令。 不要关闭该命令，重新开启一个ssh窗口。 [plain]

view plain copy print ? mysql -uroot -pxxxxx

mysql> use hr_resume_center;

Database changed

mysql> show create table hr_business_from_0\G

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

Table: hr_business_from_0

Create Table: CREATE TABLE `hr_business_from_0` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`resume_id` bigint(20) unsigned NOT NULL COMMENT '简历ID',

`from_id` smallint(4) unsigned DEFAULT NULL COMMENT '来源类型: 与hr_dict_from 来源网站关联',

`link_id` varchar(50) DEFAULT NULL COMMENT '来源网站简历标识',

`add_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '添加记录时间',

`sys_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '更新记录时间',

`version` smallint(4) unsigned NOT NULL DEFAULT '0' COMMENT '妙招简历版本，默认都为最新的版本',

PRIMARY KEY (`id`),

KEY `resume_id` (`resume_id`),

KEY `sys_time` (`sys_time`)

) ENGINE=InnoDB AUTO_INCREMENT=5853666 DEFAULT CHARSET=utf8 COMMENT='简历来源表'

1 row in set (0.00 sec)

mysql> show index from hr_business_from_0;

+--------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table

| Non_unique | Key_name

| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+--------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| hr_business_from_0 |

0 | PRIMARY

|

1 | id

| A

|

5802629 |

NULL | NULL

|

| BTREE

|

|

|

| hr_business_from_0 |

1 | resume_id |

1 | resume_id

| A

|

5802629 |

NULL | NULL

|

| BTREE

|

|

|

| hr_business_from_0 |

1 | sys_time

|

1 | sys_time

| A

|

290131 |

NULL | NULL

|

| BTREE

|

|

|

+--------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

3 rows in set (0.99 sec)

mysql> optimize table hr_business_from_0 ;

Query OK, 5853665 rows affected (2 min 30.36 sec)

Records: 5853665

Duplicates: 0

Warnings: 0

mysql> show index from hr_business_from_0;

+--------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table

| Non_unique | Key_name

| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+--------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| hr_business_from_0 |

0 | PRIMARY

|

1 | id

| A

|

5898310 |

NULL | NULL

|

| BTREE

|

|

|

| hr_business_from_0 |

1 | resume_id |

1 | resume_id

| A

|

5898310 |

NULL | NULL

|

| BTREE

|

|

|

| hr_business_from_0 |

1 | sys_time

|

1 | sys_time

| A

|

842615 |

NULL | NULL

|

| BTREE

|

|

|

+--------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

3 rows in set (0.00 sec)

hr_business_from_0为innodb数据，经过优化Cardinality发生的变化。查询速度提成了10倍以上。 附加： 如果你觉得这样一个表优化速度太慢的话，这里有一键优化脚本： [plain]

view plain copy print ? #!/bin/sh

echo -n "MySQL username: " ; read username

echo -n "MySQL password: " ; stty -echo ; read password ; stty echo ; echo

mysql -u $username -p"$password" -NBe "SHOW DATABASES;" | grep -v 'lost+found' | while read database ; do

mysql -u $username -p"$password" -NBe "SHOW TABLE STATUS;" $database | while read name engine version rowformat rows avgrowlength datalength maxdatalength indexlength datafree autoincrement createtime updatetime checktime collation checksum createoptions comment ; do

if [ "$datafree" -gt 0 ] ; then

fragmentation=$(($datafree * 100 / $datalength))

echo "$database.$name is $fragmentation% fragmented."

mysql -u "$username" -p"$password" -NBe "OPTIMIZE TABLE $name;" "$database"

fi

done

done