第三章--MySQL重点面试题-CSDN博客

本文链接：https://blog.csdn.net/Kaka_csdn14/article/details/130876973

写在开始 : 本篇博客仅仅用作个人知识点复习用~

1. MySQL优化

1. 1 定位慢查询

当时做压测的时候有的接口非常慢,响应时间在2s以上,然后系统有部署运维的监控系统 Skywalking,在展示的报表可以定位到哪个接口比较慢,也可以分析这个接口哪部分比较慢,能看到SQL的具体执行时间,就可以定位哪个sql出问题;
假如项目没有类似的运维监控系统,在MySQL里也有慢日志查询功能,首先在 MySQL 的系统配置文件开启这个慢日志功能,也可以设置 SQL 执行超过多少时间来记录到一个日志文件,比如配置2s,只要sql执行时间超过2s就会被记录到日志文件中,那就可以在日志文件找到执行慢的SQL了;

**可能原因 : **

聚合查询 ->新增临时表解决 —>SQL执行计划(找到慢的原因)
多表查询 ->优化SQL语句结构 —>SQL执行计划(找到慢的原因)
表数据量过大查询 -> 添加索引 —>SQL执行计划(找到慢的原因)
深度分页查询 ->覆盖索引

覆盖索引指查询使用索引,返回的列,必须在索引中全部能找到
使用id查询,直接走聚集,一次索引扫描,直接返回数据,性能高;
如果返回的列没有建索引,可能触发回表查询,尽量避免用 Select *;

:::info

Q : 数据量大时,limit分页查询,需要对数据排序,效率低(超大分页如何处理)
A : 覆盖索引 + 子查询

:::
:::info

覆盖索引，最左匹配原则时优化查询的常见思路
最左匹配：先匹配最左边的，索引只能⽤于查找key是否存在（相等），遇到范围查询 (>、<、between、like左匹配)等就不能进⼀步匹配了，后续退化为线性查找
从最左边为起点开始连续匹配，遇到范围查询终止；

:::

-- 先分页查询数据的 id 字段,确定id 后,再用子查询过滤,只查询这个 id 列表的数据就可以,因为
-- 查询 id 时候,走覆盖索引,所以效率可以提升很多
select * from t_sku limit 0,10;    (0.00sec)
select * from t_sku limit 9000000,10;  ( 11.05sec)
-- 优化 :  
select * from t_sku t,(select id from t_sku order by id limit 9000000,10) a 
where t.id = a.id;

表象 ; 页面加载慢,接口压测响应时间长( 超过 1 s )
**工具 : **

阿尔萨斯调试工具
运维工具 : 普罗米修斯 Skywalking

MySQL自带慢日志

MySQL 定位慢查询的步骤如下：

开启查询日志，在my.cnf配置文件中将slow_query_log参数设置为ON，并设置slow_query_log_file指定日志文件的路径。

设置慢查询阈值，在my.cnf配置文件中将long_query_time参数设置为一个较小的值，通常为2秒到5秒，以便记录数据库查询速度慢的SQL语句。

重启 MySQL 服务，以使配置文件的修改生效。

分析查询日志文件，查看哪些 SQL 语句运行时间较长；可以使用mysqldumpslow工具快速统计查询日志。

使用EXPLAIN命令分析慢查询语句的执行计划，查看是否有索引失效、全表扫描等性能瓶颈。

根据执行计划的分析结果，优化 SQL 语句或添加合适索引来提升查询性能。

1.2 SQL执行计划

Q : SQL 语句执行很慢,如何分析?
A : 通常使用mysql自带的执行计划 explain查看这条sql的执行情况

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

分析 : 语句前面加 explain 或者 desc;

-- SQL执行计划（Execution plan）是MySQL为查询语句生成的一种查询方式，也称为查询计划或查询方案。
-- 它表示MySQL的优化器选择了哪些索引、表关联方式、join操作等，以处理SQL查询语句，从而获得结果集。
-- SQL执行计划对于数据库调优和性能优化非常重要。

-- 在MySQL中，使用`EXPLAIN`关键字可以获得SQL语句的执行计划，其基本语法如下：


EXPLAIN SELECT * FROM table_name WHERE condition ORDER BY column_name LIMIT num;


