MySQL创建类似于引擎覆盖的表

I am in the process of breaking up a wordpress site with one database and 81,000+ tables into a multi-DB using a plugin by wpmudev.org. The main motivator is performance and I checked the tables itself and they are of type MyISAM.

我正在用wpmudev.org上的一个插件把一个wordpress站点和81,000多个表分解成一个多数据库。主要的动机是绩效，我检查了表格本身，它们是MyISAM类型的。

I was thinking that while moving the tables into new databases I could also change the table type to InnoDB which should see a site performance improvement.

我在想，在将表移动到新的数据库时，我还可以将表类型更改为InnoDB，这会提高站点的性能。

The script to migrate tables from the existing db to the new db uses the following syntax:

将表从现有数据库迁移到新数据库的脚本使用以下语法:

CREATE TABLE .... LIKE .... INSERT INTO .... SELECT * FROM ....

创建表....像....插入....SELECT * FROM ....

I could not locate information if I could oveerride the engine in the CREATE TABLE command such as:

如果我可以在CREATE TABLE命令(例如:)中使用引擎，我就无法找到信息。

CREATE TABLE .... LIKE .... ENGINE=InnoDB

创建表....像....引擎= InnoDB

Also I am wondering if "INSERT INTO .... SELECT * FROM ...." is the most efficient way to insert the data... This is a php script so I don't mind adding a little code to improve the performance which - at an earlier trial took 3 days to run on a 4-core 4GB RAM!

我想知道“插入....SELECT * FROM ....”是最有效的方式插入数据……这是一个php脚本，所以我不介意添加一些代码来提高性能——在早期的测试中，在4核4GB内存上运行需要3天!

3 个解决方案

#1

Yes, I've done just this (override the table type). Works fine.

是的，我已经这样做了(重写表类型)。工作很好。

For big transfers like this I've generally done some form of database dump, a script to massage the data (e.g. change engine types), then a restore to the new database. The text based database dumps mostly use COPY which is faster than INSERT INTO.

对于像这样的大传输，我通常会做一些形式的数据库转储，一个用于处理数据的脚本(例如更改引擎类型)，然后恢复到新的数据库。基于文本的数据库转储大多使用复制，复制比插入要快。

You can also issue ALTER TABLE {} ENGINE=INNODB;. Though that said a fresh start has a lot going for it also.

您还可以发出ALTER TABLE {} ENGINE=INNODB;。尽管这意味着一个新的开始也有很多好处。

81,000 tables. Wow.

81000表。哇。

#2

This question was also answered indirectly by another question, which helped me as I was trying to clone a table into the MEMORY engine: how-to-copy-mysql-table-structure-to-table-in-memory

这个问题也被另一个问题间接地回答了，这个问题帮助了我，因为我正在尝试将一个表克隆到内存引擎中:如何复制-mysql-table-structure-to-table-in- MEMORY

CREATE TABLE % ENGINE=InnoDB SELECT * FROM %

Hope this helps someone as it helped me!

希望这对我有帮助!

#3

You can't specify ENGINE parametr with create table like... syntax, but here are some alternatives:

不能用create这样的表指定ENGINE parametr。语法，但这里有一些替代方法:

CREATE TABLE `another_table` ENGINE=MEMORY SELECT * FROM `original`

... will copy table structure and data, without keys and trigers

…是否将复制表结构和数据，没有键和trigers

CREATE TABLE `another_table` ENGINE=MEMORY SELECT * FROM `original` WHERE 0

... will copy just structure, but no data (because WHERE 0 will filter all out)

…将只复制结构，但不复制数据(因为0将过滤所有数据)

CREATE TEMPORARY TABLE `another_table` ) ENGINE=MEMORY SELECT * FROM `original`

... maybe, you would like to add TEMPORARY word, then DB would automatically remove the table when you close the connection

…也许您想添加临时单词，那么当您关闭连接时，DB将自动删除表

CREATE TABLE `another_table` ( INDEX(`id_column`) ) ENGINE=MEMORY SELECT * FROM `original`

... and this is an example on how to recreate the keys in new table (syntax of defining keys as the same as in simple CREATE TABLE syntax)

…这是一个如何在新表中重新创建键的示例(定义键的语法与简单的CREATE table语法相同)

#1