数据库SQL

　　数据库有关的知识，学习后端必备。

MySQL

MySQL 使用 InnoDB 存储表时，会将表的定义和数据索引等信息分开存储，其中前者存储在 .frm 文件中，后者存储在 .ibd 文件中，这一节就会对这两种不同的文件分别进行介绍。

https://mp.weixin.qq.com/s/JQCtqM6aep3jtgiRL_9J5g

.frm 文件

无论在 MySQL 中选择了哪个存储引擎，所有的 MySQL 表都会在硬盘上创建一个 .frm 文件用来描述表的格式或者说定义；.frm 文件的格式在不同的平台上都是相同的。

索引

索引是数据库中非常非常重要的概念，它是存储引擎能够快速定位记录的秘密武器，对于提升数据库的性能、减轻数据库服务器的负担有着非常重要的作用；索引优化是对查询性能优化的最有效手段，它能够轻松地将查询的性能提高几个数量级。

索引的数据结构

InnoDB 存储引擎在绝大多数情况下使用 B+ 树建立索引，这是关系型数据库中查找最为常用和有效的索引，但是 B+ 树索引并不能找到一个给定键对应的具体值，它只能找到数据行对应的页，然后正如上一节所提到的，数据库把整个页读入到内存中，并在内存中查找具体的数据行。

聚集索引

数据库中的 B+ 树索引可以分为聚集索引（clustered index）和辅助索引（secondary index），它们之间的最大区别就是，聚集索引中存放着一条行记录的全部信息，而辅助索引中只包含索引列和一个用于查找对应行记录的『书签』

InnoDB 存储引擎中的表都是使用索引组织的，也就是按照键的顺序存放；聚集索引就是按照表中主键的顺序构建一颗 B+ 树，并在叶节点中存放表中的行记录数据。

MySQL中索引的类型：

• 全局索引(FULLTEXT)：全局索引，目前只有 MyISAM 引擎支持全局索引，它的出现是为了解决针对文本的模糊查询效率较低的问题。

• 哈希索引(HASH)：哈希索引是 MySQL 中用到的唯一 key-value 键值对的数据结构，很适合作为索引。HASH 索引具有一次定位的好处，不需要像树那样逐个节点查找，但是这种查找适合应用于查找单个键的情况，对于范围查找，HASH 索引的性能就会很低。

• B-Tree 索引：B 就是 Balance 的意思，BTree 是一种平衡树，它有很多变种，最常见的就是 B+ Tree，它被 MySQL 广泛使用。

• R-Tree 索引：R-Tree 在 MySQL 很少使用，仅支持 geometry 数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种，相对于 B-Tree 来说，R-Tree 的优势在于范围查找。

辅助索引

数据库将所有的非聚集索引都划分为辅助索引，但是这个概念对我们理解辅助索引并没有什么帮助；辅助索引也是通过 B+ 树实现的，但是它的叶节点并不包含行记录的全部数据，仅包含索引中的所有键和一个用于查找对应行记录的『书签』，在 InnoDB 中这个书签就是当前记录的主键。

辅助索引的存在并不会影响聚集索引，因为聚集索引构成的 B+ 树是数据实际存储的形式，而辅助索引只用于加速数据的查找，所以一张表上往往有多个辅助索引以此来提升数据库的性能。

索引注意事项

1.不要在列上使用函数和进行运算

不要在列上使用函数和进行运算，这将导致索引失效而进行全表扫描。

2.尽量避免使用 != 或 not in或 <> 等否定操作符

应该尽量避免在 where 子句中使用 != 或 not in 或 <> 操作符，因为这几个操作符都会导致索引失效而进行全表扫描。

2.5使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

3.尽量避免使用 or 来连接条件

应该尽量避免在 where 子句中使用 or 来连接条件，因为这会导致索引失效而进行全表扫描。

4.多个单列索引并不是最佳选择

MySQL 只能使用一个索引，会从多个索引中选择一个限制最为严格的索引，因此，为多个列创建单列索引，并不能提高 MySQL 的查询性能。

