面试之数据库


原理和SQL

事务及其基本特性

事务就是用户定义的一系列数据库操作,这些操作可以视为一个完整的逻辑处理工作单元,要么全部执行,要么全部不执行,是不可分割的工作单元。事务指的是满足 ACID 特性的一组操作,可以通过 Commit 提交一个事务,也可以使用 Rollback 进行回滚。

image

  • 事务基本特性 ACID
    • A原子性(atomicity) 指的是一个事务中的操作要么全部成功,要么全部失败。
    • C一致性(consistency) 指的是数据库总是从一个一致性的状态转换到另外一个一致性的状态。比如 A 转账给 B 100 块钱,假设中间 sql 执行过程中系统崩溃 A 也不会损失 100 块,因为事务没有提交,修改也就不会保存到数据库。事务执行前后的状态(数据)保持一致
    • I隔离性(isolation) 指的是一个事务的修改在最终提交前,对其他事务是不可见的。一个事务的执行不可以被其他事务干扰。
    • D持久性(durability) 指的是一旦事务提交,所做的修改就会永久保存到数据库中。

数据库中并发一致性问题

在并发环境下,事务的隔离性很难保证,因此会出现很多并发一致性问题。

  • 丢失修改

T1 和 T2 两个事务都对一个数据进行修改,T1 先修改,T2 随后修改,T2 的修改覆盖了 T1 的修改。

image

  • 读脏数据

T1 修改一个数据,T2 随后读取这个数据。如果 T1 撤销了这次修改,那么 T2 读取的数据是脏数据。

image

  • 不可重复读

T2 读取一个数据,T1 对该数据做了修改。如果 T2 再次读取这个数据,此时读取的结果和第一次读取的结果不同。

image

  • 幻读

T1 读取某个范围的数据,T2 在这个范围内插入新的数据,T1 再次读取这个范围的数据,此时读取的结果和和第一次读取的结果不同。

image

事务的隔离等级

  • 读未提交(READ UNCOMMITTED) 事务中的修改,即使没有提交,对其它事务也是可见的。
  • 读已提交(READ COMMITTED) 一个事务只能读取已经提交的事务所做的修改。换句话说,一个事务所做的修改在提交之前对其它事务是不可见的。
  • 可重复读(REPEATABLE READ) 保证在同一个事务中多次读取同样数据的结果是一样的。
  • 可串行化(SERIALIZABLE) 提供严格的事务隔离,它要求事务序列化执行,事务只能一个接着一个地执行,但不能并发执行,如果仅仅通过“行级锁”是无法实现序列化的,必须通过其他机制保证新插入的数据不会被执行查询操作的事务访问到。
隔离级别 脏读 不可重复读 幻读
读未提交
读已提交 ×
可重复读 × ×
可串行化 × × ×

ACID 靠什么保证

  • A原子性(atomicity)undo log 日志保证,它记录了需要回滚的日志信息,事务回滚时撤销已经执行成功的 sql
  • C一致性(consistency) 一般由代码层面来保证
  • I隔离性(isolation)MVCC 来保证
  • D持久性(durability)内存+ redo log 来保证,mysql 修改数据同时在内存和 redo log 记录这次操作,事务提交的时候通过redo log 刷盘,宕机的时候可以从 redo log 恢复

