DataBase Notes

引言：

针对面试的mysql数据库总结

术语

实体(Entity)

客观存在并可相互区别的事物称之为实体。可以看成是Java类

例子：(课程关系表)就是一个实体。

属性(Attribute)

实体所具有的某一特性称之为属性。可以看成是Java类的成员变量。属性在数据库中又称为字段（或者是列）

例子：(课程名)，(课程号)、(学时)就是属性名。

元组

除含有属性名所在的行之外的其他行称之为元组。

下面的每一行数据都称之为元组
(C401001 数据结构 70)
(C401002 操作系统 80)
(C402001 计算机原理 60)

码(Key)

码也被称作是关键字。它可以唯一标识一个实体。

候选码和主码：

候选码：如果一组属性集能唯一地标识一个关系中的元组而又不含有多余的属性，则称该属性集为该关系的候选码。（候选码可能不止有一个）
主码：用户选定的那个候选键称为主键

例子：邮寄地址（城市名，街道名，邮政编码，单位名，收件人）

它有两个候选键:{城市名，街道名} 和 {街道名，邮政编码}

如果我选取{城市名，街道名}作为唯一标识实体的属性，那么{城市名，街道名} 就是主码

关系模式

关系名和其属性集合的组合称之为关系模式。

关系模式例子：课程关系表(课程号,课程名,学时)

域

关系模型要求元组的每一个分量都是原子性的，也就是说，它必须属于某种元素类型，如Integer、String等等，不能是列，集合，记录，数组！

域就代表着该元组中每个分量的类型，例如：课程号:string,课程名:string,学时:int

概述

1566261820297

数据库最主要的功能是存储数据，因此他有一个存储模块，将数据持久化存入磁盘中；

我们还需要组织并且用到这些数据，所以需要程序实例来映射出物理结构。

实际程序时要考虑：存储管理（尽量优化减少IO操作），缓存机制（优化访问），sql解析（操作数据库，优化可将sql放入缓存，编译好的sql可以直接用），日志管理（记录操作），权限划分，异常机制（容灾），索引管理（优化查询），锁管理（并发）

索引模块

为什么使用索引？

直接加在到内存中，进行全表扫描，很慢。使用索引避免全表查询，加速查询数据；

磁盘文件存储

页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位交换数据。

InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB 存储引擎中默认每个页的大小为 16 KB，因此 InnoDB 每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小 16KB 。InnoDB 在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘 I/O 次数，提高查询效率。

文件系统及数据库系统的设计者利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。

什么样的信息能够称为索引？

主键，唯一键，普通键，有一定区分性

主键：唯一标识表中的每一行数据，特点不能为空！！！不能重复！！！

auto_increment的字段必须是主键, 但是主键不一定是auto_increment的, 只要是唯一的就可以一个表只能有一个主键, 但是主键可以是1个或多个字段组成

唯一键：将表中的某个字段设置为不可重复值，可以将其设为唯一键！！！

唯一键不是主键，但主键有不可重复性

一张表可以有多个唯一键，但只能有一个主键

有了关键字索引还不行，还需要以某种数据结构将其组织起来才能够使检索更高效。

索引的数据结构？

二叉树：二分查找，缺点：每个节点只能存储两个节点，树的深度很深，IO的操作就会很多，效率就很差

B数：树的每个节点最多有m个孩子，就是m阶B树，下图3阶：

1566261830802

特性：

1566261842897

1566261850318

让每个索引块尽可能存储更多信息，让树的高度低，减少IO次数；

B+树：

1566261860507

结论：B+树更适合，原因：

B+树的磁盘读写代价更低，内部（非叶子节点）并没有指向关键字具体信息的指针，不存放数据只存放索引信息。
查询效率更稳定，每次查询都是根节点到叶子节点的路径，查询基本一样
只需要遍历叶子节点就可以完成对全部关键字的扫描，所以他更有利于对数据库的扫描。（更适合范围条件查询）

Hash索引了解一下：

缺点：

比较进行hash运算之后的值，仅满足等值查询，不能使用范围查询；
无法运用索引值来排序
不能利用部分索引键查询
不能避免表扫描，哈希值可能重复，需要全表扫描
大量hash值相等时，效率很底。

密集索引和稀疏索引

密集索引：叶子节点不仅保存了索引值，还保存了其同一行的其他列（属性）的数据。

