涂抹mysql笔记-InnoDB/MyISAM及其它各种存储引擎

2022-11-22 12:29:37

存储引擎：一种设计的存取和处理方式。为不同访问特点的表对象指定不同的存储引擎，可以获取更高的性能和处理数据的灵活性。
通常是.so文件，可以用mysql命令加载它。

查看当前mysql数据库支持的存储引擎：show engines \G
system@(none)>show engines \G
*************************** 1. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 2. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 8. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 9. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
9 rows in set (0.00 sec)
Engine 存储引擎名称
Support 当前是否支持，有下面4个可选值
YES：当前支持该存储引擎，并且处于可用状态
DEFAULT：当前支持该存储引擎，并且未默认存储引擎
NO：当前不支持该存储引擎，说明该存储引擎尽管被编译了但没被支持，因此当前不可用
DISABLED：支持该存储引擎，但是当前被禁用。
Commit：注释信息，一般是该存储引擎的简要说明。
Transactions：是否支持事务。
Savepoints：是否支持保存点。
<>MEMORY存储引擎：
创建一个MEMORY存储引擎的表：
system@5ienet>create table t_mem1(id int) engine=memory;
Query OK, 0 rows affected (0.00 sec)
MEMORY存储引擎的表的物理结构只有一个扩展名为.frm的文件用来存储表结构的定义，不包括数据。
所以mysql数据库关闭后所有MEMORY引擎表的数据都会丢失，表结构会保留。分配给MEMORY引擎表的内存状态
一直持有及时删除表中的数据，所占用的内存也不会被mysql服务收回。只有当整个MEMORY引擎表被删除或重建时才会收回内存。
MEMORY引擎表能够使用的最大内存空间不能超过max_heap_table_size系统变量设定的值。该值默认16M，这个值能够
作用于create table或alter table以及truncate table语句
MEMORY引擎表也可以自己设定内存值。例如：创建两个MEMORY引擎表，1个1M一个2M，创建之前先在会话中设置
max_heap_table_size变量的值（注意是在会话级别设置，而非全局级）
system@5ienet>set max_heap_table_size=1024*1024;
Query OK, 0 rows affected (0.00 sec)

system@5ienet>create table t_mem_1m(v1 varchar(10000)) engine=memory;
Query OK, 0 rows affected (0.00 sec)

system@5ienet>set max_heap_table_size=1024*1024*2;
Query OK, 0 rows affected (0.00 sec)

system@5ienet>create table t_mem_2m(v1 varchar(10000)) engine=memory;
Query OK, 0 rows affected (0.00 sec)

system@5ienet>show table status like 't_mem%' \G
*************************** 1. row ***************************
Name: t_mem1
Engine: MEMORY
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 8
Data_length: 0
Max_data_length: 268435456
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2017-04-07 09:06:17
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
*************************** 2. row ***************************
Name: t_mem_1m
Engine: MEMORY
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 30003
Data_length: 0
Max_data_length: 1020102
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2017-04-07 09:17:30
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
*************************** 3. row ***************************
Name: t_mem_2m
Engine: MEMORY
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 30003
Data_length: 0
Max_data_length: 2070207
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2017-04-07 09:17:38
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
3 rows in set (0.00 sec)
从结果可以看出max_data_lenth标志的最大数据长度时不同的。max_heap_table_size分别为16M 1M 2M
不过mysql服务重启后所有表会自动继承全局max_heap_table_size的值16M
MEMORY存储引擎的表不支持BLOB/TEXT这类大字段。实际内存内存空间应该满足：
max(Avg_row_length*rownum)<max_heap_table_size公式的值。以t_mem_1m表为例
每行记录占用30003B，如果存储35行记录的话，实际占用内存就是1050105超出了1024x1024的范围值。
因此实际分配的内存空间就是30003x34即1020102，另外为什么这里的Avg_row_lenth平均列长度会显示
30003B呢，首先3个自己是行头占用的空间，剩下的30000与当前表对象的字符集有关。可以创建latin1或bgk字符集的表做对比。
MEMORY存储引擎的表因为其特性通常应用在数据临时保存比如论坛中常见的用户当前位置、在线列别之类，或者数据来源于其他表，及时丢失也可以通过一些手段快速重建的场景。

<>CSV(Comma Seoarated Values)存储引擎
CSV存储引擎基于CSV格式文件存储数据。
创建CSV引擎表create table t_csv1 (id int not null default 0,v1 varchar(20) not null default '') engine=csv;
system@5ienet>create table t_csv1 (id int not null default 0,v1 varchar(20) not null default '') engine=csv;
Query OK, 0 rows affected (0.00 sec)

system@5ienet>insert into t_csv1 values(1,'a');
Query OK, 1 row affected (0.01 sec)

system@5ienet>insert into t_csv1 values(2,'b');
Query OK, 1 row affected (0.00 sec)

system@5ienet>select * from t_csv1;
+----+----+
| id | v1 |
+----+----+
| 1 | a |
| 2 | b |
+----+----+
2 rows in set (0.00 sec)
CSV存储引擎表所有列均必须强制指定NOT NULL，不支持索引、分区。对应的物理文件为表结构定义文件.frm 和.CSV的数据文件。还有一个.CSM用来保存表的状态及表中保存的数据量。
-rw-rw----. 1 mysql mysql 8582 4月 7 09:51 t_csv1.frm
-rw-rw----. 1 mysql mysql 35 4月 7 09:52 t_csv1.CSM
-rw-rw----. 1 mysql mysql 12 4月 7 09:52 t_csv1.CSV
[mysql@linux01 5ienet]$ more t_csv1.CSV
1,"a"
2,"b"
也可以不通过sql语句直接通过物理层修改文件内容。.CSV文件损坏可以通过check table 或repair table来检查和修复。

<>archive存储引擎：使用这种存储引擎能将大量数据压缩存储，插入的列会被压缩。具体压缩比多少我们进行测试：
创建MyISAM引擎表对象向其插入若干条记录：
system@5ienet>create table t_myd1 engine=myisam as select table_catalog,table_schema,table_name,column_name from information_schema.columns;
Query OK, 1670 rows affected (0.03 sec)
Records: 1670 Duplicates: 0 Warnings: 0

system@5ienet>insert into t_myd1 select * from t_myd1;
Query OK, 1670 rows affected (0.01 sec)
Records: 1670 Duplicates: 0 Warnings: 0

system@5ienet>insert into t_myd1 select * from t_myd1;
Query OK, 3340 rows affected (0.01 sec)
Records: 3340 Duplicates: 0 Warnings: 0

