mysql 导出索引_MySql，如何将索引从我的开发数据库导出到我的生产数据库？

I've been working on my development database and have tweaked its performance.

However, to my surprise I can't find a way to export the indexes to my production database.

I thought there would be an easy way to do this.

The main problem is that I can't see sorting in the indexes so its going to be difficult to even do it manually.

EDIT -

I don't want to replace the data in my production database.

解决方案

Perhaps you mean "How do I re-create my development indexes on my (existing) live database"?

If so, I think the SQL commands you're looking for are;

SHOW CREATE TABLE {tablename};

ALTER TABLE ADD INDEX {index_name} (col1, col2)

ALTER TABLE DROP INDEX {index_name}

You can copy the "KEY" and "CONSTRAINT" rows from "SHOW CREATE TABLE" output and put it back in the "ALTER TABLE ADD INDEX".

dev mysql> SHOW CREATE TABLE city;

CREATE TABLE `city` (

`id` smallint(4) unsigned NOT NULL auto_increment,

`city` varchar(50) character set utf8 collate utf8_bin NOT NULL default '',

`region_id` smallint(4) unsigned NOT NULL default '0',

PRIMARY KEY (`id`),

KEY `region_idx` (region_id),

CONSTRAINT `city_ibfk_1` FOREIGN KEY (`region_id`) REFERENCES `region` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT

) ENGINE=InnoDB;

live mysql> SHOW CREATE TABLE city;

CREATE TABLE `city` (

`id` smallint(4) unsigned NOT NULL auto_increment,

`city` varchar(50) character set utf8 collate utf8_bin NOT NULL default '',

`region_id` smallint(4) unsigned NOT NULL default '0',

PRIMARY KEY (`id`)

) ENGINE=InnoDB;

live mysql> ALTER TABLE `city` ADD KEY `region_idx` (region_id);

live mysql> ALTER TABLE `city` ADD CONSTRAINT `city_ibfk_1` FOREIGN KEY (`region_id`) REFERENCES `region` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT;

Hope this helps!