SQL 优化的实践经验

  1. 对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

  2. 应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:

    1
    select id from t where num is null

    最好不要给数据库留 NULL,尽可能的使用 NOT NULL填充数据库.

    备注、描述、评论之类的可以设置为 NULL,其他的,最好不要使用NULL。

    不要以为 NULL 不需要空间,比如:char(100) 型,在字段建立时,空间就固定了, 不管是否插入值(NULL也包含在内),都是占用 100个字符的空间的,如果是varchar这样的变长字段, null 不占用空间。

    可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:

    1
    select id from t where num = 0
  3. 应尽量避免在 where 子句中使用 != 或 < > 操作符,否则将引擎放弃使用索引而进行全表扫描。

  4. 应尽量避免在 where 子句中使用 or 来连接条件,如果一个字段有索引,一个字段没有索引,将导致引擎放弃使用索引而进行全表扫描,如:

    1
    select id from t where num=10 or Name = 'admin'

    可以这样查询:

    1
    2
    3
    select id from t where num = 10
    union all
    select id from t where Name = 'admin'
  5. in 和 not in 也要慎用,否则会导致全表扫描,如:

    1
    select id from t where num in(1,2,3)

    对于连续的数值,能用 between 就不要用 in 了:

    1
    select id from t where num between 1 and 3

    很多时候用 exists 代替 in 是一个好的选择:

    1
    select num from a where num in(select num from b)  

    用下面的语句替换:

    1
    select num from a where exists(select 1 from b where num=a.num)
  6. 下面的查询也将导致全表扫描:

    1
    select id from t where name like ‘%abc%’

    若要提高效率,可以考虑全文检索。

  7. 如果在 where 子句中使用参数,也会导致全表扫描。因为 SQL 只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然 而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:

    1
    select id from t where num = @num

    可以改为强制查询使用索引:

    1
    select id from t with(index(索引名)) where num = @num

    应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:

    1
    select id from t where num/2 = 100

    应改为:

    1
    select id from t where num = 100*2
  8. 应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:

    1
    2
    select id from t where substring(name,1,3) = ’abc’       -–name以abc开头的id
    select id from t where datediff(day,createdate,’2005-11-30′) = 0 -–‘2005-11-30’ --生成的id

    应改为:

    1
    2
    select id from t where name like 'abc%'
    select id from t where createdate >= '2005-11-30' and createdate < '2005-12-1'
  9. 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

  10. 在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。

  11. 不要写一些没有意义的查询,如需要生成一个空表结构:

    1
    select col1,col2 into #t from t where 1=0

    这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样:

    1
    create table #t(…)
  12. Update 语句,如果只更改1、2个字段,不要 Update 全部字段,否则频繁调用会引起明显的性能消耗,同时带来大量日志。

  13. 对于多张大数据量(这里几百条就算大了)的表 JOIN,要先分页再 JOIN,否则逻辑读会很高,性能很差。

  14. select count(*) from table;这样不带任何条件的 count 会引起全表扫描,并且没有任何业务意义,是一定要杜绝的。

  15. 索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过 6 个,若太多则应考虑一些不常使用到的列上建的索引是否有 必要。

  16. 应尽可能的避免更新 clustered 索引数据列,因为 clustered 索引数据列的顺序就是表记录的物理存储顺序,一旦该列值改变将导致整个表记录的顺序的调整,会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列,那么需要考虑是否应将该索引建为 clustered 索引。

  17. 尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连 接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。

  18. 尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。

  19. 任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。

  20. 尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引)。

  21. 避免频繁创建和删除临时表,以减少系统表资源的消耗。临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件, 最好使用导出表。

  22. 在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先 create table,然后 insert。

  23. 如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。

  24. 尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。

  25. 使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。

  26. 与临时表一样,游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法,尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时 间允许,基于游标的方法和基于集的方法都可以尝试一下,看哪一种方法的效果更好。

  27. 在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

  28. 尽量避免大事务操作,提高系统并发能力。

  29. 尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。

Buffer Pool、undo log、redo log、bin log

由于磁盘随机读写的效率很低,MySQL 为了提供性能,读写不是直接操作的磁盘文件,而是在内存中开辟了一个叫做 buffer pool 的缓存区域,更新数据的时候会优先更新到 buffer pool,之后再由 I/O 线程写入磁盘。同时为了 InnoDB 为了保证宕机不丢失 buffer pool 中的数据,实现 crash safe,还引入了一个叫做redo log 的日志模块。另外还有处于 MySQL Server 层的用于备份磁盘数据的 bin log,用于事务回滚和 MVCC 的 undo log 等。

  • Buffer Pool 是 MySQL 的一个非常重要的组件,因为针对数据库的增删改操作都是在 Buffer Pool 中完成的
  • Undo log 记录的是数据操作前的样子
  • redo log 记录的是数据被操作后的样子(redo log 是 Innodb 存储引擎特有)
  • bin log 记录的是整个操作记录(这个对于主从复制具有非常重要的意义)

从准备更新一条数据到事务的提交的流程描述?

img

  • 首先执行器根据 MySQL 的执行计划来查询数据,先是从缓存池中查询数据,如果没有就会去数据库中查询,如果查询到了就将其放到缓存池中
  • 在数据从缓存到缓存池的同时,会写入 undo log 日志文件
  • 更新的动作是在 Buffer Pool 中完成的,同时会将更新后的数据添加到 redo log buffer 中
  • 完成以后就可以提交事务,在提交的同时会做以下三件事
    • 将 redo log buffer中的数据刷入到 redo log 文件中
    • 将本次操作记录写入到 bin log 文件中
    • 将 bin log 文件名字和更新内容在 bin log 中的位置记录到 redo log 中,同时在 redo log 最后添加 commit 标记

