数据库基础

存储过程

存储过程是由一组 SQL 语句构成，预先编译好后存储在数据库中，可供前台应用程序多次调用。使用存储过程既能方便软件开发，又能减少解释执行 SQL 语句时句法分析和查询优化的时间，提高了效率。

优点：

• 可以重复使用
• 提高性能，使用时无需编译
• 减少网络流量
• 增加安全性，方式注入式 SQL 攻击，用户只有执行权限
• 可将 Grant、 Deny和 Revoke应用于存储过程

存储过程的优化思路

• 尽量利用一些 SQL 语句来替代一些小循环，例如聚合函数，求平均函数等
• 中间结果存放于临时表，加索引
• 少使用游标。SQL 是个集合语言，对于集合运算具有较高性能。而 cursors 是过程运算。比如
  对一个 100 万行的数据进行查询。游标需要读表 100 万次，而不使用游标则只需要少量几次
  读取
• 事务越短越好。SQL Server支持并发操作。如果事务过多过长，或者隔离级别过高，都会造成
  并发操作的阻塞，死锁。导致查询极慢，cpu占用率极地
• 使用 try-catch 处理错误异常
• 查找语句尽量不要放在循环内

触发器

触发器，是一类由数据库操作事件(插入、删除、修改)驱动的特殊过程，一旦由某个用户定义，任何用户对该触发器指定的数据进行增删改操作时，系统将自动激活响应的触发动作，在数据库服务器上进行集中的完整性控制。触发器的定义包括两部分内容：

• 指明触发器的触发事件
• 指明触发器执行的动作
  触发器的类型：

优点：

• 安全性：可以基于数据库的值使用户具有操作数据库的某种权利。可以基于时间限制用户的操作，例如不允许下班后和节假日修改数据库数据；可以基于数据库中的数据限制用户的操作，例如不允许股票的价格的升幅一次超过10%
• 审计：可以跟踪用户对数据库的操作。审计用户操作数据库的语句；把用户对数据库的更新写入审计表。
• 实现复杂的数据完整性规则：实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。与规则不同，触发器可以引用列或数据库对象。例如，触发器可回退任何企图吃进超过自己保证金的期货。提供可变的缺省值。
• 同步实时地复制表中的数据。

缺点：

• 消耗资源
• 延长响应时间

SQL中左连接和右连接的区别

• 左连接：左边有的，右边没有的为null
• 右连接：左边没有的，右边有的为null
• 内连接：显示左边右边共有的

数据库锁

数据库中的锁

在数据库中，使用锁来管理对共享资源的并发访问，维护数据一致性。在数据库中有两类锁，分别是 latch 和 lock 。latch 一般称为闩锁(轻量级锁)，因为其要求锁定的时间必须非常短，若持续的时间长，则其性能将会非常差。在 InnoDB 存储引擎中又分 mutex(互斥量) 和 rwlock(读写锁) 。其目的是保证并发线程操作临界资源的正确性，并且通常没有死锁检测机制。lock 的对象是事务，用来锁定的是数据库中的对象，如表、页、行。并且一般 lock 的对象仅在事务 commit 或 rollback 后进行释放(不同事务隔离级别释放的时间可能不同)。lock 有死锁机制。二者对比如下：

表锁 行锁 页锁

• 表级锁：开销小，加锁快；不会出现死锁(因为MyISAM会一次性获得SQL所需的全部锁)；锁定粒度大，发生锁冲突的概率最高,并发度最低
• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高
• 页锁：开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁。

Lock锁的类型

数据库中有多重Lock，分别是：

• 共享锁-S
• 排他锁-X
• 意向排他锁-IX
• 意向共享锁-IS

InnoDB存储引擎支持多粒度锁定，这种锁允许事务在行级上的锁和表级上的锁同时存在。为了支持不同粒度上进行加锁操作，InnoDB支持一种额外的上锁方式，称为意向锁。意向锁是将锁定的对象分为多个层次，意向锁意味着事务希望在更细粒度上进行加锁。锁兼容性对比：

行锁的实现方法分类

行锁有多重算法控制，这里仅对以下三种做件简要描述：

• Record Lock：单个行记录上的锁
• Gap Lock：间隙锁，锁定一个范围，但不包括记录本身
• Next-Key Lock：Record Lock+Gap Lock，锁定一个范围，并且锁定记录本身，可解决幻读问题

数据库中锁升级的理解

锁升级是指将当前锁的粒度提升。例如，数据库可以将一个表的 1000 个行锁升级为一个页锁，或者将页锁升级为表锁。在适当的时候将锁升级为更粗粒度的锁，这样可以保护锁资源，防止系统使用太多的内存来维护锁，在一定程度上提高了效率。

死锁以及解决死锁的理解

事务 A 与事务 B 由于某种调度顺序，可能会互相等待对方释放资源的锁，进而造成死锁忙的忙等。在数据库中，解决死锁采用两种方式，预防死锁和解决死锁。发生死锁的四个条件：

• 互斥
• 请求与保持
• 不剥夺
• 循环等待

预防死锁的方式如下：

• 一次封锁法：任务事务必须一次同时申请所有加锁请求，若不能同时加锁成功，则全部不加锁，并处于等待状态；若全部加锁成功，则可继续执行，在执行过程中不能对任何数据申请加锁
• 顺序封锁法：预先对所有数据对象规定一个顺序，任何一个事务要对几个数据对象进行封锁时，必须按照此规定顺序进行，若有一个对象封锁未成功，只能等待之，不得先封锁后面的数据对象

解决死锁的方式如下：

• 超时法
• 等待图法

银行家算法：当一个进程申请使用资源的时候，银行家算法通过先试探分配给该进程资源，然后通过安全性算法判断分配后的系统是否处于安全状态，若不安全则试探分配作废，让该进程继续等待。

悲观锁和乐观锁

悲观锁：顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会 block 直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。
乐观锁： 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。乐观锁有可能导致类似循环等待的死锁状态，解决方案是把事务中的语句执行顺序进行调整。

事务

事务是 RDBMS (关系数据库管理系统)提供的一种特殊手段。通过这一手段，应用程序将一系列的数据库操作组合在一起，作为一个整体执行，以保证数据库处于一致性状态。一个事务，可以是一个 SQL 语句，一组 SQL 语句或整个程序。事务的开始可以由用户显示控制。如果用户没有显示定义，则由具体的 DBMS 按缺省规定自动划分事务。事务可以嵌套使用，一个嵌套事务可以包含若干个子事务。在嵌套事务中，必须先完成所有的子事务，才能提交顶级事务。事务具有四个特性：

• 原子性：事务的所有操作要么全部执行，要么由于出错而被整体取消
• 一致性：当一个或多个事务并发执行时，如果不加以控制，将会产生不一致的中间数据，进而导致数据库中数据的错误更新。原子性是一致性的保证。一致性由 DBMS 的并发控制子系统实现。
• 隔离性：数据库保证事务 A 不受其他事务并发执行的影响。隔离性由 DBMS 的并发控制子系统实现。
• 持久性：事务一旦成功执行，其影响必须被存储在数据库中，即使系统故障也不应该导致数据库忽略成功完成的事务。

并发操作可能产生的异常

如果数据库不采用并发操作，那么会使用户的响应时间变得极大，且无法充分利用集群的资源。采用并发操作，可以：

• 提高系统资源利用率
• 改善事务的响应时间
  虽然产生并发操作可以带来诸多优点，但是会产生一些并发性问题：
