MySQL存储引擎
存储引擎就是一种数据存取和处理方式；MySQL存储引擎是一种插件式设计方式。
1.查看MySQL支持什么存储引擎
mysql> 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: MRG_MYISAM Support: YES Comment: Collection of identical MyISAM tables Transactions: NO XA: NO Savepoints: NO *************************** 3. row *************************** Engine: MEMORY Support: YES Comment: Hash based, stored in memory, useful for temporary 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: MyISAM Support: YES Comment: MyISAM storage engine Transactions: NO XA: NO Savepoints: NO *************************** 6. row *************************** Engine: CSV Support: YES Comment: CSV storage engine 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: PERFORMANCE_SCHEMA Support: YES Comment: Performance Schema 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：当前是否支持；
Comment：存储引擎的简要描述；
Transactions：是否支持事务；
XA：是否支持分布式事务；
Savepoints：是否支持保存点；
2.MEMORY存储引擎
mysql> create table t_mem(id int) engine=memory; Query OK, 0 rows affected (0.00 sec) mysql> show create table t_mem; +-------+-------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------------+ | t_mem | CREATE TABLE `t_mem` ( `id` int(11) DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1 | +-------+-------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
当MySQL服务关闭时候，MEMORY引擎中的数据都丢失，当然，表结构会保留。分配给MEMORY引擎的内存正常状态下不会释放，而是右MEMORY引擎一致持有，即使删除MEMORY引擎表中的数据，内存也不会被回收，只有表重建或删除时候才会被回收。MEMORY表能够使用的最大内存空间不能超过max_heap_table_size,默认是16M
3.CSV存储引擎
CSV（Comma Separated Values逗号分割）
mysql> create table t_csv1(id int not null,v1 varchar(20) not null) engine=csv; Query OK, 0 rows affected (0.01 sec) mysql> show create table t_csv1; +--------+------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+------------------------------------------------------------------------------------------------------------------+ | t_csv1 | CREATE TABLE `t_csv1` ( `id` int(11) NOT NULL, `v1` varchar(20) NOT NULL ) ENGINE=CSV DEFAULT CHARSET=latin1 | +--------+------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
mysql> insert into t_csv1 values(1,'ss');
Query OK, 1 row affected (0.01 sec)
可以查看数据文件
[root@localhost gis]# ls
db.opt gisusers.ibd t3.ibd t4.ibd t5.ibd t_csv1.CSV test.frm t_mem.frm t_tx_zwyc_wlg.ibd
gisusers.frm t3.frm t4.frm t5.frm t_csv1.CSM t_csv1.frm test.ibd t_tx_zwyc_wlg.frm
[root@localhost gis]# cat t_csv1.CSV
1,"ss"
也可以直接编辑数据文件
所有列都必须指定NOT NULL，CSV不支持索引，不支持分区；
4.ARCHIVE存储引擎
基于归档存储引擎的对象，能够将大量数据压缩存储，插入的列会被压缩，ARCHIVE引擎使用zlib无损压缩所发，并且可以使用OPTIMIZE TABLE分析表并使用打包成更小的格式。
mysql> create table t_arc engine=archive as select * from gisusers;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t_arc\G *************************** 1. row *************************** Table: t_arc Create Table: CREATE TABLE `t_arc` ( `username` varchar(20) DEFAULT NULL, `sex` tinyint(4) DEFAULT NULL, `birth` date DEFAULT NULL, `address` varchar(50) DEFAULT NULL, `phone` varchar(15) DEFAULT NULL, `email` varchar(20) DEFAULT NULL ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 1 row in set (0.00 sec)
ARCHIVE仅支持insert和select语句，不支持update，delete，replace语句，能够支持order by语句，能支持行级别锁，不支持索引，插入效率搞，占用空间小。
5.BLACKHOLE存储引擎
BLACKHOLE存储引擎插入的数据都不会保存，永远保持为空。
mysql> create table t_bho(id int) engine=blackhole; Query OK, 0 rows affected (0.01 sec) mysql> insert into t_bho values(22); Query OK, 1 row affected (0.00 sec) mysql> select * from t_bho; Empty set (0.00 sec)
6.MERGE引擎
MERGE引擎也称为MGR_MyISAM引擎，它实际上是将一组MyISAM引擎表聚合在一起，使用时候就像一张表，以此来简化查询操作，MERGE引擎表要求基表拥有相同的列和索引信息，甚至列的定义和顺序，索引的定义和顺序都必须一致。
mysql> create table t_merge1(id int not null primary key,v1 varchar(20)) engine=myisam; Query OK, 0 rows affected (0.00 sec) mysql> create table t_merge2(id int not null primary key,v1 varchar(20)) engine=myisam; Query OK, 0 rows affected (0.01 sec) mysql> insert into t_merge1 values(1,'aa'); Query OK, 1 row affected (0.00 sec) mysql> insert into t_merge2 values(2,'bb'); Query OK, 1 row affected (0.01 sec)
mysql> create table t_merge3 (id int not null primary key,v1 varchar(20)) engine=merge union(t_merge1,t_merge2);
Query OK, 0 rows affected (0.01 sec)
mysql> select * from t_merge3;
+----+------+
| id | v1 |
+----+------+
| 1 | aa |
| 2 | bb |
+----+------+
2 rows in set (0.00 sec)
MERGE引擎并不存储数据，它只是起一个汇总作用，也可以执行插入，修改，删除，不过在自行插入的时候要指定默认的表：
mysql> alter table t_merge3 insert_method=first; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> insert into t_merge3 values(4,'CC'); Query OK, 1 row affected (0.00 sec) mysql> select * from t_merge3; +----+------+ | id | v1 | +----+------+ | 1 | aa | | 4 | CC | | 2 | bb | +----+------+ 3 rows in set (0.00 sec) mysql> select * from t_merge1; +----+------+ | id | v1 | +----+------+ | 1 | aa | | 4 | CC | +----+------+ 2 rows in set (0.00 sec)
7.FEDERATED存储引擎
访问远端MySQL数据库实例中的对象。
8.MyISAM存储引擎
mysql> create table t_myisam2(id int) engine=myisam;
Query OK, 0 rows affected (0.01 sec)
对象创建后，在物理层面，咩歌MyISAM表对象都有3个独立的操作系统文件组成，文件名与表名相同，文件的扩展名下面3个类型：
.frm：对象结构定义文件，用于存储表对象结构；
.YMD：数据文件，用于存储数据表；
.YMI：索引文件：用户存储表的索引文件
-rw-r----- 1 mysql mysql 8556 5月 14 23:04 t_myisam2.frm -rw-r----- 1 mysql mysql 0 5月 14 23:04 t_myisam2.MYD -rw-r----- 1 mysql mysql 1024 5月 14 23:04 t_myisam2.MYI
MyISAM引擎特性：
MySQL自带的mysql和information_schema两个系统数据库，其内部表对象就是使用MyISAM引擎；
缺点是：粒度太粗，不支持事务；
优点：查询块，读写块；
MyISAM引擎存储格式：
在存储数据的时候MyISAM支持3中不同的存储格式：定长（FIXED，也称为静态）、动态（DYNAMIC）和压缩（COMPRESSED），其中前两种不需要指定，会在创建对象的时候根据列的类型自动适配(如果没有BLOB或TEXT类型列的话)也可以在执行CREATE TABLE或者ALTER TABLE语句时候，通过ROW_FORMAT选项强制指定，第三种用专用工具-myisampack创建。
所谓静态格式表，表中不包含变长类型的列（varchar、varbinary、blob、text）定义每一列保存的均是固定的字节数。
mysql> show table status like 't_myisam3%'\G *************************** 1. row *************************** Name: t_myisam3 Engine: MyISAM Version: 10 Row_format: Fixed Rows: 0 Avg_row_length: 0 Data_length: 0 Max_data_length: 4222124650659839
用ROW_FORMAT选项，可以指定表的对象为静态表：
mysql> create table t_myisam4(id int,name varchar(20)) ROW_FORMAT=FIXED engine=myisam; Query OK, 0 rows affected (0.01 sec) mysql> show table status like 't_myisam4%'\G *************************** 1. row *************************** Name: t_myisam4 Engine: MyISAM Version: 10 Row_format: Fixed Rows: 0
静态表的特点：
对于CHAR、VARCHAR类型的列会填充到定义的长度；
较快、易于缓存；
易于崩溃重建、因为记录保存的位置是固定的；
一般不需要重建；
通常比动态格式的表占用更多的磁盘空间；
动态表：
包含变长的字符类型,也可以用ROW_FORMAT指定，但是如果有BLOB、TEXT类型的无法指定为静态的表。
动态表的特点：
除了字符串长度小于4的列外，其他字符列长度都是动态的；
每行记录了的行头都有一个bitmap，标识该行那些类包含空自妇产；
相对静态表，相同列长定义的情况下，有可能会省一定空间；
每行仅存储实际需要的空间，如果记录变大，有可能会分片保存，这也是行碎片产生的原有；
相对静态表恢复更复杂。
mysql> create table t_myisam6(id int,name varchar(20)) engine=myisam; Query OK, 0 rows affected (0.01 sec) mysql> show table status like 't_myisam6%'\G *************************** 1. row *************************** Name: t_myisam6 Engine: MyISAM Version: 10 Row_format: Dynamic Rows: 0 Avg_row_length: 0
9.InnoDB存储引擎
InnoDB存储引擎有.frm表结构定义文件外，还有一个同名的.ibd文件，这个文件就是表空间文件；
InnoDB存储引擎特性
- 遵循ACID模型，支持事务，拥有从服务崩溃中恢复的能力，能够最大限度的保护数据；
- ACID即事务的四个特性：原子性（Atomiocity）、一致性（Consistency）、隔离性（Isolation）、持久性（Druability）；
- 支持行级锁；
- InnoDB引擎表组织数据时候按照主键聚簇；
- InnoDB拥有自己独立的缓冲池；
InnoDB存储引擎配置：
- 指定InnoDB引擎数据文件保存路径；可以通过 innodb_data_home_dir=/data/mysqldata/3306/innodb_ts 也可以 innodb_data_file_path=/data/mysql/3306/innodb-ts/ibdata01.df:2048M:autoextend:max:100G
- 设置多重表空间，每个表对象拥有独享的.ibd为扩展名的数据文件； set global innodb_file_table=1 来控制，设置为1表示启用多重表空间，设置为0表示关闭。
- 配置InnoDB日志文件： innoDB_log_group_home_dir innodb_log_file_size innodb_log_files_in_group
innodb_fast_shutdown参数：这是系统用来控制Innodb的关闭模式，共有0、1、2三种模式，默认是1，这种模式下，InnoDB将关闭会话终止连接，将已经提交的数据刷新志数据文件，未提交的事务回退，类似于Oracle的shutdown immediate；如果是0，则要等到所有的回话结束，所有的事务结束，缓存的数据刷新到磁盘类似Oracle的shutdown normal，模式如果是2，则直接关闭，类似Oracle的shutdown abort；
查看数据库innodb_fast_shutdown设置：
mysql> show global variables like '%innodb_fast_shutdown%'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | innodb_fast_shutdown | 1 | +----------------------+-------+ 1 row in set (0.01 sec)
修改l日志的大小及数量
原来的文件：
-rw-r----- 1 mysql mysql 48M 5月 17 21:59 ib_logfile0 -rw-r----- 1 mysql mysql 48M 5月 4 21:50 ib_logfile1
[root@localhost mysql]# systemctl stop mysqld
[root@localhost mysql]# vi /etc/my.cnf
innodb_log_file_size=128M
innodb_log_files_in_group=3
启动数据库：
-rw-r----- 1 mysql mysql 134217728 5月 18 02:06 ib_logfile0
-rw-r----- 1 mysql mysql 134217728 5月 18 02:06 ib_logfile1
-rw-r----- 1 mysql mysql 134217728 5月 18 02:06 ib_logfile2
设置独立的UNDO表空间
innodb_undo_directory:用于指定保存物理文件的位置；
innodb_undo_tablespaces;用户指定undo表空间的数量，每个表空间都是独立的.idb文件；
设置事务支持
禁用事务自动提交
回话级别的设置：
mysql> show variables like 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+ 1 row in set (0.00 sec) mysql> set autocommit=off; Query OK, 0 rows affected (0.00 sec)
mysql> insert into t3 values(22);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t3;
+------+
| id |
+------+
| 22 |
+------+
1 row in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from t3;
Empty set (0.00 sec)
显示的声明事务
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into t3 values(4); Query OK, 1 row affected (0.01 sec) mysql> select * from t3; +------+ | id | +------+ | 4 | +------+ 1 row in set (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> select * from t3; Empty set (0.00 sec)
10.InnoDB存储引擎逻辑存储机构
- 页（Pages，也叫块）：InnoDB中的最小管理单位，默认大小16KB，可选值有4KB，8KB，16KB三种；
- 扩展（Extend，也叫区）：每个扩展1MB大小，由64个16KB的页组成（页大小为8KB是则由128个组成，页大小为4KB时候则由256个组成）；
- 段（Segments）：段本身有很多种，比如数据段，索引段，回滚段，对于InnoDB来说，段则是独立表空间对应的数据文件；
- 表空间（Tablespaces）：表空间。
11.联机DDL测试
algorithm=inplace 就地操作，
mysql> alter table t_idb_big add index ind_data_type (data_type),algorithm=inplace; Query OK, 0 rows affected (0.36 sec) Records: 0 Duplicates: 0 Warnings: 0
删除索引，空间也不会释放；
mysql> alter table t_idb_big drop index ind_data_type,algorithm=inplace; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0
algorithm=copy 将对象中的数据重新复制一份的方式修改
mysql> create index index_ind_type on t_idb_big(data_type) algorithm=copy; Query OK, 50176 rows affected (1.50 sec) Records: 50176 Duplicates: 0 Warnings: 0
删除后，将磁盘的空间释放
mysql> drop index index_ind_type on t_idb_big algorithm=copy; Query OK, 50176 rows affected (1.07 sec) Records: 50176 Duplicates: 0 Warnings: 0
lock子句控制DML
LOCK=NONE：允许对表进行查询和跟新操作；
LOCK=SHARED：仅允许同时对表进行查询操作，不能跟新记录；
LOCK=EXCLUSIVE：不允许同时进行查询和跟新操作。