MySQL

myisam 和 innodb

myisam 引擎是5.1版本之前的默认引擎,支持全文检索、压缩、空间函数等,但是不支持事务和行级锁,所以一般用于有大量查询少量插入的场景来使用,而且myisam 不支持外键,并且索引和数据是分开存储的。

innodb 是基于 B+Tree 索引建立的,和 myisam 相反它支持事务、外键,并且通过 MVCC 来支持高并发,索引和数据存储在一起。

MySQL 的索引

索引在什么层面?

首先,索引是在存储引擎层实现的,而不是在服务器层实现的,所以不同存储引擎具有不同的索引类型和实现。

有哪些?

  • B+Tree 索引
    • 是大多数 MySQL 存储引擎的默认索引类型。
  • 哈希索引
    • 哈希索引能以 O(1) 时间进行查找,但是失去了有序性;
    • InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”,当某个索引值被使用的非常频繁时,会在 B+Tree 索引之上再创建一个哈希索引,这样就让 B+Tree 索引具有哈希索引的一些优点,比如快速的哈希查找。
  • 全文索引
    • MyISAM 存储引擎支持全文索引,用于查找文本中的关键词,而不是直接比较是否相等。查找条件使用 MATCH AGAINST,而不是普通的 WHERE。
    • 全文索引一般使用倒排索引实现,它记录着关键词到其所在文档的映射。
    • InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。
  • 空间数据索引
    • MyISAM 存储引擎支持空间数据索引(R-Tree),可以用于地理数据存储。空间数据索引会从所有维度来索引数据,可以有效地使用任意维度来进行组合查询。

索引实现 B+树

  • 什么是 B+Tree?

B+Tree 是基于 B Tree 和叶子节点顺序访问指针进行实现,它具有 B Tree 的平衡性,并且通过顺序访问指针来提高区间查询的性能。在 B+Tree 中,一个节点中的 key 从左到右非递减排列,如果某个指针的左右相邻 key 分别是 keyi 和 keyi+1,且不为 null,则该指针指向节点的所有 key 大于等于 keyi 且小于等于 keyi+1。

img

  • 为什么是 B+Tree?
    • 为了减少磁盘读取次数,决定了树的高度不能高,所以必须是先 B-Tree;
    • 以页为单位读取使得三次 I/O 就能完全载入一个节点,且相邻的节点也能够被预先载入;所以数据放在叶子节点,本质上是一个 Page 页;
    • 为了支持范围查询以及关联关系, 页中数据需要有序,且页的尾部节点指向下个页的头部;
  • B+树 索引可分为聚簇索引和非聚簇索引?
  1. 主索引就是聚簇索引(也称聚集索引,clustered index)
  2. 辅助索引(有时也称非聚簇索引或二级索引,secondary index,non-clustered index)。

img

如上图,主键索引的叶子节点保存的是真正的数据。而辅助索引叶子节点的数据区保存的是主键索引关键字的值

假如要查询 name = C 的数据,其搜索过程如下:a) 先在辅助索引中通过 C 查询最后找到主键 id = 9; b) 在主键索引中搜索 id 为 9 的数据,最终在主键索引的叶子节点中获取到真正的数据。所以通过辅助索引进行检索,需要检索两次索引。

之所以这样设计,一个原因就是:如果和 MyISAM 一样在主键索引和辅助索引的叶子节点中都存放数据行指针,一旦数据发生迁移,则需要去重新组织维护所有的索引。

覆盖索引和回表

覆盖索引指的是在一次查询中,如果一个索引包含或者说覆盖所有需要查询的字段的值,我们就称之为覆盖索引,而不再需要回表查询。

而要确定一个查询是否是覆盖索引,我们只需要 explain sql 语句看 Extra 的结果是否是“Using index”即可。

比如:

1
2
3
4
5
6
7
8
9
10
11
explain select * from user where age=1; // 查询的name无法从索引数据获取
explain select id, age from user where age=1; //可以直接从索引获取

