数据库SQL | Kun's Wiki

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

# 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引擎强调性能，所以查询快，但是不支持事务和外键，轻装上阵。

# 存储

存储过程是一些预编译 (opens new window)的SQL语句。（类似于函数）

存储过程可以说是一个记录集，它是由一些T-SQL语句 (opens new window)组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。

优点：

1）存储过程是预编译过的，执行效率高。

2）存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。

3）安全性高，执行存储过程需要有一定权限的用户。

4）存储过程可以重复使用，可减少数据库开发人员的工作量。

缺点：

1：调试麻烦，但是用 PL/SQL Developer 调试很方便！弥补这个缺点。

2：移植性差，数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。

3：重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。

4：如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难、而且代价是空前的。维护起来更加麻烦！

# 存储过程与函数的区别

# 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 (opens new window) 中找到它支持的全部存储引擎，例如：MyISAM、CSV、MEMORY 等，然而默认情况下，使用如下所示的 SQL 语句来创建表就会得到 InnoDB 存储引擎支撑的表

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

读写性能

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

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

1
2
3
4
5
6

数据加载

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

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

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

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

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

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

# Limit偏移量大效率慢的问题

在MySQL中，LIMIT OFFSET偏移量特别大时，效率会非常低

如果说LIMIT 1000,10，一个偏移量很小的值，一般是没有问题的。但是，比如说，LIMIT 10000000,10时就有些费劲了，让你等到花儿都谢了是没有问题的

原因是是因为MySQL的查询并非先跳过10000000条，再查询10条，而是先查询再跳过。所以上面例子，要先查询出10000000行之后，再取10条，速度当然很慢，并且跳过的行数越多，会越慢。

解决方案：

1.使用索引

可以使用 id > 10000000的方式诱导MySQL使用主键索引。

这样的Where语句MySQL是非常喜欢并且乐于执行的，因为你给到MySQL的是一个范围，它最容易执行的就是有序和范围的查询，这对它来说易如反掌。

当然，使用这个方法需要注意，id最好是连续的，中间的记录没有被物理删除过。如果其中有数据被物理删除过，用在分页场景的话，就看起来不是那么的优雅。当然，大部分数据库的参与者都倾向于逻辑删除。即便是物理删除的话，也可以使用程序去做区分显示，这不是问题。

2.延迟关联技巧

这个方法在《高性能MySQL》中也有提到，所谓延迟关联（deferred join），就是通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据。原理还是想方设法利用索引的速度，方可以柔克刚。

假设，有一张表保存了学生的名字，然后要执行这样的sql：

select id,name from student limit 1000000,10;

这样执行的效率必然会很慢

将SQL改写：

SELECT id,name FROM student INNER JOIN (SELECT id FROM student LIMIT 1000000,10) stu ON student.id = stu.id;

可以看到，在上述语句的子查询中，“SELECT id FROM student LIMIT 1000000,10”只查询了主键id一个字段，对于这样的索引覆盖情况，查询速度还是可以接受的。就等于说，该子查询只返回了10个id给上级查询。

上级查询接收到这10个id之后，迅速的查询出了“name”字段，速度得到了极大提升

# 主从同步

随着用户和数据的增多，单机的数据库往往支撑不住快速发展的业务，所以数据库集群就产生了

读写分离顾名思义就是读和写分离，对应到数据库集群一般都是一主一从（一个主库，一个从库）或者一主多从（一个主库，多个从库），业务服务器把需要写的操作都写到主数据库中，读的操作都去从库查询。主库会同步数据到从库保证数据的一致性。

这种集群方式的**本质是把访问的压力从主库转移到从库****，**也就是在单机数据库无法支撑并发读写，并且读的请求很多的情况下适合这种读写分离的数据库集群。如果写的操作很多的话不适合这种集群方式，因为你的数据库压力还是在写操作上，即使主从了之后压力还是在主库上，这样和单机的区别就不大了。