• 丢失更新：当访问同一数据的多个事务以某种方式交替执行时(对同一数据写)，就可能产生丢失更新问题
• 读脏：事务更新了某个数据，接着由于某个原因事务被撤销，然而所更新的数据在被恢复之前，另一个事务读取了该数据
• 不可重复读：事务 A 需要对同一数据读取两次，然而两次读取间隔中，事务 B 修改了该数据导致事务 A 前后读取了不一致的值
• 幻读：事务 A 重复执行两次期间其他事务执行了插入、删除操作，使得事务 A 第二次查询的结果集与第一次查询不同

事务隔离级别

为了解决数据库出现的并发问题，数据库事务采用隔离级别进行解决。事务的隔离级别共有四种，分别为：

• Read UnCommitted(读未提交)：最低的隔离级别。一个事务可以读取另一个事务并未提交的更新结果
• Read Committed(读提交)：大部分数据库采用的默认隔离级别。一个事务的更新操作结果只有在该事务提交之后，另一个事务才可以的读取到同一笔数据更新后的结果
• Repeatable Read(重复读)：mysql的默认级别。整个事务过程中，对同一笔数据的读取结果是相同的，不管其他事务是否在对共享数据进行更新，也不管更新提交与否。InnoDB存储引擎默认支持该级别的事务隔离，但值得特别注意的是，InnoDB在该隔离级别下，使用Next-Key Lock算法，可以额外避免幻读这种情况
• Serializable(序列化)最高隔离级别。所有事务操作依次顺序执行。注意这会导致并发度下降，性能最差。通常会用其他并发级别加上相应的并发锁机制来取代它。大部分人质疑该隔离级别存在一定的性能问题，但是根据某大牛指出，某些情况下 Serializable 性能更优
  上述四中隔离级别可以逐个解决读脏、不可重复读、幻读这几类问题。
  

MySQL中修改事务隔离级别的方法：

在MySQL配置文件中[mysqld]添加一行：transaction-isolation=READ-COMMITTED

不可重复读和幻读的区别

不可重复读重点在于 update 和 delete ，而幻读的重点在于 insert。如果使用锁机制来实现这两种隔离级别，在可重复读中，该 sql 第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现可重复读了。但这种方法却无法锁住 insert 的数据，所以当事务 A 先前读取了数据，或者修改了全部数据，事务 B 还是可以insert数据提交，这时事务 A 就会发现莫名其妙多了一条之前没有的数据，这就是幻读，不能通过行锁来避免。需要 Serializable 隔离级别，读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力。所以说不可重复读和幻读最大的区别，就在于如何通过锁机制来解决他们产生的问题。

Record、Gap Next-Key Lock

• innodb对于行的查询使用 Kext-key lock
• Next-key lock为了解决Phantom Problem幻读问题
• 当查询的索引含有唯一属性时，将 Next-key lock 降级为 record key
• Gap 锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生
• 有两种方式显式关闭 gap 锁：（除了外键约束和唯一性检查外，其余情况仅使用record lock） A. 将事务隔离级别设置为Read Commited B. 将参数innodb_locks_unsafe_for_binlog设置为1

数据库的封锁

封锁是普遍采用的一种并发控制手段，封锁可以避免并发操作出现的问题。所谓封锁就是某事务在执行操作前，先对此对象加上自己的锁。加锁后其他事务对此对象的操作就受到了规定的限制。事务结束后再释放锁。锁的基本类型有两种：

• 排它锁
• 共享锁
  数据库中使用三级封锁协议来执行并发控制：
• 一级封锁协议：事务 A 在修改某一数据项前，必须先对其加上自己的排他锁，直到事务结束前才能释放之。事务结束包括正常结束和非正常结束(回滚)。该协议可解决丢失更新，不保证读脏、可重复读
• 二级封锁协议：一级封锁协议；任一事务在读取某数据前(不修改)，必须先对其加共享锁，读完即可释放共享锁。该协议可防止更新丢失和读脏，不保证可重复读
• 三级封锁协议：一级封锁协议；任一事务在读取某数据前(不修改)，必须先对其加共享锁，事务结束才释放共享锁。该协议可防止更新丢失、读脏、不可重复读等一致性问题

数据库事务的传播行为

• PROPAGATION_REQUIRED：如果当前没有事务，就创建一个新事务，如果当前存在事务，就加入该事务，该设置是最常用的设置。
• PROPAGATION_NESTED：如果当前存在事务，则在嵌套事务内执行。如果当前没有事务，则执行与PROPAGATION_REQUIRED类似的操作
• PROPAGATION_SUPPORTS：支持当前事务，如果当前存在事务，就加入该事务，如果当前不存在事务，就以非事务执行。
• PROPAGATION_MANDATORY：支持当前事务，如果当前存在事务，就加入该事务，如果当前不存在事务，就抛出异常。
• PROPAGATION_REQUIRES_NEW：支持当前事务，创建新事务，无论当前存不存在事务，都创建新事务。
• PROPAGATION_NOT_SUPPORTED：以非事务方式执行操作，如果当前存在事务，就把当前事务挂起。
• PROPAGATION_NEVER：以非事务方式执行，如果当前存在事务，则抛出异常。

MySQL引擎

什么是数据库引擎

数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理事务，从而满足企业内大多数需要处理大量数据的应用程序的要求。

MyISAM和InnoDB

MyISAM 是 MySQL 的默认数据库引擎（5.5版之前），由早期的 ISAM（Indexed Sequential Access Method：有索引的顺序访问方法）所改良。虽然性能极佳，但却有一个缺点：不支持事务处理（transaction）。InnoDB ，是 MySQL 的数据库引擎之一，最大特色就是支持了 ACID 兼容的事务（Transaction）功能，并且实现了 SQL 标准的四种隔离级别。MyISAM和InnoDB的区别如下：

• 事务支持性：MyISAM不支持事务，而InnoDB支持。InnoDB的AUTOCOMMIT默认是打开的，即每条SQL语句会默认被封装成一个事务并自动提交。每条语句组成一个事务会影响执行速度，所以最好是把多条SQL语句显示放在begin和commit之间，组成一个事务去提交。InnoDB具有事务的回滚和崩溃修复能力
• 存储结构：每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义；数据文件的扩展名为.MYD (MYData)；索引文件的扩展名是.MYI (MYIndex)。InnoDB所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB
• 存储空间：可被压缩，存储空间较小，支持静态表、动态表、压缩表三种存储格式。InnoDB需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引
• 可移植性、备份及恢复：MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。InnoDB可以通过备份BinLog或MuSQLDump，当数据量较大时会比较耗时
• 表锁差异：MyISAM只支持表级锁，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。MyISAM同一个表上的读锁和写锁是互斥的，MyISAM并发读写时如果等待队列中既有读请求又有写请求，默认写请求的优先级高，即使读请求先到，所以MyISAM不适合于有大量查询和修改并存的情况，那样查询进程会长时间阻塞。因为MyISAM是锁表，所以某项读操作比较耗时会使其他写进程饿死。InnoDB支持支持事务和行级锁，行锁大幅度提高了多用户并发操作的性能。但是InnoDB的行锁，只有支持WHERE的主键是有效的情况，非主键的WHERE都会锁全表
• 全文索引：MyISAM支持全文索引；InnoDB默认不支持全文索引
• 表主键：MyISAM允许没有任何索引和主键的表存在，索引保存行的地址；InnoDB中如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。此外InnoDB的主键范围更大，最大值是MyISAM的两倍
• 是否保存表的总行数：MyISAM保存有表的总行数，如果"select count(星号) from table;"会直接取出出该值；InnoDB没有保存表的总行数(只能遍历)，如果使用"select count(星号) from table;"就会遍历整个表，消耗相当大
• CURD性能：如果执行大量的SELECT，MyISAM是更好的选择；执行大量的INSERT、UPDATE或DELETE，出于性能方面的考虑，应该使用InnoDB
• 删除表时的执行逻辑：当执行删除时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令；MyISAM则重新建表
• 外键支持性：MyISAM不支持；InnoDB支持

