数据库知识点梳理

序

数据库设计范式

第一范式：(确保每列保持原子性)所有字段值都是不可分解的原子值。

第二范式：(确保表中的每列都和主键相关)在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

第三范式：(确保每列都和主键列直接相关,而不是间接相关) 数据表中的每一列数据都和主键直接相关，而不能间接相关。

第四范式:要求把同一表内的多对多关系删除。

第五范式:从最终结构重新建立原始结构。

关系型数据库 & 非关系型数据库

关系型数据库的优势：

  1. 复杂查询：可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询。
  2. 事务支持：使得对于安全性能很高的数据访问要求得以实现。

关系型数据库的性能瓶颈：

  1. 在数据库表非常大的时候，不容易恢复；
  2. 扩展性差，需要复杂的技术实现。
  3. 大数据量下IO压力大；
  4. 表结构更改困难。

非关系型数据库的优势：

  1. 性能：NOSQL是基于键值对的，可以想象成表中的主键和值的对应关系，而且不需要经过SQL层的解析，所以性能非常高。
  2. 可扩展性：同样也是因为基于键值对，数据之间没有耦合性，所以非常容易水平扩展。

其他：

  1. 对于这两类数据库，对方的优势就是自己的弱势，反之亦然；
  2. NOSQL数据库慢慢开始具备SQL数据库的一些复杂查询功能，比如MongoDB；
  3. 对于事务的支持也可以用一些系统级的原子操作来实现例如乐观锁之类的方法来曲线救国，比如Redis set nx。

什么时候会选择用nosql呢？

  1. 数据库表schema经常变化
    比如在线商城，维护产品的属性经常要增加字段，这就意味着ORMapping层的代码和配置要改，如果该表的数据量过百万，新增字段会带来额外开销（重建索引等）。NoSQL应用在这种场景，可以极大提升DB的可伸缩性，开发人员可以将更多的精力放在业务层。
  2. 数据库表字段是复杂数据类型
    对于复杂数据类型，比如SQL Sever提供了可扩展性的支持，像xml类型的字段。很多用过的同学应该知道，该字段不管是查询还是更改，效率非常一般。主要原因是是DB层对xml字段很难建高效索引，应用层又要做从字符流到dom的解析转换。NoSQL以json方式存储，提供了原生态的支持，在效率方便远远高于传统关系型数据库。
  3. 高并发数据库请求
  4. 海量数据的分布式存储

数据库查询

sql的执行顺序

1
2
3
4
5
6
7
8
9
10
11
12
13
 SELECT DISTINCT
     <select_list>
 FROM
     <left_table> <join_type> JOIN <right_table> ON <join_condition>
 WHERE
     <where_condition>
 GROUP BY
     <group_by_list>
 HAVING
     <having_condition>
 ORDER BY
     <order_by_condition>
 LIMIT <limit number>

多表查询

7-JOINS

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
 select *
 from A
 inner join B
 on A.Key = B.Key;
 
 select *
 from A
 left join B
 on A.Key = B.Key;
 
 select *
 from A
 right join B
 on A.Key = B.Key;
 
 select *
 from A
 left join B
 on A.Key = B.Key
 where B.Key is NULL;
 
 select *
 from A
 right join B
 on A.Key = B.Key
 where A.Key is NULL;
 
 select *
 from A
 full outer join B
 on A.Key = B.Key;
 
 select *
 from A
 full outer join B
 on A.Key = B.Key
 where A.Key is NULL or B.key is NULL;

索引

什么是索引

索引是一种能够快速获取数据的数据结构。

索引是干什么的

索引是提高数据查询效率的。

主要通过索引依赖的数据结构。

InnoDB用的是B+树。

为什么用B+树？

