MySql，如何将索引从我的开发数据库导出到我的生产数据库？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4744287/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySql, how can I export indexes from my development database to my production database?
提问by Jules
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. I don't want to replace the data in my production database.
我认为会有一种简单的方法来做到这一点。我不想替换生产数据库中的数据。
The main problem is that I can't see sorting in the indexes so its going to be difficult to even do it manually.
主要问题是我看不到索引中的排序，因此即使手动进行排序也很困难。
回答by origo
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;
如果是这样，我认为您正在寻找的 SQL 命令是；
SHOW CREATE TABLE {tablename};
显示创建表 {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}
You can copy the "KEY" and "CONSTRAINT" rows from "SHOW CREATE TABLE" output and put it back in the "ALTER TABLE ADD INDEX".
您可以从“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;
Hope this helps!
希望这可以帮助！
回答by shamittomar
First, read the tutorial here about how-to Export MySQL Indexes using a SQL query. Further:
首先，阅读此处的教程，了解如何使用 SQL 查询导出 MySQL 索引。更远：
If you do complete DUMP of your database and IMPORT it to another (using PHPMyAdmin, etc), the indexes will get regenerated.
If possible, you can copy contents of your entire MySQL database folder to the production database. This will do the trick too, quickly. Read more here at MySQL docs.
如果您完成了数据库的转储并将其导入另一个（使用 PHPMyAdmin 等），索引将重新生成。
如果可能，您可以将整个 MySQL 数据库文件夹的内容复制到生产数据库。这也能很快解决问题。在 MySQL 文档中阅读更多信息。
回答by viggy28
Extending on @origo's answer. There is a case where i needed to extract the DDL for a bunch of indexes. This script does the job.
扩展@origo的回答。有一种情况，我需要为一堆索引提取 DDL。这个脚本完成了这项工作。
source : https://rogerpadilla.wordpress.com/2008/12/02/mysql-export-indexes/
来源：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;
回答by UVM
you can use the following command to take a dump
您可以使用以下命令进行转储
mysqldump -u [USERNAME] -p [DBNAME] | gzip > [/path_to_file/DBNAME].sql.gz
and indexes will be copied automatically.
和索引将被自动复制。
回答by Kevin Durette
I believe you're trying to export the indexes themselves and not just the code to regenerate them in production, right? (I'm assuming this because the load of generating these indexes is not favorable in most production environments.)
我相信您正在尝试导出索引本身，而不仅仅是在生产中重新生成它们的代码，对吗？（我这样假设是因为在大多数生产环境中生成这些索引的负载并不理想。）
The mysqldump utility is useful if performance isn't your main concern, and I use it all the time. If you're looking for a very fast method, though, I would suggest copying the actual InnoDB files from one cold database to the other (assuming they're exactly the same MySQL version with the exactly the same configuration and the exactly the same expected behavior, etc). This method is dangerous if there any differences between the systems.
如果性能不是您主要关心的问题，mysqldump 实用程序很有用，我一直在使用它。但是，如果您正在寻找一种非常快速的方法，我建议将实际的 InnoDB 文件从一个冷数据库复制到另一个（假设它们是完全相同的 MySQL 版本，具有完全相同的配置和完全相同的预期行为等）。如果系统之间存在任何差异，则此方法是危险的。
It sounds like, in your situation, you might want to copy your good data to your testing environment first. My development cycle typically follows this approach: DDL flows from testing to production via programming, and DML flows from production to testing via actual use of the system.
听起来，在您的情况下，您可能希望先将好的数据复制到测试环境中。我的开发周期通常遵循这种方法：DDL 通过编程从测试流向生产，而 DML 通过系统的实际使用从生产流向测试。
回答by Tarik
I also have development and production servers with the same database structure.
我还有具有相同数据库结构的开发和生产服务器。
I modified indexes on both of them so I wanted to merge all together. So I needed to compare data with Notepad+.
我修改了它们的索引，所以我想将它们合并在一起。所以我需要用 Notepad+ 比较数据。
Here is how you export indexes for all tables, all databases and how to filter and compare them:
以下是导出所有表、所有数据库的索引以及如何过滤和比较它们的方法：
--- 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;
Now, phpMyAdmin -> Export to CSV-Excel:
现在，phpMyAdmin -> 导出到 CSV-Excel：
add a header row -> delete all columns but leave "database_name table_name index column value"
添加标题行-> 删除所有列但保留“database_name table_name 索引列值”
Then, filter by database.
然后，按数据库过滤。
Copy all from database1 to a notepad+ screen 1, filter excel database2, copy all to notepad+ screen 2 -> COMPARE!
将所有从数据库 1 复制到记事本 + 屏幕 1，过滤 excel 数据库 2，将所有复制到记事本 + 屏幕 2 -> 比较！