MyISAM和InnoDB的应用场景

• MyISAM 管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的 SELECT 查询，那么MyISAM是更好的选择
• InnoDB 用于事务处理应用程序，具有众多特性，包括 ACID 事务支持。如果应用中需要执行大量的 INSERT 或 UPDATE 操作，则应该使用 InnoDB ，这样可以提高多用户并发操作的性能

索引

什么是索引

索引是存储引擎一种用于快速查找记录的数据结构。

索引的分类(顶层分类)

• B-Tree索引
• 哈希索引
• 全文索引

B-Tree索引

B+ 树索引的本质就是 B+ 树在数据库中的实现。但是 B+ 索引在数据库中有一个特点是高扇出性，因此在数据库中，B+数的高度一般为2-4层，意味着查找某一键值的行记录时最多需要2-4次I/O。数据库中的B+树索引分为聚簇索引和非聚簇索引。二者内部结构均为B+树，即高度平衡的，叶子节点存放数据。而二者不同的是，聚簇索引的叶子节点存放一整行的信息。不同存储引擎以不同的方式使用B-Tree索引，性能也各有不同。MyIASM索引通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行。B-Tree索引适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找。

为什么不用B树

用 B+ 树而不用 B 树考虑的是 IO 对性能的影响，B 树的每个节点都存储数据，而 B+ 树只有叶子节点才存储数据，所以查找相同数据量的情况下，B 树的高度更高，IO 更频繁。数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘页（对应索引树的节点）。此外 B+ 树更擅长范围查询。叶子节点数据是按顺序放置的双向链表。B 树范围查询只能中序遍历。

B树和B加树在用于文件系统时的区别

• B树的关键字集合分布在整颗树中
• B树任何一个关键字出现且只出现在一个结点中
• B树搜索有可能在非叶子结点结束
• B+树的中间节点不保存数据，所以磁盘页能容纳更多节点元素，更“矮胖”
• B+树查询必须查找到叶子节点，b树只要匹配到即可不用管元素位置，因此b+树查找更稳定（并不慢）
• 对于范围查找来说，b+树只需遍历叶子节点链表即可，b树却需要重复地中序遍历

一棵B+树能存多少行数据

答案1

以页大小为 16k 为例，若单行数据占 1k ，则每个叶子节点能存 16 行数据。中间节点假设主键大小为 8k， 指针节点为 6k， 则每个中间节点能存 16384/14=1170 个索引。双层可存 117016=18720 条数据；三层可存 11701170*16=21902400 条数据。

哈希索引

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。因为索引本身只需存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找速度极快。然而，哈希索引也有一定的限制：

• 哈希索引中只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。
• 哈希索引数据并不是按照索引值排序存储，所以无法用于排序
• 不支持部分索引匹配查找
• 只支持等值比较查询
• 如果哈希冲突很严重，一些索引维护代价也会很高
• 仅仅能满足=，IN，不能使用范围查询
• 不能避免表扫描

全文索引

全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文索引和其他几类索引的匹配方式不同。需要注意很多细节，例如停用词、词干和复数、布尔搜索等。全文索引更类似于搜索引擎做的事情，而不是简单的 where 条件匹配。

B+树索引的原理(为什么索引能加快查询)

没有索引的弊端

MySQL的基本存储结构是页，一页的大小为 16KB，其中行记录所占空间弹性变化。MySQL 中各个数据页之间使用双向链表连接，在每个数据页中的各个记录又以单向链表的形式连接。在查找某一行数据时，例如 select * from user where indexname = 'xxx'; 这种没有经过任何优化的SQL语句时，执行流程如下：首先定位到记录所在的页，需要遍历双向链表，找到所在的页；从所在的页内中查找相应的记录，由于不是根据主键查询，只能遍历所在页的单链表。采用上述流程查询，当数据量极大时，速度将会很慢，时间复杂度为O(N)。

索引的数据结构

索引底层对应的数据结构是平衡树(非二叉)，即 B 树和 B+ 树。这种数据结构能将无序的数据变成相对有序。当没有索引时，我们只能通过遍历双向链表来定位对应的页，有了 B+ 树，可以通过二分查找，并以 log(N) 的时间复杂度定位到所在页上。

为什么需要表主键

在关系型数据库中，一个表没有主键，数据将会无序地存储在磁盘存储器上，整齐地排列。如果给表加上了主键，那么表就会由整齐的排列结构(无序)变成树状结构，即平衡树结构，此时整个表就变成了一个索引，也就是聚集索引。这也是一个表只能有一个主键的原因，一个表只能有一个聚集索引，主键的作用就是把表中的数据格式转换成索引的格式放置。

主键与唯一性索引的区别

• 主键一定是唯一性索引，唯一性索引并不一定就是主键
• 一个表中可以有多个唯一性索引，但只能有一个主键
• 主键列不允许空值，而唯一性索引列允许空值
• 主键可以被其他字段作外键引用，而索引不能作为外键引用

为什么加索引后查询快

要找到id为8的记录简要步骤：

很明显的是：没有用索引我们是需要遍历双向链表来定位对应的页，现在通过 目录 就可以很快地定位到对应的页上了！（二分查找，时间复杂度近似为O(logn)）

假如一个表有一亿条数据，通过遍历双向链表页+页内单链表查询，时间复杂度为O(n)，此外这一亿条数据无法一次加载到内存中，还要提供额外的IO开销。如果使用这张表的平衡树结构，那么只需要对数次次IO开销即可完成查询。

为什么加索引后写入修改删除变慢

索引虽然能使查询速度有质的提升，但是会有一定的代价。索引本身占用磁盘空间、此外索引的维护也会带来相当大的性能开销。每当数据增删改时，为了维护索引的正确性，DBMS会重新梳理索引的结构。

索引的优点

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
• 可以大大加快数据的检索速度，这也是创建索引的最主要的原因
• 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义
• 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间
• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能
• 索引可以将随机 I/O 变成顺序 I/O
• 索引可以帮助服务器避免排序和临时表

索引的缺点

• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
• 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大
• 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度

聚簇索引

InnoDB 存储引擎是索引组织表，即表中数据按照主键顺序存放。如果没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键作为聚簇索引。通常来讲，聚簇索引就是按照每张表的主键构造一棵 B+ 树，同时叶子节点中存放的即为整张表的行记录数据，也将聚簇索引的叶子结点称为数据页。同 B+ 树一样，每个数据页通过一个双向链表连接。由于实际的数据页只能按照一棵 B+ 树进行排序，因此每张表只能拥有一个聚簇索引。
聚簇索引的优点：

• 相关数据关联，这样从磁盘读取少量数据页就能获得完整的目标数据，否则将会导致大量I/O
• 聚簇索引查找速度优于非聚簇索引
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键

聚簇索引的缺点：

