用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/ 

转载于:https://www.cnblogs.com/sunss/p/6606655.html