system@5ienet>insert into t_myd1 select * from t_myd1;
Query OK, 6680 rows affected (0.01 sec)
Records: 6680 Duplicates: 0 Warnings: 0
查看表对象大小：show table status like 't_myd1' \G
system@5ienet>show table status like 't_myd1' \G
*************************** 1. row ***************************
Name: t_myd1
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 13360
Avg_row_length: 61
Data_length: 817888
Max_data_length: 281474976710655
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2017-04-07 10:04:38
Update_time: 2017-04-07 10:05:44
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
当前有13360行数据，占用了800K空间，再创建一个相同的结构、记录数的archive引擎表对比如下：
system@5ienet>create table t_arc1 engine=archive as select * from t_myd1;
Query OK, 13360 rows affected (0.05 sec)
Records: 13360 Duplicates: 0 Warnings: 0
system@5ienet>show table status like 't_arc1' \G
*************************** 1. row ***************************
Name: t_arc1
Engine: ARCHIVE
Version: 10
Row_format: Compressed
Rows: 13360
Avg_row_length: 8
Data_length: 110442
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: NULL
Update_time: 2017-04-07 10:12:29
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
Data_length: 110442 大小仅为100K左右。压缩比和MyISAM比压缩比达到8倍。节省空间方面成效显著。
但是archive引擎表不足也比较明显：仅能支持insert和select语句，而不能支持delete replace update。
能够支持order by和blob列等常规类型，也能使用行锁但是不支持索引，最出众的就是插入效率高，占用空间小。
应用场景：极少访问的归档数据如大量的历史数据极少访问又不能删除。
物理文件：标准的.frm结构定义文件 .arz的数据文件执行优化操作时可能还会出现.arn的文件

<>BLACKHOLE存储引擎：只接受数据但不保存数据。
创建BLACKHOLE存储引擎表t_bh1并插入两条数据：
system@5ienet>create table t_bh1(i int,c char(10)) engine=BLACKHOLE;
Query OK, 0 rows affected (0.00 sec)

system@5ienet>insert into t_bh1 values(1,'record one'),(2,'record two');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

system@5ienet>select * from t_bh1;
Empty set (0.00 sec)
物理文件只有.frm也就是说只有表的定义。
尽管BLACKHOLE存储引擎对象不会报错任何数据，但是如果启用了binlog，那么执行的SQL语句实际上是被记录的了（也就是说能够被复制到Slava端）结合复制特性中的replicate-do-*活replicate-ignore-*规则，可以实现对日志的过滤，通过一些巧妙的设计就可以实现相同的写入操作。但在Master和Slave中产生不同的数据。
应用场景：
验证dump文件语法
通过对比启动及禁用二进制日志文件时的性能来评估二进制日志对负载的影响。
BLACKHOLE实际上是no-op(无操作)引擎因此可以用于发现与存储引擎无关的性能瓶颈。
BLACKHOLE存储引擎支持事务，提交会写入二进制日志，但回滚不会。

<>MERGE存储引擎：也被称为MGR_MyISAM存储引擎，它实际上是将一组MyISAM表聚合在一起，使用时就像一张表
以此来简化查询操作。
创建两个结构完全相同的MyISAM引擎表，并分别插入数条记录：
system@5ienet> create table t_mys1(id int not null auto_increment primary key,v1 varchar(20)) engine=myisam;
Query OK, 0 rows affected (0.01 sec)
system@5ienet>create table t_mys2(id int not null auto_increment primary key,v1 varchar(20)) engine=myisam;
Query OK, 0 rows affected (0.01 sec)

system@5ienet>insert into t_mys1(v1) values ('This'),('Is'),('mys1');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
system@5ienet>insert into t_mys2(v1) values ('This'),('Is'),('mys2');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
然后就可以创建MERGE引擎表，注意UNION子句：
system@5ienet> create table t_mer1(id int not null auto_increment primary key,v1 varchar(20)) engine=merge union=(t_mys1,t_mys2);
Query OK, 0 rows affected (0.01 sec)

system@5ienet>select * from t_mer1;
+----+------+
| id | v1 |
+----+------+
| 1 | This |
| 2 | Is |
| 3 | mys1 |
| 1 | This |
| 2 | Is |
| 3 | mys2 |
+----+------+
6 rows in set (0.00 sec)
物理结构存储结构定义的.frm文件保存数据来源地的.mgr
[mysql@linux01 5ienet]$ cat t_mer1.MRG
t_mys1
t_mys2
MERGE引擎表本身并不存储数据，它只是起一个汇总作用。同时也支持向MERGE引擎表中插入、修改、删除记录。
对MERGE引擎表update delete时就像操作普通的MyISAM表一样。MERGE引擎表插入数据时需要做一下配置，默认情况下是不能像MERGE引擎表插入记录的，它不知道应该把记录写到那个具体的MyISAM表中，除非在创建MERRGE表是通过INSERT_METHOD现象指定了插入记录写到哪个表。
INSERT_METHOD有三个可选值：
NO：不允许插入，默认值
FIRST：插入第一张表中
LAST：插入最后一张表中。

system@5ienet>alter table t_mer1 insert_method=first;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

system@5ienet>insert into t_mer1 (v1) values('first');
Query OK, 1 row affected (0.00 sec)
system@5ienet>select * from t_mer1;
+----+-------+
| id | v1 |
+----+-------+
| 1 | This |
| 2 | Is |
| 3 | mys1 |
| 4 | first |
| 1 | This |
| 2 | Is |
| 3 | mys2 |
+----+-------+
7 rows in set (0.00 sec)
system@5ienet>select * from t_mys1;
+----+-------+
| id | v1 |
+----+-------+
| 1 | This |
| 2 | Is |
| 3 | mys1 |
| 4 | first |
+----+-------+
4 rows in set (0.00 sec)
t_mys1中多一条first记录。

system@5ienet>insert into t_mys2(v1) values ('mys2 record2');
Query OK, 1 row affected (0.00 sec)
system@5ienet>select * from t_mer1;
+----+--------------+
| id | v1 |
+----+--------------+
| 1 | This |
| 2 | Is |
| 3 | mys1 |
| 4 | first |
| 1 | This |
| 2 | Is |
| 3 | mys2 |
| 4 | mys2 record2 |
+----+--------------+
8 rows in set (0.00 sec)
t_mer1已经有mys2 record2 记录了。

<>FEDERATED存储引擎：类似oracle dblink可以实现从一个mysql实例连到另一个mysql实例的库。
如果希望使用这个存储引擎那么在源码编译mysql时需要执行CMake时附加-DWITH_FEDERATED_STORAGE_ENGINE选项默认不启用
可以执行show engines;查看。
*************************** 9. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
9 rows in set (0.00 sec)
默认启动启用FEDERATED存储引擎需要编辑my.cnf在[mysqld]区块中添加一行federated。修改后重新启动mysql查看存储引擎可以看到FEDERATED引擎已经启用。
*************************** 9. row ***************************
Engine: FEDERATED
Support: YES
Comment: Federated MySQL storage engine
Transactions: NO
XA: NO
Savepoints: NO
9 rows in set (0.00 sec)

