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.索引插入
先执行第二步的得到的脚本,再执行第一步的脚本。