MySQl Development and Production Environment Index comparison, mysql Development Environment Index

MySQl Development and Production Environment Index comparison, mysql Development Environment Index

-- 1. Create an index information table
Create table 't_ index_update '(
'Table _ name' varchar (20) COLLATE gbk_bin default null,
'Index _ name' varchar (20) COLLATE gbk_bin default null,
'Index _ cols' varchar (100) COLLATE gbk_bin DEFAULT NULL
) ENGINE = InnoDB default charset = gbk COLLATE = gbk_bin;
-- 2. Insert the offline 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 online
-- 4. Create deletion 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
)
Left join 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. Create a new addition statement for the Newly Added Index
Select concat ('alter table'', a. table_name, ''add Index', a. index_name, '(', a. index_cols ,');')
From t_index_update
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;