• 聚簇索引在密集型I/O中性能优势明显，但是如果数据都在内存中，优势全无
• 插入速度严重依赖插入顺序。如果不是按照主键顺序加载数据，最好先使用OPTIMIZE TABLE命令重新组织表
• 更新聚簇索引列的代价很高
• 基于聚簇索引的表在插入新行或主键被更新导致需要移动行的时候，可能面临页分裂的情况。页分裂会导致表占用更多的磁盘空间
• 聚簇索引可能会导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候
• 二级索引(非聚簇索引)可能极大

非聚集索引(辅助索引)

辅助索引也称非聚簇索引，叶子节点并不包含行记录的全部数据。叶子节点除了键值以外，每个叶子节点中的索引行中还包括了一个书签。该书签用来告诉 InnoDB 存储引擎可以找到与索引的书签就是相应行的聚集索引键。非聚集索引和聚集索引一样，同样是采用平衡树作为索引的数据结构。如果给表中多个字段加上索引，那么就会出现多个独立的索引结构，每个索引（非聚集索引）互相之间不存在关联。每次给字段建一个新索引，字段中的数据就会被复制一份出来，用于生成索引。因此，给表添加索引，会增加表的体积，占用磁盘存储空间。

聚簇索引和非聚簇索引间的区别

非聚集索引和聚集索引的区别在于，通过聚集索引可以查到需要查找的数据，而通过非聚集索引可以查到记录对应的主键值，再使用主键的值通过聚集索引查找到需要的数据。

覆盖索引的理解

不管以任何方式查询表，最终都会利用主键通过聚集索引来定位到数据，聚集索引（主键）是通往真实数据所在的唯一路径。然而，有一种例外可以不使用聚集索引就能查询出所需要的数据，这种非主流的方法称之为「覆盖索引」查询，也就是平时所说的复合索引或者多字段索引查询。覆盖索引，即从非聚簇索引(辅助索引)中就可以得到查询的记录，而不需要查询聚簇索引的的记录。使用聚簇索引的好处是：

• 索引条目通常远小于数据行大小，所以如果只需要读取索引，那 MySQL 就会极大地减少数据访问量
• 因为索引是按照列值顺序存储，对于 I/O 密集型的范围查询会比随机从磁盘读取每一行数据的 I/O 要少的多
• 覆盖索引在一定程度上可以避免主键索引的二次查询

联合索引

联合索引是指对表上的多个列进行索引。联合索引的创建方法与单个索引创建的方法一样，不同之处仅在于有多个索引列。本质上来看，联合索引也是一棵 B+ 树，不同的是，联合索引的键值数量不是 1 而是大于等于 2 。联合索引的一个好处是已经对键值进行排序，可以避免多一次的排序操作。在设计实现联合索引时，应该着重考虑索引的顺序，一般来说，将选择性最高的列放在前面较好。

最左前缀原则

MySQL建立多列索引（联合索引）时有最左前缀的原则，即最左优先，如：

• 如果有一个2列的索引(col1,col2),则已经对(col1)、(col1,col2)上建立了索引；
• 如果有一个3列索引(col1,col2,col3)，则已经对(col1)、(col1,col2)、(col1,col2,col3)上建立了索引；

B+ 树的数据项是复合的数据结构，比如(name,age,sex)的时候，B+ 树是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，B+ 树会优先比较name来确定下一步的所搜方向，如果 name 相同再依次比较 age 和sex ，最后得到检索的数据；但当 (20,F) 这样的没有 name 的数据来的时候， B+ 树就不知道第一步该查哪个节点，因为建立搜索树的时候 name 就是第一个比较因子，必须要先根据 name 来搜索才能知道下一步去哪里查询。比如当 (张三,F) 这样的数据来检索时， b+ 树可以用 name 来指定搜索方向，但下一个字段 age 的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。（这种情况无法用到联合索引）

最左前缀的使用说明：

• mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整
• 和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

最左前缀的原则的实际验证

https://blog.csdn.net/albertsh/article/details/79192488

最左索引前缀失效的情况

下面对索引失效的情况简要列出：

• 如果条件中有or，即使其中有条件带索引也不会使用
• 对于多列索引，不是使用的第一部分，则不会使用索引
• like 查询是以%开头
• 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
• 如果 mysql 估计使用全表扫描要比使用索引快,则不使用索引

为什么要使用联合索引

• 减少开销：建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。但是对于大量数据的表，使用联合索引会大大的减少搜索开销
• 覆盖索引。对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2 。那么 MySQL 可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机 io 操作。减少 io 操作，特别的随机 io 其实是 dba 主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一
• 效率高：索引列越多，通过索引筛选出的数据越少。有 1000W 条数据的表，有如下sql: select from table where col1=1 and col2=2 and col3=3 ，假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出 1000W10%=100w 条数据，然后再回表从 100w 条数据中找到符合 col2=2 and col3=3 的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出 1000w10%10%*10%=1w ，效率提升可想而知

索引优化的不冗余原则

尽量扩展索引，而不是新建索引。索引本身会占用一定的磁盘空间，同时索引的维护也会给 DB 带来负担。基于最左匹配原则，尽量在原有基础上扩展索引，不要新增索引。能用单索引，不用联合索引；能用窄索引，不用宽索引；能复用索引，不新建索引。

索引优化的最大选择性原则

选择区分度高列做索引，所谓选择性是指不重复的索引值与表记录数的比值。

索引的应用注意事项

常见的索引原则

• 唯一性索引的值惟一
• 为经常需要排序、分组和联合操作的字段建立索引
• 为常做查询条件的字段建立索引
• 限制索引的数目
• 如果索引的值很长，那么查询速度会受到影响
• 如果索引字段的值很长，最好使用值前缀做索引
• 删除不再使用或者很少使用的索引
• 最左前缀匹配原则
• 尽量选择区分度高的列做索引
• 索引列不能参与计算，保持列的干净
• 尽量扩展索引而不是新建索引

应该添加索引的场景

• 在经常需要搜索的列上，可以加快搜索的速度
• 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构
• 在经常用在连接的列上，这 些列主要是一些外键，可以加快连接的速度
• 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
• 在经常需要排序的列上创 建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间
• 在经常使用在 Where 子句中的列上面创建索引，加快条件的判断速度

不应该添加索引的场景

• 对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求
• 对于那 些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
• 对于那些定义为 text, image 和 bit 数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
• 当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

MySQL中普通索引唯一索引和主索引的理解

普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHEREcolumn=）或排序条件（ORDERBYcolumn）中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列（如一个整数类型的数据列）来创建索引。

如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。这么做的好处：一是简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；二是MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。

主索引与唯一索引的唯一区别是：前者在定义时使用的关键字是PRIMARY而不是UNIQUE。

示例：

• mysql>ALTER TABLE table_name ADD INDEX index_name (column)
• mysql>ALTER TABLE table_name ADD UNIQUE (column)
• mysql>ALTER TABLE table_name ADD PRIMARY KEY (column)

MySQL中的常用数据类型

MySQL支持多种数据类型，大致可以分为四类：数值型、浮点型、日期/时间和字符串类型。例如：

• INT：4字节
• TINTINT：1字节
• BIGINT：8字节
• FLOAT与DOUBLE
• DATE：3字节
• DATETIME：8字节
• TIMESTAMP：8字节
• CHAR：0-255字节，定长字符串
• VARCHAR：0-65536字节，变长字符串
• TEXT：0-65535字节，长文本数据

