MySQL常见的后端面试题，你会几道？

 

为什么分库分表

  • 单表数据量过大，会出现慢查询，所以需要水平分表

  • 可以把低频、高频的字段分开为多个表，低频的表作为附加表，且逻辑更加清晰，性能更优

  • 随着系统的业务模块的增多，放到单库会增加其复杂度，逻辑不清晰，不好维护，所以会对业务进行微服务拆分，同时拆分数据库

怎么分库分表

  • 对于水平，不同的业务按不同的条件去分，比如财务数据，可以按年份作为库，月份做为表。 比如多用户系统，可以按用户id取模划分。
  • 对于垂直，一般按不同的业务模块来划分即可

事务

  • 原子性（Atomicity）：指事务是一个不可分割的最小工作单位，事务中的操作只有都发生和都不发生两种情况

  • 一致性（Consistency）：事务必须使数据库从一个一致状态变换到另外一个一致状态，举一个栗子，李二给王五转账50元，其事务就是让李二账户上减去50元，王五账户上加上50元；一致性是指其他事务看到的情况是要么李二还没有给王五转账的状态，要么王五已经成功接收到李二的50元转账。而对于李二少了50元，王五还没加上50元这个中间状态是不可见的。

  • 隔离性（Isolation）：一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

  • 持久性（Durability）：一个事务一旦提交成功，它对数据库中数据的改变将是永久性的，接下来的其他操作或故障不应对其有任何影响。

悲观锁，排他锁，写锁

数据更新、插入、删除都是带锁的。 只有获得锁才能更新数据，如果查询其他也有for update 或者读锁，那么他的查询也不能更新

  • select * from table where id=xx for update;

尽量要带上条件，且条件一定要有索引， 避免锁表

读锁，共享锁

  • select ... lock in share mode;

读锁相互可以兼容，与写锁冲突，所以如果数据上了写锁，读锁就要等待。同理，如果数据上了读锁，写锁就要等待

查看锁

  • 锁情况 select * from information_schema.innodb_locks;

  • 引擎整体情况 包括锁 show engine innodb status;

索引

  • 对于联合索引 index(a,b,c), 5.7版本及之前遵从最左匹配原则，就是说 单纯是 a和ab和abc和ac走索引，其他不走

  • 当存在多个索引，possible keys有多个，但是使用的时候mysql会选取成本最少的那一个使用

  • 什么字段适合加索引？ where order by group by

  • 不应该建立索引的字段规则 1.不应该在字段比较长的字段上建立索引，因为会消耗大量的空间 2.对于频繁更新、插入的字段应该少建立索引，因为在修改和插入之后，数据库会去维护索引，会消耗资源 3.尽量少在无用字段上建立索引【where条件中用不到的字段】 4.表记录太少不应该创建索引 5.数据重复且分布平均的表字段不应该创建索引【选择性太低，例如性别、状态、真假值等字段】

  • 通常场景，IN条件查询走索引；

  • 当IN多条件查询时，例子： id in (1,2)。 如果数据量大于总数据量30%（这个数好像不准确），就会走全表扫描(暂未找到官方结论，但在Mysql版本为8.0.18中，本人验证基本符合上述结论)；

  • 当IN是单条件，例子：id in (1) 数据量大于总数据30%时，依然走索引。

隔离级别

  • 脏读：事务a读到了事务b未提交到数据

  • 不可重复读：事务a读到了事务提交的数据

  • 幻读：事务a读到了事务b insert的数据（第一次查询读条数和第二次读不同）

级别脏读不可重复读幻读
Read uncommitted √ v √
Read committed Sql Server -- Oracle × v √
Repeatable read Mysql × × √
Serializable × × ×

但是mysql的innodb通过mvcc机制避免了幻读。

  • 普通读（快照读，即普通的select）读到的是历史版本的数据，

  • 当前读（select..for update , insert , update, delete）这些因为需要修改数据，所以就不能读 历史数据，所以就会加锁阻塞。

  • 但是如果是穿插使用的话--先普通读再当前读，是无法避免幻读的。

mvcc

  1. 多版本并发控制技术
  2. 为了解决并发安全问题+提高并发处理能力+解决读写冲突
  3. 读写并发阻塞问题，提高并发读写能力
  4. 采用乐观锁的实现，降低了死锁的概率， 因为有个事务id
  5. 解决一致性读读问题，可重复读
  6. 悲观锁用来解决写和写读冲突
  7. 底层原理：

