秋招MYSQL个人总结

1.基本概念：

关系，一张表

元组，记录，一行

属性，字段，一列

 

2.数据库的特点：

共享性高，冗余度低，数据独立性高

 

3.DBS：数据库系统，指在计算机系统中引入数据库后的系统，一般由数据库(DB)、数据库管理系统(DBMS，作用是定义数据库)、应用系统、数据库管理员（DBA）构成。

 

4.数据模型

数据模型意思就是把现实事物抽象成一组数据。（了解）

三要素：数据结构，数据操作，完整性约束

 

概念模型（ER图，面向用户，提供清晰的关联图）->逻辑模型（面向操作系统，细化每一个表应该有哪些属性的设计，分层次模型（按树建模，一对一，一对多），网状模型（多对多）等）->物理模型（底层数据库物理上如何实现）

ER图包括：属性，实体，联系

数据库ER图基础概念整理https://blog.csdn.net/belen_xue/article/details/52763629

数据库建模三步骤：概念模型->逻辑模型->物理模型https://blog.csdn.net/huojiao2006/article/details/52935321

 

5.三级模式，两级映射

模式：就是我们数据库中的全体数据的逻辑结构和特征。只有一个，包含了所有数据的视图。

外模式（子模式）：外模式是模式的一个子集，不同用户从不同角度部分看待数据库的方式，当前视图也是外模式。比如使用select查找某个表，用户所能看到的视图。可以有多个。

内模式，物理结构和存储方式的描述，比如存储范式是顺序的还是hash的等。底层一般不需要管理。只有一个。

两级映射

外模式/模式映像，当模型变化，改变映像，则不需要改变外模式。

模式/内模式映像，当内模式变化，改变映像，则不需要改变模式。

不同层之间建立联系，保证逻辑和物理独立性。

 

6.键，约束，索引（数据库中的索引、键和约束https://www.2cto.com/database/201504/391818.html）

键，指实体的一个或一组属性。

约束，指对某一组属性插入和删除的规则，分表级约束和列级约束。（指的是规则，键指的是满足规则的属性）

索引，是物理层概念，而非逻辑概念，指对表的一组或多组属性进行排序的结构。

符合某种约束的键对应主键，外键，约束的实现有的使用的是索引。

 

    1）约束：（5种）

外键约束（foreign key），主键约束（primary key），唯一约束（unique），默认约束，非空约束（not null）

主键约束：唯一的，不为null，不重用（删除则不会重用）

外键约束：外键列和参照列必须创建索引，外键列无会自动创建。

唯一约束：可为null，可重用，唯一，不可是外键。

默认约束：指插入为null时的默认值，在建表时设置default

 

    2）键：（4种）

主键，外键，候选键（指可以当作主键的一组或多组属性，且没有冗余），超键（指可以当作主键的一组或多组属性）

 

    3）索引：（3种）

普通索引（只能优化查询，不构成约束），键索引（分主键索引，唯一索引，构成约束），全文索引（文本检索，优化查询（云笔记））

底层分两种：聚集索引和非聚集索引。

innoDB只能有一个聚集索引。

文本索引（两种引擎都支持，分词，使用倒排索引，每个单词对应一个索引项）

 

7.数据库范式

第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。列不可再分。

第二范式（2NF）：非主属性不部分依赖于候选码，如果依赖于主键，则需要依赖于所有主键，不能存在依赖部分候选主键的情况，行不可再分。

第三范式（3NF）：非主属性不传递依赖于候选码，在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖，指的是如 果存在"A → B → C"的决定关系，则C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系： 关键字段 → 非关键字段 x → 非关键字段y。主键外，表不可分。

BC范式（BCNF）：在满足第二第三范式的情况下，主属性内部也不能部分或传递依赖。满足第三范式的数据库表应该不存在如下依赖关系： 关键字段 → 关键字段 x → 非关键字段y，主键内，表不可分

 

8.数据库的事务

事务四大特性（ACID)

    ①原子性(Atomicity)：事务中的所有元素作为一个整体提交或回滚，事务的个元素是不可分的，事务是一个完整操作。

  ②一致性(Consistemcy)：事物完成时，数据必须是一致的，也就是说，和事物开始之前，数据存储中的数据处于一致状态。保证数据的无损。

  ③隔离性(Isolation)：对数据进行修改的多个事务是彼此隔离的。这表明事务必须是独立的，不应该以任何方式以来于或影响其他事务。

  ④持久性(Durability)：事务完成之后，它对于系统的影响是永久的，该修改即使出现系统故障也将一直保留，真实的修改了数据库

