Mysql 索引迁移策略
Mysql 索引迁移策略
近日在核查项目中的一些慢sql时发现一个很鸡仔儿的问题，本地开发库表中索引跟生产上差距很大，又因为生产库登录各种麻烦，需要各种验证码，那么多的慢sql分给好些个人，不可能让大家一人登一次数据库，所以就有了这个索引迁移的问题，其实就是把生产的库表的索引同步到本地库。针对这一问题，经过查阅各种资料得出以下的解决策略。
1.索引导出
第一步就是将生产库中的索引导出来，这里的导出的索引不应该包含主键约束，任何一张表在创建时按照规定都是会加主键的，所以这里没有必要。以下就是导出mysql指定数据库除主键的所有索引的sql。
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 'all table index'
FROM
information_schema.STATISTICS
WHERE
TABLE_SCHEMA = 'test'
AND UPPER(INDEX_NAME) != 'PRIMARY'
GROUP BY
TABLE_NAME,
INDEX_NAME
ORDER BY
TABLE_NAME ASC,
INDEX_NAME ASC
注意：复制sql时记得更改数据库名 以上标红的 test 是我本地库。
2.索引删除
导出生产索引后，将脚本保存，接下来就要执行本地库的索引删除了，这里的删除索引也需要先构建出删除索引的alter语句。
SELECT
CONCAT(
'ALTER TABLE `',
TABLE_NAME,
'` ',
GROUP_CONCAT(
DISTINCT CONCAT(
'DROP ',
IF (
UPPER(INDEX_NAME) = 'PRIMARY',
'PRIMARY KEY',
CONCAT('INDEX `', INDEX_NAME, '`')
)
) SEPARATOR ', '
),
';'
)
FROM
information_schema.STATISTICS
WHERE
TABLE_SCHEMA = 'test'
and UPPER(INDEX_NAME) != 'PRIMARY'
GROUP BY
TABLE_NAME
ORDER BY
TABLE_NAME ASC
以上sql是删除表中不包含主键的索引
注意数据库的名称一定不要搞反了。
3.索引插入
先执行第二步的得到的脚本，再执行第一步的脚本。