采用什么样的索引需要考虑两个方面：

  1. InnoDB 需要支持的场景和功能需要在特定查询上拥有较强的性能；（综合考虑CRUD、排序、关联性能）
  2. CPU 将磁盘上的数据加载到内存中需要花费大量的时间，要尽量减少IO操作次数；

  • Hash索引的优势在于：INSERT、DELETE、UPDATE操作的时间复杂度是O(1)的，而B+树的时间复杂度是O(logn)的。劣势在于：当有大量重复键值的情况下，会存在哈希碰撞的问题，降低查询效率；排序和范围查找要通过全表扫描，效率极低。

    ⭐️相较于Hash索引，B+树的优势有：

    • 利用索引可以完成排序；
    • 支持范围查询；
    • 支持最左匹配原则（联合索引）；

  • B树和B+树最大的两点区别是：

    • B树每个结点都存数据，B+树只在叶子结点存数据；
    • B树的叶子结点没有链表连接，而B+树的叶子结点构成了一个双向链表；

    这就导致两点差别：

    • 在查询单个记录的时候，B+树的查询效率比较稳定，但低于B树；
    • 涉及遍历操作的时候，B+树可以利用双向链表顺序查询（由树根自上而下在叶子结点层顺序遍历），而B树要通过树型查找，从磁盘读到内存会浪费大量时间（类似dfs树层上下相间跳来跳去）；

    在关系型数据库中经常用到JOIN操作进行顺序匹配，因此选择了B+树。

  • 红黑树基本都是存储在内存中才会使用的数据结构。在大规模数据数据存储的时候，显然不能将全部数据全部加载进内存，因此如果采用红黑树，就会造成频繁IO，效率低下。

B+树的叶子结点都存的什么？

  • 创建的是聚集索引的时候，叶子结点存的是整行数据；
  • 创建的是辅助索引的时候，叶子结点存的是主键的值；
  • 如果查询走的是覆盖索引的话，叶子节点同时存了主键以及对应的整行数据。

聚集索引和辅助索引有什么区别？

  • 由于主键唯一，聚集索引构建的B+树也是唯一的。经过树高次数的磁盘读取，可以直接获得整行的记录。
  • 辅助索引可以构造多个索引树，包含两种类型：多个单列索引，单个多列索引。
    • 单列索引：通过辅助索引先查询主键，再进行回表，通过聚集索引查询整行记录。（此时比聚集索引的查询速度要慢。）
    • 多列索引：这种情况下创建的是一种特殊的辅助索引，即覆盖索引（也属于联合索引）。这时候如果查询的是联合索引中列对应的记录，则不需要回表，直接在叶子结点查得。（此时和聚集索引的查询速度相同。）

最左匹配原则

最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。

最左匹配原则按照创建联合索引的顺序进行匹配，和查询条件的顺序无关。

如有索引(a, b, c, d)，查询条件c > 3 and b = 2 and a = 1 and d < 4与a = 1 and c > 3 and b = 2 and d < 4等顺序都是可以的，MySQL会自动优化为a = 1 and b = 2 and c > 3 and d < 4，依次命中a、b、c。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
table T, index (a,b,c)

-- 全值匹配
select * from T where a='' and b='' and c='';	-- Y
select * from T where c='' and b='' and a='';	-- Y

-- 匹配左前缀
select * from T where a='';	-- Y
select * from T where b='';	-- N

-- 匹配列前缀
select * from T where a like 'x%';	-- Y
select * from T where a like '%x';	-- N
select * from T where b like 'x%';	-- N

-- 匹配范围值
select * from T where a between '' and '';	-- Y
select * from T where b between '' and '';	-- N

-- 全值匹配 + 范围匹配
select * from T where a='' and b between '' and '';	-- Y
select * from T where b='' and c between '' and '';	-- N
select * from T where a between '' and '' and b='';	-- N

如何创建索引

如何判断创建的索引是否生效？

可以通过explain查看sql语句的执行计划，通过执行计划来分析索引使用情况。

索引失效的原因

