MySQL面试题

1、回滚机制

2、乐观锁、悲观锁实现

实现并发控制的主要手段大致可以分为乐观并发控制和悲观并发控制两种。
乐观锁比较适用于读多写少的情况(多读场景)，悲观锁比较适用于写多读少的情况(多写场景)。

  • 乐观锁

    依据数据本身来保证数据的正确性。乐观锁适用于读操作多的场景，这样可以提高程序的吞吐量

    【CAS 实现】：Java 中java.util.concurrent.atomic包下面的原子变量使用了乐观锁的一种 CAS 实现方式。

    【版本号控制】(MVVC 多版本并发控制)：每一行都有create_version，delete_version，每个事务都有一个自增的事务版本号。

    • select：create_version < 事务版本号 and (delete_version > 事务版本号 or delete_version == null)
    • insert：set create_version = 事务版本号
    • update：先删后插
    • delete：set delete_version = 事务版本号

  • 悲观锁

    基于数据库提供的锁机制。

    **共享锁【shared locks】**又称为读锁，简称S锁。顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

    **排他锁【exclusive locks】**又称为写锁，简称X锁。顾名思义，排他锁就是不能与其他锁并存，如果一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据行读取和修改。

    悲观并发控制实际上是“先取锁再访问”的保守策略，为数据处理的安全提供了保证。但是在效率方面，处理加锁的机制会让数据库产生额外的开销，还有增加产生死锁的机会。另外还会降低并行性，一个事务如果锁定了某行数据，其他事务就必须等待该事务处理完才可以处理那行数据。

6、3层的b+树大概能存多少数据

如果是在主键上建立的索引就是聚簇索引，即只有在叶子节点才存储行数据，而非叶子节点里面的内容其实是键值和指向数据页的指针。

假设一行数据的大小是1k，那么一个叶子节点页可以存放16行这样的数据。

假设主键ID为bigint类型，长度为8字节（byte），而指针大小在InnoDB源码中设置为6字节（byte），这样一共14字节（byte），因为一个页可以存放16k个byte，所以一个目录页可以存放的指针个数为16384/14=1170个

三层b+树大概可以存1170*1170*16 = 21902400行数据。

7、数据库范式

第一范式

第一范式无重复的列，表中的每一列都是拆分的基本数据项，即列不能够再拆分成其他几列，强调的是列的原子性。

如果在实际场景中，一个联系人有家庭电话和公司电话，那么以“姓名、性别、电话”为表头的表结构就没有达到 1NF。要符合 1NF 我们只需把电话列拆分，让表头变为姓名、性别、家庭电话、公司电话即可。

第二范式

第二范式属性完全依赖于主键，首先要满足它符合 1NF，另外还需要包含两部分内容：

  • 表必须有一个主键；
  • 没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。即要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。

第三范式

第三范式属性不传递依赖于其他非主属性，首先需要满足 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。

第二范式和第三范式的区别

  • 第二范式：非主键列是否依赖主键（包括一列通过某一列间接依赖主键），要是有依赖关系就是第二范式；
  • 第三范式：非主键列是否直接依赖主键，不能是那种通过传递关系的依赖。要是符合这种依赖关系就是第三范式。

通过对前三个范式的了解，我们知道 3NF 是 2NF 的子集，2NF 是 1NF 的子集。

范式优缺点

范式具备以下优点：

  • 避免数据冗余，减少维护数据完整性的麻烦；
  • 减少数据库的空间；
  • 数据变更速度快。

同时，也有如下缺点：

  • 按照范式的规范设计的表，等级越高的范式设计出来的表数量越多。
  • 获取数据时，表关联过多，性能较差。

表的数量越多，查询所需要的时间越多。也就是说所用的范式越高，对数据操作的性能越低。

8、

9、WAL技术与两阶段提交

10、给数据表加个字段，结果搞挂了