-- 其中，`SELECT`后面的语句可以是任何合法的SELECT语句，可以包含WHERE、GROUP BY、HAVING、
-- ORDER BY子句。使用`EXPLAIN`关键字时，MySQL会解析SQL语句，并返回一张表格，表示SQL语句
-- 将如何执行。执行计划表包含以下列：

-- `id`: 执行的操作标识符，从1开始递增，每个操作有唯一的标识符。
-- `select_type`: 查询类型，包括简单查询、联合查询、子查询等类型。
-- `table`: 表的名称。

-- `type`: 访问类型，MySQL查询优化器选择的访问方法，具体包括以下几种：ALL（全表扫描）、
-- index（索引扫描）、range（索引范围扫描）、ref（使用非唯一索引扫描）、eq_ref（使用唯一索引等值匹配）、
-- const（使用PRIMARY KEY或UNIQUE索引进行匹配），以及system和null（这两种类型比较特殊）。

-- `possible_keys`: 可能使用的索引。
-- `key`: 实际使用的索引。
-- `key_len`: 使用的索引长度。
-- `ref`: 列（或常量）与索引之间的匹配方式。
-- `rows`: MySQL估计从表中检索出的行数。
-- `Extra`:表示MySQL在执行查询时使用了额外的一些操作，如使用了临时表、使用了文件排序等。

-- 通过分析SQL执行计划，可以了解MySQL使用的索引、访问表的方式、查询的优化情况和性能瓶颈等信息，
-- 从而进行调优优化。

1.3 索引

储存引擎

MySQL 默认存储引擎 InnoDB 采用B+树的数据结构存储索引,原因有:

阶数更多,路径更短;
磁盘读写代价 B+ 树更低,非叶子节点只存储指针,叶子节点储存数据
B+树便于扫库和区间查询,叶子节点是一个双向链表

InnoDB 是MySQL5.5之后的默认储存引擎,

支持行级锁和表级锁,默认行级锁;
支持事务和崩溃后的安全恢复;具有事务,回滚和崩溃修复能力和事务安全型表
支持外键
支持 MVCC ,应对高并发事务,MVCC比单纯加锁更高效,只在 RC 和 RR 两个隔离级别工作; 可以使用乐观和悲观锁实现;
四大特性：插入缓冲（insert buffer）二次写(double write) 自适应哈希索引(ahi) 预读(read ahead)

索引底层数据结构

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

帮助MySQL高效获取数据的数据结构;
主要用来提高数据检索效率,降低数据库IO成本,(不需要全表扫描)
同时通过索引列对数据进行排序,降低数据排序成本,降低了CPU消耗

B 和B+区别

B树,非叶子和叶子节点都存放数据,B+数据出现在叶子节点,查询时候,B+查找效率更加稳定;B+树是多路搜索树，树的层级更低（检索更快），B+树更加矮壮；
进行范围查询时,B+树效率更高,因为数据都存在叶子节点,并且叶子节点是一个双向链表;便于遍历数据（遍历场景多）

聚簇和非聚簇索引

A : 聚簇索引主要指数据和索引放一块,B+树的叶子节点保存整行数据,有且只有一个,一般情况下主键作为聚簇索引
非聚簇索引指数据和索引分开存储,B+树叶子节点保存对应主键,可以有多个,一般自定义的索引就是非聚簇索引

聚集索引 Clustered Index 数据+索引,索引结构的叶子节点保存行数据必须有且唯一
二级索引 Secondary Index 数据和索引分开存储,叶子节点关联对应主键,可以存在多个
聚集索引选取规则:首选主键 --> 其次使用第一个唯一索引 --> InnoDB自动生成rowid作为隐藏聚集索引

Q : 回表查询
A : 回表意思是通过二级索引找对应主键值,然后通过主键值找聚集索引所对应的整行数据,这个过程就是回表

回表 : 当前索引无法检索出完整的内容，需要通过主键二次查询

-- 回表是 用索引查数据时，检索的数据可能包含其它列，但走的索引树叶子节点只有当前列值和主键ID，
-- 所以需要根据主键ID再去查一遍数据，得到SQL所需要的列