范围查询 + 没符合最左匹配

  • 单表查询时：

    • 查询得到的结果集占数据总量很大比例，mysql会认为全表扫描会优于索引，则不走索引。

    • 在where条件后对索引字段加了函数转换或者运算逻辑（+、-、*、/、！、<>、%、like’%_’（%放在前面）、or、in (疑问、可能存在成本问题)、exist等）的处理，比如对时间戳字段进行日期格式化函数都会引起索引失效。

    • 查询时where条件后的字段类型要与表结构中该字段类型一致，

      例：select * from userInfo where user_type=2 ，user_type在表结构中时字符类型，查询时没用有单引号包含起来则不走索引。

  • 多表关联查询时：

    • 在表结构设计阶段主表与关联表之间的关联字段的数据类型、数据长度、字段的编码格式以及字段的排序规则需要保持一致。

  • 联合索引时：

    • 没满足最左匹配原则：

      select * from userInfo where user_id=’001’ and user_name=’小张’ and user_type=’1’；这个时候是走了索引的，但是 select * from userInfo where user_name=’小张’ and user_type=’1’；这时user_id没有在where条件内将不走索引。

唯一索引和普通索引的区别是什么？

创建唯一索引的字段里的数据必须是唯一的，普通索引的字段的数据可以是重复的。这导致唯一索引的查询速度快于普通索引，而插入的速度慢于普通索引。

如：可以对学号建立唯一索引，但对成绩只能建立普通索引。

  • 查询速度快的原因：
    • 普通索引在找到满足条件的第一条记录后，还需要判断下一条记录，直到第一个不满足条件的记录出现；
    • 唯一索引在找到满足条件的第一条记录后，直接返回，不用判断下一条记录了。
  • 插入速度慢的原因：
    • 在进行非聚簇索引的插入时，先判断插入的索引页是否在内存中。如果在，则直接插入；如果不在，则先放入Insert Buffer 中，然后再以一定频率和情况进行Insert Buffer和原数据页合并(merge)操作。这么做的优点是能将多个插入合并到一个操作中，就大大提高了非聚簇索引的插入性能。
    • InnoDB 从 1.0.x 版本开始引入了 Change Buffer，可以算是对 Insert Buffer 的升级。从这个版本开始，InnoDB 存储引擎可以对 insert、delete、update 都进行缓存。
    • 唯一索引为了保证唯一性，需要将数据页加载进内存才能判断是否违反唯一性约束。但是，既然数据页都加载到内存了，还不如直接更新内存中的数据页，没有必要再使用Change Buffer。
    • 唯一索引无法利用Change Buffer，普通索引可以利用Change Buffer，因此唯一索引插入速度慢。

事务

事务的基本特性

  1. 原子性（atomicity）：一系列的操作，要么都执行，要么都不执行；
  2. 隔离性（isolation）：每个读写事务的对象对其他事务的操作对象能相互分离，即该事务提交前对其他事务不可见；
  3. 持久性（durability）：一旦事务被提交，那么数据一定会被写入到数据库中并持久存储起来；
  4. 一致性（consistency）：数据库经由一个事务从一个状态转移到下一个状态；
  • 通过数据库锁的机制，保障事务的隔离性；
  • 通过 Redo Log（重做日志）来，保障事务的持久性；
  • 通过 Undo Log （撤销日志）来，保障事务的原子性；
  • 通过 Undo Log （撤销日志）来，保障事务的一致性；

Undo Log 如何保障事务的原子性的？

具体的方式为：在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方称为 Undo Log），然后进行数据的修改。如果出现了错误或者用户执行了 Rollback 语句，系统可以利用 Undo Log 中的备份将数据恢复到事务开始之前的状态。

Redo Log如何保障事务的持久性呢？

具体的方式为：Redo Log 记录的是新数据的备份（和 Undo Log 相反）。在事务提交前，只要将 Redo Log 持久化即可，不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，但是 Redo Log 已经持久化。系统可以根据 Redo Log 的内容，将所有数据恢复到崩溃之前的状态。

事务的隔离级别

  • 未提交读（read uncommitted）

    使用查询语句不会加锁，可能会读到未提交的行，会发生脏读（Dirty Read）。

  • 提交读（read committed）

    没有加行锁导致当其他事务修改某行数据时，当前事务多次读取该行时可能得到不同的结果。会发生不可重复读（Non-Repeatable Read）。

    采用Record Lock 解决了脏读问题，采用MVCC总是读取被锁定行的最新一份快照数据。

  • 可重复读（repeatable read）

    多次读取同一范围的数据会返回第一次查询的快照，不会返回不同的数据行，但是可能发生幻读（Phantom Read）。

    采用next-key lock 解决了脏读、不可重复读、幻读问题；采用MVCC，总是读取事务开始时的行数据版本。

  • 可串行化（serializable）

    InnoDB 隐式地将全部的查询语句加上共享锁，解决了幻读的问题，完全满足了事务的四大特性。
    因此也牺牲了并发性能。