在单机的情况下，一般我们做数据库优化都会加索引，但是加了索引对查询有优化，但会影响写入，因为写入数据会更新索引。所以做了主从之后，我们可以单独地针对从库（读库）做索引上的优化，而主库（写库）可以减少索引而提高写的效率。

初始状态时，master 和 slave 的数据要保持一致。

master 提交完事务后，写入 binlog。
slave 连接到 master，获取 binlog。
master创建 dump 线程，推送 binlog 到 slave。
slave 启动一个 I/O 线程读取同步过来的 master 的 binlog，记录到 relay log（中继日志）中。
slave 再开启一个 SQL 线程从 relay log 中读取内容并在 slave 执行（从 Exec_Master_Log_Pos 位置开始执行读取到的更新事件），完成同步。
slave 记录自己的 binlog。

由于 MySQL 默认的复制方式是异步的，主库把日志发送给从库后不关心从库是否已经处理。这样会产生一个问题，假设主库挂了，从库处理失败了，这时从库升为主库后，日志就丢失了。由此产生以下两个概念

全同步复制

主库写入 binlog 后，强制同步日志到从库，等所有的从库都执行完成后，才返回结果给客户端，显然这个方式的性能会受到严重影响。

半同步复制

从库写入日志成功后返回 ACK（确认）给主库，主库收到至少一个从库的确认就可以认为写操作完成，返回结果给客户端。

主从同步延迟

主库有数据写入之后，同时也写入在 binlog（二进制日志文件）中，从库是通过 binlog 文件来同步数据的，这期间会有一定时间的延迟，可能是 1 秒，如果同时有大量数据写入的话，时间可能更长。

这会导致什么问题呢？比如有一个付款操作，你付款了，主库是已经写入数据，但是查询是到从库查，从库里还没有你的付款记录，所以页面上查询的时候你还没付款。那可不急眼了啊，吞钱了这还了得！打电话给客服投诉！

所以为了解决主从同步延迟的问题有以下几个方法：

1）二次读取

二次读取的意思就是读从库没读到之后再去主库读一下，只要通过对数据库访问的 API 进行封装就能实现这个功能。很简单，并且和业务之间没有耦合。但是有个问题，如果有很多二次读取相当于压力还是回到了主库身上，等于读写分离白分了。而且如有人恶意攻击，就一直访问没有的数据，那主库就可能爆了。

2）写之后的马上的读操作访问主库

也就是写操作之后，立马的读操作指定为访问主库，之后的读操作则访问从库。这就等于写死了，和业务强耦合了。

3）关键业务读写都由主库承担，非关键业务读写分离

类似付钱的这种业务，读写都到主库，避免延迟的问题，但是例如改个头像啊，个人签名这种比较不重要的就读写分离，查询都去从库查，毕竟延迟一下影响也不大，不会立马打客服电话投诉。

分配机制

分配机制的考虑也就是怎么制定写操作是去主库写，读操作是去从库读。

一般有两种方式：代码封装、数据库中间件。

代码封装

代码封装的实现很简单，就是抽出一个中间层，让这个中间层来实现读写分离和数据库连接。讲白点就是搞个 provider 封装了 save、select 等通常数据库操作，内部 save 操作的 dataSource 是主库的，select 操作的 dataSource 是从库的。

优点：

实现简单。
可以根据业务定制化变化，随心所欲。

缺点：

如果哪个数据库宕机了，发生主从切换了之后，就得修改配置重启。
如果系统很大，一个业务可能包含多个子系统，一个子系统是 java 写的，一个子系统用 go 写的，这样的话得分别为不同语言实现一套中间层，重复开发。

数据库中间件

就是有一个独立的系统，专门来实现读写分离和数据库连接管理，业务服务器和数据库中间件之间是通过标准的 SQL 协议交流的，所以在业务服务器看来数据库中间件其实就是个数据库。

优点：

因为是通过 SQL 协议的所以可以兼容不同的语言不需要单独写一套。
由中间件来实现主从切换，业务服务器不需要关心这点。

缺点：