select * from s1 where id=123;
sql命中了索引,但未覆盖索引。
利用 id=123 到索引的数据结构中定位到该 id 在硬盘中的位置,或者说再数据表中的位置。
但是我们 select 的字段为*,除了 id 以外还需要其他字段,这就意味着,我们通过索引结构取到 id 还不够,
还需要利用该 id 再去找到该 id 所在行的其他字段值,这是需要时间的,很明显,如果我们只 select id,
就减去了这份苦恼,如下
select id from s1 where id=123;
这条就是覆盖索引了,命中索引,且从索引的数据结构直接就取到了id在硬盘的地址,速度很快

回表:在 InnoDB 存储引擎下,二级索引查询到的索引列,如果需要查找所有列的数据,则需要到主键索引里面去取出数据。这个过程就称为回表。因为行的数据都是存在主键B+tree的叶子节点里面,二级索引的B+树叶子节点都是存放的(索引列,主键)。

MVCC

  • 什么是MVCC?

MVCC,全称 Multi-Version Concurrency Control,即多版本并发控制。MVCC是一种并发控制的方法,一般在数据库管理系统中,实现对数据库的并发访问,在编程语言中实现事务内存。

在 Mysql 的 InnoDB 引擎中就是指在已提交读(READ COMMITTD)和可重复读(REPEATABLE READ)这两种隔离级别下的事务对于 SELECT 操作会访问版本链中的记录的过程。

这就使得别的事务可以修改这条记录,反正每次修改都会在版本链中记录。SELECT 可以去版本链中拿记录,这就实现了读-写,写-读的并发执行,提升了系统的性能。

  • MySQL 的 InnoDB 引擎实现 MVCC 的 3 个基础点
  1. 隐式字段

img

如上图,DB_ROW_ID 是数据库默认为该行记录生成的唯一隐式主键;DB_TRX_ID 是当前操作该记录的事务 ID; 而 DB_ROLL_PTR 是一个回滚指针,用于配合undo日志,指向上一个旧版本;delete flag 没有展示出来。

  1. undo log

img

从上面,我们就可以看出,不同事务或者相同事务的对同一记录的修改,会导致该记录的 undo log 成为一条记录版本线性表,即链表,undo log 的链首就是最新的旧记录,链尾就是最早的旧记录

  1. ReadView

已提交读和可重复读的区别就在于它们生成 ReadView 的策略不同。

ReadView 中主要就是有个列表来存储我们系统中当前活跃着的读写事务,也就是begin了还未提交的事务。通过这个列表来判断记录的某个版本是否对当前事务可见。假设当前列表里的事务 id 为[80,100]。

a) 如果你要访问的记录版本的事务 id 为 50,比当前列表最小的 id 80 小,那说明这个事务在之前就提交了,所以对当前活动的事务来说是可访问的。

b) 如果你要访问的记录版本的事务 id 为 90,发现此事务在列表id最大值和最小值之间,那就再判断一下是否在列表内,如果在那就说明此事务还未提交,所以版本不能被访问。如果不在那说明事务已经提交,所以版本可以被访问。

c) 如果你要访问的记录版本的事务 id 为 110,那比事务列表最大 id 100 都大,那说明这个版本是在 ReadView 生成之后才发生的,所以不能被访问。

这些记录都是去 undo log 链里面找的,先找最近记录,如果最近这一条记录事务 id 不符合条件,不可见的话,再去找上一个版本再比较当前事务的 id 和这个版本事务 id 看能不能访问,以此类推直到返回可见的版本或者结束。

  • 举个例子 ,在已提交读隔离级别下:

比如此时有一个事务 id 为 100 的事务,修改了 name,使得的 name 等于小明 2,但是事务还没提交。则此时的版本链是

img

那此时另一个事务发起了select 语句要查询 id 为1的记录,那此时生成的 ReadView 列表只有[100]。那就去版本链去找了,首先肯定找最近的一条,发现 trx_id 是 100,也就是 name 为小明 2 的那条记录,发现在列表内,所以不能访问。

这时候就通过指针继续找下一条,name 为小明 1 的记录,发现 trx_id 是 60,小于列表中的最小 id,所以可以访问,直接访问结果为小明 1。

那这时候我们把事务 id 为 100 的事务提交了,并且新建了一个事务 id 为 110 也修改 id 为 1 的记录,并且不提交事务

img

这时候版本链就是

