Mysql面试

MySQL的主从复制原理以及流程

        主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中；

        从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进 自己的relay log中；

        从：sql执行线程——执行relay log中的语句；

 

简述MyISAM和InnoDB的区别

        MyISAM：不支持事务；支持表锁；支持全文索引；count()有变量存储，无需查全表；非聚簇索引

        InnoDB：支持事务；支持行级锁；聚簇索引；

 

为什么mysql的索引使用B+树而不是B树呢？？

（1）B+树更适合外部存储(一般指磁盘存储),由于内节点(非叶子节点)不存储data，所以一个节点可以存储更多的内节点，每个节点能索引的范围更大更精确。也就是说使用B+树单次磁盘IO的信息量相比较B树更大，IO效率更高。

（2）mysql是关系型数据库，经常会按照区间来访问某个索引列，B+树的叶子节点间按顺序建立了链指针，加强了区间访问性，所以B+树对索引列上的区间范围查询很友好。而B树每个节点的key和data在一起，无法进行区间查找。

 

事务的ACID特性

即Atomicity(原子性) Consistency(一致性), Isolation(隔离性), Durability(持久性)

原子性：要执行的事务是一个独立的操作单元，要么全部执行，要么全部不执行

一致性：事务的一致性是指事务的执行不能破坏数据库的一致性，一致性也称为完整性。一个事务在执行后，数据库必须从一个一致性状态转变为另一个一致性状态。

隔离性：多个事务并发执行时，一个事务的执行不应影响其他事务的执行，SQL92规范中对隔离性定义了不同的隔离级别：

持久性：事务对数据库的修改是持久存在。

事务隔离级别：

事务交替执行，效率更高

READ UNCOMMITTED（读未提交）

在该级别中，事务中的修改即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据称之为脏读（Diryt Read）。这个级别会导致很多问题，性能也不会比其他级别好太多，但却缺乏其他级别的很多好处，除非真的有非常必要的理由，在实际应用中一般很少使用。

READ COMMITED（读已提交）

大多数数据库系统的默认隔离级别都是该级别（但MySQL不是）。该级别满足隔离性的简单定义：一个事务开始时，只能“看见”已经提交的事务所做的修改。也就是说，一个事务从开始直到提交前，所做的修改对其他事务都是不可见的。这个级别有时候也叫作不可重复读，因为*两次执行同样的查询，可能会得到不一样的结果。

REPEATABLE READ（可重复读）

该级别是MySQL的默认事务隔离级别，解决了脏读的问题，并保证了在同一个事务中多次读取同样的记录的结果是一致的。理论上可重复读还是无法解决另外一个幻读的问题。幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，之前的事务再次读取该范围的记录时，会产生幻行。InnoDB存储引擎通过多版本并发控制（MVCC）解决了该问题。

SERIALIZABLE（可串行化）

该级别是最高的隔离级别，它通过强制事务串行执行，避免了前面所说的幻读问题。简单来说，该级别会对读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用问题。实际应用中也很少用到这个隔离级别，只有在非常需要确保数据的一致性而且可以接受没有并发的情况下，才考虑采用该级别。

 

引擎：

InnoDB

支持事务；行级锁；外键约束和聚簇索引；

InnoDB行锁实现方式: InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行锁，否则，InnoDB将使用表锁

 MyISAM

不支持事务；支持表锁；不支持外键约束；支持全文索引；count()有变量存储，无需查全表；非聚簇索引

 

锁介绍

• 表级锁：开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发量最低

• 行级锁：开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发度最高

• 乐观锁：每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。

  数据库的乐观锁需要自己实现，在表里面添加一个 version 字段，每次修改成功值加 1，这样每次修改的时候先对比一下，自己拥有的 version 和数据库现在的 version 是否一致，如果不一致就不修改，这样就实现了乐观锁。 

• 悲观锁：每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻止，直到这个锁被释放。

 

mysql 问题排查都有哪些手段？

• 使用 show processlist 命令查看当前数据库运行的所有线程。

• 使用 explain 命令查询 SQL 语句执行计划。

• 开启慢查询日志，查看慢查询的 SQL。

慢查询日志分析

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10。

通过慢查询日志，可以查找出哪些查询语句的执行效率低，以便进行优化。

如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。

如何做 mysql 的性能优化？

• 为搜索字段创建索引。

• 避免使用 select *，列出需要查询的字段。

• 垂直分库分表。

• 选择正确的存储引擎。

 

MVCC

MVCC多版本并发控制指的是 “维持一个数据的多个版本，使得读写操作没有冲突” ，即使有读写冲突时，也能做到不加锁，非阻塞并发读，提高数据库并发性能。

InnoDB基于行锁还实现了MVCC多版本并发控制

1、多版并发控制MVCC是MySQL InnoDB存储引擎实现隔离等级的具体方法，用于实现读已提交和可重复读两种隔离等级。

读未提交隔离等级，总是读取最新的数据行（当前读），不需要使用MVCC。

2、可串行隔离等级需要锁定所有读取的行，单纯使用MVCC是无法实现的。

 

 

 

索引的类型（B+树索引）

1.聚簇索引，表数据按照索引的顺序来存储的。对于聚集索引，叶子结点即存储了真实的数据行，不再有另外单独的数据页。（主键索引属于这个）

2.非聚簇索引，表数据存储顺序与索引顺序无关。对于非聚集索引，叶子结点包含索引字段值及指向数据页数据行的逻辑指针。

 

执行SQL语句时各关键字的顺序：

from            #step1 确定在哪张表做查询
where           #step2 设置过滤条件，过滤出符合条件的内容
group by        #step3 对过滤后的数据按字段分组
having          #step4 再进一步对分组后的数据过滤
select          #step5 查找数据
distinct        #step6 去重
order by        #step7 对去重后的数据按要求排序
limit           #step8 限制显示或打印数据的条数 分页查询

 

 

分库分表

 

 

分库分表方案是对关系型数据库数据存储和访问机制的一种补充。
水平分库：将一个库的数据拆分到多个相同的库中，访问的时候访问一个库

垂直分库：将一个库的数据拆分到多个不同的库中，访问的时候访问一个库

分水平表：把一个表的数据按照行放到多个相同表中，操作对应的某个表就行

垂直分表：把一个表的数据按照列放到多个不同表中，操作对应的某个表就行

 

1、水平切分

水平切分又称Sharding，是将同一表中的记录分割到多个结构相同的表中。

Sharding是增加，Sharding是必然的选择，它将数据分布到集群的不同节点，从而缓存单个数据库的压力。

2、垂直切分

垂直切分是将一个表按列分成多个表，通常根据列的关系密集度进行分割。 垂直会改变库或表的结构

在数据库层面使用垂直分割将根据数据库中表的密度部署到不可接受的数据库，如垂直分割原始电子商务数据数据库，称为商品数据库和用户数据库。