MySql，如何将索引从开发数据库导出到生产数据库？

Question

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

然而，令我惊讶的是，我找不到将索引导出到生产数据库的方法。

我认为有一个简单的方法可以做到这一点。我不想替换生产数据库中的数据。

主要问题是我看不到索引中的排序，因此甚至手动执行排序也很困难。

Answer 1

也许您的意思是“如何在我的（现有）实时数据库上重新创建我的开发索引”？

如果是这样，我认为您正在寻找的 SQL 命令是；

显示创建表 {表名};

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

ALTER TABLE DROP INDEX {index_name}

您可以从“SHOW CREATE TABLE”输出中复制“KEY”和“CONSTRAINT”行，并将其放回“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;

希望这有帮助！

Answer 2

扩展@origo 的答案。有一种情况我需要提取一堆索引的 DDL。这个脚本完成了这项工作。

来源：https://rogerpadilla.wordpress.com/2008/12/ 02/mysql-export-indexes/

SELECT
CONCAT(
'ALTER TABLE ' ,
TABLE_NAME,
' ',
'ADD ',
IF(NON_UNIQUE = 1,
CASE UPPER(INDEX_TYPE)
WHEN 'FULLTEXT' THEN 'FULLTEXT INDEX'
WHEN 'SPATIAL' THEN 'SPATIAL INDEX'
ELSE CONCAT('INDEX ',
INDEX_NAME,
' USING ',
INDEX_TYPE
)
END,
IF(UPPER(INDEX_NAME) = 'PRIMARY',
CONCAT('PRIMARY KEY USING ',
INDEX_TYPE
),
CONCAT('UNIQUE INDEX ',
INDEX_NAME,
' USING ',
INDEX_TYPE
)
)
),
'(',
GROUP_CONCAT(
DISTINCT
CONCAT('', COLUMN_NAME, '')
ORDER BY SEQ_IN_INDEX ASC
SEPARATOR ', '
),
');'
) AS 'Show_Add_Indexes'
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'PLEASE CHANGE HERE'
GROUP BY TABLE_NAME, INDEX_NAME
ORDER BY TABLE_NAME ASC, INDEX_NAME ASC;

Answer 3

首先，阅读此处有关如何使用导出 MySQL 索引的教程SQL 查询。此外：

1. （使用 PHPMyAdmin 等），索引将重新生成。

2. 如果可能，您可以将整个 MySQL 数据库文件夹的内容复制到生产数据库。这也能很快达到目的。 在 MySQL 文档中了解更多信息。

Answer 4

您可以使用以下命令进行转储

mysqldump -u [USERNAME] -p [DBNAME] | gzip > [/path_to_file/DBNAME].sql.gz

，索引将自动复制。

Answer 5

我相信您正在尝试导出索引本身，而不仅仅是在生产中重新生成它们的代码，对吧？ （我之所以这样假设，是因为在大多数生产环境中生成这些索引的负载并不理想。）

如果性能不是您主要关心的问题，则 mysqldump 实用程序非常有用，而且我一直使用它。不过，如果您正在寻找一种非常快速的方法，我建议将实际的 InnoDB 文件从一个冷数据库复制到另一个数据库（假设它们是完全相同的 MySQL 版本，具有完全相同的配置和完全相同的预期）行为等）。如果系统之间存在任何差异，则此方法是危险的。

听起来，在您的情况下，您可能希望首先将良好的数据复制到测试环境中。我的开发周期通常遵循这种方法：DDL 通过编程从测试流向生产，DML 通过系统的实际使用从生产流向测试。

Answer 6

我还有具有相同数据库结构的开发和生产服务器。

我修改了它们的索引，所以我想将它们合并在一起。所以我需要用Notepad+来比较数据。

以下是如何导出所有表、所有数据库的索引以及如何过滤和比较它们：

--- Single table:
    SHOW INDEX FROM mydb.mytable;
    SHOW INDEX FROM mytable;

--- Multi tables, databases:
    USE information_schema;
    SELECT * FROM `statistics` ORDER BY TABLE_SCHEMA ASC, TABLE_NAME ASC, INDEX_NAME ASC;

现在，phpMyAdmin ->导出到 CSV-Excel：

添加标题行 ->删除所有列，但保留“database_name table_name 索引列值”

然后，按数据库过滤。

将数据库1中的所有内容复制到记事本+屏幕1，过滤excel数据库2，将所有内容复制到记事本+屏幕2 - >比较！

Answer 7

对于多个索引，我更喜欢每个表一个查询。
但不适用于具有多个列的组合索引。
不管怎样，试一试吧。

SELECT 
CONCAT
(concat("ALTER TABLE ",trim(TABLE_NAME)),
group_concat(
IF(INDEX_NAME="PRIMARY", 
concat(" ADD PRIMARY KEY (`", column_name,"`)"),
concat(" ADD INDEX `",INDEX_NAME,"` (`",column_name,"` ASC) ")
)),";") 
AS CMD
FROM information_schema.STATISTICS 
WHERE TABLE_SCHEMA='your_schema'  
GROUP BY TABLE_NAME;