所有完整的用户记录都存放在这个聚簇索引的叶子节点处。在InnoDB存储引擎中，聚簇索引就是数据的存储方式

稀疏索引：叶子节点仅保存了键位信息（索引键位）及其主键。

InnoDB的索引

1566261871063

如何定位并优化sql？

根据慢日志定位慢查询sql（较慢sql执行的记录）
使用explain等工具分析sql
- 一般放在select查询语句前，用于描述MySQL如何执行查询操作，以及MySQL成功返回结果集需要执行的行数
- 字段：type表示MySQL找到数据行的方式，性能最优到最差如图，index/all表示是全表查询。
- 字段：extra，如图
修改sql或者尽量让sql走索引
- 改用索引查
- 添加索引
使用fore_index()测试那个索引更好

联合索引的最左匹配原则

1566262151740

MySQL创建复合索引的规则是首先会对最左边的也就是第一个字段进行排序，在第一个字段排序的基础上再对第二个字段排序，所以第一个字段是绝对有序的，第二个字段就是无序的了，因此通常情况下直接使用第二个字段进行条件判断是用不到索引的。这就是MySQL联合索引强调最左匹配的原因。

索引建立的越多越好吗？

1566262159079

锁

前言：

首先需要了解：

对于UPDATE、DELETE、INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)
MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预

其次需要了解：

InnoDB只有通过索引条件检索数据才使用行级锁，否则，InnoDB将使用表锁

也就是说，InnoDB的行锁是基于索引的！

Myisam：表级锁，不支持行级锁

InnoDB：默认行级锁，支持表级锁，

注：当SQL语句中使用索引作为条件时，使用的是行级索，当不用索引时，整张表会被锁住，使用的是表级锁，

无论表锁还是行锁，默认都分为共享锁和排他锁

1566262385992

场景分析：

MyISAM

适用频繁执行全表count语句；因为有一个变量值存储了该值
适用增删改不高，查询频繁；因为增删改会涉及锁表操作，会产生很多碎片，但是纯查询效率是可以的
适合没有事务的

InnoDB

适合数据增删改查都频繁；增删改时某些行被锁，避免了被阻塞，不像MyISAM每次锁住整张表
支持事务的系统

数据库锁分类：

按粒度划分：表级锁，行级锁，页级锁
按级别划分：共享锁，排他锁
按加锁方式：自动锁（意向锁，MyISAM表锁，以及增删改时的锁），显示锁
按操作划分：DML锁（数据操作），DDL锁（表结构变更）
按使用方式：乐观锁（认为数据处理过程不会发生冲突，提交更新时才会检测，实现方式是记录数据版本：版本号或者时间戳），悲观锁（全程使用排他锁）

乐观锁和悲观锁：

确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性，乐观锁和悲观锁是并发控制主要采用的技术手段。

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作
- 在查询完数据的时候就把事务锁起来，直到提交事务
- 实现方式：使用数据库中的锁机制
乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。
- 在修改数据的时候把事务锁起来，通过version的方式来进行锁定
- 实现方式：使用version版本或者时间戳

事务隔离与锁

并发事务

不同的隔离级别对锁的使用是不同的，锁的应用最终导致不同事务的隔离级别

ACID：原子性，一致性，隔离性，持久性

事务的隔离性以及各级别下的并发问题

更新丢失——MySQL所有事务隔离级别在数据库层面上均可避免

脏读——一个事务读到另一个事务未提交的更新数据，READ-UNCOMMITTED事务隔离级别不能解决，使用READ-COMMITTED（读提交）级别，隔离一个事务读取另一个事务未提交数据

不可重复读——一事务A多次读取数据，另一事务B在此期间修改数据，导致事务A多次读取数据不一致，使用REPEATABLE-READ（可重复读）级别可以避免。

幻读——事务A读取匹配条件的行数据，事务B以插入或删除的方式修改A的结果集，导致事务A产生差错。设置为SERIALIZABLE可以避免。

1566263834174

InnoDB引擎的隔离与锁

多版本并发控制（Multi-Version Concurrency Control, MVCC）是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。

而未提交读隔离级别总是读取最新的数据行，无需使用 MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。

InnoDB基于行锁实现了MVCC多版本并发控制，MVCC在隔离级别下的Read committed和Repeatable read下工作。MVCC能够实现读写不阻塞！

MVCC实现方式

