written by : 小旋风

1：过滤database中所有table的重复的索引
前奏：记得小编刚入职时，就摊上事了，审核sql,其中有审核库中重复的索引及索引命名规范，公司没有自动化审核工具，n张表让小编情何以堪，虽说男人男人哭吧哭吧不是罪。
  果不其然，审核不多会，两眼果断直了，烦躁的一米。转念一想，小编也是DBA开发出身的，多大点事，自动化sql搞起...... 经过炼狱般的大脑风暴，耳边想起悦耳的节奏，搞定

过滤重复的索引思路：
1：找到一个索引的所有子索引。   比如一个索引的索引字段为（a,b,c）对应的子索引（a,b）(a)
2：确定当下索引是直接索引还是间接索引。比如上面（a,b,c）是直接索引，（a,b）(a) 是间接索引
  3：分组过滤，组成员个数大于等于2且组中必须有一个直接索引。

具体的sql:

点击(此处)折叠或打开

select table_name 表名,group_concat(index_name separator ' ; ') 重复索引列表 ,count(*)-1 重复次数,idx_name 索引列 from
(
select a.table_name ,a.index_name,a.column_name ,a.seq_in_index,a.idx_name,if(b.max_seq_index=a.seq_in_index,1,0)max_seq_index from
(
select table_name ,index_name,column_name ,seq_in_index
,if(index_name=@befo_index_name,@_column_names:=concat(@_column_names ,',',column_name),@_column_names:=column_name)idx_name
,@befo_index_name:=index_name
from information_schema.statistics ,(select @befo_index_name:='',@_column_names:='')b
where table_schema=database_name
order by table_name ,index_name ,seq_in_index
)a ,
(
select table_name ,index_name,max(seq_in_index) max_seq_index
from information_schema.statistics
where table_schema=database_name
group by table_name ,index_name
) b
where a.table_name =b.table_name and a.index_name=b.index_name
) c
group by table_name,idx_name
having count(*)>1 and sum(max_seq_index)>=1;

说明：database_name ：指定你要检查的database
执行结果：

2：在线清除大表数据
前奏：小编自从上次自动化搞定安逸了好久，然有一天zabbix 报zabbix server 磁盘空间不足，经过排查是zabbix 的history_uint 20G 导致的，当然了磁盘存储也不给力。
只见BOSS 雷厉风行干掉上过月的数据，那就动起来呗。
     需求：在线批量删除大表大量数据
    分析：删除大量数据会走全表扫描，会长时间占用数据库主机大量的 IO ,CUP ,内存。  事必会长时间影响线上的应用的响应时间。甚至是灾难性的。

   结论：采用的策略： “化整为零，各个击破” ，
  具体实现：每间1秒隔删除固定行数比如1W 条记录，往复循环，直至把需要数据都清除掉
  优点：
1: 每隔一秒删除给予线上应用的喘息的机会，对前端应用影响较小。
2: 要是一条删除sql搞定，执行到一半失败了，光回滚的时间及给系统再次负载够你喝一壶的，最最不爽的是
  执行这么长时间，数据还是没有被删除想想就都心碎了。
3：可以查看删除记录数的进度。
类比：同样在线批量修改同理
存储过程实现：

点击(此处)折叠或打开

CREATE PROCEDURE `p_batch_delete`()
BEGIN
set @start_date=now() ;
set @num=0;
-- 往复干掉满足条件的1W行数据
lab:while true do
delete from history_uint where clock <UNIX_TIMESTAMP('2015-10-26') limit 10000;
-- 记录删除的影响的行数时间复杂度为1
set @count=row_count();
-- 数据已被全部干掉，则退出循环体
if @count=0 then
leave lab;
else
-- 累加删除记录数
set @num=@num+@count;
-- 每干掉200000记录，记录一条数据，便于后续查看删除进度
if mod(@num,200000)=0 then
insert into delete_progresse (records,time)values( @num,round((unix_timestamp(now())-unix_timestamp(@start_date))/1));
end if;
-- 每干掉1W条记录停顿1S 给予前端应用喘息的机会
set @s=sleep(1) ;
end if;
end while ;
-- 满足条件的数据被全部干掉后，统计删除行数，开始时间，结束时间
select @num delete_row ,@start_date start_date, now() end_date ;
END;

3：实现自动化排班功能
前奏：小编有一段时间在搞php开发，终于找回了久违的兴奋感。有天一妹子在群里发值班信息，说实在的那个排班表格，设计的那个完美，完美到想让它实现自动化
还有妹子每次排班各种复制黏贴，想想都醉了，为什么不能让繁琐枯燥的事情变成愉悦的事情呢？再着搞IT的妹子本来就少，必须悉心呵护，小编果断上演一次英雄救美，好吧是我想多了。下面分享下数据逻辑加工部分

