数据库

储存过程

是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。经过编译和优化后存储在数据库服务器中，使用时只需要调用即可。

优点

• 能够将代码封装起来，保证数据完整性，使一组相关动作在一起执行，可以多次调用。
• 存储过程是一个预编译的代码块，调用存储过程前，数据库已经对其进行优化执行方案已经进行了优化，执行效率比较高
• 存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
• 替代了大量的Transaction-SQL语句，可以降低网络通信量，提高通信速率

缺点

• 每个数据库储存过程的语法都不同，难以维护。

函数

由一个或多个 SQL 语句组成的子程序，可用于封装代码以便重新使用。
和存储过程的区别：

触发器

触发器是一段能自动执行的程序，在对某一个表或者数据进行操作前后触发，例如进行 UPDATE、INSERT、DELETE 操作。和存储过程的区别是，触发器主要是通过事件执行触发而被执行的，而存储过程可以通过存储过程名称名字而直接调用。（触发器一般用于数据变化后需要执行一系列操作的情况，比如对系统核心数据的修改需要通过触发器来存储操作日志的信息等。）

事务

事务的提出主要是为了解决并发情况下保持数据一致性的问题（类似于多线程）。

ACID

• 原子性
  一个事务是无法分割的，也就是说一个事务中的所有操作要么同时成功，要么同时失败。
• 一致性
  一个事务前后的数据完整性要一致。（银行转账案例，A给B转账，B增加金额，A必须减少相应金额）
• 持久性
  事务一旦提交，对数据的改变就是永久性的。即使数据库故障也不会对其有任何影响。
• 隔离性
  多个事务并发运行时，别的事务中对数据的修改在提交前对当前事务中数据是没有影响的。别的事务提交后根据不同的隔离级别可能会对当前事务造成影响。

隔离级别

从低至高：

• 未提交读 问题：脏读、不可重复读、幻读
• 已提交读 问题：不可重复读、幻读
• 可重复读 问题：幻读
• 可串行化 解决所有问题

设置：SET GLOBAL transaction_isolation = '隔离级别';

脏读、不可重复度、幻读

• 脏读：事务A读到了事务B未提交的数据，事务B发生回滚，事务A就读到了脏数据
• 不可重复读：在事务A多次读取一组数据的过程中，事务B对该组数据进行了修改并提交，那么事务A会读到不一样的数值。（针对update操作，解决：使用行级锁，不允许别的事务对这行数据修改）
• 幻读：事务A多次读取数据总量过程中，事务B新增或删除了数据并提交，导致事务A前后读取到的数据总量不一致。（针对insert, delete，解决：使用表级锁，事务A结束前不允许别的事务对该表进行修改）

三大范式

• 1NF 强调列的原子性，即一列不能够被拆分成多个列
  • 学生信息组成学生信息表，有年龄、性别、学号等信息组成。这些字段都不可再分，所以它是满足第一范式的
• 2NF 基于1NF，还要满足一个表必须有一个主键，没有包含在主键中的列必须完全依赖于主键
  • 学号为1024的同学，姓名为Borris，年龄是23岁。姓名和年龄字段都依赖着学号主键。
• 3NF 基于1、2NF，非主键列必须直接依赖主键，不能存在依赖传递，就是说：不能存在非主键列依赖于非主键列B，非主键列B依赖于主键。
  • 比如，大学分了很多系（中文系、英语系、计算机系……），这个系别管理表信息有以下字段组成：系编号，系主任，系简介，系架构。那么就不能在学生信息表添加系编号，系主任，系简介，系架构字段了，因为这样就冗余了，系别管理表非主键外的字段依赖到学生信息表了。正确的做法是：学生表就只能增加一个系编号字段。

SQL分类

• DQL：数据查询语言，SELECT,WHERE,FROM
• DML：数据操作语言，INSERT,UPDATE,DELETE
• DCL：数据控制语言，REVOKE,GRANT,设置用户组访问权限
• DDL：数据定义语言，CREATE,DROP新建删除表

视图

• 是一种虚拟的表，具有和物理表相同的功能，但视图的建立基于已有的表。

• 通过SELECT查询得到数据内容，通常是有一个表或者多个表的行或列的子集。

• 视图没有存储真正的数据，真正的数据还是存储在基表中。

• 有选择性地获取部分数据，可以简化查询。可以从一个表中获取多个视图。

• 对视图的修改会影响基本表。

