(网络学习)6、mysql维护、开发技巧

课程：
数据库设计那些事：https://www.imooc.com/learn/117
MySQL开发技巧（一）：https://www.imooc.com/learn/398
MySQL开发技巧（二）：https://www.imooc.com/learn/427
MySQL开发技巧（三）：https://www.imooc.com/learn/449

1、数据库维护注意事项

a. 导出数据字典

SELECT TABLE_COMMENT '表名',COLUMN_NAME '字段名',COLUMN_TYPE '数据类型',COLUMN_COMMENT '备注' 
    FROM information_schema.COLUMNS a JOIN information_schema.TABLES 
    b ON a.table_schema=b.table_schema AND a.table_name=b.table_name 
    WHERE a.table_name='s_goods' 

b. 索引维护

索引碎片,原因：update insert delete

• 删除索引并重建
• 使用ALTER INDEX REBUILD语句重建索引，通过ONLINE关键字减少锁
• 使用ALTER INDEX REORGANIZE 整理，当遇到加锁的页时跳过，整理效果会差

c.表的拆分

• 表的垂直查分
  经常查询的列放到一起，大字段放到附加表中
• 表的水平查分
  hash(key)方式存放

2、慢查询工具

a.mysqldumpslow

mysqldumpslow -t 3 /usr/local/mysql/data/mysql-slow.log | more 

b.pt-query-digest

apt-get install percona-toolkit
pt-query-digest -t 3 /usr/local/mysql/data/mysql-slow.log | more 

3、开发技巧

