数据库 | Next

SQL

操作符的计算顺序

AND在计算次序中优先级比OR更高，因此下列命令会返回不符合我们预期的结果

1
2
3

SELECT id
FROM mytable
WHERE col1=1 OR col2=2 AND col3=3;

此时的计算顺序并不是从左至右，而是先计算col2=2 AND col3=3，如果想按由左至右的顺序计算，则应该按照如下写法

1
2
3

SELECT id
FROM mytable
WHERE (col1=1 OR col2=2) AND col3=3;

因为圆括号具有比AND与OR更高的计算顺序

通配符

在where子句中使用LIKE谓词使用搜索模式利用通配符匹配而不是直接相等进行比较

%（百分号）：表示任何字符出现任何次数（可以是0次）

_（下划线）：表示匹配任何一个字符

需要注意的是通配符搜索处理需要花费较多的时间，因此不要过度使用通配符。

通配符与正则表达式的区别

LIKE谓词是对整个列进行匹配（利用通配符%val%的形式可以实现对列值的匹配），REGEXP在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP就会找到它。同时正则如果使用^和￥定位符也可以做到匹配整个列值。

更详细的正则语法可以参考：正则表达式 – 教程 | 菜鸟教程 (runoob.com)

别名

在SELECT子句中，我们常会使用聚集函数或是Conct拼接来输出想要的结果。需要注意的是，使用Concat拼接后的列是没有名字的，它只是一个值。而一个未命名的列不能用于客户机的应用中，为了之后更好的引用SQL支持使用AS来对一个字段或值进行替换名。同时AS还可以在实际表列中出现不符合规定的字符（空格）或是原有名字存在混淆容易误解的情况下重新命名。

日期和时间处理函数

哎呀，图片不见了

HAVING和WHERE的区别

WHERE子句是在数据分组前进行约束，是约束声明，不能使用聚合函数。HAVING是在分组后进行过滤，不能使用聚合函数。也就是说HAVING是对已经经过WHERE过滤后的分组数据进行处理的。因此尽可能在where子句中对查询结果进行约束。

连接

自然连接

自然连接首先形成它的两个参数的笛卡尔积，然后基于两个关系模式中都出现的属性上的相等性进行选择，最后去除重复属性。

我们很有可能不会使用到不是自然连接的内部连接

内连接和自然连接的区别

内连接提供连接的列，而自然连接自动连接所有同名列

左外连接与右外连接

左外连接：取出左侧关系中所有与右侧关系的任一元组都不匹配的元组。用空值填充所有来自右侧关系的属性，再把产生的元组加入自然连接结果中。即最后得到的结果是左表中的所有行＋右表中对应的行（不匹配的行填充空值）

条件语句ON与USING的区别

USING：只需要在指定属性上进行取值匹配

ON：需要接一个predicate，是在参与连接的关系上设置通用的谓词

SQL注入

SQL注入的原理是将SQL代码伪装到输入参数中，传递到服务器解析并执行的一种攻击手法。也就是说，在一些对SERVER端发起的请求参数中植入一些SQL代码，SERVER端在执行SQL操作时，会拼接对应参数，同时也将一些SQL注入攻击的“SQL”拼接起来，导致会执行一些预期之外的操作。

解决方法

严格的参数校验：不符合参数类型要求的请求即认为是非法的
SQL预编译：在服务器启动时，MySQL Client把SQL语句的模板（变量采用占位符进行占位）发送给MySQL服务器，MySQL服务器对SQL语句的模板进行编译，编译之后根据语句的优化分析对相应的索引进行优化，在最终绑定参数时把相应的参数传送给MySQL服务器，直接进行执行，节省了SQL查询时间，以及MySQL服务器的资源，达到一次编译、多次执行的目的，除此之外，还可以防止SQL注入。

索引

优点

通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
可以大大加快数据的查询速度，这也是创建索引的主要原因。
在实现数据的参考完整性方面，可以加速表和表之间的连接。
在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间。（可以在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。）

缺点

创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

MySQL的Hash索引和B树索引的区别

hash索引进行等值查询更快(一般情况下)，但是却无法进行范围查询。因为在hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。而B+树的的所有节点皆遵循(左节点小于父节点，右节点大于父节点，多叉树也类似)，天然支持范围。
hash索引不支持使用索引进行排序，原理同上。
hash索引不支持模糊查询以及多列索引的最左前缀匹配，原理也是因为hash函数的不可预测。
hash索引任何时候都避免不了回表查询数据，而B+树在符合某些条件(聚簇索引，覆盖索引等)的时候可以只通过索引完成查询。
hash索引虽然在等值查询上较快，但是不稳定，性能不可预测，当某个键值存在大量重复的时候，发生hash碰撞，此时效率可能极差。而B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。

