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 |
+-----------------------+-------+
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 |
+----------------+----------+----------+-------------------------------------------------------------------+
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, rows affected (14.27 sec)
Records: Duplicates: Warnings: MariaDB [zabbix]> analyze table history;
+----------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------+---------+----------+----------+
| zabbix.history | analyze | status | OK |
+----------------+---------+----------+----------+
row in set (0.00 sec)
如果开启了独享表空间,即每张表都有ibdfile。这个时候如果删除了大量的行,索引会重组并且会释放相应的空间因此不必优化
- 由于共享表空间所有表的数据与索引都存放于ibddata1文件中,随着数据量的增长会导致该文件越来越大。超过10G的时候查询速度就非常慢,因此在编译的时候最好开启独享表空间。因为mysql默认是关闭了独享表空间,下面有两个解决方案
- 方案一:先逻辑备份所有的数据库,将配置文件中innodb_file_per_table参数=1,再将备份导入
- 方案二:只要修改innodb_file_per_table参数,然后将需要修改的所有innodb的表都运行一遍 alter table table_name engine=innodb;即可使用第二种方式修改后,原来库中的表中的数据会继续存放于ibdata1中,新建的表才会使用独立表空间
最新文章
- 个人记录比较好的css样式
- 【SVN多用户开发】代码冲突&;解决办法
- robotframework笔记27
- 【题解】【排列组合】【回溯】【Leetcode】Gray Code
- 第二次正式java web开发项目的总结(回收站恢复)
- Contest2037 - CSU Monthly 2013 Oct (Problem J: Scholarship)
- MultiTouch camera controls source code
- js-权威指南学习笔记4
- discuz_style_default.xml 修改
- web前端学习就这9个阶段,你属于哪个阶段?
- 腾讯windows系统服务器
- JS+CSS实现弹出全屏灰黑色透明遮罩效果的方法
- Centos7上安装、破解bamboo6.0.3
- Skipping acquire of configured file ···doesn't support architecture 'i386' acquire of configured file
- ubuntu源码安装jdk8
- document.ready(function(){}),window.onload,$(function(){})的区别
- eureka client服务续约源码分析
- 廖雪峰Java2面向对象编程-4抽象类和接口-2接口
- 车牌、手机、身份证、等敏感信息 屏蔽 替换 、中文转unicode编码 函数
- MAC nginx代理设置