char 类型和 varchar 类型的区别：

• char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格，varchar不限于此
• char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，所以varchar(4),存入3个字符将占用4个字节
• char类型的字符串检索速度要比varchar类型的快

MySQL架构与集群

MySQL分析器

SQL语句在分析器中主要进行词法分析和语法分析：

• 词法分析，一条 SQL 语句有多个字符串组成，首先要提取关键字，比如 select，提出查询的表，提出字段名，提出查询条件等等。做完这些操作后，就会进入下一步
• 语法分析，主要就是判断你输入的 sql 是否正确，是否符合 MySQL 的语法

MySQL优化器

优化器的作用就是它认为的最优的执行方案去执行（有时候可能也不是最优，这篇文章涉及对这部分知识的深入讲解），比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。经过了优化器之后可以说这个语句具体该如何执行就已经决定。

MySQL的日志模块binlog和redolog

binlog(归档日志) 是 MySQL 自带的日志模块，所有的存储引擎都可以使用；redolog(重做日志) 是 InnoDB 自带的日志模块。以一条更新语句为例，说明两个日志模块的使用次序：

update tb_student A set A.age='19' where A.name=' 张三 ';

• 先查询到张三这一条数据，如果有缓存，也是会用到缓存
• 然后拿到查询的语句，把 age 改为 19 。然后调用引擎 API 接口，写入这一行数据，InnoDB 引擎把数据保存在内存中，同时记录 redolog ，此时 redolog 进入 prepare 状态，然后告诉执行器，执行完成了，随时可以提交
• 执行器收到通知后记录 binlog ，然后调用引擎接口，提交 redolog 为提交状态
• 更新完成

redolog 的两阶段提交的方式可以解决数据一致性问题。

MySQL主从复制过程

主要涉及三个线程：binlog 线程、 I/O 线程和 SQL 线程。

• binlog 线程：负责将主服务器上的数据更改写入二进制日志（Binary log）中，运行在主机上。
• I/O 线程：负责从主服务器上读取二进制日志，并写入从服务器的中继日志（Relay log），运行在从机上。
• SQL线程：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放（Replay），运行在从机上。
  

Step1-主节点binlog线程

当从节点连接主节点时，主节点会创建一个log dump线程，用于发送 bin-log 的内容。在读取 bin-log 中的操作时，此线程会对主节点上的 bin-log 加锁，当读取完成锁会被释放。

Step2-从节点I/O线程

从节点会创建一个 I/O 线程用来连接主节点，请求主库中更新的 bin-log 。I/O 线程接收到主节点 binlog dump 进程发来的更新之后，保存在本地 relay-log 中。

Step3-从节点SQL线程

SQL 线程负责读取 relay log 中的内容，解析成具体的操作并执行，最终保证主从数据的一致性。

对于每一个主从连接，都需要三个进程来完成。当主节点有多个从节点时，主节点会为每一个当前连接的从节点建一个 binary log dump 进程，而每个从节点都有自己的 I/O 进程， SQL 进程。从节点用两个线程将从主库拉取更新和执行分成独立的任务，这样在执行同步数据任务的时候，不会降低读操作的性能。比如，如果从节点没有运行，此时 I/O 进程可以很快从主节点获取更新，尽管 SQL 进程还没有执行。如果在 SQL 进程执行之前从节点服务停止，至少 I/O 进程已经从主节点拉取到了最新的变更并且保存在本地 relay 日志中，当服务再次起来之后，就可以完成数据的同步。

MySQL主从复制模式

• 异步模式：主节点不会主动将 bin-log 推送到从节点上，主机执行读写后并写入 log 后即向用户返回
• 半同步模式(需插件支持)：主机接收到一台从机返回的消息后，执行 commit ；否则需要等待直到超时时间然后切换成异步模式再提交。这样做的目的可以使主从数据库的数据延迟缩小，可以提高数据安全性，确保了事务提交后，binlog 至少传输到了一个从节点上，不能保证从节点将此事务更新到 db 中。性能上会有一定的降低，响应时间会变长
• 同步模式：主机和从机全部执行了 commit 并确认才会向客户端返回成功

分库分表

为什么要做分库分表

关系型数据库由于单机存储容量、连接数、处理能力都有限，比较容易造成系统单点瓶颈。尤其是单表的数据量达到 1000W 或 100G 以后，由于查询维度较多，即使添加从库、优化索引，执行做很多操作时性能下降严重。此时就要考虑对其进行切分了，切分的目的就在于减少数据库的负担，缩短查询时间。数据切分就是将数据分散存储到多个数据库中，使得单一数据库中的数据量变小，通过扩充主机的数量缓解数据库的单点性能问题，从而达到提升数据库操作性能的目的。数据切分根据其切分类型，可以分为两种方式：垂直（纵向）切分和水平（横向）切分。

垂直切分

垂直切分常见有垂直分库和垂直分表两种。垂直分库就是根据业务的耦合性，将业务关联度低的不同表存储在不同的数据库。与系统拆分、微服务治理类似，每个微服务使用单独的一个数据库。垂直分表基于表中的列进行。由于表中的字段较多，可以新建一张扩展表，将不经常用或长度较大的字段拆到扩展表中。核心思想是——大表拆小表，便于开发和维护，也能避免跨页问题。垂直切分的优缺点如下：
优点：

• 解决系统业务层面的耦合，使得业务架构更清晰
• 与微服务的治理类似，也能对不同业务的数据进行分级管理、维护、监控、扩展等
• 高并发场景下，垂直切分一定程度地提升IO、数据库连接数、单机硬件资源的瓶颈

缺点：

• 部分表无法 join ，只能通过接口聚合方式解决，提升了开发的复杂度
• 存在分布式事务的复杂实现
• 依然存在单表数据量过大的问题(通过水平切分解决)

水平切分

当一个应用难以再以细粒度的方式进行垂直切分时，或切分后行数量巨大，依旧导致单库存在读写、存储瓶颈，这时就要进行水平切分。水平切分分为库内分表和分库分表，是根据表内数据内在的逻辑关系，将同一个表按不同的条件分散到多个数据库或多个表中，每个表中只包含一部分数据，从而使得单个表的数据量变小，从而实现分布式的效果。库内分表只解决了单一表数据量过大的问题，但没有将表分布到不同机器的库上，因此对于减轻MySQL数据库的压力来说，帮助不是很大，大家还是竞争同一个物理机的 CPU 、内存、网络 IO ，最好通过分库分表来解决。水平切分的优缺点如下：
优点：

• 不存在单库数据量过大、高并发的性能瓶颈，提升系统稳定性和负载能力
• 应用端改造较小，不需要拆分业务模块

缺点：

• 跨分片的事务一致性难以保证
• 跨库的 join 关联查询性能较差
• 数据多次扩展难度和维护量极大

分库分表的工具(中间件)

• Cobar：来自阿里的 mysql 中间件，但是现在已经很久没有更新了，它可以在分布式的环境下看上去像传统数据库一样为您提供海量数据服务
• Sharding JDBC：当当应用框架 ddframe 中，从关系型数据库模块 dd-rdb 中分离出来的数据库水平分片框架，实现透明化数据库分库分表访问
• Mycat：一个开源的分布式数据库系统，实现了 mysql 协议的服务器。前端用户可以把它看作是一个数据库代理，用 mysql 客户端工具和命令行访问，而其后端可以用 mysql 原生协议与多个 mysql 服务器通信，也可以用 jdbc 协议与大多数主流数据库服务器通信

