• 内联接：在两张表进行连接查询时，只保留两张表中完全匹配的结果集。
  • select .... from t1 inner join t2 on t1.ID=t2.ID
  • 结果是只保留既是本校校友，又是两院院士的人的信息。
• 外联接：分为左联接和右联接两种
  1. 左联接：在两张表进行连接查询时，会返回左表所有的行，即使左表在右表中没有匹配的记录。
     • select .... from t1 left (outer) join t2 on t1.ID=t2.ID
     • 结果是返回全部本校校友的记录，部分校友可能同时是院士，其他大部分校友，t2表的相关字段值都为null。
  2. 右联接：在两张表进行连接查询时，会返回右表所有的行，即使右表在左表中没有匹配的记录。
     • select .... from t1 right (outer) join t2 on t1.ID=t2.ID
     • 结果是返回全部两院院士的记录，部分院士可能是我校校友，其他大部分院士，t1表的相关字段值都为null。
• 全联接：在两张表进行连接查询时，返回左表和右表中所有的行（即便没有匹配）。
  • select .... from t1 full join t2 on t1.ID=t2.ID
  • 结果是返回本校校友+两院院士所有人的记录（当然会去重）。
  • 其实也就是left join和right join的并集。

单纯的select * from a,b是笛卡尔乘积。比如a表有5条数据，b表有3条数据，那么最后的结果有5*3=15条数据。但是如果对两个表进行关联:select * from a,b where a.id = b.id 意思就变了，此时就等价于：select * from a inner join b on a.id = b.id。即就是内连接。但是这种写法并不符合规范，可能只对某些数据库管用，如sqlserver。推荐最好不要这样写。最好写成inner join的写法。

1.2 drop、delete与truncate的区别

SQL中的drop、delete、truncate都表示删除，但是三者有一些差别

1. delete和truncate只删除表的数据不删除表的结构，drop都删除。
2. 一般来说，执行速度方面是 drop> truncate >delete
3. delete语句是dml，这个操作会放到rollback segement中，事务提交之后才生效; 如果有相应的trigger，执行的时候将被触发。
4. truncate、drop是ddl，操作立即生效，原数据不放到rollback segment中，不能回滚.。操作不触发trigger.

1.3 数据并发问题

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对同一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题。

1. 脏读（Dirty read）:

   • 针对同一个字段，一个事务（假设事务A）读到了另一个的事务（假设事务B）提交前的数据。比如事务B执行过程中修改了数据X，在未提交前，事务A读取了X，而事务B却回滚了，这样事务A就形成了脏读。

2. 丢失修改（Lost to modify）:

   • 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。
   • 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。

3. 不可重复读（Unrepeatableread）:

   • 一般发生在一个事务要在事务内读取一个字段多次的场景。
   • 事务A首先读取了一条数据，然后执行逻辑的时候，事务B将这条数据改变了，然后事务A再次读取的时候，发现数据和第一次读取的时候不一样了，就是所谓的不可重复读了。

4. 幻读（Phantom read）:

   • 幻读与不可重复读类似。也发生在一个事务在事务内部针对某些记录多次查询的情况。
   • 例如在一个事务（A）读取了几行数据，接着另一个并发事务（B）插入并提交了一些数据，并且这些数据符合事务A的where条件时。在第二次的查询中，事务（A）就会发现相比第一次查询，第二次多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复读和幻读两者有些相似，他们的区别是： | 不可重复读 | 幻读 | | ------------ | ------------ | | 针对的是update或delete | 针对的是insert | | 重点是修改:同样的条件, 你读取过的数据, 再次读取出来发现值不一样了 | 重点在于新增或者删除 (数据条数变化)：同样的条件, 第1次和第2次读出来的记录数不一样 |

1.4 事务隔离级别

SQL 标准定义了四个隔离级别：

