mysql的optimize

今天测试了一下optimize,发现不错,下面是测试过程:

CREATE TABLE `moe` (
  `phonenum` varchar(11) NOT NULL,
  `citynum` int(11) NOT NULL DEFAULT ‘0‘,
  `phonetype` int(11) DEFAULT NULL,
  PRIMARY KEY (`phonenum`,`citynum`)
)


load data infile ‘/tmp/hun.txt‘ into table moe FIELDS TERMINATED BY ‘,‘ ENCLOSED BY ‘"‘ LINES TERMINATED BY ‘\n‘;

mysql> select count(*) from moe;
+----------+
| count(*) |
+----------+
|  4232081 |
+----------+
1 row in set (1.75 sec)

[mysql@BJ-CSK-0-135 moe]$ ll |grep moe
-rw-rw---- 1 mysql mysql      8644 9月  19 15:03 moe.frm
-rw-rw---- 1 mysql mysql 310378496 9月  19 15:15 moe.ibd

mysql> show index from moe;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| moe   |          0 | PRIMARY  |            1 | phonenum    | A         |     4219450 |     NULL | NULL   |      | BTREE      |         |               |
| moe   |          0 | PRIMARY  |            2 | citynum     | A         |     4219450 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> delete from moe limit 2100000;        
Query OK, 2100000 rows affected, 1 warning (18.06 sec)

mysql> select count(*) from moe;
+----------+
| count(*) |
+----------+
|  2132081 |
+----------+
1 row in set (2.14 sec)

mysql> show index from moe;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| moe   |          0 | PRIMARY  |            1 | phonenum    | A         |     2272822 |     NULL | NULL   |      | BTREE      |         |               |
| moe   |          0 | PRIMARY  |            2 | citynum     | A         |     2272822 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

index减少差不多一半

[mysql@BJ-CSK-0-135 moe]$ ll |grep moe
-rw-rw---- 1 mysql mysql      8644 9月  19 15:03 moe.frm
-rw-rw---- 1 mysql mysql 310378496 9月  19 15:26 moe.ibd

表大小不变

optimize table moe;

mysql> show index from moe;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| moe   |          0 | PRIMARY  |            1 | phonenum    | A         |     2126976 |     NULL | NULL   |      | BTREE      |         |               |
| moe   |          0 | PRIMARY  |            2 | citynum     | A         |     2126976 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> select count(*) from moe; 
+----------+
| count(*) |
+----------+
|  2132081 |
+----------+
1 row in set (0.84 sec)

[mysql@BJ-CSK-0-135 moe]$ ll |grep moe
-rw-rw---- 1 mysql mysql     8644 9月  19 15:27 moe.frm
-rw-rw---- 1 mysql mysql 92274688 9月  19 15:28 moe.ibd

引用官方文档如下:

13.5.2.5. OPTIMIZE TABLE语法

 

  OPTIMIZE [LOCAL |NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

 

  如果您已经删除了表的一大部分,或者如果您已经对含有可变长度行的表(含有VARCHAR, BLOBTEXT列的

  表)进行了很多更改,则应使用OPTIMIZE TABLE。被删除的记录被保持在链接清单中,后续的INSERT操作会

  重新使用旧的记录位置。您可以使用OPTIMIZE TABLE来重新利用未使用的空间,并整理数据文件的碎片。

 

  在多数的设置中,您根本不需要运行OPTIMIZE TABLE。即使您对可变长度的行进行了大量的更新,您也不需

  要经常运行,每周一次或每月一次即可,只对特定的表运行。

 

  OPTIMIZETABLE只对MyISAM, BDBInnoDB表起作用。

 

  对于InnoDB表,OPTIMIZE TABLE被映射到ALTER TABLE上,这会重建表。

 

  注意,在OPTIMIZE TABLE运行过程中,MySQL会锁定表。

 

  OPTIMIZE TABLE语句被写入到二进制日志中,除非使用了自选的NO_WRITE_TO_BINLOG关键词(或其别

  LOCAL)。已经这么做了,因此,用于MySQL服务器的OPTIMIZE TABLE命令的作用相当于一个复制主服务

  器,在默认情况下,这些命令将被复制到复制从属服务器中。


本文出自 “刚刚出壳的小鸟” 博客,请务必保留此出处http://qhd2004.blog.51cto.com/629417/1555194

mysql的optimize

上一篇:SQL Error 1227: Access denied; you need the SUPER privilege for this operation


下一篇:Mysql集群