我的校招记录：校招笔记（零）_写在前面 ，以下是校招笔记总目录。

备注
算法能力（“刷题”） 这部分就是耗时间多练习，Leetcode-Top100 是很好的选择。 补充练习：codeTop
计算机基础(上)（“八股”） 校招笔记（一）__Java_Java入门 C++后端后续更新
校招笔记（一）__Java_面对对象
校招笔记（一）__Java_集合
校招笔记（一）__Java_多线程
校招笔记（一）__Java_锁
校招笔记（一）__Java_JVM
计算机基础(下)（“八股”） 校招笔记（二）__计算机基础_Linux&Git
校招笔记（三）__计算机基础_计算机网络
校招笔记（四）__计算机基础_操作系统
校招笔记（五）__计算机基础_MySQL
校招笔记（六）__计算机基础_Redis
校招笔记（七）__计算机基础_数据结构
校招笔记（八）__计算机基础_场景&智力题
校招笔记（九）__计算机基础_相关补充
项目&实习 主要是怎么准备项目，后续更新

五、MySQL

5.1 MySQL基本

0. 关系型数据库和非关系数据库的区别？

  • 关系型数据库的优点
    • 容易理解，因为它采用了关系模型来组织数据；
    • 可以保持数据的一致性；
    • 数据更新的开销比较小；
    • 支持复杂查询（带where子句的查询）。
  • 非关系型数据库的优点
    • 不需要经过SQL层的解析，读写效率高；
    • 基于键值对，数据的扩展性很好；
    • 支持多种类型数据的存储，如图片，文档等等。

1.介绍一下数据库三范式？

参考：https://www.cnblogs.com/linjiqin/archive/2012/04/01/2428695.html

  • 第一范式： 数据库表中的所有字段值都是不可分解的原子值 。

    数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储。

    img

  • 第二范式： 第二范式基于第一范式，且要求：数据库表中的每一列都和主键相关，且不能只与主键的某一部分相关（主要针对联合主键而言）, 即不存在部分依赖；

    下表是以：订单编号&商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的订单编号相关，而仅仅是与商品编号相关。

    img

    所以根据第二范式，将它进行拆分三个表：

    img

  • 第三范式： 基于第二范式，数据表中的每一列数据都和主键直接相关，即不存在传递依赖；

    比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。

    img

2. MySQL数据库引擎有哪些？

MySQL查看所有的数据引擎：

1
mysql> show engines;

MySQL常用引擎包括：MYISAM、Innodb、Memory、MERGE，

  • MYISAM （读：my+i+son）

    以select、insert为主的应用基本上可以使用这引擎。

    • 优点：全表锁，拥有较高的执行速度，占用空间小；
    • 缺点 ：不支持事务，不支持外键，并发性能差。

  • Innodb

    Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别，它的设计目标是处理大容量数据库系统。

    • 优点：行级锁，支持事务，支持自动增长列，支持外键约束，并发能力强
    • 缺点： 占用空间是MYISAM的2倍，处理效率相对也更低

  • Memory

    主要用于内容变化不频繁的代码表。

    • 优点：全表锁，存储在内存中，默认使用Hash，检索效率非常高
    • 缺点： 会占用和数据量成正比的内存空间且，mysql重启时会丢失，不适合精确查找

  • MERGE

    是一组MYISAM表的组合。

2.1 InnoDB、MyISAM、Memory 【索引】（按数据结构分）

参考：https://segmentfault.com/a/1190000037683781

image-20210820194526488

2.2 为什么Innodb使用自增id作为主键？
  • 如果不使用自增主键， 如身份证号、学号，每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置（涉及到B+树分裂等）， 频繁的移动、分页操作造成了大量的碎片；
  • 如果使用自增主键， 那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。

3.说说InnoDB与MyISAM的区别？

见前。

3.1 说说InnoDB与MyISAM在B+数索引方式区别？

  • MyISAm , B+Tree叶节点的data域存放的是数据记录的地址，在索引检索的时候，如果指定的key存在，则取出其data域的值，然后以data域的值为地址读取相应的数据记录，这被称为“非聚簇索引” ；

  • InnoDB， 树的节点data域保存了完整的数据记录，这个索引的key是数据表的主键（自增id） ；而其它索引都叫做辅助索引， 助索引的data域存储相应记录主键的值而不是记录地址。

    • 在根据主索引搜索时，直接找到key所在的节点即可取出数据；
    • 在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。
3.2 【百度】说说InnoDB与MyISAM在适用场景上的区别？
  • MyISAM ，没有事务， 适合插入不频繁，查询非常频繁；
  • Innodb： 有事务，适合可靠性要求比较高，或者更新和查询比较频繁。

4. 为什么 SELECT COUNT(*) FROM table 在 InnoDB 比MyISAM 慢？

对于 SELECT COUNT(*) FROM table 语句，在没有 WHERE 条件的情况下，InnoDB 比 MyISAM 可能会慢很多，尤其在大表的情况下。

  • InnoDB 是去实时统计结果，会全表扫描；
  • 而 MyISAM内部维持了一个计数器，预存了结果，所以直接返回即可。

5.简单说一说drop、delete与truncate的区别？

SQL中的drop、delete、truncate都表示删除，但是三者有一些差别：

  • 删除类型： drop删除表结构；delete、truncate删除表内容。
  • 删除速度： drop> truncate >delete
  • 生效速度： drop和truncate ，操作立即生效，不能回滚也不触发触发器；delete事务提交后才生效，会触发相应触发器。

6.什么是视图？ 游标？

视图是一种虚拟的表，通常是一个表或者多个表的行或列的子集，具有和物理表相同的功能。

  • 可以对视图进行增，改，查，操作，但对视图的修改不影响基本表；
  • 相比多表查询，获取数据速度更容易。

游标，是对查询出来的结果集作为一个单元来有效的处理。

  • 一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。

7.什么是内联接、左外联接、右外联接？

  • 内联接（Inner Join）：两个表（或连接）中某一数据项相等的连接称为内连接。 连接的结果是形成一个新的数据表。 内连接中参与连接的表（或连接）的地位是相等的；

  • 左外联接（Left Outer Join）：除了匹配2张表中相关联的记录外，还会匹配左表中剩余的记录，右表中未匹配到的字段用NULL表示；

  • 右外联接（Right Outer Join）：除了匹配2张表中相关联的记录外，还会匹配右表中剩余的记录，左表中未匹配到的字段用NULL表示。

8.说说在 MySQL 中一条查询 SQL 是如何执行的？

例如：select name from t_user where id=1

  1. 取得链接，使用使用到 MySQL 中的连接器；

  2. 查询缓存，key 为 SQL 语句，value 为查询结果，如果查到就直接返回；

    在 MySQL 8.0 版本已经将查询缓存删除，也就是说 MySQL 8.0 版本后不存在此功能。

  3. 分析器，分为词法分析和语法分析。此阶段只是做一些 SQL 解析，语法校验。所以一般语法错误在此阶段；

  4. 优化器，是在表里有多个索引的时候，决定使用哪个索引；或者一个语句中存在多表关联的时候（join），决定各个表的连接顺序；

  5. 执行器，开始执行语句。执行语句的时候还要判断是否具备此权限，如果有权限：（1）根据表的引擎定义，使用引擎提供的接口，获取表的第一行，判断id是否等于1 ；（2）如果不是则继续调用引擎接口去获取下一行，继续判断；（3）直至取到这个表的最后一行

9. MySQL 中 varchar 与 char 的区别？ int(3) 呢？

  • char 是一种固定长度的类型，varchar 则是一种可变长度的类型。

    image-20210503214113219

  • float(3,2) 和 int(3)

    • float(3,2) : "浮点型"的长度是用来限制数字存储范围的。比如 float(3,2) 只能够写入 0.00~999.99。
    • int(3) ："整型"的长度并不会限制存储的数字范围，都是-2147483648 ~ 2147483647 。只限制显示长度。

10.超键、主键、候选键和外键有什么区别？

  • 超键(super key)：在关系中能唯一标识元组的属性集称为关系模式的超键；
  • 候选键(candidate key)：不含有多余属性的超键称为候选键，是超键的子集；
  • 主键(primary key)：用户选作元组标识的一个候选键程序主键，是候选键的子集 ；
  • 外键：如果关系模式R中属性K是其它模式的主键，那么k在模式R中称为外键。

下面举例说明（假设姓名不重复唯一）：

身份证 姓名 性别 年龄
  • 超键： 身份证、姓名、（姓名、性别）、（姓名、性别、年龄）都可以作为超键；
  • 候选键 ：身份证、姓名 都唯一，都可以作为候选键；
  • 主键： 在候选键选一个作为主键，例如：身份证。