img

这时候之前那个 select 事务又执行了一次查询,要查询 id 为 1 的记录。

已提交读隔离级别下的事务在每次查询的开始都会生成一个独立的 ReadView,而可重复读隔离级别则在第一次读的时候生成一个 ReadView,之后的读都复用之前的 ReadView

  1. 如果你是已提交读隔离级别,这时候你会重新一个 ReadView,那你的活动事务列表中的值就变了,变成了[110]。按照上的说法,你去版本链通过 trx_id 对比查找到合适的结果就是小明 2。
  2. 如果你是可重复读隔离级别,这时候你的 ReadView 还是第一次 select 时候生成的 ReadView,也就是列表的值还是[100]。所以 select 的结果是小明1。所以第二次 select 结果和第一次一样,所以叫可重复读!

这就是 Mysql 的 MVCC,通过版本链,实现多版本,可并发读-写,写-读。通过 ReadView 生成策略的不同实现不同的隔离级别。

MySQL 锁的类型

说两个维度

  • 共享锁(简称S锁)和排他锁(简称X锁)

    • 读锁是共享的,可以通过lock in share mode实现,这时候只能读不能写。
    • 写锁是排他的,它会阻塞其他的写锁和读锁。从颗粒度来区分,可以分为表锁和行锁两种。
  • 表锁和行锁

    • 表锁会锁定整张表并且阻塞其他用户对该表的所有读写操作,比如alter修改表结构的时候会锁表。

    • 行锁

      又可以分为乐观锁和悲观锁

      • 悲观锁可以通过for update实现
      • 乐观锁则通过版本号实现。

两个维度结合来看

  • 共享锁(行锁):Shared Locks
    • 读锁(s锁),多个事务对于同一数据可以共享访问,不能操作修改
    • 使用方法:
      • 加锁:SELECT * FROM table WHERE id=1 LOCK IN SHARE MODE
      • 释锁:COMMIT/ROLLBACK
  • 排他锁(行锁):Exclusive Locks
    • 写锁(X锁),互斥锁/独占锁,事务获取了一个数据的X锁,其他事务就不能再获取该行的读锁和写锁(S锁、X锁),只有获取了该排他锁的事务是可以对数据行进行读取和修改
    • 使用方法:
      • DELETE/ UPDATE/ INSERT – 加锁
      • SELECT * FROM table WHERE … FOR UPDATE – 加锁
      • COMMIT/ROLLBACK – 释锁
  • 意向共享锁(IS)
    • 一个数据行加共享锁前必须先取得该表的IS锁,意向共享锁之间是可以相互兼容的 意向排它锁(IX) 一个数据行加排他锁前必须先取得该表的IX锁,意向排它锁之间是可以相互兼容的 意向锁(IS、IX)是InnoDB引擎操作数据之前自动加的,不需要用户干预; 意义: 当事务操作需要锁表时,只需判断意向锁是否存在,存在时则可快速返回该表不能启用表锁
    • 意向共享锁(IS锁)(表锁):Intention Shared Locks
      • 表示事务准备给数据行加入共享锁,也就是说一个数据行加共享锁 前必须先取得该表的IS锁。
    • 意向排它锁(IX锁)(表锁):Intention Exclusive Locks
      • 表示事务准备给数据行加入排他锁,说明事务在一个数据行加排他 锁前必须先取得该表的IX锁。

大表优化

当MySQL单表记录数过⼤时,数据库的CRUD性能会明显下降,⼀些常⻅的优化措施如下:

限定数据的范围

务必禁⽌不带任何限制数据范围条件的查询语句。⽐如:我们当⽤户在查询订单历史的时候,我们可以控制在⼀个⽉的范围内;

读/写分离

经典的数据库拆分⽅案,主库负责写,从库负责读;

垂直分区

根据数据库⾥⾯数据表的相关性进⾏拆分。 例如,⽤户表中既有⽤户的登录信息⼜有⽤户的基本信息,可以将⽤户表拆分成两个单独的表,甚⾄放到单独的库做分库。

简单来说垂直拆分是指数据表列的拆分,把⼀张列⽐较多的表拆分为多张表。 如下图所示,这样来说⼤家应该就更容易理解了

image-20220908103814804

垂直拆分的优点: 可以使得列数据变⼩,在查询时减少读取的Block数,减少I/O次数。此外,垂直分区可以简化表的结构,易于维护。

