1.获取大数据集之后再执行需要索引的操作,一定要使用中间表!
drop table if EXISTS temp.csm_temp2;
create table temp.csm_temp2 select acct_id,sum(csm) csm from temp.csm_temp GROUP BY acct_id;
ALTER table temp.csm_temp2 add INDEX key1 (acct_id);-- 为中间表加索引
alter table temp.csm_temp2 modify column acct_id varchar(50); -- 修改索引列数据类型,使其与将要关联的列类型相同!
2.join时左右两边关联的列一定要有索引,一定要有相同的数据类型!(否则出现隐式转换,相当于在一边加了函数,索引不起作用)
3.mysql不支持full join,要实现full join再两列求和,可以先取两个子集union all,再根据关联列分组
drop table if EXISTS temp.csm_temp;
CREATE table temp.csm_temp
select acct_id,integrated_csm csm from zhixiao_cash.feed_express_data where stdate like '201612%' and integrated_csm>0;
INSERT into temp.csm_temp
select acct_id,csm from zhixiao_cash.original_ad where stdate BETWEEN'2016-12-01'and'2016-12-31' and csm>0;
ALTER table temp.csm_temp add INDEX key1 (acct_id);
drop table if EXISTS temp.csm_temp2;
create table temp.csm_temp2 select acct_id,sum(csm) csm from temp.csm_temp GROUP BY acct_id;
alter table temp.csm_temp2 modify column acct_id varchar(50);
select s1.acct_id,s1.csm,s2.manager_id,s2.dep_big,s2.dep_small FROM
temp.csm_temp2 s1
left JOIN
zhixiao_acct.acct_info201612 s2
on s1.acct_id=s2.acct_id
-- 存在渠道数据,管理员id和部门为空
3、对于不重要的表如计算中间表、测试表等,发生死锁时直接show processlist再kill xxxxxxx,即可解除死锁。
4、a left join b on a.name=b.name,此句中如果a.name没有not null约束,则不会走索引,解决方法是给a.name加非空约束或者使用a join b即内连接
总结来看:left join 左边要not null,right join 右边要not null,join 无要求,这样才会使用索引。
5、获取表字段名
select COLUMN_NAME from information_schema.COLUMNS where table_name = 'kpi_acct' and table_schema = 'temp';
6、使用load data导入数据时,报错:The MySQL server is running with the --secure-file-priv option so it cannot execute this statement,需打开mysql安装目录项的mysql.ini文件,加上一行secure_file_priv="C:/ProgramData/MySQL/MySQL Server 5.5/upload",重启服务,将待导入文件复制到指定文件夹下才可导入,注意windows下mysql只对C:/ProgramData/MySQL/MySQL Server 5.5有权限,只能设置为该文件夹下的子目录,否则仍会报错
7、load data local infile 'C:/ProgramData/MySQL/MySQL Server 5.5/upload/xzq_aaa.csv' into table test.t
FIELDS TERMINATED BY ',' -- 逗号分隔字段
OPTIONALLY ENCLOSED BY '"' -- 源文件中每个值都被双引号包围,导入时去除
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES -- 跳过首行
导入本地文件到远程服务器时必须加local关键字
8、重复导入数据处理,取出重复数据
select * from temp.xzq_ori GROUP BY creative_id
即可得到唯一数据,其中creative_id为原数据中的唯一id
9、cmd中连接mysql,并执行sql脚本
"C:\Program Files\MySQL\MySQL Server 5.5\bin\mysql.exe" --defaults-file="D:\workspace\LA_daily_report\sql\conf.cnf" zhixiao_la<D:\workspace\LA_daily_report\sql\sql_shouru.sql
参数依次为配置文件位置、数据库、sql脚本位置
配置文件内容为:
[client]
host=***.***.***.***
port=****
user=*********
password=*********
10、mysql left join 时左右两表都有条件,则效果等同于inner join,结果数目可能比左表少。因为实际上匹配不到的记录相当于右表字段都为空,肯定不满足右表条件,导致左表中匹配不上右表的记录不会出现在结果集中,相当于内连接
例如:
x,y---
1,a
2,b
3,c
与
x,y---
1,10
2,20
若限制 select a.x,a.y,b.y
from a left join b
on a.x=b.x
where a.x<3 and b.y>5
则结果为
x,a.y,b.y
1,a,10
2,b,20
而不是
1,a,10
2,b,20
3,null,null
要得到预期结果,需将条件转为左右表中的子查询:
select s1.x,s1.y,s2.y
from (select * from a where x<3) s1
left join (select * from b where y>5) s2
on s1.x=s2.x
11、修改大数据量的表(增删字段,修改字段类型等),应先建立结构相同的新表,在新表上修改结构,再将数据全部复制到新表中,用新表代替旧表,速度大幅提升,同时提高数据安全性。
12、lock wait ;try restart transcation。事务未提交导致锁表,关闭eclipse,mysql中show processlist,kill所有线程,再运行即可。
13、锁表解决方案:
SELECT * FROM information_schema.processlist
WHERE db='zhixiao_la'
找出可能锁表的连接kill掉
kill 82726
14、设置字段非空
alter TABLE zhixiao_cash.original_ad MODIFY stdate date not null ;
15、group_concat函数:
select s1.op_unit_name,GROUP_CONCAT(DISTINCT divide ORDER BY divide DESC SEPARATOR ';') from zhixiao_acct.acct_info s1 GROUP BY s1.op_unit_name
16、清理二进制日志 reset master
关闭二进制日志
在my.cnf/my.ini中注释掉以下两句并重启
#log-bin=mysql-bin
#binlog_format=mixed
17、使用用户输入的字符串时需防止注入,应该以select quote() 的形式使用,该方法自动用单引号包围输入字符串,在sql拼接时保证结果为文本,常用形态为
SELECT CONCAT('SHOW GRANTS FOR ', QUOTE(user), '@', QUOTE(host), ';') FROM mysql.user