创建普通的存储引擎表，表对象包含表结构和数据，创建FEDERATED表只有表结构，其物理的数据来自远端的mysql服务器。
FEDERATED表包含两个元素：
一个远端的mysql数据库表，这个数据库表可以是目标端支持的任意类型的表。
一个本地的数据库表，该表结构与目标端的表完全相同，当然本地只有.frm文件，不会保存具体的数据。另外表的定义信息中还会包括到目标端的连接信息。

FEDERATED表基础结构图：
Loca Server Remote Server
| |
v v
Federated Table <--Queries/Data ---> Remote Table
| |
v v
.frm File .frm File Data
查询FEDERATED表时需要先将数据从远端拉回本地，但数据不会在本地保存，也就是说客户端每次查询数据实际上都需要传输数据。FEDERATED表能够指向另一个FEDERATED表，但这么用移动要注意不要造成了循环。
可以通过在FEDERATED表所引用的基表上创建适当的索引以提高性能。如果索引比较合理能够在很大程度上降低返回到本地的数据量，这样就能降低网络负载，提高响应速度，否则的话则可能需要将远端整个表的数据都拿到本地进行处理了。
FEDERATED表上尽管能够有索引，但本质上还是不支持索引的。因为表的读取是在远端进行的，远端对象上可以有索引。因此在创建FEDERATED表时，列上有索引时就需要注意了，特别是当对varchar text blob列创建前缀索引时（或者说当创建操作需要在本地产生数据时）创建语句失败。
对于配置了replication的复制环境，FEDERATED表可能被复制到其他slave端，那么一定要注意该slava端能够成功连接至远程服务器。

