浅谈MYSQL存储引擎及锁机制
Mysql存储引擎
- Mysql支持的存储引擎
show engines
MariaDB [(none)]> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| CSV | YES | Stores tables as CSV files | NO | NO | NO |
| MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | YES | Non-transactional engine with good performance and small data footprint | NO | NO | NO |
| SEQUENCE | YES | Generated tables filled with sequential values | YES | NO | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES | YES | YES |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
- 查看当前默认的存储引擎
MariaDB [(none)]> SHOW VARIABLES LIKE '%STORAGE_ENGINE%';
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | |
| enforce_storage_engine | |
| storage_engine | InnoDB |
+----------------------------+--------+
4 rows in set (0.00 sec)
- 设置默认的存储引擎
vim /etc/my.cnf
[mysqld]
default_storage_engine=innodb
- 查看库中所有表以及指定表使用的存储引擎
MariaDB [(none)]> SHOW TABLE STATUS FROM wordpress\G;
*************************** 1. row ***************************
Name: user
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 1
Create_time: 2019-11-23 15:12:06
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
ERROR: No query specified
MariaDB [(none)]> SHOW CREATE TABLE wordpress.user;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user | CREATE TABLE `user` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`USER` varchar(20) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
- 设置表的存储引擎
CREATE TABLE table_name(...)ENGINE=INNODB;
ALTER TABLE table_name ENGINE=INNODB;
MYISAM 跟 InnoDB的区别
- MyISAM应用于不需要事务或执行大量的SELECT或COUNT操作的场景中
- InnoDB应用于对数据一致性要求较高，需保证ACID事务操作或执行大量的INSERT或UPDATE操作的场景中
锁机制
- 锁粒度：
- 表级锁：直接锁定整张表，在锁定期间，其他进程无法对该表进行写操作，如果设置的是写锁，那么其他进程读也不允许
- 行级锁：只对指定的行进行锁定，其他进程还是可以对表中的其他行进行操作的。
行级锁是Mysql粒度最小的一种锁，它能大大的减少数据库操作的冲突，但是粒度越小实现成本也越大
- 锁类型：
- 读锁：共享锁，只读不可写（包括当前事务） ，多个读互不阻塞
- 写锁：独占锁，排它锁，写锁会阻塞其它事务（不包括当前事务）的读和写
- 锁实现
- 存储引擎：自行实现其锁策略和锁粒度
- 服务器级：实现了锁，表级锁，用户可显式请求
- 锁分类：
- 隐式锁：由存储引擎自动施加锁
- 显式锁：用户手动请求
- 锁策略：在锁粒度及数据安全性寻求的平衡机制
显示锁的使用
- 加锁
LOCK TABLES
tbl_name [[AS] alias] lock_type
[, tbl_name [[AS] alias] lock_type] ...
lock_type: READ ， WRITE
- 解锁
UNLOCK TABLES
- 关闭正在打开的表（清除查询缓存），通常在备份前加全局读锁
FLUSH TABLES [tb_name[,...]] [WITH READ LOCK]
- 查询时加写或读锁
SELECT clause [FOR UPDATE | LOCK IN SHARE MODE]
实验：读锁与写锁的差异
创建读锁与写锁
- 创建一个读锁
MariaDB [hellodb]> LOCK TABLES classes read;
Query OK, 0 rows affected (0.00 sec)
- 远程主机读取这个表的数据
MariaDB [hellodb]> SELECT * FROM classes;
+---------+--------------------+----------+
| ClassID | Class | NumOfStu |
+---------+--------------------+----------+
| 1 | Shaolin Pai | 10 |
| 2 | Emei Pai | 7 |
| 3 | QingCheng Pai | 11 |
| 4 | Wudang Pai | 12 |
| 5 | Riyue Shenjiao | 31 |
| 6 | Lianshan Pai | 27 |
| 7 | Ming Jiao | 27 |
| 8 | Xiaoyao Pai | 15 |
| 9 | Baijian Shanzhuang | 6 |
+---------+--------------------+----------+
9 rows in set (0.00 sec)
# 读取此表数据不受任何影响
- 远程主机插入数据
mysql> INSERT INTO classes VALUES(10,1,1);
.
.
.
# 因为有锁无法继续执行操作
- 本机释放锁
UNLOCK TABLES
- 远程主机未执行完成的数据会立即执行
Query OK, 1 row affected (23.08 sec)
mysql> SELECT * FROM classes;
+---------+--------------------+----------+
| ClassID | Class | NumOfStu |
+---------+--------------------+----------+
| 1 | Shaolin Pai | 10 |
| 2 | Emei Pai | 7 |
| 3 | QingCheng Pai | 11 |
| 4 | Wudang Pai | 12 |
| 5 | Riyue Shenjiao | 31 |
| 6 | Lianshan Pai | 27 |
| 7 | Ming Jiao | 27 |
| 8 | Xiaoyao Pai | 15 |
| 9 | Baijian Shanzhuang | 6 |
| 10 | 1 | 1 |
+---------+--------------------+----------+
10 rows in set (0.00 sec)
# 读锁解除后，数据顺利插入
- 创建一个显式写锁
MariaDB [hellodb]> LOCK TABLES classes write;
Query OK, 0 rows affected (0.00 sec)
- 远程主机查看此表数据
mysql> SELECT * FROM classes;
.
.
# 卡住等待锁的释放
- 释放显式写锁
UNLOCK TABLES;
- 远程主机查看此表数据，以及时间
mysql> SELECT * FROM classes;
+---------+--------------------+----------+
| ClassID | Class | NumOfStu |
+---------+--------------------+----------+
| 1 | Shaolin Pai | 10 |
| 2 | Emei Pai | 7 |
| 3 | QingCheng Pai | 11 |
| 4 | Wudang Pai | 12 |
| 5 | Riyue Shenjiao | 31 |
| 6 | Lianshan Pai | 27 |
| 7 | Ming Jiao | 27 |
| 8 | Xiaoyao Pai | 15 |
| 9 | Baijian Shanzhuang | 6 |
| 10 | 1 | 1 |
+---------+--------------------+----------+
10 rows in set (2 min 0.37 sec)
# 上一行的时间表示了，写锁解除后，数据继续查询
创建全局锁
- 通过FLUSH创建全局读锁
MariaDB [hellodb]> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
- 远程主机查询插入数据
mysql> SELECT * FROM classes;
+---------+--------------------+----------+
| ClassID | Class | NumOfStu |
+---------+--------------------+----------+
| 1 | Shaolin Pai | 10 |
| 2 | Emei Pai | 7 |
| 3 | QingCheng Pai | 11 |
| 4 | Wudang Pai | 12 |
| 5 | Riyue Shenjiao | 31 |
| 6 | Lianshan Pai | 27 |
| 7 | Ming Jiao | 27 |
| 8 | Xiaoyao Pai | 15 |
| 9 | Baijian Shanzhuang | 6 |
| 10 | 1 | 1 |
+---------+--------------------+----------+
10 rows in set (0.01 sec)
mysql> INSERT INTO classes VALUES(11,1,1);
.
.
# 写操作被阻塞等待锁的释放
- 释放全局读锁
UNLOCK TABLES;
mysql> INSERT INTO classes VALUES(11,1,1);
Query OK, 1 row affected (2 min 15.55 sec)
# 时间表名是锁被释放后，操作继续进行
总结差异
- 读锁允许其他进程对表进行读操作，不允许写操作；
- 写锁不允许其他进程对表进行读写操作
- SQL语言的运行时间除了与复杂性有关外，还受数据库所处的环境影响（例如大量写操作的环境下，很难迅速通过FLUSH命令添加读锁）
FLUSH TABLES WITH READ LOCK全局只读锁可以用来维护数据库
1418
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