通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot)，并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度来看，好像是数据库可以提供同一数据的多个版本。
版本号
- 系统版本号：是一个递增的数字，每开始一个新的事务，系统版本号就会自动递增。
- 事务版本号：事务开始时的系统版本号。
  - 版本号用来和查询到的每行记录的版本号进行比较。
MVCC 在每行记录后面都保存着两个隐藏的列，用来存储两个版本号：
- 创建版本号：指示创建一个数据行的快照时的系统版本号；
- 删除版本号：如果该快照的删除版本号大于当前事务版本号表示该快照有效，否则表示该快照已经被删除了。

`Read committed`实现

原理：快照读机制，使用 MVCC 读取的是快照中的数据，这样可以减少加锁所带来的开销。

Read committed避免脏读的做法其实很简单：

在读取的时候生成一个版本号，直到事务其他commit被修改了之后，才会有新的版本号，读取时会对比版本号

过程：

事务A读取了记录(生成版本号)
事务B修改了记录(此时加了写锁)
事务A再读取的时候，是依据最新的版本号来读取的(当事务B执行commit了之后，会生成一个新的版本号)，如果事务B还没有commit，那事务A读取的还是之前版本号的数据。

`Repeatable read`实现

Read committed会出现不可重复读：一个事务可以看到其他事务所做的修改。

例如，A查询数据库得到数据，B去修改数据库的数据，导致A多次查询数据库的结果都不一样（危害：A每次查询的结果都是受B的影响的，那么A查询出来的信息就没有意思了）

Read committed是语句级别（执行语句）的快照！每次读取的都是当前最新的数据行版本！

Repeatable read避免不可重复读是事务级别的快照！每次读取的都是当前事务的版本，即使被修改了，也只会读取当前事务版本的数据。

总结：事务开启时使用系统版本作为事务版本号，事务开启时数据行更新后使用最新数据行版本号，读提交实现使用数据行版本号的比对，确保每次commit被修改了之后，才会有新的版本号，通过版本号实现隔离；重复读实现对比的是快照中的事务版本号，即在 tempdb 中存储原始行的副本，和为该行添加事务版本号，通过版本号实现隔离。

InnoDB在可重复度隔离级别下是如何避免幻读？

引言：

表象：快照度（非阻塞读）——伪MVCC

内在：next-key锁（行锁+gap锁）

1566264130165

当前读表示：读取的是记录的最新版本，并且读取后保证其他并发事务不能修改当前记录，对读取记录加锁。

快照读是基于提升并发性能的考虑，基于多版本并发控制（MVCC），他是行级锁的变动，但是他在很多情况下避免了加锁操作，开销更低，由于是基于多版本快照，所以读到的数据不是最新版本。

RC、RR级别下InnoDB的非阻塞读（快照读）如何实现？

每行数据的额外字段，DB_TRX_ID（最近一次事务标识符），DB_ROLL_PTR（回滚指针段），DB_ROW_ID（行号，隐藏主键）
undo日志，当我们对记录做了变更操作时就会产生undo日志，其中存储的是老版数据

例：数据（11，12，13）修改为（11，32，13）时，会发生：

1566265001814

再次修改为（11，32，45）时会发生：

1566264973315

read view可见性判断，当使用快照读时，会创建一个read view，告知我们读的是哪一个版本，根据可见性算法，将DB_TRX_ID取出与系统其他活跃事务ID对比，如果大于或者等于，就取出undo log中的版本，直到取出小于活跃事务ID号（事务ID是递增状态，越新开启的事务，ID越大）

因为生成时机？？？的不同造成RC,RR隔离级别的不同可见性，在RR级别下，事务在开启事务的第一条快照读会创建一个快照，即read view，将当前系统中活跃的其他事务记录起来，此后在调用快照读时还是使用同一个read view；而在RC级别下，事务中每次调用快照读时都会创建一个新的快照，这就是为什么在RC下能够看到别的事务提交的对表记录的增删改。而在RR下，如果首次使用快照读是在别的事物对数据做出增删改并提交之前的，此后即便别的事物对数据进行增删改并提交还是读不到数据变动的原因。对RR来说，首次事务调用快照读的时机很重要。

正是因为上面三个原因，使得InnoDB在RR、RC级别支持非阻塞读，而读取数据时的非阻塞就是MVCC，而InnoDB的非阻塞读实现了MVCC的仿照版；MVCC代表多版本并发控制，读不加锁，读写不冲突，在读多写少的应用中，读写不冲突很重要。这里仅实现伪MVCC机制是因为并没有实现核心的多版本并存，undo log中的内容是串行化的结果，记录了多个事务的过程，不属于多版本共存。