多了一个系统其实就等于多了一个关心，比如数据库中间件挂了。
多了一个系统就等于多了一个瓶颈，所以对中间件的性能要求也高，因为所有的数据库操作都要先经过它。
中间件实现较为复杂，难度比代码封装高多了。

常用的开源数据库中间件有 Mysql Proxy、Atlas、LVS 等。

为什么使用 MySQL-Proxy 而不是 LVS？

LVS：分不清读还是写；不支持事务。
MySQL-Proxy：自动区分读操作和写操作；支持事务（注意在 MySQL-Proxy 中不要使用嵌套查询，否则会造成读和写的混乱）。

解决单点故障

MySQL-Proxy 实际上非常不稳定，在高并发或有错误连接的情况下，进程很容易自动关闭，因此打开 --keepalive 参数让进程自动恢复是个比较好的办法，但还是不能从根本上解决问题，通常最稳妥的做法是在每个应用服务器（如 Tomcat）上安装一个 MySQL-Proxy 供自身使用（解决 Proxy 单点故障问题），虽然比较低效但却能保证稳定性。

Proxy 之后搭 LVS，LVS 为两台主数据库做负载均衡，从数据库从两台主数据库同步。此方案旨在解决：

主数据库的单点故障问题（服务不可用、备份问题）
分担写操作的访问压力。

不过多主需要考虑自增长 ID 问题，这个需要特别设置配置文件，比如双主可以使用奇偶。总之，主之间设置自增长 ID 相互不冲突就能解决自增长 ID 冲突问题。

主从同步的方式也分很多种，一主多从、链式主从、多主多从，根据你的需要来进行设置。

# 分库分表

当访问用户越来越多，写请求暴涨，对于上面的单 Master 节点肯定扛不住，那么该怎么办呢？多加几个 Master？不行，这样会带来更多的数据不一致的问题，且增加系统的复杂度。那该怎么办？就只能对库表进行拆分了

常见的拆分类型有垂直拆分和水平拆分，一般来说我们拆分的顺序是先垂直后水平。

垂直分库

以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。

基于现在微服务的拆分来说，都是已经做到了垂直分库了

垂直分表

以字段为依据，按照字段的活跃性、数据长度等，将表中字段拆到不同的表（主表和扩展表）中。

水平分库/分表

以字段为依据，按照一定策略（hash、range 等），将一个库/表中的数据拆分到多个库/表中。

首先结合业务场景来决定使用什么字段作为分库/分表字段（sharding_key），比如我们现在日订单 1000 万，我们大部分的场景来源于 C 端，那么我们可以用 user_id 作为 sharding_key，数据查询支持到最近 3 个月的订单，超过 3 个月的做归档处理，那么 3 个月的数据量就是 9 亿，可以分 1024 张表，每张表的数据大概就在 100 万左右。

比如用户 id 为 100，那我们都经过 hash(100)，然后对 1024 取模，就可以落到对应的表上了。

常用的分库分表中间件

sharding-jdbc
Mycat

可能遇到的问题

事务问题：使用分布式事务。
跨节点 Join 的问题：可以分两次查询实现。
跨节点的 order by、group by 聚合函数、排序等问题：分别在各个节点上得到结果后在应用程序端进行合并。
数据迁移，容量规划，扩容等问题。
ID 唯一性问题：数据库被切分后，不能再依赖数据库自身的主键生成机制。

分表后id怎么保证唯一性

因为我们的主键默认都是自增的，那么分表之后的主键在不同表就肯定会有冲突了。有几个方案可以考虑：

设定步长。比如 1-1024 张表我们可以分别设定 1-1024 的基础步长，这样主键落到不同的表就不会冲突了。
分布式 ID。自己实现一套分布式 ID 生成算法，或者使用开源的比如雪花算法这种。
分表后不使用主键作为查询依据，而是每张表单独新增一个字段作为唯一主键使用，比如订单表订单号是唯一的，那么不管最终落在哪张表，都可以基于订单号作为查询依据，更新也一样。

分表后非 sharding_key 的查询怎么处理呢？

