mariaDB存储引擎,并发控制,事务

数据抽象：

    物理层：决定数据的存储格式，即RDBMS在磁盘上如何组织文件；

    逻辑层：描述DB存储什么数据，以及数据间存在什么样的关系，数据库管理员看到的；

    视图层：描述DB中的部分数据；最终用户实际看到的

mysql使用插件式存储引擎：定义mysql组织数据数据的格式和磁盘存储格式如何兼容起来。存储管理器有多种实现版本，功能和特性可能均略有差别；用户可根据需要灵活选择；相比mysql：
    (1) 更多的存储的存储引擎
        MyISAM --> Aria(Mariadb上),不支持事务 
        InnoDB --> XtraDB
    (2) 诸多扩展和新特性
    (3) 提供了较多测试组件
    (4) truly open source

存储引擎是表级别的概念，每创建一张表都应该指明它的存储引擎，对同一个数据库而言，不建议交叉使用多个存储引擎。

使用SHOW TABLE STATUS\G;查看数据库用的存储引擎
InnoDB：增强版是xtraDB，mariadb默认使用xtraDB
    处理大量的短期事务；
    表空间:每种存储引擎可以唯一识别的文件系统
    数据存储于“表空间(table space)”中；
    (1) 所有InnoDB表的数据和索引放置于同一个表空间中哪怕不在同一个数据库；
        表空间文件：datadir定义的目录下
        数据文件：ibdata1, ibdata2, ...
    (2) 每个表单独使用一个表空间存储表的数据和索引；易于实现单表导入导出
        innodb_file_per_table=ON
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'innodb_file_per%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF   |
+-----------------------+-------+
1 row in set (0.00 sec)
创建t2表：
MariaDB [mydb]> CREATE TABLE t2 (id int, name char(30));
Query OK, 0 rows affected (0.01 sec)
在系统的数据目录下发现了t2表的相关数据
    数据文件(存储数据和索引)：tbl_name.ibd, 
    表格式定义：tbl_name.frm
[root@node1 mydb]# ls
db.opt  t1.frm  t2.frm
[root@node1 mydb]# pwd
/var/lib/mysql/mydb

基于MVCC来支持高并发，支持所有的四个隔离级别，默认级别为REPEATABLE READ; 间隙锁防止幻读；
    使用聚集索引
    支持“自适应hash索引”
    锁粒度：行级锁

数据存储：表空间
并发：MVCC, 间隙锁
索引：聚集索引、辅助索引
性能：预计操作、自适应hash、插入缓存区
备份：支持热备(xtrabacup)

MyISAM：
    支持全文索引(FULLTEXT index)，innodb不支持、压缩、空间函数(GIS); 但不支持事务，且为表级锁，不支持行级锁；
    崩溃后无法安全恢复
适用场景：只读（或者写较少）、表较小（可以接受长时间进行修复操作）
    Aria：crash-safe
文件：
    tbl_name.frm: 表格式定义
    tbl_name.MYD: 数据文件
    tbl_name.MYI: 索引文件
特性：
    加锁和并发：表级锁
    修复：手工或自动修复、但可能丢失数据
    索引：非聚集索引
    延迟更新索引键：
    压缩表
行格式：dynamic动态, fixed固定, compressed, compact, redundent

其他存储引擎
CSV：将普通的CSV(字段通过逗号分隔)作为MySQL表使用；
MRG_MYISAM：将多个MyISAM表合并成为一个虚拟表；
BLACKHOLE：类似于/dev/null，不真正存储任何数据；
MEMORY：所有数据都保存于内存中，内存表；支持hash索引；表级锁；临时表
PERFORMANCE_SCHEMA：伪存储引擎；
ARCHIVE：只支持SELECT和INSERT操作；支持行级锁和专用缓存区；
FEDERATED：用于访问其它远程MySQL服务器一个代理，它通过创建一个到远程MySQL服务器的客户端连接，并将查询传输到远程服务器执行，而后完成数据存取；
在MariaDB的上实现是FederatedX

MariaDB支持的其它存储引擎：
   OQGraph
   SphinxSE
   TokuDB
   Cassandra
   CONNECT
   SQUENCE

mariaDB锁机制实现并发控制：
    读锁：共享锁
    写锁：独占锁
锁粒度：
    表级锁
    行级锁

锁类型：

    隐式锁：由存储引擎自动施加锁；
    显式锁：手动施加锁；

锁策略：在锁粒度及数据安全性寻求的平衡机制；
    每种存储引擎都可以自行实现其锁策略和锁粒度；
    MySQL在服务器级也实现了锁，表级锁；用户可显式(手动)请求，看下面示例：

MariaDB [mydb]> LOCK TABLES t1 READ;
Query OK, 0 rows affected (0.00 sec)
添加一个写锁：
MariaDB [mydb]> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mydb]> LOCK TABLES t1 WRITE;
Query OK, 0 rows affected (0.00 sec)
在另一个线程执行SELECT操作就会卡住，被阻塞：
MariaDB [mydb]> SELECT * FROM t1;

事务：一组原子性的SQL查询，或者说一个独立工作单元。

ACID测试：
    A：atomicity，原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚；
    C：consistency, 一致性；数据库总是从一个一致性状态转换为另一个一致性状态；
    I：Isolation隔离性；一个事务所做出的操作在提交之前，是不能为其它所见；隔离有多种隔离级别；
    D：durability: 持久性；一旦事务提交，其所做的修改会永久保存于数据库中；

事务：
    启动事务：START TRANSACTION
    结束事务：
        (1) COMMIT：提交
        (2) ROLLBACK: 回滚

流程：