垂直拆分的缺点: 主键会出现冗余,需要管理冗余列,并会引起Join操作,可以通过在应⽤层进⾏Join来解决。此外,垂直分区会让事务变得更加复杂;

水平分区

保持数据表结构不变,通过某种策略存储数据分⽚。这样每⼀⽚数据分散到不同的表或者库中,达到了分布式的⽬的。 ⽔平拆分可以⽀撑⾮常⼤的数据量。

⽔平拆分是指数据表⾏的拆分,表的⾏数超过200万⾏时,就会变慢,这时可以把⼀张的表的数据拆成多张表来存放。举个例⼦:我们可以将⽤户信息表拆分成多个⽤户信息表,这样就可以避免单⼀表数据量过⼤对性能造成影响。

image-20220908104021379

⽔平拆分可以⽀持⾮常⼤的数据量。需要注意的⼀点是:分表仅仅是解决了单⼀表数据过⼤的问题,但由于表的数据还是在同⼀台机器上,其实对于提升MySQL并发能⼒没有什么意义,所以⽔平拆分最好分库

⽔平拆分能够 ⽀持⾮常⼤的数据量存储,应⽤端改造也少,但 分⽚事务难以解决 ,跨节点Join性能较差,逻辑复杂。《Java⼯程师修炼之道》的作者推荐 尽量不要对数据进⾏分⽚,因为拆分会带来逻辑、部署、运维的各种复杂度 ,⼀般的数据表在优化得当的情况下⽀撑千万以下的数据量是没有太⼤问题的。如果实在要分⽚,尽量选择客户端分⽚架构,这样可以减少⼀次和中间件的⽹络I/O。

分库分表

首先分库分表分为垂直和水平两个方式,一般来说我们拆分的顺序是先垂直后水平。

  • 垂直分库

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

  • 垂直分表

垂直切分是将一张表按列切分成多个表,通常是按照列的关系密集程度进行切分,也可以利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中。

在数据库的层面使用垂直切分将按数据库中表的密集程度部署到不同的库中,例如将原来的电商数据库垂直切分成商品数据库、用户数据库等。

img

  • 水平分表

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

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

img

分表后的 ID 怎么保证唯一性

因为我们主键默认都是自增的,那么分表之后的主键在不同表就肯定会有冲突了。有几个办法考虑:

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

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

  • 可以做一个 mapping 表,比如这时候商家要查询订单列表怎么办呢?不带user_id查询的话你总不能扫全表吧?所以我们可以做一个映射关系表,保存商家和用户的关系,查询的时候先通过商家查询到用户列表,再通过user_id去查询。
  • 大宽表,一般而言,商户端对数据实时性要求并不是很高,比如查询订单列表,可以把订单表同步到离线(实时)数仓,再基于数仓去做成一张宽表,再基于其他如es提供查询服务。
  • 数据量不是很大的话,比如后台的一些查询之类的,也可以通过多线程扫表,然后再聚合结果的方式来做。或者异步的形式也是可以的。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
List<Callable<List<User>>> taskList = Lists.newArrayList();
for (int shardingIndex = 0; shardingIndex < 1024; shardingIndex++) {
taskList.add(() -> (userMapper.getProcessingAccountList(shardingIndex)));
}
List<ThirdAccountInfo> list = null;
try {
list = taskExecutor.executeTask(taskList);
} catch (Exception e) {
//do something
}

public class TaskExecutor {
public <T> List<T> executeTask(Collection<? extends Callable<T>> tasks) throws Exception {
List<T> result = Lists.newArrayList();
List<Future<T>> futures = ExecutorUtil.invokeAll(tasks);
for (Future<T> future : futures) {
result.add(future.get());
}
return result;
}
}

MySQL 主从复制

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

  • binlog 线程 : 负责将主服务器上的数据更改写入二进制日志中。
  • I/O 线程 : 负责从主服务器上读取二进制日志,并写入从服务器的中继日志中。
  • SQL 线程 : 负责读取中继日志并重放其中的 SQL 语句。

img

全同步复制

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

半同步复制

和全同步不同的是,半同步复制的逻辑是这样,从库写入日志成功后返回ACK确认给主库,主库收到至少一个从库的确认就认为写操作完成。

MySQL主从的延迟怎么解决

