MySQL¶

事务 ¶

事务是满足ACID特性的一组操作。

• 原子性（Atomicity）：事务时不可分割的最小单元，事务内的语句，要么全部执行成功，要么全部执行失败。
• 一致性（Consistency）：数据库在事务执行前后都保持一致性状态。在一致性状态下，所有事务对一个数据的读写结果都是相同的。
• 隔离性（Isolation）：一旦事务所做的修改在最终提交以前，对其他事务是不可见的。
• 持久性（Durability）：一旦事务提交，则其所做的修改将会永远保存在数据库中。

MYSQL 默认采用自动提交模式。

并发一致性 ¶

• 丢失修改：T1 和 T2 两个事务都对一个数据进行修改，T1 先修改，T2 后修改，则 T2 点修改会覆盖 T1.
• 读脏数据：T1 修改一个数据，T2 随后读取这个数据。如果 T1 撤销了这次修改，则 T2 读到的是脏数据。
• 不可重复读：T2 读取一个数据，T1 对该数据做了修改，此时 T2 再次读取这个数据会和第一次读取的结果不同。
• 幻影读：T1 读取某个范围的数据，T2 在这个范围内插入了新的数据，T1 再次读取和第一次的结果不同。

B 树和 B+ 树 ¶

• B 树的特点
  • 一个节点上包括至多 \(m-1\) 个值
  • 根节点至少有两个孩子
  • 非叶子结点如果包含了 \(k\) 个值，则其包含了 \(k+1\) 个孩子节点
  • 所有叶子结点都位于同一层
• B+ 树的特点
  • 所有的非叶子结点不再保存值，而是只保存了中间值
  • 所有值保存在叶子结点上
  • 所有的叶子结点通常链表按照顺序进行连接
• 为什么数据库会采用 B+ 树而不是 B 树活着 AVL 树
  • AVL 的结点访问次数更多，而对于数据库而言，每个节点通常被存储在一个文件中，所以需要读取的文件数量更多，导致效率低
  • 在数据库中有时需要进行范围的所有，此时 B+ 树的链表结构可以快速的找到某个结点的下一个结点位置

MySQL 锁 ¶

• 类型
  • 全局锁
  • 表级锁
  • 行级锁
• 读写锁
  • 读锁：可以与其他的读锁共存，但是不可以与写锁共存
  • 写锁：不可以与其他任何锁共存
• 悲观锁
  • 普通的锁，锁定此行 / 表 / 数据库以防止其他操作进行修改，会导致其他事务被阻塞
• 乐观锁
  • 通过比较版本号的区别的方法，来确定此数据是否经过修改，如果修改则需要读取最新的值

数据库隔离级别 ¶

• 隔离级别：
  • 未提交读：最朴素的数据形式
  • 已提交读：在事务完成之后再更新数据库的值
  • 可重复读：每个事务开始前锁定所更新的行
  • 可串行化：单一线程，所有事务必须按照顺序进行 3

存储引擎 InnoDB 和 MyISAM ¶

InnoDB¶

是 MySQL 默认的存储引擎。实现了四个标准的隔离级别。默认是可重复读，通过 MVCC 和 Next-Key Locking 防止幻影读。支持真正的在线热备份，其他存储引擎不支持在线热备份。

• Record Locks：锁定一个记录上的索引，而不是记录本身。
• Gap Locks：锁定索引之间的间隙，而不是索引本身。
• Next-Key Locks：InnoDB 存储引擎的一种锁实现，是 Record Locks 和 Gap Locks 的结合。不仅仅锁定索引，也锁定索引之间的间隙。

MyISAM¶

设计简单，数据以紧密格式存储。提供了大量的特性，包括压缩表、空间数据索引等。不支持事务，不支持行级锁，只支持表级锁，可以手工或者自动执行检查和修复操作，但可能导致一些数据丢失，而且修复操作非常慢。

多版本并发控制（MVCC）¶

多版本并发控制（Multi-Version Concurrency Control, MVCC）是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。而未提交读隔离级别总是读取最新的数据行，要求很低，无需使用 MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。