注意：只有事务型存储引擎（比如innodb）方能支持此类操作；
建议：显式请求和提交事务，而不要使用“自动提交”功能；自动会降低系统性能
MariaDB [hellodb]> SHOW GLOBAL VARIABLES LIKE '%autoc%'
    -> ;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)
关闭自动提交：
MariaDB [hellodb]> SET SESSION autocommit=0;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> SHOW VARIABLES LIKE '%autoc%'
    -> ;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

示例：
开启事务：
MariaDB [hellodb]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
删除两行：
MariaDB [hellodb]> DELETE FROM students WHERE StuID in (18,23);
Query OK, 2 rows affected (0.00 sec)
查看发现被删除了：
MariaDB [hellodb]> SELECT * FROM students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
23 rows in set (0.00 sec)
回滚
MariaDB [hellodb]> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
查看发现刚刚删除的马超和华容又回来了：
MariaDB [hellodb]> SELECT * FROM students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
事务相关命令帮助：
MariaDB [(none)]> HELP Transactions
You asked for help about help category: "Transactions"
For more information, type 'help <item>', where <item> is one of the following
topics:
   CHANGE MASTER TO
   DEALLOCATE PREPARE
   EXECUTE STATEMENT
   ISOLATION
   LOCK
   PREPARE
   PURGE BINARY LOGS
   RESET MASTER
   RESET SLAVE
   SAVEPOINT
   SET GLOBAL SQL_SLAVE_SKIP_COUNTER
   SET SQL_LOG_BIN
   START SLAVE
   START TRANSACTION
   STOP SLAVE

巧用SAVEPOINT:

MariaDB [hellodb]> INSERT INTO students (Name,Age,Gender) VALUEs ('Jinjiao King',100,'M');
Query OK, 1 row affected (0.00 sec)
创建回滚点：
MariaDB [hellodb]> SAVEPOINT sp1;
Query OK, 0 rows affected (0.00 sec)
查看：金角大王存在
MariaDB [hellodb]> SELECT * FROM students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
|    28 | Jinjiao King  | 100 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
26 rows in set (0.00 sec)
删除金角大王：
MariaDB [hellodb]> DELETE FROM students WHERE StuID = 28;
Query OK, 1 row affected (0.00 sec)
金角大王被删除了：
MariaDB [hellodb]> SELECT * FROM students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
回滚到回滚点1：
MariaDB [hellodb]> ROLLBACK TO sp1;
Query OK, 0 rows affected (0.00 sec)
金角大王又回来了：
MariaDB [hellodb]> SELECT * FROM students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
|    28 | Jinjiao King  | 100 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
26 rows in set (0.00 sec)

事务隔离级别：
    READ UNCOMMITTED 即使别人没提交我们也能看到(读未提交)---存在问题1,2,3
    READ COMMITTED别人提交了我们才能看到 (读提交)----存在问题2,3
    REPEATABLE READ只要自己没提交，看到的数据肯定是不变的，不管别人如何改变此数据 (可重读)----存在问题3   默认级别

    SERIALIZABILE (可串行化)-----存在问题4，并发能力最低，安全性最高

可能存在问题：
    1.脏读：读到别人未提交的数据，别人一旦回滚，可能出现读到的数据和别人真实拥有的不一致
    2.不可重复读：在别人提交前读了一次，别人改变数据提交后又读了一次，两次不一致
    3.幻读：自欺欺人式读法，即使别人改了数据，自己还认为读到的原来的是正确的
    4.加锁读：数据被加锁，别人无法读到

更改隔离级别：

MariaDB [hellodb]> SHOW PROCESSLIST;
+----+------+-----------+---------+---------+------+-------+------------------+----------+
| Id | User | Host      | db      | Command | Time | State | Info             | Progress |
+----+------+-----------+---------+---------+------+-------+------------------+----------+
|  5 | root | localhost | mydb    | Sleep   | 7952 |       | NULL             |    0.000 |
|  8 | root | localhost | hellodb | Query   |    0 | NULL  | SHOW PROCESSLIST |    0.000 |
+----+------+-----------+---------+---------+------+-------+------------------+----------+
2 rows in set (0.03 sec)
定义mysql隔离级别的变量，可在session级别进行修改，默认是REPEATABLE READ：
MariaDB [hellodb]> SHOW GLOBAL VARIABLES LIKE '%isola%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)
修改会话的隔离级别：
MariaDB [hellodb]> SET tx_isolation='READ-UNCOMMITTED';
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> SHOW GLOBAL VARIABLES LIKE '%isola%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.01 sec)

死锁：两个或多个事务在同一资源相互占用，并请求锁定对方占用的资源的状态；

事务日志：磁盘上一段连续的空间记录事务日志，只要一提交，马上写入到事务日志文件，完成后才写进数据文件中，事务日志一般应该与数据文件放置在不同磁盘，但是如果使用lvm做快照就只能放在同一个磁盘上
    事务日志的写入类型为“追加”，因此其操作为“顺序IO”；此日志通常也被称为“预写式日志(write ahead logging)”；避免了直接写入数据文件产生大量的随机IO
     修改以下三个数据库参数时应关闭数据库，通过修改配置文件修改

    innodb_log_file_size 定义事务日志文件大小，不建议太大
    innodb_log_files_in_group  存储位置
    innodb_log_group_home_dir

MariaDB [hellodb]> SHOW GLOBAL VARIABLES LIKE 'innodb_log%'
    -> ;
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| innodb_log_block_size     | 512     |
| innodb_log_buffer_size    | 8388608 |
| innodb_log_file_size      | 5242880 |
| innodb_log_files_in_group | 2       |
| innodb_log_group_home_dir | ./      |
+---------------------------+---------+
5 rows in set (0.00 sec)

转载于:https://blog.51cto.com/leeyan/1709447