SQL记录 - MySql大数据导入导出 表优化

select count(1) from exceldatas; #9771551
OPTIMIZE table exceldatas;


# 修改当前事务的隔离级别为未提交读：read uncommitted
# 以查看数据insert导入的进度
mysql> set session transaction isolation level read uncommitted;


解决mysql 事务未提交导致死锁报错：
当 sessionA 尝试修改 B 表数据，因为 sessionB 当前为锁定状态，而且 sessionB 对 B 表中数据具有锁定状态中，则出现死锁。sessionB 会自动终止尝试修改 A 表数据事务， 并返回下面错误信息。而且下面两个事务操作都被终止。
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction


# 查看事务自动提交状态
select @@autocommit;
autocommit=0，这个设置导致原来的update语句如果没有commit的话，你再重新执行update语句，就会等待锁定，
当等待时间过长的时候，就会报
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction的错误。
所以赶紧commit刚才执行的update语句，之后 set global autocommit=1;


# 查看数据库引擎状态
show engine innodb status;


# 查看数据库的进程信息
show full processlist;
show processlist
# kill 对应的线程
kill 19;
kill 20;


# 找到了一个一直没有提交的事务,在5.5中，information_schema库中增加了三个关于锁的表（MEMORY引擎）；
SELECT * FROM information_schema.INNODB_TRX; # 当前运行的所有事务
select * from information_schema.innodb_locks; # 当前出现的锁
select * from information_schema.innodb_lock_waits; # 锁等待的对应关系


# 可以通过检查 table_locks_waited 和 table_locks_immediate 状态变量来分析系统上的表锁定争夺：
# Table_locks_immediate 发生表锁定操作, 但表锁定后马上释放
# Table_locks_waited  发生表锁定, 并因此具有锁等待
show global status like "table_locks%";


# 查看一下索引信息
show index from datalist from ax_crm;


# 唯一性校验会降低插入记录的速度，可以在插入记录之前禁用唯一性检查，插入数据完成后再开启。
# 禁用唯一性检查的语句：]
SET UNIQUE_CHECKS = 0;


# 开启唯一性检查的语句：
SET UNIQUE_CHECKS = 1;


# 在批量插入数据之前禁用索引，数据插入完成后再开启索引。
# 禁用索引的语句：
ALTER TABLE datalist DISABLE KEYS


# 开启索引语句：
ALTER TABLE datalist ENABLE KEYS


# 禁止自动提交。插入数据之前禁止事务的自动提交，数据导入完成之后，执行恢复自动提交操作。禁止自动提交语句为： 
SET AUTOCOMMIT=1;  
set global autocommit=1;
# 恢复自动提交只需将该值置1。


#mysql 主从日志文件mysql-bin文件清除方法
reset master;
reset slave






OPTIMIZE TABLE datalist;
OPTIMIZE TABLE exceldatas;
OPTIMIZE TABLE roottask;
OPTIMIZE TABLE searchtask;
OPTIMIZE TABLE sqlite_sequence;
OPTIMIZE TABLE yunkebaodatas;


#innodb_file_per_table OFF
#OFF代表开启共享表空间没有打开，即采用的是默认的共享表空间
show variables like 'innodb_file_per_table';




alter table datalist ENGINE = 'InnoDB';
analyze table datalist;
alter table exceldatas ENGINE = 'InnoDB';
analyze table exceldatas;




#Oracle中的行迁移：
alter table datalist enable row movement; 
alter TABLE datalist shrink SPACE; 
alter table datalist DISABLE row movement;


#mysql查表空间大小的SQL语句 
select 
concat(truncate(sum(data_length)/1024/1024,2),'MB') as data_size,
concat(truncate(sum(max_data_length)/1024/1024,2),'MB') as max_data_size,
concat(truncate(sum(data_free)/1024/1024,2),'MB') as data_free,
concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
from information_schema.tables where TABLE_SCHEMA = 'ax_crm_bk';



#查看表占用硬盘空间大小的SQL语句如下：（默认用M做展示单位）
SELECT 
TABLE_NAME, 
(DATA_LENGTH+INDEX_LENGTH)/1048576, 
TABLE_ROWS 
FROM information_schema.tables 
WHERE 
TABLE_SCHEMA='ax_crm_bk' 

AND TABLE_NAME='exceldatas';