原理：

next-key锁（record锁+gap锁）

record锁：对单个行记录上锁，锁定一个记录上的索引，而不是记录本身。
gap锁：gap表示索引树中插入新纪录的空隙，gap锁即锁定一个范围但不包括记录本身，是为了方式同一事物的两次当前读出现幻读的情况
next-key锁：Record Locks 和 Gap Locks 的结合，不仅锁定一个记录上的索引，也锁定索引之间的间隙。例如一个索引包含以下值：10, 11, 13, and 20，那么就需要锁定以下区间：
1
(-∞, 10],(10, 11],(11, 13],(13, 20],(20, +∞)

在RR及以上级别默认都支持gap锁，RC及以下级别都没有gap锁。

RR级别下gap锁的使用场景，对主键索引或唯一索引会使用gap锁吗？

如果where条件全部命中，就不会用gap锁，只会加记录锁（行锁）
如果where条件部分命中或者未命中，就会加上gap锁

gap锁会出现在非唯一索引或者不走索引的当前读中

非唯一索引：

1566268250911

gap会在（6，9]，（9，11] 这两个区间加上gap锁，防止幻读。

不走索引

1566268427287

会对所有gap上锁，类似表锁，也能防止幻读。

InnoDB在RR级别主要通过引入next-key锁来避免幻读问题，next-key由行锁和gap锁，gap锁会用在非唯一索引或者不走索引的当前读以及仅命中检索条件的部分结果集并且用到主键索引以及唯一索引的当前读中。

SQL优化

在我们书写SQL语句的时候，其实书写的顺序、策略会影响到SQL的性能，虽然实现的功能是一样的，但是它们的性能会有些许差别。

因此，下面就讲解在书写SQL的时候，怎么写比较好。

选择最有效率的表名顺序

数据库的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表将被最先处理

在FROM子句中包含多个表的情况下：

如果三个表是完全无关系的话，将记录和列名最少的表，写在最后，然后依次类推
也就是说：选择记录条数最少的表放在最后

如果有3个以上的表连接查询：

如果三个表是有关系的话，将引用最多的表，放在最后，然后依次类推。
也就是说：被其他表所引用的表放在最后

例如：查询员工的编号，姓名，工资，工资等级，部门名

emp表被引用得最多，记录数也是最多，因此放在form字句的最后面

1
2
3

select emp.empno,emp.ename,emp.sal,salgrade.grade,dept.dname
from salgrade,dept,emp
where (emp.deptno = dept.deptno) and (emp.sal between salgrade.losal and salgrade.hisal)

WHERE子句中的连接顺序

数据库采用自右而左的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之左,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的之右。

emp.sal可以过滤多条记录，写在WHERE字句的最右边

1
2
3

select emp.empno,emp.ename,emp.sal,dept.dname
from dept,emp
where (emp.deptno = dept.deptno) and (emp.sal > 1500)

SELECT子句中避免使用*号

我们当时学习的时候，星号是可以获取表中全部的字段数据的。

但是它要通过查询数据字典完成的，这意味着将耗费更多的时间
使用*号写出来的SQL语句也不够直观。

用TRUNCATE替代DELETE

这里仅仅是：删除表的全部记录，除了表结构才这样做。

DELETE是一条一条记录的删除，而Truncate是将整个表删除，保留表结构，这样比DELETE快

多使用内部函数提高SQL效率

例如使用mysql的concat()函数会比使用||来进行拼接快，因为concat()函数已经被mysql优化过了。

使用表或列的别名

如果表或列的名称太长了，使用一些简短的别名也能稍微提高一些SQL的性能。毕竟要扫描的字符长度就变少了。。。

多使用commit

comiit会释放回滚点…

善用索引

索引就是为了提高我们的查询数据的，当表的记录量非常大的时候，我们就可以使用索引了。

SQL写大写

我们在编写SQL 的时候，官方推荐的是使用大写来写关键字，因为Oracle服务器总是先将小写字母转成大写后，才执行

避免在索引列上使用NOT

因为Oracle服务器遇到NOT后，他就会停止目前的工作，转而执行全表扫描

避免在索引列上使用计算

WHERE子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描，这样会变得变慢

用 `>=` 替代 `>`