11. 解释⼀下什么是池化设计思想。什么是数据库连接池?为什么需要数据库连接池?

  • 池化设计思想

    我们常⻅的如java线程池、jdbc连接池、redis连接池等就是这类设计的代表实现。这种设计会初始预设资源，解决的问题就是抵消每次获取资源的消耗，如创建线程的开销，获取远程连接的开销等。

  • 数据库连接池

    数据库连接本质就是⼀个 socket 的连接。数据库服务端还要维护⼀些缓存和⽤户权限信息之类的所以占⽤了⼀些内存。我们可以把数据库连接池是看做是维护的数据库连接的缓存，以便将来需要对数据库的请求时可以重⽤这些连接。

  • 为什么要用数据库连接池？

    为每个⽤户打开和维护数据库连接，尤其是对动态数据库驱动的网站应⽤程序的请求，既昂贵⼜浪费资源。在连接池中，创建连接后，将其放置在池中，并再次使⽤它，因此不必建⽴新的连接。如果使⽤了所有连接，则会建⽴⼀个新连接并将其添加到池中。

12. 如果数据不存储在磁盘而是在内存，用什么数据结构？为什么Mysql不使用hash？

参考：为什么hash作为内存使用的经典数据结构?

数据放在磁盘，使用B+树，核心是为了减少磁盘IO，因为磁盘IO的代价很大（是内存的十万倍）。

我们使用hash寻找数据的时候，数据随机分散到各个物理位置，不是有序的数据。而内存设备也是随机访问设备，内存很适合用hash方式来读取数据。

  • 随机访问：存储器单元的内容可以根据需要自由取出或存储，而且访问的速度与存储器单元的位置无关 。(通过行，列地址总线就可以快速定位存储的数据)

    但是磁盘，每次访问数据，是需要先定位，然后顺序移动；如果下个数据不在磁头附近，又要重新定位。

    如果Hash索引磁盘数据，（1）每次访问都要IO不能范围（2）数据太多，Hash索引保存不了键值，而高度为3的B+数就能保存千万级别的数据（3）当数据量很大时，hash冲突的概率也会非常大（4）组合索引可以支持部分索引查询，如(a,b,c)的组合索引，查询中只用到了阿和b也可以查询的，如果使用hash表，组合索引会将几个字段合并hash，没办法支持部分索引 （5）当需要按照索引进行order by时，hash值没办法支持排序 。

13.【字节】Mysql Join的原理？

参考：https://zhuanlan.zhihu.com/p/54275505

1
select * from user tb1 left join level tb2 on tb1.id=tb2.user_id

  1. 简单嵌套循环

    双层for 循环 ，通过循环外层表的行数据，逐个与内层表的所有行数据进行比较来获取结果。

    image-20210904063419237

  2. 索引嵌套循环连接

    通过外层表匹配条件直接与内层表索引进行匹配，避免和内层表的每条记录去进行比较， 这样极大的减少了对内层表的匹配次数。

    • 从原来的匹配次数=外层表行数 * 内层表行数,变成了 外层表的行数 * 内层表索引的高度，极大的提升了 join的性能；
    • 注意，要为表user_level 去建立索引
    image-20210904063546011

  3. 缓存块嵌套循环连接

    其优化思路是减少内层表的扫表次数。

    • 通过简单的嵌套循环查询的图，我们可以看到，左表的每一条记录都会对右表进行一次扫表，扫表的过程其实也就是从内存读取数据的过程，那么这个过程其实是比较消耗性能的。

    img

    所以缓存块嵌套循环连接算法意在通过一次性缓存外层表的多条数据，以此来减少内层表的扫表次数，从而达到提升性能的目的。如果无法使用Index Nested-Loop Join的时候，数据库是默认使用的是Block Nested-Loop Join算法的。

    • 当level 表的 user_id 不为索引的时候，默认会使用Block Nested-Loop Join算法。

    img

13.1 join和left join区别?

join相当于我们平时用的where，就是把两张表中同时满足a.id=b.id的数据找出来；

left join是以左表（a）为参考对象，相当于做一个for循环，把a表的数据一条一条的读取出来，然后根据a.id=b.id的条件到b表中查找数据。

13.2 unio和unio all的区别?

如果我们需要将两个select语句的结果作为一个整体显示出来，我们就需要用到union或者union all关键字。

1
select employee_id,job_id from employees union select employee_id,job_id from job_history

union会自动压缩多个结果集合中的重复结果，而union all则将所有的结果全部显示出来，不管是不是重复。

13.3 unio 和 join的区别?

参考：https://www.jb51.net/article/30975.htm

join是对两个表进行联合，相当于where，满足条件的行会被选出，其中列会被扩充！ 但unio是连接结果集，需要满足列个数相同，只会保存第一个表列个数，列个数不会被扩充！

5.2 分布式数据库

0. 【字节】分布式数据库锁如何实现？

  • 什么时候用到分布式数据库？
    1. 水平拆分 ：数据量大到单机数据库已存储不下时， 可以对数据进行拆分，化整为零，将数据均匀分布到多个数据库节点中。
    2. 读写分离：主要用在数据量并不大，单机数据库能够hold得住，但读请求很高的情况下。此时，可以配置多个只读数据库节点，来分担主节点的读请求。通过数据复制机制，在主节点和只读节点之间进行数据的实时同步，保证主从节点的数据一致性。
  • 分布式数据库锁？
    1. 直接锁表，代价比较大
    2. 加入排它锁，查询语句后面增加for update（这里我们希望使用行级锁，就要给method_name添加索引）
    3. Zookeeper实现分布式锁

1. 请说说MySQL数据库的锁？

img

MySQL 中常见锁如下：

【按使用方式划分】

  • 共享锁：不堵塞，多个用户可以同一时刻读取同一个资源，相互之间没有影响；

  • 排它锁（写锁）：一个写操作阻塞其他的读锁和写锁，只允许一个用户进行写入，防止其他用户读取正在写入的资源。

【按锁粒度划分】

  • 表锁：系统开销最小，会锁定整张表，不会出现死锁；但是由于粒度太大，因此造成锁的冲突几率大，并发性能低。MyISAM 使用表锁。

  • 行锁：容易出现死锁，发生冲突概率低，并发性能高。InnoDB 支持行锁。

    必须有索引才能实现，否则会自动锁全表，那么就不是行锁。

【按思想划分】

  • 乐观锁
  • 悲观锁

2.说说什么是锁升级？什么情况发生锁升级？

区分一下sycronized锁升级。

锁升级是指将当前锁的粒度降低：行锁→页锁→表锁。

发生锁升级的情况：

  1. 当一条SQL语句对同一个对象上持有的锁数量超锁了阈值，默认这个阈值为5000，但是对于不同对象不会发生锁升级
  2. 锁资源占用的内存超过激活内存的百分之40 就会发生锁升级
2.1 为什么说innoDB 引擎不存在锁升级的问题 ？

待补充。

2.2 什么时候触发行锁和表级锁？

mysql默认存储引擎都是innodb，默认是使用行锁 。

  • 触发行级锁 ，行级锁锁的是索引记录 ，使用了索引所以就会触发行级锁。

  • 触发表级锁，有以下三种情况

    其实不管是读操作(select)还是写操作(update,delete,insert),只要涉及到带有筛选条件的语句，如果筛选条件中没有用到索引，就会触发全表扫描。

    1. 全表更新：事务需要更新大部分数据或全部数据，如果使用行级锁，会导致事务执行效率低，从而导致其他事务长时间等待锁和更多的锁冲突
    2. 多表级联：事务涉及多张表，比较复杂的关联查询，很可能造成死锁，这种情况若能一次性锁住事务涉及的表，从而避免死锁，减少数据库事务回滚所带来的开销
    3. 筛选条件中未用到索引： 全表扫描
    4. 用到索引，但区分度程度不高 ：innodb认为全表扫描比走索引效率更高导致索引失效，这个时候就要通过explain去查看下查询计划，看下查询语句是否真的用到了索引
2.3 行锁适合的场景？

A用户消费，service层先查询该用户的账户余额，若余额足够，则进行后续的扣款操作；这种情况查询的时候应该对该记录进行加锁。

1
for update # 操作该记录时加上

否则，B用户在A用户查询后消费前先一步将A用户账号上的钱转走，而此时A用户已经进行了用户余额是否足够的判断，则可能会出现余额已经不足但却扣款成功的情况。

3.怎样尽量避免死锁的出现？

  1. 设置获取锁的超时时间，至少能保证最差情况下，可以退出程序，不至于一直等待导致死锁；

  2. 设置按照同一顺序访问资源，类似于串行执行；

  3. 避免事务中的用户交叉；

  4. 保持事务简短并在一个批处理中；

  5. 使用低隔离级别。

