Home | 简体中文 | 繁体中文 | 杂文 | 知乎专栏 | Github | OSChina 博客 | 云社区 | 云栖社区 | Facebook | Linkedin | 视频教程 | 打赏(Donations) | About
知乎专栏多维度架构 微信号 netkiller-ebook | QQ群:128659835 请注明“读者”

16.5. 关于切换引擎

转换ENGINE从MyISAM到InnoDB会经历creating table然后copy to tmp table在修改表名几个阶段,过程非常缓慢

		
mysql> show processlist;
+------+-----------------+---------------------+---------+---------+-------+-----------------------------+------------------------------------------+
| Id   | User            | Host                | db      | Command | Time  | State                       | Info                                     |
+------+-----------------+---------------------+---------+---------+-------+-----------------------------+------------------------------------------+
|    1 | event_scheduler | localhost           | NULL    | Daemon  |    10 | Waiting for next activation | NULL                                     |
| 3167 | dba             | 192.168.6.20:56723  | example | Query   |     2 | creating table              | ALTER TABLE `mytable`	ENGINE=InnoDB   |
| 3172 | dba             | localhost           | example | Query   |     0 | NULL                        | show processlist                         |
+------+-----------------+---------------------+---------+---------+-------+-----------------------------+------------------------------------------+
18 rows in set (0.00 sec)
		
		

copy to tmp table 过程

		
mysql> show processlist;
+------+-----------------+---------------------+---------+---------+-------+-----------------------------+------------------------------------------+
| Id   | User            | Host                | db      | Command | Time  | State                       | Info                                     |
+------+-----------------+---------------------+---------+---------+-------+-----------------------------+------------------------------------------+
|    1 | event_scheduler | localhost           | NULL    | Daemon  |    21 | Waiting for next activation | NULL                                     |
| 3167 | dba             | 192.168.6.20:56723  | example | Query   |    13 | copy to tmp table           | ALTER TABLE `mytable`	ENGINE=InnoDB   |
| 3172 | dba             | localhost           | example | Query   |     0 | NULL                        | show processlist                         |
+------+-----------------+---------------------+---------+---------+-------+-----------------------------+------------------------------------------+
18 rows in set (0.00 sec)
		
		

此时我们查看mysql data目录会看到临时表文件

		
# ll /var/lib/mysql/hx9999_real_history/
		
-rw-rw---- 1 mysql mysql      9522 May 16 17:17 #sql-c2f_c5f.frm
-rw-rw---- 1 mysql mysql        48 May 16 17:17 #sql-c2f_c5f.par
-rw-rw---- 1 mysql mysql 637534208 May 16 17:29 #sql-c2f_c5f#P#p0.ibd
-rw-rw---- 1 mysql mysql    180224 May 16 17:17 #sql-c2f_c5f#P#p1.ibd
-rw-rw---- 1 mysql mysql    180224 May 16 17:17 #sql-c2f_c5f#P#p2.ibd
-rw-rw---- 1 mysql mysql    180224 May 16 17:17 #sql-c2f_c5f#P#p3.ibd
-rw-rw---- 1 mysql mysql    180224 May 16 17:17 #sql-c2f_c5f#P#p4.ibd
-rw-rw---- 1 mysql mysql    180224 May 16 17:17 #sql-c2f_c5f#P#p5.ibd
-rw-rw---- 1 mysql mysql    180224 May 16 17:17 #sql-c2f_c5f#P#p6.ibd
-rw-rw---- 1 mysql mysql    180224 May 16 17:17 #sql-c2f_c5f#P#p7.ibd