排班表格样式：

组别	星期一	星期二	星期三	星期四	星期五	星期六	星期天
oracle		1李二,李三	2李五	3李七	4李八	5李九	6李十
mysql		1王五	2王六	3王一	4王二	5王三	6王四
oracle	7李十一	8李十二,李十三	9李一	10李二	11李三	12李五	13李七
mysql	7王五	8王六	9王一	10王二	11王三	12王四	13王五
oracle	14李八	15李九,李十	16李十一	17李十二	18李十三	19李一	20李二
mysql	14王六	15王一	16王二	17王三	18王四	19王五	20王六
oracle	21李三	22李五,李七	23李八	24李九	25李十	26李十一	27李十二
mysql	21王一	22王二	23王三	24王四	25王五	26王六	27王一
oracle	28李十三	29李一,李二	30李三	31李五
mysql	28王二	29王三	30王四	31王五

心得体会：
1：一口吃不成胖子，怎么办？逆向思维拆分；正向思维验证
2：寻找规律，怎么着？观察；模仿人的行为，抽象出规律。
3：不要为了解决一个问题而解决问题，试着总结出解决问题的统一方法论。

分析：通过观察其实这个是行列转换一个典型的例子。
逆向思维拆分：
  1：每一行数据都有2个相同的属性：组别，一年多少个星期。【观察得出规律】
   2：展示结果是由值班信息表（组别，值班人，值班日期）根据组别，一年多少个星期分组得来的
  3：怎样得到值班信息表？经分析是有日期表和值班设置表（排班根据值班人值班顺序排的，当然也可以设置请假时间区间）
  4：怎样得到日期表？一年365天存储到表里？2年呢？n年呢？这样要更新日期表，使用存储0-30一共31个数字数值表生成任意年月的日期，一劳永逸。
正向思维验证：
1：数值表----》日期表 + 排班设置表----》值班信息表表----》结果
结论：思路正确

过程主要代码；根据某月第一天生成当月排班数据

点击(此处)折叠或打开

-- 动态获取 oracle 和 mysql 组更有多少个成员
select sum(if(category=1,1,0)) , sum(if(category=2,1,0)) into oracle_count,mysql_count from duty_order ;
-- 保存oracle组当前月的值班数据
insert into tmp_duty_info(category,date,member)
select 1,c.date,group_concat(d.member order by c.date) member from
(
select a.date , if(@duty_order=oracle_count,@duty_order:=f_get_duty_order(date,1,1,oracle_count),@duty_order:=f_get_duty_order(date,@duty_order+1,1,oracle_count)) duty_order
from
( select DATE_ADD( datetime,INTERVAL num day) date from number
where num<datediff(DATE_ADD( datetime,INTERVAL 1 month),datetime)
union all
select date
from (select DATE_ADD( datetime,INTERVAL num day) date from number
where num<datediff(DATE_ADD( datetime,INTERVAL 1 month),datetime) )a
where dayofweek(date)=3
order by date )a,
( select @duty_order:=b.duty_order duty_order from duty_info a ,duty_order b
where a.member=b.member and a.date=DATE_ADD(datetime,INTERVAL -1 day) and a.category=1) b
)c join duty_order d on c.duty_order=d.duty_order and d.category=1
group by date ;
-- 保存mysql组当前月的值班数据
insert into tmp_duty_info(category,date,member)
select 2,c.date,d.member from
(
select a.date , if(@duty_order=mysql_count,@duty_order:=f_get_duty_order(date,1,2,mysql_count),@duty_order:=f_get_duty_order(date,@duty_order+1,2,mysql_count)) duty_order
from
( select DATE_ADD( datetime,INTERVAL num day) date from number
where num<datediff(DATE_ADD( datetime,INTERVAL 1 month),datetime)
order by date )a,
( select @duty_order:=b.duty_order duty_order from duty_info a ,duty_order b
where a.member=b.member and a.date=DATE_ADD(datetime,INTERVAL -1 day) and a.category=2) b
)c join duty_order d on c.duty_order=d.duty_order and d.category=2;
-- 根据2组的当前月排班数据，加工数据
select
if(category=1,'oracle','mysql') cate ,
max(if(dayofweek(date)=2,concat(dayofmonth(date),member),'')) 星期一,
max(if(dayofweek(date)=3,concat(dayofmonth(date),member),'')) 星期二,
max(if(dayofweek(date)=4,concat(dayofmonth(date),member),'')) 星期三,
max(if(dayofweek(date)=5,concat(dayofmonth(date),member),'')) 星期四,
max(if(dayofweek(date)=6,concat(dayofmonth(date),member),'')) 星期五,
max(if(dayofweek(date)=7,concat(dayofmonth(date),member),'')) 星期六,
max(if(dayofweek(date)=1,concat(dayofmonth(date),member),'')) 星期天
from tmp_duty_info
group by weekofyear(date), category
order by date,weekofyear(date), category ;