4.解释一下悲观锁和乐观锁？

  • 悲观锁： 悲观锁是基于一种悲观的态度类来防止一切数据冲突。它是以一种预防的姿态 在修改数据之前把数据锁住，然后再对数据进行读写，在它释放锁之前任何人都不能对其数据进行操作。一般数据库本身锁的机制都是基于悲观锁的机制实现的。

    • 特点： 完全保证数据的独占性和正确性，因为每次请求都会先对数据进行加锁；但是造成性能消耗

    • 实现 ：

      1
      2
      3
      4
      5
      6
      7
      8
      # 加读锁
      LOCK tables test_db READ 
      # 释放锁
      UNLOCK TABLES
      # 加写锁
      LOCK tables test_db WRITE 
      # 释放锁
      UNLOCK TABLES

  • 乐观锁： 对于数据冲突保持一种乐观态度，操作数据时不会对操作的数据进行加锁，使得多个任务可并行对数据操作。但是使用一种验证机制来避免数据冲突 （一般通过加版本后对比来实现）。

    • 特点： 并发类型的锁，本身不加锁但通过业务实现锁的功能 ，没有锁操作因此性能更高。

    • 实现形式：

      （1）两个请求同时操作操作标Name字段，二者查询需检索的数据都是一样的：

      img

      （2）请求1修改字段数据“zhangsan”→“lisi” ，并将版本号增加+1 ，验证版本号一直后提交

      1
      update A set Name=lisi,version=version+1 where ID=#{id} and version=#{version}

      （3）请求2也想修改“zhangsan”→“liming” ，但是提交时由于 版本号不一致，无法提交成功

4.1 数据库乐观锁和悲观锁，如何实现？

  • 实现乐观锁

    1. 利用版本号，如MVCC；

    2. 时间戳：同样是在需要乐观锁控制的table中增加一个字段，名称无所谓，字段类型使用时间戳（timestamp）, 和上面的version类似。

      也是在更新提交的时候，将当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突。

  • 实现悲观锁

    直接加上读锁或者写锁，SELECT … FOR UPDATE 。

5.介绍一下分布式数据库全局ID唯一且自增，如何生成？ （或者问分库分表之后，id 主键如何处理？）

因为要是分成多个表之后，每个表都是从 1 开始累加，这样是不对的，我们需要⼀个全局唯⼀的 id来支持。

  1. UUID：不适合作为主键，因为太长了，并且无序不可读，查询效率低。比较适合用于生成唯一的名字的标示比如文件的名字。

  2. ID自增量为分布式数据库个数，缺点是扩展性不好；

  3. snow flake算法：snowflake是Twitter开源的分布式ID生成算法，结果是一个long型的ID。

    img

    • 核心思想： 使用41bit作为毫秒数，10bit作为机器的ID（5个bit是数据中心，5个bit的机器ID），12bit作为毫秒内的流水号（意味着每个节点在每毫秒可以产生 4096 个 不同ID），最后还有一个符号位，永远是0。

  4. Leaf算法：分号段

8.介绍一下哈希算法？和一致性哈希算法？

  • 哈希算法

    • 介绍： 哈希算法将任意长度的二进制值映射为较短的固定长度的二进制值，这个小的二进制值称为哈希值。哈希值是一段数据唯一且极其紧凑的数值表示形式。
    • 分布式应用中缺点： 在分布式的存储系统中，要将数据存储到具体的节点上。如果采用哈希算法：key%N（key是数据的key，N是机器节点数） 。出现机器加入或退出集群（N变了，映射值不一样了），所有的数据映射都无效了。

  • 一致性哈希算法

    • 介绍：解决普通哈希算法造成负载均衡时，在服务节点数量变动时出现哈希失效 问题

    • 实现：

      1. 构建环：按照常用的hash算法来将对应的key哈希到一个具有2^32次方个节点的空间中（即0 ~ (2^32)-1）。

        Note: 节点的个数可以自定义, hash环我们可以用TreeMap来实现, 因为treeMap是排序的，我们刚好可以利用上。

        img

      2. 映射服务器节点： 将各个服务器使用Hash进行一个哈希，确定每台机器其在哈希环上的位置。

        一般用服务器ip或唯一主机名进行哈希。

        img

      3. 映射数据。 对于要存储的数据：ojectA、objectB、objectC、objectD ，首先通过特定哈希函数计算出hash值 ，散列到环上。然后从数据所在位置沿环顺时针“行走”，第一台遇到的服务器就是其应该定位到的服务器。

        img

      4. 出现服务器变动。 如果此时NodeC宕机了，此时Object A、B、D不会受到影响，只有Object C会重新分配到Node D。避免了大量数据迁移，减小了服务器的的压力 。

9. （再理解）介绍一下MVVC？

MVCC（Multi-Version Concurrency Control）多版本并发控制，是

数据库控制并发访问的一种手段。

  • 特别要注意MVCC只在读已提交(RC) 和可重复读（RR） 这两种事务隔离级别下才有效
  • 是数据库引擎（InnoDB） 层面实现的，用来处理读写冲突的手段（不用加锁），提高访问性能

  • MVCC协议中，事务提交不会覆盖原数据，而是产生新版本数据，每个数据有多个历史版本，但同一时刻只有最新的版本有效；

  • MVCC是在并发访问数据库时，通过对数据做多版本管理，避免【因为写锁的阻塞而造成读数据的并发阻塞】问题。可以让读取数据同时修改，【修改数据时同时可读取】。简单来说，就是不对数据库加上读写锁！

9.1 MVCC 如何实现 ？

参考：Innodb MVCC实现原理

  • 总结性回答

    MVCC是以一个多版本并发控制系统，它主要通过以下两个部分实现：

    • 每行记录：记录最近操作该行记录的事务ID，和上一版本数据的指针（指向undo log）
    • readview：每次读（写不可以） 可以获取一个readview，记录当前活跃的事务ID ，可以在写的过程进行读

    写的时候并发读，通过比较当前行记录的事务ID和readview活跃的事务ID，来决定是否读取该行记录还是上一版本的数据。

MVCC实现的核心部分为：

  1. 事务版本号：每次事务开启前都会从数据库获得一个自增长的事务ID，可以从事务ID判断事务的执行先后顺序。

  2. 表的隐藏列 ：每一行 有如下三个重要字段属性：

    隐藏列 作用
    DB_TRX_ID 记录操作该行事务的ID
    DB_ROLL_PTR 指向上一个版本的数据（在undo log）的指针
    DB_ROW_ID 隐藏ID，当表没有合适的索引作为聚簇索引时，会用该ID创建聚簇索引

  3. undo log ：每一行记录被修改之前的日志。

    当事务被回滚时，可以用通过undo log日志对数据进行还原。

    一个小例子：修改某行记录name从 “张三→李四”

    preview

  4. read view。在innodb 中每个SQL语句执行前都会得到一个read_view，保存了当前数据库系统中正处于活跃（没有提交）的事务的ID号。

    根据事务的隔离性，这些事务ID列表不会被其它事务看到。

    其相关重要属性如下：

    属性 作用
    trx_ids 当前系统：活跃（未提交）事务版本号集合
    low_limit_id 当前系统：最大版本事务号+1
    up_limit_id 当前系统：活跃的最小事务版本号
    creator_trx_id 当前read view：事务版本号

具体原理可以描述如下：

举个例子，假设有一个user_info表，初始数据如下：

image-20210529211818428

现在有事务A，B同时对id=1 这行数据进行操作，先开启事务A（未提交），此时执行事务B，最后返回什么结果呢？

  1. 事务A（写），执行 update user_info set name =”李四”

    • 获得事务编号：102

    • 当前事务A的视图为：（写）不会获得read view！

  2. 事务B（读），执行 select * fom user_info where id=1

    • 获得事务B编号：103

    • 当前事务B的视图为：

      trx_ids 当前活跃的事务：102,103
      low_limit_id 当前最大事务id+1:104
      up_limit_id 当前最小事务id：102
      creator_trx_id 当前事务id：103

    不同隔离级别下，read view的工作方式：

    1. 读未提交：不会获得read view的副本（等于没有并发控制所以会出现脏读）

    2. 读提交：每一次select都会获得一个read view的副本，所以会造成【不可重复读】

      image-20210529222746175

    3. 可重复读：同一个事务select只会获得一次read view副本（select的时候不允许修改），所以不会出现不可重复读。

      image-20210529222905837

  3. 事务A（写），开始执行update语句：（1）把原数据拷贝到undo log，（2）然后进行修改name字段：“张三→李四”，（3）并设置DB_TRX_ID 隐藏字段：99→102 （4）设置DB_ROLL_PTR隐藏字段：执行上个事务版本地址

    image-20210529213739125

    此时，事务A还并未提交，依旧是处于活跃状态。

  4. 事务B（读），开始执行select语句，查询到是事务A修改后的语句

    image-20210529214159799

    把数据和read view （视图）匹配： 保证（1）数据已提交，没有其他事物修改，否则去找上一版本的数据。

    1. 当前数据记录的事务id < 事务B视图中最小活跃事务id : 说明数据，是在事务B的read view 创建前就存在，所以可以显示。
    2. 当前数据记录的事务id > 事务B视图中最大活跃事务id ：说明数据，是在事务B的read view 创建后才存在，此时不应该显示。
    3. 事务B视图中最小活跃事务id<= 当前数据记录的事务id <= 事务B视图中最大活跃事务id ： 说明有其它事务在修改这行记录数据，但可能还没有提交。把id和事务B的read view的活跃事务集合trx_ids进行匹配：
      • 如果数据记录的ID不存在trx_ids，说明已经提交了事务，可以显示
      • 如果存在trx_ids，说明数据还没提交，此时事务B查询到数据不能显示（除非此时数据记录的事务id == 事务B的id，由于是自己的当然可以显示）

  5. 事务B（读），根据上述匹配规则，此时不能读，应该去undo log中找到上一版本数据。

    此时事务B的id=103，数据记录的事务id=102 ：满足条件3，且此时trx_ids存在事务id=102，说明事务A（id=102）还没提交，因此不能显示。

    故，最终查找的数据为：

    image-20210529211818428