幻读问题是如何解决的？

InnoDB默认的隔离级别是可重复读，通过Next-Key Lock解决了幻读问题。

Next-key 锁是记录锁（record lock）和间隙锁（gap lock）的组合，锁定包含记录本身的一个范围，当查询的索引含有唯一属性的时候，next-key lock降级为record lock。

当可重复读级进行范围查询时，另一个事务进行插入操作时要进行等待。

在不同的事务的隔离级别下，都对应什么数据库锁？

  • 在Read Uncommitted级别下，读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突
  • 在Read Committed级别下，读操作需要加共享锁，但是在语句执行完以后释放共享锁；（语句级锁）
  • 在Repeatable Read级别下，读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁。（事务级锁）
  • SERIALIZABLE 是限制性最强的隔离级别，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。

不可重复读和幻读的区别是什么？

  • 不可重复读指的是指当前读没有加行锁，导致其他事务修改某行数据的时候，当前事务多次读该行会得到不同的结果，区分点在于其他事务修改了数据；
  • 幻读是指首次快照读后，其他事务在某行附近插入了新字段，当前事务再插入字段时会发生错误，区分点在于其他事务新增了字段。

RC,RR级别下的InnoDB快照读有什么不同？

  • RR级别下，快照读生成Read View时，Read View会记录此时所有其他活动事务的快照，这些事务的修改对于当前事务都是不可见的，而早于Read View创建的事务所做的修改均是可见。
  • 在RC级别下的事务中，每次快照读都会新生成一个快照和Read View, 因此可以在RC级别下的事务中可以看到别的事务提交的更新。

redo log、undo log、binlog

binlog（Binary Log）

二进制日志文件就是常说的binlog。二进制日志记录了MySQL所有修改数据库的操作，然后以二进制的形式记录在日志文件中，其中还包括每条语句所执行的时间和所消耗的资源，以及相关的事务信息。

默认情况下，二进制日志功能是开启的，启动时可以重新配置--log-bin[=file_name]选项，修改二进制日志存放的目录和文件名称。

redo log

重做日志用来实现事务的持久性，即事务ACID中的D。它由两部分组成：一是内存中的重做日志缓冲（redo log buffer），其是易失的；二是重做日志文件（redo log file），它是持久的。

InnoDB是事务的存储引擎，它通过Force Log at Commit机制实现事务的持久性，即当事务提交（COMMIT）时，必须先将该事务的所有日志写入到重做日志文件进行持久化，待事务的COMMIT操作完成才算完成。这里的日志是指重做日志，在InnoDB存储引擎中，由两部分组成，即redo log和undo log。

redo log用来保证事务的持久性，undo log用来帮助事务回滚及MVCC的功能。redo log基本上都是顺序写的，在数据库运行时不需要对redo log的文件进行读取操作。而undo log是需要进行随机读写的。

undo log

undo log有两个作用：提供回滚和多个行版本控制(MVCC).主要分为两种

undo log的记录伴随着redo log，undo log会记录事务执行过程中相反的一条语句，事务提交后可立即清除。

MVCC

MySQL什么时候用的MVCC？

InnoDB默认的隔离级别是RR（REPEATABLE READ），RR解决脏读、不可重复读、幻读等问题，使用的是MVCC。

MVCC简介

MVCC全称Multi-Version Concurrency Control，即多版本的并发控制协议。它最大的优点是读不加锁（读采用快照读，而增删改用的是当前读，需要加锁），因此读写不冲突，并发性能好。InnoDB实现MVCC，多个版本的数据可以共存，主要基于以下技术及数据结构：

  1. 隐藏列：InnoDB中每行数据都有隐藏列，隐藏列中包含了本行数据的事务id、指向undo log的指针等。
  2. 基于undo log的版本链：每行数据的隐藏列中包含了指向undo log的指针，而每条undo log也会指向更早版本的undo log，从而形成一条版本链。
  3. ReadView：通过隐藏列和版本链，MySQL可以将数据恢复到指定版本。但是具体要恢复到哪个版本，则需要根据ReadView来确定。所谓ReadView，是指事务（记做事务A）在某一时刻给整个事务系统（trx_sys）打快照，之后再进行读操作时，会将读取到的数据中的事务id与trx_sys快照比较，从而判断数据对该ReadView是否可见，即对事务A是否可见。

