mysql online ddl
- online ddl
从5.6、5.7起,mysql开始支持online ddl,即新增索引,删除索引,新增列等操作不会锁表。只需要在ddl语句后加上“ALGORITHM=INPLACE, LOCK=NONE”即可。
ALTER TABLE tbl_name RENAME INDEX old_index_name TO new_index_name, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE t1 ADD COLUMN c2 INT, ADD COLUMN c3 INT, ALGORITHM=INSTANT;
链接:MySQL online ddl
- limit优化
当offset很大时,性能会大幅度下降,可以采用延迟关联法,例如:
Select * From user Where vid >=(
Select id From user order By id limit 10000,1
) limit 10
- (1206)The total number of locks exceeds the lock table size‘’
设置buffer为1G=110241024*1024
SET GLOBAL innodb_buffer_pool_size=1073741824;