在SQL语言中,定义事务的语句有三条： 

BEGIN TRANSACTION 

COMMIT 

ROLLBACK

 

9.事务的并发问题：

读：脏读，（读一次，得到不确定的数据，比如该数据正被另一个事务进行修改而未提交）

不可重复读，（读两次，在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的。）

幻读，（当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。）

不可重复读与幻读的区别，不可重复读只是针对某数据的读取，考虑的是读取数据的变化，锁行就行，而幻读是要考虑是否多出一行数据，也考虑自身数据的变化，范围更大一点，需要锁表。

更新：第一类丢失更新：回滚覆盖B事务的更新。

第二类丢失更新：提交覆盖B事务的更新。

 

10.隔离级别：

Read Uncommitted（读取未提交内容）所有事务可以看到未提交事务的执行结果，用的少。

Read Committed（读取提交内容）大多数数据库系统的默认隔离级别（Oracle，但不是MySQL默认的）

Repeatable Read（可重读）MySQL的默认事务隔离级别。行锁

Serializable（可串行化） 表级锁

 

11.ACID的mysql实现：

undo log是逻辑日志，rollback操作只会逻辑地将数据库回滚到原来的操作，而物理上数据结构和页本身是不会恢复的。重要的使用是MVCC

 

A-数据库依赖 undo 日志实现事务的原子性。

 

Redo 日志记录事务后的修改数据，记录的是页的物理修改操作, 用来保证事务的原子性和持久性。Undo 日志记录事务前的原始数据，用来保证事务的一致性和原子性。回滚行记录到某个特定版本，是逻辑日志。在恢复时，数据库先检查事务中断在什么阶段：如果事务中断在 commit 阶段（事务已经完成但是没有写入），则重放 Redo 日志；如果中断在事务执行过程中，则利用 Undo 日志进行回滚。

 

C-一致性

undo log是逻辑日志，只有在事务完成后才修改redo log日志，保证了事务的一致性（总是从一个状态转换为下一个一致性状态）。

 

I-隔离性

有三种隔离级别。隔离性：事务之间的操作是相对独立的，事务提交前对其他事务不可见。

 实现事务隔离的主要手段是锁，InnoDB利用Next-key Lock锁可以解决幻读（在repeatable级别一定条件下解决了幻读）。另外一个关键技术是 MVCC (Multi-version Concurrency Control), 它可以在一些场景避免加锁, 实现同时读写，且可以解决幻读（快照读的情况）。

 

• 在快照读读情况下，mysql通过mvcc来避免幻读。

• 在当前读读情况下，mysql通过next-key来避免幻读

 

    不同的隔离级别在数据库中的加锁策略不一样：

 

    Read uncommitted - 读不需要加锁，写仅仅需要加行锁。

 

    Read committed - 需要读加共享锁（读完释放），写加排他锁，必须等待事务结束才释放。

 

    Repeatable reads - 需要读加共享锁，写加排他锁，都要求在事务结束后释放。

 

    Serializable - 读写加表锁，事务结束后释放。

 

    MVCC 则优化了事务读的场景。数据库保存记录的多个版本，在进行更新时，其他只读事务 / 非事务读可以直接访问记录的上一个版本，不需要等待锁。用undo Log，乐观锁。

 

D-持久性

redo Log存储的是页的物理操作，事务完成表示已经持久化。

 

12.数据库的锁（InnoDB）

行锁，共享锁，排他锁

表锁，读/写意向锁，表示事务想要获得一张表某几行的共享/排他锁

InnoDB的行锁算法分为三种，Record Lock锁住索引记录，而非记录本身（是依赖索引实现的，没有索引则会锁表），Gap Lock锁住索引记录间隙（不包含索引记录自身），Next-Key Lock其他两种的组合。默认级别隔离级别是Repeatable，使用的是Next-Key Lock，由于间隙锁的存在，不会发生幻读。

1.如果写条件没有走索引，需要全表检查，则加表锁。

2.如果是非唯一索引和辅助索引，使用Next-Key Lock的原因：

a）、首先要保证在符合条件的记录上加上排他锁，会锁定当前非唯一索引和对应的主键索引的值；

