一、存储引擎：
表类型：也称为“表类型”，表级别概念，不建议在同一个库中的表上使用不同的ENGINE；
1、引擎管理命令
格式：CREATE TABLE ... ENGINE[=]STORAGE_ENGINE_NAME ...
显示信息：SHOW TABLE STATUS
2、常见的存储引擎种类：
MyISAM, Aria, InnoDB, MRG_MYISAM, CSV, BLACKHOLE, MEMORY, PERFORMANCE_SCHEMA, ARCHIVE, FEDERATED
InnoDB：InnoBase
Percona-XtraDB, Supports transactions, row-level locking, and foreign keys
3、数据和索引存储
- 数据存储于“表空间(table space)"中，根据存储位置可分为两种格式：
格式一：数据和索引存储于同一个表空间中
所有数据库中的所有类型为InnoDB的表的数据和索引存储于同一个表空间中；
表空间文件：datadir定义的目录中
文件：ibdata1, ibdata2, ...
格式二：数据和索引存储于各自专用的表空间中
innodb_file_per_table=ON，意味着每表使用单独的表空间文件；
每表的数据文件（数据和索引，存储于数据库目录）存储于自己专用的表空间文件中，并存储于数据库目录下: tbl_name.ibd
4、表结构的定义：
在数据库目录，tbl_name.frm
二、Innodb事务型存储引擎
特点：
- 1、事务型存储引擎，适合对事务要求较高的场景中；但较适用于处理大量短期事务；
- 2、基于MVCC（Mutli Version Concurrency Control）支持高并发；
支持四个隔离级别，默认级别为REPEATABLE-READ；间隙锁以防止幻读； - 3、innodb使用聚集索引（主键索引），把数据和索引存放到一起，找到索引即找到数据，聚集索引只能一个表只能有一个；
- 4、innodb存储引擎支持”自适应Hash索引“；
- 5、锁粒度：行级锁，只锁定需要操作的行；间隙锁；
总结：
数据存储：表空间；
并发：MVCC，间隙锁，行级锁；
索引：聚集索引、辅助索引；
性能：预读操作、内存数据缓冲、内存索引缓存、自适应Hash索引、插入操作缓存区；
备份：支持热备；
三、MyISAM存储引擎：
特点：
- 1、支持全文索引（FULLTEXT index）、压缩、空间函数（GIS）；
- 3、MyISAM存储引擎不支持事务
- 3、锁粒度：表级锁，操作时候锁定整个表
- 4、崩溃无法保证表安全恢复
适用场景：
只读或读多写少的场景、较小的表（以保证崩溃后恢复的时间较短）；
文件：
每个表有三个文件，存储于数据库目录中
tbl_name.frm：表格式定义；
tbl_name.MYD：数据文件；
tbl_name.MYI：索引文件；
总结：
加锁和并发：表级锁；
修复：手动或自动修复、但可能会丢失数据；
索引：非聚集索引；
延迟索引更新；
表压缩；
行格式：
DEFAULT默认格式
DYNAMIC行长度
FIXED固定行长度
COMPRESSED行中的数据做压缩存储
REDUNDANT对行中的数据做冗余存储
COMPACT记住存储格式
五、其它的存储引擎：
- CSV：将CSV文件（以逗号分隔字段的文本文件）作为MySQL表文件；
- MRG_MYISAM：将多个MyISAM表合并成的虚拟表；
- BLACKHOLE：类似于/dev/null，不真正存储数据；
- MEMORY：内存存储引擎，支持hash索引，表级锁，常用于临时表；
- FEDERATED: 用于访问其它远程MySQL服务器上表的存储引擎接口；
MariaDB额外支持很多种存储引擎：
OQGraph、SphinxSE、TokuDB、Cassandra、CONNECT、SQUENCE、...
搜索引擎：
java语言研发的lucene,
c++语言研发的 sphinx
lucene：Solr, ElasticSearch
查看引擎状态信息
SHOW ENGINE INNODB STATUS;
SHOW ENGINE MyISAM STATUS;
六、数据库的并发控制
数据库管理中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。
1、锁：Lock
- 数据库是一个多用户使用的共享资源,当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性(脏读，不可重复读，幻读等)，可能产生死锁。为了解决这个问题，加锁是一个非常重要的技术，对实现数据库并发控制是一个好的方案。简单说，当一个执行sql语句的事务想要操作表记录之前，先向数据库发出请求，对你访问的记录集加锁，在这个事务释放这个锁之前，其他事务不能对这些数据进行更新操作。
2、锁类型 ：
读锁：共享锁，可被多个读操作共享；
写锁：排它锁，独占锁；
3、锁粒度：
表锁：在表级别施加锁，并发性较低；
行锁：在行级别施加锁，并发性较高，但维持锁状态的成本较大；
4、锁策略：
在锁粒度及数据安全性之间寻求一种平衡机制；
存储引擎：级别以及何时施加或释放锁由存储引擎自行决定；
MySQL Server：表级别，可自行决定，也允许显式请求；
5、锁类别：
- 显式锁：用户手动请求的锁；
- 隐式锁：存储引擎自行根据需要施加的锁；
显式锁的使用：
(1) LOCK TABLES锁表
LOCK TABLES tbl_name read|write, tbl_name read|write, ...
UNLOCK TABLES解锁
(2) FLUSH TABLES刷写所有缓存中的表到硬盘中
FLUSH TABLES tbl_name,... [WITH READ LOCK];
#温备语句，刷写所有缓存中的表到硬盘中，并读锁
FLUSH TABLES WITH READ LOCK;
UNLOCK TABLES;解锁
(3) SELECT cluase
[FOR UPDATE | LOCK IN SHARE MODE]
七、事务：
- 事务：一组原子性的SQL查询、或者是一个或多个SQL语句组成的独立工作单元；
1、事务日志：
#查询日志相关设置
SHOW GLOBAL VARIABLES LIKE 'innodb%log%';
innodb_log_files_in_group 一个组文件数量
innodb_log_group_home_dir 当前数据目录
innodb_log_file_size 事物日志文件存储空间
innodb_mirrored_log_groups 镜像事物组
2、ACID测试：
- A：AUTOMICITY，原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚；
- C：CONSISTENCY，一致性；数据库总是应该从一个一致性状态转为另一个一致性状态；
- I：ISOLATION，隔离性；一个事务所做出的操作在提交之前，是否能为其它事务可见；出于保证并发操作之目的，隔离有多种级别；
- D：DURABILITY，持久性；事务一旦提交，其所做出的修改会永久保存；
自动提交：单语句事务
mysql> SELECT @@autocommit;
+------------------------+
| @@autocommit |
+------------------------+
| 1 |
+------------------------+
mysql> SET @@session.autocommit=0；
手动控制事务：
启动：START TRANSACTION
提交：COMMIT
回滚：ROLLBACK
事务支持savepoints：事物保存点
SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier
3、事务隔离级别：
- READ-UNCOMMITTED：读未提交 --> 脏读；终端1操作表中数据变化，无论提交不提交，其他终端查看对应表也随之变化。
- READ-COMMITTED：读提交--> 不可重复读；终端1操作表中数据变化，不提交情况下，其他终端查看对应表无变化。
- REPEATABLE-READ：可重复读 --> 幻读；终端1操作表中数据变化，提交后，其他终端查看对应表无变化，但其他终端回滚后，数据反而发生变化。
- SERIALIZABLE：串行化，事务不可同时进行；终端1操作表中数据变化，提交后，其他终端查看对应表也随之变化，如果不提交，将阻塞事物。
#查看当前级别
mysql> SELECT @@session.tx_isolation;
+----------------------------------+
| @@session.tx_isolation |
+----------------------------------+
| REPEATABLE-READ |
+----------------------------------+
#设定级别为读未提交
mysql> SET @@session.tx_isolation='READ-UNCOMMITTED' ;
#启动
mysql> START TRANSACTION;
查看InnoDB存储引擎的状态信息：
SHOW ENGINE innodb STATUS;
网友评论