数据抽象:
物理层:决定数据的存储格式,即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