java面试题_数据库篇_Mysql

数据库篇-优化-如何定位慢查询

方案一：开源工具

调试工具：Arthas 

运维工具：Prometheus 、Skywalking

方案二：MySQL自带慢日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志

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

# 开启MySQL慢日志查询开关

slow_query_log=1

# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志long_query_time=2

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

一个SQL语句执行很慢, 如何分析

type 这条sql的连接的类型，性能由好到差为NULL、system、const、eq_ref、ref、range、 index、all

system：查询系统中的表

const：根据主键查询

eq_ref：主键索引查询或唯一索引查询

ref：索引查询

range：范围查询

index：索引树扫描

all：全盘扫描

MYSQL支持的存储引擎有哪些, 有什么区别 ?

在mysql中提供了很多的存储引擎，比较常见有InnoDB、MyISAM、Memory

InnoDB存储引擎是mysql5.5之后是默认的引擎，它支持事务、外键、表级锁和行级锁

MyISAM是早期的引擎，它不支持事务、只有表级锁、也没有外键，用的不多

Memory主要把数据存储在内存，支持表级锁，没有外键和事务，用的也不多

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

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

B+Tree

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

B树与B+树对比:

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

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

聚集索引选取规则:

如果存在主键，主键索引就是聚集索引。

如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。

如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

回表查询

通过二级索引找到对应的主键值，到聚集索引中查找整行数据，这个过程就是回表

覆盖索引

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

id为主键，默认是主键索引

name字段为普通索引

MYSQL超大分页怎么处理 ?

问题：在数据量比较大时，limit分页查询，需要对数据进行排序，效率低

解决方案：覆盖索引+子查询

索引创建原则有哪些？

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

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

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

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

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

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

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

什么情况下索引会失效 ?

1). 违反最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。匹配最左前缀法则。

2). 范围查询右边的列，不能使用索引 。

3). 不要在索引列上进行运算操作， 索引将失效。

4). 字符串不加单引号，造成索引失效。

5).以%开头的Like模糊查询，索引失效。如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

SQL语句优化

SELECT语句务必指明字段名称（避免直接使用select * ）

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

尽量用union all代替union union会多一次过滤，效率低

避免在where子句中对字段进行表达式操作

Join优化 能用innerjoin 就不用left join right join，如必须使用 一定要以小表为驱动，

内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边。left join 或 right join，不会重新调整顺序

事务的特性是什么？

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

ACID是什么？

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

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

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

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

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

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

并发事务问题

怎么解决并发事务的问题？

解决方案：对事务进行隔离

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

mysql默认隔离级别：可重复读。

undo log和redo log的区别

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

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

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

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

redo log

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

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

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

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

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

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

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

mvcc : 多版本并发控制

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

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

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

隐藏字段：

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

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

undo log：

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

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

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

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

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

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

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

主从同步原理：

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

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

MySQL主从复制的核心就是二进制日志binlog(DDL（数据定义语言）语句和 DML（数据操纵语言）语句)

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

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

从库重做中继日志中的事件，将改变反映它自己的数据

分库分表

分库分表的时机：

1，前提，项目业务数据逐渐增多，或业务发展比较迅速 单表的数据量达1000W或20G以后

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

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

垂直拆分

垂直分库：以表为依据，根据业务将不同表拆分到不同库中。

特点：

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

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

垂直分表：

拆分规则：

把不常用的字段单独放在一张表

把text，blob等大字段拆分出来放在附表中

垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同表中。

特点：

1，冷热数据分离

2，减少IO过渡争抢，两表互不影响

水平拆分

水平分库：将一个库的数据拆分到多个库中。

特点：

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

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

水平分表：将一个表的数据拆分到多个表中(可以在同一个库内)。

特点：

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

避免IO争抢并减少锁表的几率;

分库之后的问题：

分布式事务一致性问题

跨节点关联查询

跨节点分页、排序函数

主键避重

具体拆分策略

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

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

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

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