1. READ-UNCOMMITTED(读未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
2. READ-COMMITTED(读已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
3. REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
4. SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

Mysql InnoDB 存储引擎的默认的隔离级别是 REPEATABLE-READ（可重复读）。我们可以通过SELECT @@tx_isolation;命令来查看

我们知道隔离级别越低，事务请求的锁越少，并发效率越高，所以大部分数据库系统的隔离级别都是 READ-COMMITTED(读取提交内容) ，但是你要知道的是InnoDB 存储引擎默认使用 REPEATABLE-READ（可重读） 并不会有任何性能损失。

与 SQL 标准不同的地方在于 InnoDB 存储引擎在 REPEATABLE-READ（可重读） 事务隔离级别下使用的是Next-Key Lock 锁算法，因此可以避免幻读的产生，这与其他数据库系统(如 SQL Server) 是不同的。

所以说InnoDB 存储引擎的默认的隔离级别是 REPEATABLE-READ（可重读） 已经可以完全保证事务的隔离性要求，即达到了 SQL标准的 SERIALIZABLE(可串行化) 隔离级别。

2 数据库设计的三范式

2.1 第一范式（1NF）

1NF是对属性的原子性，要求每一列（或者叫字段，属性）具有原子性，不可再分解；

如

学生表（学号，姓名，性别，生日）

如果认为最后一列还可以再分成（出生年，出生月，出生日），它就不满足第一范式了；

2.2 第二范式（2NF）

第二范式是指在满足第一范式的条件下，除主键外的每一列都完全依赖于主键（主要针对于联合主键而言）。

2NF是对记录的惟一性，要求记录有惟一标识，即实体的惟一性，即不存在部分依赖；

举个反例：

表（学号、课程号、姓名、学分） 联合主键为学号和课程号

这个表明显涵盖了两个信息主体：

1. 学生信息：学号和姓名字段属于学生信息，且姓名依赖于学号（学生信息的唯一标识）
2. 课程信息：课程号和学分字段属于课程信息，学分依赖课程号（课程信息的唯一标识）。

姓名由学号即可唯一标识，是对主键的部分依赖； 学分由课程号即可唯一标示，是对主键的部分依赖；

由于2NF要求非主键字段必须完全依赖主键，所以不符合二范式。 可能会存在问题：

• 数据冗余:，每条记录都含有相同信息；
• 删除异常：删除所有学生成绩，就把课程信息全删除了；
• 插入异常：学生未选课，无法记录进数据库；
• 更新异常：调整课程学分，所有行都调整。

正确做法:

• 学生表：Student(学号, 姓名)；
• 课程表：Course(课程号, 学分)；
• 选课关系表：StudentCourse(学号, 课程号, 成绩)。

2.3 第三范式（3NF）

第三范式是指在满足第二范式的基础上，每一条数据不能依赖于其他的非主属性，也就是消除了传递依赖关系。

3NF是对字段的冗余性，要求任何字段不能由其他字段派生出来，它要求字段没有冗余，即不存在传递依赖；

例如

表（学号, 姓名, 年龄, 学院名称, 学院电话）

因为存在依赖传递: (学号) → (学生)→(所在学院) → (学院电话) 。

可能会存在问题：

• 数据冗余:有重复值；
• 更新异常：有重复的冗余信息，修改时需要同时修改多条记录，否则会出现数据不一致的情况

正确做法：

• 学生：(学号, 姓名, 年龄, 所在学院)；
• 学院：(学院, 电话)。

2.4 反范式化

一般说来，数据库只需满足第三范式（3NF）就行了。没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。达到以空间换时间的目的。

比如：有一张存放商品的基本表，“金额”这个字段的存在，表明该表的设计不满足第三范式，因为“金额”可以由“单价”乘以“数量”得到，说明“金额”是冗余字段。但是，增加“金额”这个冗余字段，可以提高查询统计的速度，这就是以空间换时间的作法。

3. mysql存储引擎简述

简单来说，存储引擎就是指表的类型以及表在计算机上的存储方式。

存储引擎的概念是MySQL的特点，oracle中没有专门的存储引擎的概念，Oracle有OLTP和OLAP模式的区分。不同的存储引擎决定了MySQL数据库中的表可以用不同的方式来存储。我们可以根据数据的特点来选择不同的存储引擎。

在MySQL中的存储引擎有很多种，可以通过mysql> show engines;语句来查看。下面重点关注InnoDB、MyISAM、MEMORY这三种。

3.1 InnoDB引擎

MySQL默认的事务型引擎，也是最重要和使用最广泛的存储引擎。在MySQL从3.23.34a版本开始包含InnnoDB。

InnoDB给MySQL的表提供了事务处理、回滚、崩溃修复能力和多版本并发控制的事务安全。它是MySQL上第一个提供外键约束的存储引擎。而且InnoDB对事务处理的能力，也是其他存储引擎不能比拟的。

InnoDB的性能与自动崩溃恢复的特性，使得它在非事务存储需求中也很流行。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。

3.2 MyISAM引擎

在MySQL 5.1 及之前的版本，MyISAM是默认引擎。MyISAM提供的大量的特性，包括全文索引、压缩、空间函数（GIS）等，但MyISAM并不支持事务以及行级锁，而且一个毫无疑问的缺陷是崩溃后无法安全恢复。正是由于MyISAM引擎的缘故，即使MySQL支持事务已经很长时间了，在很多人的概念中MySQL还是非事务型数据库。尽管这样，它并不是一无是处的。对于只读的数据，或者表比较小，可以忍受修复操作，则依然可以使用MyISAM（但请不要默认使用MyISAM，而是应该默认使用InnoDB）

3.3 MEMORY引擎

MEMORY是MySQL中一类特殊的存储引擎。它使用存储在内存中的内容来创建表，而且数据全部放在内存中。这些特性与前面的两个很不同。

每个基于MEMORY存储引擎的表实际对应一个磁盘文件。该文件的文件名与表名相同，类型为frm类型。该文件中只存储表的结构。而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率。值得注意的是，服务器需要有足够的内存来维持MEMORY存储引擎的表的使用。如果不需要了，可以释放内存，甚至删除不需要的表。

MEMORY默认使用哈希索引。速度比使用B型树索引快。当然如果你想用B型树索引，可以在创建索引时指定。

注意，MEMORY用到的很少，因为它是把数据存到内存中，如果内存出现异常就会影响数据。如果重启或者关机，所有数据都会消失。因此，基于MEMORY的表的生命周期很短，一般是一次性的。

3.4 如何合适的选择存储引擎

• 有以下要求，则适合采用InnoDB：

  • 需要对事务的完整性要求比较高（比如银行）
  • 要求实现并发控制（比如售票）
  • 如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。

• 有以下要求，则适合采用MyISAM：

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

• 有以下要求，则适合采用MEMORY：

  • 如果需要很快的读写速度，对数据的安全性要求较低，且数据量很小时，可以选择MEMOEY。

3.5 MyISAM与InnoDB区别

4. MySql索引

我们都希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化。

在数据之外，数据库系统维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

上图展示了一种可能的索引方式。左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。

为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在O(log2n)的复杂度内获取到相应数据。

虽然这是一个货真价实的索引，但是实际的数据库系统几乎没有使用二叉查找树或其进化品种红黑树（red-black tree）实现的，原因会在下文介绍。

4.1 索引的优缺点

优点：

• 可以快速检索，减少I/O次数，加快检索速度；
• 根据索引分组和排序，可以加快分组和排序；

缺点：

• 索引本身也是表，因此会占用存储空间，一般来说，索引表占用的空间的数据表的1.5倍；
• 索引表的维护和创建需要时间成本，这个成本随着数据量增大而增大；
• 构建索引会降低数据表的修改操作（删除，添加，修改）的效率，因为在修改数据表的同时还需要修改索引表；

4.2 索引的分类

4.2.1 按类型分类

1. 聚集索引
   • 主键索引；
     • 数据列不允许重复，不允许为NULL，一个表只能有一个主键。
2. 二级索引（又称辅助索引、非聚簇索引）
   • 唯一索引；
     • 约束数据列不允许重复，允许为NULL值
     • 一个表允许组合多个列创建唯一索引，这时约束的是：不同记录，被唯一索引约束的这多个列不能让完全相同
   • 普通索引（又叫辅助索引）；
     • 可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引
     • 会对该列创建索引。
   • 组合索引(又称联合索引，复合索引)；
     • 即普通索引的多字段版本
     • 可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引
     • 如下图，可以理解成把几个字段拼接起来的一个普通索引
     • 

4.2.2 按按数据结构分类

• BTree索引
  • 下文详解
• B+Tree索引；
  • 下文详解
• 哈希索引；
  • 只有memory存储引擎支持哈希索引，哈希索引用索引列的值计算该值的hashCode，然后在hashCode相应的位置存储该值所在行数据的物理位置。
  • 因为使用散列算法，因此访问速度非常快，但是一个值只能对应一个hashCode，而且是散列的分布方式，因此哈希索引不支持范围查找和排序的功能。
• 全文索引；
  • 通过建立倒排索引来实现，查询效率比like有很大提升。
  • 5.6版本前的MySQL自带的全文索引只能用于MyISAM存储引擎，如果是其它数据引擎，那么全文索引不会生效。5.6版本之后InnoDB存储引擎开始支持全文索引
  • 在MySQL中，全文索引支队英文有用，目前对中文还不支持。5.7版本之后通过使用ngram插件开始支持中文。

4.2.3 聚簇索引和非聚簇索引的区别（针对InnoDB）

假设我们有如下表

mysql对ID生成了聚簇索引，我们再对k字段生成普通索引（非聚簇），如下图：

其中R代表一整行的记录。

从图中不难看出，聚簇索引和非聚簇索引的区别是：非聚簇索引的叶子节点存放的是主键的值，而聚簇索引的叶子节点存放的是整行数据。

根据这两种结构我们来进行下查询，看看他们在查询上有什么区别。

1. 如果查询语句是 select * from table where ID = 100，即主键查询的方式，则只需要搜索 ID 这棵 B+树。

2. 如果查询语句是 select * from table where k = 1，即非主键的查询方式，则先搜索k索引树，得到ID=100，再到ID索引树搜索一次，这个过程也被称为回表。

什么非主键索引结构叶子节点存储的是主键值？
一是保证一致性，更新数据的时候只需要更新主键索引树，二是节省存储空间。

4.2.4 为什么建议使用主键自增的索引

自增的主键，插入到索引的时候，直接在最右边插入就可以了

但是如果插入的是 ID = 350 的一行数据，由于 B+ 树是有序的，那么需要将下面的叶子节点进行移动，腾出位置来插入 ID = 350 的数据，这样就会比较消耗时间，如果刚好 R4 所在的数据页已经满了，需要进行页分裂操作，这样会更加糟糕。

所以使用自增主键，每次插入的 ID 都会比前面的大，那么就可以避免这种情况。

4.3 索引的数据结构

索引的数据结构，常见的是B树和B+树，MySql的索引使用的是B+树，关于B树一家子的分析，可以详见下文：B树/B+树分析

不过虽然都是使用B+树来做数据结构，但在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的（不过至少都是B+树）。

4.3.1 MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构，其主键索引和普通索引在结构上没有区别，叶节点的data域存放的是数据记录的地址。

4.3.1.1 MyISAM主键索引

如下图，这时一个针对主键col1字段的索引结构图：

可以看出MyISAM的索引文件仅仅保存数据记录的地址。

4.3.1.2 MyISAM普通索引

在MyISAM中，主索引和普通索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而普通索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

发现没有？MyISAM的索引方式，跟我们上文说的非聚簇索引十分相像（一个是存放id，一个是存放地址）。所以MyISAM索引的实现方式是非聚簇索引。

4.3.2 InnoDB索引实现

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。对，InnoDB的索引是聚簇式的：InnoDB的数据文件本身就是索引文件，树的叶节点data域保存了完整的数据记录。

4.3.2.1 InnoDB主键索引实现

我们先来看 InnoDB的主键索引，这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

4.3.2.2 InnoDB普通索引实现

在MyISAM中主索引和普通索引（Secondary key）在结构上没有任何区别，但InnoDB中，普通索引和主键索引是不同的，前文我们也介绍过，InnoDB的普通索引是非聚簇式的。

例如，图11为定义在Col3上的一个辅助索引：

图中的15,18这些数字，就是col3所对应的主键值，普通索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

4.3.3 联合索引的数据结构

我们知道了Mysql的索引采用B+树，那么，联合索引的B+树长什么样呢？？

假如我们有一张表

那么，联合索引的B+树结构是长这样的：

即每个元素的key，都是b,c,d三个字段的组合。那么不同元素之间的排序是依照什么规则呢？第一列的值大小吗？

答案是：先判断 b 再判断 c 然后是 d，即优先级为b>c>d。

4.4 索引生效条件

我们创建了索引，但很多时候，我们发现我们的查询语句无法使用到索引，基于此，我们首先要了解索引的命中规则。

那么怎么知道我们写的sql语句是否有使用到索引呢，可以使用explain命令，直接在sql语句前加explain执行：

explain执行结果关注以下几个字段：

1. select_type:
   • 查询的类型，主要是区别普通查询和联合查询、子查询之类的复杂查询
     • SIMPLE：查询中不包含子查询或者UNION
     • 查询中若包含任何复杂的子部分，最外层查询则被标记为：PRIMARY
     • 在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY
2. possible_keys
   • 表示查询时可能使用的索引。如果是空的，没有相关的索引。这时要提高性能，可通过检验WHERE子句，看是否引用某些字段，或者检查字段不是适合索引
3. key
   • 显示sql执行过程中实际使用的键或索引，如果为null则表示未使用任何索引，必须进行优化。
4. rows
   • rows是指这次查找数据所内循环的次数。
5. Extra:
   • 执行情况的说明和描述。包含不适合在其他列中显示但十分重要的额外信息
6. type
   • type意味着类型，这里的type官方全称是“join type”，意思是“连接类型”,这样很容易给人一种错觉觉得必须需要俩个表以上才有连接类型。事实上这里的连接类型并非字面那样的狭隘，
   • 它更确切的说是一种数据库引擎查找表的一种方式，在《高性能mysql》一书中作者更是觉得称呼它为访问类型更贴切一些。
   • mysql5.7中type的类型达到了14种之多，这里只记录和理解最重要且经常遇见的六种类型，它们分别是all。从左到右，它们的效率依次是增强的。
   • 撇开sql的具体应用环境以及其他因素，你应当尽量优化你的sql语句，使它的type尽量靠右，但实际运用中还是要综合考虑各个方面的。

SELECT * from t1 WHERE b = 1 and c = 2 and d = 3; //走索引
SELECT * from t1 WHERE b = 1 and c = 2                  //走索引
SELECT * from t1 WHERE b = 1                                  //走索引
SELECT * from t1 WHERE c = 2 and d = 3;                 //不走索引
SELECT * from t1 WHERE d = 3;                                 //走索引
SELECT * from t1 WHERE b = 1 and d = 3                 //走部分索引，至少会走到b = 1的子树上。

//范围查询
SELECT * from t1 WHERE b < 1;                                  //走索引
SELECT * from t1 WHERE b < 1 and c < 2 and d>3;   //走部分索引，b<1走了索引，后面两个条件无法走索引。（索引最多用于一个范围列）

SELECT count(DISTINCT(first_name))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.0042 |
+-------------+
SELECT count(DISTINCT(concat(first_name, last_name)))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.9313 |
+-------------+

SELECT count(DISTINCT(concat(first_name, left(last_name, 3))))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.7879 |
+-------------+

SELECT count(DISTINCT(concat(first_name, left(last_name, 4))))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.9007 |
+-------------+

ALTER TABLE employees.employees
ADD INDEX `first_name_last_name4` (first_name, last_name(4));

SHOW PROFILES;
+----------+------------+---------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                           |
+----------+------------+---------------------------------------------------------------------------------+
|       87 | 0.11941700 | SELECT * FROM employees.employees WHERE first_name="Eric" AND last_name="Anido" |
|       90 | 0.00092400 | SELECT * FROM employees.employees WHERE first_name="Eric" AND last_name="Anido" |
+----------+------------+---------------------------------------------------------------------------------+