低效：
SELECT * FROM EMP WHERE DEPTNO > 3   
首先定位到DEPTNO=3的记录并且扫描到第一个DEPT大于3的记录
高效：
SELECT * FROM EMP WHERE DEPTNO >= 4  
直接跳到第一个DEPT等于4的记录

用IN替代OR

1 2	select * from emp where sal = 1500 or sal = 3000 or sal = 800; select * from emp where sal in (1500,3000,800);

总是使用索引的第一个列

如果索引是建立在多个列上，只有在它的第一个列被WHERE子句引用时，优化器才会选择使用该索引。当只引用索引的第二个列时，不引用索引的第一个列时，优化器使用了全表扫描而忽略了索引

create index emp_sal_job_idex
on emp(sal,job);
----------------------------------
select *
from emp  
where job != 'SALES';    

上边就不使用索引了。

数据库结构优化

1）范式优化：比如消除冗余（节省空间。。）
2）反范式优化：比如适当加冗余等（减少join）
3）拆分表：垂直拆分和水平拆分

服务器硬件优化

money

其他知识点

范式

第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。
第二范式（2NF）：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况），也即所有非关键字段都完全依赖于任意一组候选关键字。
第三范式（3NF）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖，指的是如果存在”A → B → C”的决定关系，则C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系：关键字段 → 非关键字段x → 非关键字段y

总结一下：

首先要明确的是：范式一到三是越来越严格的。满足着第三范式，那么就一定满足第二范式、满足着第二范式就一定满足第一范式
第一范式：字段是最小的的单元不可再分
- 学生信息组成学生信息表，有年龄、性别、学号等信息组成。这些字段都不可再分，所以它是满足第一范式的
第二范式：满足第一范式,表中的字段必须完全依赖于全部主键而非部分主键。
- 其他字段组成的这行记录和主键表示的是同一个东西，而主键是唯一的，它们只需要依赖于主键，也就成了唯一的
- 学号为1024的同学，姓名为Java3y，年龄是22岁。姓名和年龄字段都依赖着学号主键。
第三范式：满足第二范式，非主键外的所有字段必须互不依赖
- 就是数据只在一个地方存储，不重复出现在多张表中，可以认为就是消除传递依赖
- 比如，我们大学分了很多系（中文系、英语系、计算机系……），这个系别管理表信息有以下字段组成：系编号，系主任，系简介，系架构。那我们能不能在学生信息表添加系编号，系主任，系简介，系架构字段呢？不行的，因为这样就冗余了，非主键外的字段形成了依赖关系(依赖到学生信息表了)！正确的做法是：学生表就只能增加一个系编号字段。

视图

视图是一种基于数据表的一种虚表

（1）视图是一种虚表
（2）视图建立在已有表的基础上, 视图赖以建立的这些表称为基表
（3）向视图提供数据内容的语句为 SELECT 语句,可以将视图理解为存储起来的 SELECT 语句
（4）视图向用户提供基表数据的另一种表现形式
（5）视图没有存储真正的数据，真正的数据还是存储在基表中
（6）程序员虽然操作的是视图，但最终视图还会转成操作基表
（7）一个基表可以有0个或多个视图

有的时候，我们可能只关系一张数据表中的某些字段，而另外的一些人只关系同一张数据表的某些字段…

那么把全部的字段都都显示给他们看，这是不合理的。

我们应该做到：他们想看到什么样的数据，我们就给他们什么样的数据…一方面就能够让他们只关注自己的数据，另一方面，我们也保证数据表一些保密的数据不会泄露出来…

视图可以将查询出来的数据进行封装。。。那么我们在使用的时候就会变得非常方便…

CREATE VIEW myview AS
SELECT Concat(col1, col2) AS concat_col, col3*col4 AS compute_col
FROM mytable
WHERE col5 = val;

E-R图

Entity-Relationship，有三个组成部分：实体、属性、联系。

用来进行关系型数据库系统的概念设计。

实体的三种联系

包含一对一，一对多，多对多三种。

如果 A 到 B 是一对多关系，那么画个带箭头的线段指向 B；
如果是一对一，画两个带箭头的线段；
如果是多对多，画两个不带箭头的线段。

下图的 Course 和 Student 是一对多的关系。

表示出现多次的关系

一个实体在联系出现几次，就要用几条线连接。

下图表示一个课程的先修关系，先修关系出现两个 Course 实体，第一个是先修课程，后一个是后修课程，因此需要用两条线来表示这种关系。

