数据库设计

RDBMS组成



存储（文件系统）：将数据持久化到磁盘中

程序实例（内存数据和磁盘数据的交互）：存储逻辑关系转化为物理关系

• 存储管理

• 缓存机制

• SQL 解析

• 日志管理

• 权限划分

• 容灾机制

• 索引管理

• 锁管理



执行一条 sql 的过程



1. 客户端连接 MySQL 服务器

2. 查询缓存

3. 解析器解析 SQL：词法解析，语法解析，语义分析，预处理器 -> 解析数

4. 查询优化器

5. 生成执行计划

6. 查询执行引擎调用存储引擎的 API 获取到数据

7. 返回查询结果：缓存结果，返回结果



数据库事务特性 (ACID)



• 原子性，atomic：最小的执行单元，不可再分，要么全部成功，要么全部失败，通过 undo log 来实现

• 一致性，consistency：不破坏数据库的约束，不破坏自定义的约束

• 隔离性，isolation：各个事务对表的操作相互独立，互不影响

• 持久性，durability：commit 后的数据保存到磁盘，不会因为服务重启儿丢失数据，通过 redo log 来实现



数据库事务隔离级别



• 读未提交，read uncommitted：可能出现脏读，未解决任何问题

• 读已提交，read committed：解决了脏读问题，可能出现不可重复读

• 可重复读，repeatable read：解决了不可重复读问题，可能出现幻读

• 串行化，serializable：解决了所有问题



脏读：同一个事务中两次读取，读取到另一个事务未提交的数据



不可重复读：同一个事务两次读取，两次读取间隔中有另一个事务提交了数据（update/delete），导致前后读取的数据不一致



幻读：同一个事务中两次读取，两次读取间隔中有另一个事务插入的数据（insert），导致前后读取的数据不一致



事务并发访问引起的问题以及如何避免



• 更新丢失--MySQL 所有的事务隔离级别在数据库层面上均可避免

• 脏读--在 read-commited （Oracle 默认隔离级别）隔离级别以上可避免

• 不可重复读(侧重更新)--在repeatable-read（MySQL默认隔离级别）隔离级别以上可避免

• 幻读(新增/删除)--在 serializable 隔离级别可避免



索引



数据量大的时候避免全表扫描，提高查询速度



主键、唯一键及普通键等能成为索引



在建表时需要考虑所有可能的高频查询，另一方面，忌讳过度地“为未来设计 ”，也就是加一堆可能根本不常用的索引，反而增加了写数据时候的成本和负担。



索引数据结构



• 生成索引，建立二叉查找树进行二分查找

• 生成索引，建立 B-Tree 结构进行查找

• 生成索引，建立 B+-Tree 结构进行查找：非叶子节点不存储数据，叶子节点有指针。磁盘读写代价更低；查询效率更加稳定；更有利于数据库的扫描

• 生成索引，建立 Hash 结构进行查找：不能使用范围查询；无法被用来避免数据的排序操作；不能利用部分索引键查询；不能避免表扫描；遇到大量 Hash 值相等的情况性能不一定比 B+-Tree 高



建立索引的原则： 最左匹配原则 > 离散性高原则 > 最少空间原则



表中有 name 索引，where name like 'abc%' 可以用到索引吗？ 答：不一定，在查询优化阶段会进到数据抽样环节，但是数据抽样的环节判定该列的数据离散性不高也有可能导致不走索引而进行全表扫描。



密集索引和稀疏索引



密集索引文件中的每个搜索码值都对应一个索引值



稀疏索引文件只为索引码的某些值建立索引项



InnoDB:



• 若一个主键被定义，该主键则作为密集索引

• 若没有主键被定义，该表的第一个唯一非空索引则作为密集索引

• 若不满足以上条件，InnoDB 内部会生成一个隐藏主键（密集索引）

• 非主键索引存储相关键位和其对应的主键值，包含两次查找



InnoDB 索引和数据存放在一起的，MyISAM 索引和数据是分开存放的



如何定位并优化慢查询 sql



根据慢日志定位慢查询 sql

set global slow_query_log = on;
set global long_query_time = 1;
查看 slow_query_file



使用 explain 等工具分析 sql

type 字段：system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>all

extra 字段：出现 using filesort/ using temporary 时，完全不可能走索引，需要优化 sql

• using filesort: 表示MySQL 会对结果使用一个外部索引排序，而不是从表里按索引次序读到内容。可能在内存或者磁盘上进行排序。MySQL 中无法利用索引完成的排序操作称为“文件排序”

• using temporary: 表示 MySQL 在对查询结果排序时使用临时表。常见于 order by 和 group by

修改 sql 或者尽量让 sql 走索引



锁



数据库锁分类



• 按锁粒度划分，可分为表级锁、行级锁、页级锁

• 按锁级别划分，可分为共享排他锁

• 按加锁方式划分，可分为自动锁、显式锁

• 按操作划分，可分为 DML 锁、DDL 锁

• 按使用方式划分，可分为乐观锁、悲观锁