InnoDB 的 MVCC，是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存了行的过期时间。存储的不是实际的时间值，而是系统的版本号。MVCC 只要可重复读和提交读两个隔离级别下工作。 在可重复读的隔离级别下，MVCC 的操作： - SELECT 1. InnoDB只查找版本早于当前事务版本的数据行，这样可以确保事务读取的行，要么是事务开启前已经存在的，要么是事务自身插入或者修改过的。 2. 行的删除版本号要么未定义，要么大于当前事务的版本号，这可以保证事务读取到的行，在事务开始前未被删除。 - INSERT 1. 为插入的每一行保存当前系统版本号作为行版本号。 - DELETE 1. 为删除的每一行保存当前系统版本号为删除版本号。 - UPDATE 1. 修改后的数据行保存当前系统版本号为创建版本号，同时保存当前系统版本号作为原来的行的删除版本号。

范式 ¶

• 第一范式：属性不可分
• 第二范式：每个非主属性完全函数依赖于键码
• 第三范式：非主属性不传递函数依赖于键码
• 范式化的好处：
  1. 范式化的更新操作通常比反范式化要快
  2. 当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据。
  3. 范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快。
• 范式化的缺点：通常需要关联，代价昂贵，也可能使一些索引策略无效。

查询优化 ¶

查询性能低下的最基本的原因是访问的数据太多。

• 是否向数据库请求了不需要的数据

  1. 只返回必要的列，最好不要用select *进行查询
  2. 只返回必要的行，使用 limit 来限制返回的数据
  3. 缓存重复查询的数据

• 是否在扫描额外的记录

  1. 使用索引覆盖扫描，把所有需要用到的列都放在索引中， 这样存储引擎无需回表获取对应行就可以返回结果
  2. 改变库表结构
  3. 重构查询方式

• 重构查询方式

  1. 切分大查询
  2. 分解大连接查询

主从复制 ¶

概念 ¶

MySQL主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。

作用 ¶

• 数据分布
• 负载均衡
• 数据备份，保证数据安全
• 高可用性和容错行
• 实现读写分离，缓解数据库压力

由于 mysql 实现的异步复制，所以主库和从库之间存在一定的差异，在从库执行查询操作需要考虑这些数据的差异，一般只有更新不频繁和对实时性要求不高的数据可以通过从库查询，实行要求高的仍然要从主库查询。

原理 ¶

MySQL 主从复制涉及到三个线程，一个运行在主节点（log dump thread），其余两个（I/O thread， SQL thread）运行在从节点，如下图所示：

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

连接池 ¶

• 为什么使用连接池
  • 与 Java 的线程池相同，为了节约系统资源
• 常用的连接池
  • DBCP：使用量最大的连接池
  • C3P0

分表 ¶

• 水平分表
  • 将一个表的记录分割到数个表中，可以减少索引的大小，加快索引
• 垂直分表
  • 将部分字段划分至其他的表，部分字段数据量大，进行索引时会带来大量的 IO 负担，进行分表有利于查询效率

SQL¶

一条 SQL 语句的执行过程： 1. 连接器查看当前用户权限，若拥有权限，建立连接。如果客户端太长时间没有动静，连接器会自动断开连接 2. 分析器分析SQL语句，包括语法分析（语法是否正确），词法分析（识别SQL语句）。 3. 优化器优化查询，决定使用的索引等。 4. 执行器来时执行语句。

说一下索引？¶

什么是索引 ¶

索引是一种特殊的文件（InnoDB 数据表上的索引是表空间的一个组成部分），他们包含着对数据表里所有记录的引用指针。 索引是一种数据结构。数据库索引，是数据库管理系统中的一个排序的数据结构，为了协助快速查询、更新数据表中数据。索引的实现通常使用B树以及变种B+树。

索引的缺点 ¶

• 时间方面：创建索引和维护索引要耗费时间，具体的，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会较低增改删的执行效率。
• 空间方面：索引需要占用物理空间。

索引的主要类型 ¶

• 物理存储角度：
  • 聚簇索引：以主键作为 B+ 树索引的键值而构建的 B+ 树索引
  • 非聚簇索引：以主键之外的列值作为键值构建的 B+ 树索引
• 数据结构角度：
  • B+ 树索引
  • hash 索引
  • FULLTEXT 索引
  • R-Tree 索引