• 缺点：
  性能：查询视图时，必须把视图的查询转化成对基本表的查询，查询性能没有提升。
  修改：如果这个视图是由一个复杂的多表查询所定义，那么就不可修改数据，因为查询的数据是基于多表的。

索引

类型（主要的）

• 全文索引
  以关键词建立索引，查找的是文本中的关键词，而不是直接比较索引中的值，为了解决一些模糊查询效率较低的问题。
• 哈希索引
  基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，并且不同键值的行计算出的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。
• B-TREE索引
  是将索引使用B-TREE的形式建立起来。InnoDB引擎使用的是B+树，类似于二叉查找树。
  根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终叶子节点的指针指向的是被索引的数据。
  B+树索引所有的用户数据储存在叶子节点，要通过上层节点的目录项，从根节点层层查找，找到对应的数据。
• 覆盖索引
  一个索引包含或覆盖了所有需要查询的字段的值，不再需要根据索引回表查询数据。覆盖索引必须要存储索引列的值，因此 MySQL 只能使用 B-Tree 索引做覆盖索引。

种类

• 普通索引：使用KEY或INDEX关键字建立
• 唯一索引：使用UNIQUE，索引列的值必须唯一，组合索引的组合必须唯一，允许空值
• 主键索引：PRIMARY，特殊的唯一索引，不允许有空值，InnoDB会自动为主键建立聚簇索引
• 组合索引：基于多个字段创建的索引，查询时必须遵循最左前缀原则（脑子里详细过一下）。
• 全文索引：FULL TEXT，用来查找文本中关键字，为了解决一些模糊查询效率较低的问题

聚簇索引和非聚簇索引

• 聚簇索引：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据
• 非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置

为啥使用B+树

• 由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫。所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。
• 数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作或者说效率太低。

InnonDB的B+树索引查询如何实现？

• 首先，B+树每一层节点是一个页，页面之间由双向链表连接，页面中的记录项由单向链表连接，记录项根据索引列值从小到大排列

• 索引建立的过程

  • 一个表创建一个 B+ 树索引时，会创建一个根节点页面
    • 这个根节点是不会再移动的，InnoDB 用到该索引时会从数据字典中取出这个索引根节点页面号，进行访问。
  • 每当表中有用户记录插入，都会把用户记录储存到根节点
  • 当根节点可用空间用完的时候（InnoDB 每页大小 16KB），根节点的所有用户记录都复制到一个新分配的页面，通过页分裂，得到两个页，用户记录根据索引值从小到大分配到两个新页，成为叶子节点，根节点成为储存目录项记录的页。
  • 目录项记录中记录两个页中的最小索引值和页号，并将记录根据索引值从小到大排列。
  • 当叶子节点全部存满之后，再进行上一步的分裂操作，始终保证叶子节点储存用户数据，而上级页面成为目录项记录页。不断重复这个过程，形成一个多级目录。

• 根据主键索引查找

  • 获取到要查询的主键值之后，访问主键根节点所在页面
  • 由于根节点中目录项记录是根据主键索引值从小到大排列，我们可以用二分查找找到主键大概所在的目录项记录页
  • 再到下一层的这个目录项中继续二分查找，找到下层的目录项记录页
  • 直到找到叶子节点的目录项，在目录项中查找到具体的主键值

• 根据自己建立的二级索引查找

  • 由二级索引建立的B+树，叶子节点储存的是二级索引值+主键值
  • 根据索引值在由这个索引建立的B+树中找到所有匹配的二级索引值，再根据它们对应的主键值一一回表查询，得到最终要查询的完整结果。

B+树索引的适用条件

• 全值匹配
• 最左列匹配
• 列前缀匹配
• 范围值匹配
• orderBy排序，且desc,asc不混用
• groupBy

回表的代价

• 二级索引得到的结果过多，回表次数会过多，造成使用这个二级索引查询的性能低下。
  • 原因：访问二级索引使用的是顺序I/O，因为数据依据大小顺序是存放在连续页中，用链表连接的。取出二级索引查询结果后，去主键索引中查询，并不是按照主键大小依次查询的，所以是随机I/O，会访问较多数据页，造成性能低下。

索引的选择

• 只为用于搜索、分组、排序的列创建索引
• 列的基数尽量大（重复的值少，查出的结果就少，回表次数少）
• 索引列数据类型尽量小（MEDIUMINT，INT，BIGINT）
• 为字符串前缀建立索引（只取字符串前几位，节约B+树空间，节省查询比较的时间）

为什么主键索引是整型且自增的？

