Comparison of MySQl indexes in different development and production environments

--1. Create 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 into 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 one
--4. Construct the deletion statement 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 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. Construct a new statement for adding a new index
select concat('alter table `',a.table_name,'' add index ',a.index_name,'(',a.index_cols , ');') ##, t_index_update a
LEFT JOIN (
SELECT
TABLE_NAME,
Index_name,
Group_Concat (Distant Concat ('`', colorn_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;

The above is the detailed content of Comparison of MySQl indexes in different development and production environments. For more information, please follow other related articles on the PHP Chinese website!