本地的FEDERATED表无法感知到远程表的变更，如果远程端对象发生结构修改，可能会影响本地表的完整性。
在FEDERATED表上执行批量插入的性能会比其他引擎表稍差，这是因为实际上还是以每条记录单独插入的方式处理。
FEDERATED表不支持查询缓存，不支持分区，不支持事务。insert_id和timestamp选项不会传送到数据提供方。
远程端必须是mysql服务器，远程端的对象必须存在。drop table语句操作FEDERATED表只是删除本地表，不会影响远程对象。
虽然能够对本地的FEDERATED表执行select insert update delete语句，但只是执行并非处理（handler）处理是目标端的事情。
FEDERATED存储引擎支持select insert update delete truncate table及索引，但是不支持alter table以及其他ddl语句对表结构的变更，drop table除外，insert update delete操作本地的表实际上都是要将数据发送至远端。
FEDERATED表狗能接受insert... on duplicate key update语句，不过如果违反复制键的原则语句执行就会失败。
如果使用connection语句时，密码中不能包含有'@'字符，因为这个字符在连接语句时是关键字。
FEDERATED表在创建时跟MERGE表有些类似，本地表对象只是个空壳子，实际数据来源于基表，但是本地表的结构又必须与基表相同。所以第一步是获取建表语句。
system@5ienet>show create table t3 \G
*************************** 1. row ***************************
Table: t3
Create Table: CREATE TABLE `t3` (
`v1` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
`v2` varchar(20) CHARACTER SET gbk DEFAULT NULL,
`v3` varchar(20) CHARACTER SET gb2312 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
本地创建FEDERATED表，需要附加CONNECTION选项指定连接信息：
1、使用CONNECTION子句在192.168.1.7上的mysql上创建FEDERATED表
system@5ienet>CREATE TABLE `t3` (
-> `v1` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
-> `v2` varchar(20) CHARACTER SET gbk DEFAULT NULL,
-> `v3` varchar(20) CHARACTER SET gb2312 DEFAULT NULL
-> ) ENGINE=FEDERATED DEFAULT CHARSET=utf8
-> CONNECTION='mysql://system:oralinux@192.168.1.6/5ienet/t3';
Query OK, 0 rows affected (0.01 sec)
system@5ienet>select * from t3;
+-------+----------+-------+
| v1 | v2 | v3 |
+-------+----------+-------+
| china | cn中国 | cn中国|
+-------+----------+-------+
1 row in set, 1 warning (0.00 sec)

在192.168.1.6mysql上5ienet的t3表插入一条数据：
system@5ienet>insert into t3 values('china1','china2','china3');
Query OK, 1 row affected (0.00 sec)
然后从192.168.1.7上的mysql上查询t3表：
system@5ienet>select * from t3;
+--------+----------+--------+
| v1 | v2 | v3 |
+--------+----------+--------+
| china | cn中国 | cn中国 |
| china1 | china2 | china3 |
+--------+----------+--------+
2 rows in set, 1 warning (0.01 sec)
CONNECTION子句格式：
scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name
scheme：连接所用的接口，目前只支持mysql
user_name：连接的用户名，该用户必须有操作远端服务器目标对象的权限。
password：与连接用户对应的密码
host_name：远端主机的主机名或IP地址
port_num：指定远端服务的连接端口，如不指定则默认是3306
db_name：指定远端目标对象所属的数据库名。
tbl_name：指定远端目标对象的名称，这个名称可以与本地并不相同，重要的是列要对应。
mysql://system:oralinux@192.168.1.6:3306/db_name/tbl_name

2、利用create server创建FEDERATED表
create server linux01_5ienet foreign data wrapper mysql
options(user 'system',password'oralinux',host'192.168.1.6',port 3306,database '5ienet');

system@5ienet>create server linux01_5ienet foreign data wrapper mysql
-> options(user 'system',password'oralinux',host'192.168.1.6',port 3306,database '5ienet');
Query OK, 1 row affected (0.00 sec)

通过create server创建的server信息会保存在mysql.servers表中。

<>MyISAM存储引擎：5.5之前默认存储引擎，之后InnoDB
创建MyISAM引擎表：create table t_myd2(id int) engine=myisam;

system@5ienet>create table t_myd2(id int) engine=myisam;
Query OK, 0 rows affected (0.10 sec)
物理结构：
.frm：对象结构定义文件，用于存储表对象的结构
.MYD：数据文件用于存储表数据
.MYI：索引文件，用于存储表的索引信息。
[mysql@linux01 ~]$ ls -ltr /mysql/data/5ienet/ |grep t_myd2
-rw-rw----. 1 mysql mysql 8556 4月 10 08:38 t_myd2.frm
-rw-rw----. 1 mysql mysql 1024 4月 10 08:38 t_myd2.MYI
-rw-rw----. 1 mysql mysql 0 4月 10 08:38 t_myd2.MYD
自定义MyISAM表数据文件和索引未见的实际存储路径（不指定默认保存在db下）create table 提供了下列两个选项：
DATA DIRECTORY[=]'absolute path to directory'
INDEX DIRECTORY[=]'absolute path to directory'
这样创建后db下的.MYD和.MYI文件只是个软连接指向实际的存储路径。
Mysql中的CHAR类型定义长度时最大可以达255B，VARCHAR类型的最大长度可以达到65535B
MySQL中创建表对象时，定义的字符类型长度的是字符长度，而非常见的字节长度。
创建一个t_myd3仅用欧一个v1列，列长度定义为10：create table t_myd3 (v1 char(10)) engine=myisam;
system@5ienet>create table t_myd3 (v1 char(10)) engine=myisam;
Query OK, 0 rows affected (0.02 sec)
system@5ienet>insert into t_myd3 values('一二三四五六七八九十');
Query OK, 1 row affected (0.00 sec)
system@5ienet>insert into t_myd3 values('1234567890');
Query OK, 1 row affected (0.00 sec)
查询每条记录的长度：
system@5ienet>select length(v1) from t_myd3;
+------------+
| length(v1) |
+------------+
| 30 |
| 10 |
+------------+
2 rows in set (0.00 sec)
单表（注意不是单列）字符列最大长度不能超过65532B，这一点与oracle数据库列字段定义不同。

定义一个长度为65533B长度的列创建就会报错：create table t_myd4(v1 varchar(65533)) engine=myisam charset=latin1;
system@5ienet>create table t_myd4(v1 varchar(65533)) engine=myisam charset=latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
改为65532则可以：create table t_myd5(v1 varchar(65532)) engine=myisam charset=latin1;
system@5ienet>create table t_myd5(v1 varchar(65532)) engine=myisam charset=latin1;
Query OK, 0 rows affected (0.00 sec)
这个设定对于其他引擎一样，对于CHAR VARCHAR BINARY VARBINARY这些字符类型，在定义列长时长度不能超过65532B
MyISAM引擎特性：
选项指标
最大存储能力 256TB
MVCC NO
B-tree索引 YES
Clustered索引 NO
Hash索引 NO
全文检索索引 YES
索引缓存 YES
数据压缩 YES
复制 YES
查询缓存 YES
事务 NO
地理三维数据类型支持 YES
数据缓存 NO
数据加密 YES
外键约束 NO
统计信息 YES
锁粒度 Table
集群数据库 NO
备份/时间点恢复 YES
mysql自带的mysql和information_schma两个数据库其内部表对象就是使用MyISAM引擎（且不能转化为其他引擎）
优点：查询快、写入快
缺点：锁粒度太粗（表锁），使得其在应对OLTP中读写并重的场景时整体响应速度不甚理想，并不支持事务。

MyISAM支持3中不同的存储格式：定长（FIXED也称静态）、动态（DYNAMIC）和压缩（COMPRESSED）前两种会根据列的类型自动适配。压缩必须通过专用工具--myisampack创建
静态格式表：表中不包含变长类型的列(varchar/varbinary/blob/text)所定义的列是固定的字节数。
创建静态表：create table t_myd5(id int,v1 char(10)) engine=myisam;
system@5ienet>create table t_myd5(id int,v1 char(10)) engine=myisam;
Query OK, 0 rows affected (0.00 sec)
查询表是否是静态表 show table status like 't_myd5' \G
system@5ienet>show table status like 't_myd5' \G
*************************** 1. row ***************************
Name: t_myd5
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 9851624184872959
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2017-04-10 09:26:11
Update_time: 2017-04-10 09:26:11
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
可以看到Row_format: Fixed

创建一个MyISAM表，通过附加ROW_FORMAT选项强制指定表对象为静态表(即使其中表包含有变长列)
system@5ienet> desc t_myd1;
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| table_catalog | varchar(512) | NO | | | |
| table_schema | varchar(64) | NO | | | |
| table_name | varchar(64) | NO | | | |
| column_name | varchar(64) | NO | | | |
+---------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
system@5ienet>create table t_myd6 row_format=fixed engine=myisam as select * from t_myd1;
Query OK, 13360 rows affected (0.06 sec)
Records: 13360 Duplicates: 0 Warnings: 0
system@5ienet>show table status like 't_myd6' \G
*************************** 1. row ***************************
Name: t_myd6
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 13360
Avg_row_length: 2118
Data_length: 28296480
Max_data_length: 596164000673169407
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2017-04-10 09:30:44
Update_time: 2017-04-10 09:30:44
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options: row_format=FIXED
Comment:
1 row in set (0.00 sec)
这种强制转换变长列的表成静态表后表空间扩大将近10倍。
使用静态表很容易就可以导磁盘中的数据文件中定位和查找记录，扫描表示因为记录的长度确定也很容易得到指定数据的记录。
在向静态表中写数据时，如果mysql服务崩溃，那么数据的安全相对有保障。在这种情况下通过myisamchk命令行工具就可以很方便的判断出每行的其实和终止位置，然后就可以修复正确的记录。丢弃哪些未完整写入的部分。

静态表的特点：
对于char、varchar类型的列会自动填充空格以达到定义的列长度
较快、易于缓存
易于崩溃后重建，因为记录保存的位置是固定的
一般不需要重建，除非删除了大量的记录，而后希望释放相应的磁盘空间。
通常会比动态格式的表占用更多的磁盘空间
若表中含有blob、text类型那只能是动态表了，含有varchar还可以强制转成静态表，会以空格补足。

动态表：每行都需要有个行头来记录改行的长度，二期由于长度不定，在不定期的更新操作后会产生存储上的碎片。（可以用OPTIMIZE TABLE语句和myisamchk -r命令来消除碎片）从性能上来讲静态表的处理性能会高于动态表。但是占用存储空间也会高于动态表。
动态表的特点：
除了字符长度小于4的列外，其他字符长度都是动态的。
每行记录的行头都有一个bitmap标示该行哪些列包含空字串（对于字符类型的列）或0（对于数值类型的列），注意这不包含NULL值得列，如果字符类型的列截取掉空格后长度为0，或者数值类型的列的列值为0，那么这类列的列值只需要在bitmap中标注即可。并不需要向磁盘中列实际对应的位置写任何值。如果是非空的字符列的话，则按照实际长度去保存。相比静态表而言，相同列长定义的情况下有可能会节省一定的空间。
每行仅需要存储字符实际需要的空间，如果之后记录变大则该条记录可能会分片保存，这也是行碎片产生的主因。比如说修改一条附加更多信息，则该条记录的长度必然会扩展，当原始分配的空间无法存储新增的数据时，只能将新数据保存在另外的位置，这即产生了碎片。更新一行记录产生碎片的同时就会生成一个链接。一个新链接至少20个字节，以便能够满足扩展需求。myisamchk -ei可以用来查询表的统计信息。通过myisamchk -ed命令可以查询表对象的链接数，同事也可以通过定期执行OPTIMIZE TABLE或者myisamchk -r命令来消除碎片和链接。
相比静态类型表，遇到崩溃时恢复操作会更加复杂。也是因为节能存在碎片其中某些链接（碎片）可能丢失。一旦出现这种情况那么基本上这行记录的数据也就丢失了。
压缩格式的表：创建只能使用myisampack命令，解压缩使用myisamchk命令。压缩表是只读的不支持添加或者修改记录。压缩表是基于静态或动态格式标的，有点在于更加小，更节省空间，相对理论上也会更快。但是使用场景有限。

<>InnoDB存储引擎：mysql5.5之后默认的存储引擎其包含如下特性：
设计遵循ACID模型（事务的原子性Atomiocity 一致性 Consistency 隔离性 Isolation 持久性 Durability），支持事务拥有从服务崩溃中恢复的能力，能最大限度地保护用户的数据。
支持行锁，并且引入类似oracle数据库中的一致性读特性，以提升多用户并发时的读写性能。
InnoDB引擎表组织数据时按照逐渐聚簇，通过主键查找数据时的性能极为优异。
在维护数据完整性方面，InnoDB支持外键约束。
服务软硬件宕机在启动时能够自动进行故障恢复（原理同oracle数据库中的实例恢复）
InnoDB拥有自己独立的换存池(innodb_buffer_pool_size系统变量类似oracle中的sga_target)常用数据(含索引)都在缓存中
对于insert update delete操作，会被一种称为change buffering的机制自动优化。InnDB不仅仅提供了一致性读，而且能够换存变更的数据，以减少磁盘I/O

show engines;可以看到InnoDB是默认的存储引擎（Support列为DEFAULT）。默认存储引擎可以通过default_storage_engine修改

InnoDB存储引擎特点：
选项指标
存储能力 64TB
MVCC YES
B-tree索引 YES
CLUSTERED索引 YES
Hash索引 NO
全文检索索引 NO
外键约束 YES
查询缓存 YES
索引缓存 YES
数据缓存 YES
事务 YES
地理三维数据类型支持 YES
地理数据索引支持 NO
数据加密 YES
数据压缩 YES
统计信息 YES
锁粒度 ROW
复制 YES
集群数据库 NO
备份/时间点恢复 YES

InnoDB特性要求：
所有的表都要创建主键，最好选择常作为查询条件的列，如果没有合适的列那么就创建到auto-increment列上
如果数据时通过多表关联获取那么使用join，为提高join的性能最好在join列上创建索引，并且join条件的列最好使用相同的数据类型和定义。
综合考虑磁盘的I/O能力必要时可以禁用autocommit自动提交功能。
相互关联的DML操作放到同一个事务中处理
停止使用LOCK TABLE语句，InnoDB能够处理多会话并发读写同一个表对象，如果是希望之星排它的记录更新，那么可以尝试使用select ... for update语句
启动innodb_file_per_table选项以便表中数据和索引保存在单独的文件中，而不是保存到系统表空间(System Tablespace)
评估数据和读写行为是否适用新的压缩特性，如果可以建议在执行create table时指定row_format=compressed选项，以提高读写性能
启动mysql服务时附加--sql_mode=NO_ENGINE_SUBSTITUTION以防止表被创建成其他存储引擎
在新版本中删除或创建索引性能有所提升，最系统的冲击也有所降低。
清空（truncate）表，非常的快。并且释放的空间能够被操作系统重用。
使用DYNAMIC格式保存大数据类型（BLOB或TEXT）将更有效率
在INFORMATION_SCHEMA库中提供了若干新的表对象，可用于监控存储引擎的工作和负载情况。
新增了PERFORMANCE_SCHEMA库，可以用来查看存储引擎的性能统计数据。

InnoDB引擎配置：
创建Innodb引擎表（手动指定InnoDB引擎）:create table t_idb1(id int not null auto_increment primary key,v1 varchar(20)) engine=innodb auto_increment=1;
system@5ienet>create table t_idb1(id int not null auto_increment primary key,v1 varchar(20)) engine=innodb auto_increment=1;
Query OK, 0 rows affected (0.01 sec)

默认情况下InnoDB引擎只对应一个表空间即系统表空间。所有InnoDB引擎表的数据（含索引）都存储在该表空间中，注意仅仅是保存数据，表对象的结构则仍然需要保存在表对象同名的.frm文件中。
InnoDB表空间都对应哪些物理的数据文件是通过系统变量innodb_data_file_path来设置的，该变量语法如下：
innodb_data_file_path=datafile_spec1[;datafile_spec2]...
每个datafile_spec对应一个数据文件，在具体设置时可以指定文件名、文件大小和扩展支持，其语法完整格式如下：
file_name:file_size[:autoextend[:max:max_file_size]]
file_name：指定文件名
file_size：指定文件大小
autoextend：指定是否可扩展，可选参数。
:max:max_file_size：指定该数据文件最大可占用空间，可选参数。
autoextend现象默认一次扩展8M空间，大小可以通过innodb_autoextend_increment系统变量进行设置。
例如：设置数据文件ibdata01.df，文件大小初始2G，允许自动扩展。最大可扩展至100G，则指定innodb_data_file_path参数值如下：
innodb_data_file_path=ibdata01.df:2048M:autoextend:max:100G

InnoDB系统表空间数据文件默认保存在mysql的data目录中，如果变更文件的保存路径可通过系统变量innodb_data_home_dir设置如下：
innodb_data_home_dir=/mysql/data/innodb_ts
也可直接在innodb_data_file_path中进行设置：innodb_data_file_path=/mysql/data/innodb_ts/ibdata01.df:2048M:autoextend:max:100G
innodb_data_file_path只是设定系统表空间的存储路径。还有非系统表空间下面介绍：
与MyISAM引擎不同默认情况下InnoDB引擎的表和索引都保存在系统表空间对应的数据文件中，这种方式的优点是便于管理，只需要关注系统表空间对应的少数几个文件即可，不过缺点也是挺明显的。当数据量很大的时候DBA的管理成本可能会上升。另外系统表空间的数据文件扩展后无法回缩。就是说及时表被删除或truncate甚至该表空间内实际已经没有存储任何数据，已分配的空间仍然仅是相对于InnoDB数据可用，而不能被操作系统再分配给其他文件使用。如果要收回这部分空间只能先导出数据删除数据文件并重新配置innodb_data_file_path参数，重新启动mysql服务后再重新导入数据。这种方式效率差代价高，许多场景下不具备实施性，针对这种情况可以考虑应用InnoDB数据存储的另一项设定，InnoDB将其定义为多重表空间（multiple tablespaces），说的是每个表对象拥有一个独享的.ibd为扩展的数据文件，这个文件就是一个独立的表空间，相当于MyISAM中的.myi和.myd的合体。相比系统表空间多重表空间有下列有点：
各表对象的数据独立存储至不同的文件，可以更灵活的分散I/O、执行备份及恢复操作
能够支持compressed row format压缩存储数据
当执行truncate/drop删除表对象时，空间可以及时释放回操作系统层
空间自动扩展，无需额外配置
是否启用多重表空间是由系统变量innodb_file_per_table来控制，该参数的设置较为简单，1或ON标志启用多重表空间，0或OFF表示不启用。
启用多重表空间：set global innodb_file_per_table=1; 查看是否启用：show variables like 'innodb_file_per_table';
system@(none)>set global innodb_file_per_table=1;
Query OK, 0 rows affected (0.00 sec)
system@(none)>show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)
mysql5.6 innodb_file_per_table默认为启用状态，所以前面创建的t_idb1表的物理文件有：
-rw-rw----. 1 mysql mysql 8582 4月 10 15:17 t_idb1.frm ----->表结构定义文件
-rw-rw----. 1 mysql mysql 98304 4月 10 15:17 t_idb1.ibd ----->表空间文件
除了有同名的.frm表结构定义文件外，还有一个同名的.ibd这个文件就是表空间文件。