这个问题貌似真的是个无解的问题,只能是说自己来判断了,需要走主库的强制走主库查询。

MySQL读写分离方案

主服务器处理写操作以及实时性要求比较高的读操作,而从服务器处理读操作。

读写分离能提高性能的原因在于:

  • 主从服务器负责各自的读和写,极大程度缓解了锁的争用;
  • 从服务器可以使用 MyISAM,提升查询性能以及节约系统开销;
  • 增加冗余,提高可用性。

读写分离常用代理方式来实现,代理服务器接收应用层传来的读写请求,然后决定转发到哪个服务器。

img

池化设计思想

池化设计应该不是⼀个新名词。我们常⻅的如 java 线程池、jdbc 连接池、redis 连接池等就是这类设计的代表实现。这种设计会初始预设资源,解决的问题就是抵消每次获取资源的消耗,如创建线程的开销,获取远程连接的开销等。就好⽐你去⻝堂打饭,打饭的⼤妈会先把饭盛好⼏份放那⾥,你来了就直接拿着饭盒加菜即可,不⽤再临时⼜盛饭⼜打菜,效率就⾼了。除了初始化资源,池化设计还包括如下这些特征:池⼦的初始值、池⼦的活跃值、池⼦的最⼤值等,这些特征可以直接映射到java线程池和数据库连接池的成员属性中。这篇⽂章对池化设计思想介绍的还不错,直接复制过来,避免重复造轮⼦了。

数据库连接本质就是⼀个 socket 的连接。数据库服务端还要维护⼀些缓存和⽤户权限信息之类的 所以占⽤了⼀些内存。我们可以把数据库连接池是看做是维护的数据库连接的缓存,以便将来需要对数据库的请求时可以重⽤这些连接。为每个⽤户打开和维护数据库连接,尤其是对动态数据库驱动的⽹站应⽤程序的请求,既昂贵⼜浪费资源。在连接池中,创建连接后,将其放置在池中,并再次使⽤它,因此不必建⽴新的连接。如果使⽤了所有连接,则会建⽴⼀个新连接并将其添加到池中。 连接池还减少了⽤户必须等待建⽴与数据库的连接的时间。

Redis

mysql与redis的区别

类型上

从类型上来说,mysql 是关系型数据库,redis 是缓存数据库

作用上

mysql 用于持久化的存储数据到硬盘,功能强大,速度较慢,基于磁盘,读写速度没有Redis快,但是不受空间容量限制,性价比高

redis用于存储使用较为频繁的数据到缓存中,读取速度快,基于内存,读写速度快,也可做持久化,但是内存空间有限,当数据量超过内存空间时,需扩充内存,但内存价格贵

需求上

mysql 和 redis 因为需求的不同,一般都是配合使用。需要高性能的地方使用 Redis,不需要高性能的地方使用 MySQL。存储数据在MySQL和Redis之间做同步。

什么是Redis,为什么用Redis?

Redis 是一种支持 key-value 等多种数据结构的存储系统。可用于缓存,事件发布或订阅,高速队列等场景。支持网络,提供字符串,哈希,列表,队列,集合结构直接存取,基于内存,可持久化

  • 读写性能优异
    • Redis能读的速度是110000次/s,写的速度是81000次/s (测试条件见下一节)。
  • 数据类型丰富
    • Redis支持二进制案例的 Strings, Lists, Hashes, Sets 及 Ordered Sets 数据类型操作。
  • 原子性
    • Redis的所有操作都是原子性的,同时Redis还支持对几个操作全并后的原子性执行。
  • 丰富的特性
    • Redis支持 publish/subscribe, 通知, key 过期等特性。
  • 持久化
    • Redis支持RDB, AOF等持久化方式
  • 发布订阅
    • Redis支持发布/订阅模式
  • 分布式
    • Redis Cluster

为什么 Redis 是单线程的以及为什么这么快?

  • redis 完全基于内存,绝大部分请求是纯粹的内存操作,非常快速.
  • 数据结构简单,对数据操作也简单,redis 中的数据结构是专门进行设计的
  • 采用单线程模型,避免了不必要的上下文切换和竞争条件,也不存在多线程或者多线程切换而消耗CPU,不用考虑各种锁的问题,不存在加锁,释放锁的操作,没有因为可能出现死锁而导致性能消耗
  • 使用了多路 IO 复用模型,非阻塞IO
  • 使用底层模型不同,它们之间底层实现方式及与客户端之间的通信的应用协议不一样,Redis直接构建了自己的VM机制,因为一般的系统调用系统函数,会浪费一定的时间去移动和请求

