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、开发技巧
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
复杂情况,单字段属性内容拆分:
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、数据库上传