Question

我使用的是MySQl,有一个名为cars的表格,载于我的dev_db数据库。

我在表格中插入了6 000 000数据(其中含有大量数据)。如下:

INSERT INTO cars (cid, name, msg, date) 
VALUES (1,  blabla ,  blabla ,  2001-01-08 ),
       (11,  blabla ,  blabla ,  2001-11-28 ), 
       ... ,
       (3,  blabla ,  blabla ,  2010-06-03 );

After this large data insertion into my cars table

I decide to also optimize the table like following:

OPTIMIZE TABLE cars;

我等待了53名议员的优化,最后是做的,我的谢克尔·乔尔向我传达了以下信息:

enter image description here

<代码>Msg_text 下表显示:这一表格并不支持优化......,这使我的脑能2个问题,请:

1. Does the mysql message above means the 53min I waited actually did nothing useful??

<>strong>2. 是用大量数据插入后优化我的表格的必要条件? 以及为什么?

Answer 1

Optimize is useful if you have removed or overwritten rows, or if you have changed indexes. If you just inserted data it is not needed to optimize.

The MySQL Optimize Table command will effectively de-fragment a mysql table and is very useful for tables which are frequently updated and/or deleted.

另见:。

Answer 2

参见:Yous InnoDB table,

Answer 3

As you can read in the output InnoDB does not support optimize as such.
Instead it does a recreate + optimize on the indexes instead.

结果是完全一样的,不应真正地把你们都放在一边,归根结底,你们的指数是优化的。

However you only ever have to optimize your indexes if you delete rows or update indexed fields.
If you only ever insert then your B-trees will not get unbalanced and do not need optimization.

因此:

上面的神话信息是否意味着我等待的53名未成年人实际上没有任何用处?

The time spend waiting was useless, but not for the reason you think.
If there is anything to optimize, MySQL will do it.

在大量数据插入之后,是否有必要优化我的表格? 以及为什么?

No, never.
The reason is that MySQL (InnoDB) uses B-trees, which are fast only if they are balanced.
If the nodes are all on one side of the tree, the index degrades into a ordered list, which gives you O(n) worst case time, An fully balanced tree has O(log n) time.
However the index can only become unbalanced if you delete rows, or alter the values of indexed fields.

友情链接