[8.0+]
数学函数列表 (https://dev.mysql.com/doc/ref...
函数索引 (https://dev.mysql.com/doc/ref...

a.分组选择

分类流水记录表：取每个类（相同排序数据）的前2条

with tmp as (
        select a.user_name,b.time,b.kills,row_number() over(
                partition by a.user_name order by kills desc
            ) cnt from users a join user_kills b on a.id=b.user_id
    ) select * from tmp where cnt<=2;

user_name	time	kills	cnt
孙悟空	2013-02-11 00:00:00	20	1
孙悟空	2013-03-12 00:00:00	17	2
沙僧	2013-02-02 00:00:00	10	1
沙僧	2013-02-11 00:00:00	3	2
猪八戒	2013-02-05 00:00:00	12	1
猪八戒	2013-01-10 00:00:00	10	2

select d.user_name,c.time,kills from (
        select user_id,time,kills,(
                select count(*) from user_kills b where b.user_id=a.user_id AND a.kills<=b.kills
            ) AS cnt FROM user_kills a GROUP BY user_id,time,kills
    ) c JOIN users d ON c.user_id=d.id where cnt<=2;

user_name	time	kills
孙悟空	2013-02-11 00:00:00	20
孙悟空	2013-03-12 00:00:00	17
猪八戒	2013-02-05 00:00:00	12
猪八戒	2013-01-10 00:00:00	10
沙僧	2013-02-02 00:00:00	10
沙僧	2013-02-11 00:00:00	3

b.行转列

分类流水记录表：分类汇总

select a.user_name,sum(b.kills) as sum_kills from users a join user_kills b 
    on a.id =b.user_id group by b.user_id;

user_name	sum_kills
猪八戒	29
沙僧	13
孙悟空	57

select 
        sum(case when user_name='孙悟空' then kills end) as '孙悟空',
        sum(case when user_name='猪八戒' then kills end) as '猪八戒',
        sum(case when user_name='沙僧' then kills end) as '沙僧'
    from users a join user_kills b on a.id=b.user_id;

孙悟空	猪八戒	沙僧
57	29	13

c.列转行

单字段属性内容拆分：

select user_name,CONCAT(mobile,',') AS mobile,LENGTH(mobile)-LENGTH(REPLACE(mobile,',',''))+1 AS size from users b;

user_name	mobile	size
孙悟空	16366665325,15623231454,14789897474,11456563214,16832565214,	5
猪八戒	13641412541,12645456332,12463665214,	3
沙僧	13869696541,19811113265,	2

//辅助序列表
create table tb_sequence(id int unsigned not null auto_increment,primary key(id));
insert into tb_sequence values(),(),(),(),(),(),(),(),(),();

select * 
    from tb_sequence a 
        CROSS JOIN(
            select user_name,CONCAT(mobile,',') AS mobile,
                    LENGTH(mobile)-LENGTH(REPLACE(mobile,',',''))+1 AS size 
                from users
    ) b on a.id<=b.size;

id	user_name	mobile	size
1	孙悟空	16366665325,15623231454,14789897474,11456563214,16832565214,	5
2	孙悟空	16366665325,15623231454,14789897474,11456563214,16832565214,	5
3	孙悟空	16366665325,15623231454,14789897474,11456563214,16832565214,	5
4	孙悟空	16366665325,15623231454,14789897474,11456563214,16832565214,	5
5	孙悟空	16366665325,15623231454,14789897474,11456563214,16832565214,	5
1	猪八戒	13641412541,12645456332,12463665214,	3
2	猪八戒	13641412541,12645456332,12463665214,	3
3	猪八戒	13641412541,12645456332,12463665214,	3
1	沙僧	13869696541,19811113265,	2
2	沙僧	13869696541,19811113265,	2

select user_name,REPLACE(
    SUBSTRING(
            SUBSTRING_INDEX(mobile,',',a.id), 
            CHAR_LENGTH(
                SUBSTRING_INDEX(mobile,',',a.id-1)
            ) +1
        ),',','') AS mobile 
    from tb_sequence a 
        CROSS JOIN(
            select user_name,CONCAT(mobile,',') AS mobile,LENGTH(mobile)-LENGTH(REPLACE(mobile,',',''))+1 AS size from users
    ) b on a.id<=b.size;

user_name	mobile
孙悟空	16366665325
孙悟空	15623231454
孙悟空	14789897474
孙悟空	11456563214
孙悟空	16832565214
猪八戒	13641412541
猪八戒	12645456332
猪八戒	12463665214
沙僧	13869696541
沙僧	19811113265

d.生成订单号

辅助表 + 存储过程

CREATE TABLE `order_seq` (time_str varchar(20) NOT NULL, order_sn int(10) unsigned NOT NULL)

DROP PROCEDURE `seq_no`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `seq_no`()
BEGIN
DECLARE v_cnt INT;
DECLARE v_time_str BIGINT;
DECLARE row_count INT;
SET v_time_str=DATE_FORMAT(NOW(),'%Y%m%d%H%i%s');
SELECT ROUND(RAND()*100,0)+1 INTO v_cnt;
START TRANSACTION;
    UPDATE order_seq SET time_str=v_time_str;
    IF ROW_COUNT()=0 THEN
        INSERT INTO order_seq(time_str,order_sn) VALUES(v_time_str,v_cnt);
    END IF;
    SELECT CONCAT(v_time_str,LPAD(order_sn,5,0)) AS order_sn 
        FROM order_seq WHERE time_str=v_time_str;
COMMIT;
END

call seq_no();

e.清理重复数据

简单重名删除

DELETE a from users a join(
    select user_name,count(*),max(id) as id from users group by user_name having count(*)>1
    ) b on a.user_name=b.user_name where a.id<b.id;

复杂情况，单字段属性内容拆分：

user_name	mobile	size
孙悟空	16366665325,15623231454,16366665325,16366665325,16832565214	5
猪八戒	13641412541,12645456332,13641412541	3
沙僧	13869696541,19811113265	2

#行转列
SELECT id,oid,REPLACE(
SUBSTRING(
            SUBSTRING_INDEX(c.mobile,',',c.id), 
            CHAR_LENGTH(
                SUBSTRING_INDEX(c.mobile,',',c.id-1)
            ) +1
        ),',','') AS mobile 
FROM (select a.id oid,b.id,mobile from (
        select id,CONCAT(mobile,',') AS mobile,LENGTH(mobile)-LENGTH(REPLACE(mobile,',',''))+1 AS size from users
    ) a cross join tb_sequence b on a.size>1 and a.size>=b.id
) c;

关联表更新：

UPDATE table1 SET column1 = (SELECT columnA FROM table2 [WHERE condition]) WHERE table1.column2 = table2.columnB;

行转列、带用户id，在已知所有(不同人间)号码不重复时，分组统计号码数，合并必要号码：单人手机数和少于行数（sum(f.mcount)>count(f.mobile)），更新。

update users h set mobile=(SELECT mobile FROM (select f.id,group_concat(f.mobile) as mobile from (SELECT d.oid as id,d.mobile,count(d.mobile) as mcount from (SELECT id,oid,REPLACE( SUBSTRING( SUBSTRING_INDEX(c.mobile,',',c.id),CHAR_LENGTH(SUBSTRING_INDEX(c.mobile,',',c.id-1))+1),',','') AS mobile  FROM (select a.id oid,b.id,mobile from (select id,CONCAT(mobile,',') AS mobile,LENGTH(mobile)-LENGTH(REPLACE(mobile,',',''))+1 AS size from users ) a cross join tb_sequence b on a.size>1 and a.size>=b.id) c) d group by d.mobile) f group by id having sum(f.mcount)>count(f.mobile)) g where g.id=h.id);

oid	id	mobile
2	1	16366665325,15623231454,16366665325,16366665325,16832565214,
2	2	16366665325,15623231454,16366665325,16366665325,16832565214,
2	3	16366665325,15623231454,16366665325,16366665325,16832565214,
2	4	16366665325,15623231454,16366665325,16366665325,16832565214,
2	5	16366665325,15623231454,16366665325,16366665325,16832565214,
3	1	13641412541,12645456332,13641412541,
3	2	13641412541,12645456332,13641412541,
3	3	13641412541,12645456332,13641412541,
4	1	13869696541,19811113265,
4	2	13869696541,19811113265,

id	oid	mobile
1	2	16366665325
2	2	15623231454
3	2	16366665325
4	2	16366665325
5	2	16832565214
1	3	13641412541
2	3	12645456332
3	3	13641412541
1	4	13869696541
2	4	19811113265

id	mobile	mcount
2	16366665325	3
2	15623231454	1
2	16832565214	1
3	13641412541	2
3	12645456332	1
4	13869696541	1
4	19811113265	1

id	mobile
2	16366665325,15623231454,16832565214
3	13641412541,12645456332

update users h set mobile=(SELECT mobile FROM (select f.id,group_concat(f.mobile) as mobile from (SELECT d.oid as id,d.mobile,count(d.mobile) as mcount from (SELECT id,oid,REPLACE( SUBSTRING( SUBSTRING_INDEX(c.mobile,',',c.id),CHAR_LENGTH(SUBSTRING_INDEX(c.mobile,',',c.id-1))+1),',','') AS mobile  FROM (select a.id oid,b.id,mobile from (select id,CONCAT(mobile,',') AS mobile,LENGTH(mobile)-LENGTH(REPLACE(mobile,',',''))+1 AS size from users ) a cross join tb_sequence b on a.size>1 and a.size>=b.id) c) d group by d.mobile) f group by id having sum(f.mcount)>count(f.mobile)) g where g.id=h.id);

f.累进税

区间纳税

• LEAST(value1,value2,...)：返回小值

select a.user_name,money,low,hight,rate from user_salary a join tax_rate b on a.money>b.low order by user_name;

user_name	money	low	hight	rate
唐僧	35000.00	1500.00	4500.00	0.10000
唐僧	35000.00	9000.00	35000.00	0.25000
唐僧	35000.00	0.00	1500.00	0.03000
唐僧	35000.00	4500.00	9000.00	0.20000
孙悟空	28000.00	1500.00	4500.00	0.10000
孙悟空	28000.00	9000.00	35000.00	0.25000
孙悟空	28000.00	0.00	1500.00	0.03000
孙悟空	28000.00	4500.00	9000.00	0.20000
沙僧	8000.00	0.00	1500.00	0.03000
沙僧	8000.00	4500.00	9000.00	0.20000
沙僧	8000.00	1500.00	4500.00	0.10000
猪八戒	15000.00	1500.00	4500.00	0.10000
猪八戒	15000.00	9000.00	35000.00	0.25000
猪八戒	15000.00	0.00	1500.00	0.03000
猪八戒	15000.00	4500.00	9000.00	0.20000

select a.user_name,money,low,hight,LEAST(money-low,hight-low) as cur_money,rate from user_salary a join tax_rate b on a.money>b.low order by user_name,low;

user_name	money	low	hight	cur_money	rate
唐僧	35000.00	0.00	1500.00	1500.00	0.03000
唐僧	35000.00	1500.00	4500.00	3000.00	0.10000
唐僧	35000.00	4500.00	9000.00	4500.00	0.20000
唐僧	35000.00	9000.00	35000.00	26000.00	0.25000
孙悟空	28000.00	0.00	1500.00	1500.00	0.03000
孙悟空	28000.00	1500.00	4500.00	3000.00	0.10000
孙悟空	28000.00	4500.00	9000.00	4500.00	0.20000
孙悟空	28000.00	9000.00	35000.00	19000.00	0.25000
沙僧	8000.00	0.00	1500.00	1500.00	0.03000
沙僧	8000.00	1500.00	4500.00	3000.00	0.10000
沙僧	8000.00	4500.00	9000.00	3500.00	0.20000
猪八戒	15000.00	0.00	1500.00	1500.00	0.03000
猪八戒	15000.00	1500.00	4500.00	3000.00	0.10000
猪八戒	15000.00	4500.00	9000.00	4500.00	0.20000
猪八戒	15000.00	9000.00	35000.00	6000.00	0.25000

select a.user_name,money,sum(LEAST(money-low,hight-low)*rate) as fee from user_salary a join tax_rate b on a.money>b.low group by a.user_name order by a.money desc;

user_name	money	fee
唐僧	35000.00	7745.00002
孙悟空	28000.00	5995.00002
猪八戒	15000.00	2745.00002
沙僧	8000.00	1045.00001

4、数据库上传

数据库文件
https://github.com/cffycls/cluster/blob/master/sql_test/last12.sql