mysql面试题

文章目录

• 一. 引擎
• • 1.1 引擎类型
  • 1.2 Inonodb和MyiSAM区别
• 二. 索引
• • 2.1 B+树
  • • 2.1.1 B-tree和B+tree区别
    • 2.1.2 一颗B+树能存储多少数据
  • 2.2 索引类型
  • 2.3 索引原则
  • 2.4 慢sql的原因
  • 2.5 索引失效情况
• 三. 事务
• • 3.1 四大特性及保证机制
  • 3.2 四大隔离级别
  • 3.3 MVCC
• 四. 锁
• • 4.1 锁类型
  • • 4.1.1 行锁
    • 4.1.2 页锁
    • 4.1.2 表锁
    • 4.1.3 全局锁
  • 4.2 锁算法
  • • 4.2.1 记录锁
    • 4.2.2 间隙锁
    • 4.2.3 临建锁（Next-key）
  • 4.3 快照读和当前读的加锁
• 五. 主从复制
• • 5.1 执行流程
  • 5.2 产生问题
  • 5.3 复制类型
• 六.分库分表
• • 6.1 类型
  • 6.2 常见拆分方式
  • 6.3 问题瓶颈

一. 引擎 1.1 引擎类型

● Innodb：默认
● MyISAM：不支持事务
● memory：基于内存，存储临时表
● archive：支持insert和select，用于日志收集
● csv：将csv文件转换成数据表

1.2 Inonodb和MyiSAM区别

● Inonodb支持事务、支持外键、索引采用聚簇索引、不存储总行数、支持行锁
● MyiSAM不支持事务、不支持外键、索引采用非聚簇索引（索引文件、数据文件、结构文件）、支持总行数、支持表锁

二. 索引 2.1 B+树 2.1.1 B-tree和B+tree区别

• B+tree是B-tree的升级版
• B-tree非叶子节点存储索引地址和数据，叶子节点无指针相连
• B+tree非叶子节点存储索引地址，叶子节点存储数据，且叶子节点有指针相连

2.1.2 一颗B+树能存储多少数据

一页能存储16k的数据，非叶子节点存储主键（bigint：8字节）和指针（6字节），因此161024/14=1170.假设树的高度为3，叶子节点存储的数据大小为一行为1k,总共能存储16行数据，总数据为11701170*16，大概为2000w

2.2 索引类型

• 主键索引、唯一索引、普通索引、前缀索引、联合索引

2.3 索引原则

1. 三星索引

• 一星：where后面的谓词和索引列越多，索引片越窄越好
• 二星：避免排序，结果集采用现有顺序排序，即不出现like等条件
• 三星：索引行包含查询条件的所有列

2. 最左索引
   创建联合索引后，每次查询尽量遵循最左原则
3. 覆盖索引
   查询的字段建立了索引

2.4 慢sql的原因

1. 未走索引
2. 索引非最佳
3. 是否查询出多余字段
4. 数据量过大，考虑分库分表
5. 检查机器性能配置

2.5 索引失效情况

1. 全表扫描比走索引更快
   select * 时用到order by排序，虽然有索引树，但回表反而更消耗内存
2. 未遵循最左索引原则
3. 对字段进行了隐式替换或对字段进行操作
4. 采用=null或！=null等条件
5. 采用or字段

三. 事务 3.1 四大特性及保证机制

1. 原子性

• 要么成功要么失败，通过undo log保证

2. 一致性

• 事务操作前后数据保持一致，通过三大特性保证

3. 隔离性

• 当前事务的操作不受其他事务影响，通过MVCC机制保证

4. 持久性

• 数据文件一旦写入磁盘，可永久保存

3.2 四大隔离级别

1. RU(Read Uncommited)：读未提交

• 会出现脏读
  

2. RC(Read Commited)：读已提交

• 会出现不可重复读
  

3. RR(Repeatable Read)：可重复读

• 可能出现幻读
  
  幻读解决方法
• 如果查询某条数据，可加读共享锁
• 如果查询多条数据，可加间隙锁或临建锁

