在mysql中优化表和分析表有什么区别?我已阅读在线文档,但不确定有什么区别。

#1 楼

要扩展@MitchWheat的答案(首先直接回答+1),请执行以下操作:做一些表压缩。如果表是MyISAM,则等效于OPTIMIZE TABLE mydb.mytable;是:

ALTER TABLE mydb.mytable ENGINE=MyISAM;
ANALYZE TABLE mydb.mytable;

具有以下文件:


/var/lib/mysql

/var/lib/mysql/mydb/mytable.frm(数据)

/var/lib/mysql/mydb/mytable.MYD(索引)

/var/lib/mysql/mydb/mytable.MYI会缩小表的OPTIMIZE TABLE mydb.mytable.MYD文件。

InnoDB并不相同。它的不同之处在于:

InnoDB(启用了innodb_file_per_table)

每个表的数据和索引都存储在外部表空间文件中。对于.MYIdatadir和表/var/lib/mysql,它将存储如下: 。

InnoDB(禁用innodb_file_per_table)

仅存在mydb.mytable。表/var/lib/mysql/mydb/mytable.frm的所有数据页和索引页都存储在系统表空间文件/var/lib/mysql/mydb/mytable.ibd中。

执行OPTIMIZE TABLE mydb.mytable时,数据页和索引页连续写入ibdata1中。不幸的是,这使ibdata1突飞猛进。

请参阅Percona CTO Vadim Tkachenko的图片表示法



UPDATE 2013-02- 26:EST EST

我的评论是


我认为,不支持针对innodb的优化表。我收到一条消息,索引将被重新创建。它是如何工作的?


我已经尝试过了

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.29 MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed
mysql> create table dat (a int, primary key (a));
Query OK, 0 rows affected (0.08 sec)

mysql> insert into dat values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
Query OK, 10 rows affected (0.04 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> analyze table dat;
+----------+---------+----------+----------+
| Table    | Op      | Msg_type | Msg_text |
+----------+---------+----------+----------+
| test.dat | analyze | status   | OK       |
+----------+---------+----------+----------+
1 row in set (0.06 sec)

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

mysql>


您是正确的。您不能将mytable.ibd作为单个操作运行。 InnoDB的工作如下:

ALTER TABLE mydb.mytable ENGINE=InnoDB;
ANALYZE TABLE mydb.mytable;


您也可以自己运行这些步骤。

但是,老实说,您不必对InnoDB表运行/var/lib/mysql/mydb/mytable.frm,因为每次执行查询时,InnoDB存储引擎都会根据索引中的页面传递对表基数进行估算。如果mydb.mytable/var/lib/mysql/ibdata1OPTIMIZE TABLE mydb.mytable的数量很多,则需要OPTIMIZE TABLE。当有大量的ANALYZE TABLE时,就需要INSERTs来缩小表。 br />
UPDATEs:突然必须重建索引以防止站点崩溃


DELETEs:MySQL Query Optimizer从哪里读取索引统计信息? br />

评论


我认为,不支持针对innodb的优化表。我收到一条消息,索引将被重新创建。它是如何工作的?

–布尔
13年2月27日在3:23

@RolandoMySQLDBA我不确定当您说“您无法运行OPTIMIZE TABLE”时的含义。当您为InnoDB表优化表时,MySQL会在显示“正在执行重新创建+分析表”时为您执行ALTER TABLE ... ENGINE = InnoDB和ANALYZE TABLE ...操作。

– Michael-sqlbot
13年2月27日在4:14

@ Michael-sqlbot如我的答案所示,我运行了OPTIMIZE TABLE dat;在MySQL 5.5.29中,它立即抱怨,表不支持优化,而是进行重新创建+分析。这就是为什么我建议ALTER TABLE dat ENGINE = InnoDB;分析表数据;

– RolandoMySQLDBA
13年2月27日在21:27



是的,但是我断言这是对您立即发生的,因为您测试的表很小。当您在InnoDB上优化表时,服务器实际上实际上会在返回响应之前在后台执行ALTER TABLE ... ENGINE = InnoDB和ANALYZE TABLE ...因此您确实可以在InnoDB上运行OPTIMIZE TABLE,并达到预期的效果。

– Michael-sqlbot
13年2月28日在3:25

#2 楼

取决于您的MySQL和存储引擎的版本,但一般来说:

OPTIMIZE TABLE
分析表,存储表的键分布,回收未使用的空间并对数据文件进行碎片整理。

分析表
仅分析表并存储密钥分布。