事实上，MySQL 只能使用一个单列索引。为了提高性能，可以使用复合索引 news_year_month_idx(news_year, news_month) 保证 news_year 和 news_month 两个列都被索引覆盖。

5.复合索引的最左原则

复合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用。因此，在复合索引中索引列的顺序至关重要。如果不是按照索引的最左列开始查找，则无法使用索引

6.覆盖索引

如果一个索引包含所有需要的查询的字段的值，直接根据索引的查询结果返回数据，而无需读表，能够极大的提高性能。因此，可以定义一个让索引包含的额外的列，即使这个列对于索引而言是无用的。

7.范围查询对多列查询的影响

查询中的某个列有范围查询，则其右边所有列都无法使用索引优化查找。

对于范围查询，务必要注意它带来的副作用，并且尽量少用范围查询，可以通过曲线救国的方式满足业务场景。

8.索引不会包含有NULL值的列

只要列中包含有 NULL 值都将不会被包含在索引中，复合索引中只要有一列含有 NULL值，那么这一列对于此复合索引就是无效的。

因此，在数据库设计时，除非有一个很特别的原因使用 NULL 值，不然尽量不要让字段的默认值为 NULL。

9.隐式转换的影响

当查询条件左右两侧类型不匹配的时候会发生隐式转换，隐式转换带来的影响就是可能导致索引失效而进行全表扫描。

10.like 语句的索引失效问题

like 的方式进行查询，在 like “value%” 可以使用索引，但是对于 like “%value%” 这样的方式，执行全表查询，这在数据量小的表，不存在性能问题，但是对于海量数据，全表扫描是非常可怕的事情。所以，根据业务需求，考虑使用 ElasticSearch 或 Solr 是个不错的方案。

数据库中的锁

锁的种类一般分为乐观锁和悲观锁两种，InnoDB 存储引擎中使用的就是悲观锁，而按照锁的粒度划分，也可以分成行锁和表锁。

乐观锁和悲观锁其实都是并发控制的机制，同时它们在原理上就有着本质的差别；

虽然乐观锁和悲观锁在本质上并不是同一种东西，一个是一种思想，另一个是一种真正的锁，但是它们都是一种并发控制机制。

对数据的操作其实只有两种，也就是读和写，而数据库在实现锁时，也会对这两种操作使用不同的锁；

InnoDB 实现了标准的行级锁，也就是共享锁（Shared Lock）和互斥锁（Exclusive Lock）；共享锁和互斥锁的作用其实非常好理解：

• 共享锁（读锁）：允许事务对一条行数据进行读取；
• 互斥锁（写锁）：允许事务对一条行数据进行删除或更新；

共享锁之间是兼容的，而互斥锁与其他任意锁都不兼容：

无论是共享锁还是互斥锁其实都只是对某一个数据行进行加锁，InnoDB 支持多种粒度的锁，也就是行锁和表锁；为了支持多粒度锁定，InnoDB 存储引擎引入了意向锁（Intention Lock）

意向锁就是一种表级锁，目的是为了使行锁和表锁共存。

• 意向共享锁：事务想要在获得表中某些记录的共享锁，需要在表上先加意向共享锁；
• 意向互斥锁：事务想要在获得表中某些记录的互斥锁，需要在表上先加意向互斥锁；

死锁

死锁是指两个或两个以上的进程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象。

若无外力作用，它们都将无法推进下去，此时称系统处于死锁状态或者系统产生了死锁，这些永远在相互等待的进程称为死锁进程

InnoDB引擎采用wait-for-graph 等待图的方法来自动检测死锁，如果发现死锁会自动回滚一个事务

行锁的算法

InnoDB存储引擎使用三种行锁来满足事务隔离级别的要求：

Record Locks：该锁为索引记录上的锁。

Gap Locks：该锁会锁定一个范围，但不包括记录本身。

Next key Locks：该锁是前两种锁的结合，即锁定一个记录并锁定记录本身。如果索引有唯一属性，InnoDB会自动将Next-key Locks降级为Record Locks。

锁如果利用不好，会给业务造成大量的卡顿现象。设计锁的原则：

1.合理设计索引，尽可能的缩小锁定范围，避免其他query的执行。