原因：表数据量虽然小，却是一个热点表，访问频率特别高，而且该表的访问是在一个大事务中。加字段的时候一直在等待获取MDL写锁。这个等待也影响了后续表访问对MDL读锁的获取，导致后面的查询也都被堵塞了。更惨的是，客户端有重试机制，查询堵塞超过超时时间会再起一个session进行请求，导致数据库的线程池很快就爆满了，直接挂掉。

解决方法：在alter table语句里设置等待时间，如果拿不到MDL写锁就放弃。之后挑选访问量小的时间再重试。

11、死锁和死锁检测

死锁原因：双方互相等待对方占用的锁。

解决方法：

  • 直接进入等待，直到超时。超时时间可以通过innodb_lock_wait_timeout来设置

  • 发起死锁检测，发现死锁后，主动回滚某一个事务，让其他事务得以执行。

    死锁检测，即每当一个事务被锁时，查看它所依赖的线程有没有被别人锁住，判断是否出现循环等待。

12、MySQL为什么有时会选错索引

选择索引是优化器的工作。优化器找到一个最优的执行方案，并用最小的代价去执行语句。

扫描行数是影响执行代价的因素之一，还会结合是否使用临时表、是否排序等因素进行综合判断。

  • 扫描行数是怎么判断的？

    MySQL在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数。这个统计信息就是索引的“区分度”。一个索引上不同的值的个数，我们称之为“基数”，这个基数越大，索引的区分度越好。

  • 这个基数是怎么得来的？

    采样统计。默认会选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过1/M的时候，会自动触发重新做一次索引统计。

解决方式：

  • 采用force index强行选择一个索引，但是这样不利于迁移。
  • 修改sql语句引导使用正确索引。如，order by b => order by b,a
  • 新建更合适的索引或删除不合适的索引。

13、给字符串加索引(前缀索引 or ?)

  • 普通索引与前缀索引对比

    • 普通索引(整个字符串的索引结构)

      从索引树中找到’zhangssxyz@xxx.com’记录，回表。

      只需回表一次。

    • 前缀索引(即email(6)索引结构)

      从索引树找到满足索引值是’zhangs’的多条记录，回表，判断值是否为’zhangssxyz@xxx.com’

      使用前缀索引后，可能会导致查询语句读数据的次数变多。

  • 尽量使用覆盖索引

  • 其他方式

    • 倒序存储。存储倒序字符串。
    • hash字段。在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。

14、重建表

删除数据后，并不是把数据完全删除，而是将其加入free链表等待复用(insert时重新使用)，会产生碎片，所以并不会减少磁盘空间(只有两个数据页合并时才会)。

同样，添加数据后的数据页分裂，也会造成数据页利用率低。

解决办法是重建表alter table A engine=InnoDB或optimize table A

  • MySQL5.5之前

    创建临时表、转存数据、交换表名、删除旧表

  • MySQL5.6之后

    Online DDL，对这个操作流程做了优化。

    • 执行alter语句时，获取MDL写锁(防止其他MDL操作)
    • 之后释放MDL写锁，转为读锁，可以让其他语句并行操作
    • 将数据写入临时文件，同时将操作记录在一个日志文件
    • 临时文件生成后，将日志文件中的操作应用到临时文件
    • 用临时文件替换原数据文件

15、sql语句执行慢的原因

  • 等MDL锁

    MDL写锁等待其他事务，当前事务等待MDL锁。

    5.7版本后，获取到MDL写锁后就会释放，转为MDL读锁

  • 等flush

    flush tables t with read lock;
flush tables with read lock;

  • 等行锁

    X S 锁

  • 查询慢

    • 实例一

      select * from t where id=1;
select * from t where id=1 lock in share mode;  #加读锁却更快？

      带lock in share mode的SQL语句，是当前读，因此会直接读到1000001这个结果，所以速度很快；

      而select * from t where id=1这个语句，是一致性读，因此需要从1000001开始，依次执行undo log，执行了100万次以后，才将1这个结果返回。

      Snip_09-13_21-19-48