MVCC一般是用来干什么的？

  • MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突（不能解决更新丢失问题），做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。

    不加锁地处理读写冲突，提高数据库并发性能。

  • 用MVCC和锁的组合解决并发读写冲突 （具体是怎么实现的？）

    • MVCC + 悲观锁 MVCC解决读写冲突，悲观锁解决写写冲突；
    • MVCC + 乐观锁 MVCC解决读写冲突，乐观锁解决写写冲突 这种组合的方式就可以最大程度的提高数据库并发性能，并解决读写冲突，和写写冲突导致的问题；

数据库的并发场景有三种：读-读：不存在任何问题，也不需要并发控制；读-写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读；写-写：有线程安全问题，可能会存在更新丢失问题，比如第一类更新丢失，第二类更新丢失

MVCC的实现原理

MVCC的实现原理主要是依赖记录中的隐式字段，回滚日志（undo log），读视图（read view）来实现的。

  • 隐式字段（仅前两个与MVCC有关）

    • DB_TRX_ID（事务ID）：6byte，记录创建这条记录/最后一次修改该记录的事务ID
    • DB_ROLL_PT（回滚指针）：7byte，指向这条记录的上一个版本（存储于rollback segment里）
    • DB_ROW_ID（隐含的自增ID（隐藏主键））：6byte，如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引

  • 回滚日志

    • insert undo log：代表事务在insert新记录时产生的undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃
    • update undo log：事务在进行update或delete时产生的undo log; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，==只有在快照读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除。==

    purge 从前面的分析可以看出，为了实现InnoDB的MVCC机制，更新或者删除操作都只是设置一下老记录的deleted_bit，并不真正将过时的记录删除。 为了节省磁盘空间，InnoDB有专门的purge线程来清理deleted_bit为true的记录。为了不影响MVCC的正常工作，purge线程自己也维护了一个read view（这个read view相当于系统中最老活跃事务的read view）;如果某个记录的deleted_bit为true，并且DB_TRX_ID相对于purge线程的read view可见，那么这条记录一定是可以被安全清除的。

  • 读视图

    • read view 主要是用来做可见性判断的。

      当我们某个事务执行快照读的时候，对该记录创建一个Read View读视图，把它比作条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo log里面的某个版本的数据。

    • read view 如何进行可见性判断的？/ 判断的条件是什么？/ 可见性算法是如何实现的？

      1. 如果记录的事物ID比当前活跃事务的ID都小，说明读的记录为最新版本，因此可见；
      2. 如果记录的事务ID比当前活跃的事务的ID都大，说明相对当前活跃事务，记录对应的事务还未提交，因此不可见；
      3. 如果记录的事务ID在活跃事务ID的范围中：如果活跃事务ID列表中包含记录的事务ID，说明该记录的事务还在活跃状态中，尚未提交，因此不可见；反之则可见。

MVCC实现原理

MVCC的增删改查是如何work的？

  • 增：在新增记录的隐式字段中，记录事务ID设为当前事务ID，回滚指针初始化为null；
  • 删：直接把该行的被删除版本号（记录的事务ID中的一个特殊的位）设置为当前的事务ID；
  • 改：1. 对当前记录加行锁；2. 拷贝当前记录的副本到回滚日志中，如果发现该行记录已经存在在回滚日志中，就头插法插入到该链表中，没有则新建一个链表作为该链表的头指针；3. 插入一个新记录，事务ID为当前事务ID，回滚指针指向undo log该记录的副本地址；4. 提交事务，释放锁。
  • 查：满足以下两个条件才会被返回：1. 记录的修改版本号要小于当前事务ID（确保了读取到的数据是当前事务开始前已经存在的数据，或者是自身事务改变过的数据）；2. 记录的删除版本号要么为空，要么大于当前事务ID（确保查询出来的数据行记录在事务开启之前没有被删除）。

