mysql千万级数据查询select、插入insert慢 可能原因总结
表连表查询 并 insert ,insert into …select …from…
insert into T1(XX,XX)
select T2.date,
T3.time
from T2 b inner join (select * from T3)c on c.XX=b.XX;
1.(select * from T3)c 与 T3 表名直接写 ,这两种写法亲自测试有些细微的耗时差距,具体我也不清楚,可以亲自试验一下自行选择
2.inner join…on… 与 select …from …where…二者写法耗时结果差别不大
3.能用双表联查就千万千万不要用三表联查,哪怕第三个表只有几十条数据,查询耗时也是成指数级上升的
我选择双表联查生成临时表再与第三个表联查,速度快很多
4. 条件的选择 on… /where …
尽量使用on/where …XXX = T2.XXX 而不是 in (…xxx.,xxx)二者查询时间差距非常大
5.主表有条件先select一遍主表,关联表有条件最好放最后where后(注意:条件放最后和放关联表中查询有时是不一样的)
千万级别数据插入数据库中时一些mysql的设置
1.这些设置不确定一定有很大帮助,没有细致的调试过,自行辨别
– 最大数据量设置
show variables like ‘max_allowed_packet’ ;
SET GLOBAL max_allowed_packet=61210241024;
– 设置超时时间
set net_read_timeout=120;
set net_write_timeout=900;
show variables like “%tmp%”;
SET GLOBAL tmp_table_size =410241024*1024;
set global innodb_flush_log_at_trx_commit=0;
– 查询buffer类全局字段设置
show variables like “%_buffer%”;
SET GLOBAL innodb_buffer_pool_size=102410246000;
SET @@global.bulk_insert_buffer_size = 102410241000;
– 查询所有参数设置
show variables;
SET autocommit=0;
– 关闭唯一性检查
SET UNIQUE_CHECKS=0;
– 禁用外键检查语句为:
SET global FOREIGN_KEY_CHECKS=0;
– 禁止自动提交语句为:
SET AUTOCOMMIT=0;
set global innodb_flush_log_at_trx_commit=2; #禁止时时同步日志到磁盘
set global max_allowed_packet=1024*1024; # 配置max_allowed_packet为1G
set global innodb_io_capacity =2000 ;
set global innodb_io_capacity_max =20000 ;
set global innodb_autoextend_increment = 1024*100 ;
– mysql日志大小
set global innodb_log_file_size=128M;