数据库基础（一）

目录

一、一些基本概念

1. 四个事务隔离级别

Read Uncommitted（读取未提交内容）

Read Committed（读取提交内容）

Repeatable Read（可重读）

Serializable（可串行化）

2. MVCC（多版本并发控制）

3. 外键

a. 约束

删除约束

更新约束

b. 外键的使用

二、存储引擎

MySql中存储引擎

---

一、一些基本概念

1. 四个事务隔离级别

• Read Uncommitted（读取未提交内容）

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

• Read Committed（读取提交内容）

这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

• Repeatable Read（可重读）

这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

• Serializable（可串行化）

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

2. MVCC（多版本并发控制）

MVCC的核心思想是，对于每个修改操作，不是直接在原始数据上进行修改，而是创建一个新的数据版本，并将修改操作应用于新版本。这样，其他事务仍然可以访问旧版本的数据，而不会受到正在进行的修改的影响。只有在事务提交时，新版本的数据才会替代旧版本，从而实现数据的一致性。

MVCC在数据库中起作用的原理是通过使用版本号、时间戳或类似的标识来管理数据版本，并通过一定的规则来决定哪些版本对于每个事务是可见的。

3. 主键

主键在物理层面上只有两个用途：

        1. 惟一地标识一行。

        2. 作为一个可以被外键有效引用的对象。

基于以上这两个用途，下面给出了我在设计物理层面的主键时所遵循的一些原则：

        1. 主键应当是对用户没有意义的。

        2. 主键应该是单列的，以便提高连接和筛选操作的效率。

        3. 永远也不要更新主键。

        4. 主键不应包含动态变化的数据，如时间戳、创建时间列、修改时间列等。

        5. 主键应当有计算机自动生成。

4. 外键

一般外键都为其他表中的主键。将拥有外键的表称之为子表，该外键作为主键的表成为主表。

a. 约束

更能体现外键的作用是约束，这种约束，其实是定义好的规则，简化我们对数据库操作。就是某些操作在定义好的约束后，自动帮你完成。

基本约束内容：

        1、当主表中没有对应的记录时，不能将记录添加到子表

        2、不能更改主表中的值而导致子表中的记录孤立

        3、子表存在与主表对应的记录，不能从主表中删除该行

        4、删除主表前，先删子表

约束分为删除和更新。删除用的是比较多的

删除约束

就是当主表中的某条记录被删除的时候，子表中对应的记录如何处理

        方式1：当主表中的某条记录被删除时，对应的子表中的记录中的外键自动置为Null（ON DELETE SET NULL操作，当父表中的记录被删除时，MySQL会将子表中的外键列值设置为NULL）。

        方式2 ：当主表中的某条记录被删除时，对应的子表中的元素自动也删除（删除对应的行记录）。（当取值为Cascade时，则当在父表（即外键的来源表）中删除对应记录时，首先检查该记录是否有对应外键，如果有则也删除外键在子表（即包含外键的表）中的记录。）

        方式3 ： 当主表中的某条记录被删除时，如果子表有记录关联到该主表记录，此时无法删除主表中的这条记录，会报错。（当取值为No Action或者Restrict时，则当在父表（即外键的来源表）中删除对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除）

更新约束

这个情况发生的较少，就是当主表中的主键发生更改时，子表中的外键也会随之自动变化。

b. 外键的使用

外键的使用需要满足下列的条件：
        1、两张表必须都是InnoDB表，并且它们没有临时表。

        2、建立外键关系的对应列必须具有相似的InnoDB内部数据类型。

        3、建立外键关系的对应列必须建立了索引。

创建外键的两种方式：

在创建表时，必须先建被关联的表，才能建关联表

在插入记录时，必须先插入被关联的表，才能插关联表

        方式一：在创建表的时候进行添加
        方式二：表已经创建好了，继续修改表的结构来添加外键。

二、存储引擎

在Oracle 和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。而MySql数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎。

 数据库的引擎有InnoDB引擎、MyISAM引擎、Archive引擎、CSV引擎、Memory引擎、Federated引擎、Mrg_MyISAM引擎和NDB集群引擎等等。

MySql中存储引擎

1. MyISAM：这种引擎是mysql最早提供的。这种引擎又可以分为静态MyISAM、动态MyISAM 和压缩MyISAM三种，不管是何种MyISAM表，目前它都不支持事务，行级锁和外键约束的功能。 

   • MySQL5.1中默认，不支持事务和行级锁；
   • 提供大量特性如全文索引、空间函数、压缩、延迟更新等；
   • 数据库故障后，安全恢复性差；
   • 对于只读数据可以忍受故障恢复，MyISAM依然非常适用；
   • 日志服务器的场景也比较适用，只需插入和数据读取操作；
   • 不支持单表一个文件，会将所有的数据和索引内容分别存在两个文件中；
   • MyISAM对整张表加锁而不是对行，所以不适用写操作比较多的场景；
   • 支持索引缓存不支持数据缓存。

2. MyISAM Merge引擎：这种类型是MyISAM类型的一种变种。合并表是将几个相同的MyISAM表合并为一个虚表。常应用于日志和数据仓库。
3. InnoDB：InnoDB表类型可以看作是对MyISAM的进一步更新产品，它提供了事务、行级锁机制和外键约束的功能，也是目前MySQL默认的存储引擎。

   1. 将数据存储在表空间中，表空间由一系列的数据文件组成，由InnoDB管理；

   2. 支持每个表的数据和索引存放在单独文件中(innodb_file_per_table)；

   3. 支持事务，采用MVCC（多版本并发控制）来控制并发，并实现标准的4个事务隔离级别，支持外键；

   4. 索引基于聚簇索引建立，对于主键查询有较高性能；

   5. 数据文件的平台无关性，支持数据在不同的架构平台移植；

   6. 能够通过一些工具支持真正的热备。如XtraBackup等；

   7. 内部进行自身优化如采取可预测性预读，能够自动在内存中创建hash索引等。