数据库锁

数据库锁

乐观锁

乐观锁一般是指用户自己实现的一种锁机制，假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。乐观锁的实现方式一般包括使用版本号和时间戳。

悲观锁

悲观锁一般就是我们通常说的数据库锁机制，以下讨论都是基于悲观锁。 悲观锁主要表锁、行锁、页锁。在MyISAM中只用到表锁，不会有死锁的问题，锁的开销也很小，但是相应的并发能力很差。innodb实现了行级锁和表锁，锁的粒度变小了，并发能力变强，但是相应的锁的开销变大，很有可能出现死锁。同时inodb需要协调这两种锁，算法也变得复杂。InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。 表锁和行锁都分为共享锁和排他锁（独占锁），而更新锁是为了解决行锁升级（共享锁升级为独占锁）的死锁问题。 innodb中表锁和行锁一起用，所以为了提高效率才会有意向锁（意向共享锁和意向排他锁）。

意向锁

意向锁是用来增加表锁行锁读写效率的，属于表级锁。

场景：事务A拿到了表的行锁读，事务B想拿该表的表锁写。这时候B的写可能会影响到A的读。有了意向锁后，A想拿表的行锁读，先检测该表有没有表锁写，没有就开始读。当B想表锁写，发现有A有行锁读，会阻塞。

意向锁分为意向共享锁（IS）和意向排它锁（IX）。

共享锁

  1. 加锁与解锁：当一个事务执行select语句时，数据库系统会为这个事务分配一把共享锁，来锁定被查询的数据。在默认情况下，数据被读取后，数据库系统立即解除共享锁。例如，当一个事务执行查询“SELECT * FROM accounts”语句时，数据库系统首先锁定第一行，读取之后，解除对第一行的锁定，然后锁定第二行。这样，在一个事务读操作过程中，允许其他事务同时更新accounts表中未锁定的行。
  2. 兼容性：如果数据资源上放置了共享锁，还能再放置共享锁和更新锁。
  3. 并发性能：具有良好的并发性能，当数据被放置共享锁后，还可以再放置共享锁或更新锁。所以并发性能很好。

排他锁

  1. 加锁与解锁：当一个事务执行insert、update或delete语句时，数据库系统会自动对SQL语句操纵的数据资源使用独占锁。如果该数据资源已经有其他锁（任何锁）存在时，就无法对其再放置独占锁了。
  2. 兼容性：独占锁不能和其他锁兼容，如果数据资源上已经加了独占锁，就不能再放置其他的锁了。同样，如果数据资源上已经放置了其他锁，那么也就不能再放置独占锁了。
  3. 并发性能：最差。只允许一个事务访问锁定的数据，如果其他事务也需要访问该数据，就必须等待。

数据库锁的互斥性

更新锁

更新锁在的初始化阶段用来锁定可能要被修改的资源，这可以避免使用共享锁造成的死锁现象。例如，对于以下的update语句：

UPDATE accounts SET balance=900 WHERE id=1

更新操作需要分两步：读取accounts表中id为1的记录 –> 执行更新操作。

如果在第一步使用共享锁，再第二步把锁升级为独占锁，就可能出现死锁现象。例如：两个事务都获取了同一数据资源的共享锁，然后都要把锁升级为独占锁，但需要等待另一个事务解除共享锁才能升级为独占锁，这就造成了死锁。

更新锁有如下特征：

  1. 加锁与解锁：当一个事务执行update语句时，数据库系统会先为事务分配一把更新锁。当读取数据完毕，执行更新操作时，会把更新锁升级为独占锁。
  2. 兼容性：更新锁与共享锁是兼容的，也就是说，一个资源可以同时放置更新锁和共享锁，但是最多放置一把更新锁。这样，当多个事务更新相同的数据时，只有一个事务能获得更新锁，然后再把更新锁升级为独占锁，其他事务必须等到前一个事务结束后，才能获取得更新锁，这就避免了死锁。
  3. 并发性能：允许多个事务同时读锁定的资源，但不允许其他事务修改它。

