MySql，如何将索引从我的开发数据库导出到我的生产数据库？ [英] MySql, how can I export indexes from my development database to my production database?

问题描述

我一直在研究我的开发数据库，​​并调整了其性能。

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.

编辑 -

我不想替换我的生产数据库中的数据。

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"?

如果是这样，我想你要找的SQL命令是;

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

SHOW CREATE TABLE {tablename};

SHOW CREATE TABLE {tablename};

ALTER TABLE ADD INDEX {index_name}（col1，col2）

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

ALTER TABLE DROP INDEX {index_name}

ALTER TABLE DROP INDEX {index_name}

您可以从SHOW CREATE TABLE输出复制KEY和CONSTRAINT行并把它k在ALTER TABLE ADD INDEX中。

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;

希望这有帮助！

这篇关于MySql，如何将索引从我的开发数据库导出到我的生产数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！