面试-MySQL

1. 优化

1.1 定位慢查询

  • 当对接口做压测时候，接口响应时间超过指定时间就可以认定为慢查询。

  • 运维监控：Skywalking

  • MySQL****自带慢日志

    • 如果要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

      1
      2
      3
      # 开启MySQL慢日志查询开关
      slow_query_log=1
      # 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志long_query_time=2 (默认为10s)

    • 配置完毕之后，通过重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息（/var/lib/mysql/localhost-slow.log。）

1.2 SQL执行计划

  • 可以采用EXPLAIN获取 MySQL 如何执行 SELECT 语句的信息。

    1
    2
    /**直接在select语句之前加上关键字 explain / desc*/
    EXPLAIN   SELECT   字段列表   FROM   表名   WHERE  条件 ;

    image-20240503101602476

  • 字段解释：

    • type 这条sql的连接的类型，性能由好到差为NULL、system、const、eq_ref、ref、range、 index、all
      • system：查询系统中的表
      • const：根据主键查询
      • eq_ref：主键索引查询或唯一索引查询
      • ref：索引查询
      • range：范围查询
      • index：索引树扫描
      • all：全盘扫描
    • possible_key 当前sql可能会使用到的索引
    • key 当前sql实际命中的索引
    • key_len 索引占用的大小
    • Extra 额外的优化建议
      • Using where; Using Index：查找使用了索引，需要的数据都在索引列中能找到，不需要回表查询数据
      • Using index condition：查找使用了索引，但是需要回表查询数据

总结：使用explain

  • 通过key和key_len检查是否命中了索引（索引本身存在是否有失效的情况）
  • 通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描
  • 通过extra建议判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复

1.3 索引

1.3.1 存储引擎

  • 存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式 。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。

  • 不同存储引擎区别：

    image-20240503103743368

1.3.1.1 MySQL体系结构

image-20230409010508341

  • 连接层：最上层是一些客户端和链接服务，包含本地 sock 通信和大多数基于客户端/服务端工具实现的类似于 TCP/IP的通信。
  • 服务层：在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的顺序，是否利用索引等， 最后生成相应的执行操作。
  • 引擎层：数据库中的索引是在存储引擎层实现的。
  • 存储层：要是将数据**(如: redolog、undolog、数据、索引、二进制日志、错误日志、查询 日志、慢查询日志等)存储在文件系统之上，并完成与存储引擎的交互。

1.3.1.2 Innodb存储特点

  • InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB****是默认的 MySQL 存储引擎。
  • DML操作遵循ACID模型，支持事务。
  • 提高并发访问性能。
  • 支持FOREIGN KEY约束，保证数据的完整性和正确性。
  • xxx.ibd：xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引。
  • xxx.frm 存储表结构（MySQL8.0时，合并在表名.ibd中）

1.3.1.3 逻辑存储结构

image-20230409221419123

  • 表空间 : InnoDB存储引擎逻辑结构的最高层，ibd文件其实就是表空间文件，在表空间中可以包含多个Segment段。
  • 段 : 表空间是由各个段组成的， 常见的段有数据段、索引段、回滚段等。InnoDB中对于段的 理，都是引擎自身完成，不需要人为对其控制，一个段中包含多个区。
  • 区 : 区是表空间的单元结构，每个区的大小为1M。 默认情况下， InnoDB存储引擎页大小为 16K， 即一个区中一共有64个连续的页。
  • 页 : 页是组成区的最小单元，页也是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。
  • 行 : InnoDB 存储引擎是面向行的，也就是说数据是按行进行存放的，在每一行中除了定义表时所指定的字段以外，还包含两个隐藏字段。

1.3.2 什么是索引

  • 索引（index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构（B+树），这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

1.3.3 索引底层结构

  • MySQL默认使用的索引底层数据结构是B+树。

  • B树是一种多叉路衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。

    • 以一颗最大度数（max-degree）为5(5阶)的b-tree为例，那这个B树（每个节点最多存储4个key，5个指针）

    image-20230410224950819

  • B+Tree是在BTree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构

    image-20230410230124036

  • 在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能，利于排序。

    image-20230410230844291

    • B树中非叶子节点也会存储数据，B+树叶子节点才会存储数据
    • 使用链表将所有叶子节点连接，这样形成带顺序的指针，可以提高范围查找速度。

1.3.4 聚簇和非聚簇索引

  • 聚簇索引：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据。必须有且只有一个。
  • 非举簇索引：将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键。可以没有，也可以有多个。

1.3.4.1 聚簇索引选取规则

  • 如果存在主键，主键索引就是聚集索引。
  • 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
  • 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