可以看到，整个过程MVCC事务A【写】没有加锁，只是进行版本号控制 & undo log，可以进行并发【读】。

9.2 MVCC 版本号如何变化的？更新的数据如何确定版本的？【幻读】又是怎么解决的呢？

  • 版本号变化

    每条记录有三个隐藏列：（1）DB_TRX_ID （2）DB_ROLL_PTR （3）DB_ROW_ID

    每一次新事务，update更新语句，都会将（1）拷贝数据到undo log（2）DB_ROLL_PTR指向上个数据的版本undo log（3）DB_TRX_ID修改为新事务的id 。

  • 为什么MVCC可解决幻读

    在RR（可重复读级别下）不会出现幻读。例如：

    1. 开启事务1，获得事务ID为1；
    2. 事务1执行查询，得到readview；
    3. 开始事务2；
    4. 执行insert；
    5. 提交事务2；
    6. 执行事务1的第二次查询 (因为这里是RR级别，所以不会再去获得readview，还是使用第一次获得的readview，所以当前read view 事务的ID不会更新，即不会加入插入事务2)；
    7. 最后得到的结果是，插入的数据不会显示，因为插入的数据事务ID > readview里的最大活跃事务ID。
9.3 mvvc 和 for update的区别
  1. 是否加锁： mvvc不加锁，只通过多版本来进行并发控制；for update会加锁（即X/写锁）。
9.4 （快手）MVCC作用，如何实现RC，RR?

参考：https://zhuanlan.zhihu.com/p/73078137

作用如下：

  1. 最简单的描述，读写不阻塞，读的时候可以写
  2. 提高性能

实现RC，RR两个隔离级别：

MVCC 读某一个数据时，根据隔离级别，事务选择要读取哪个版本的数据，过程中完全不需要加锁。

  1. Read Committed ：一个事务读取数据时总是读这个数据最近一次被commit的版本 ，所以可以避免脏读（快手）；

  2. Repeatable Read ： 一个事务读取数据时总是读取当前【事务开始之前】最后一次被commit的版本（所以底层实现时需要比较当前事务和数据被commit的版本号），所以避免了幻读（快手）。

举个简单的例子：

  1. 一个事务A（txnId=100）修改了数据X，使得X=1，并且commit了
  2. 另外一个事务B（txnId=101）开始尝试读取X，但是还X=1。但B没有提交。
  3. 第三个事务C（txnId=102）修改了数据X，使得X=2。并且提交了
  4. 事务B又一次读取了X。这时
    • 如果事务B是Read Committed。那么就读取X的最新commit的版本，也就是X=2
    • 如果事务B是Repeatable Read。那么读取的就是当前事务（txnId=101）之前X的最新版本，也就是X被txnId=100提交的版本，即X=1。

注意，这里B不论是Read Committed，还是Repeatable Read，都不会被锁，都能立刻拿到结果。这也就是MVCC存在的意义。

5.3 索引相关

🛰 强烈建议对索引更深刻理解，先看完美团这篇技术文章：MySQL索引原理及慢查询优化

0. MySQL索引原理及慢查询优化

0.1 为什么要需要索引

答到：遍历磁盘开销太大这个关键。

数据库实现比较复杂，数据保存在磁盘上，而为了提高性能，每次又可以把部分读入内存来计算 。

磁盘的成本大概是访问内存的十万倍左右 ， 每次去遍历磁盘找到数据再读入内存，是难以接受的。因此需要一种快速找到磁盘数据的方式，就像字典通过字母索引→快速定位单词。因此索引由此而出现。

0.2 局部性原理（磁盘IO一次读取大小）

在继续往下讲之前，先来讲讲磁盘读取局部性原理。

局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。

  • 因此，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内。

每次IO读取的数据，分为下面情况：

  • Linux：上以页为单位，一次一页（4K）
  • 数据库：mysql（InnoDB引擎）一页（16K）; SQL Server/ Oracle，默认块，大小都是8KB
0.3 索引→B+树

相比二叉树，平衡树等，B+树 作为高度可控的多路搜索树 ，可以很好的满足要求。（详细选择原因，可以参考：5.3 索引相关–问答6）

  • B+树基本结构

    b+树

    浅蓝色是一个磁盘块，数据项就是被组织索引那列的字段值。

    如果是Innodb引擎，使用聚簇索引就是按照每张表的主键 构造索引，那么此时数据项就是主键值。

    • 蓝色部分（如17,35）是数据项；黄色部分（如P1,P2,P3）是指针，指向下个磁盘块（页地址）。

    • 1. 为什么B+树非叶子节点不存放数据？

      IO次数取决于b+数的高度h，每个磁盘块大小也是一定（一页）的。如果数据项占的空间越小，一个磁盘块数据项的数量越多，树的高度越低，B+可以更高阶（指针占用先忽略）。

      所以磁盘非叶子节点不保存数据，是为了保存更多的数据项和指针，这样一颗B+树叶子节点就能存储更多的数据。

    • 2. 数据查找过程？

      以查找数据项=28 ，过程为例：
      （1） 先 加载（一般常驻内存） 根页面1（磁盘块1），因为17<28<35 所以使用P2指针 ， 找到磁盘块3（页面3）地址

      （2）在 IO加载 磁盘块3，因为 26<28<30 ，因此根据此时的P2 指针，找到磁盘块8所在的地址

      （3） IO加载 磁盘块8，因为此时是叶子节点，可以得到数据项=28对应的那行记录 。

      仅仅最多3次磁盘IO就找到了数据，这提升是巨大的。

    • 3. 高度为3的B+数可以存储多少数据？

      • 叶子节点一页存储的记录数

        mysql一页16K，所以一页存储16K/1K = 16条 记录。（实际真实业务场景一条记录一般就是1K）

      • 非叶子节点一页存储的指针数

        由于数据项也要占用空间，其和指针只相差1，按成对算。

        假设主键ID（数据项）为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节 。那么一共14字节。

        所以一页可以存储：16K / 14B ≈ 1K 个指向页面的指针。

      • 计算可以存储多少页

        高度为3的B+树，第3层叶子节点存储真实数据页，前2层是存储数据项+指针。第二层指针==叶子节点存储的页面数。

        1. 第一层：根页面可以存储1K个指针，每个指针指向一个新的页面
        2. 第二层：存储 1K*1K = 1M ，约一百万个指针，指向一百万个叶子数据页面

        所以一共存储：1M*16≈16M ，即千万级别的数据。

0.4 慢查询优化

（暂略）

1. MySQ索引的原理和数据结构能介绍一下吗？索引的缺点？

  • 索引原理： 本质是用来优化查询速度。用一个数据结构组织某一列的数据，然后如果你要根据那一列的数据查询的时候，就可以不用全表扫描，只要根据那个特定的数据结构快速去找到那一列的值。

  • 数据结构： MySQL索引通过B+树来实现（按数据结构分也有hash索引、fulltext索引）。

  • 索引缺点： （1）占用磁盘存储空间 （2）降低表更新速度，表发生变化，索引也要发生变化

2. MySQL 索引类型有哪些？

按数据结构分类可分为：B+tree索引、Hash索引、Full-text索引。
按物理存储分类可分为：聚簇索引、二级索引（辅助索引）。
按字段特性分类可分为：主键索引、普通索引、前缀索引。
按字段个数分类可分为：单列索引、联合索引（复合索引、组合索引）。

  • 主键索引：索引列中的值必须是唯一的，不允许有空值；

  • 唯一索引：索引列中的值必须是唯一的，但是允许为空值；

  • 普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值；

  • 全文索引：只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引；

    字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引；

    MyISAM和InnoDB中都可以使用全文索引。

  • 前缀索引：在文本类型如CHAR,VARCHAR,TEXT类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定；

  • 空间索引： MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。

    MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。

