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

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、数据库上传