数据库

模式

外模式

外模式又称子模式或用户模式，对应于用户级。它是某个或某几个用户所看到的数据库的数据视图，是与某一应用有关的数据的逻辑表示。外模式是从模式导出的一个子集，包含模式中允许特定用户使用的那部分数据。用户可以通过外模式描述语言来描述、定义对应于用户的数据记录(外模式)，也可以利用数据操纵语言(Data Manipulation Language，DML)对这些数据记录进行操作。外模式反映了数据库的用户观。

概念模式

概念模式又称模式或逻辑模式，对应于概念级。它是由数据库设计者综合所有用户的数据，按照统一的观点构造的全局逻辑结构，是对数据库中全部数据的逻辑结构和特征的总体描述，是所有用户的公共数据视图(全局视图)。它是由数据库管理系统提供的数据模式描述语言(Data Description Language，DDL)来描述、定义的，体现、反映了数据库系统的整体观。

内模式

内模式又称存储模式，对应于物理级，它是数据库中全体数据的内部表示或底层描述，是数据库最低一级的逻辑描述，它描述了数据在存储介质上的存储方式和物理结构，对应着实际存储在外存储介质上的数据库。内模式由内模式描述语言来描述、定义，它是数据库的存储观。
在一个数据库系统中，只有唯一的数据库， 因而作为定义 、描述数据库存储结构的内模式和定义、描述数据库逻辑结构的模式，也是唯一的，但建立在数据库系统之上的应用则是非常广泛、多样的，所以对应的外模式不是唯一的，也不可能是唯一的。

事务

特性

DBMS中事务有四个特性，持久性，一致性，原子性，隔离性。
原子性-事务管理子系统。一致性-完整子系统。隔离性-并发控制子系统。持久性-恢复管理子系统。

原子性（Atomic） 整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节
一致性（Consistency）在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏
隔离性（Isolation）两个事务的执行是互不干扰的，一个事务不可能看到其他事务运行时中间某一时刻的数据
持久性（Durability）在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

并发事务引起的问题

（1）更新丢失
两个事务同时读入同一数据并修改，另一事务提交的结果覆盖了前一事务的结果，导致前一事务的修改被丢失。
（2）脏读
又称无效数据读出，指在一个事务处理过程里读取了另一个未提交的事务中的数据。
（3）不可重复读
不可重复读是指在对于数据库中的某个数据，一个事务范围内的多次相同的查询却返回了不同的结果。这是由于在查询间隔，被另外的事务插入，并提交更新了原有的数据。
（4）幻读
事务在操作过程中的多次查询，可能查出的数据数量不一致，或者有些操作未生效。幻读是事务非独立执行时发生的一种现象。

不可重复读的重点是修改：
同样的条件，你读取过的数据，再次读取出来发现值不一样了。
幻读的重点在于新增或者删除：
同样的条件，第 1 次和第 2 次读出来的记录数不一样。

隔离级别

数据库事务的隔离性：数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题

读未提交数据

允许事务读取未被其他事务提交的变更，可能有脏读，不可重复读和幻读的问题。
比如：某时刻会话 a 修改了一个数据，但还未提交，此时会话 b 读取了该数据，这时会话 a 回滚了事务，这就导致数据出现了不一致状态，这就是脏读。

原理

事务对当前被读取的数据不加锁；
事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加 行级共享锁，直到事务结束才释放。

表现

事务1读取某行记录时，事务2也能对这行记录进行读取、更新；当事务2对该记录进行更新时，事务1再次读取该记录，能读到事务2对该记录的修改版本，即使该修改尚未被提交。
事务1更新某行记录时，事务2不能对这行记录做更新，直到事务1结束。

读已提交数据

允许事务读取已经被其他事务提交的变更，可以避免脏读，可能有不可重复读和幻读的问题。
例如：某时刻会话 a 的一个事务里查询一个数据，得到的数据是 1，这时会话 b 修改了该数据的值为 2，并提交了，此时会话 a 的事务又要读取该数据，这时的数据是 2，就样就出现了同一个事务内，读的结果不一样，这就是不可重复读。