用什么算法进行分库分表

Hash 路由的方式进行分库分表。

分表实现策略(关键词：用户ID、表容量)

对于大部分数据库的设计和业务的操作基本都与用户的 ID 相关，因此使用用户 ID 是最常用的分库的路由策略。用户的 ID 可以作为贯穿整个系统用的重要字段。因此，使用用户的 ID 我们不仅可以方便我们的查询，还可以将数据平均的分配到不同的数据库中。假设有 100 张表进行存储，则我们在进行存储数据的时候，首先对用户 ID 进行取模操作，根据 user_id%100 获取对应的表进行存储查询操作。

分库实现策略(关键词：用户ID、库容量)

数据库分表能够解决单表数据量很大的时候数据查询的效率问题，但是无法给数据库的并发操作带来效率上的提高，因为分表的实质还是在一个数据库上进行的操作，很容易受数据库 IO 性能的限制。很显然将数据进行分库操作可以很好地解决单台数据库的性能问题。分库策略与分表策略的实现很相似，最简单的都是可以通过取模的方式进行路由。

分库与分表实现策略(关键词：库容量、表容量)

如果同时需要实现分库与分表，需要一种更复杂的路由策略：

1. 中间变量 = user_id % (库的数量 * 每个库的表数量)
2. 库序号 = 取整(中间变量 / 每个库的表数量)
3. 表序号 = 中间变量 % 每个库的表数量

例如：数据库有256 个，每一个库中有1024个数据表，用户的user_id＝262145，按照上述的路由策略，可得：

1. 中间变量 = 262145 % (256 * 1024) = 1
2. 库序号 = 取整(1 / 1024) = 0
3. 表序号 = 1 % 1024 = 1

Hash 路由设计的复杂点主要体现在初始表数量的选取上，表数量太多维护比较麻烦，表数量太小又可能导致单表性能存在问题。而用 Hash 路由后，增加字表数量是非常麻烦的，所有数据都要重新分布。Hash 路由的优点是表分布比较均匀，缺点是扩容时很麻烦，所有数据均需要重新分布。

此外，可采用范围路由的方式用于实现分库分表。以最常见的用户 ID 为例，路由算法可以按照 1000000 的范围大小进行分段，1 ~ 9999999 放到数据库 1 的表中， 10000000~199999999 放到数据库 2 的表中，以此类推。范围路由设计的复杂点主要体现在分段大小的选取上，分段太小会导致切分后子表数量过多增加维护复杂度，分段太大可能会导致单表依然存在性能问题，按一般大老们的经验，分段大小 100W 至 2000W 之间。范围路由的优点如下：

• 可以随着数据的增加平滑地扩充新的表或库，原有的数据不需要动
• 单表大小可控
• 使用分片字段进行范围查找时，连续分片可快速定位查询，有效避免分片查询的问题

而范围路由的缺点为：

• 热点数据成为性能瓶颈。连续分片可能存在数据热点，例如按时间字段分片，有些分片存储最近时间段内的数据，可能会被频繁的读写，而有些分片存储的历史数据，则很少被查询

最后，可以基于路由配置表的方式实现。配置路由就是路由表，用一张独立的表来记录路由信息。同样以用户 ID 为例，新增一张 ROUTER 表，这个表包含 table_Id 两列，根据 user_id 就可以查询对应的修改路由表就可以了。配置路由设计简单，使用起来非常灵活，尤其是在扩充表的时候，只需要迁移指定的数据，然后修改路由表就可以了。其缺点就是必须多查询一次，会影响整体性能，而且路由表本身如果太大，性能会成为瓶颈点，如果再将路由表分库分表，则又面临一个死循环。

分库分表带来的问题以及相应的解决方案

分库分表能有效的环节单机和单库带来的性能瓶颈和压力， IO 、硬件资源、连接数的瓶颈，同时也带来了一些问题。下面将描述这些技术挑战以及对应的解决思路。

事务一致性问题

当更新内容同时分布在不同库中，不可避免会带来跨库事务问题。跨分片事务也是分布式事务，没有简单的方案，一般可使用 XA协议 和 两阶段提交 处理。分布式事务能最大限度保证了数据库操作的原子性。但在提交事务时需要协调多个节点，推后了提交事务的时间点，延长了事务的执行时间。导致事务在访问共享资源时发生冲突或死锁的概率增高。随着数据库节点的增多，这种趋势会越来越严重，从而成为系统在数据库层面上水平扩展的枷锁。

对于那些性能要求很高，但对一致性要求不高的系统，往往不苛求系统的实时一致性，只要在允许的时间段内达到最终一致性即可，可采用事务补偿的方式。与事务在执行中发生错误后立即回滚的方式不同，事务补偿是一种事后检查补救的措施，一些常见的实现方法有：对数据进行对账检查，基于日志进行对比，定期同标准数据来源进行同步等等。事务补偿还要结合业务系统来考虑。

跨节点关联查询的Join问题

分库后，数据可能分布在不同的节点上，此时 Join 带来的问题可能就比较麻烦。为了提高性能，尽量避免使用 Join 查询。解决办法如下：

• 使用全局表：全局表也可以看做数据字典表，即系统中所有模块都可能一依赖的一些列。为了避免跨库 Join 查询，可以将这类表在每个数据库中都保存一份，这些数据通常很少进行修改，无需担心一致性问题
• 字段冗余的设计：不同于常规的设计范式，利用空间换取时间，为了性能而避免 Join 的发生
• 数据组装：分两次查询，第一次查询的结果集中找出关联数据 id ，然后根据 id 发起第二次请求得到关联数据。最后将获得到的数据进行字段拼装

跨节点分页、排序、函数问题

跨节点多库进行查询时，会出现 limit分页 、 order by 排序等问题。分页需要按照指定字段进行排序，当排序字段就是分片字段时，通过分片规则就比较容易定位到指定的分片；当排序字段非分片字段时，就变得比较复杂了。需要先在不同的分片节点中将数据进行排序并返回，然后将不同分片返回的结果集进行汇总和再次排序，最终返回给用户。

全局主键避重问题

在分库分表环境中，由于表中数据同时存在不同数据库中，主键值平时使用的自增长将无用武之地，某个分区数据库自生成的 ID 无法保证全局唯一。因此需要单独设计全局主键，以避免跨库主键重复问题。有一些常见的主键生成策略：

• UUID
• 结合数据库维护主键 ID 表
• Snowflake 分布式自增 ID 算法

在 snowflake 中的64-bit分别表示如上图所示。41-bit的时间可以表示（1L<<41）/(1000L*3600*24*365)=69 年的时间，10-bit 机器可以分别表示1024台机器。如果我们对 IDC 划分有需求，还可以将 10-bit 分 5-bit 给IDC，分 5-bit 给工作机器。这样就可以表示 32 个IDC，每个 IDC 下可以有 32 台机器，可以根据自身需求定义。12 个自增序列号可以表示 2^12 个ID，理论上 snowflake 方案的QPS约为 409.6w/s，这种分配方式可以保证在任何一个 IDC 的任何一台机器在任意毫秒内生成的 ID 都是不同的。这种方式的优点为：

• 毫秒数在高位，自增序列在低位，整个ID都是趋势递增的
• 不依赖数据库等第三方系统，以服务的方式部署，稳定性更高，生成ID的性能也是非常高的
• 可以根据自身业务特性分配bit位，非常灵活

