MySQL不同存储引擎下optimize的用法
optimize命令是mysql的常用优化命令，但是在InnoDB与MyISAM这两个存储引擎中却有很大的分别。本文将对这两个常用的存储引擎进行区分跟实例解析
1、查看mysql当前的存储引擎
一般情况下，mysql会默认提供多种存储引擎,你可以通过下面的查看: 看你的mysql现在已提供什么存储引擎: mysql> show engines; 看你的mysql当前默认的存储引擎: mysql> show variables like '%storage_engine%'; 你要看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎): mysql> show create table 表名;
如下显示zabbix history表的存储引擎
MariaDB [zabbix]> show create table history \G;
*************************** 1. row ***************************
Table: history
Create Table: CREATE TABLE `history` (
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`value` double(16,4) NOT NULL DEFAULT '0.0000',
`ns` int(11) NOT NULL DEFAULT '0',
KEY `history_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
/*!50100 PARTITION BY RANGE (`clock`)
(PARTITION p201802010000 VALUES LESS THAN (1517500800) ENGINE = InnoDB,
PARTITION p201802020000 VALUES LESS THAN (1517587200) ENGINE = InnoDB,
PARTITION p201802030000 VALUES LESS THAN (1517673600) ENGINE = InnoDB,
PARTITION p201802040000 VALUES LESS THAN (1517760000) ENGINE = InnoDB,
PARTITION p201802050000 VALUES LESS THAN (1517846400) ENGINE = InnoDB,
PARTITION p201802070000 VALUES LESS THAN (1518019200) ENGINE = InnoDB,
PARTITION p201802080000 VALUES LESS THAN (1518105600) ENGINE = InnoDB,
PARTITION p201802090000 VALUES LESS THAN (1518192000) ENGINE = InnoDB,
PARTITION p201802100000 VALUES LESS THAN (1518278400) ENGINE = InnoDB,
PARTITION p201802110000 VALUES LESS THAN (1518364800) ENGINE = InnoDB,
PARTITION p201802120000 VALUES LESS THAN (1518451200) ENGINE = InnoDB,
PARTITION p201802130000 VALUES LESS THAN (1518537600) ENGINE = InnoDB,
PARTITION p201802140000 VALUES LESS THAN (1518624000) ENGINE = InnoDB,
PARTITION p201802150000 VALUES LESS THAN (1518710400) ENGINE = InnoDB,
PARTITION p201802160000 VALUES LESS THAN (1518796800) ENGINE = InnoDB,
PARTITION p201802170000 VALUES LESS THAN (1518883200) ENGINE = InnoDB,
PARTITION p201802180000 VALUES LESS THAN (1518969600) ENGINE = InnoDB,
PARTITION p201802190000 VALUES LESS THAN (1519056000) ENGINE = InnoDB,
PARTITION p201802200000 VALUES LESS THAN (1519142400) ENGINE = InnoDB,
PARTITION p201802210000 VALUES LESS THAN (1519228800) ENGINE = InnoDB) */
1 row in set (0.00 sec)
当对表有大量的增删改操作时，需要用optimize对表进行优化。可以减少空间与提高I/O性能性能；
如果表存储引擎为MyISAM可以直接使用命令optimize table 表名;
查看show table status like 'history' \G; 中的data_free选项；data_free选项代表数据碎片。
如果是InnoDB引擎，首先查看innodb_file_per_table（是否独享表空间）。
MariaDB [zabbix]> show variables like 'innodb_file_per_table'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+ 1 row in set (0.01 sec)
ON代表共享表空间打开，OFF代表开启共享表空间没有打开，即采用的是默认的共享表空间。这个时候可以在mysql的datadir路径下看到一个非常大的文件ibdata1，这个文件存储了所有InnoDB表的数据与索引。
如果表是InnoDB，执行如下命令
MariaDB [zabbix]> optimize table history; +----------------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------------+----------+----------+-------------------------------------------------------------------+ | zabbix.history | optimize | note | Table does not support optimize, doing recreate + analyze instead | | zabbix.history | optimize | status | OK | +----------------+----------+----------+-------------------------------------------------------------------+ 2 rows in set (14.33 sec)
会返回如图信息，最后的一条Table does not support optimize, doing recreate + analyze instead，即代表optimize无法优化表。
这个时候使用如下命令优化表
MariaDB [zabbix]> alter table history ENGINE = 'InnoDB'; Query OK, 256060 rows affected (14.27 sec) Records: 256060 Duplicates: 0 Warnings: 0 MariaDB [zabbix]> analyze table history; +----------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------------+---------+----------+----------+ | zabbix.history | analyze | status | OK | +----------------+---------+----------+----------+ 1 row in set (0.00 sec)
-
如果开启了独享表空间，即每张表都有ibdfile。这个时候如果删除了大量的行，索引会重组并且会释放相应的空间因此不必优化