配置InnoDB日志文件：该日志文件与oracle数据库的redolog极为相似。
[mysql@linux01 data]$ ll /mysql/data/ |grep log
-rw-rw----. 1 mysql mysql 268435456 4月 10 15:17 ib_logfile0
-rw-rw----. 1 mysql mysql 268435456 4月 31 09:30 ib_logfile1
-rw-rw----. 1 mysql mysql 268435456 4月 31 09:30 ib_logfile2
日志保存在datadir变量指定的路径下：
[mysql@linux01 data]$ cat ../conf/my.cnf |grep datadir
datadir=/mysql/data

日志参数设定：
innodb_log_group_home_dir：指定InnoDB的redolog日志文件的保存路径默认在datadir变量指定的路径下
innodb_log_file_size：用于指定日志文件的大小，默认是5M，每个日志文件最大不能超过512G。本参数会影响检查点的执行频率，以及故障恢复的时间，一般来说日志文件设置的越大，检查点执行的频率就越低。从缓存池刷新数据到磁盘的次数就相对要少，因此能够减少IO操作。但是如果在这个期间出现故障，那么重新启动mysql服务时灾难恢复的时间也会越长。所以大小要适量。
innodb_log_files_in_group：用于指定日志文件的数量，默认（最少）是2个。最多不超过100个。
本书这3个参数是这样设置的：
innodb_log_file_size=256M
innodb_log_files_in_group=3
innodb_log_group_home_dir这个采用默认