最左匹配原则

在联合索引中，如果 SQL 语句中用到了联合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个联合索引去进行匹配。

数据库的索引是使用B+树来实现的，联合索引也是一颗B+树。而B+树只能根据一个列值来构建，也就是联合索引的最左字段。一个形如(a,b,c)联合索引的 b+ 树，其中的非叶子节点存储的是第一个关键字的索引 a，而叶子节点存储的是三个关键字的数据。这里可以看出 a 是有序的，而 b，c 都是无序的。但是当在 a 相同的时候，b 是有序的，b 相同的时候，c 又是有序的。

这也就是大部分索引失效的原因所在，select * from t where a=5 and b>0 and c =1;为例，使用范围查询查找到b后，c就是无序的状态，因此无法使用联合索引确定c的位置

索引失效的场景

不满足最左匹配原则：在where子句中，最左谓词中含有索引项才会使用索引；当遇到范围查询(>、<、between、like)也会停止匹配
使用select *：使用*查询所有列的数据，大概率会非索引的数据，这样会使用全表扫描。而如果查询的都是索引列，被称为覆盖索引，会提升查询效率
索引列上有计算
索引列使用函数：3、4索引失效的原因相同。索引列如果进行计算或是函数计算，mysql在B+树上的索引搜索就会失效
字段类型不同：mysql会将字符串类型自动转换为int类型，而int无法转换为varchar
like左边包含%
列对比：如果把两个单独建了索引的列，用来做列对比时，索引会失效。
使用or关键字：如果使用了or关键字，那么它前面和后面的字段都要加索引，不然所有的索引都会失效
not in和not exists：主键字段中使用not in关键字查询数据范围，仍然可以走索引。而普通索引字段使用了not in关键字查询数据范围，索引会失效。如果sql语句中使用not exists时，索引也会失效
order by：order by后面的条件，也要遵循联合索引的最左匹配原则。同时还需要添加limit关键字,或是使用where子句；order by后面如果包含了联合索引的多个排序字段，只要它们的排序规律是相同的（要么同时升序，要么同时降序），也可以走索引。

事务

ACID特性

A（atomicity），原子性。原子性指整个数据库事务是不可分割的工作单位。只有使事务中所有的数据库操作都执行成功，整个事务的执行才算成功。事务中任何一个SQL语句执行失败，那么已经执行成功的SQL语句也必须撤销，数据库状态应该退回到执行事务前的状态。
C（consistency），一致性。一致性指事务将数据库从一种状态转变为另一种一致的状态。在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
I（isolation），隔离性。事务的隔离性要求每个读写事务的对象与其他事务的操作对象能相互分离，即该事务提交前对其他事务都不可见，这通常使用锁来实现。
D（durability），持久性。事务一旦提交，其结果就是永久性的，即使发生宕机等故障，数据库也能将数据恢复。持久性保证的是事务系统的高可靠性，而不是高可用性。

事务类型

扁平事务：是事务类型中最简单的一种，而在实际生产环境中，这可能是使用最为频繁的事务。在扁平事务中，所有操作都处于同一层次，其由BEGIN WORK开始，由COMMIT WORK或ROLLBACK WORK结束。处于之间的操作是原子的，要么都执行，要么都回滚。
带有保存点的扁平事务：除了支持扁平事务支持的操作外，允许在事务执行过程中回滚到同一事务中较早的一个状态，这是因为可能某些事务在执行过程中出现的错误并不会对所有的操作都无效，放弃整个事务不合乎要求，开销也太大。保存点（savepoint）用来通知系统应该记住事务当前的状态，以便以后发生错误时，事务能回到该状态。
链事务：可视为保存点模式的一个变种。链事务的思想是：在提交一个事务时，释放不需要的数据对象，将必要的处理上下文隐式地传给下一个要开始的事务。注意，提交事务操作和开始下一个事务操作将合并为一个原子操作。这意味着下一个事务将看到上一个事务的结果，就好像在一个事务中进行的。
嵌套事务：是一个层次结构框架。有一个顶层事务（top-level transaction）控制着各个层次的事务。顶层事务之下嵌套的事务被称为子事务（subtransaction），其控制每一个局部的变换。
分布式事务：通常是一个在分布式环境下运行的扁平事务，因此需要根据数据所在位置访问网络中的不同节点。对于分布式事务，同样需要满足ACID特性，要么都发生，要么都失效。

