mysql 优化记录

最新推荐文章于 2021-09-24 19:05:44 发布

奇妙探险家

最新推荐文章于 2021-09-24 19:05:44 发布

阅读量372

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/u013760453/article/details/54295443

版权

mysql 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

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