Mysql DDL学习笔记

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语句不能被回滚