mysql两大索引_MySQl两个环境的索引对比

--1.创建索引信息表

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 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.同步线下索引信息表到线上

--4.构建删除和修改过的索引的删除语句

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 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.构建新加索引的的新加语句

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;