DDL
创建表
create table new_table like exist_table 包括了主外键及索引
rename table old_table_name to new_table_name
alter table tablename rename to new_table_name
create new_table as select * from exist_table;同时填充了数据,但不包括主外键及索引相相关约束;
change 和modify 都可以修改表的定义,change后面需要写两次列名,可以修改列名称,modify不能修改列名称。
alter table table_name age new_age int(4)
alter table table_name add newCol varchar(30) after c0
alter table table_name modify c0 first
所有的modify column 操作都将导致表重建。通过alter column修改列的默认值,例如:
alter table xx alter column c1 set default 5;
通过repair table 来重建表的索引,该操作会通过排序来构建所有索引,包括唯一索引。
定期分析表analyze table ,optimize table,analyze check optimize alter table 执行期都会锁表。
通过设置innodb_file_per_table=1,每个表都会生成一个独立的ibd文件,用于存储表的数据和索引,在删除大量数据后,设为off或0时表的数据放在系统共享表空间。也就是根数据字典放在一起的。mysql5.6开始默认就是开启的。
删除记录为什么空间没有收回?
innodb的数据是按页存储的,删了一个页上的所有记录,那么这个数据页就可以复用了。数据页的复用和记录的复用是不同的。记录的复用只限于符合范围条件的数据。比如,某个记录被删了,在插入同一个id(主键)时就可以复用这个在数据页的空余空间。如果插入的时一个新的id(与删除的主争键不同),则不能复用这个空余空间。如果整个页的数据被删,则该页被标记为可复用,这时插入一条新记录时,此页就可以复用。如查相邻的数据页利用率很小,系统会把这两个页上的数据合并到其中一个页上,另一个页就标记为可复用。所以,在磁盘上的文件不会变小。
插入数据也会造成页空洞,如果数据是按照索引递增的顺序插入的,那么索引是紧凑的,如果是随机插入的,会造成数据页分裂。更新索引上的值 ,可以理解为删除一个旧的值 ,再插入一个新值,不难理解,也是会造成空洞的。经过大量增删改查的表,都查可能存在空泂的。
可以通过alter table tablename engine=innodb回收不用的空间。自动完成存数据、交换表名、删除旧表的操作。花时间最多的是往临时表中插入数据的过程,在整个DDL过程中,表中不能更新,否则会丢失数据。temp_talbe是一个临时表,是在server层创建的,对于server层来说,没有把数据挪动到临时表,是一个原地操作,即inplace名称的来源。
5.6开始支持on line DDL:
1、建立临时文件,扫描表主键的所有数据页
2、用数据页中表的记录生成B+树,存储到临时文件中
3、生成的临时文件的过程中,将所有对A的操作记录在一个日志文件中
4、临时文件生成后,将日志文件中的操作应用到临时文件,得到一个逻辑数据上的表相同的数据文件。
5、用临时文件替换表的数据文件
由于日志文件记录和重放操作这个功能的存在,这个方案在重建表的过程中,允许对表做增删改操作,这也就是on line ddl的名字来源。
alter 语句需要获取MDL写锁,但在这个写负在真正拷贝数据之前就退化成读锁了。
临时文件temp_file是在innodb引擎内部创建的,整个DDL过程在innodb内部完成,temp_file也是占用临时空间的。alter table tablename engine=innodb 隐含的意思是 alter table tablename engine=innodb ,algorithm=inplace;与之相对应的就是copy,即alter table tablename engine=innodb,algorithm=copy;
DDL过程如果是online的,就一定时inplace的;反过来未必,也就是说inplace的DDL有可能不是OnLine的。
analyze talbe t不是重建表,只是对表的索引信息做重新统计,没有修改数据,这个过程加了MDL读锁。
optimize table t等于recreate+ analyze
mysql中的自增id
myisam引擎的自增值保存在数据文件中
innodb引擎的自增值保存在内存中,8.0后才有了自增持久化的能力。
在双M主备加构中,让一个库的自增id都是奇数,另一个库的自增id都是偶数,避免两个库发生主键冲突。
insert into t values(null,1,1)
innodb获取当前表自增值是2,则插入为(2,1,1),新的自增值改为3,但插入时报duplicate key error,返回,但是自增值并没有改回去,这时要是再插入新的一条,就出现自增主键不一连续的情况。
事务回滚也会产生不连续现象。
在生产生批量插入数据时,innodb_autoinc_lock_mode=2并且binlog_format=row,既能提升并发性,又不会出现数据一致性的问题。
在innodb_auotmic_lock_mode设置为1,也不会等语句执先完成才释放锁,因为这类语句在申请时,就可以计算出要申请多少个,然后一次性申请,申请完成后就可释放锁了。
insert into t select * from t2 每次分配的是前一次分配的2倍
如果最后一次申请的没有用完,就会出现浪费id的现象,导致不连续
自增id用完怎么办
表自增id用完后,再申请得到的值保持不变。最好设为bigint unsigned;
innodb系统自增id row_id,即表没有显式主键时,那么innodb会给你创建一个不可见的长度为6字节的row_id。inno_db维护了一个全局的dict_sys.row_id所有无主键的innodb表每插入一行都将当前的dict_sys.row_id值作为要插入数据的row_id然后把dict_sys.row_id的值 加1
实际上,row_id是一个长度为8字节无符号长整型 bigint unsigned,但实际innodb只给row_id 6字节。
1、row_id是从0 到2^48-1,当dict_sys.row_id2^48时,如果再插入数据的行为来申请row_id,拿到以后再取最后6个字节的话就0
也就是说,达到上限后, 下一下值就是0,然后继续循环。
XID,在redo log 和binlog中有一个共同的字段叫作xid,它在mysql中是用来对应事务的。
mysql内部维护了一个全局变量 global_query_id,每次执行语句的时候将它赋值给query_id,然后给这个变量加1,如果当前语句是这个事务执行的第一条语解码器,那么mysql还会同时把query_id赋值给这个事务的xid.而global_query_id是一个纯内存变量,重启之后就清0了,所以在同一个实例中,不同的事务的xid也是有可能相同的。
但是mysql在重启后会生成新的binlog,这就保证了同一个binlog文件里xid一定是唯一的。
但是如果global_query_id达到上限后,就会继续从0开始计算,所以从理论上讲,有可能会出现同一个binlog里面出现相同的xid场景。
xid是由server层维护的,innodb内部使用xid,就是为了能够在innodb事务和server之间做关联,但是innodb自己的trx_id是另外维护的。
innodb内部维护了一个max_trx_id全局变量,每次需要申请一个新的trx_id时,就获得max_trx_id的当前值 ,然后将max_trx_id加1。每一行数据都记录了更新它的trx_id,当一个事务读到一行数据的时候,判断这个数据是否可见的方法,就是通过事务的一致性视图与这个行数据trx_id做对比。
thread_id才是mysql中最常见的一种自增id,show processlist里面的第一列就是thread_id.
系统保存了一个全局变量,thread_id_counter每新建一个连接,就将thread_id_counter赋值给这个新连接的线程变量。
大小是4个字节,因此达到2^32-1后,就会重置0,然后继续增加,但是在show processlist里面不会看到两个相同的thread_id.这是因为mysql 设计了一个唯数组逻辑,给新线程分配thread_id的时候,逻辑代码是这样的。
do{
new_id=thread_id_counter++;
}while (!thread_ids.insert_unique(new_id).second)
1、表自增id达到上限后,再申请时不会改变,再继续插入就报主键冲突
2、row_id达到上限后,会归0再递增,如果出现相同的row_id,后写的数据会覆盖之前的数据
3、xid只需要不在同一个binlog文件中出现重复值即可,虽然理论上会出现重复值 ,但概率极不,可以忽略不计
4、innodb的max_trx_id递增值每次mysql重启都会被保存起来,所以会出现脏读
5、thread_id是最常见的,而且也是处理得最好的一个自增id
rename table 原表名 to 新表名
rename table 原表名 to 库名.表名(可将表移动到另一个数据库) -- rename 可以交换两个表名
set names utf8相当于完成以下三个
set character_set_client=utf8
set character_set_results=utf8
set character_set_connection=utf8
DDL语句不能被回滚