4. Serializable：串行化

3.3 MVCC

针对Innodb下的RC和RR进行多版本并发控制

上图四个事务同时执行
RC级别结果：select1:name=张三，select2:name=张小三
RR级别结果：select1:name=张三，select2:name=张三

原因分析：

undo_log版本链记录了每个事务对于同个数据的操作记录，MVCC通过ReadView进行数据判断

ReadView结构

• m_ids:当前活跃的事务编号集合
• min_trx_id:最小活跃事务编号
• max_trx_id:预分配事务编号，当前最大事务编号+1
• creator_trx_id:ReadView创建者的事务编号

RC分析：

对于select1，当前事务id为4,在活跃集合里，说明数据未提交，所以根据版本链查询到事务id为1的结果为张三，对于select2，同理根据版本链查询到事务id为2的结果为张小三，因此会产生不可重复读

RR分析：

• select2会复用select1的ReadView，因此可以解决不可重复读的问题
• 但如果两个快照读之间出现当前读，则会产生新的ReadView，即会出现幻读的结果

快照读和当前读

• select属于快照读
• update,insert,delete,for update,lock in share mode属于当前读

四. 锁 4.1 锁类型 4.1.1 行锁

• 会产生死锁:事务1对某条数据进行update操作为提交，事务2对相同事务进行update操作。
  共享锁，排他锁，自增锁

4.1.2 页锁

• 会产生死锁

可自定义页大小

4.1.2 表锁

• 采用or字段，行锁将升级成表锁
• 事务1update操作包含or字段且为提交，事务2对同表其他数据进行update操作将失败。

表共享锁，表排他锁，意向锁

4.1.3 全局锁

Flush tables with read lock ,一般用于全库备份

4.2 锁算法 4.2.1 记录锁

锁一条具体的数据

4.2.2 间隙锁

userid(1,4,9) update user set xxx where userid=5
RR级别下锁住（5,9），防止产生幻读
尽量较少使用，否则容易造成数据库性能低下

4.2.3 临建锁（Next-key）

• 非唯一索引精准匹配时采用
  间隙锁+右记录锁，对于上条SQL，锁住[5,9]

4.3 快照读和当前读的加锁

• 当前读会默认加锁
  update user set age = 10 where id =1 -----加行锁
  update user set age = 10 where id =1 or id =2 -----未commit，行锁升级成表锁
  update user set age =10 where id > 1 and id < 5 -----加间隙锁
• 快照读需手动加锁
  select * from user where id =1 for update-----加行锁(id为主键索引)
  age(1,2,3,5,7)
  select * from user where age >2 and age < 5 for update -----加间隙锁(2,5)
  select * from user where age =3 for update -----加临建锁(3,5]
• 索引应尽量避免非唯一索引，减少锁的范围。

五. 主从复制

5.1 执行流程

1. 数据写入主库，从而同步到binlog文件，log dump线程监测到binlog数据变化，读取其内容并发送到从节点
2. 从节点I/O线程接收binlog内容，记录到relay log文件
3. 从节点SQL线程读取relay log文件内容，从而保证数据一致性

5.2 产生问题

如果主机宕机，binlog文件未记录到最后一次数据，当从节点变成主机时，可能会产生数据不一致的问题，不过这种情况概率极小。

5.3 复制类型

1. 全同步复制
   主库写入binlog后，所有从库执行完毕后返回给客户端，性能较低。
2. 半同步复制
   从库执行完毕返回ACK给主库，主库收到至少一个从库的确认即为操作完成。

六.分库分表 6.1 类型

1. 垂直分片

• 将多个数据表拆分到不同库中，并不能解决数据量大的问题。

2. 水平分片

• 将一个表中的数据拆分到不同的库或表中，能从根据上解决数据量大的问题。

6.2 常见拆分方式

1. 主键取模：优点-均匀存放数据，缺点-扩容麻烦
2. 按时间分：如月份，热点数据
3. 按枚举值分：如地区

6.3 问题瓶颈

垮库查询，垮库排序，分布式事务，主键重复，公共表等。