MySQL 面试题 - 参考回答

本贴最后更新于 340 天前,其中的信息可能已经时移世改

MySQL 面试题-参考回答.pdf

image

MySQL 优化

如何定位慢查询

image

场景:聚合查询 多表查询 表数据量过大查询 深度分页查询

定位慢查询

1 使用调试工具:Arthas 运维工具:Prometheus,Skywalking

2 MySQL 自带的慢日志

MySQL 默认未开启慢查询日志,需要在配置文件中手动开启

日志在/var/lib/mysql/localhost-slow.log 中查看

#开启慢日志查询开关
slow_query_log=1
#超出该时间为慢查询
long_query_time=2

SQL 语句的分析image

对于聚合查询,多表查询,表数据过大查询都可以通过分析 SQL 执行计划来分析 SQL 执行慢的原因

-添加EXPLAIN或DESC都可以分析
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;

image

字段

possible_key 当前 sql 可能使用到的索引

key 当前 sql 实际命中的索引

key_len 索引占用的大小

extra 额外的优化建议,如果是 Using index condition 的话索引有优化的空间

image

type sql 连接的类型 性能由好到坏 NULL(查询没涉及到表),system(查询的是 mysql 内置的表),const(根据主键查询),eq_ref(主键索引查询或唯一索引查询,只能返回一条数据),ref(索引查询,返回多条数据),range(范围查询),index(遍历全索引树),all(全盘扫描)

type 类型如果是 index 或 all,这个语句需要优化

什么是索引

image

概念

索引是帮助 MySQL 高效获取数据的数据结构,特定查找算法的数据结构(B+ 树)

底层数据结构

B+ 树 Mysql 的默认存储引擎 InnoDB 使用 B+ 树实现

B+ 树与 B 树对比:​image

1 B+ 树磁盘读写代价低 2 查询效率 B+ 树更稳定(查询每个数据路径大小都一致)

3 B+ 树便于扫库和区间查询

4 B+ 树,非叶节点不包含该关键字对应记录的存储地址,可以使一个磁盘块包含更多关键字,使 B+ 树的阶更大,树更矮,读磁盘次数少,查找快

什么是聚簇索引,什么是非聚簇索引(二级索引又称为非聚集索引)

image

概念

