MySQl在開發和生產不同環境下的索引對比

--1.建立索引資訊表
create table `t_index_update` (
  `table_name` varchar(20) COLLATE gbk_bin DEFAULT NULL,
  `index_name` varchar(20) COLLATE gbk_bin, DEULTTor
  `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_sche.STATIST
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,
    `') order by seq_in_index asc separator ', ') as index_cols
     from information_schema.STATISTICS
     where  table_schema= ” _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_ ;
--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,
    by seq_in_index asc separator ', ') as index_cols
     from information_schema.STATISTICS
     where  table_schema= 'elk'
&lt_lt  and coop; gt;'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在開發和生產不同環境下的索引對比的詳細內容。更多資訊請關注PHP中文網其他相關文章！