2.1 什么是覆盖索引？

我们在非聚簇索引一般只获得了，记录的key值（Innodb引擎），还需要回到聚簇索引中再次查询。

  • 覆盖索引： 从非主键索引中就能查到的记录，而不需要查询主键索引中的记录，避免了回表的产生减少了树的搜索次数，显著提升性能。

一个小例子。

以name和age两个字段建立联合索引，sql命令与建立后的索引树结构如下：

image-20210526230732546 
1
2
ALTER TABLE student DROP INDEX I_name;
ALTER TABLE student ADD INDEX I_name_age(name, age);

此时执行如下sql：

1
SELECT age FROM student WHERE name = '小李'；

在非聚簇索引中包含了age信息，那么直接返回12，不需要再到聚簇索引中查询。

【重点】3.什么时候使用索引比较好？什么时候不要使用索引？

3.1 什么时候用索引

特别的，主键自动建立唯一索引。

  1. 频繁作为查询条件的字段应该创建索引 ；

  2. 查询中与其他表关联的字段，外键关系建立索引；

  3. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度（索引就是排序加快速查找）；

  4. 查询中统计或者分组字段。

3.2 什么时候不用索引

  1. 经常增删改的列不要建立索引（因为每次更新不单单是更新记录，还会更新索引，保存索引文件）；

  2. 表记录太少不要建立索引；

  3. （被CSIG问过）区分度低，数据重复且分布平均的字段不适合做索引；

    例如性别字段，只有男女，不适合建立索引。

    • 因为从索引中拿到的只是地址，要想真正访问到数据还是要对表进行一次IO；
    • 假如你要从表的100万行数据中取几个数据，那么利用索引迅速定位，访问索引的这IO开销就非常值了；
    • 但如果你是从100万行数据中取50万行数据，就比如性别字段，那你相对需要 访问50万次索引，再访问50万次表，加起来的开销并不会比直接对表进行一次完整扫描小。

  4. text，image等类型不应该建立索引，这些列的数据量大；

  5. 多个单列索引并不是最佳选择 MySQL 只能使用一个索引，会从多个索引中选择一个限制最为严格的索引 ；

  6. 参加运算 或 作为函数参值等字段，不要建立索引，这会使得索引失效而全表扫描 。

4.主键与唯一索引有什么区别？

  • 主键一定会创建一个唯一索引，但是有唯一索引的列不一定是主键；

  • 主键不允许为空值，唯一索引列允许空值；

  • 一个表只能有一个主键，但是可以有多个唯一索引；

  • 主键可以被其他表引用为外键，唯一索引列不可以；

  • 主键是一种约束，而唯一索引是一种索引，是表的冗余数据结构，两者有本质差别。

5. b+树和b树有什么区别？

B树：

clip_image002

  • 索引与数据存储在每个节点中（增加了IO次数）；
  • 搜索过程有可能在非叶子节点结束（最好情况下O(1)O(1)）。

B+树：

clip_image039

  • 所有数据按顺序存储在叶子节点中；
  • 所有叶子节点被双向链连接；
  • 搜索过程固定时间复杂度（O(log⁡n)O(\log n)）；
  • （优点易忘）适合范围查找，降低磁盘IO次数。

6.为什么MySQL使用B+树作为索引？而不是平衡二叉树、红黑树、B树、或者Hash？

  • 不用平衡二叉树或者红黑树： 树的查询时间和树的结构有关，B+树是一颗多路搜索数，可以降低树的高度提高查询速度。

  • 不用B树： （1）无法范围查询，而B+树所有叶子节点形成有序链表便于范围查询；（2）N阶B+数，B树，B+数可以存储n个关键字，而B树是n个。

  • 不用Hash： （1）B+树允许分配加载节点，如果内存数据太大B+树更好（2）B+树更适合范围查询

    用Hash需要把数据全部加载到内存中，如果数据量大，是一件很消耗内存的事，而采用B+树，是基于按照节点分段加载，由此减少内存消耗。

6.1 无限增加树的路数是不是可以有最优的查找效率？

  • 这样会形成一个有序数组，文件系统和数据库的索引都是存在硬盘上的，并且如果数据量大的话，不一定能一次性加载到内存中。

  • 有序数组没法一次性加载进内存，这时候B+树的多路存储威力就出来了，可以每次加载B+树的一个结点，然后一步步往下找。

7. B+树怎么进行分裂、合并的？知道具体步骤吗？

参考：https://www.cnblogs.com/nullzx/p/8729425.htm

image-20210421124006795

8. MySQL聚簇索引和非聚簇索引的区别是什么？

  • 主要区别

    • 聚簇索引 ： 找到索引就找到了需要的数据，那么这个索引就是聚簇索引;

      所以Innodb主键就是聚簇索引，修改聚簇索引其实就是修改主键；但在Myisam下主键索引是非聚集索引。

      InnoDB 会隐式定义一个主键来作为聚簇索引（但是这个主键如果更改代价较高，故建表时要考虑自增ID不能频繁update这点）。

      img

    • 非聚簇索引：索引的存储和数据的存储是分离的 , 找到了索引但没找到数据，需要根据索引上的值(主键/地址)再次回表查询，也叫做辅助索引。

      MyISM使用的是非聚簇索引 ，下图叶节点的data域存放的是数据记录的地址 。

      img

  • 聚簇索引查找过程

    首先澄清一个概念，聚簇索引和和辅助索引。

    Innodb存储引擎的B+树分为，聚簇索引保存的是放着一整行的数据；辅助索引都引用主键作为data域。

    例如，下图以Col3建立一个Innodb辅助索引 ：

    img

    对于MyISAM，主索引和辅助索引（Secondary key）在结构上没有任何区别。只是主索引要求key是唯一的，而辅助索引的key可以重复。

    例如，下图以Col2建立一个MyISAM的辅助索引：

    img

    1
    Select * Where name=‘Alice’

    首先根据辅助索引 ，在叶子节点找到0X56对应主键值18 ；然后在聚簇索引，根据18 找到对应行数据。

8.1 看上去聚簇索引的效率明显要低于非聚簇索引，因为每次使用辅助索引检索都要经过两次B+树查找，这不是多此一举吗？聚簇索引的优势在哪？

  • 重复访问同一页更快：由于行数据和叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中，再次访问的时候，会在内存中完成访问，不必访问磁盘；

    B+树的每个节点的数量都是一个mysql分区页的大小(阿里面试) 。

  • 辅助索引使用主键值作为指针： 辅助索引使用主键作为"指针"而不是使用地址值作为指针（MyISAM） ，减少了当出现【行移动】或者数据页分裂时（地址值变化）辅助索引的维护工作 。

8.2 为什么name,age这些字段不适合做索引？

在InnoDB中每一个表都会有聚集索引，如果表定义了主键，则（默认）主键就是聚簇索引。

一个表只有一个聚集索引，其余为普通索引。所以name，age就是普通索引，也就是作为辅助索引。

在使用普通索引name查询时，会先加载普通索引：

（1）通过普通索引查询到实际行的主键

（2）再使用主键通过聚集索引查询相应的行

（3）以此循环查询所有的行
若直接全量搜索聚集索引，则不需要在普通索引和聚集索引中来回切换。相比两种操作的总开销可能扫描全表效率更高。

9. MySQL联合索引如何使用？什么是最左匹配原则？

参考：https://blog.csdn.net/Abysscarry/article/details/80792876

对多个字段同时建立的索引(有顺序，ABC，ACB是完全不同的两种联合索引。

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE `test` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `a` varchar(10) NOT NULL,
  `b` varchar(10) NOT NULL,
  `c` varchar(10) NOT NULL,
  `d` varchar(10) NOT NULL,
  `e` varchar(10) NOT NULL,
  PRIMARY KEY (`id`),
  # 联合索引(a,b,c)
  UNIQUE KEY `idx_a_b_c` (`a`,`b`,`c`) USING BTREE 
) ENGINE=I

以联合索引(a,b,c)为例：

  • 建立这样的索引相当于建立了索引a、ab、abc三个索引。一个索引顶三个索引当然是好事，毕竟每多一个索引，都会增加写操作的开销和磁盘空间的开销。

    a,c组合也可以但不是为他建立了索引，但实际上只用到了a的索引，c并没有用到！

  • 最左匹配原则

    最左优先，在检索数据时从联合索引的最左边开始匹配，即是where条件必须有联合索引的第一个字段。

    • 不包含第一个字段

      不会走索引

      1
      select  * from test where  b = '333' and c = '333';

    • where和索引顺序不一样

      会走索引，和顺序无关。

      where 后面列的顺序是被优化器优化了的，所以才会觉得无影响，你可以查看一下优化器优化后的语句，会帮你调整为满足最左前缀的顺序。

      1
      select  * from test where c = '333' and b = '333' and a = '333';