4. Memory(heap)：这种类型的数据表只存在于内存中，不消耗IO。它使用散列索引，所以数据的存取速度非常快。因为是存在于内存中，所以这种类型常应用于临时表中。

MEMORY表的每个表可以有多达32个索引，每个索引16列，以及500字节的最大键长度。

可以在一个MEMORY表中有非唯一键值。

MEMORY支持AUTO_INCREMENT列和对可包含NULL值的列的索引。

MEMORY表在所由客户端之间共享（就像其他任何非TEMPORARY表）。

MEMORY表内存被存储在内存中，内存是MEMORY表和服务器在查询处理时的空闲中，创建的内部表共享。

默认情况下，MEMORY数据表使用散列索引，利用这种索引进行“相等比较”非常快，但是对“范围比较”的速度就慢多了。因此，散列索引值适合使用在"="和"<=>"的操作符中，不适合使用在"<"或">"操作符中，也同样不适合用在order by字句里。如果确实要使用"<"或">"或betwen操作符，可以使用btree索引来加快速度。

存储在MEMORY数据表里的数据行使用的是固定长度的格式，因此加快处理速度，这意味着不能使用BLOB和TEXT这样的长度可变的数据类型。VARCHAR是一种长度可变的类型，但因为它在MySQL内部当作长度固定不变的CHAR类型，所以也可以使用。

create table tab_memoryengine=memory select id,name,age,addr from man order by id;

使用USING HASH/BTREE来指定特定到索引。

create index mem_hash using hashon tab_memory(city_id);

在启动MySQL服务的时候使用--init-file选项，把insert into...select或load data infile 这样的语句放入到这个文件中，就可以在服务启动时从持久稳固的数据源中装载表。

每个MEMORY表中放置到数据量的大小，受到max_heap_table_size系统变量的约束，这个系统变量的初始值是16M，同时在创建MEMORY表时可以使用MAX_ROWS子句来指定表中的最大行数。

每个MEMORY表实际对应一个磁盘文件，格式是.frm。MEMORY类型的表访问非常快，因为它到数据是放在内存中的，并且默认使用HASH索引，但是一旦服务器关闭，表中的数据就会丢失，但表还会继续存在。　

服务器需要足够的内存来维持所在的在同一时间使用的MEMORY表，当不再需要MEMORY表的内容时，要释放被MEMORY表使用的内存，应该执行 DELETE FROM或 TRUNCATE TABLE，或者删除整个表（使用DROP TABLE）。

5. archive：这种类型只支持select 和 insert语句，而且不支持索引。常应用于日志记录和聚合分析方面

1. 只支持insert和select操作；
2. 缓存所有的写数据并进行压缩存储，支持行级锁但不支持事务；
3. 适合高速插入和数据压缩，减少IO操作，适用于日志记录和归档服务器。

其他引擎

Blackhole引擎：没有实现任何存储机制，会将插入的数据进行丢弃，但会存储二进制日志。会在一些特殊需要的复制架构的环境中使用。

CSV引擎：可以打开CSV文件存储的数据，可以将存储的数据导出，并利用excel打开。可以作为一种数据交换的机制使用。

Federated引擎：能够访问远程服务器上的数据的存储引擎。能够建立一个连接连到远程服务器。

Mrg_MyISAM引擎：将多个MYISAM表合并为一个。本身并不存储数据，数据存在MyISAM表中间。

NDB集群引擎：MySQL Cluster专用。

Mysql索引命中规则
           最左匹配原则 
                 1、先定位该sql的查询条件，有哪些，那些是等值的，那些是范围的条件。 
                  2、等值的条件去命中索引最左边的一个字段，然后依次从左往右命中，范围的放在最后。

           一条sql语句要执行完成需要经历什么样的过程 
                  当一条sql语句提交给mysql数据库进行查询的时候需要经历以下几步 
                 1、先在where解析这一步把当前的查询语句中的查询条件分解成每一个独立的条件单元 
                 2、mysql会自动将sql拆分重组 
                 3、然后where条件会在B-tree index这部分进行索引匹配，如果命中索引，就会定位到指定的table records位置。如果没有命中，则只能采用全部扫描的方式 
                 4、根据当前查询字段返回对应的数据

存储引擎的选择

InnoDB： 支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。

MyISAM： 插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比较低，也可以使用。

MEMORY： 所有的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMOEY。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表。

同一个数据库也可以使用多种存储引擎的表。如果一个表要求比较高的事务处理，可以选择InnoDB。这个数据库中可以将查询要求比较高的表选择MyISAM存储。如果该数据库需要一个用于查询的临时表，可以选择MEMORY存储引擎。

若要修改默认引擎，可以修改配置文件中的default-storage-engine。可以通过：show variables like 'default_storage_engine';查看当前数据库到默认引擎。命令：show engines和show variables like 'have%'可以列出当前数据库所支持到引擎。其中Value显示为disabled的记录表示数据库支持此引擎，而在数据库启动时被禁用。在MySQL5.1以后，INFORMATION_SCHEMA数据库中存在一个ENGINES的表，它提供的信息与show engines;语句完全一样，可以使用下面语句来查询哪些存储引擎支持事物处理：select engine from information_chema.engines where transactions ='yes';

可以通过engine关键字在创建或修改数据库时指定所使用到引擎。

在创建表的时候通过engine=...或type=...来指定所要使用的引擎。show table status from DBname来查看指定表的引擎。