image-20240503105336583

  • 有一个tb_user，id作为主键，创建了一个index_user_name索引。

  • 那么id就是聚簇索引，index_user_name就是非聚簇索引。

  • select * from user where name = ‘Arm’;

    image-20240503105554055

    此时会使用index_user_name索引，查询得到id为10，进行回表查询，最后得到行数据。

1.3.5 索引覆盖

  • 覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到 。

    image-20240503105758103

  • 所以在查询的时候尽量少使用select *

1.3.5.1 MySQL超大分页处理

  • 在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。

    image-20240503110016469

  • 优化思路: 一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

    1
    2
    3
    4
    5
    select *
    from tb_sku t,     
    	(select id from tb_sku order by id limit 9000000,10) a
    where 
    t.id = a.id;

  • 在数据量比较大时，limit分页查询，需要对数据进行排序，效率低；解决方案：覆盖索引+子查询

1.3.6 索引创建原则

  • 针对于数据量较大，且查询比较频繁的表建立索引。
  • 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
  • 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
  • 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引（此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。）。
  • 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
  • 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
  • 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

1.3.7 索引失效

  • 索引列运算

  • 字符串不加引号

  • 模糊查询：如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

  • 范围查询右边的列，不能使用索引 。即：> <在进行范围查询的时候，右边的列不会用到索引。

    image-20240503114936277

  • 数据分布影响

1.4 SQL优化

  • 表的设计优化（参考阿里开发手册《嵩山版》）
    • 比如设置合适的数值（tinyint int bigint），要根据实际情况选择
    • 比如设置合适的字符串类型（char和varchar）char定长效率高，varchar可变长度，效率稍低
  • 索引优化
  • SQL语句优化
    • SELECT语句务必指明字段名称（避免直接使用select * ）
    • SQL语句要避免造成索引失效的写法
    • 尽量用union all代替union union会多一次过滤，效率低
    • 避免在where子句中对字段进行表达式操作
    • Join优化 能用innerjoin 就不用left join right join，如必须使用 一定要以小表为驱动，内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边。left join 或 right join，不会重新调整顺序

2. 事务

  • 事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

2.1 事务特性

  • 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
  • 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
  • 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
  • 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

2.2 隔离级别

2.2.1 并发事务问题

  • 脏读：一个事务读到另外一个事务还没有提交的数据。
  • 不可重复读： 一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
  • 幻读： 一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了”幻影”。

2.2.2 隔离级别

  • 针对并发事务问题有四种解决方案
  • Read uncommitted 未提交读：无法避免脏读、不可重复读、幻读。
  • Read committed 读已提交：只能避免脏读
  • Repeatable Read(默认) 可重复读：无法避免幻读。
  • Serializable 串行化：都可以解决

事务隔离级别越高，数据越安全，但是性能越低

2.3 MVCC

  • 缓冲池（buffer pool）:主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度**
  • 数据页（page）:是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。页中存储的是行数据

image-20240503120544723

2.3.1 redo log

  • 重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。

  • 该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中, 用于在刷新脏页到磁盘,发生错误时, 进行数据恢复使用。

    image-20240503120647750

2.3.2 undo log

  • 回滚日志，用于记录数据被修改前的信息 , 作用包含两个 : 提供回滚 和 MVCC(多版本并发控制) 。undo log和redo log记录物理日志不一样，它是逻辑日志。
  • 可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，
  • 当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。
  • 当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。
  • 而update、delete的时候，产生的undo log日志不仅在回滚时需要，mvcc版本访问也需要，不会立即被删除。
  • undo log可以实现事务的一致性和原子性

2.3.3 MVCC（多版本并发控制）

  • 指维护一个数据的多个版本，使得读写操作没有冲突。
  • MVCC的具体实现，主要依赖于数据库记录中的隐式字段、undo log日志、readView。

2.3.3.1 隐式字段

image-20240503121049428

  • 目前有一个user表，里面字段为id、age、name。
  • 但是隐藏了几个字段：
    • DB_TRX_ID：最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID，初始值为1。
    • DB_ROLL_PTR：回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本。
    • DB_ROW_ID：隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。
undo log版本链

image-20240503121407240

  • 此时有五个事务对user表进行操作，事务5分别在不同时间查询id为30的记录。

  • undo log链最终样子：

    image-20240503121538617

    • 在事务1进行操作的时候，在undo log中生成一条记录，此时原数据中隐藏字段ROLL_PTR指向这条记录，隐藏字段TRX_ID设置为2。
    • 在事务2进行操作的时候，undo log再生成一条记录，该记录的ROLL_PTR指向之前的记录，并且将原数据的隐藏字段ROLL_PTR指向新生成的记录，隐藏字段TRX_ID设置为3。
    • 在事务2进行操作的时候，undo log再生成一条记录，该记录的ROLL_PTR指向之前的记录，并且将原数据的隐藏字段ROLL_PTR指向新生成的记录，隐藏字段TRX_ID设置为4。