-- 给订单号ID建索引
select orderId,orderName from orderdetail where orderId = 123
-- SQL走订单ID索引，但在订单ID的索引树的叶⼦节点只有orderId和Id，⽽我们还想检索出orderName，
-- 所以MySQL 会拿到ID再去查出orderName给我们返回，这种操作就叫回表

索引创建原则

表数据超过10万以上,才会建索引,并且加索引的字段都是查询比较频繁的字段,一般也是像作为查询条件,排序字段或分组的字段这些;
使用复合索引,一条sql返回值,尽量使用覆盖索引,如果字段区分度不高的化,也会放在组合索引后面的字段;
比如字段内容较长,考虑用前缀索引,注意不是所有字段都要加索引,这个索引数量也要控制,因为加索引也会导致新增改的速度变慢;

数据量大,且查询比较频繁的表建立索引 (单表超10万数据)
常用作查询条件 where 排序 order by 分组group by 操作的字段建立索引
区分度高的列,尽量建立唯一索引
字符串类型,且字段长度较长,可对字段特点,建立前缀索引
使用联合索引,避免回表
控制索引数量
如果索引列不能存 NULL 值,用 NOT NULL 约束它,优化器知道每列是否包含空值,可以确定哪个索引更有效用于查询

:::info
补充点 ;

选合适的数据类型; 比如整数类型比字符类型更快,因为整数类型需要的存储空间更小
经常被查询的字段,经常被用于排序、分组和联表查询的字段添加索引，可加快查询速度
索引列数量尽量少，别出现重复的索引列
前缀索引优化长文本字段查找；比如varchar类型，长度不要太长；
遵循最左前缀原则，联合索引将最常用列放最左边
联合索引中，避免Null值，因为null比较多，索引变得无意义，甚至导致性能下降
索引列不要做运算操作，导致索引失效
定期优化和重建索引，如果数据量增加很多，定期重建索引，索引列加锁保证数据一致性

:::

索引失效场景

比如 , 索引使用时候没有遵循最左匹配法则;
第二个是模糊查询,如果%号在前面也会导致索引失效.
如果添加索引的字段进行运算操作或者类型转换也会导致索引失效;
如果用了复合索引,中间使用了范围查询,右边的条件索引也会失效.
判断 sql 是否有索引失效情况,可用 explain 执行计划来分析。

索引列进行运算计算,函数导致索引失效(where子句里 = 号左边进行函数或者计算)
like以 %开头索引失效
不等于 != <>索引失效 (范围查询,右边的条件索引也会失效)
is not null 可能失效(不同版本和数据决定) is null有效
类型转换导致索引失效 (字符串不加单引号)
数据库优化器觉得不用索引更快的时候有效
违反最左前缀法则,索引失效; 如果符合最左法则,出现跳跃某一列,右侧会失效

索引的原理

原理:

mysql为例,默认引擎innoDB使用B+数实现索引,在索引查找实现了 log(n) 的时间复杂度
聚簇索引记录了主键id(完整数据) , 非聚簇索引中的索引树种记录数据(索引字段+主键)
聚簇索引的叶子节点记录了完整的值,非聚簇索引的叶子节点记录的是主键和索引字段,如果需要完整值,需要回表操作, 即使用主键去聚簇索引再次查找完整数据
索引叶子节点以链表形式存储, 方便顺序查找和排序

索引缺点

占空间
更新时候级联更新索引
高并发写影响性能

非主键自增&主键生成

第一：MySQL 自增主键
第二：主键保证 唯一性和空间尽可能短（长度）以及块移动的问题
第三：索引特性有序，如果是UUID类似主键，插入性能比自增肯定差；因为UUID插入时需要移动磁盘块（比如块内空间当前已满，但新生的UUID需要插入已满的块，就需要移动块的数据）。另外UUID比较适合生成唯一名字标识比如文件名。但无序不可读，查询效率低。
第四：利用 redis 生成 id,性能好，灵活方便，不依赖数据库。但引入新组件更复杂，可用性降低，编码复杂，增加系统成本。
第五：推特的雪花算法
第六：美团的 Leaf分布式ID生成系统

1.4 SQL优化经验

