mysql要做表分析吗_mysql中的优化表和分析表有什么区别

要扩展到

@MitchWheat’s answer(1直接回答第一个)：

OPTIMIZE TABLE在执行一些表压缩后执行ANALYZE TABLE.相当于OPTIMIZE TABLE mydb.mytable;如果表是MyISAM是这样的：

ALTER TABLE mydb.mytable ENGINE=MyISAM;

ANALYZE TABLE mydb.mytable;

MyISAM数据

对于datadir / var / lib / mysql中的MyISAM表mydb.mytable,您有以下文件：

> /var/lib/mysql/mydb/mytable.frm

> /var/lib/mysql/mydb/mytable.MYD(数据)

> /var/lib/mysql/mydb/mytable.MYI(索引)

OPTIMIZE TABLE mydb.mytable会缩小表的.MYD和.MYI文件.

这与InnoDB不同.以下是它的不同之处：

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

> /var/lib/mysql/mydb/mytable.frm

> /var/lib/mysql/mydb/mytable.ibd

执行OPTIMIZE TABLE mydb.mytable时,mytable.ibd会缩小.

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

执行OPTIMIZE TABLE mydb.mytable时,数据和索引页将在ibdata1中连续写入.不幸的是,这使得ibdata1实现了跨越式发展.

更新2013-02-26 22:33美国东部时间

你的评论是

I think, optimize table for innodb is not supported. I got a message, index will be recreated. How does it work?

我试过这个

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>

你是对的.您不能将OPTIMIZE TABLE作为单个操作运行. InnoDB的功能如下：

ALTER TABLE mydb.mytable ENGINE=InnoDB;

ANALYZE TABLE mydb.mytable;

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

但是,老实说,您不必对InnoDB表运行ANALYZE TABLE,因为每次执行查询时,InnoDB存储引擎都会根据索引中的页面执行对表基数的估计.如果有大量的INSERT,UPDATE和DELETE,那么您将需要ANALYZE TABLE.当有大量DELETE时,则ALTER TABLE mydb.mytable ENGINE = InnoDB;需要缩小表格.

在某些情况下,我实际上写过有关InnoDB上ANALYZE TABLE无效的帖子：