9.1 联合索引的好处？

10. （还要补充）介绍一下索引失效的原因？

很不错的一篇文章：索引失效原理，终于有人讲明白了

结合这篇文章：https://juejin.cn/post/6844904073955639304

假设mysql 存在联合索引（a,b），显然也是一颗B+树:

image-20210524114029113

  • 不符合最左匹配失效

    1. 情况1 ：select * from testTable where b=2

      联合索引是通过第一个索引a 来构建B+树，进行定位二分查找的，不能直接通过b 来进行二分查找。

    2. 情况2：like查询失效

      1
      2
      3
      4
      5
      where name like "a%"  # 会有效，因为可以匹配到首字母

      where name like "%a%" # 首字母是任意匹配的，所以索引用不上

      where name like "%a" # 同上，不能根据尾字母来索引

  • 范围查询失效

    1
    select * from testTable where a>1 and b=2

    首先a字段在B+树上是有序的，所以可以用二分查找法定位到1，然后将所有大于1的数据取出来，a可以用到索引。

    b有序的前提是a是确定的值，那么现在a的值是取大于1的，可能有10个大于1的a，也可能有一百个a。

    大于1的a那部分的B+树里，b字段是无序的（开局一张图），所以b不能在无序的B+树里用二分查找来查询，b用不到索引。

  • 多个单列索引

    我们首先建立了三个单列索引：userid，mobile，billMonth。然后使用以下查询语句：

    1
    EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE  userid='2222' AND mobile='13281899972' AND billMonth='2018-04'

    • explain 结果分析，最终只有userid 这一个索引有效。

      此处userid 、mobile 、billMonth这三个索引列都能用，只不过优化器判断使用userid这一个索引能最高效完成本次查询，故最终explain展示的key为userid。

    特别的，如果改为 or 进行判断：

    1
    EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE  userid='2222' OR mobile='13281899972'

    这里写图片描述

    • 此时两个索引 userid & mobile都用上了。

      mysql 5.0 版本之前 使用or只会用到一个索引, 自从5.0版本开始 ，引入了index_merge索引合并优化！也就是说，我们现在可以利用上多个索引去优化or查询了。

  • 或者索引加上了运算

    1
    select * from t where c - 1 = 1000;

    这种情况不会有效，应该是：

    1
    select * from t where c = 1000 + 1;

    又比如用了函数操作：

    1
    select * from t where pow(c,2) = 1000;

11. 【⚠️ 新增】索引实战篇

参考：https://zhuanlan.zhihu.com/p/115778804

  1. where a=xxx and b=xxx order by c ，如何建立索引？

    建立联合索引（a,b,c） ，因为这样的话无需做一次额外的排序操作。

    因为建立联合索引时，根据最左匹配原则，先按a排序，再按b排序，最后按c 排序。根据联合索引检索到，a=xxx and b=xxx条件时，再去检索c本身已排序的。那么不用 order by c ，不用进行文件排序。

    • 注意，建立联合索引 （a,b） , 则是走不到 sort的！

  2. where a=xxx order by b,c ，如何建立索引？

    道理同上，建立（a,b,c） 。

  3. where a > 1 ORDER BY b，怎么建立索引？

    建立索引（a）即可，因为a是范围查询：

    • 建立（a,b）的 话b也用不上，因为是a是范围查询；
    • 建立（b,a）, b按索引排好序，a>1可以用上？错误，因为先where筛选才会order by！

  4. where a=1 and b=2 and c>3 and d=4 ，如何建立索引？

    建立联合索引，(a,b,d,c) 即可。

    • 但是对于建立(a,b,c,d) ，a,b,c三个字段能用到索引，而d就匹配不到，因为遇到了范围查询，在c>3这个范围内，d不是有序的；但是c=3，这个范围内，d是有序的！

  5. where a > 1 and b = 2 and c > 3，如何建立索引？

    建立（b,c）或者（b,a）即可，但至少有一个索引要失效（遇上范围查询）。

  6. WHERE a IN (1,2,3) and b > 1，怎么建立索引？

    还是对(a，b)建立索引，因为IN在这里可以视为等值引用，不会中止索引匹配，所以还是(a,b)！

11.1 有个字段值可能正，可能负，查询该字段要绝对值，但计算会导致索引失效，怎么优化？
  1. 拆成两列1列只存绝对值，另起一列tinyint来表示正负或者方向；
  2. 先查询到结果，然后再查询的结果上进行操作（取绝对值）？

12. order by 怎么进行排序的？怎么优化？

参考：https://segmentfault.com/a/1190000040357292

假设存在以下数据：

image-20210904013336980

此时进行如下查询：

1
select city, order_num, user_code from `order` where city='广州' order by order_num limit 1000;

  1. 建立索引(city)：order by过程

    1
    ALTER TABLE `order` ADD INDEX city_index ( `city` );
    image-20210904013506155

    • 用 explain 看看执行情况

      img

      注意到最后一个 extra 字段的结果是：Using filesort，表示需要排序。其实 MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。

    • 完整执行过程

      image-20210904013732363

      1. 初始化 sort_buffer，放入 city、order_num、user_code 这三个字段；

      2. 从索引 city 找到第一个满足 city=’ 广州’条件的主键 id，也就是图中的 ID_3；

      3. 到主键 id 索引取出整行，取 city、order_num、user_code 三个字段的值，存入 sort_buffer 中；

      4. 从索引 city 取下一个记录的主键 id；

      5. 重复步骤 3、4 直到 city 的值不满足查询条件为止，对应的主键 id 也就是图中的 ID_X；

      6. 对 sort_buffer 中的数据按照字段 order_num 做快速排序；

        其中，按 order_num 排序这个步骤，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数 sort_buffer_size。

      7. 按照排序结果取前 1000 行返回给客户端。

  2. 优化1：使用rowid

    上面的全字段排序其实会有很大的问题，你可能发现了。我们需要查询的字段都要放到 sort_buffer 中，如果查询的字段多了起来，内存占用升高，就会很容易打满 sort_buffer 。

    • sort_buffer 只存 order_num 和 id 字段。

      img
      1. 初始化 sort_buffer，确定放入两个字段，即 order_num 和 id；
      2. 从索引 city 找到第一个满足 city=’ 广州’条件的主键 id，也就是图中的 ID_3；
      3. 回表，取 order_num、id 这两个字段，存入 sort_buffer 中；
      4. 从索引 city 取下一个记录的主键 id；
      5. 重复步骤 3、4 直到不满足 city=’ 广州’条件为止，也就是图中的 ID_X；
      6. 对 sort_buffer 中的数据按照字段 order_num 进行排序；
      7. 遍历排序结果，取前 1000 行，再次回表取出 city、order_num 和 user_code 三个字段返回给客户端。

  3. 优化2：建立联合索引

    参见索引部分解释，不再赘述。

    img

    1. 从索引 (city,order_num) 找到第一个满足 city=’ 广州’条件的主键 id；

    2. 回表，取 city、order_num、user_code 三个字段的值，作为结果集的一部分直接返回；

    3. 从索引 (city,order_num) 取下一个记录主键 id；

    4. 重复步骤 2、3，直到查到第 1000 条记录，或者是不满足 city=’ 广州’条件时循环结束。

5.3 事务相关

1.请介绍一下数据库事务？和事务特性（ACID）？

  • 数据库事务： 数据库事务指的是一组数据操作，事务内的操作要么就是全部成功，要么就是全部失败。

    e.g. 假设一个网购付款的操作，用户付款后要涉及到订单状态更新、扣库存以及其他一系列动作，这就是一个事务，如果一切正常那就相安无事，一旦中间有某个环节异常，那整个事务就要回滚，总不能更新了订单状态但是不扣库存吧。

  • 事务特性：（1） 原子性（Atomicity）：一个事务必须视为一个不可分割的最小工作单元，要么都成功要么都失败（2）一致性（Consistency）：数据库总能从一个一致性的状态转换到另一个一致性的状态（3）隔离性（Isolation）：一个事务所做的修改在最终提交以前，对其他事务是不可见的（4）持久性（Durability）：一旦事务提交，则其所做的修改就会永久保存到数据库中

1.1 四大特性得不到保障会出现什么情况？