关键词: 建表，索引，sql语句编写，主从复制，读写分离，分库分表

建表主要参考阿里的开发手册《嵩山版》，比如定义字段的时候需要结合字段内容选择合适类型，如果是数值，像 tinyint,int,bigint这些类型，根据实际情况选择，如果是字符串，结合存储内容选择 char varchar text类型
索引方面：参考索引创建原则和避免失效
sql语句优化：

select具体字段，不用select * ,
注意sql语句避免造成索引失效的写法；
聚合查询用 union all代替 union,因为 union会多一次过滤，效率比较低，
如果表关联的话，尽量用 inner join,少用外连接，如必须要用一定要以小表驱动大表（内连接会对两个表进行优化，优先把小表放在外边，大表放里边，而外连接不会重新调整顺序）；
where子句不要对字段进行表达式操作（比如 substring等）
主从复制，读写分离，不让数据的写入，影响读操作
分库分表

优化数据库的方法

硬件主要在存储层优化(企业级别的ssd)CPU 带宽等
网络
操作系统调优协议优化,网络打开关闭优化
表结构设计优化 字段容量控制好,类型选择,字符串存储(固定长度用char, 大的直接用blob,时间建议用timestamp,它比datetime存储时候容量少一半)
sql优化 不用select*, where条件尽量精准,先等值再范围, 用union all 代替union,能用inner join就不用外连接,必须使用,一定要以小表为驱动
减少函数使用
索引优化 减少关联查询,产生临时拘束, 减少子查询,减少临时表产生
大字段和全文检索优化尽量将大字段存fastdfs或者es里,如果存选择blob
连接池优化
事务优化注意锁的问题,for update尽量少用, 简单查询能不用事务就不要用事务,能把事务隔离级别尽量低
数据库集群化
加缓存
冷热存储
分库分表
主从复制,读写分离

第一部分 : 硬件操作系统层面优化
硬件方面主要是CPU,带宽,磁盘读取速度,可用内存大小等方面,操作系统网络配置等,主要DBA或运维优化;

第二部分 : 架构设计层面优化
磁盘IO频繁,高并发高性能的环境下,1 搭建主从集群;2 读写分离设计;3分库分表;4 热点数据引入高效数据库,比如Redis

第三部分 : MySQL程序配置优化
配置文件 my.cnf修改 , binlog日志的开启(默认不开启的) , bufferpool 的缓存池配置;
配置作用域(会话和全局) ;热加载; 全局配置建议配置在默认配置

第四部分 : SQL 执行优化
慢SQL的定位和排查
执行计划分析: explain 
show profile 工具,分析得到所有资源的开销情况,内存,IO等开销

常见的规则:
1. SQL查询基于索引进行数据扫描
2. 避免索引列使用函数或者运算符
3. where 子句的like 一定要把%放最右边
4. 索引扫描,联合索引的列从左到右,命中越多越好
5. 尽可能使用索引列完成排序,避免文件排序
6. 少用 select * 
7. 用小结果集驱动大结果集

2. MySQL事务

2.1 ACID

A（原子性底层依赖undolog日志）
I （隔离性底层实现是锁）
D （持久性底层依赖redolog持久化数据）
C （一致性，是事务的目的，通过应用程序保证一致性）
隔离级别(自行补充,读未提交,rc,rr,串行化)
(脏读 | 不可重复读 | 幻读)

不可重复读的重点是修改⽐如多次读取⼀条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除⽐如多次读取⼀条记录发现记录增多或减少了。

2.2 Q : undolog & redolog

A : redolog 记录的是数据页物理变化,服务宕机可用来同步数据,
undolog 记录的是逻辑日志,事务回滚,通过逆操作恢复原来数据
redolog 保证事务持久性, undolog保证事务原子性和一致性

**缓冲池(buffer pool) **: 主内存的一个区域,里面缓存磁盘上经常操作的真实数据,crud时,先操作缓存池的数据(若缓冲池没有数据,先从磁盘加载并缓存),以一定频率刷新到磁盘,从而减少磁盘IO,加快处理速度
**数据页(page) **: InnoDB存储引擎磁盘管理的最小单元,每页大小默认16KB,页中存储的行数据