1 聚集索引(必须有,有且只有一个

将数据存储与索引放到了一块,索引结构的叶子节点保存了行数据

聚集索引的选取规则:

存在主键,主键索引就是聚集索引

不存在主键,使用第一个 UNIQUE 索引作为聚集索引

如果表中没有主键,或者没有合适的唯一索引,InnoDB 会自动生成一个 rowid 作为隐藏的聚簇索引

2 二级索引(可以存在多个)(单独给字段创建的索引大部分为二级索引)

将数据与索引分开存储,索引结构的叶子节点关联的是对应的主键,可以有多个

image

3 回表查询

image

什么是覆盖索引

image

**概念:**查询使用到了索引,并且需要返回的列,在该索引中已经可以全部找到

image

覆盖索引可以用于解决 MySQL 超大分页问题

image

使用覆盖索引加子查询优化超大分页问题

select * 
from tb_sku t ,
  (Select id from tb_sku order by limit 9000000,10) a
where t.id=a.id;

索引的创建索引有哪些

1 针对数据量较大,且查询比较频繁的表建立索引

2 针对常作为查询条件,排序,分组操作的字段建立索引

3 尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高

4 如果是字符串类型的字段,字段的长度较长,可以针对字段的特点,建立前缀索引

5 尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表,提高查询效率

6 要控制索引的数量,索引并不是越多越好,索引越多,维护索引结构的代价也越大,会影响增删改的效率

7 如果索引列不能存储 NULL 值,请在创建表时使用 NOT NULL 约束,当优化器知道每列是否包含 NULL 值时,可以更好确定哪个索引最有效用于查询

索引失效问题

1 违反最左前缀法则

如果索引了多列(联合索引),要遵循最左前缀法则,查询从索引的最左列开始,并且不跳过索引中的列,匹配最左前缀法则,如果跳过的话可能会导致索引失效,如果符合最左原则,但是出现跳跃某一列,只有最左列索引生效

2 范围查询右边的列不能使用索引,如果使用,索引会失效

image

3 不要在索引列上进行运算操作,否则会失效

image

4 字符串不加单引号,造成索引失效

image

5 以 % 开头的 like 模糊查询,索引失效,如果仅仅是尾部模糊匹配,索引不会失效,头部模糊匹配,索引会失效

image

对 SQL 优化的经验

表设计的优化

我们项目主要使用的是阿里卡法手册(嵩山版)

1.设置合适的数值(tinyint,int,bigint),根据实际情况选择

2.设置合适的字符串类型(char,varchar)char 效率高,varchar 可变长度,效率低

索引优化(参考索引创建原则和索引失效)

SQL 语句优化

image

1.SELECT 语句务必指名字段名称(避免使用 select*) --尽量使用覆盖索引,避免回表查询

2.SQL 语句要避免造成索引失效的写法

3.尽量使用 union all 代替 union union 会多一次过滤,效率低

4.避免在 where 子句对字段进行表达式操作(在 where 中对表达式操作可能导致 SQL 索引失效)

5.Join 优化 能用 innerjoin 就不用 left join right join,如必须使用要以小表为驱动,内连接会对两个表进行优化,优先把小表放外边,大表放里边。 innerjoin 默认会把小表放外边,大表放里面,而 left join 和 right join,已经人为确定好表顺序

主从复制,读写分离优化

如果数据库的使用场景读多写少时,为了避免写操作造成的性能影响,可以采用读写分离的架构,主库进行写,从库进行读

image

分库分表(后续讲)

MySQL 其他面试题

事务的特性是什么

概念:

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

特性:(ACID)

原子性(Atomicity) 事务是不可分割的最小操作单元,要么同时成功,要么同时失败

一致性(Consistency) 事务完成时,所有数据要保持一致状态

隔离性(Isolation) 数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境运行

持久性(Durability) 事务一旦提交或回滚,它对数据库的改变是永久的

并发事务带来的问题?怎么解决这些问题?MySQL 的默认隔离级别是?

image

并发事务问题

1 脏读 一个事务读到了另一个事务没有提交的数据

image

2 不可重复读

一个事务先后读取同一个记录,两次读取的数据不同

image

3 幻读(在解决了不可重复读条件下发生的)

一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据存在了,出现幻读

image

解决并发事务的问题

对事务进行隔离

事务隔离级别越往下级别越高,数据越安全,性能越差

image

undolog 和 redo log 的区别

image

概念:

缓冲池(buffer pool):缓存磁盘上经常操作的真实数据,在执行增删改查操作时,先操作缓冲池中的数据,缓冲池中没有,从磁盘中加载,并以一定频率刷新到磁盘,从而减少 IO,加快处理速度

数据页: 是 InnoDB 存储引擎磁盘管理的最小单位,每个页默认大小为 16KB 后缀名为.ibd

redo log

image

重做日志,记录的是事务提交时对物理页的物理修改,实现事务的持久化

日志文件由两部分组成:重做日志缓冲(redo log buffer)和重做日志文件(redo log file),前者在内存中,后者在磁盘中,redo log 日志文件在磁盘中有两份是循环进行写的

image

undo log(解决事务的一致性和原子性)

回滚日志,用于记录数据被修改前的信息,作用是:提供回滚和 MVCC(多版本并发控制),undo log 记录的是逻辑日志(是一条一条的指令)

当执行 delete,update,insert 时,会在 log 中记录一条相反的指令,方便回滚

执行 rollback 时,可以从 undo log 中的逻辑记录中读取到内容进行回滚

事务的隔离性如何保证(排他锁和 MVCC 来共同保证)

:排他锁(如果一个事务获取了一个数据行的排他锁,其他事务就不能获取该行的其他锁,包括排他锁)

MVCC (Multi-Version Concurrency Control,多版本并发控制) 维护一个数据的多个版本,使读写操作没有冲突

1.MVCC 依赖于隐藏字段,undo log 日志,readView

DB_TRX_ID 事务 ID,一个事务在开启时,会默认分配一个 ID,是自增的唯一的

image

2.undo log 版本链

image

3.readView

ReadView 是快照读 SQL 执行时 MVCC 提取数据的依据,记录并维护系统当前活跃的事务(未提交的)id

MySQL 的主从同步原理

image

image

核心是二进制日志,二进制日志(BINLOG)中记录了所有的 DDL(数据定义语言)和 DML(数据操纵语言)语句,但不包括数据查询语言(SELECT ,SHOW)

image

MySQL 中的分库分表问题

image

主从解决的是访问的压力 分库分表解决的是存储的压力

1 分库分表的时机

业务数据逐渐增多,业务发展比较迅速 (例如单表的数据量达到 1000w 或 20G)

优化已经解决不了性能问题,数据增多情况下

IO 瓶颈(磁盘 IO,网络 IO),CPU 瓶颈(表太多情况下聚合查询会占用大量 CPU 用户太多连接数太多,也会占用大量 CPU)

image

2 分库分表的策略

垂直拆分

垂直分库 以表为依据,根据业务将不同表拆分到不同的库中(例如不同的微服务对应不同的表)

image

特点:

1 按照业务对数据分级管理,维护,监控,扩展

2 在高并发下,提高磁盘 IO 和数据量连接数

垂直分表

拆分规则: 1 把不常用的字段单独拆分成一张表 2 把 text,blob 大文本拆分成一张表

特点:

1 冷热数据分离,展示数据时,只展示基本信息,只有用户感兴趣才会点进去展示描述信息

2 提高 IO 的效率,不用经常性传输大数据

image

水平拆分

水平分库

将一个库的数据拆分到多个库中(解决的是一个库装不下的问题)

可以通过一些算法将查找的索引路由到某一台数据库中

特点:

1 解决了单库大数量,高并发的性能瓶颈问题

2 提高了系统的稳定性和可用性

image

水平分表

image

将一个表中的数据拆分到多个表中(这些表可以位于一个库中)

同样查找某一条数据时,通过算法路由到指定数据库

特点:

1 优化单一表数据量过大产生的性能问题

2 避免 IO 争抢减少锁表的几率,减少阻塞的几率

分库出现的问题**:**

1 分布式事务一致性问题(一个服务操作多个库,涉及到同时提交或者回滚)

2 跨结点关联查询 3 跨节点分页,排序函数 4 主键避重

解决此问题的技术:使用分库分表的中间件 sharding-sphere 或 Mycat

image

  • MySQL

    MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。MySQL 是最流行的关系型数据库管理系统之一。

    675 引用 • 535 回帖

相关帖子

欢迎来到这里!

我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。

注册 关于
请输入回帖内容 ...