ACID特性的实现

实现原子性

原子性实现的关键，是当事务回滚时能够撤销所有已经成功执行的sql语句。InnoDB实现回滚靠的是undo log，当事务对数据库进行修改时，InnoDB会生成对应的undo log。如果事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

实现持久性

数据库使用redo log实现持久性。当数据修改时，除了修改Buffer Pool中的数据，还会在redo log记录这次操作。当事务提交时，会调用fsync接口对redo log进行刷盘。如果MySQL宕机，重启时可以读取redo log中的数据，对数据库进行恢复。redo log采用的是WAL（Write-ahead logging，预写式日志），所有修改先写入日志，再更新到Buffer Pool，保证了数据不会因MySQL宕机而丢失，从而满足了持久性要求。

Buffer Pool

当向数据库写入数据时，会首先写入Buffer Pool，Buffer Pool中修改的数据会定期刷新到磁盘中（这一过程称为刷脏）。

而redo log写入磁盘比直接将Buffer Pool中修改的数据写入磁盘(即刷脏)要快

刷脏是随机IO，因为每次修改的数据位置随机，但写redo log是追加操作，属于顺序IO。

刷脏是以数据页（Page）为单位的，MySQL默认页大小是16KB，一个Page上一个小修改都要整页写入。而redo log中只包含真正需要写入的部分，无效IO大大减少。

实现隔离性

锁机制保证写写操作的隔离性。事务在修改数据之前，需要先获得相应的锁。获得锁之后，事务便可以修改数据。该事务操作期间，这部分数据是锁定的，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁。
MVCC保证写读操作的隔离性。读不加锁，因此读写不冲突，并发性能好。

锁

InnoDB存储引擎支持多粒度锁定，按照粒度，锁可以分为表锁、行锁以及其他位于二者之间的锁。

表锁在操作数据时会锁定整张表，并发性能较差。

行锁则只锁定需要操作的数据，并发性能好。但是由于加锁本身需要消耗资源，因此在锁定数据较多情况下使用表锁可以节省大量资源。

MVCC（多版本的并发控制协议）

隐藏列：InnoDB中每行数据都有隐藏列，隐藏列中包含了本行数据的事务id、指向undo log的指针等。

基于undo log的版本链：每行数据的隐藏列中包含了指向undo log的指针，而每条undo log也会指向更早版本的undo log，从而形成一条版本链。

ReadView：通过隐藏列和版本链，MySQL可以将数据恢复到指定版本。但是具体要恢复到哪个版本，则需要根据ReadView来确定。所谓ReadView，是指事务（记做事务A）在某一时刻给整个事务系统（trx_sys）打快照，之后再进行读操作时，会将读取到的数据中的事务id与trx_sys快照比较，从而判断数据对该ReadView是否可见，即对事务A是否可见。

实现一致性

保证原子性、持久性和隔离性，如果这些特性无法保证，事务的一致性也无法保证。
数据库本身提供保障，例如不允许向整形列插入字符串值、字符串长度不能超过列的限制等。
应用层面进行保障，例如如果转账操作只扣除转账者的余额，而没有增加接收者的余额，无论数据库实现的多么完美，也无法保证状态的一致。

事务隔离级别

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED（读未提交）	可能	可能	可能
READ COMMITTED（读提交）	不可能	可能	可能
REPEATABLE READ（可重复读）	不可能	不可能	可能
SERIALIZABLE（串行化）	不可能	不可能	不可能

脏读：当前事务(A)中可以读到其他事务(B)未提交的数据（脏数据），这种现象是脏读。
不可重复读：在事务A中先后两次读取同一个数据，两次读取的结果不一样，这种现象称为不可重复读。脏读与不可重复读的区别在于：前者读到的是其他事务未提交的数据，后者读到的是其他事务已提交的数据。
幻读：在事务A中按照某个条件先后两次查询数据库，两次查询结果的条数不同，这种现象称为幻读。不可重复读与幻读的区别可以通俗的理解为：前者是数据变了，后者是数据的行数变了。

四种隔离级别的实现机制