联系的多向性

虽然老师可以开设多门课，并且可以教授多名学生，但是对于特定的学生和课程，只有一个老师教授，这就构成了一个三元联系。

表示子类

用一个三角形和两条线来连接类和子类，与子类有关的属性和联系都连到子类上，而与父类和子类都有关的连到父类上。

QA

MySQL表设计要注意什么？

主键是用自增还是UUID?

肯定答自增啊。innodb 中的主键是聚簇索引。如果主键是自增的，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。如果不是自增主键，那么可能会在中间插入，就会引发页的分裂，产生很多表碎片！。

主键为什么不推荐有业务含义?

有如下两个原因

因为任何有业务含义的列都有改变的可能性,主键一旦带上了业务含义，那么主键就有可能发生变更。主键一旦发生变更，该数据在磁盘上的存储位置就会发生变更，有可能会引发页分裂，产生空间碎片。
带有业务含义的主键，不一定是顺序自增的。那么就会导致数据的插入顺序，并不能保证后面插入数据的主键一定比前面的数据大。如果出现了，后面插入数据的主键比前面的小，就有可能引发页分裂，产生空间碎片。

时间字段用什么类型?

(1)varchar，如果用varchar类型来存时间，优点在于显示直观。但是坑的地方也是挺多的。比如，插入的数据没有校验，你可能某天就发现一条数据为2013111的数据，请问这是代表2013年1月11日，还是2013年11月1日？
其次，做时间比较运算，你需要用STR_TO_DATE等函数将其转化为时间类型，你会发现这么写是无法命中索引的。数据量一大，是个坑！

(2)timestamp，该类型是四个字节的整数，它能表示的时间范围为1970-01-01 08:00:01到2038-01-19 11:14:07。2038年以后的时间，是无法用timestamp类型存储的。
但是它有一个优势，timestamp类型是带有时区信息的。一旦你系统中的时区发生改变，例如你修改了时区

1	SET TIME_ZONE = "america/new_york";

你会发现，项目中的该字段的值自己会发生变更。这个特性用来做一些国际化大项目，跨时区的应用时，特别注意！

(3)datetime，datetime储存占用8个字节，它存储的时间范围为1000-01-01 00:00:00 ~ 9999-12-31 23:59:59。显然，存储时间范围更大。但是它坑的地方在于，他存储的是时间绝对值，不带有时区信息。如果你改变数据库的时区，该项的值不会自己发生变更！

(4)bigint，也是8个字节，自己维护一个时间戳，表示范围比timestamp大多了，就是要自己维护，不大方便。

为什么不直接存储图片、音频、视频等大容量内容?

我们在实际应用中，都是用HDFS来存储文件。然后mysql中，只存文件的存放路径。mysql中有两个字段类型被用来设计存放大容量文件，也就是text和blob类型。但是，我们在生产中，基本不用这两个类型！
主要原因有如下两点

(1)Mysql内存临时表不支持TEXT、BLOB这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行。导致查询效率缓慢
(2)binlog内容太多。因为你数据内容比较大，就会造成binlog内容比较多。大家也知道，主从同步是靠binlog进行同步，binlog太大了，就会导致主从同步效率问题！

因此，不推荐使用text和blob类型！

字段为什么要定义为NOT NULL?

(1)索引性能不好

Mysql难以优化引用可空列查询，它会使索引、索引统计和值更加复杂。可空列需要更多的存储空间，还需要mysql内部进行特殊处理。可空列被索引后，每条记录都需要一个额外的字节，还能导致MYisam 中固定大小的索引变成可变大小的索引。

(2)查询会出现一些不可预料的结果
这里举一个例子，大家就懂了。假设，表结构如下

1
2
3

create table table_2 (
    `id` INT (11) NOT NULL,
    name varchar(20) NOT NULL)

表数据是这样的

id	name
1	孤独烟
3	null
5	肥朝
7	null

你执行语句

1	select count(name) from table_2;

你会发现结果为2，但是实际上是有四条数据的！类似的查询问题，其实有很多，不一一列举。
记住，因为null列的存在，会出现很多出人意料的结果，从而浪费开发时间去排查Bug.

索引的特点

是一种快速查询表中内容的机制，
运用在表中某个些字段上，但存储时，独立于表之外

索引的特点