innodb_fast_shutdown参数：共有0 1 2三种选项，默认是1
模式1：InnoDB将关闭会话终止连接，将已提交的数据刷新至数据文件，为提交的事务则进行回滚。这种方式也称为快速关闭（Fast Shutdown）类似oracle数据库的shutdown immediate
模式0：则要等到会话关闭、所有事务结束，缓存区中的数据刷新到磁盘。类似oracle的shutdown normal
模式2：这种模式将忽略当前执行的所有操作直接关闭。类似oracle中的shutdown abort。下次启动时InnoDB需要故障恢复，重新读取日志文件中的数据回滚未提交的事务等。
system@(none)>show global variables like 'innodb_fast_shutdown';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| innodb_fast_shutdown | 1 |
+----------------------+-------+
1 row in set (0.00 sec)

设置独立undo表空间：
UNDO日志：事务操作有提交和回滚。提交：确定保存写入的数据。回滚：先撤销刚刚做的修改，将数据恢复至修改前的状态。那么UNDO日志就是保存修改前的数据。存储在系统分配好的回滚段中。

mysql5.6之前undo都是保存在系统表空间，之后undo可以单独设置表空间。
设置独立的undo表空间需要的参数：
innodb_undo_directory：指定保存undo日志的物理文件的位置
innodb_undo_tablespaces：指定undo表空间的数量，每个undo表空间都是独立的.idb文件。
innodb_undo_logs：指定undo表空间中回滚段的数量（之前是innodb_rollback_segments）。

undo表空间必须得在数据库创建之前指定，undo表空间一旦创建就不能删除，意味着一旦使用了undo表空间该数据库就无法被降级到5.6之前的版本。

InnoDB引擎事务默认为自动提交模式。
禁用事务自动提交：set autocommit=off;(0or1也行)
system@(none)>set autocommit=off;
Query OK, 0 rows affected (0.00 sec)
show variables like 'autocommit';
system@(none)>show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set (0.00 sec)
以上是session级别的设置，全局设置方法：
方法一：set global init_connect='set autocommit=0';
方法二：初始化文件中
[mysqld]
init_connect='set autocommit=0'
方法三：启动mysql时带上命令行参数 -init_connect='set autocommit=0'

显式声明事务：start transaction; 这样即使是自动提交事务但显式声明后事务的控制就可以使用commit和rollback了。

修改其他存储引擎表为InnoDB：alter table table_name engine=innodb;更改引擎表相当于对象重建（重建结构和数据）
但是不要更改mysql的系统表（mysql库下的表默认均为MyISAM引擎表）不能更改成InnoDB引擎表
InnoDB表会比MyISAM表占用更多的磁盘空间。

InnoDB的逻辑存储结构从小到大分成了4中粒度：
页（Pages，也叫块），页是InnoDB中的最小管理单位，同一个mysql数据库不管它分成多少个表空间，所有表空间都拥有相同页大小。默认情况下Page size的大小为16KB，不过可以在创建mysql实例（即初始化数据库）时通过innodb_page_size变量进行配置，可选值有4k 8k 16k三种。
扩展（Extents也叫区）每个扩展固定1M大小，由64个16k的页组成。页大小为8k时由128个页组成，大小为4k由256个页组成。
段（Segments）段本身有很多种，比如像数据段、索引段还有前面提到的回滚段，不过对于InnoDB来说，这里说的段实际上指的是独立表空间对应的数据文件。
表空间（Tablespaces）InnoDB逻辑单元中的最高粒度。
这种逻辑结构与oracle相似，但是mysql中最细粒度的存储单元叫做页，oracle中则叫块。