缺点为：

• 强依赖机器时钟，如果机器上时钟回拨，会导致发号重复或者服务会处于不可用状态

全局避重
美团点评分布式ID生成系统

数据迁移、扩容问题

当业务高速发展，面临性能和存储的瓶颈时，才会考虑分片设计，此时就不可避免的需要考虑历史数据迁移的问题。一般做法是先读出历史数据，然后按指定的分片规则再将数据写入到各个分片节点中。此外还需要根据当前的数据量和 QPS ，以及业务发展的速度，进行容量规划，推算出大概需要多少分片（一般建议单个分片上的单表数据量不超过 1000W ）。如果采用数值范围分片，只需要添加节点就可以进行扩容了，不需要对分片数据迁移。如果采用的是数值取模分片，则考虑后期的扩容问题就相对比较麻烦。

哪些场景下需要执行分库分表

• 能不切分尽量不要切分：不到万不得已不用轻易使用分库分表这个大招，避免过度设计和过早优化。分库分表之前，不要为分而分，先尽力去做力所能及的事情，例如：升级硬件、升级网络、读写分离、索引优化等等。当数据量达到单表的瓶颈时候，再考虑分库分表。
• 数据量过大，正常运维影响业务访问时进行切分
• 随着业务发展，需要对某些字段进行垂直拆分
• 数据量增长过快，单表中的数据量会持续增长，当性能接近瓶颈时，就需要考虑水平切分，做分库分表了。此时一定要选择合适的切分规则，提前预估好数据容量
• 安全性和可用性：在业务层面上垂直切分，将不相关的业务的数据库分隔，因为每个业务的数据量、访问量都不同，不能因为一个业务把数据库搞挂而牵连到其他业务。利用水平切分，当一个数据库出现问题时，不会影响到 100% 的用户，每个库只承担业务的一部分数据，这样整体的可用性就能提高

分布式数据库

分布式事务

分布式事务就是指事务的参与者、支持事务的服务器分别位于不同的分布式系统的不同节点之上。事务一次大的操作由不同的小操作组成，这些小的操作分布在不同的服务器上，且属于不同的应用，分布式事务需要保证这些小操作要么全部成功，要么全部失败。本质上来说，分布式事务就是为了保证数据库在分布式场景下的数据一致性。

分布式事务产生的原因

• 数据库分库分表：如果一个操作既访问 A 库，又访问 B 库，而且要保证数据的一致性，那么就要用到分布式事务
• 应用 SOA 化：所谓的 SOA 化，就是业务的服务化。比如原来单机支撑了整个电商网站，现在对整个网站进行拆解，分离出了 订单中心 、 用户中心 以及 库存中心。对于订单中心，有专门的数据库存储订单信息，用户中心也有专门的数据库存储用户信息，库存中心也会有专门的数据库存储库存信息。这时候如果要同时对订单和库存进行操作，那么就会涉及到订单数据库和库存数据库，为了保证数据一致性，就需要用到分布式事务

分布式事务的解决方案

基于XA协议的两阶段提交(2PC)

二阶段提交(Two-pha***mit)是指，在计算机网络以及数据库领域内，为了使基于分布式系统架构下的所有节点在进行事务提交时保持一致性而设计的一种算法(Algorithm)。通常，二阶段提交也被称为是一种协议(Protocol))。在分布式系统中，每个节点虽然可以知晓自己的操作时成功或者失败，却无法知道其他节点的操作的成功或失败。当一个事务跨越多个节点时，为了保持事务的 ACID 特性，需要引入一个作为协调者的组件来统一掌控所有节点(称作参与者)的操作结果并最终指示这些节点是否要把操作结果进行真正的提交(比如将更新后的数据写入磁盘等等)。因此，二阶段提交的算法思路可以概括为：参与者将操作成败通知协调者，再由协调者根据所有参与者 的反馈情报决定各参与者是否要提交操作还是中止操作。

• 准备阶段：事务协调者(事务管理器)给每个参与者(资源管理器)发送消息，每个参与者要么直接返回失败(如权限验证失败)，要么在本地执行事务，写本地的 redo 和 undo 日志，但不提交，到达一种万事俱备，只欠东风的状态
• 提交阶段：如果协调者收到了参与者的失败消息或超时，直接给每个参与者发送回滚(Rollback)消息；否则，发送提交(Commit)消息；参与者根据协调者的指令执行提交或者回滚操作，释放所有事务处理过程中使用的锁资源。(注意:必须在最后阶段释放锁资源)

二阶段提交存在的问题：

• 阻塞问题：二阶段提交的准备阶段中，协调者需要等待参与者的响应，如果没有接收到任意参与者的响应，这时候进入等待状态，而其他正常发送响应的参与者，将进入阻塞状态，将无法进行其他任何操作，只有等待超时中断事务，极大的限制了系统的性能
• 单点问题：协调者处于一个中心的位置，一旦出现问题，那么整个二阶段提交将无法运转，更为严重的是，如果协调者在阶段二中出现问题的话，那么其他参与者将会一直处于锁定事务资源的状态中，将无法继续完成操作
• 在阶段二中，当协调者向参与者发送commit请求后，发生了局部网络异常或在发送commit请求过程中协调者出现了故障，导致只有一部分参与者接到了commit请求。于是整个分布式系统遍出现了数据不一致的现象，即脑裂
• 协调者再发出commit消息之后宕机，而唯一接收到这条消息的参与者同时也宕机了。那么即使协调者通过选举协议产生了新的协调者，这条事务的状态也是不确定的，没人知道事务是否被已经提交

三阶段提交(3PC)

3PC 是 2PC 的改进版本，将 2PC 的提交阶段一分为二，由 CanCommit、PreCommit 和 doCommit 三个阶段组成的事务处理协议：

3PC 同时在协调者和参与者引入超时机制，一旦事务参与者迟迟没有收到协调者的 commit 请求，就会自动进行本地 commit，这样相对有效地解决了协调者单点故障的问题。；在 2PC 与 3PC 之间插入一个准备阶段，保证了在最后提交阶段之前各参与节点的状态一致。3PC 的各个阶段如下：

• CanCommit：协调者向参与者发送 commit 请求，参与者检查自身状态的健康性，看有没有能力进行事务操作。如果参与者满足条件就返回 yes 响应，否则返回 no 响应
• PreCommit：协调者根据参与者的反应情况来决定是否可以继续进行，有以下两种可能。假如协调者从所有的参与者获得的反馈都是 yes 响应，那么就会执行事务的预执行；假如有任意一个参与者向协调者发送 no 响应，或者等待超时后，协调者都没有接到参与者的响应，那么就执行事务的中断
• DoCommit：该阶段进行真正的事务提交，主要包括：1.协调者发送提交请求；2.参与者提交事务；3.参与者响应反馈(事务提交完之后，向协调者发送ACK响应)；4.协调者确定完成事务

在 DoCommit 阶段，如果 参与者 无法及时接收到来自 协调者 的 DoCommit 或者 rebort 请求时，会在等待超时之后，会继续进行事务的提交。（其实这个应该是基于概率来决定的，当进入第三阶段时，说明参与者在第二阶段已经收到了 PreCommit 请求，那么协调者产生 PreCommit 请求的前提条件是在第二阶段开始之前，收到所有参与者的 CanCommit 响应都是 Yes 。（一旦参与者收到了PreCommit，意味他知道大家其实都同意修改了）所以，一句话概括就是，当进入第三阶段时，由于网络超时等原因，虽然参与者没有收到 commit 或者 abort 响应，但是他有理由相信：成功提交的几率很大。 ）