• 逻辑角度：
  • 主键索引：数据列不允许重复，不允许为NULL，一个表中只能有一个主键
  • 普通 / 单列索引：基本的索引类型，没有唯一性的性质，允许为NULL
  • 多列 / 联合 / 复合索引：以一定顺序引用多列
  • 唯一索引：数据列不允许重复，允许为NULL，一个表中允许多个列创建唯一索引
  • 全文索引：目前搜索引擎使用的一种关键技术
  • 空间索引：对空间数据类型的字段建立的索引

最左前缀匹配原则 ¶

主要针对联合索引，如果查询的时候查询条件精确匹配索引的左边连续一列或多列，则此列就可以被用到。

redis 应用场景？¶

缓存、计数器、队列、位操作、最新列表。

说一下回表操作？¶

非聚簇索引和聚簇索引的区别在于非聚簇索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚簇索引中进行查找，这个再根据聚簇索引查找数据的过程，被称为回表。

如何避免回表查询 ? 什么是索引覆盖 ? ¶

如何避免：将被查询的字段，建立到联合索引中。尽量使用到索引覆盖，不应该使用*来全表查询。

索引覆盖是指从索引中就可以得到查询结果，从而不需要查询聚簇索引中的行数据信息。

需要说明的是，覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引不存储索引列的值，所以 mysql 只能用 B+tree 索引做覆盖索引。

说一下表连接的操作？¶

在 MySQL 中，常见的表连接方式有 4 类，共 7 种方式： - INNER JOIN：inner join是根据表连接条件，求取两个表的数据交集； - LEFT JOIN：left join是根据表连接条件，求取两个表的数据交集再加上左表剩下的数据，此外，还可以用where过滤条件 - RIGHT JOIN：right join是根据表连接条件，求取两个表的数据交集再加上右表剩下的数据，此外，还可以使用where过滤条件 - FULL JOIN：full join是左连接和右连接的并集，MySQL并未提供full join的语法，如果要实现，需要left join于right join进行求并集，还有用where过滤

索引什么时候会失效？¶

1. 条件中有or
2. like查询（以%开头）
3. 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引
4. 对列进行函数运算
5. 负向查询条件会导致无法使用索引，比如NOT IN，NOT LIKE， !=等
6. 对于联合索引，不是使用第一部分（第一个），则不会使用索引（最左匹配）
7. 如果 mysql 评估使用全表扫描要比使用索引块，则不使用索引

判断 sql 是否使用索引 ¶

使用解释函数explain，只需添加在 sql 语句之前即可：

我们只需要注意一个最重要的 type 的信息很明显的提现是否用到索引： type结果值从好到坏依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题。

possible_keys：sql 所用到的索引 key：显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL rows: 显示MySQL认为它执行查询时必须检查的行数。

说一下锁？说一下区别？¶

Redis 缓存更新策略 ¶

高并发 ¶

高并发（High Concurrency）是指系统运行过程中的一种“短时间内遇到大量操作请求”的情况，主要发生在 web 系统集中大量访问收到大量请求，例如淘宝双十一、京东 618 类的活动。该情况的发生会导致系统在这段时间内执行大量操作（对资源的请求、数据库的操作等）。

高并发相关常用的一些指标有：响应时间、吞吐量、每秒查询率 QPS、并发用户数。
响应时间：系统对请求做出响应的时间。例如系统处理一个 http 请求需要 600ms，这个 600ms 就是系统的响应时间。
吞吐量：单位时间内处理的请求数量。
QPS：每秒响应请求数。
并发用户数：同时承载正常使用系统功能的用户数量。例如一个即时通讯系统，同时在线的用户量一定程度上代表了系统的并发用户数。

简单来说，高并发的基本表现就是系统在单位时间内能够同时处理的请求数。高并发没有具体的范围规定多少并发算是高并发，比如你开发的系统最大并发是 1000，那么来了 1001 的并发量对你来说就是高并发，但是这个并发量放在淘宝上，简直什么都算不上。

如果高并发处理不好，不仅会降低用户的体验度 ( 请求响应时间过长等 )，同时可能导致系统宕机、停止工作等。

使用缓存 ¶