原理

事务对当前被读取的数据加 行级共享锁（当读到时才加锁），一旦读完该行，立即释放该行级共享锁；
事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加 行级排他锁，直到事务结束才释放。

表现

事务1读取某行记录时，事务2也能对这行记录进行读取、更新；当事务2对该记录进行更新时，事务1再次读取该记录，读到的只能是事务2对其更新前的版本，要不就是事务2提交后的版本。
事务1更新某行记录时，事务2不能对这行记录做更新，直到事务1结束。

可重复读

Mysql 的默认隔离级别，确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其他事务对这个字段进行更新，可以避免脏读和不可重复读，可能会有幻读。
即当用户读取某一范围的数据行时，如果另一个事务在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行，即发生幻读。

原理

事务在读取某数据的瞬间（就是开始读取的瞬间），必须先对其加 行级共享锁，直到事务结束才释放；
事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加 行级排他锁，直到事务结束才释放。

表现

事务1读取某行记录时，事务2也能对这行记录进行读取、更新；当事务2对该记录进行更新时，事务1再次读取该记录，读到的仍然是第一次读取的那个版本。
事务1更新某行记录时，事务2不能对这行记录做更新，直到事务1结束。

可串行化

最高的隔离级别，它要求事务序列化地，只能一个接着一个地执行，不能并发执行。这样也不可能相互冲突，从而解决幻读问题。简言之，它是在每个被读的数据行上加上共享锁。
避免了幻读，但可能导致大量的超时现象和锁竞争，影响并发性能，代价最高。

原理

事务在读取数据时，必须先对其加 表级共享锁 ，直到事务结束才释放；
事务在更新数据时，必须先对其加 表级排他锁 ，直到事务结束才释放。

表现

事务1正在读取A表中的记录时，则事务2也能读取A表，但不能对A表做更新、新增、删除，直到事务1结束。
事务1正在更新A表中的记录时，则事务2不能读取A表的任意记录，更不可能对A表做更新、新增、删除，直到事务1结束。

事务控制语句

BEGIN 或 START TRANSACTION：显示地开启一个事务；
COMMIT：提交事务，并使已对数据库进行的所有修改称为永久性的；
ROLLBACK：回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

MVCC

数据库锁

基本类型

共享锁
共享锁又称读锁，是读取操作创建的锁。如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。
排他锁
排他锁又称写锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。

锁的粒度

在DBMS中，可以按照锁的粒度把数据库锁分为行级锁、表级锁和页级锁。
（1）行级锁
行级锁是MySQL中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁和排他锁。InnoDB引擎通过索引查询时加的是行级锁。
特点：开销大，加锁慢；会死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
（2）表级锁
表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。
特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。
（3）页级锁
页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。
特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

 MyISAM和MEMORY采用表级锁(table-level locking)
 BDB采用页面锁(page-level locking)或表级锁，默认为页面锁
 InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁

引擎

InnoDB

MyISAM

索引

索引的定义

索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。
简单地说，索引是对数据库表中一列或多列的值进行排序的一种结构，提高查询效率。

