用INFORMATION_SCHEMA逻辑MySQL的索引

分库分表的场景下，变更目前还不知道有哪个表变更索引失败，是不是所有的表都变更成功了，所以可以从INFORMATION_SCHEMA通过罗列索引个数，或者查看索引行，就可以知道是不是所有的都变更成功了：

SELECT table_name AS `Table`,

       index_name AS `Index`,

       GROUP_CONCAT(column_name ORDER BY seq_in_index) AS `Columns`

FROM information_schema.statistics

WHERE table_schema = 'sakila'

GROUP BY 1,2;

也可以执行：

SELECT t.name AS `Table`,

       i.name AS `Index`,

       GROUP_CONCAT(f.name ORDER BY f.pos) AS `Columns`

FROM information_schema.innodb_sys_tables t

JOIN information_schema.innodb_sys_indexes i USING (table_id)

JOIN information_schema.innodb_sys_fields f USING (index_id)

WHERE t.schema = 'sakila'

GROUP BY 1,2;

参考资料：

http://blog.9minutesnooze.com/mysql-information-schema-indexes/

巴特西

用INFORMATION_SCHEMA逻辑MySQL的索引

最新文章

热门文章