2.尽可能减少基于范围的数据检索过滤条件

3.尽量控制事务的大小，减少锁定的资源量和锁定时间长度

4.在业务环境允许的环境下，尽量使用较低级别的事务隔离，以减少MySQL因为实现事务隔离级别所带来的附加成本。

事务

事务是一组操作，组成这组操作的各个单元，要不全都成功要不全都失败，这个特性就是事务。

在 MySQL 中，事务是在引擎层实现的，只有使用 innodb 引擎的数据库或表才支持事务。

事务的隔离级别

• RAED UNCOMMITED（读未提交）：使用查询语句不会加锁，可能会读到其他未提交事务的数据（Dirty Read，脏读）；大部分业务场景都不允许脏读出现，但是此隔离级别下数据库的并发是最好的。
• READ COMMITED（读提交）：该隔离级别是Oracle和SQL Server的默认隔离级别。只对记录加记录锁，而不会在记录之间加间隙锁，所以允许新的记录插入到被锁定记录的附近，所以再多次使用查询语句时，可能得到不同的结果（Non-Repeatable Read）；此现象称为不可重复读。
• REPEATABLE READ（可重复读）：该隔离级别是MySQL的默认隔离级别。多次读取同一范围的数据会返回第一次查询的快照，不会返回不同的数据行，但是可能发生幻读（Phantom Read）；MySQL的InnonDB引擎可以提高next-key locks机制来避免幻读。
• SERIALIZABLE（序列化）：在该隔离级别下事务都是串行顺序执行的，InnoDB 隐式地将全部的查询语句加上读共享锁，避免了脏读、不可重复读和幻读的问题；

事务还遵循包括原子性在内的 ACID 四大特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）；

事务的原子性，在数据提交工作时，要么保证所有的修改都能够提交，要么就所有的修改全部回滚。

一致性：事务开始前和结束后，数据库的完整性约束没有遭到破坏。

隔离性：每个读写事务的对象对其他事务的操作对象能够相互分离，即该事务提交前对其他事务不可见。

持久性：事务一旦提交，其结果就是永久性的，即使发生宕机的故障，数据库也能将数据恢复。

MyiSAM不支持事务：MyiSAM引擎强调性能，所以查询快，但是不支持事务和外键，轻装上阵。

MySQL的日志模块

MySQL的日志模块是redo log和bin log，也就是重做日志和归档日志

redo log

在MySQL中，如果每次更新操作都要先查询再更新，这样它耗费的资源是相当高的。会让io成本和查询成本都很改，而且效率非常感人，所以这时候他不是先查找再更新的，而是每次先把更新的语句写到日志里，等空闲的时候再去查询，然后更改

这里所用的技术就是WAL(white-ahead log)技术，它就是先写日志，再写磁盘，当你执行一条更新语句时，InnoDB首先会将这条语句记录在redo log里，等系统闲置时再去将整条操作更新到磁盘。

如果某次更新操作特别多的情况？InnoDB的redo log是固定大小的，但是它可以配置几组文件，然后从头开始写，写到末尾再从头开始写，就相当于是一条链表，然后设立指针联动。最先开始动的指针进行写操作，后面的指针进行擦除操作，也就是将这条操作更新到磁盘里面。当快指针把最后一个节点写完时，再回到头节点，即重新开始。

这样，哪怕InnoDB引擎不小心重启，也不会丢失数据，这种操作是crash-safe。

bin log

redo log是存储引擎层的，bin log是属于service层的日志。InnoDB有redo log和bin log两种，MyISAM只有bin log。redo log是一种物理操作，bin log是一种逻辑操作，记录的是这个语句的原始逻辑。redo log是循环写的，大小是固定的，写完了擦了重写，bin log可以理解为一个本子，写完一页之后翻页继续写。

MySQL底层机制

Service层：

包括连接器、查询缓存、分析器、优化器、执行器。它涵盖了MySQL的大多数核心服务功能和所有的内置函数，所有跨存储引擎的功能都在这里实现

连接器

负责跟客户端建立连接、获取权限、维持或者管理连接。连接器使用tcp连接。认证完成后连接器会在权限表里查询你的权限。如果管理员修改了权限，下次连接时生效。

MySQL的连接分为长连接和短连接。长连接是指客户端有请求就一直用一个连接，短连接是指执行很少的几次查询之后自动断开，下次查询时重新创建一个。

建立连接的过程复杂且消耗资源，所以尽量使用长连接。但是长连接时MySQL的内存涨的很快，因为MySQL在执行过程中临时使用的内存是在连接对象里，只有连接断开时才能被释放。这些长连接长期的积累可能会导致内存占用过大，进而导致异常重启。

查询缓存

当MySQL拿到一个查询请求时，首先不会在磁盘而会在缓存中查找是否执行过该语句。执行过的语句会以key-value的形式缓存到内存中，如果在内存中能查到这个key，就会直接返回缓存中的结果，这样可以大大提高效率。

这样做的缺点是，如果缓存失效，即有一个表更新之后，所有的缓存就会失效然后被清除，这对一个频繁更新的数据库来说极为不便

分析器

如果缓存中没有，就会执行语句。执行时，先对语句进行词法分析和语法分析

词法分析就是MySQL把SQL语句的每一个字符都识别出来

语法分析就是当词法分析通过之后再根据语法规则判断语句是否合法，不合法就报错

优化器

通过分析器之后就到了优化阶段，在执行前要进行优化处理，表中有多个引擎时，决定使用哪个索引或者在一张语句关联多张表时，各个表的连接顺序。有时候即使结果相同，但是效率不同，优化器就是尽量选择效率比较好的方式。

执行器

优化完由执行器执行。执行前会先检查有没有权限，没有权限会报错，有权限会继续执行。执行器会根据引擎的定义使用引擎的接口

存储引擎层：

负责数据的存储与提取，有InnoDB和MyISAM等

varchar与char的区别

char ：表示的是定长的字符串，当你输入小于指定的数目，比如你指定的数目是 char(6)，当你输入小于 6 个字符的时候，char 会在你最后一个字符后面补空值。当你输入超过指定允许最大长度后，MySQL 会报错

varchar：varchar 指的是长度为 n 个字节的可变长度，并且是非Unicode的字符数据。n 值是介于 1 - 8000 之间的数值。存储大小为实际大小。

什么是外连接 内连接

外连接(OUTER JOIN)：外连接分为三种，分别是左外连接(LEFT OUTER JOIN 或 LEFT JOIN)、右外连接(RIGHT OUTER JOIN 或 RIGHT JOIN)、全外连接(FULL OUTER JOIN 或 FULL JOIN)

左外连接：又称为左连接，这种连接方式会显示左表不符合条件的数据行，右边不符合条件的数据行直接显示 NULL

右外连接：也被称为右连接，他与左连接相对，这种连接方式会显示右表不 符合条件的数据行，左表不符合条件的数据行直接显示 NULL

内连接(INNER JOIN)：结合两个表中相同的字段，返回关联字段相符的记录。

什么是临时表？何时删除临时表？

MySQL 在执行 SQL 语句的过程中，通常会临时创建一些存储中间结果集的表，临时表只对当前连接可见，在连接关闭时，临时表会被删除并释放所有表空间。

临时表分为两种：一种是内存临时表，一种是磁盘临时表，什么区别呢？内存临时表使用的是 MEMORY 存储引擎，而临时表采用的是 MyISAM 存储引擎。

为什么mysql不推荐使用uuid作为主键

mysql设计表时，官方不推荐用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续递增的主键id，官方推荐的是auto_increment，那么为什么不建议使用uuid？

使用自增主键的值是顺序的，所以Innodb会把每一条记录都存储在一条记录的后面，当达到页面的最大填充因子时候(innodb默认的最大填充因子是页大小的15/16，会留出1/16的空间留作以后的修改)：

(1)下一条记录就会写入新的页中，一旦数据按照这种顺序的方式加载，主键页就会近乎于顺序的记录填满，提升了页面的最大填充率，不会有页的浪费

(2)新插入的行一定会在原有的最大数据行下一行，mysql定位和寻址很快，不会为计算新行的位置而做出额外的消耗

(3)减少了页分裂和碎片的产生