MyISAM 默认用的是表级锁，不支持行级锁。适合场景：



• 频繁执行全表 count 语句

• 对数据进行增删改的频率不高，查询非常频繁

• 没有事务



InnoDB 默认用的是行级锁，也支持表级锁；走索引时用的时行级锁，不走索引用的是表级锁。适合场景：



• 数据增删改查都非常频繁

• 可靠性要求比较高，要求支持事务



对主键索引或则唯一索引会用 Gap 锁吗？



• 如果 where 条件全部命中，则不会用 Gap 锁，只会加记录锁

• 如果 where 条件部分命中或者全不命中，则会加 Gap 锁



Gap 锁在走非唯一索引或不走索引的当前读时出现



读写分离



读写分离的基本原理是将数据库读写操作分散到不同的节点上，适用单机并发无法支撑并且读的请求更多的情形。



读写分离的基本实现是：



• 数据库服务器搭建主从集群，一主一从、一主多从都可以。

• 数据库主机负责读写操作，从机只负责读操作。

• 数据库主机通过复制将数据同步到从机，每台数据库服务器都存储了所有的业务数据。

• 业务服务器将写操作发给数据库主机，将读操作发给数据库从机。



复制延迟



如果业务服务器将数据写入到数据库主服务器后立刻（1 秒内）进行读取，此时读操作访问的是从机，主机还没有将数据复制过来，到从机读取数据是读不到最新数据的，业务上就可能出现问题。



解决办法：



• 写操作后的读操作指定发给数据库主服务器

• 读从机失败后再读一次主机

• 关键业务读写操作全部指向主机，非关键业务采用读写分离



分配机制



程序代码封装： 在代码中抽象一个数据访问层，实现读写操作分离和数据库服务器连接的管理。



中间件封装 ： 独立一套系统出来，实现读写操作分离和数据库服务器连接的管理。



分库分表



适用于写请求量大而造成的性能和可用性方面的问题的情形。



业务分库



业务分库指的是按照业务模块将数据分散到不同的数据库服务器。



问题



• join 操作问题 ：业务分库后，原本在同一个数据库中的表分散到不同数据库中，导致无法使用 SQL 的 join 查询。

• 事务问题： 原本在同一个数据库中不同的表可以在同一个事务中修改，业务分库后，表分散到不同的数据库中，无法通过事务统一修改。

• 成本问题： 业务分库同时也带来了成本的代价，本来 1 台服务器搞定的事情，现在要 3 台，如果考虑备份，那就是 2 台变成了 6 台。



分表



单表进行切分后，是否要将切分后的多个表分散在不同的数据库服务器中，可以根据实际的切分效果来确定，并不强制要求单表切分为多表后一定要分散到不同数据库中。原因在于单表切分为多表后，新的表即使在同一个数据库服务器中，也可能带来可观的性能提升，如果性能能够满足业务要求，是可以不拆分到多台数据库服务器的，毕竟我们在上面业务分库的内容看到业务分库也会引入很多复杂性的问题；如果单表拆分为多表后，单台服务器依然无法满足性能要求，那就不得不再次进行业务分库的设计了。



垂直分表



表记录数相同但包含不同的列，适合将表中某些不常用且占了大量空间的列拆分出去。垂直分表引入的复杂性主要体现在表操作的数量要增加。



水平分表



表的列相同但包含不同的行数据，看表的访问性能， 适合表行数特别大的表。



路由： 水平分表后，某条数据具体属于哪个切分后的子表，需要增加路由算法进行计算，这个算法会引入一定的复杂性。

常见路由算法：

• 范围路由：选取有序的数据列（例如，整形、时间戳等）作为路由的条件，不同分段分散到不同的数据库表中。

• Hash 路由：选取某个列（或者某几个列组合也可以）的值进行 Hash 运算，然后根据 Hash 结果分散到不同的数据库表中。

• 配置路由：配置路由就是路由表，用一张独立的表来记录路由信息。



水平分表后，数据分散在多个表中，如果需要与其他表进行 join 查询，需要在业务代码或者数据库中间件中进行多次 join 查询，然后将结果合并。



水平分表后，虽然物理上数据分散到多个表中，但某些业务逻辑上还是会将这些表当作一个表来处理。



水平分表后，数据分散到多个子表中，排序操作无法在数据库中完成，只能由业务代码或者数据库中间件分别查询每个子表中的数据，然后汇总进行排序。



数据库调优维度



数据库调优的目的就是要让数据库运行得更快，也就是说响应的时间更快，吞吐量更大。



1. 选择合适的 DBMS：SQL/NoSQL，存储引擎

2. 优化表设计：表结构，表字段类型

3. 优化逻辑查询：改变 SQL 语句的内容让 SQL 执行效率更高效，采用的方式是对 SQL 语句进行等价变换，对查询进行重写。

4. 优化物理查询：高效地建立索引，并通过这些索引来做各种优化

5. 使用 Redis 或 Memcached 作为缓存

6. 库级优化：读写分离，分库分表