我们以从A账户转账50元到B账户为例进行说明一下ACID这四大特性。

  • 原子性 : 原子性是指一个事务是一个不可分割的工作单位，其中的操作要么都做，要么都不做。即要么转账成功，要么转账失败，是不存在中间的状态。

    如果无法保证原子性会怎么样？

    会出现数据不一致的情形，A账户减去50元，而B账户增加50元操作失败。系统将无故丢失50元~

  • 一致性 ：一致性是指事务执行前后，数据处于一种合法的状态，这种状态是语义上的而不是语法上的。 那什么是合法的数据状态呢？这个状态是满足预定的约束就叫做合法的状态，再通俗一点，这状态是由你自己来定义的。满足这个状态，数据就是一致的，不满足这个状态，数据就是不一致的。

    如果无法保证一致性会怎么样？

    • 例一: A账户有200元，转账300元出去，此时A账户余额为-100元。

      你自然就发现了此时数据是不一致的，为什么呢？因为你定义了一个状态，余额这列必须大于0。

    • 例二：A账户200元，转账50元给B账户，A账户的钱扣了，但是B账户因为各种意外，余额并没有增加。

      你也知道此时数据是不一致的，为什么呢？因为你定义了一个状态，要求A+B的余额必须不变。

  • 隔离性 ：隔离性是指多个事务并发执行的时候，事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

    如果无法保证隔离性会怎么样？

    假设A账户有200元，B账户0元。A账户往B账户转账两次，金额为50元，分别在两个事务中执行。

    如果无法保证隔离性，A可能就会出现扣款两次的情形，而B只加款一次，凭空消失了50元，依然出现了数据不一致的情形！

  • 持久性 : 根据定义，持久性是指事务一旦提交，它对数据库的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

    如果无法保证持久性会怎么样？

    在MySQL中，为了解决CPU和磁盘速度不一致问题，MySQL是将磁盘上的数据加载到内存，对内存进行操作，然后再回写磁盘。好，假设此时宕机了，在内存中修改的数据全部丢失了，持久性就无法保证。

    设想一下，系统提示你转账成功。但是你发现金额没有发生任何改变，此时数据出现了不合法的数据状态，我们将这种状态认为是数据不一致的情形。

1.2 数据库如何保证四大特性（如何实现事务）？

数据库事务的 ACID 四大特性是事务的基础，了解了 ACID 是如何实现的，我们也就清楚了事务的实现，接下来我们将依次介绍数据库是如何实现这四个特性的。

  • 保证原子性

    主要是利用Innodb的undo log。

    undo log名为回滚日志，是实现原子性的关键，当事务回滚时能够撤销所有已经成功执行的sql语句，他需要记录你要回滚的相应日志信息。

    例如

    • (1)当你delete一条数据的时候，就需要记录这条数据的信息，回滚的时候，insert这条旧数据
    • (2)当你update一条数据的时候，就需要记录之前的旧值，回滚的时候，根据旧值执行update操作
    • (3)当年insert一条数据的时候，就需要这条记录的主键，回滚的时候，根据主键执行delete操

    undo log记录了这些回滚需要的信息，当事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

  • 保证一致性

    数据库对于 ACID 中的一致性的定义是这样的：如果一个事务原子地在一个一致地数据库中独立运行，那么在它执行之后，数据库的状态一定是一致的。

    • 它的第一层意思就是对于数据完整性的约束，包括主键约束、引用约束以及一些约束检查等等，在事务的执行的前后以及过程中不会违背对数据完整性的约束，所有对数据库写入的操作都应该是合法的，并不能产生不合法的数据状态。
    • 第二层意思其实是指逻辑上的对于开发者的要求，我们要在代码中写出正确的事务逻辑，比如银行转账，事务中的逻辑不可能只扣钱或者只加钱，这是应用层面上对于数据库一致性的要求。

  • 保证隔离性

    在 SQL 标准中定义了四种数据库的事务的隔离级别：READ UNCOMMITED、READ COMMITED、REPEATABLE READ 和 SERIALIZABLE；

    数据库对于隔离级别的实现就是使用并发控制机制对在同一时间执行的事务进行控制：

    1. 锁： MySQL 和常见数据库中的锁都分为两种，共享锁（Shared）和互斥锁（Exclusive），前者也叫读锁，后者叫写锁。

    2. mvcc实现：实现RC和RR。

  • 保证持久性

    利用Innodb的redo log，为什么要使用redo log，请看下面：

    • 正如之前说的，Mysql是把磁盘上的数据先加载到内存中，在内存中对数据进行修改，再刷回磁盘上。如果此时突然宕机，内存中的数据就会丢失；
    • 如果事务提交前直接把数据写入磁盘，只修改一个页面里的一个字节，就要将整个页面刷入磁盘，太浪费资源了。

    redo log解决方案：

    • 当做数据修改的时候，不仅在内存中操作，还会在redo log中记录这次操作；
    • 当事务提交的时候，会将redo log日志进行刷盘(redo log一部分在内存中，一部分在磁盘上)；
    • 当数据库宕机重启的时候，会将redo log中的内容恢复到数据库中，再根据undo log和binlog内容决定回滚数据还是提交数据。
1.3 binlog日志是做什么的？

参考：https://www.cnblogs.com/kevingrace/p/5907254.html

MySQL的二进制日志binlog可以说是MySQL最重要的日志，它记录了所有的DDL和DML语句（除了数据查询语句select），以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。

DDL ，即 Data Definition Language 数据库定义语言。

  • 主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用。

DML，即Data Manipulation Language 数据操纵语言。

  • 主要的命令是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言。

binlog日志有两个最重要的使用场景：

  1. MySQL主从复制：MySQL Replication在Master端开启binlog，Master把它的二进制日志传递给slaves来达到
    master-slave数据一致的目的。
  2. 自然就是数据恢复了，通过使用mysqlbinlog工具来使恢复数据。

2.介绍一下（并发）事务不同隔离级别？对应可能出现的问题？

参考：https://www.cnblogs.com/shan-kylin/p/9543294.html

下面排它锁和共享锁都是行锁，锁住一行。

  • Read uncommitted： 顾名思义，就是一个事务可以读取另一个未提交事务的数据。但这种情况会出现脏读： 因为写数据的时候添加一个X锁（排他锁，拿到锁的事务可以写，其它事务只能等待阻塞）；但读不受限制，读不加锁。

    由于读不加锁，读的是可能是修改前（未提交）的数据，也就是脏读。

  • Read Committed ：写数据的时候加上X锁（排他锁），读数据的时候添加S锁（共享锁，拿到锁的事务可以查看但无法修改和删除，其它事务【只能加S锁】【不能加其它锁】如X锁）。解决脏读，但会导致出现不可重复读的问题。

    1. 初始x=100,；
    2. 事务A读x，加上S锁，读到x=100，立即释放S锁且未提交；
    3. 事务B修改x，加上X锁，修改x=50，提交 ；
    4. 事务A再次读取x，加上S锁，读取x=50，发现和第一次读取不一致，提交事务。

    上述过程，事务A在提交前读取两次不一样的x值，为不可重复读。

  • Repeatable read ：MySQL 默认隔离界别 , 开始读取数据（事务开启）时，【S】锁不再立即释放，其它事务就不能加上【X】锁修改数据。解决了脏读、不可重复读，但是可能会出现幻读：-

    • Mysql官方给出的幻读解释是：只要在一个事务中，第二次select（读）多出了row就算幻读。

    现在做如下修改：

    • 写：X锁保持不变
    • 读：S锁，读完不再立即释放，而是在提交的时候再释放。

    这样的话，保证事务A在提交前，读取的x是一致的。解决了不可重复读，但依旧可能出现幻读。例如，X锁不能阻止（表中其它记录）插入操作，因为只锁住了当前记录，是行级锁。

    1. 事务A加上X锁，更新了所有用户年龄从20→18；
    2. 过一会儿再读发现还有一个用户没修改，还是20岁？出现幻觉了吗？

    这是因为，其它事务B同时插入了一行新数据，年龄就是20岁。所以再读发现没修改。

  • Serializable： 序列化，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。耗费性能，一般不用。

2.1【猿辅导】 怎么解决幻读？

解决幻读使用两种方式：

  1. 间隙锁

    • 间隙锁（Gap Lock）：当我们用【范围条件】而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁；

    • 举例： 假如user表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：

      1
      select * from  user where user_id > 99 for update;

      是一个范围条件的检索，InnoDB不仅会对（1）符合条件的user_id值（100、101）的记录加锁，也会对（2）user_id大于101（这些记录并不存在）的“间隙”加锁。

    • 如何解决幻读？ 产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”，导致下次读取产生幻读。如果锁住了间隙，自然也就没锁的问题了。

  2. mvvc

    在RR（可重复读级别下）不会出现幻读。例如：

    1. 开启事务1，获得事务ID为1。

    2. 事务1执行查询，得到readview。

    3. 开始事务2。

    4. 执行insert。

    5. 提交事务2。

    6. 执行事务1的第二次查询 (因为这里是RR级别，所以不会再去获得readview，还是使用第一次获得的readview，所以当前read view 事务的ID不会更新，即不会加入插入事务2)

    7. 最后得到的结果是，插入的数据不会被读取显示，因为插入的数据事务ID > readview里的最大活跃事务ID。