使用uuid，uuid相对顺序的自增id来说是毫无规律可言的，新行的值不一定要比之前的主键的值要大，所以innodb无法做到总是把新行插入到索引的最后，而是需要为新行寻找新的合适的位置从而来分配新的空间。

这个过程需要做很多额外的操作，数据的毫无顺序会导致数据分布散乱，将会导致以下的问题：

(1)写入的目标页很可能已经刷新到磁盘上并且从缓存上移除，或者还没有被加载到缓存中，innodb在插入之前不得不先找到并从磁盘读取目标页到内存中，这将导致大量的随机IO

(2)因为写入是乱序的，innodb不得不频繁的做页分裂操作，以便为新的行分配空间，页分裂导致移动大量的数据，一次插入最少需要修改三个页以上

(3)由于频繁的页分裂，页会变得稀疏并被不规则的填充，最终会导致数据会有碎片

在把随机值（uuid和雪花id）载入到聚簇索引(innodb默认的索引类型)以后，有时候会需要做一次OPTIMEIZE TABLE来重建表并优化页的填充，这将又需要一定的时间消耗。

但是，使用自增id也有缺点：

(1)别人一旦爬取你的数据库，就可以根据数据库的自增id获取到你的业务增长信息，很容易分析出你的经营情况

(2)对于高并发的负载，innodb在按主键进行插入的时候会造成明显的锁争用，主键的上界会成为争抢的热点，因为所有的插入都发生在这里，并发插入会导致间隙锁竞争

(3)Auto_Increment锁机制会造成自增锁的抢夺，有一定的性能损失

https://jianshu.com/p/b3f9007be020

SQL优化的经验

• 查询语句无论是使用哪种判断条件 等于、小于、大于， WHERE 左侧的条件查询字段不要使用函数或者表达式

• 使用 EXPLAIN 命令优化你的 SELECT 查询，对于复杂、效率低的 sql 语句，我们通常是使用 explain sql 来分析这条 sql 语句，这样方便我们分析，进行优化。

• 当你的 SELECT 查询语句只需要使用一条记录时，要使用 LIMIT 1

• 不要直接使用 SELECT *，而应该使用具体需要查询的表字段，因为使用 EXPLAIN 进行分析时，SELECT * 使用的是全表扫描，也就是 type = all。

• 为每一张表设置一个 ID 属性

• 避免在 WHERE 字句中对字段进行 NULL 判断

• 避免在 WHERE 中使用 != 或 <> 操作符

• 使用 BETWEEN AND 替代 IN

• 为搜索字段创建索引

• 选择正确的存储引擎，InnoDB 、MyISAM 、MEMORY 等

• 使用 LIKE %abc% 不会走索引，而使用 LIKE abc% 会走索引

• 对于枚举类型的字段(即有固定罗列值的字段)，建议使用ENUM而不是VARCHAR，如性别、星期、类型、类别等

• 拆分大的 DELETE 或 INSERT 语句

• 选择合适的字段类型，选择标准是 尽可能小、尽可能定长、尽可能使用整数。

• 字段设计尽可能使用 NOT NULL

• 进行水平切割或者垂直分割

为什么Mysql使用B+树

MySQL 跟 B+ 树没有直接的关系，真正与 B+ 树有关系的是 MySQL 的默认存储引擎 InnoDB，MySQL 中存储引擎的主要作用是负责数据的存储和提取，除了 InnoDB 之外，MySQL 中也支持 MyISAM 作为表的底层存储引擎。

我们在使用 SQL 语句创建表时就可以为当前表指定使用的存储引擎，你能在 MySQL 的文档 Alternative Storage Engines 中找到它支持的全部存储引擎，例如：MyISAM、CSV、MEMORY 等，然而默认情况下，使用如下所示的 SQL 语句来创建表就会得到 InnoDB 存储引擎支撑的表

MySQL 作为 OLTP 的数据库不仅需要具备事务的处理能力，而且要保证数据的持久化并且能够有一定的实时数据查询能力

读写性能

我们通常会使用以下MySql命令

1
2
3
4
5
6

