MySQL development and production environment index comparison

--1. Creating an index information table
CREATE TABLE ' T_index_update ' (
' table_name ' varchar (COLLATE) Gbk_bin DEFAULT NULL,
' index_name ' varchar (COLLATE) Gbk_bin DEFAULT NULL,
' Index_cols ' varchar (+) COLLATE gbk_bin DEFAULT NULL
) Engine=innodb DEFAULT CHARSET=GBK Collate=gbk_bin;
--2. Inserting a line down index information table
Insert into T_index_update ()
Select
TABLE_NAME,
Index_name,
Group_concat (Distinct concat ("', column_name, '") Order by seq_in_index ASC separator ', ') as Index_cols
From INFORMATION_SCHEMA. STATISTICS
where table_schema= ' elk '
and column_name<> ' seq_id '
and index_name<> ' primary '
GROUP BY TABLE_NAME, index_name
ORDER BY table_name ASC, index_name ASC;
--3. Synchronize the offline index information table to the line
--4. Building DELETE statements for deleted and modified indexes
Select Concat (' ALTER TABLE ', A.table_name, ' Drop index ', a.index_name, '; ')
From
(
Select
TABLE_NAME,
Index_name,
Group_concat (Distinct concat ("', column_name, '") Order by seq_in_index ASC separator ', ') as Index_cols
From INFORMATION_SCHEMA. STATISTICS
where table_schema= ' elk '
and column_name<> ' seq_id '
and index_name<> ' primary '
GROUP BY TABLE_NAME, index_name
) A
Left joins T_index_update B on B.table_name and B.index_name=a.index_name and B.index_cols=a.index_cols
where b.index_name is null;
--5. Constructing new add-on statements with new indexes
Select Concat (' ALTER TABLE ', A.table_name, ' Add index ', A.index_name, ' (', A.index_cols, '); ')
From T_index_update A
Left JOIN (
Select
TABLE_NAME,
Index_name,
Group_concat (Distinct concat ("', column_name, '") Order by seq_in_index ASC separator ', ') as Index_cols
From INFORMATION_SCHEMA. STATISTICS
where table_schema= ' elk '
and column_name<> ' seq_id '
and index_name<> ' primary '
GROUP BY TABLE_NAME, index_name
) b on B.table_name and B.index_name=a.index_name and B.index_cols=a.index_cols
where b.index_name is null;

MySQL development and production environment index comparison