16、误删数据后怎么办？

误删行

用Flashback工具恢复，通过修改binlog

误删库/表

  • 全量备份，加增量日志。(要求线上有定期的全量备份，并且实时备份binlog)

    假如有人中午12点误删了一个库，恢复数据的流程如下：

    1. 取最近一次全量备份，假设这个库是一天一备，上次备份是当天0点；
    2. 用备份恢复出一个临时库；
    3. 从日志备份里面，取出凌晨0点之后的日志；
    4. 把这些日志，除了误删除数据的语句外，全部应用到临时库。

  • 设置临时库为备库，加速恢复

  • 延迟复制备库

    延迟一小时的备库，若一小时内发现了问题，就截断备份，删除那条语句后再备份

17、kill命令(杀死sql)

kill query +线程id，表示终止这个线程中正在执行的语句；

kill connection +线程id，这里connection可缺省，表示断开这个线程的连接，当然如果这个线程有语句正在执行，也是要先停止正在执行的语句的

当用户执行kill query thread_id_B时，MySQL里处理kill命令的线程做了两件事：

  1. 把session B的运行状态改成THD::KILL_QUERY(将变量killed赋值为THD::KILL_QUERY)；
  2. 给session B的执行线程发一个信号。

  • 锁等待时，可以kill掉

  • 线程数不足时，可能杀不死(假设并发线程上限数为2)

    等行锁时，线程可以被唤醒；

    但是线程不足时，会每10毫秒判断一下是否可以进入InnoDB执行，如果不行，就进入sleep状态。

    虽然12号线程的状态已经被设置成了KILL_QUERY，但是线程还在sleep，并没有去判断线程的状态，因此根本不会进入终止逻辑阶段。

  • 杀死一个大事务，回滚耗时可能比较长

18、如何防止数据库被其他人修改

1、权限最小化。设置账户，每个账户有不同的权限

2、数据库的主机的安全策略。在防火墙设置按会话流量切断连接的的规则，一个SSH会话的流量超过100K之后就会强制断开，因此，就算你想select *，然后手动从终端上复制数据出来，能得到的数据量都非常有限。

3、配置只允许指定ip访问

4、若数据无需更改，则设置lock table t_depart_info read，设置表级读锁

19、查这么多数据会不会把数据库内存打爆？

MySQL是“边读边发的”，服务端并不需要保存一个完整的结果集。取数据和发数据的流程是这样的：

1- 获取一行，写到net_buffer中。这块内存的大小是由参数net_buffer_length定义的，默认是 16k。

2-重复获取行，直到net_buffer写满，调用网络接口发出去。

3-如果发送成功，就清空net_buffer，然后继续取下一行，并写入net_buffer。

4-如果本地网络栈（socket send buffer）写满了，进入等待。直到网络栈重新可写，再继续发送。

20、临时表

使用规则：

  • 每个session的临时表是隔离的，不能互相访问
  • 临时表可以和真实表重名，访问时优先访问临时表

什么时候会使用临时表？

  • union

    会将结果放入临时表，以达到去重的效果。(union all会直接放入结果集)

  • group by

    select id%10 as m, count(*) as c from t1 group by m;

    1-创建内存临时表，表里有两个字段m和c，主键是m；

    2-扫描表t1的索引a，依次取出叶子节点上的id值，计算id%10的结果，记为x；

    ​ 2.1-如果临时表中没有主键为x的行，就插入一个记录(x,1);

    ​ 2.2-如果表中有主键为x的行，就将x这一行的c值加1；

    3-遍历完成后，再根据字段m做排序，得到结果集返回给客户端。

    group by 优化方式

    • 尽量走索引

      当碰到第一个1的时候，已经知道累积了X个0，结果集里的第一行就是(0,X);

      当碰到第一个2的时候，已经知道累积了Y个1，结果集里的第一行就是(1,Y);

      不需要临时表，不需要额外排序

    • 若对排序没有要求，可以直接加上order by null