主要实现rr rc的隔离级别。 通过undolog readview技术共同实现可重复读、读已提交 undolog: 事务的历史版本链表,全局（多个事务共享）维护一个, 每一行有一个递增的事务id 前一个版本的指针 和一个隐藏的rowid readview：每个事务开启的时候，当前读后生成一个readview。有 trx_ids（活跃-未提交的事务id） min_trx_id（trx_ids中最小） max_rtx_id（预分配的下一个trx_id） creator_trx_id (创建readview的trx_id)

匹配规则： 如果数据事务ID trx_id < min_limit_id，表明生成该版本的事务在生成Read View前，已经提交(因为事务ID是递增的)，所以该版本可以被当前事务访问。 如果trx_id>= max_limit_id，表明生成该版本的事务在生成ReadView后才生成，所以该版本不可以被当前事务访问。 如果 min_limit_id =<trx_id< max_limit_id,需腰分3种情况讨论

（1）.如果m_ids包含trx_id,则代表Read View生成时刻，这个事务还未提交，但是如果数据的trx_id等于creator_trx_id的话，表明数据是自己生成的，因此是可见的。

（2）如果m_ids包含trx_id，并且trx_id不等于creator_trx_id，则Read View生成时，事务未提交，并且不是自己生产的，所以当前事务也是看不见的；

（3）.如果m_ids不包含trx_id，则说明你这个事务在Read View生成之前就已经提交了，修改的结果，当前事务是能看见的

rr: 使用第一次当前读创建的readview rc: 每一次当前读都更新readview

三范式

  • 第一范式(1NF)：每个列都不可以再拆分。（比如地址，如果用到城市、省份的数据的话，一定还要继续拆分）

  • 第二范式(2NF)：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。（在第一范式的基础上消除非主键对主键的部分依赖， 比如成绩表有学号、学生、系主任、系名、课程、分数）， 其实如果学号+课程为主键就能确定分数，所以其他无关的字段就可以分离出去）

  • 第三范式(3NF)：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。（其实说白了就是面向对象编程的思想，比如：把系名、系主任拆到另一张表）

但也不一定必须满足3范式，对于数据量大的业务其实要避免3表及以上的join操作，因为要避免没有用到索引，而是用一张宽表记录，查询即可。

怎么保证acid

  1. 原子性： undolog, 保留一份事务中语句的操作日志，当执行发生错误时候，进行回滚，用反向逻辑执行操作语句。

  2. 持久性： redolog, 无论是redolog，还是正常当数据，都是先写到buffer，在写到磁盘。但是redolog是通过追加当模式写入，即：顺序写。而数据 是随机写，明显速度更慢，而且mysql是按页即16kb的传输，所以更慢。采用了先持久化到redolog的方式。 innodb_flush_log_at_trx_commit = 1为 redolog同步到磁盘。如果是0，则系统每秒刷一次buffer到内存。

  3. 隔离性：写写操作，通过锁。写读操作，mvcc。

  4. 一致性：事务到目的，上面三种特性共同保障数据最终一致性

数据库出现慢查询原因

  1. 硬件资源问题，如内存不足

  2. 出现高并发，查询数量过大

  3. 没有建立索引

  4. sql没有命中索引

  5. 返回很多不必要的数据

  6. 数据库数量大

一亿订单数据如何设计数据库，需要卖家、状态进行查询

  1. 水平分表

  2. 通过买家好hash （因为通过买家查询的量是很大的）

  3. 做好映射，比如：卖家：买家列表--> 买家通过路由规则找到表 --> 再通过条件进行查询 --> 合并

innodb myisam区别

  1. 事务

  2. 外键

  3. innodb:聚簇索引 不支持fulltext 但可以通过sphinx插件支持。 myisam: 非聚簇 支持fulltext全文索引

  4. 行锁 表锁

  5. 存储文件， innodb两个：.frm(表定义) .ibd（数据和索引存储） myisam三个： .frm .myd（数据存储） .myi(索引存储)

为啥不用b树

b树非页子节点保存有数据，如果按照一行数据1k，不算上指针和键值的情况下，一个节点是16k，也就最大是16行数据， 所以3层b树最多是 16 * 16 * 16 = 4096行，太少了，所以只能增加层高，意味着增加io次数，所以这个方案不可用

redolog和binglog有啥不同

  1. 使用场景不同：binlog主要用于备份、迁移、数据同步， redolog主要保证事务的持久性，遇到故障时候提供回滚重放操作

  2. 记录时机不同，binglog是在commit之后再一次性记录的，redolog是事务中执行的时候一条一条记录的

  3. binlog是记录执行语句，redolog是记录物理块的数据变更

posted @ 2024-06-18 22:47  小小zboy  阅读(12)  评论(0)  编辑  收藏  举报