通过 INSERT、UPDATE 和 DELETE 语句对表中的数据进行增加、修改和删除；
通过 UPDATE 和 DELETE 语句对符合条件的数据进行批量的删除；
通过 SELECT 语句和主键查询某条记录的全部列；
通过 SELECT 语句在表中查询符合某些条件的记录并根据某些字段排序；
通过 SELECT 语句查询表中数据的行数；
通过唯一索引保证表中某个字段或者某几个字段的唯一性；

数据加载

计算机在读写文件时会以页为单位将数据加载到内存中。页的大小可能会根据操作系统的不同而发生变化，不过在大多数的操作系统中，页的大小都是 4KB

当我们需要在数据库中查询数据时，CPU 会发现当前数据位于磁盘而不是内存中，这时就会触发 I/O 操作将数据加载到内存中进行访问，数据的加载都是以页的维度进行加载的，然而将数据从磁盘读取到内存中所需要的成本是非常大的，普通磁盘（非 SSD）加载数据需要经过队列、寻道、旋转以及传输的这些过程，大概要花费 10ms 左右的时间。

由于所有的节点都可能包含目标数据，我们总是要从根节点向下遍历子树查找满足条件的数据行，这个特点带来了大量的随机 I/O，也是 B 树最大的性能问题。

B+ 树中就不存在这个问题了，因为所有的数据行都存储在叶节点中，而这些叶节点可以通过『指针』依次按顺序连接，当我们在如下所示的 B+ 树遍历数据时可以直接在多个子节点之间进行跳转，这样能够节省大量的磁盘 I/O 时间，也不需要在不同层级的节点之间对数据进行拼接和排序；通过一个 B+ 树最左侧的叶子节点，我们可以像链表一样遍历整个树中的全部数据，我们也可以引入双向链表保证倒序遍历时的性能。

有些读者可能会认为使用 B+ 树这种数据结构会增加树的高度从而增加整体的耗时，然而高度为 3 的 B+ 树就能够存储千万级别的数据，实践中 B+ 树的高度最多也就 4 或者 5，所以这并不是影响性能的根本问题。

B+ 树可能不是 InnoDB 的最优选择，但是它一定是能够满足当时设计场景的需要，从 B+ 树作为数据库底层的存储结构到今天已经过了几十年的时间，我们不得不说优秀的工程设计确实有足够的生命力。而我们作为工程师，在选择数据库时也应该非常清楚地知道不同数据库适合的场景，因为软件工程中没有银弹。

MySQL与PostgreSQL的区别

Oracel

PostGreSQL

Ubuntu

1
2
3
4
5

apt-get install postgresql-client
apt-get install postgresql
## 图形化界面安装和启动
apt-get install pgadmin4
pgadmin4

启动

1

/etc/init.d/postgresql start

切换到数据库自动创建的用户

1
2
3

su - postgres
## 进入数据库，要求输入密码
psql

基本操作

1
2
3
4
5
6
7
8

## 创建用户
create user test with password 'test';
## 创建数据库
create database testdb owner test;
## 授权
grant all privileges on database testdb to test
## 退出
\q

关系型数据库与非关系型数据库的区别

关系型数据库以表格的形式存在，以行和列的形式存取数据，关系型数据库这一系列的行和列被称为表，无数张表组成了数据库，

关系型数据库能够支持复杂的 SQL 查询，能够体现出数据之间、表之间的关联关系；关系型数据库也支持事务，便于提交或者回滚。

常见的关系型数据库有 Oracle、DB2、Microsoft SQL Server、MySQL等。

非关系型数据库（感觉翻译不是很准确）称为 NoSQL，也就是 Not Only SQL，不仅仅是 SQL。

非关系型数据库不需要写一些复杂的 SQL 语句，其内部存储方式是以 key-value 的形式存在可以把它想象成电话本的形式，每个人名（key）对应电话（value）。

非关系型数据库不需要经过 SQL 的重重解析，所以性能很高；非关系型数据库的可扩展性比较强，数据之间没有耦合性，遇见需要新加字段的需求，就直接增加一个 key-value 键值对即可。

常见的非关系型数据库主要有 Hbase、Redis、MongoDB 等。