MySql, how can I export indexes from my developmen

问题:

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.

回答1:

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!

回答2:

First, read the tutorial here about how-to Export MySQL Indexes using a SQL query. Further:

1. If you do complete DUMP of your database and IMPORT it to another (using PHPMyAdmin, etc), the indexes will get regenerated.

2. 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.

回答3:

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.

回答4:

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.

source : 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;

回答5:

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.

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.

回答6:

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+.

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:

add a header row -> delete all columns but leave "database_name table_name index column value"

Then, filter by database.

Copy all from database1 to a notepad+ screen 1, filter excel database2, copy all to notepad+ screen 2 -> COMPARE!