b）、还要保证锁定的区间不能插入新的数据。

3.如果是唯一的聚集索引，使用Record Lock，将主键索引值和唯一索引值加上记录锁。但不使用Gap Lock（间隙锁）。（其实这样会有幻读？？）（所以尽量使用唯一索引，如果唯一索引由多列组成，要求查询索引的全部列）

 

意向锁：IX,IS都是表锁，意向锁直接不冲突，也不会和行级的X，S锁发生冲突。意向锁是在添加行锁之前添加。

作用：为了方便检测表级锁和行级锁之间的冲突，就引入了意向锁。

MyISAM用的是表锁，InnoDB用的是行锁，也支持表锁，当对某行加行锁时，会在加锁之前自动在表上加意向锁，表示这个表准备加读/写锁，（表操作）避免了逐行查询。意向锁是表级，所以与X的冲突指的是表级的X锁。

数据库锁的使用是由DBMS决定的。

 

MVCC(Multi-version Concurrency Control)多版本并发控制

通过undo log保存行之前版本的数据，可以允许一致性非锁定读（快照读）。在事务隔离界别READ COMMITTED，REPEATABLE READ下，使用MVCC，但是对于前者，总是读取锁定行最新的一份快照数据，对于后者，总是读取事务开始时的行数据版本。

innodb的MVCC实现不是完全意义上的多版本控制，因为修改操作时仍旧会添加排它锁，undo log中的内容只是串行化的结果，记录了多个事务的过程，把修改前的数据存放于undo log，通过回滚指针与主数据关联，修改成功（commit）啥都不做，失败则恢复undo log中的数据（rollback）。

所谓的避免幻读，并不能保证读到的是最新的值，比如事务A先修改了行a，事务B读取时，只能看到之前的数据，不管事务A是不是很早就已经修改了，只是还没有提交。而next keylock的办法，可以解决这个问题。

https://www.cnblogs.com/chenpingzhao/p/5065316.html

一致性锁定读（当前读）：（加表锁，比如事务中操作外键的子表时，内部对父表使用当前读，防止死锁）

SELECT ... FOR UPDATE

SELECT ... LOCK IN SHARE MODE

 

13.存储过程

存储过程是一些预编译的SQL语句。

优：1.预编译过，执行速度快。（对于MySQL来说，不会预编译，每次执行都要编译）

2.保存在数据库中，只需要存储过程名进行调用，减少网络通讯。

3.安全性高，需要权限。

4.可重用

缺：移植性差

 

14.触发器

是特殊的存储过程，通过事件触发，用来强化约束。

班级表 class(班级号 classID, 班内学生数 stuCount)

学生表 student(学号 stuID, 所属班级号 classID)

要创建触发器来使班级表中的班内学生数随着学生的添加自动更新

 

15.drop、delete与truncate分别在什么场景之下使用？

1. delete删除数据可以rollback，操作会放到rollback segment中，事务提交后生效，如果有触发器，执行时会被触发。而truncate不可以恢复，也不会触发触发器。

2. truncate和 delete只删除数据不删除表的结构，drop会删除表结构，包括被依赖的约束，索引，触发器，相应的存储过程/函数将保留，但变成invalid状态。

3. delete语句不影响表占用的空间，高水线保持原位置不变（delete删除后，自增ID不归0，truncate会归0）

4. 速度 ： drop>; truncate >; delete

 

16.sql优化/索引优化

索引的优缺点：

优：

1.索引可以实现约束，例如唯一索引实现唯一约束，主键索引实现主键约束。

2.合理使用可以增加检索速度。将随机IO变成顺序IO。

3.在使用分组和排序 子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

 

缺:

1.创建和维护需要时间.

2.索引占用物理空间,特别是聚集索引.

3.没有合理建立和使用索引,效果反而变差.

 

InnoDB的索引底层是B+树实现的，分成聚集索引和非聚集索引。

聚集索引和非聚集索引：

区别主要是叶子节点除了主键外，data存储的是数据的地址（非聚集）还是实际的行数据（聚集）。

 

每个InnoDB表具有一个特殊的索引称为聚集索引。

1.如果您的表上定义有主键，该主键索引是聚集索引。

2.如果你不定义为您的表的主键时，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB使用它作为聚集索引。

3.如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。

 

辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。在MySQL5.0之后支持覆盖索引，支持查询联合索引直接返回查询结果（查询字段在辅助索引字段范围内），但是要求查询的数据量是少量的（小于20%）。

 

sql优化

主要是为了提高运行速度，有两点：一个是控制查询结果，二是灵活建立、使用索引。

对于一：

1.避免使用*，只需返回需要的字段。

2.灵活使用limit分页。

 

对于二：

建立索引：

1.1.不要随便建索引，对经常使用的列创建索引（查询，排序，表联结），

    1.Where子句中经常使用的字段应该创建索引，

    2.分组字段或者排序字段应该创建索引，

    3.两个表的联合查询字段应该创建索引。

2.更新频繁的字段不适合创建索引，不会出现在where子句中的字段不应该创建索引。

3.某个字段的值离散度越高,适合建立索引.

4.占用存储空间少的字段更适合选作索引的关键字。例如，与字符串相比，整数字段占用的存储空间较少，因此，较为适合选作索引关键字。

5.使用短索引，可以考虑最左前缀原则，字符串创建前缀索引

 

使用索引：

1.避免where中进行or，in判断，null值判断，between（其实是可以使用索引的），like判断（以通配符开头%或没有使用常量），！=或<>，否则会放弃索引，进行全表查询。

   使用索引的： <，<=，=，>，>=，BETWEEN，IN(in的子查询处理的不好，会不使用索引，in完全可以用子查询联合语句代替)

    不使用索引的：<>，not in ，！=，OR，NULL，not in

2.避免使用函数，表达式，否则会放弃索引，进行全表查询。

3.最左前缀原则

 

6.用exists代替in，多使用select子句。？？

7.避免使用游标和建立临时表

3.使用varchar代替char，对小字段的查询效率会更高。

4.使用数字型代替字符型，字符串比较需要逐字比较，数字只比较一次。

 

17.B+树：为磁盘或其他直接存取辅助设备设计的一种平衡查找树，更矮胖且匹配磁盘页面大小（磁盘预读和局部性原理）

是什么？

1.根节点至少有两个子女

2.每个非叶子节点由n个key和n个指针组成，其中d<=n<=2d。（每个节点申请相同大小存储空间）

3.中间节点不保存数据，只用来索引，所有数据保存在叶子节点。

3.所有叶子结点包含了所有中间节点的key，且在同一层，具有相同的深度，即树高h

B+树的叶子节点用链表连接。

 

为什么要使用B+,B树？（笔记：索引）

1）三个前提，磁盘读写效率比内存低，局部性原理（一个数据被用到时，其附近的数据也通常会马上被使用。）和磁盘预读原理（预读页（4K）的整数倍）。

2）B树是一种矮胖的数据结构（高度一般不超过3，4），检索一次访问的节点最多需要h次IO。一个节点通常被保存为页的整数倍，方便磁盘读取。

 

B+树与B-树的区别：

1. B+树的中间节点没有卫星数据，所以同样的大小的磁盘页可以容纳更多的节点元素。即更“矮胖”，IO次数更少。

2. B+树的查询必须最终到叶子节点，B-树只需找到匹配元素即可。因此B-树的查找性能不稳定（最好时只查根节点，最坏查到叶子）。而B+树每次查找都比较稳定。

3. 所有叶子节点形成有序链表，便于范围查询。方便优化。

 

18.InnoDB和MyISAM的区别？

1.事务，MyISAM不支持事务，但是每次查询都是原子的。InnoDB支持事务，并有四种隔离级别；适于并发操作

2.锁，表锁和行锁

3.索引，聚集索引和非聚集索引

4.OLTP，OLAP

 

MyISAM的优势：

1.在表有读取查询的同时，支持往表中插入新纪录。

2.支持 BLOB 和 TEXT 的前 500 个字符索引，支持全文索引。

3.支持延迟更新索引，极大提升写入性能。

4.对于不会进行修改的表，支持压缩表，极大减少磁盘空间占用。

 

InnoDB的优势：

1.更新密集的表。InnoDB存储引擎特别适合处理多重并发的更新请求。

2.事务。InnoDB存储引擎是支持事务的标准MySQL存储引擎。

3.自动灾难恢复。与其它存储引擎不同，InnoDB表能够自动从灾难中恢复。

4.外键约束。MySQL支持外键的存储引擎只有InnoDB。