• 自增是由B+树索引结构决定的。
• 如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，也就是说只会影响B+树的右子树。当一页写满，就会自动开辟一个新的页
• 如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。
• 使用整型是为了节省索引的储存空间，在一个页中尽可能多地储存索引。

主键、外键和索引的区别

InnoDB 和 MyISAM 引擎的区别

两者的索引结构都是B+树！

连接

内连接: 只连接匹配的行 select a.name,b.job from A a inner join B b on a.id=b.A_id
左外连接: 包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行 left join ... on ...
右外连接: 包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行 right join ... on ...

数据库优化

查询语句优化

• Mysql 8.0 以前要排除缓存对查询速度的干扰。
• EXPLAIN对执行计划分析
  • 分析所选择的索引和估计的行数。
  • 比如发现explain中rows的值和count(*)得出的行数相差过大，从而导致索引选择错误，可以使用force index强制使用正确的索引。
  • 或者发现语句执行时没有使用索引，需要调整查询条件。
• 使用覆盖索引
  • 尽量少使用select *查询，而是通过覆盖索引查询可以直接在自身的索引上查到所需的数据，避免了回表，减少查询次数。覆盖索引：查询使用的联合索引覆盖到了所查询的字段。
• 建立合适的联合索引
  • 对于一些高频的查询，建立联合索引（比如：学号，姓名）。目的也是避免回表到主键索引，以减少查询的次数。
• 索引下推优化（官方进行的优化）：MySql 5.6 开始，在索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，再去主键索引中查询，减少回表次数。
• 查询时遵循最左匹配原则，按照索引的字段顺序去查询
• 使用前缀索引。比如：对邮箱的前几位建立索引，减少建立索引消耗的空间，让一个数据页尽可能多地放入索引值，提高搜索效率。
  • 对于很长的字段，如需建立前缀索引，那么关键是需要提高这个索引的区分度。使用hash，截取字段，反转字符串均可，但需要注意使用函数也是开销。要避免在查询条件中使用函数。
  • flush

数据库结构优化

• 选择合适的数据库引擎
• 范式优化：消除一个表中的冗余列
• 反范式优化：适当增加一个表中的冗余列，以减少join查询
• 数据库分区
  如果MySQL的优化器知道哪个分区中才包含特定查询中需要的数据，它就能直接去扫描那些分区的数据，而不用浪费很多时间扫描不需要的地方了。
  • 水平拆分和垂直拆分
    • 垂直拆分就是要把表按模块划分到不同数据库表中，单表大数据量依然存在性能瓶颈
    • 水平切分就是要把一个表按照某种规则把数据划分到不同表或数据库里。
    • 通俗理解：水平拆分行，行数据拆分到不同表中， 垂直拆分列，列数据拆分到不同表中
  • 范围分区、List分区

服务器硬件优化

• 升级硬件设备。。

EXPLAIN关键字详解

MySQL 性能优化神器 Explain 使用分析

MyBatis中 ${} 和 #{}

• 使用${}方式传入的参数，mybatis不会对它进行特殊处理，而使用#{}传进来的参数，mybatis默认会将其当成字符串。
• #和$在预编译处理中是不一样的。#类似jdbc中的PreparedStatement，对于传入的参数，在预处理阶段会使用占位符?代替，可以有效防止sql注入

SQL注入

Sql 注入攻击是通过将恶意的 Sql 查询或添加语句插入到应用的输入参数中，再在后台 Sql 服务器上解析执行进行的攻击。

• 解决：
  • sql预编译，变量绑定
    • 预编译语句就是将sql语句中的值用占位符替代，也就是sql语句模板化，使其结构固定。预编译完成后，下次遇到相同的预编译语句只需将变量值传入编译过的语句执行代码中即可执行。
    • 如果有预编译，sql注入语句如果符合该预编译语句，会将sql注入语句的变量进行传递，其中的or/and也都会视作变量，就失去了本来的作用。
    • 比如:
      • 预编译语句 select * from users where username = ? and password = ?
      • 注入语句 select * from users where username='' or true or'' and password=''在预编译中or true就会失去恒成立的效果。
  • 严格的参数校验：在执行sql语句前检查变量数据类型和格式是否符合要求。

数据库的锁

按类型

• 分为共享锁（S 锁）和排它锁（X 锁），也叫读锁和写锁。读锁是共享的，相互不阻塞，多个客户在同一时刻可以读取同一资源。写锁是排他的，会阻塞其他的写锁和读锁，确保在给定时间内只有一个用户能执行写入。
• 写锁比读锁有更高的优先级，一个写锁请求可能会被插入到读锁队列的前面，但读锁不能插入到写锁前面。

