首页 > 数据库技术 > 详细

MySQl开发和生产环境索引对比

时间:2017-07-13 09:38:13      阅读:282      评论:0      收藏:0      [点我收藏+]

--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;




MySQl开发和生产环境索引对比

原文:http://www.cnblogs.com/dbaljc/p/7158589.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!