5.支持自动增加列AUTO_INCREMENT属性。

 

总结：

1） MyISAM不支持事务，优势是大量数据的存储（压缩表）和查询（写入性能好，查询性能好，现在用MongoDB）。锁是表锁

2） InnoDB，只有它支持外键约束，另外，它可以处理高并发事务。锁是行锁，支持全文索引。只有通过索引条件检索数据，InnoDB才使用行级锁，否则，

注意：InnoDB将使用表锁！间隙锁只会出现在辅助索引（index）上，唯一索引(unique)和主键索引是没有间隙锁。

https://blog.csdn.net/dcj0913/article/details/39252153

https://www.2cto.com/database/201504/390838.html

 

19.哈希索引？

InnoDB有自适应哈希索引，字典查找，冲突机制采用链表方式，由数据库内部创建并使用。

1.只用于单值判断，=或<=>操作符，不能进行范围处理。

2.不带排序效果。

3.不能避免表扫描。？？？

 

20.union和union all区别？

union和union all的区别是,union会自动压缩多个结果集合中的重复结果，而union all则将所有的结果全部显示出来，不管是不是重复。

 

21.explain的使用？

分析sql语句执行计划，查看是否使用索引还是全表查询。

 

22.乐观锁和悲观锁

乐观锁用于事务冲突较少发生的情况，且事务回滚成本低。利用版本号或者时间戳判断是否冲突，发生冲突交由用户决定如何处理（回滚or其他）

悲观锁表示事务中的操作会直接对目标加锁，保证执行可靠性。innodb是悲观锁。（MVCC）

 

23.MVCC多版本并发控制

在每一行记录的后面增加两个隐藏列，记录创建版本号和删除版本号，而每一个事务在启动的时候，都有一个唯一的递增的版本号。 行数据删除时，只需要在该行添加删除版本号，而不是直接删除行。

 

快照读和当前读？

快照读：读取的是快照版本，也就是历史版本

当前读：读取的是最新版本

普通的SELECT就是快照读，而UPDATE、DELETE、INSERT、SELECT … LOCK IN SHARE MODE、SELECT … FOR UPDATE是当前读。

（后两种称为锁定读，select语句仍然会进行加锁）

https://github.com/jxnu-liguobin/Java-Learning-Summary/blob/master/src/cn/edu/jxnu/questions/MySQL.md

 

24.char和varchar的区别？

char是定长的，而varchar是可变长的。对于比较固定的字符串，使用char，对于可变长的使用VARCHAR。

可以认为VARCHAR会产生存储碎片，而char不会。另外，varchar的长度设定会影响内存占用的大小，虽然存储空间相同。

 

25.分布式事务的支持？XA事务

XA 规范主要定义了事务管理器(Transaction Manager)和局部资源管理器(Local Resource Manager)之间的接口.

XA事务由一个或多个资源管理器，一个事务管理器，一个应用程序组成。二阶段提交。

 

26.分区表（适用于OLAP，区域查询，比如按时间分区）

MySQL支持水平分区（将同一表中不同行的记录分配到不同的物理文件中），并不支持垂直分区。支持局部分区索引（一个分区中既存放了数据又存放了索引），并不支持全局分区（全局索引文件单独存放）。

MySQL支持的分区类型：

1.range分区，按连续区间的列值分区

2.list分区，list分区面向离散值，对比range分区

3.hash分区，根据用户自定义表达式返回值分区

4.key分区，根据MySQL数据库提供的哈希函数进行分区

 

好处：

1.优化查询速度

2.更容易维护，可以备份和恢复单个分区

3.可以使用分区表来避免某些特殊瓶颈，例如 InnoDB 单个索引的互斥访问、 ext3 文件系统的 inode 锁竞争。

 

缺点：

1.一个表最多只能有 1024 个分区。

2.分区表无法使用外键约束。

3.NULL 值会使分区过滤无效。

 

27.全文索引

是将存储于数据库中的整本书或整篇文章中的任意内容信息查找出来的技术。

数据结构：倒排索引

辅助表中存储了单词与单词自身在一个或多个文档中的位置之间的映射。

两种形式：

inverted file index {单词， 单词所在文档的ID}

full inverted index {单词，（单词所在文档的ID，在具体文档中的位置）} （innodb的实现方式）

有更详细扩展 见书