Mysql面试合集

概念

是一个开源的关系型数据库。

数据库事务及其特性

事务：是一系列的数据库操作，是数据库应用的基本逻辑单位。

事务特性：

（1）原子性：即不可分割性，事务要么全部被执行，要么就全部不被执行。

（2）一致性。事务的执行使得数据库从一种正确状态转换成另一种正确状态

（3）隔离性。在事务正确提交之前，不允许把该事务对数据的任何改变提供给任何其他事务，

（4）持久性。事务正确提交后，其结果将永久保存在数据库中，即使在事务提交后有了其他故障，事务的处理结果也会得到保存。

数据库三范式

第一范式（1NF）无重复的列（原子性）

第二范式（2NF）属性完全依赖于主键

第三范式（3NF）属性不依赖于其它非主属性

sql语句在mysql中的执行过程

客户端发起sql请求，与MySQL服务器建立连接，如果是select会先查询缓存（mysql8删除）,没有命中缓存进入分析器，解析处理，更新前记录下log，用于事务回滚。然后判断记录是否存在缓冲池中，查询存在直接返回，更新存在判断存在冲突。最后提交事务。

常见优化方式

a. 设计良好的数据库结构，允许部分数据冗余，尽量避免join查询，提高效率。
b. 选择合适的表字段数据类型和存储引擎，适当的添加索引。
c. mysql库主从读写分离。
d. 找规律分表，减少单表中的数据量提高查询速度。
e. 添加缓存机制，比如memcached，apc等。
f. 不经常改动的页面，生成静态页面。
g. 书写高效率的SQL。比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE.

mysql事务隔离级别

1、串行读 (Serializable)

2、可重复读 (RR默认)

确保同一事务的多个实例并发读取数据时，会看到同样的数据行，解决了不可重复读的问题。

3、读已提交 (RC)

一个事务只能看见已经提交事务所做的改变。可避免脏读的发生。

4、读未提交 (Read uncommitted)

所有事务都可以看到其他未提交事务的执行结果。

脏读：读到了脏数据，即无效数据。
不可重复读：是指在数据库访问中，一个事务内的多次相同查询却返回了不同数据。
幻读：指同一个事务内多次查询返回的结果集不一样，比如增加了行记录。

脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。
不可重复读是指在对于数据库中的某行记录，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，另一个事务修改了数据并提交了。
幻读是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录。对幻读的正确理解是一个事务内的读取操作的结论不能支撑之后业务的执行。假设事务要新增一条记录，主键为id，在新增之前执行了select，没有发现id为xxx的记录，但插入时出现主键冲突，这就属于幻读，读取不到记录却发现主键冲突是因为记录实际上已经被其他的事务插入了，但当前事务不可见。
不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。

innodb和myisam的区别？

InnoDB支持行锁、事务处理、外键、安全恢复，MyISAM不支持

InnoDB必须有主键，MyISAM可以没有

建立索引需要考虑什么？

1、选择合适的字段建立索引

根据查询频率（如姓名、日期），设置索引。字段大量重复、为空不适合建立索引。
尽量找那种占用空间小的类型字段做索引，长字符串可以使用前缀索引，减少索引大小，提高查询速度。

2、多个经常查询列可以建立联合索引

遵循向左原则。

3、避免过度索引和频繁更新索引字段

占用磁盘空间、需要频繁维护字段，浪费性能。

4、SQL优化慢查询

//使用 show processlist
//观察是否有大量线程处于不正常的状态或者特征

//使用 explain（desc） 分析单条SQL语句

一般做到range，极好做到ref，ALL全表扫描不推荐

如何MySQL和ES一致性？

1、操作MYSQL时同步操作ES，这种不推荐，因为重试逻辑嵌在业务代码中，服务宕机，写入失败会一直重试。

2、通过binlog进行同步，客户端从canal拉取消息进行消费，再由客户端主动插入或者更新ES中的数据。也可以cannal发送binlog消息到消息队列，client异步消费kafka中的消息。

b+树是如何实现的优势在哪

B树也称B-树,它是一颗多路平衡查找树，B树和后面讲到的B+树也是从最简单的二叉树变换而来的，并没有什么神秘的地方，下面我们来看看B树的定义。

每个节点中的关键字都按照从小到大的顺序排列，每个关键字的左子树中的所有关键字都小于它，而右子树中的所有关键字都大于它。
所有叶子节点都位于同一层，或者说根节点到每个叶子节点的长度都相同。
每个节点都存有索引和数据，也就是对应的key和value。

B树和B+树的区别在于，B+树的非叶子结点只包含导航信息，不包含实际的值，每个叶子结点都存有相邻叶子结点的指针，叶子结点本身依关键字的大小自小而大顺序链接，便于区间查找和遍历。

由于B+树在内部节点上不包含数据信息，因此在内存页中能够存放更多的key。数据存放的更加紧密，具有更好的空间局部性。因此访问叶子节点上关联的数据也具有更好的缓存命中率。
B+树的叶子结点都是相链的，因此对整棵树的便利只需要一次线性遍历叶子结点即可。而且由于数据顺序排列并且相连，所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。

MySQL悲观锁

在查询库存时加排它锁，阻止其他事务对这条数据进行加锁或者修改

优点：MySQL事物锁准确度高。缺点：耗性能，对MySQL压力较大。

DB::beginTransaction();try {
    $stock = Skill::query()->where('id', $id)->lockForUpdate()->value('stock');
    if ($stock > 0) {
        Skill::query()->where('id', $id)->decrement('stock');
        echo '抢购成功';
    } else {
        echo '库存不足，抢购失败';
    }
    DB::commit();} catch (\Exception $e) {
    echo $e->getMessage();
    DB::rollBack();}

MySQL乐观锁

不加锁实现锁效果，MySQL乐观锁就是MVCC机制，借助version版本号进行控制

优点：因为不涉及锁数据，并发量比悲观锁。缺点：MySQL抗压瓶颈。

$info = Skill::query()->where('id', $id)->first(['stock', 'version']);
if ($info->stock > 0) {
 $skill = Skill::query()->where(['id' => $id, 'version' => $info->version])
   ->update(['stock' => $info->stock -1, 'version' => $info->version + 1]);
 echo '抢购成功';
} else {
 echo '库存不足，抢购失败';
}