2.3 Q : MVCC

事务隔离性是由锁和MVCC实现
MVCC多版本并发控制，维护一个数据的多个版本，使得读写操作没有冲突；
底层实现分为三个部分，第一隐藏字段，第二undolog日志,第三readView读视图
隐藏字段指: mysql给每个表设置隐藏字段,
一个是 trx_id事务id,记录每次操作的事务id,自增;
另一个是 roll_pointer回滚指针,指向上一个版本的事务版本记录地址;
ROW_ID 如果没设置主键且该表没有唯一非空索引时, innoDB会使用该id生成聚簇索引
undolog 记录回滚日志,存储老版本数据,内部形成一个版本链,在多事务并行操作某一行记录,记录不同事务修改数据的版本,通过回滚指针形成一个链表
readView解决事务查询版本的问题,内部定义了匹配规则和当前的一些事务id判断该访问哪个版本的数据,不同隔离级别快照读不一样,最终访问结果不一样,rc级别,每一次执行快照都都生成ReadView, rr级别仅在第一次,后续复用

2.4 MySQL的锁

按锁粒度分类：

⾏锁：锁某⾏数据，锁粒度最⼩，并发度⾼
表锁：锁整张表，锁粒度最⼤，并发度低
间隙锁（Gap）：锁的是⼀个区间
其它：页级锁(BDB引擎)，记录锁（Record Lock），临键锁(next-key lock)

还可以分为：

共享锁：也就是读锁，⼀个事务给某⾏数据加了读锁，其他事务也可以读，但是不能写
排它锁：也就是写锁，⼀个事务给某⾏数据加了写锁，其他事务不能读，也不能写

还可以分为：

乐观锁：并不会真正的去锁某⾏记录，⽽是通过⼀个版本号来实现的
悲观锁：上⾯所的⾏锁、表锁等都是悲观锁

在事务隔离级别实现中，需要利用锁解决幻读

innodb对于行的查询使用next-key lock
Next-locking keying为了解决Phantom Problem幻读问题
当查询的索引含有唯一属性时，将next-key lock降级为record key
Gap锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生
有两种方式显式关闭gap锁：（除了外键约束和唯一性检查外，其余情况仅使用record lock）
A… 将事务隔离级别设置为RC
B. 将参数innodb_locks_unsafe_for_binlog设置为1

3. 主从同步

二进制日志,binlog记录所有DDL语句和DML语句

主库在事务提交时,把数据变更记录在binlog
从库读取主库的binlog写入到从库的中继日志 relay log
从库重做中继日志的事件,将改变反映它自己的数据

4. 分库分表

业务介绍 1数据量大的业务(请求数多,业务累计大) 2(单表1kw或超过20g)
拆分策略 (sharding-sphere mycat)

水平分库, 一个库数据拆分到多个库,解决海量数据存储和高并发问题
水平分表,解决单表存储和性能问题
垂直分库,根据业务拆分,高并发下提高磁盘IO和网络连接数
垂直分表,冷热数据分离,多表互不影响

数据库分⽚的两种常⻅⽅案：
客户端代理：分⽚逻辑在应⽤端，封装在jar包中，通过修改或者封装JDBC层来实现。当当⽹的 Sharding-JDBC 、阿⾥的TDDL是两种⽐较常⽤的实现。
中间件代理：在应⽤和数据中间加了⼀个代理层。分⽚逻辑统⼀维护在中间件服务中。 Mycat 、360的Atlas、⽹易的DDB等等都是这种架构的实现。

4.1 分库分表数据迁移过程

我们⼀般采取「双写」的⽅式来进⾏迁移，⼤致步骤就是：
⼀、增量的消息各⾃往新表和旧表写⼀份
⼆、将旧表的数据迁移⾄新库
三、迟早新表的数据都会追得上旧表（在某个节点上数据是同步的）
四、校验新表和⽼表的数据是否正常（主要看能不能对得上）
五、开启双读（⼀部分流量⾛新表，⼀部分流量⾛⽼表），相当于灰度上线的过程
六、读流量全部切新表，停⽌⽼表的写⼊
七、提前准备回滚机制，临时切换失败能恢复正常业务以及有修数据的相关程序。