MYSQL常问面试题

MySQL中如何定位慢查询

一：使用开源工具
调试工具：Arthas（查看方法运行时间）
运维工具：Prometheus、Skywalking（实时查看运行时的数据，接口响应的时间）
二：MySQL自带慢日志
慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志
慢日志都在开发测试阶段才会开启，实际生产环境中不会开启，因为慢日志会损耗MySQL性能

 SQL语句执行的很慢如何优化分析

通过查找SQL执行计划找到慢的原因
在SQL语句前面加上explain或desc可以获取mysql执行select语句的信息
possible_key  当前sql可能会使用到的索引
key 当前sql实际命中的索引
key_len 索引占用的大小
Extra 额外的优化建议
type是这条sql连接的类型，性能由好到差为NULL、system、const、eq_ref、ref、range、 index、all 

 了解过索引吗？（什么是索引）

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

l 索引（ index ）是帮助 MySQL 高效获取数据的数据结构 ( 有序 )

l 提高数据检索的效率，降低数据库的 IO 成本（不需要全表扫描）

l 通过索引列对数据进行排序，降低数据排序的成本，降低了 CPU 的消耗

索引的底层数据结构了解过嘛 ?

MySQL的InnoDB引擎采用的B+树的数据结构来存储索引

l 阶数更多，路径更短

l 磁盘读写代价 B+ 树更低，非叶子节点只存储指针，叶子阶段存储数据

l B+ 树便于扫库和区间查询，叶子节点是一个双向链表

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

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

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

B树与B+树对比:

①：磁盘读写代价B+树更低；②：查询效率B+树更加稳定；③：B+树便于扫库和区间查询 

什么是聚簇索引什么是非聚簇索引 ?

l 聚簇索引（聚集索引）：数据与索引放到一块， B+ 树的叶子节点保存了整行数据，有且只有一个

l 非聚簇索引（二级索引）：数据与索引分开存储， B+ 树的叶子节点保存对应的主键，可以有多个

 

知道什么叫覆盖索引嘛 ?

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

使用id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。

如果返回的列中没有创建索引，有可能会触发回表查询，尽量避免使用select *

MYSQL超大分页处理

可以使用覆盖索引解决

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

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

索引创建的原则 

1). 针对于数据量较大，且查询比较频繁的表建立索引。

2). 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。

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

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

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

6). 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。

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

什么情况下索引会失效

① 违反最左前缀法则

② 范围查询右边的列，不能使用索引

③ 不要在索引列上进行运算操作， 索引将失效

④ 字符串不加单引号，造成索引失效。 ( 类型转换 )

⑤ 以 % 开头的 Like 模糊查询，索引失效

你对SQL优化的经验 

l 表的设计优化

l 索引优化

l SQL 语句优化

l 主从复制、读写分离

l 分库分表

l 表的设计优化（参考阿里开发手册 《 嵩山版 》 ）

① 比如设置合适的数值（ 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 ，不会重新调整顺序

主从复制、读写分离

如果数据库的使用场景读的操作比较多的时候，为了避免写的操作所造成的性能影响 可以采用读写分离的架构。读写分离解决的是，数据库的写入，影响了查询的效率。

 

 事务的特性是什么

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

ACID是什么？可以详细说一下吗？

l 原子性（ A tomicity ）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。

l 一致性（ C onsistency ）：事务完成时，必须使所有的数据都保持一致状态。

l 隔离性（ I solation ）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。

l 持久性（ D urability ）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

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

并发事务问题：脏读、不可重复读、幻读

隔离级别：读未提交、读已提交、可重复读、串行化

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

 

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

undo log和redo log的区别 

l 缓冲池（ buffer pool ） : 主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），以一定频率刷新到磁盘，从而减少磁盘 IO ，加快处理速度

l 数据页（ page ） : 是 InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB 。页中存储的是行数据

redo log

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

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

 undo log

回滚日志，用于记录数据被修改前的信息 , 作用包含两个 : 提供回滚 和 MVCC(多版本并发控制) 。undo log和redo log记录物理日志不一样，它是逻辑日志。

l 可以认为当 delete 一条记录时， undo log 中会记录一条对应的 insert 记录，反之亦然，

l 当 update 一条记录时，它记录一条对应相反的 update 记录。当执行 rollback 时，就可以从 undo log 中的逻辑记录读取到相应的内容并进行回滚。

undo log可以实现事务的一致性和原子性

l redo log: 记录的是数据页的物理变化，服务宕机可用来同步数据

l undo log ：记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据

l redo log 保证了事务的持久性， undo log 保证了事务的原子性和一致性

 事务中的隔离性是如何保证的呢？

锁：排他锁（如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁）

mvcc : 多版本并发控制

你解释一下MVCC?

全称 Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突

MVCC的具体实现，主要依赖于数据库记录中的隐式字段、undo log日志、readView。

l记录中的隐藏字段

隐藏字段	含义
DB_TRX_ID	最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID。
DB_ROLL_PTR	回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本。
DB_ROW_ID	隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。

lundo log

回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。

当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。

而update、delete的时候，产生的undo log日志不仅在回滚时需要，mvcc版本访问也需要，不会立即被删除。

l undo log 版本链

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

 readview

ReadView（读视图）是 快照读 SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id。

回答：

MySQL中的多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突

l 隐藏字段 ：

① trx_id ( 事务 id) ，记录每一次操作的事务 id ，是自增的

② roll_pointer ( 回滚指针 ) ，指向上一个版本的事务版本记录地址

l undo log ：

① 回滚日志，存储老版本数据

② 版本链：多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过 roll_pointer 指针形成一个链表

l readView 解决的是一个事务查询选择版本的问题

Ø 根据 readView 的匹配规则和当前的一些事务 id 判断该访问那个版本的数据

Ø 不同的隔离级别快照读是不一样的，最终的访问的结果不一样

    RC ：每一次执行快照读时生成ReadView

    RR：仅在事务中第一次执行快照读时生成ReadView，后续复用

 MySQL主从同步原理

主库写操作，从库读操作，实现读写分离

MySQL主从复制的核心就是二进制日志

复制分成三步：

1. Master 主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中。

2. 从库读取主库的二进制日志文件 Binlog ，写入到从库的中继日志 Relay Log 。

3. slave重做中继日志中的事件，将改变反映它自己的数据。

使用过分库分表吗？主要作用是什么

分库分表的时机：

1，前提，项目业务数据逐渐增多，或业务发展比较迅速

2，优化已解决不了性能问题（主从读写分离、查询索引…）

3，IO瓶颈（磁盘IO、网络IO）、CPU瓶颈（聚合查询、连接数太多）

业务介绍

1，根据自己简历上的项目，想一个数据量较大业务（请求数多或业务累积大）

2，达到了什么样的量级（单表1000万或超过20G）

具体拆分策略

1，水平分库，将一个库的数据拆分到多个库中，解决海量数据存储和高并发的问题

2，水平分表，解决单表存储和性能的问题

3，垂直分库，根据业务进行拆分，高并发下提高磁盘IO和网络连接数

4，垂直分表，冷热数据分离，多表互不影响