可以做一个 mapping 表，比如这时候商家要查询订单列表怎么办呢？不带 user_id 查询的话总不能扫全表吧？所以我们可以做一个映射关系表，保存商家和用户的关系，查询的时候先通过商家查询到用户列表，再通过 user_id 去查询。
打宽表。一般而言，商户端对数据实时性要求并不是很高，比如查询订单列表，可以把订单表同步到离线（实时）数据仓库，再基于数仓去做成一张宽表，再基于其他如 es 提供查询服务。
数据量不是很大的话，比如后台的一些查询之类，也可以通过多线程扫表，然后再聚合结果的方式来做，或者异步的形式也是可以的。

# MySQL与PostgreSQL的区别

VS	PostgreSQL	MySQL
开源	PostgreSQL是一个免费的开源系统，它受PostgreSQL许可证（自由的开源许可证）的约束。	MySQL属于Oracle旗下产品，并提供几种付费版本供用户使用
管理	PostgreSQL是全球用户共同发展的产品	MySQL是GNU通用公共许可以及各种专有协议条款下的产品
性能	PostgreSQL适合对读写速度要求很高的大型系统中使用	MySQL主要用于Web应用程序，该Web应用程序仅需要数据库来进行数据交易。
遵循ACID	PostgreSQL从头到尾都遵循ACID原则，并确保满足需求	MySQL只有在使用InnoDB和NDB集群存储引擎时才符合ACID要求。
SQL 兼容性	“从文档看，PostgreSQL是兼容大部分SQL的。 PostgreSQL支持SQL:2011的大多数功能。在核心一致性所需的179个强制性功能中，PostgreSQL至少兼容160个。此外，还有一系列受支持的可选功能。”	“从文档看，MySQL在某些版本是兼容部分SQL。我们对该产品的主要目标之一是继续努力达到SQL标准的要求，但又不牺牲速度或可靠性。我们可以添加SQL扩展或对非SQL功能的支持，如果这样可以极大地提高MySQL服务器在我们大部分用户群中的可用性。”
支持平台	PostgreSQL可以运行在Linux, Windows (Win2000 SP4 及以上)，FreeBSD，OpenBSD，NetBSD , Mac OS X, AIX, IRIX ,Solaris和 Tu64. 也支持由技术巨头惠普开发的HP-UX OS，以及开源的Unix OS。	MySQL可以运行在Oracle Solaris，Microsoft Windows, Linux Mac OS X。MySQL扩展了对开源FreeBSD OS的支持
编程语言支持	PostgreSQL是用C语言编写的，它支持多种编程语言，最突出的C/C++, Delphi, JavaScript, Java, Python, R , Tcl , Go, Lisp, Erlang和.Net.	PostgreSQL是用C和C++编写的，它支持C/C++, Erlang，PHP，Lisp,和Go, Perl，Java, Delphi, R ,和 Node.js.
物化视图	PostgreSQL支持物化视图	MySQL不支持物化视图
数据备份	PostgreSQL支持主备复制，并且还可以通过实现第三方扩展来处理其他类型的复制	MySQL支持主备复制，其中每个节点都是主节点，并且有权更新数据
可拓展性	PostgreSQL是高度可扩展的，您可以添加和拥有数据类型，运算符，索引类型和功能语言。	MySQL不支持拓展性。
访问方法	PostgreSQL支持所有标准。	MySQL支持所有标准。
社区支持	PostgreSQL有一个活跃的社区支持，该社区帮助改善现有功能，其富有创造力的提交者竭尽全力确保该数据库保持最新的功能和最大的安全性，成为最先进的数据库。	MySQL也有一个庞大的追随者社区，这些社区贡献者，特别是在被Oracle收购之后，主要关注一些偶尔出现的新功能，并维护现有功能。
安全性	PostgreSQL为连接提供本机SSL支持，以加密客户端/服务器通信。 PSQL还具有行级安全性。	MySQL是高度安全的，并且包含多个安全功能。

← 前后端开发概述（四）数据库SQL →