5.4 MySQ优化

0. 分页查询慢的原因？如何优化？

参考：https://juejin.cn/post/6844904141878214664

在偏移量非常大的时候，也就是翻页到很靠后的页面时，查询速度会变得越来越慢，例如：

1
select * from t_order order by id limit 1000000000, 10;

这是因为，需要查询 100000000010 (100000000000 + 10) 条记录，然后只返回最后 10 条，并将前面的 100000000000 条记录抛弃，这样当翻页越靠后时，代价就变得越来越高。

优化方法：

  1. 最大id法

    • 举例：查询第一页的时候是limit 400000,10 查询到的最后一条id是4000000，那么下一页的查询只需要查询id大于400000的10条数据即可。

    • 语句：

      1
      explain select * from user where id > 4000000  limit 10;

  2. BETWEEN … AND

    1
    select * from user where id BETWEEN 4000000 and 4000010

  3. 分表查询

    mysql推荐一张表的存储不要超过500w数据，查询400w不到1秒对于一般的查询来说已经可以了，如果还要更快的话，我建议使用分表存储，分表又分两种情况，水平分表于垂直分表。

  4. 延迟关联（个人推荐）

    1
    select * from table a,(select id from table limit 100000,20) b on a.id=b.id

1. SQL优化手段有哪些？

  1. 查询语句中不要使用select

  2. 尽量减少子查询，使用关联查询（left join,right join,inner join）替代

  3. 减少使用IN或者NOT IN ,使用exists，not exists或者关联查询语句替代(在确认没有重复数据或者不用剔除重复数据时，union all会更好)

  4. 应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

  5. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： select id from t where num=0

2.请问如何防止SQL被注入？

  • 什么是SQL注入？

    攻击者在HTTP请求中注入恶意的SQL代码，服务器使用参数构建数据库SQL命令时，恶意SQL被一起构造，并在数据库中执行。

    • 例如：用户登录，攻击者输入：用户名 = liangzone，密码 = ‘ or ‘1’=’1 ，那么拼接后的语句是：

      1
      select * from user where name = ‘lianggzone’ and password = ‘’ or ‘1’=‘1’

      攻击者就可以查询出所有的用户表信息！

  • 如何防范SQL注入？

    • Web端：1）有效性检验； 2）限制字符串输入的长度
    • 服务器端：1）不用拼接SQL字符串， 2）使用预编译的PrepareStatement， 3）有效性检验，4）过滤SQL需要的参数中的特殊字符，比如单引号、双引号。

3. MySQL 如何做到高并发解决方案？

  1. 在web服务框架中加入缓存。在服务器与数据库层之间加入缓存层，将高频访问的数据存入缓存中，减少数据库的读取负担。
  2. 增加数据库索引，进而提高查询速度。（不过索引太多会导致速度变慢，并且数据库的写入会导致索引的更新，也会导致速度变慢）
  3. 主从读写分离分库，让主服务器负责写，从服务器负责读。
  4. 将数据库表进行拆分表，使得数据库的表尽可能小，提高查询的速度。
  5. 使用分布式架构，分散计算压力。

4. 大表如何进行优化？

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

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

  • 读/写分离：经典的数据库拆分⽅案，主库负责写，从库负责读；

  • 垂直分区：根据数据库⾥⾯数据表的相关性进行拆分。

    例如，⽤户表中既有⽤户的登录信息⼜有⽤户的基本信息，可以将⽤户表拆分成两个单独的表，甚⾄放到单独的库做分库。

    • 简单来说垂直拆分是指数据表列的拆分，把⼀张列⽐较多的表拆分为多张表。

      image-20210516234359675

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

其它数据库结构方面优化：

  • 范式优化： 比如消除冗余（节省空间。。）
  • 反范式优化：比如适当加冗余等（减少join）

5. 数据库如何去重？

参考：MySQL数据库行去重复和列去重复

5.1 行去重

存在行重复，则无法建立 唯一索引 等。

用distinct 得到我们要保留的数据也是可以的。

下面假设的是email字段重复。

  1. 查看我们的分组后

    1
    select *,count(id) from demo_table group by email having count(id)>1 order by id;

    执行结果：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    +----+------+--------------+-----------+
    | id | name | email        | count(id) |
    +----+------+--------------+-----------+
    |  1 | u1   | u1@email.com |         4 |
    |  2 | u2   | u2@email.com |         4 |
    |  3 | u3   | u3@email.com |         3 |
    |  4 | u4   | u4@email.com |         2 |
    |  5 | u5   | u5@email.com |         2 |
    +----+------+--------------+-----------+
    5 rows in set (0.00 sec)

  2. 保留每个分组最小的id，表中其余都删除

    在表中删除不符合要求的数据：

    1
    delete from demo_table where id not in (select min(id) from demo_table group by email);

    但是似乎有保护机制，无法直接删除：

    1
    ERROR 1093 (HY000): You can't specify target table 'demo_table' for update in FROM clause

  3. 创建临时表再删除

    1
    2
    3
    4
    5
    6
    7
    8
    # 创建临时表保存每个分组中最小的那个id
    create table tmp_table as select min(id) from demo_table group by email;

    # 删除表中数据
    delete from demo_table where id not in (select * from tmp_table);

    # 删除临时表
    drop table tmp_table;
5.2 列重复

  1. 先找到重复字段的行

    1
    select * from demo_table as a, demo_table as b where a.id=b.id and a.name=b.email;

    执行结果：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    +----+--------------+--------------+----+--------------+--------------+
    | id | name         | email        | id | name         | email        |
    +----+--------------+--------------+----+--------------+--------------+
    | 31 | 31@email.com | 31@email.com | 31 | 31@email.com | 31@email.com |
    | 32 | 32@email.com | 32@email.com | 32 | 32@email.com | 32@email.com |
    | 33 | 33@email.com | 33@email.com | 33 | 33@email.com | 33@email.com |
    | 34 | 34@email.com | 34@email.com | 34 | 34@email.com | 34@email.com |
    | 35 | 35@email.com | 35@email.com | 35 | 35@email.com | 35@email.com |
    +----+--------------+--------------+----+--------------+--------------+
    5 rows in set (0.00 sec)

    重复记录的id都已经找出来了，删除就参考上述方式处理了。

6. select慢的原因？慢查询的优化策略？

6.1 select 很慢的原因

参考：腾讯面试：一条SQL语句执行得很慢的原因有哪些?

  • 如果是偶尔很慢

    针对这种情况，这条SQL语句的书写本身是没什么问题的。可能是其它原因导致：

    1. 被加锁。 要执行的这条语句，刚好这条语句涉及到的表，别人在用，并且加锁了，我们拿不到锁，只能慢慢等待别人释放锁了；或者，表没有加锁，但要使用到的某个一行被加锁了 。
    2. 数据库在刷新脏页 ？ 看链接，不太明白流程。

  • 如果是经常很慢

    1. 没用到索引

    2. 索引失效了 ：

      （1）联合索引不满足最左匹配（不包含第一个索引）；

      （2）like语句不满足最左匹配（不包含第一个索引）

      （3）或者索引加上了运算

      1
      select * from t where c - 1 = 1000;

      这种情况不会有效，应该是：

      1
      select * from t where c = 1000 + 1;

      （4）或者函数操作用上索引

      1
      select * from t where pow(c,2) = 1000;

    3. 数据库使用非聚簇索引

      我们在进行查询操作的时候，例如：

      1
      select * from t where 100 < c and c < 100000;

      此时使用的非聚簇索引（辅助索引），只是存储了主键的key ，还是要再次通过聚簇索引去查找。效率可能不必全表扫描高。

7. select…for update用法？

数据库-MySQL中for update的作用和用法

for update是一种行级锁，又叫排它锁。

一旦用户对某个行施加了行级加锁，则该用户可以查询也可以更新被加锁的数据行，其它用户只能查询但不能更新被加锁的数据行。

1
2
3
4
5
6
-- 开启事务
begin;
SELECT * FROM user WHERE id=3 FOR UPDATE;
SELECT * FROM user WHERE id=3 and name='Tom' FOR UPDATE;
-- 提交事务
commit;

8. 场景题：假如选择MySQL数据库，一天五万条增量，运维三年，有哪些优化手段？

  1. 设计良好的数据库结构，允许部分数据冗余，尽量避免join查询，提高效率；
  2. 选择合适的表字段数据类型和存储引擎，适当的添加索引；
  3. MySQL库主从分库读写分离；
  4. 找规律分表，减少单表中的数据量提高查询速度；
  5. 添加缓存机制，比如Memcached，Apc等；
  6. 书写高效率的SQL。比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE。