MySQL引擎

只介绍常用MySQL引擎, 分别是MyISAM、InnoDB、Memory和CSV

MySAM是MySQL 5.5.8版本之前的默认引擎, 在MySQL 5.5.8+后的默认存储引擎是InnoDB。

采用表空间保存文件, 表空间有两种, 一直是系统表空间(ibdataN), 一种是独立表空间(tablename.ibd)。
在5.3版本后默认使用独立表空间。
设置innodb_file_per_table属性选择表空间模式

//查看表空间模式
SHOW VARIABLES LIKE 'innodb_file_per_table';

on代表使用独立表空间模式
off代表使用系统表空间

//修改表空间模式
set GLOBAL innodb_file_per_table = off;

通常表数据都存储在MySQL目录下的data目录中, 存储的文件如果是是系统表空间, 则保存形式为table_name.frm, 数据部分存放在data目录下的ibdataN文件中; 如果是独立表空间, 保存为两个文件, 分别是table_name.frm和table_name.ibd。
frm是from的缩写, frm表中存放是表定义数据, 比如字段的名称属性和约束。.ibd是独立表空间文件。

创建数据库时, 推荐使用utf8mb4, 因为MySQL的utf8和标准的utf8标准不同，只支持3字节,utf8mb4支持4字节。

在此只重点介绍InnoDB引擎

InnoDB引擎

特点:

• 支持事务
• 默认使用行级锁
• 5.7版本后支持全文检索与空间函数

MySQL锁

职责分类	粒度分类
共享锁 - 读锁	行级锁
独占/排他锁 - 写锁	表级别

行级锁和表级锁从字面就可以明白是锁的范围, 在并发情况下, 行锁是远远优于表锁的, 这也是MyISAM被淘汰的原因(MyISAM是表锁)。

在InnoDB中, 只有利用索引的更新, 删除操作才会使用行级锁, 不能使用索引的写操作则是表级锁!

MyISAM

特点:

• 不支持事务
• 支持全文检索, 支持text支持前缀检索
• 支持数据压缩
• 紧密存储, 顺序读性能好
• 表级锁

应用场景

1. 非事务应用, 例如保存日志
2. 只读类引用, 报表数据, 字段数据
3. 系统临时表,SQL查询, 分组的临时表引擎

Memory

特点:

• 不支持事务
• 内存读写, 临时存储
• 读写效率高
• 表级锁

应用场景

1. 读多写少的静态数据
2. 充当缓存使用, 保存高频访问静态资源
3. 系统临时表

此处的临时表并不是用SQL语句创建的临时表, 而是在执行诸如ORDER BY、GROUP BY 这种SQL时MySQL在内存中自动创建的中间表。如果这个SQL的中间结果超过了默认内存表的限制, 就会以MyISAM引擎存储在磁盘中。

Memory关键参数

• 设置max_heap_table_size控制内存表大小(字节)
• 设置tmp_table_size设置内存临时表最大值(字节)

CREATE TABLE memory_test(id int,name VARCHAR(6)) ENGINE MEMORY
//内存表默认大小为16M
//查看内存表大小
show VARIABLES like '%HEAP%';
//设置内存表大小,设置为2G
set GLOBAL max_heap_table_size = 2147483648
//设置临时表最大值
set GLOBAL tmp_table_size= 2147483648
//tmp_table_size 应当<= max_heap_table_size

此处修改只会临时生效, 如果要永久生效需要在my.cnf文件中新增max_heap_table_size = 2048M

CSV

特点:

• 纯文件表村
• 不支持事务
• 不支持索引