索引一旦建立, DB管理系统会对其进行自动维护, 而且由DB管理系统决定何时使用索引
用户不用在查询语句中指定使用哪个索引
在定义primary key或unique约束后系统自动在相应的列上创建索引
用户也能按自己的需求，对指定单个字段或多个字段，添加索引

什么时候【要】创建索引	什么时候【不要】创建索引
表经常进行 SELECT 操作	表经常进行 INSERT/UPDATE/DELETE 操作
表很大(记录超多)，记录内容分布范围很广	表很小(记录超少)
列名经常在 WHERE 子句或连接条件中出现	列名不经常作为连接条件或出现在 WHERE 子句中

索引优缺点：

索引加快数据库的检索速度
索引降低了插入、删除、修改等维护任务的速度(虽然索引可以提高查询速度，但是它们也会导致数据库系统更新数据的性能下降，因为大部分数据更新需要同时更新索引)
唯一索引可以确保每一行数据的唯一性，通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能
索引需要占物理和数据空间

索引分类：

唯一索引：唯一索引不允许两行具有相同的索引值
主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空
聚集索引(Clustered)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个
二级索引(Non-clustered)：二级索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。

SQL 约束有哪几种？

NOT NULL: 用于控制字段的内容一定不能为空（NULL）。
UNIQUE: 控件字段内容不能重复，一个表允许有多个 Unique 约束。
PRIMARY KEY: 也是用于控件字段内容不能重复，但它在一个表只允许出现一个。
FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
CHECK: 用于控制字段的值范围。

一个 SQL 执行的很慢？

1、大多数情况下很正常，偶尔很慢，则有如下原因

(1)、数据库在刷新脏页，例如 redo log （重做日志）写满了需要同步到磁盘。

(2)、执行的时候，遇到锁，如表锁、行锁。

2、这条 SQL 语句一直执行的很慢，则有如下原因。

(1)、没有用上索引：例如该字段没有索引；由于对字段进行运算、函数操作导致无法用索引。

(2)、数据库选错了索引。

MySQL 基础架构分析

简单来说 MySQL 主要分为 Server 层和存储引擎层：

Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块。

存储引擎：主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redolog 模块。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始就被当做默认存储引擎了。

SQL语句执行过程分析

对于查询等过程如下：权限校验—》查询缓存—》分析器—》优化器—》权限校验—》执行器—》引擎

对于更新等语句执行流程如下：分析器—-》权限校验—-》执行器—》引擎—redo log prepare—》binlog（归档日志）—》redo log commit

常用的存储引擎

Innodb引擎，Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。
MyIASM引擎(原本Mysql的默认引擎),不提供事务的支持，也不支持行级锁和外键。
MEMORY引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不高。

	Innodb	MyIASM
事务	√	×
哈希索引	√	×
全文索引	×	√
存储容量	64TB	无上限

术语

实体(Entity)

属性(Attribute)

元组

码(Key)

关系模式

域

概述

索引模块

为什么使用索引？

什么样的信息能够称为索引？

索引的数据结构？

如何定位并优化sql？

联合索引的最左匹配原则

锁

前言：

场景分析：

乐观锁和悲观锁：

事务隔离与锁

并发事务

InnoDB引擎的隔离与锁

MVCC实现方式

Read committed实现

Repeatable read实现

InnoDB在可重复度隔离级别下是如何避免幻读？

引言：

原理：

SQL优化

选择最有效率的表名顺序

WHERE子句中的连接顺序

SELECT子句中避免使用*号

用TRUNCATE替代DELETE

多使用内部函数提高SQL效率

使用表或列的别名

多使用commit

善用索引

SQL写大写

避免在索引列上使用NOT

避免在索引列上使用计算

用 >= 替代 >

用IN替代OR

总是使用索引的第一个列

数据库结构优化

服务器硬件优化

其他知识点

范式

视图

E-R图

实体的三种联系

表示出现多次的关系

联系的多向性

表示子类

QA

MySQL表设计要注意什么？

主键是用自增还是UUID?

主键为什么不推荐有业务含义?

时间字段用什么类型?

为什么不直接存储图片、音频、视频等大容量内容?

字段为什么要定义为NOT NULL?

索引的特点

SQL 约束有哪几种？

一个 SQL 执行的很慢？

MySQL 基础架构分析

SQL语句执行过程分析

常用的存储引擎

`Read committed`实现

`Repeatable read`实现

用 `>=` 替代 `>`