查询优化

优化查询策略

  1. 使用索引

    索引可以提高查询的速度，但并不是使用带有索引的字段查询时索引都会起作用。

    索引不起作用的几种情况：

    1. 使用LIKE关键字的查询语句

      在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不会起作用。只有“%”不在第一个位置，索引才会起作用。

    2. 使用多列索引的查询语句

      MySQL可以为多个字段创建索引。一个索引可以包括16个字段。对于多列索引，只有查询条件中使用了这些字段中的第1个字段时索引才会被使用。

    3. 使用OR关键字的查询语句

      查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，查询中才使用索引。否则，查询将不使用索引。

  2. 优化子查询

    使用子查询可以进行SELECT语句的嵌套查询，即一个SELECT查询的结果作为另一个SELECT语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。

    子查询虽然可以使查询语句很灵活，但执行效率不高。执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。因此，子查询的速度会受到一定的影响。如果查询的数据量比较大，这种影响就会随之增大。

    在MySQL中，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引，性能会更好。

表中含有上千万条数据怎么办？

建议按照如下顺序进行优化：

  1. 优化SQL和索引；
  2. 增加缓存，如memcached、redis；
  3. 读写分离，可以采用主从复制，也可以采用主主复制；
  4. 使用MySQL自带的分区表，这对应用是透明的，无需改代码，但SQL语句是要针对分区表做优化的；
  5. 做垂直拆分，即根据模块的耦合度，将一个大的系统分为多个小的系统；
  6. 做水平拆分，要选择一个合理的sharding key，为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表。

慢查询优化的经验：

  1. 开启慢查询日志：

    MySQL中慢查询日志默认是关闭的，可以通过配置文件my.ini或者my.cnf中的log-slow-queries选项打开，也可以在MySQL服务启动的时候使用--log-slow-queries[=file_name]启动慢查询日志。

    启动慢查询日志时，需要在my.ini或者my.cnf文件中配置long_query_time选项指定记录阈值，如果某条查询语句的查询时间超过了这个值，这个查询过程将被记录到慢查询日志文件中。

  2. 分析慢查询日志：

    直接分析mysql慢查询日志，利用explain关键字可以模拟优化器执行SQL查询语句，来分析sql慢查询语句。

    常见慢查询优化：

    1. 索引没起作用的情况

      • 在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不会起作用。只有“%”不在第一个位置，索引才会起作用。
      • MySQL可以为多个字段创建索引。一个索引可以包括16个字段。对于多列索引，只有查询条件中使用了这些字段中的第1个字段时索引才会被使用。
      • 查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，查询中才使用索引。否则，查询将不使用索引。

    2. 优化数据库结构

      • 对于字段比较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。
      • 对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。

    3. 分解关联查询

      很多高性能的应用都会对关联查询进行分解，就是可以对每一个表进行一次单表查询，然后将查询结果在应用程序中进行关联，很多场景下这样会更高效。

    4. 优化LIMIT分页

      当偏移量非常大的时候，例如可能是limit 10000,20这样的查询，这是mysql需要查询10020条然后只返回最后20条，前面的10000条记录都将被舍弃，这样的代价很高。优化此类查询的一个最简单的方法是尽可能的使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候这样做的效率会得到很大提升。

