MySQL表空间集

--MySQL表空间集
----------------------2014-09-20

1. 收缩ibdata的方法,目前MySQL依然没有提供收缩ibdata的方法,只能重构,下面是5.7的步骤。

复制代码
Decreasing the Size of the InnoDB Tablespace
Currently, you cannot remove a data file from the system tablespace. To decrease the system tablespace size, use this procedure:

1. Use mysqldump to dump all your InnoDB tables, including InnoDB tables located in the MySQL database. As of 5.6, there are five InnoDB tables included in the MySQL database:

mysql> select table_name from information_schema.tables where table_schema='mysql' and engine='InnoDB';
+----------------------+
| table_name           |
+----------------------+
| innodb_index_stats   |
| innodb_table_stats   |
| slave_master_info    |
| slave_relay_log_info |
| slave_worker_info    |
+----------------------+
5 rows in set (0.00 sec)
      
Stop the server.

2. Remove all the existing tablespace files (*.ibd), including the ibdata and ib_log files. Do not forget to remove *.ibd files for tables located in the MySQL database.

3. Remove any .frm files for InnoDB tables.

4. Configure a new tablespace.

5. Restart the server.

6. Import the dump files.
复制代码

2. 再看一个官方说明,file-per-table的优势,同时也指出了ibdata文件的空间只能被重用,但无法释放给操作系统。

You can reclaim operating system disk space when truncating or dropping a table. For tables created when file-per-table mode is turned off, truncating or dropping the tables creates free space internally in the ibdata files but the free space can only be used for new InnoDB data.

3. 对于file-per-table的表,回收空间,使用optimize table,实现原理如下面所示,其实就是重建+改名。

You can run OPTIMIZE TABLE to compact or recreate a tablespace. When you run an OPTIMIZE TABLE, InnoDB will create a new .ibd file with a temporary name, using only the space required to store actual data. When the optimization is complete, InnoDB removes the old .ibd file and replaces it with the new .ibd file. If the previous .ibd file had grown significantly but actual data only accounted for a portion of its size, running OPTIMIZE TABLE allows you to reclaim the unused space.

 4. innodb的索引相关,5.5版mysql中测试索引的添加过程,看看mysql做了哪些事情。

复制代码
CREATE TABLE `tindex` (
  `id` int(11) DEFAULT NULL,
  `name` char(10) DEFAULT NULL,
  `address` varchar(20) NOT NULL,
  `c3` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;  --创建一个innodb的表

mysql> alter table tindex add index idx_name(name);       --添加索引的name列
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show profile for query 3;                  --查看profile
+------------------------------+----------+
| Status                       | Duration |
+------------------------------+----------+
| starting                     | 0.000087 |
| checking permissions         | 0.000004 |
| checking permissions         | 0.000004 |
| init                         | 0.000007 |
| Opening tables               | 0.000022 |
| System lock                  | 0.000008 |
| setup                        | 0.000025 |
| creating table               | 0.013806 |            --创建临时表
| After create                 | 0.000003 |
| manage keys                  | 0.019027 |
| rename result table          | 0.000236 |            --完成索引创建工作和表同步后,rename结果表
| end                          | 0.000017 |
| Waiting for query cache lock | 0.000002 |
| end                          | 0.000004 |
| query end                    | 0.000003 |
| closing tables               | 0.000006 |
| freeing items                | 0.000013 |
| cleaning up                  | 0.000002 |
+------------------------------+----------+
18 rows in set (0.00 sec)
复制代码

 --可见就是重建了表,同样alter table tindex engine innodb; 也就是会重建一下表(虽然表本来就是innodb的)

 -- 还有optimize操作,实质就是doing recreate + analyze。

For InnoDB tables prior to 5.7.4 and other table types, MySQL locks the table during the time OPTIMIZE TABLE is running. As of MySQL 5.7.4, OPTIMIZE TABLE is performed online for regular and partitioned InnoDB tables.

总之:5.6之前没有online DDL,所以只要涉及到结构的改变都是重建!

有了online ddl后一些操作不再需要重建表,参考http://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html#innodb-online-ddl-summary-grid

 

posted @   胡.杰  阅读(443)  评论(0编辑  收藏  举报
编辑推荐:
· AI与.NET技术实操系列:基于图像分类模型对图像进行分类
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语,封装的思维:从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热?
阅读排行:
· 25岁的心里话
· 闲置电脑爆改个人服务器(超详细) #公网映射 #Vmware虚拟网络编辑器
· 零经验选手,Compose 一天开发一款小游戏!
· 因为Apifox不支持离线,我果断选择了Apipost!
· 通过 API 将Deepseek响应流式内容输出到前端
历史上的今天:
2013-09-20 RMAN数据库恢复测试
点击右上角即可分享
微信分享提示