Redis 一般有哪些使用场景?

可以结合自己的项目讲讲,比如

  • 热点数据的缓存

缓存是 Redis 最常见的应用场景,之所有这么使用,主要是因为 Redis 读写性能优异。而且逐渐有取代 memcached,成为首选服务端缓存的组件。而且,Redis内部是支持事务的,在使用时候能有效保证数据的一致性。

  • 限时业务的运用

redis 中可以使用 expire 命令设置一个键的生存时间,到时间后 redis 会删除它。利用这一特性可以运用在限时的优惠活动信息、手机验证码等业务场景。

  • 计数器相关问题

redis 由于 incrby 命令可以实现原子性的递增,所以可以运用于高并发的秒杀活动、分布式序列号的生成、具体业务还体现在比如限制一个手机号发多少条短信、一个接口一分钟限制多少请求、一个接口一天限制调用多少次等等。

  • 分布式锁

这个主要利用 redis 的 setnx 命令进行,setnx:”set if not exists”就是如果不存在则成功设置缓存同时返回1,否则返回0 ,这个特性在俞你奔远方的后台中有所运用,因为我们服务器是集群的,定时任务可能在两台机器上都会运行,所以在定时任务中,首先通过 setnx 设置一个 lock,如果成功设置则执行,如果没有成功设置,则表明该定时任务已执行。 当然结合具体业务,我们可以给这个 lock 加一个过期时间,比如说 30 分钟执行一次的定时任务,那么这个过期时间设置为小于 30 分钟的一个时间就可以,这个与定时任务的周期以及定时任务执行消耗时间相关。

在分布式锁的场景中,主要用在比如秒杀系统等。

Redis 有哪些数据类型?

  • 5种基础数据类型,分别是:String、List、Set、Zset、Hash。

img

结构类型 结构存储的值 结构的读写能力
String字符串 可以是字符串、整数或浮点数 对整个字符串或字符串的一部分进行操作;对整数或浮点数进行自增或自减操作;
List列表 一个链表,链表上的每个节点都包含一个字符串 对链表的两端进行push和pop操作,读取单个或多个元素;根据值查找或删除元素;
Set集合 包含字符串的无序集合 字符串的集合,包含基础的方法有看是否存在添加、获取、删除;还包含计算交集、并集、差集等
Hash散列 包含键值对的无序散列表 包含方法有添加、获取、删除单个元素
Zset有序集合 和散列一样,用于存储键值对 字符串成员与浮点数分数之间的有序映射;元素的排列顺序由分数的大小决定;包含方法有添加、获取、删除单个元素以及根据分值范围或成员来获取元素
  • 三种特殊的数据类型 分别是 HyperLogLogs(基数统计), Bitmaps (位图) 和 geospatial (地理位置)

Redis 的对象机制(redisObject)

比如说,集合类型就可以由字典和整数集合两种不同的数据结构实现,但是,当用户执行 ZADD 命令时,他/她应该不必关心集合使用的是什么编码,只要 Redis 能按照 ZADD 命令的指示,将新元素添加到集合就可以了。

这说明, 操作数据类型的命令除了要对键的类型进行检查之外, 还需要根据数据类型的不同编码进行多态处理

为了解决以上问题,Redis 构建了自己的类型系统,这个系统的主要功能包括:

  • redisObject 对象
  • 基于 redisObject 对象的类型检查
  • 基于 redisObject 对象的显式多态函数
  • 对 redisObject 进行分配、共享和销毁的机制
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*
* Redis 对象
*/
typedef struct redisObject {

// 类型
unsigned type:4;

// 编码方式
unsigned encoding:4;

// LRU - 24位, 记录最末一次访问时间(相对于lru_clock); 或者 LFU(最少使用的数据:8位频率,16位访问时间)
unsigned lru:LRU_BITS; // LRU_BITS: 24

// 引用计数
int refcount;

// 指向底层数据结构实例
void *ptr;

} robj;

下图对应上面的结构

img


文章作者: Yang Shiyu
版权声明: 本博客所有文章除特別声明外,均采用 CC BY 4.0 许可协议。转载请注明来源 Yang Shiyu !
  目录