分表 & 分区 & 分库

  1. 分表：分表包含垂直切分和水平切分。

    • 垂直分表：以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和扩展表）中。
      • 每个表的结构都不一样；
      • 每个表的数据也不一样，一般来说，每个表的字段至少有一列交集，一般是主键，用于关联数据；
      • 所有表的并集是全量数据；
      • 场景：系统绝对并发量并没有上来，表的记录并不多，但是字段多，并且热点数据和非热点数据在一起，单行数据所需的存储空间较大。以至于数据库缓存的数据行减少，查询时会去读磁盘数据产生大量的随机读IO，产生IO瓶颈。
      • 分析：可以用列表页和详情页来帮助理解。垂直分表的拆分原则是将热点数据（可能会冗余经常一起查询的数据）放在一起作为主表，非热点数据放在一起作为扩展表。这样更多的热点数据就能被缓存下来，进而减少了随机读IO。拆了之后，要想获得全部数据就需要关联两个表来取数据。但记住，千万别用join，因为join不仅会增加CPU负担并且会讲两个表耦合在一起（必须在一个数据库实例上）。关联数据，应该在业务Service层做文章，分别获取主表和扩展表数据然后用关联字段关联得到全部数据。
    • 水平分表：以字段为依据，按照一定策略（hash、range等），将一个表中的数据拆分到多个表中。
      • 每个表的结构都一样；
      • 每个表的数据都不一样，没有交集；
      • 所有表的并集是全量数据；
      • 场景：系统绝对并发量并没有上来，只是单表的数据量太多，影响了SQL效率，加重了CPU负担，以至于成为瓶颈。

  2. 分区：将大表进行分区，不同分区可以放置在不同存储设备上，这些分区在逻辑上组成一个大表，对客户端透明。

    • 分区方式：和水平切片是类似的，分区方式也和水平切片方式类似，如范围切片，取模切片等
    • 数据库分区无法将分区跨库，更不能跨数据库服务器，但能保存在不同数据文件从而放置在不同存储设备上

  3. 分库

    • 垂直分库：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。
      • 每个库的结构都不一样；
      • 每个库的数据也不一样，没有交集；
      • 所有库的并集是全量数据；
      • 场景：系统绝对并发量上来了，并且可以抽象出单独的业务模块。
      • 分析：到这一步，基本上就可以服务化了。例如，随着业务的发展一些公用的配置表、字典表等越来越多，这时可以将这些表拆到单独的库中，甚至可以服务化。再有，随着业务的发展孵化出了一套业务模式，这时可以将相关的表拆到单独的库中，甚至可以服务化。
    • 水平分库：以字段为依据，按照一定策略（hash、range等），将一个库中的数据拆分到多个库中。
      • 每个库的结构都一样；
      • 每个库的数据都不一样，没有交集；
      • 所有库的并集是全量数据；
      • 场景：系统绝对并发量上来了，分表难以根本上解决问题，并且还没有明显的业务归属来垂直分库。

分库分表

EXPLAIN

MySQL中提供了EXPLAIN语句和DESCRIBE语句，用来分析查询语句，EXPLAIN语句的基本语法为：EXPLAIN [EXTENDED] SELECT select_options

使用EXTENED关键字，EXPLAIN语句将产生附加信息。执行该语句，可以分析EXPLAIN后面SELECT语句的执行情况，并且能够分析出所查询表的一些特征。下面对查询结果进行解释：

  • id：SELECT识别符。这是SELECT的查询序列号。
  • select_type：表示SELECT语句的类型。
  • table：表示查询的表。
  • type：表示表的连接类型。
  • possible_keys：给出了MySQL在搜索数据记录时可选用的各个索引。
  • key：是MySQL实际选用的索引。
  • key_len：给出索引按字节计算的长度，key_len数值越小，表示越快。
  • ref：给出了关联关系中另一个数据表里的数据列名。
  • rows：是MySQL在执行这个查询时预计会从这个数据表里读出的数据行的个数。
  • Extra：提供了与关联操作有关的信息。

高效插入数据策略

影响插入速度的主要是索引、唯一性校验、一次插入记录条数等。针对这些情况，可以分别进行优化。（为什么是这么因素导致的？）

对于InnoDB引擎的表，常见的优化方法如下：

  1. 禁用唯一性检查

    插入数据之前执行set unique_checks=0来禁止对唯一索引的检查，数据导入完成之后再运行set unique_checks=1。

  2. 禁用外键（非主键）检查

    插入数据之前执行禁止对外键的检查，数据插入完成之后再恢复对外键的检查。

  3. 禁用自动提交

    插入数据之前禁止事务的自动提交，数据导入完成之后，执行恢复自动提交操作。

分布式

其他

参考资料：

  1. https://www.nowcoder.com/discuss/135748
  2. https://www.huaweicloud.com/articles/8ac1dc6dfe005bdaf76f7d1d2136276a.html