21、自增id不连续的原因

自增主键可以让主键索引尽量地保持递增顺序插 入，避免了页分裂，因此索引更紧凑。

但是自增id不一定都是连续的。

自增值保存在哪儿？

  • MYISAM保存在数据文件里
  • InooDB
    • 5.7之前，保存在内存，重启之后把max(id)+1作为自增值
    • 8.0之后，将自增值的变更记录在了redo log中，重启的时候依靠redo log恢复 重启之前的值。

自增不连续的原因

  • 唯一键冲突或者回滚，会导致数据插入失败，但是自增值不能回到原来的值

  • 自增锁的优化问题

    参数innodb_autoinc_lock_mode为0时，语句执行结束后才释放自增锁；

    为1时，普通insert语句，自增锁在申请之后就马上释放；类似insert …select这样的批量插入数据的语句，还是要等语句结束后才被释放；

    为2时，所有的申请自增主键的动作都是申请后就释放锁。

    批量插入数据时，如果binlog格式为statement，可能导致主备数据不一致，因此需要使用row格式。

    另外，MySQL有一个批量申请自增id的策略：

    语句执行过程中，第一次申请自增id，会分配1个；1个用完以后，这个语句第二次申请自增id，会分配2个； 第三次申请自增id，会分配4个； 依此类推，同一个语句去申请自增id，每次申请到的自增id个数都是上一次的两倍。导致自增不连续。

22、自增id用完了怎么办？

1- 表的自增id达到上限后，再申请时它的值就不会改变，进而导致继续插入数据时报主键冲突的错误。

2- row_id(6字节)达到上限后，则会归0再重新递增，如果出现相同的row_id，后写的数据会覆盖之前的数据。

3- Xid只需要不在同一个binlog文件中出现重复值即可。虽然理论上会出现重复值，但是概率极小，可以忽略。

4- thread_id自增超过大小就重置为0

5- InnoDB的max_trx_id递增值每次MySQL重启都会被保存起来。

只读事务并不会申请trx_id，即trx_id为0，但是显示给我们时是一个很大的值(trx_id变量地址+2^48)。因为只读事务不会影响可见性判断，而且这样做可以减小事务视图里活跃事务数组的大小；可以减少trx_id的申请次数。

trx_id超过大小重置为0，可能在这时会造成脏读。

22、如何复制一个表？

  1. 物理拷贝的方式速度最快，尤其对于大表拷贝来说是最快的方法。如果出现误删表的情况， 用备份恢复出误删之前的临时库，然后再把临时库中的表拷贝到生产库上，是恢复数据最快 的方法。

    但是，这种方法的使用也有一定的局限性： 必须是全表拷贝，不能只拷贝部分数据； 需要到服务器上拷贝数据，在用户无法登录数据库主机的场景下无法使用； 由于是通过拷贝物理文件实现的，源表和目标表都是使用InnoDB引擎时才能使用。

  2. 用mysqldump生成包含INSERT语句文件的方法，可以在where参数增加过滤条件，来实现 只导出部分数据。这个方式的不足之一是，不能使用join这种比较复杂的where条件写法。

  3. 用select …into outfile的方法是最灵活的，支持所有的SQL写法。但，这个方法的缺点之一就 是，每次只能导出一张表的数据，而且表结构也需要另外的语句单独备份。 后两种方式都是逻辑备份方式，是可以跨引擎使用的。

23、分区表

1- MySQL在第一次打开分区表的时候，需要访问所有的分区；

2- 在server层，认为这是同一张表，因此所有分区共用同一个MDL锁；

3- 在引擎层，认为这是不同的表，因此MDL锁之后的执行过程，会根据分区表规则，只访问必要的分区。

如，加gap锁时，只会加到分区表的边界

4- 对业务透明，使用分区表的业务代码更简洁

5- 分区表可以很方便的清理历史数据，可以直接通过alter table t drop partition …这个语法删掉分区

hhhhh