数据库复习笔记02

MySQL的逻辑架构

MySQL最重要、最与众不同的特性是它的存储引擎架构，这种架构的设计将查询处理（Query Processing）及其他系统任务（Server Task）和数据的存储/提取相分离。这种处理和存储分离的设计可以在使用时根据性能、特性，以及其他需求来选择数据存储的方式。

• MyISAM不支持事务、也不支持外键，其优势是访问的速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用。每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD
  (MYData)。索引文件的扩展名是.MYI (MYIndex)。

• InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保存数据和索引。InnoDB：所有的表都保存在同一个数据文件中，InnoDB表的大小只受限于操作系统文件的大小限制。Myisam只缓存索引，不缓存真实数据；Innodb不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响。

• MEMORY存储引擎使用存在于内存中的内容来创建表。MEMORY类型的表访问非常的快，因为它的数据是放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失。主要用于那些内容变化不频繁的代码表或者作为统计操作的中间结果表。
  查看数据库的引擎

show engines;

SQL分类

• DDL（Data Definition
  Languages)：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象。
  主要的语句关键字包括create、drop、alter等。
• DML（Data Manipulation Language）：
  数据操作语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性。
  主要的语句关键字包括insert、delete、update、select等。
• DCL（Data ControlLanguage）：
  数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。
  主要的语句关键字包括grant、revoke等。

索引：INDEX

索引：索引是对数据库表中一列或多列的值进行排序的一种结构。索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。由此可知，索引是要消耗数据库空间的。而约束是一种逻辑概念

索引好处：加快了查询速度(select )
索引坏处:降低了增,删,改的速度(update/delete/insert)，增大了表的文件大小(索引文件甚至可能比数据文件还大)

MySQL提供多种索引类型供选择：

• 普通索引：
• 唯一性索引：
• 主键索引：只有一个主键索引
• 全文索引：MySQL5.X版本只有MyISAM存储引擎支持FULLTEXT，并且只限于CHAR、VARCHAR和TEXT类型的列上创建。

MySQL的索引方法：

• HASH
• BTREE
  索引的使用原则：
  （1）不过度索引
  （2）索引条件列(where后面最频繁的条件比较适宜索引)
  （3）索引散列值,过于集中的值不要索引，例如:给性别"男","女"加索引,意义不大

DCL用来控制数据库的访问，包括如下SQL语句

• GRANT：授予访问权限
• REVOKE：撤销访问权限
• COMMIT：提交事务处理
• ROLLBACK：事务处理回退
• SAVEPOINT：设置保存点
• LOCK：对数据库的特定部分进行锁定

1、事务的ACID特性

• 原子性(Atomicity)：原子意为最小的粒子，或者说不能再分的事物。数据库事务的不可再分的原则即为原子性。
  组成事务的所有SQL必须：要么全部执行，要么全部取消（就像上面的银行例子）。
• 一致性(Consistency)：指数据的规则,在事务前/后应保持一致
• 隔离性(Isolation)：简单点说，某个事务的操作对其他事务不可见的.
• 持久性(Durability)：当事务提交完成后，其影响应该保留下来，不能撤消

2、事务的用法

• 开启事务(start transaction)
• 执行sql操作(普通sql操作)
• 提交/回滚(commit/rollback)
  注意:
• 建表的时候,选择 Innodb引擎才支持事务
• 默认情况下，MySQL是自动提交事务，每次执行一个 SQL语句时，如果执行成功，就会向数据库自动提交，而不能回滚。如果某一组操作需要在一个事务中，那么需要使用starttransaction，一旦rollback或commit就结束当次事务，之后的操作又自动提交。
• 如果需要在当前会话（连接）的整个过程中都取消自动提交事务，进行手动提交事务，就需要设置set autocommit =false;或set autocommit =0;那样的话每一句SQL都需要手动commit提交才会真正生效。rollback或commit之前的所有操作都视为一个事务，之后的操作视为另一个事务，还需要手动提交或回滚。和Oracle一样，DDL语句是不能回滚的，并且部分的DDL语句会造成隐式的提交，因此最好事务中不要涉及DDL语句。

3、数据库的隔离级别

对于同时运行的多个事务（多线程并发）, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题: （问题的本质就是线程安全问题，共享数据的问题）

• 脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段. 之后, 若 T2 回滚,T1读取的内容就是临时且无效的.
• 不可重复读: 对于两个事务 T1, T2, T1 读取了一个字段, 然后 T2 更新并提交了该字段. 之后, T1再次读取同一个字段,值就不同了.
• 幻读: 对于两个事务 T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入、删除了一些新的行. 之后, 如果 T1 再次读取同一个表, 就会多出、少了几行.

数据库事务的隔离性:
数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题。一个事务与其他事务隔离的程度称为隔离级别. 数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱。
MySQL数据库的隔离级别如下：