查看InnoDB的基础状态信息：show engine innodb status;
InnoDB表会产生碎片其原因是：一条记录小于page的一半那么它被完整的存储在一个page内，当其增长到超过page一半时，变长的列就选择在另外的page保存，表对象的数据经过一段时间的增删改操作极有可能出现存储上的碎片。也就是说数据在磁盘上的物理顺序并不相连或相邻各记录之前存在着或大或小的空闲空间。

关于联机DDL
就地进行（In-Place）
复制表（Copies Tables）
允许同时执行DML（Allows Concurrent DML）
允许同时执行查询（Allows Concurrent Queries）
备注：（Notes）
表联机DDL的场景描述（略）

联机DDL相关语句语法：
alter table中的lock和algorithm来明确控制联机DDL时的操作行为。其中lock子句对于表并行度控制的微调比较有效，而algorithm子句则对于操作时的性能和操作策略有较大影响。
lock子句用于控制表变更期间读写并发粒度它有4个选项：
Default：默认处理策略，等同于不指定lock子句
None：不适用锁定策略这种情况下其他会话既能读也能写。
Shared：采取共享锁定策略，这种情况下其他会话可读但不可写。
Exclusive：采取排它锁定，这种情况下其他会话不能读也不能写。
Algorithm子句3个选项值：
Default：由mysql按照默认方式处理，相当于不指定Algorithm子句，如果指定了Algorithm子句的值为Default，则跟不指定没有区别。
Inplace：如果支持直接对当前表对象做修改，则直接就地修改，对于联机DDL语句最好是能够inplace，但是前提是操作确实支持inplace，如果对于不支持inplace的操作或引擎指定Algorithm值为inplace，则语句执行就会报错
Copy：不管是否支持就地修改，都采取将表中数据新复制一份的方式修改，这是在联机DDL被引入前的操作方式。毫无疑问这种方式成本高代价大，执行时间长。尽可能避免此类情况发生。不过某些情况下，可能必须采取Copy方式，比如说冲定义主键的情况等。

联机DDL测试：
system@5ienet>use jason;
Database changed
system@jason>set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

system@jason>create table t_idb_big as select * from information_schema.columns;
Query OK, 1712 rows affected (0.17 sec)
Records: 1712 Duplicates: 0 Warnings: 0
system@jason>insert into t_idb_big select * from t_idb_big;
Query OK, 1712 rows affected (0.13 sec)
Records: 1712 Duplicates: 0 Warnings: 0

system@jason>insert into t_idb_big select * from t_idb_big;
Query OK, 3424 rows affected (0.04 sec)
Records: 3424 Duplicates: 0 Warnings: 0

system@jason>insert into t_idb_big select * from t_idb_big;
Query OK, 6848 rows affected (0.10 sec)
Records: 6848 Duplicates: 0 Warnings: 0

system@jason>insert into t_idb_big select * from t_idb_big;
Query OK, 13696 rows affected (0.20 sec)
Records: 13696 Duplicates: 0 Warnings: 0

system@jason>insert into t_idb_big select * from t_idb_big;
Query OK, 27392 rows affected (0.40 sec)
Records: 27392 Duplicates: 0 Warnings: 0

system@jason>insert into t_idb_big select * from t_idb_big;
Query OK, 54784 rows affected (0.80 sec)
Records: 54784 Duplicates: 0 Warnings: 0

system@jason>insert into t_idb_big select * from t_idb_big;
Query OK, 109568 rows affected (1.50 sec)
Records: 109568 Duplicates: 0 Warnings: 0

system@jason>alter table t_idb_big add id int unsigned not null primary key auto_increment;
Query OK, 0 rows affected (3.58 sec)
Records: 0 Duplicates: 0 Warnings: 0
最后一个DDL语句会做隐式提交，查询下对象中记录数：
system@jason>select count(0) from t_idb_big;
+----------+
| count(0) |
+----------+
| 219136 |
+----------+
1 row in set (0.07 sec)

测试增删索引：
查看表对象数据文件占用物理空间
-rw-rw----. 1 mysql mysql 14K 4月 11 15:06 t_idb_big.frm
-rw-rw----. 1 mysql mysql 40M 4月 11 15:07 t_idb_big.ibd
占用约40M，接下来执行创建你索引的命令，明确指定Algorithm参数，使其执行就地操作以减少I/O量：
system@jason>alter table t_idb_big add index ind_data_type(data_type),algorithm=inplace;
Query OK, 0 rows affected (0.77 sec)
Records: 0 Duplicates: 0 Warnings: 0
从语句执行的速度看没有影响任何记录条目，而且在不足1秒的时间内即完成了22万条记录的表索引创建，速度尚可。
进一步查看该对象文件占用的物理空间：
-rw-rw----. 1 mysql mysql 14K 4月 11 15:17 t_idb_big.frm
-rw-rw----. 1 mysql mysql 48M 4月 11 15:17 t_idb_big.ibd
大小将近50M了，这主要是索引占用的空间，接下来我们再将该索引删除执行命令：
system@jason>alter table t_idb_big drop index ind_data_type,algorithm=inplace;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
可以看到使用inplace这种方式的效率是非常高的。再看文件大小
-rw-rw----. 1 mysql mysql 14K 4月 11 15:21 t_idb_big.frm
-rw-rw----. 1 mysql mysql 48M 4月 11 15:21 t_idb_big.ibd
可看到即使索引被删除但空间并没有释放。这是InnoDB机制决定的。除非表被删除或重建，否则已分配的空间不会释放回操作系统层。

测试对比Copy方式，继续创建索引改用create语句创建索引：
system@jason>create index ind_data_type on t_idb_big(data_type) algorithm=copy;
Query OK, 219136 rows affected (3.67 sec)
Records: 219136 Duplicates: 0 Warnings: 0
这次操作时间就长多了，从返回结果看到20几万的数据全部处理个遍，开销所以大。在看文件大小：
-rw-rw----. 1 mysql mysql 14K 4月 11 15:39 t_idb_big.frm
-rw-rw----. 1 mysql mysql 52M 4月 11 15:39 t_idb_big.ibd
空间也有所增大。删除索引：drop index ind_data_type on t_idb_big algorithm=copy;
system@jason>drop index ind_data_type on t_idb_big algorithm=copy;
Query OK, 219136 rows affected (3.06 sec)
Records: 219136 Duplicates: 0 Warnings: 0
开销也不小，所用记录都要处理。不过优点相当于copy方式相当于表对象重建，原有删除记录占用的磁盘空间重建后将返还回操作系统，可以在一定程度上实现提高空间利用率的目的。