[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY [索引名](字段名1 [(长度)] [ASC | DESC])

UNIQUE：可选。表示索引为唯一性索引。
FULLTEXT；可选。表示索引为全文索引。只能在CHAR、VARCHAR或TEXT类型的字段上创建，用于全文搜索。
SPATIAL：可选。表示索引为空间索引。
INDEX和KEY：用于指定字段为索引，两者选择其中之一就可以了，作用是一样的。
索引名：可选。给创建的索引取一个新名称。
字段名1：指定索引对应的字段的名称，该字段必须是前面定义好的字段。
长度：可选。指索引的长度，必须是字符串类型才可以使用。
ASC：可选。表示升序排列。
DESC：可选。表示降序排列。

索引的优缺点

优点：
1.大大加快数据的检索速度，这也是创建索引的最主要的原因；
2.可以创建唯一性索引，保证数据库表中每一行数据的唯一性；
3.可以将表的外键制作为索引，加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义；
4.将随机I/O变为顺序I/O，帮助服务器避免排序和临时表，在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间；
5.通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
缺点：
1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加；
2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间；
3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

索引类型

按结构分类：可以分为有序索引（ordered index）和散列索引（hash index）两种基本类型。
• 有序索引是基于值的顺序排序，根据值的排序进行索引值的查找。又可以分为：顺序文件索引（稀疏、稠密索引），多级索引，B+树索引
• 散列索引基于将值平均分布到若干散列桶（hash bucket）。根据散列函数确定索引值所在的散列桶。分为：静态散列、动态散列

按功能分类：唯一索引，非唯一索引，集群索引，非集群索引和MDC块索引5种类型。

1. 聚集索引：索引项的顺序与表中记录的物理顺序一致。对于聚集索引，叶子结点即存储了真实的数据行，不再有另外单独的数据页。
2. 非聚集索引：表数据存储顺序与索引顺序无关。对于非聚集索引，叶结点包含索引字段值及指向数据页数据行的逻辑指针，其行数量与数据表行数据量一致。
   注意一个表只能有一个聚集索引，但是可以由多个非聚集索引。
3. 唯一索引：不允许具有索引值相同的行，可以为NULL，但不能有多个NULL。

4. 主键索引：主键索引是唯一索引的特殊类型。数据库表经常有一列或多列组合，其值唯一标识表中的每一行，该列称为表的主键。在数据库中为表定义主键将自动创建主键索引

5. b-tree索引
   Oracle数据库中最常见的索引类型是b-tree索引，也就是B-树索引，以其同名的计算科学结构命名。CREATE
   INDEX语句时，默认就是在创建b-tree索引。没有特别规定可用于任何情况。

6. 位图索引(bitmap index)
   位图索引特定于该列只有几个枚举值的情况，比如性别字段，标示字段比如只有0和1的情况。
7. 基于函数的索引
   比如经常对某个字段做查询的时候是带函数操作的，那么此时建一个函数索引就有价值了。
8. 分区索引和全局索引
   这2个是用于分区表的时候。前者是分区内索引，后者是全表索引
9. 反向索引（REVERSE）
   这个索引不常见，但是特定情况特别有效，比如一个varchar(5)位字段(员工编号)含值（10001,10002,10033,10005,10016..）。这种情况默认索引分布过于密集，不能利用好服务器的并行，但是反向之后10001,20001,33001,50001,61001就有了一个很好的分布，能高效的利用好并行运算。
10. HASH索引
    HASH索引可能是访问数据库中数据的最快方法，但它也有自身的缺点。集群键上不同值的数目必须在创建HASH集群之前就要知道。需要在创建HASH集群的时候指定这个值。使用HASH索引必须要使用HASH集群。

索引优化

1. 添加IN()语句使用一些本没有使用到的索引，尽可能的重用索引。不可滥用，会造成组合指数式增加
2. 范围扫描的字段尽可能的放在索引的最后面

查询优化器Explain

mysql> explain select p.POLI_NAME, c.COMM_NAME from (select POLI_ID,POLI_NAME from police where  POLI_NAME='尤东' ) as p left join community as c on p.POLI_ID=c.POLI_ID;
+----+-------------+------------+--------+-------------------------+-------------------------+---------+-------+------+--------------------------+
| id | select_type | table      | type   | possible_keys           | key                     | key_len | ref   | rows | Extra                    |
+----+-------------+------------+--------+-------------------------+-------------------------+---------+-------+------+--------------------------+
|  1 | PRIMARY     | <derived2> | system | NULL                    | NULL                    | NULL    | NULL  |    1 |                          |
|  1 | PRIMARY     | c          | ref    | INDEX_COMMUNITY_POLI_ID | INDEX_COMMUNITY_POLI_ID | 5       | const |    2 |                          |
|  2 | DERIVED     | police     | ref    | INDEX_POLICE_POLI_NAME  | INDEX_POLICE_POLI_NAME  | 62      |       |    1 | Using where; Using index |
+----+-------------+------------+--------+-------------------------+-------------------------+---------+-------+------+--------------------------+

id

选定的执行计划中查询的序列号。
表示查询中执行 select 子句或操作表的顺序,id 值越大优先级越高,越先被执行。
id 相同,执行顺序由上至下。

selct_type

数据读取操作的操作类型

类型	说明
SIMPLE	简单的 select 查询,不使用 union 及子查询
PRIMARY	最外层的 select 查询
UNION	UNION 中的第二个或随后的 select 查询,不 依赖于外部查询的结果集
DEPENDENT UNION	UNION 中的第二个或随后的 select 查询,依 赖于外部查询的结果集
SUBQUERY	子查询中的第一个 select 查询,不依赖于外 部查询的结果集
DEPENDENT SUBQUERY	子查询中的第一个 select 查询,依赖于外部 查询的结果集
DERIVED	用于 from 子句里有子查询的情况。 MySQL 会 递归执行这些子查询, 把结果放在临时表里
UNCACHEABLE SUBQUERY	结果集不能被缓存的子查询,必须重新为外 层查询的每一行进行评估
UNCACHEABLE UNION	UNION 中的第二个或随后的 select 查询,属 于不可缓存的子查询

table

输出行所引用的表。
当FROM子句中有子查询或UNION时,table列是< derivedN >，其中N是id列对应的值

type

重要的项，显示连接使用的类型。
结果值从好到坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说，得保证查询至少达到range级别，最好能达到ref。

类型	说明
system	表只有一条记录(等于系统表),这是const类型的特例
const	直接按主键或唯一键读取，通过索引一次查到数据，该类型主要用于比较primary key 或者unique 索引，因为只匹配一行数据，所以很快；如果将主键置于WHERE语句后面，Mysql就能将该查询转换为一个常量
eq_ref	唯一索引扫描，用于联表查询的情况，按联表的主键或唯一键联合查询。对于每个索引键，表中只有一条记录与之匹配。常见于主键或者唯一索引扫描
ref	非唯一索引扫描，返回匹配某个单独值得所有行，本质上是一种索引访问，它返回所有匹配某个单独值的行，就是说它可能会找到多条符合条件的数据，所以他是查找与扫描的混合体。索引要满足特定的数据结构，也就是索引字段的数据必须是有序的，才能实现这种类型的查找
range	只检索给定范围的行，使用一个索引来选着行。key列显示使用了哪个索引。一般在你的WHERE 语句中出现between 、< 、> 、in 等查询，这种给定范围扫描比全表扫描要好。因为他只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引
index	全表扫描,只是扫描表的时候按照索引次序 进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。
ALL	最坏的情况,从头到尾全表扫描。

possible_keys

显示可能应用在这张表的索引，一个或者多个。查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用。

key

实际使用到的索引。
如果为NULL，则没有使用索引。查询中如果使用了覆盖索引，则该索引仅出现在key列表中。
很少的情况 下,MYSQL 会选择优化不足的索引。这种情 况下,可以在 SELECT 语句中使用 USE INDEX (indexname)来强制使用一个索引或者用 IGNORE INDEX(indexname)来强制 MYSQL 忽略索引

key_len

使用的索引的长度。在不损失精确性的情况 下,长度越短越好。

ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

rows

根据表统计信息及索引选用的情况，大致估算找到所需记录需要读取的行数。表示mysql要遍历多少数据才能找到，在innodb上是不准确的

extra

扩展属性。
如果是Only index，这意味着信息只用索引树中的信息检索出的，这比扫描整个表要快。
如果是where used，就是使用上了where限制。
如果是impossible where 表示用不着where，一般就是没查出来啥。
如果此信息显示Using filesort或者Using temporary的话会很吃力，WHERE和ORDER BY的索引经常无法兼顾，如果按照WHERE来确定索引，那么在ORDER BY时，就必然会引起Using filesort，这就要看是先过滤再排序划算，还是先排序再过滤划算。