READ UNCOMMITTED & READ COMMITTED：通过Record Lock算法实现了行锁，但READ UNCOMMITTED允许读取未提交数据，所以存在脏读问题。而READ COMMITTED允许读取提交数据，所以不存在脏读问题，但存在不可重复读问题。
REPEATABLE READ：使用Next-Key Lock算法实现了行锁，并且不允许读取已提交的数据，所以解决了不可重复读的问题。另外，该算法包含了间隙锁，会锁定一个范围，因此也解决了幻读的问题。
SERIALIZABLE：对每个SELECT语句后自动加上LOCK IN SHARE MODE，即为每个读取操作加一个共享锁。因此在这个事务隔离级别下，读占用了锁，对一致性的非锁定读不再予以支持。

锁

锁（行级锁）的类型

共享锁（S Lock），允许事务读一行数据。
排他锁（X Lock），允许事务删除或更新一行数据。

如果一个事务T1已经获得了行r的共享锁，那么另外的事务T2可以立即获得行r的共享锁，因为读取并没有改变行r的数据，称这种情况为锁兼容。但若有其他的事务T3想获得行r的排他锁，则其必须等待事务T1、T2释放行r上的共享锁，这种情况称为锁不兼容。
哎呀，图片不见了

InnoDB行级锁是通过给索引上的索引项加锁来实现的。只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。当表中锁定其中的某几行时，不同的事务可以使用不同的索引锁定不同的行。另外，不论使用主键索引、唯一索引还是普通索引，InnoDB都会使用行锁来对数据加锁。

意向锁（表级锁）

意向共享锁（IS Lock），事务想要获得一张表中某几行的共享锁。
意向排他锁（IX Lock），事务想要获得一张表中某几行的排他锁。

由于InnoDB存储引擎支持的是行级别的锁，因此意向锁其实不会阻塞除全表扫以外的任何请求。
哎呀，图片不见了

锁的算法

Record Lock：单个行记录上的锁。
Gap Lock：间隙锁，锁定一个范围，但不包含记录本身。它的作用是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生。
Next-Key Lock∶Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身。

优化

优化查询

可以通过使用索引、使用连接代替子查询的方式来提高查询速度。

需要注意索引失效的情况
优化子查询：子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。但执行效率不高。执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表。在MySQL中，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引，性能会更好。

优化插入

MyISAM引擎

禁用索引：对于非空表，插入记录时，MySQL会根据表的索引对插入的记录建立索引。如果插入大量数据，建立索引会降低插入记录的速度。为了解决这种情况，可以在插入记录之前禁用索引，数据插入完毕后再开启索引。对于空表批量导入数据，则不需要进行此操作，因为MyISAM引擎的表是在导入数据之后才建立索引的。
禁用唯一性检查：插入数据时，MySQL会对插入的记录进行唯一性校验。这种唯一性校验也会降低插入记录的速度。为了降低这种情况对查询速度的影响，可以在插入记录之前禁用唯一性检查，等到记录插入完毕后再开启。
使用批量插入：插入多条记录时，可以使用一条INSERT语句插入多条记录会比使用一条INSERT语句插入一条记录的插入速度更快
使用LOAD DATA INFILE批量导入：当需要批量导入数据时，LOAD DATA INFILE语句导入速度比INSERT语句快

InnoDB引擎

禁用外键检查：插入数据之前执行禁止对外键的检查，数据插入完成之后再恢复对外键的检查。
禁用唯一性检查
禁用自动提交：插入数据之前禁止事务的自动提交，数据导入完成之后，执行恢复自动提交操作。

范式

第一范式(1NF)

在关系模型中，对于添加的一个规范要求，所有的域都应该是原子性的.如果实体中的某个属性有多个值，必须拆分为不同的属性。

第二范式(2NF)

在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）。第二范式要求数据库表中的每个实例或记录必须可以被唯一地区分。选取一个能区分每个实体的属性或属性组，作为实体的唯一标识。

第三范式(3NF)

在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）。第三范式要求一个关系中不包含已在其它关系已包含的非主关键字信息。

解决方法：将非主属性与其依赖的码拿出来单独成表，并设置被依赖的属性为主键，在原表中用外键表示

第三范式可以减少数据库数据的冗余
如：订单表(订单号, 订购日期, 顾客编号, 顾客名)并不符合第三范式，（订单号、顾客编号、顾客名形成了依赖传递）
可以修改为：
订单表(订单号，订购日期，顾客编号)
顾客表(顾客编号，顾客名)

BC范式

消除主属性对于码的部分函数依赖与传递函数依赖，即每个表中只有一个候选键。

解决方法：将多余的候选码提取出来单独成表，将原表中的主键放入该表