三段提交的核心理念是：在询问的时候并不锁定资源，除非所有人都同意了，才开始锁资源。但是 3PC 也存在一致性问题：旦参与者无法及时收到来自协调者的信息之后，他会默认执行 commit 。而不会一直持有事务资源并处于阻塞状态，但是这种机制也会导致数据一致性问题。

无论是二阶段提交还是三阶段提交都无法彻底解决分布式的一致性问题。世上只有一种一致性算法，那就是 Paxos ，所有其他一致性算法都是 Paxos 算法的不完整版。

3pc

消息事务

所谓消息事务，就是基于消息中间件的两阶段提交，本质上是对消息中间件的一种特殊应用。消息事务将本地事务和消息发送放在了一个分布式事务中，保证本地操作和消息发送要么同时成功，要么同时失败，RocketMQ就支持这一特性。

柔性事务

柔性事务（遵循BASE理论）是指相对于 ACID 刚性事务而言的。柔性事务分为：

• 两阶段型
• 补偿型
• 异步确保型
• 最大努力通知型

补偿型事务

TCC 其实就是采用的补偿机制，其核心思想是：针对每个操作，都要注册一个与其对应的确认和补偿（撤销）操作。它分为三个阶段：

• Try 阶段主要是对业务系统做检测及资源预留
• Confirm 阶段主要是对业务系统做确认提交，Try 阶段执行成功并开始执行 Confirm 阶段时，默认Confirm 阶段是不会出错的。即：只要 Try 成功，Confirm 一定成功
• Cancel 阶段主要是在业务执行错误，需要回滚的状态下执行的业务取消，预留资源释放

补偿型的例子，在一个长事务（long-running）中，一个由两台服务器一起参与的事务，服务器 A 发起事务，服务器 B 参与事务，B 的事务需要人工参与，所以处理时间可能很长。如果按照 ACID 的原则，要保持事务的隔离性、一致性，A 中发起的事务中使用到的事务资源将会被锁定，不允许其他应用访问到事务过程中的中间结果，直到整个事务被提交或者回滚。这就造成事务 A 中的资源被长时间锁定，系统的可用性将不可接受。

WS-BusinessActivity 提供了一种基于补偿的 long-running 的事务处理模型。服务器 A 发起事务， 服务器 B 参与事务，服务器 A 的事务如果执行顺利，那么事务 A 就先行提交，如果事务 B 也执行顺利，则事务 B 也提交，整个事务就算完成。但是如果事务 B 执行失败，事务 B 本身回滚，这时 事务 A 已经被提交，所以需要执行一个补偿操作，将已经提交的事务 A 执行的操作作反操作，恢 复到未执行前事务 A 的状态。这样的 SAGA 事务模型，是牺牲了一定的隔离性和一致性的，但是 提高了 long-running 事务的可用性。

异步确保型事务

通过将一系列同步的事务操作变为基于消息执行的异步操作，避免了分布式事务中的同步阻塞操作的影响。

• 发送者向 MQ 服务器发送 Half 消息
• MQ 服务器返回 ACK
• 执行本地事务
• MQ 发送者向 MQ 服务器发送 Commit 或回滚请求
• 如果没有收到上一行动作的确认，则查看事务状态
• 检查本地事务的状态
• 根据上一行动作的状态向 MQ 服务器发送 Commit 或回滚请求

最大努力通知型事务

这是分布式事务中要求最低的一种，也可以通过消息中间件实现，与前面异步确保型操作不同的一点是，在消息由 MQ Server 投递到消费者之后，允许在达到最大重试次数之后正常结束事务。

数据库场景相关

一条SQL语句在MySQL中的执行过程(查询在内部如何流转)

首先，一条语句在MySQL中执行时，涉及到诸多组件，分别如下：

• 连接器： 身份认证和权限相关(登录 MySQL 的时候)
• 查询缓存: 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）
• 分析器: 没有命中缓存的话，SQL语句就会经过分析器，分析器说白了就是要先看你的SQL语句要干嘛，再检查你的SQL语句语法是否正确
• 优化器：按照 MySQL 认为最优的方案去执行
• 执行器: 执行语句，然后从存储引擎返回数据

简单来说 MySQL 主要分为 Server 层和存储引擎层：
Server 层主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块。存储引擎层主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、 MyISAM 、 Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redolog 模块。

一条SQL语句执行地很慢的原因

一条语句执行地慢，有可能有两种情况。其一，偶尔很慢；其二，在数据量不变的情况下，一直很慢。

偶尔很慢的情况

• 数据库在刷新脏页：数据库在插入、更新一条数据时，并不会马上持久化到磁盘中，而是将记录写入redolog 中，等到空闲的时候，再通过 redolog 里的日志将最新数据同步到磁盘中。当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为脏页。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为干净页。如果 redolog 写满了，就必须暂停执行其他操作，将所有数据同步到磁盘后再继续执行，导致 SQL 语句执行地很慢
• 无法拿到锁

一直很慢的情况

• 没使用索引：待搜索的字段没有索引，执行全表扫描；或字段有索引，但是没有用到索引，例如不满足最左前缀原则；对字段使用函数，导致无法使用索引
• 辅助索引导致的聚簇索引二次搜索

数据库读写分离的理解

读写分离的目的是为了提高数据库服务的性能，从而支持更大规模的并发访问。读写分离采用数据冗余的方式，每台从机保存了完整的业务数据。结构上采用一主多从的结构，主机负责处理写操作，从机负责处理读操作。数据同步由主机执行。读写分离常用代理方式实现，代理服务器接收应用程序传来的读写请求，然后决定转发到哪个具体的服务器进行处理。读写分离能提高性能的原因在于：

• 主从服务器负责各自的读和写，极大程度上缓解了锁的争用
• 从服务器可以使用MyISAM，从而提供更高性能的查询并节约开销
• 增加冗余性的同时，可提高可用性

读写分离适用的场景

• 并发访问量大，单机已经无法满足并发请求
• 读操作远远多于写操作
• 对数据实时性的要求不严苛

读写分离存在的瓶颈以及相应的解决方案

采用读写分离时，最大的问题就是存在主从复制延迟。数据写入主服务器后，由于主服务器数据同步到从服务器存在延迟，导致从机读取不到最新的数据。

MySQL删除一张表的方式与区别

• 操作上：DELETE 语句执行删除的过程是每次从表中删除一行；TRUNCATE 则一次性地从表中删除所有的数据；
• Rollback 支持性：DELETE 将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作；TRUNCATE 并不把单独的删除操作记录记入日志保存，删除行是不能恢复的，并且在删除的过程中不会激活与表有关的删除触发器，执行速度快
• 表结构保留：DELETE 与 TRUNCATE 保留表结构；DROP 全删除
• 索引变化：DELETE 不涉及索引大变化；TRANCATE 则重建索引
• 性能差异：DROP 最快，其次是 TRANCATE ，DELETE 最慢

在没有备份情况下，谨慎使用 drop 与 truncate 。要删除部分数据行采用 delete 且注意结合 where 来约束影响范围。回滚段要足够大。要删除表用 drop ;若想保留表而将表中数据删除，如果于事务无关，用 truncate 即可实现。

Truncate table 表名 速度快,而且效率高,因为:
truncate table 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。