按范围

• 表锁会锁定整张表，开销小，不会出现死锁，但锁冲突概率高、并发度低。
• 行锁可以最大程度地支持并发，锁冲突概率低，但开销大，会出现死锁。行锁只在存储引擎层实现，InnoDB 实现了行锁。

数据库死锁
1. 事务之间对资源访问顺序的交替

• 出现原因：
  一个用户A 访问表A（锁住了表A），然后又访问表B；另一个用户B 访问表B（锁住了表B），然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁就产生了。
• 解决方法:
  这种死锁比较常见，是由于程序的BUG产生的，除了调整的程序的逻辑没有其它的办法。仔细分析程序的逻辑，对于数据库的多表操作时，尽量按照相同的顺序进行处理，尽量避免同时锁定两个资源，如操作A和B两张表时，总是按先A后B的顺序处理， 必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源

2. 并发修改同一记录

• 出现原因：主要是由于没有一次性申请够权限的锁导致的。
  参考：记录一次死锁排查过程
  用户A查询一条纪录，然后修改该条纪录；这时用户B修改该条纪录，这时用户A的事务里锁的性质由查询的共享锁企图上升到独占锁，而用户B里的独占锁由于A有共享锁存在所以必须等A释放掉共享锁，而A由于B的独占锁而无法上升的独占锁也就不可能释放共享锁，于是出现了死锁。这种死锁比较隐蔽，但在稍大点的项目中经常发生。
• 解决方法：
  a. 乐观锁：实现写-写并发
  b. 悲观锁：使用悲观锁进行控制。悲观锁大多数情况下依靠数据库的锁机制实现，如Oracle的Select … for update语句，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。

3. 索引不当导致的死锁

• 出现原因：
  如果在事务中执行了一条不满足条件的语句，执行全表扫描，把行级锁上升为表级锁，多个这样的事务执行后，就很容易产生死锁和阻塞。类似的情况还有当表中的数据量非常庞大而索引建的过少或不合适的时候，使得经常发生全表扫描，最终应用系统会越来越慢，最终发生阻塞或死锁。
• 解决方法：
  SQL语句中不要使用太复杂的关联多表的查询；使用“执行计划”对SQL语句进行分析，对于有全表扫描的SQL语句，建立相应的索引进行优化。

那么，如何尽可能的避免死锁呢？
1）以固定的顺序访问表和行。即按顺序申请锁，这样就不会造成互相等待的场面。
2）大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。
3）在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。
4）降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。
5）为表添加合理的索引。如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大。

drop、truncate、delete区别

简述：

• drop属于ddl（数据库定义语言），直接删除整张表。
• truncate也是ddl，删除表中数据，不删除表结构。重新插入数据后，自增id重新开始计数。
• delete属于dml（数据库操作语言），每次从表中删除一行数据，可以使用where语句进行筛选。

详细：

关系型数据库 VS 非关系型数据库

一方的优势就是另一方的劣势。
非关系型数据库的优势：

• 性能：NOSQL是基于键值对的，而且不需要经过SQL层的解析，所以读写性能非常高。
• 可扩展性：同样也是因为基于键值对，数据之间没有耦合性，所以非常容易水平扩展。
• 数据的储存格式多样。

关系型数据库的优势：

• 结构简单：二维表结构，逻辑清晰，易于理解。
• 复杂查询：使用通用SQL语句，复杂查询也可以方便进行。
• 事务支持：使得对于安全性能很高的数据访问要求得以实现。

varchar() 和 char()

• char的长度是不可变的，而varchar的长度是可变的，且占用额外1字节储存位置信息。
  所以取数据的时候，char类型的要用trim()去掉多余的空格，而varchar是不需要的。

• char的存取数度还是要比varchar要快，因为其长度固定，方便程序的存储与查找。
  因为其长度固定，所以char难免会有多余的空格占位符占据空间，可谓是以空间换取时间效率。

• char的存储方式是：对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节。
  varchar的存储方式是：对每个英文字符占用2个字节，汉字也占用2个字节。

• char 使用场景：数据是定长；数据长度较短；频繁改变的列（因为varchar每次储存需要额外计算长度）

参考

SQL注入详解
MySQL 储存过程-原理、语法、函数详细说明
数据库面试题(开发者必看)
有哪些常见的数据库优化方法？

本作品采用《CC 协议》，转载必须注明作者和本文链接