不同事务或相同事务对同一条记录进行修改，会导致该记录的undolog生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。

2.3.3.2 readView

  • ReadView（读视图）是 快照读 SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id。
  • 当前读：读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如：select … lock in share mode(共享锁)，select … for update、update、insert、delete(排他锁)都是一种当前读。
  • 快照读：简单的select（不加锁）就是快照读；快照读读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。
    • Read Committed：每次select，都生成一个快照读。
    • Repeatable Read：开启事务后第一个select语句才是快照读的地方。
四个字段
  • m_ids：当前活跃的事务ID集合
  • min_trx_id：最小活跃事务ID
  • max_trx_id：预分配事务ID，当前最大事务ID+1（因为事务ID是自增的）
  • creator_trx_id：ReadView创建者的事务ID
读取规则（了解）

image-20240503122420249

生成时机
  • 在不同的隔离级别下生成ReadView的时机不同：
    • READ COMMITTED ：在事务中每一次执行快照读时生成ReadView。
    • REPEATABLE READ：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。
RC
  • RC隔离级别下，在事务中每一次执行快照读时生成ReadView。

image-20240503122605886

  • 事务5在第一次查询记录的时候，记录中的TRX_ID为4、3、2、1，根据读取规则，4不满足读取规则、3也不满足读取规则、2满足读取规则，因此在第一次查询的时候，读取的是TRX_ID为2的数据，拿到的结果就是{id=30,age=3,name=A30}

    image-20240503122740915

  • 事务5在第二次查询记录的时候，记录中的TRX_ID为4、3、2、1，根据读取规则，4不满足读取规则，3满足读取规则，因此最终得到的结果就是{id=30，age=3,name=A3}

RR
  • RR隔离级别下，仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。
  • 那么事务5的两次查询只会共用第一次执行快照读的时候生成ReadView。

image-20240503123434142

  • 因此两次查询出来的结果都一样为：{id=30,age=3,name=A30}

3. 主从同步

  • 主从同步的核心就是二进制日志（bin log）

  • 二进制日志（BINLOG）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但不包括数据查询（SELECT、SHOW）语句。

  • 主从同步分为三步完成：

    • Master 主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中。
    • 从库读取主库的二进制日志文件 Binlog ，写入到从库的中继日志 Relay Log 。
    • slave重做中继日志中的事件，将改变反映它自己的数据。

    image-20240503123756292

4. 分库分表

  • 主从同步解决了访问压力，但是没有解决存储压力。
  • 分库分表的时机：
    • 项目业务数据逐渐增多，或业务发展比较迅速（单表的数据量达1000W或20G以后）
    • 优化已解决不了性能问题（主从读写分离、查询索引…）
    • IO瓶颈（磁盘IO、网络IO）、CPU瓶颈（聚合查询、连接数太多）

4.1 垂直拆分

4.1.1 垂直分库

  • 以表为依据，根据业务将不同表拆分到不同库中。（典型案例：微服务是按照功能模块进行拆分的，对应的数据库就是跟模块）

  • 特点：

    • 按业务对数据分级管理、维护、监控、扩展
    • 在高并发下，提高磁盘IO和数据量连接数

4.1.2 垂直分表

  • 以字段为依据，根据字段属性将不同字段拆分到不同表中。
  • 拆分规则：
    • 把不常用的字段单独放在一张表。
    • 把text，blob等大字段拆分出来放在附表中。
  • 特点：
    • 冷热数据分离。
    • 减少IO过渡争抢，两表互不影响

4.2 水平拆分

4.2.1 水平分库

  • 将一个库的数据拆分到多个库中。
  • 特点：
    • 解决了单库大数量，高并发的性能瓶颈问题
    • 提高了系统的稳定性和可用性
  • 路由规则：
    • 根据id取模
    • 范围路由

4.2.2 水平分表

  • 将一个表的数据拆分到多个表中(可以在同一个库内)。
  • 特点：
    • 优化单一表数据量过大而产生的性能问题;
    • 避免IO争抢并减少锁表的几率;

4.2.3 中间件

  • 水平分库分表之后会带来一些问题：
    • 分布式事务一致性问题
    • 跨节点关联查询
    • 跨节点分页、排序函数
    • 主键避重
  • 解决：使用分库分表中间件（lsharding-sphere、mycat）
#面试
面试-MySQL
https://baijianglai.cn/面试-MySQL/ac030fe2dab2/
作者
Lai Baijiang
发布于
2024年5月3日
许可协议