MySQL 存储引擎（九）

9、mysql 存储引擎

9.1、回顾

1. 存储过程：是 SQL 语句和控制语句的预编译集合，以一个名称存储并作为一个单元出来
2. 参数：输入类型、输出类型、输入&输出类型
3. 创建：CREATE...PROCEDURE...
4. 注意事项：
   • 创建存储过程或自定义函数时，需要通过 delimiter 来修改定界符
   • 如果函数体或过程体有多个语句，需要包含在 BEGIN...END 语句块中
   • 存储过程通过 call 来调用

9.2、存储引擎简介

mysql 可以将数据以不同的技术存储在文件（内存）中，这种技术称为存储引擎（存储、查询数据的技术）。

存储引擎使用不同的存储机制、索引技巧、锁定水平，最终提供广泛且不同的功能。

支持的存储引擎

• MyISAM
• InnoDB
• Memory
• CSV
• Archive

9.3、相关知识点之并发处理

并发控制

当多个连接对记录进行修改时保证数据的一致性和完整性（当多个用户对同一条记录进行读或写操作时，为保证数据的一致性和完整性）。系统使用锁系统来解决这个并发控制，这种锁分为：

  &nb当多个连接对记录进行修改时保证数据的一致性和完整性（当多个用户对同一条记录进行读或写操作时，为保证数据的一致性和完整性）。系统使用锁系统来解决这个并发控制，这种锁分为：

• 共享锁（读锁）：在同一时间内，多个用户可以读取同一个资源，读取过程中数据不会发生任何变化。
• 排他锁（写锁）：在任何时候只能有一个用户写入资源，当进行写锁时会阻塞其他的读锁或者写锁操作。

锁颗粒

锁的力度（锁的颗粒）：锁定时的单位。无需对所有资源加锁，只需对需要修改的资源加锁即可，为寻求平衡就需要使用合适的锁策略：

• 表锁：是一种开销最小的锁策略。得到数据表的写锁（禁止其他用户进行读写，只能有一个锁）

• 行锁：是一种开销最大的锁策略。并行性最大（有多少条记录，就有可能对每条记录都进行锁）

表锁的开销最小，因为使用锁的个数最小，行锁的开销最大，因为可能使用锁的个数比较多。

9.4、相关知识点之事务处理

1. 事物：用于保证数据库的完整性
2. 事物的特性：

• 原子性（Atomicity）
• 一致性（Consistency）
• 隔离性（Isolation）
• 持久性（Durability）

9.5、相关知识点之外键和索引

1. 外键：是保证数据一致性的策略

2. 索引：是对数据表中一列或多列的值进行排序的一种结构（分为普通索引、唯一索引、全文索引、btree 索引和 hash 索引）。

9.6、各个存储引擎特点

各个存储引擎特点

特点	MyISAM	InnoDB	Memory	Archive
存储限制	256TB	64TB	有	无
事务安全	-	支持	-	-
支持索引	支持	支持	支持
锁颗粒	表锁	行锁	表锁	行锁
数据压缩	支持	-	-	支持
支持外键	-	支持	-	-

1. CSV 引擎：

CSV 引擎是由逗号分隔的存储引擎，会在数据库的子目录里为每一个表创建一个（.csv）的文件，这是一种普通的文本文件。每一个数据行占用一个文本行，CSV 不支持索引。

2. BlackHole （黑洞）引擎：写入的数据都会消失，一般用于做数据复制的中继。

3. MyISAM 引擎：适用于事务的处理不多的情况。

4. InnoDB 引擎：适用于事务处理比较多，需要有外键支持的情况。

9.7、设置存储引擎

9.7.1、查看和修改 mysql 存储引擎

查看 mysql 存储引擎

# mysql 5.5 以上默认为 InnoDB
show engines;  # Support列， YES表示当前版本支持这个存储引擎， DEFAULT表示该引擎是默认的引擎。NO表示不支持该存储引擎。

方法一：通过修改 mysql 配置文件实现（永久有效）

# windows：找到（my.ini配置文件）C:\ProgramData\MySQL\MySQL Server 5.7，在配置文件后添加一句：
# mac：找到 my.conf 文件
# 修改完保存，重启 mysql 服务（net stop/start mysql 或者在服务里手动重启）
[mysqld]
default-storage-engine = MyISAM

方法二：使用系统命令修改（临时有效，重启失效）

SET default_storage_engine=InnoDB;

9.7.2、查看和修改数据表使用的存储引擎

方法一：查询 information_schema.TABLES

# 查询information_schema.TABLES
mysql> SELECT TABLE_SCHEMA,        # 数据表所属的数据库名（schema 可以看作为 databases）
    -> TABLE_NAME,            # 数据表名
    -> TABLE_TYPE,        # 数据表类型
    -> ENGINE          # 引擎
    -> FROM information_schema.TABLES
    -> WHERE TABLE_NAME = 'tb_4';
+--------------+------------+------------+--------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE |
+--------------+------------+------------+--------+
| test         | tb_4       | BASE TABLE | InnoDB |
+--------------+------------+------------+--------+
1 row in set (0.00 sec)

方法二：使用 SHOW CREATE TABLE tb_name\G 命令

# 两个都可以
SHOW CREATE TABLE tb_name\G         
SHOW CREATE TABLE tb_name;

方法三：SHOW TABLE STATUS

 SHOW TABLE STATUS WHERE Name = 'tb_4';   # 最好设置查询条件，否则数据库中的数据表状态信息全都显示出来

修改数据表的存储引擎

ALTER TABLE tb_name ENGINE = InnoDB;

9.7.3、创建表的时候如何指定存储引擎

CREATE TABLE tb_name(
    id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(20)
) ENGINE = InnoDB;
SET NAMES = gbk;      # 设置编码方式

9.8、mysql 中 information_schema.tables 字段说明

information_schema.tables 可以获取数据表的结构

字段	含义
table_catalog	数据表等级目录
table_schema	数据表所属数据库名
table_name	表名
table_type	数据表类型
engine	存储引擎
version	版本信息
row_format	行格式（查看数据表是否压缩过）
table_rows	表里记录行数
avg_row_length	平均行长度
data_length	数据长度
max_data_length	最大数据长度
index_length	索引长度
data_free	空间碎片
auto_increment	自动递增（与主键配合）当前值
create_time	表创建时间
update_time	表更新时间
check_time	表检测时间
table_collation	表的字符校验编码集
checksum	校验和
create_options	创建选项
table_comment	表的注释、备注