处理值班人员请假的函数：模仿人为排班得出规律

点击(此处)折叠或打开

BEGIN
declare duty_num int ;
if cate=1 then
select duty_order into duty_num
from ( select a.*, if(duty_order>=duty,duty_order,duty_order+cate_count) dx from duty_order a
where category=1 order by duty_order
)b
where (if(start_date!=''and end_date='','',start_date) = '' and if(end_date!=''and start_date='','',end_date) = '' )
or date>end_date or date<start_date order by dx limit 1 ;
else
select duty_order into duty_num
from ( select a.*, if(duty_order>=duty,duty_order,duty_order+cate_count) dx from duty_order a
where category=2 order by duty_order
)b
where (if(start_date!=''and end_date='','',start_date) = '' and if(end_date!=''and start_date='','',end_date) = '' )
or date>end_date or date<start_date order by dx limit 1 ;
end if;
RETURN duty_num;
END

4：n天之前，一个开发的哥们，向小编请教个问题，说是苦思良久，没有好的思路。暗自窃喜，嘿嘿，又要干一票了，不废话了，上干货，莱迪 GO

需要：统计当前月份未签到的人员名单。
已知： user (user_name) 用户表
sign 签到表 (user_name,date)：只存储每天签到人员信息

心得体会
1、模仿人的行为，抽象出设计思路
2、基础知识和重要，可以灵光乍现出代码具体实现

解析：有时拿到一个需求时，没有好的思路，就不要天马行空的想了，换个角度，如果这个需求是人来做，又该如何？
当人的事情想明白了，思路也就水到渠成了。比如这道题，要是人工来判断11月份每天没签到的人，咋整？首先拿1号所有签到的人员名单和
所有用户名单比对得出1号未签到的人员名单，2号...以此类推得出结果。此时想到通过外连接再过滤可以得到匹配不到的记录。瞬间思路有了，代码具体实现也有了。

思路：1：当前月份所有人员的全勤表a 和签到表 b 外连接过滤 -----》当前月没签到的人员
2：表 a 怎么获得？当前月所有日期表c和用户表笛卡尔积
3：表c 又如何获得？数值表搞起

下面是个测试案例：

用户表：
mysql> select * from user;
+----+-----------+
| id | user_name |
+----+-----------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
+----+-----------+
3 rows in set

签到表：
mysql> select * from qiandao;
+-----------+---------------------+
| user_name | date |
+-----------+---------------------+
| 张三 | 2015-10-14 00:00:00 |
| 王五 | 2015-10-15 00:00:00 |
| 李四 | 2015-10-14 00:00:00 |
+-----------+---------------------+
3 rows in set

数值表获取日期：

mysql> select date_add( date_format('20151014','%Y%m%d') , INTERVAL id day) date,id from num ;
+------------+----+
| date | id |
+------------+----+
| 2015-10-14 | 0 |
| 2015-10-15 | 1 |
| 2015-10-16 | 2 |
| 2015-10-17 | 3 |
| 2015-10-18 | 4 |
| 2015-10-19 | 5 |
| 2015-10-20 | 6 |
+------------+----+
7 rows in set

获取14、15 号没有签单的人员名单：

点击(此处)折叠或打开

select b.date 日期 ,group_concat(b.user_name) 未签到人员
from qiandao a right join
(select a.user_name,b.date
from user a ,(select date_add( date_format('20151014','%Y%m%d') , INTERVAL id day) date from num limit 2)b)b
on a.date=b.date and a.user_name=b.user_name
where a.user_name is null
group by b.date;

结果展示：
+------------+------------+
| 日期 | 未签到人员 |
+------------+------------+
| 2015-10-14 | 王五 |
| 2015-10-15 | 张三,李四 |
+------------+------------+
2 rows in set

总结：小编说了这么多，其实只想揭示一个论点：授人以鱼不如授人以渔。拿恒定不变的渔去完胜变化莫测的鱼，以静制动，以不变应万变，
御魔斩魔，遇佛成佛。

ITPub博客

sql风暴来袭

最新文章