测试增删索引过程中DML操作：
增加表的的记录量，使之操作的执行时间能够更长一些。
system@jason>alter table t_idb_big drop id;
Query OK, 219136 rows affected (2.93 sec)
Records: 219136 Duplicates: 0 Warnings: 0
system@jason>insert into t_idb_big select * from t_idb_big;
Query OK, 219136 rows affected (2.88 sec)
Records: 219136 Duplicates: 0 Warnings: 0

system@jason>insert into t_idb_big select * from t_idb_big;
Query OK, 438272 rows affected (5.91 sec)
Records: 438272 Duplicates: 0 Warnings: 0

system@jason>alter table t_idb_big add id int unsigned not null primary key auto_increment;
Query OK, 0 rows affected (19.61 sec)
Records: 0 Duplicates: 0 Warnings: 0

创建两个会话，第一个会话执行DDL语句，第二个会话在第一个会话操作工程中执行DML语句。
修改表结构在第一个会话中执行DDL语句执行命令如下：
set old_alter_table=1;
create index ind_tablename on t_idb_big(table_name);
在此过程中另一个会话执行下列操作：
set autocommit=0;
use jason;
select count(0) from t_idb_big where table_name='FILES';
delete from t_idb_big where table_name='FILES';
rollback;

会话1：
system@jason> set old_alter_table=1;
Query OK, 0 rows affected (0.00 sec)

system@jason>create index ind_tablename on t_idb_big(table_name);
Query OK, 876544 rows affected (17.49 sec)
Records: 876544 Duplicates: 0 Warnings: 0
会话2：
ystem@jason>set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

system@jason>use jason;
Database changed
system@jason>select count(0) from t_idb_big where table_name='FILES';
+----------+
| count(0) |
+----------+
| 19456 |
+----------+
1 row in set (0.46 sec)

system@jason>delete from t_idb_big where table_name='FILES';
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
system@jason>rollback;
Query OK, 0 rows affected (0.00 sec)
这个实验过程中大家就会发现执行到delete语句时就被阻塞，因为表对象正被重建。
同样场景引入联机DDL后的情况：
system@jason>drop index ind_tablename on t_idb_big;
Query OK, 876544 rows affected (11.77 sec)
Records: 876544 Duplicates: 0 Warnings: 0
set old_alter_table=0;
create index ind_tablename1 on t_idb_big(table_name) algorithm=inplace;
会话二：
select count(0) from t_idb_big where table_name='FILES';
delete from t_idb_big where table_name='FILES';
rollback;
此时第一个会话还没执行完会话二可以联机DDL

修改列，copy机制修改列，这种方式类似传统的处理机制：
alter table t_idb_big change is_nullable nullable varchar(3), algorithm=copy;
system@jason>alter table t_idb_big change is_nullable nullable varchar(3), algorithm=copy;
Query OK, 876544 rows affected (7 hours 54 min 21.96 sec)
Records: 876544 Duplicates: 0 Warnings: 0
这种方式会锁表，二期处理时间极长。DDL语句执行过程中其他会话无法修改表中的数据，影响并发。
使用联机DDL就地修改方式：
alter table t_idb_big change nullable is_nullable varchar(3), algorithm=inplace;
system@jason>alter table t_idb_big change nullable is_nullable varchar(3), algorithm=inplace;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
使用联机DDL就地修改方式修改表字段时间极明显缩短，最关键的是操作过程中DML语句可以同时读写表中的数据，并行性能不会受到影响。
注意不是说所有列的变更都不影响DML语句并行读写，而只有当修改列时只修改了列名，数据类型定义未发生改变的情况下才能实现同时执行DML语句读写该对象。

测试修改自增列：联机就地DDL方式修改自增列的值：
system@jason>alter table t_idb_big auto_increment=1000000,algorithm=inplace;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
执行时间超级快，这是因为它不需要重建对象，只需修改.frm文件中的标记和内存中的自增值就可以。完全不需要动表中的数据。

copy方式修改自增列：
system@jason>alter table t_idb_big auto_increment=1000000,algorithm=copy;
Query OK, 876544 rows affected (17.84 sec)
Records: 876544 Duplicates: 0 Warnings: 0
执行时间相对较长。

lock子句用于控制表变更期间读写并发粒度它有4个选项：
LOCK=None：不适用锁定策略这种情况下其他会话既能读也能写。可查询可更新。
LOCK=Shared：采取共享锁定策略，这种情况下其他会话可读但不可写。可查询不可更新。
LOCK=Exclusive：采取排它锁定，这种情况下其他会话不能读也不能写。不可查询和更新
lock子句在粒度控制上与Algorithm子句有一定的关联，但优先级更高，比如不管Algorithm是否允许DML读写，当指定lock=exclusive时该对象就会是既不允许同时查询也不允许更新。

lock子句指定为none时的情况：
会话1：create index ind_tablename2 on t_idb_big(table_name) lock=none;
会话2：set autocommit=0;
select count(0) from t_idb_big where table_name='TABLES';
会话3：set autocommit=0;
update t_idb_big set table_name='NEWTABLES' where table_name='TABLES';
会话4：show processlist;

会话2：system@jason>select count(0) from t_idb_big where table_name='TABLES';
+----------+
| count(0) |
+----------+
| 10752 |
+----------+
1 row in set (0.06 sec)
会话3：system@jason>update t_idb_big set table_name='NEWTABLES' where table_name='TABLES';
Query OK, 10752 rows affected (0.50 sec)
Rows matched: 10752 Changed: 10752 Warnings: 0

LOCK子句指定为shared时的情况：
会话1：create index ind_tablename2 on t_idb_big(table_name) lock=shared;
会话2：set autocommit=1;
select count(0) from t_idb_big where table_name='TABLES';
会话3：set autocommit=0;
update t_idb_big set table_name='NEWTABLES' where table_name='TABLES';
会话4：show processlist;
从返回结果看查询执行极快，会话3的update操作显示更新操作在等待元数据锁定，因为此时会话1仍在执行中。过片刻从processlist中看出会话1的DDL语句执行完后，会话3的更新语句终于开始updating了。这也说明lock=shared时只需查询不许修改（DDL持有了表锁）

LOCK子句指定为exclusive时的情况：
会话1：create index ind_tablename2 on t_idb_big(table_name) lock=exclusive;
会话2：set autocommit=1;
select count(0) from t_idb_big where table_name='TABLES';
会话3：set autocommit=0;
update t_idb_big set table_name='NEWTABLES' where table_name='TABLES';
会话4：show processlist;
从返回结果select和update语句都被阻塞，提示等待表的元数据锁定。说明LOCK子句指定为exclusive时不可更新不可查询。

<>InnoDB表对象的限制条件：（了解）参考7.4.7章节
1、最大最小
2、制约因素
3、锁和事务

码农公寓

相关文章