大家都知道 MySQL 加上 Redis 是一对儿经典的组合。使用 Redis 作为 MySQL 的前置缓存，可以为 MySQL 挡住大部分查询请求，可以很大程度上缓解 MySQL 并发请求的压力。Redis 是一个使用内存保存数据的高性能 K-V 数据库，它的高性能主要来自于：简单的数据结构和使用内存存储数据。但是我们需要知道的是内存本身就是一种易失性存储，所以使用 Redis 不能保证数据可靠存储。从设计上来说，Redis 牺牲了数据可靠性，换取了高性能。但也正是这些特性，使得 Redis 特别适合用来做 MySQL 的前置缓存。

即使只是把 Redis 作为缓存来使用，我们在设计 Redis 缓存的时候，也必须要考虑 Redis 的这种数据不可靠性，或者换句话说，我们的系统在使用 Redis 的时候，要能兼容 Redis 丢数据的情况，做到即使 Redis 发生了丢数据的情况，也不影响系统的数据准确性。

关于缓存更新策略，使用的多的也就是Read/Write Through 模式和Cache Aside 模式， Read/Write Through 模式 在查询数据的时候，先去缓存中查询，如果命中缓存那就直接返回数据；如果没有命中，那就去数据库中查询，得到查询结果之后把数据写入缓存，然后返回。在更新数据的时候，先去更新数据库，如果更新成功，再去更新缓存中的数据。

总结 ¶

使用 Redis 作为 MySQL 的前置缓存，可以非常有效地提升系统处理高并发的能力，降低请求响应时间。绝大多数情况下，使用 Cache Aside 模式来更新缓存都是最佳的选择，相比 Read/Write Through 模式更简单，还能大幅降低脏数据的可能性。特别注意的是，大量缓存穿透引起雪崩的问题，你需要针对具体业务场景来选择合适解决方案。

reids 遇到大 KV ¶

跳表 ¶

跳表可以简单理解就是给链表加索引，当结点数量多的时候，这种添加索引的方式，会使查询效率提高的非常明显。

跳表这个动态数据结构，不仅支持查找操作，还支持动态的插入、删除操作，而且插入、删除操作的时间复杂度也是 \(O(logn)\)。 对于单纯的单链表，需要遍历每个结点来找到插入的位置。但是对于跳表来说，因为其查找某个结点的时间复杂度是 \(O(logn)\)，所以这里查找某个数据应该插入的位置，时间复杂度也是 ○(㏒n)。

Redis 选中跳表而不使用 B+ 树 ¶

redis 使用跳表不用 B + 数的原因是：redis 是内存数据库，而 B + 树纯粹是为了 mysql 这种 IO 数据库准备的。B + 树的每个节点的数量都是一个 mysql 分区页的大小

Redis 中的有序集合是通过跳表来实现的，严格点讲，还用到了散列表（关于散列表），如果查看 Redis 开发手册，会发现 Redis 中的有序集合支持的核心操作主要有下面这几个： 插入一个数据 删除一个数据 查找一个数据 按照区间查找数据（比如查找在 [100,356] 之间的数据） 迭代输出有序序列 其中，插入、查找、删除以及迭代输出有序序列这几个操作，红黑树也能完成，时间复杂度和跳表是一样的，但是，按照区间来查找数据这个操作，红黑树的效率没有跳表高。

对于按照区间查找数据这个操作，跳表可以做到 \(O(logn)\) 的时间复杂度定位区间的起点，然后在原始链表中顺序往后遍历就可以了。这样做非常高效。

当然，还有其他原因，比如，跳表代码更容易实现，可读性好不易出错。跳表更加灵活，可以通过改变索引构建策略，有效平衡执行效率和内存消耗。

总共 8 个班级、输出每个班级男生的数学前三名和女生的语文前三名 ¶

SELECT g1.stu_id, g1.class_id, g1.gender, g1.math_score   
FROM grade g1   
WHERE g1.gender = 'm'   
AND EXISTS (  
   SELECT count(*)   
   FROM grade g2   
   WHERE g2.gender = 'm'   
    AND g2.math_score >= g1.math_score   
    AND g2.class_id = g1.class_id   
   GROUP BY  
    g2.class_id   
   HAVING count(*) <= 3   
)  
ORDER BY class_id, math_score DESC