Mysql 面试题复习

什么是索引

索引是一种数据结构，目的是加速查询速度，mysql innodb的索引默认是B+树，索引类型有普通索引、主键索引、唯一索引、前缀索引

有哪些存储引擎

• innodb是事务性存储引擎，应用是最广泛的，支持事务、外键、聚集索引
• myisam不支持事务，表级别的锁，表锁导致性能问题，而且崩溃后无法恢复，优点有查询速度快，存储的时候有压缩数据，不支持事务、没有外键、非聚集索引

什么是B+树

B+树是B树的一种，

1. B+树所有非叶子节点的值，都在叶子节点都冗余了一份
2. 每个节点可以有多个子节点
3. 叶子节点是双链接接口
4. 叶子节点是排好序的

高度为3的B+树能存多少数据

mysql的索引节点都是一个page形式存在，包含目录、数据、指针，指针再mysql中占用6个字节。每个page可以存16kb的数据，三层可以存入千万级别的数据

B+树/B树/hash索引

1. hash索引在内存中读取确实快，但是如果是磁盘io，那就慢，因为是随机读取，而B+树是按page读取的是顺序读取，并且hash不支持范围查询，不支持事务（不支持范围锁）
2. B树内的每个节点都存储数据，这就注定了它的树层数会更多，同样3层，它存的数据少，层级越高意味着io越多

聚集索引/聚簇索引

把主键当作索引的索引，叶子节点存的是主键的值+行记录

非聚簇索引/联合索引

不是以主键为索引的其他字段或者其他多个字段联合索引，需要注意的是，这种索引接口，叶子节点存储的是字段索引值+主键的值

覆盖索引

首先索引覆盖是优点，比如以b,c,d为联合索引，查询的时候select b from table where b = 1，那么就是覆盖索引（use index），因为只返回索引b的值，并且不需要回表，速度快

索引失效

1. 范围查找的时候，由于范围太大，比如select * from table where b > 1，需要回表很多次，不如直接全表扫描
2. select * + order by会导致回表很多次
3. 谨慎用or，比如select * from table where b = 1 or c = 2 ,这索引会失效
4. varchar类型的索引，如果给的查询条件是整型，也会导致失效，比如 select mac from table where mac =1 ,其中mac是varchar类型，这不会用到索引

最左前缀原则

1. 为啥一定要最左前缀，因为联合索引b、c、d所生成的索引树，需要比较大小，首先比较的是b，如果没有给b，就不能确定这颗树怎么往下走，是往左还是往右是不清楚的
2. 跟where条件顺序无关，mysql优化器会对where条件重新排列

varchar类型与整型比较

1. 会默认将字符转为数字0 -- varchar类型转换,字符与int类型比较，默认都转为0 select 'a' = 0; select 'b2' = 0; select 'c1233' = 0; select '123' = 123; -- 123b这种不会转为0 select '123b' = 1;
2. 危险操作，下面mac是varchar类型，会查出所有数据
   1. select * from ad where mac =0;

ACID的含义

1. 原子性：多条语句要么全部执行要么全部不执行
2. 一致性：执行前后数据是一致的，比如说：A给B账号增加100元，在A提交的时候报错了，那A/B的账户余额跟之前未操作的结果是一致的
3. 隔离性：事务之间是隔离运行的
4. 持久性：数据最终落盘持久化

事务并发出现的问题

mysql默认的隔离级别是可重复读

1. 脏读，事务A进行更新，事务B进行2次读取，当事务A更新了对应的值但是出现回滚了，这时候被事务B读取到了，就是脏读，强调读取的数据是错的。
2. 不可重复度：同样事务A进行更新，事务B进行2次去读取，返回的结果不一样
3. 幻读：事务A进程insert操作，事务B进行2次计算count读取，当事务A发生回滚，2次count的结果不一样

mysql中的锁

参考文章：

https://zhuanlan.zhihu.com/p/48127815

对于UPDATE、DELETE、INSERT 语句，InnoDB会自动给涉及数据集加排他锁(X) MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁，在执行增、删、改 操作前，会自动给涉及的表加写锁，这个过程并不需要用户干预

1. 全局锁：
2. 行锁/记录锁：粒度小，冲突少，锁主表的一条或者多条记录
3. 表锁：粒度大，不会有死锁，性能差
4. 页锁：锁一页数据
5. 间隙锁：gap lock；锁住表的一个区间，左开右闭。
   • 建表测试

CREATE TABLE `test` (
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `name` varchar(8) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
INSERT INTO `test` VALUES (1, '小罗');
INSERT INTO `test` VALUES (5, '小黄');
INSERT INTO `test` VALUES (7, '小明');
INSERT INTO `test` VALUES (11, '小红');

1. 主键索引的间隙锁，如果命中等值就是行锁，如果是没有命中或者范围查找，根据mysql版本不同而不同
   • • mysql5.7版本

# 事务A，注意没有commit
BEGIN;
SELECT * from test where id BETWEEN 5 AND 7 for update;

# 事务B
INSERT INTO `test` (`id`, `name`) VALUES (4, '小张1'); -- 成功
INSERT INTO `test` (`id`, `name`) VALUES (5, '小张1'); -- 阻塞
INSERT INTO `test` (`id`, `name`) VALUES (6, '小张1'); -- 阻塞
INSERT INTO `test` (`id`, `name`) VALUES (7, '小张1'); -- 阻塞
INSERT INTO `test` (`id`, `name`) VALUES (8, '小张1'); -- 阻塞
INSERT INTO `test` (`id`, `name`) VALUES (12, '小张1'); -- 成功

# 结论
间隙锁在mysql5.7版本中锁的[5,7],[7-11]

• • mysql8.0版本

# 事务B
INSERT INTO `test` (`id`, `name`) VALUES (4, '小张1'); -- 成功
INSERT INTO `test` (`id`, `name`) VALUES (5, '小张1'); -- 阻塞
INSERT INTO `test` (`id`, `name`) VALUES (6, '小张1'); -- 阻塞
INSERT INTO `test` (`id`, `name`) VALUES (7, '小张1'); -- 阻塞
INSERT INTO `test` (`id`, `name`) VALUES (8, '小张1'); -- 成功
INSERT INTO `test` (`id`, `name`) VALUES (12, '小张1'); -- 成功

# 结论
间隙锁在8.0中锁的是[5-7]

2. 普通字段或者普通索引的间隙锁，即使是等值查询也是间隙锁，锁定的区间是[1-7)
   • • 建表和数据

CREATE TABLE `test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `num` int unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `num_index` (`num`)
) ENGINE=InnoDB AUTO_INCREMENT=138334 DEFAULT CHARSET=utf8;

INSERT INTO `test` VALUES (1, 1);
INSERT INTO `test` VALUES (5, 5);
INSERT INTO `test` VALUES (7, 7);
INSERT INTO `test` VALUES (11, 11);

• • mysql 5.7

BEGIN;
SELECT * from test where num = 5 for UPDATE;

INSERT INTO `test` VALUES (100, 1);--阻塞
INSERT INTO `test` VALUES (100, 3);--阻塞
INSERT INTO `test` VALUES (100, 5);--阻塞
INSERT INTO `test` VALUES (100, 6);--阻塞
INSERT INTO `test` VALUES (100, 7);--通过

# 结论
锁的范围是[1-7)

• • mysql 8.0

INSERT INTO `test` (`id`, `num`) VALUES (60, 1); --阻塞
INSERT INTO `test` (`id`, `num`) VALUES (60, 2); --阻塞
INSERT INTO `test` (`id`, `num`) VALUES (60, 4); --阻塞
INSERT INTO `test` (`id`, `num`) VALUES (60, 5); --阻塞
INSERT INTO `test` (`id`, `num`) VALUES (60, 6); --阻塞
INSERT INTO `test` (`id`, `num`) VALUES (70, 7); -- 运行

# 结论
锁的范围是[1-7)

1. 临建锁：将查询出来的记录锁住+间隙锁
2. 排他锁/写锁：
   1. SELECT * FROM test WHERE id = 1 for UPDATE;
   2. 事务A对某条数据加排它锁后，不允许其他的事务对他进行加读锁和写锁。但是可以读取数据
3. 共享锁/读锁：
   1. SELECT * FROM test WHERE id = 1 LOCK IN SHARE MODE;
   2. 允许其他的事务给他加读锁，不能加写锁。直接select 可以读取数据,
   3. 如果在事务A中，加了读锁之后并且有update操作，那么事务B不能加读锁，会阻塞掉，这个道理很简单，只要是update/delete操作，就会加排它锁。
4. 意向锁：意向共享锁/意向排它锁
5. 死锁：
   1. 如何查看死锁：show engine innodb status
   2. 解决方式：设置innodb_blocklocl_timeout时间，或者设置定时扫描死锁

索引的设计原则

1. 经常出现在where条件的字段考虑加索引
2. 索引不是越多越多，会增加存储空间和影响插入性能
3. 如果已经有了索引a,又需要给b.c 加索引，可以考虑更新索引a，将a,b,c改为联合索引，而不是添加索引
4. 对varchar类型的加索引可以使用前缀索引

怎么处理慢查询

1. 是否是数据量太大导致的，可以考虑分表，或者其他搜索引擎，es
2. explan 是否走了索引，load的列是否都是需要的列，减少不需要的列
3. offset和limit优化
4. 机器性能/网络是否有问题
5. 索引和内容分开存储

mysql中如何保证ACID

AID的目的保证C过程顺利，一致性是目的

• 存储引擎：undolog/redolog
• mysql sql：binlog

undolog 保证原子性，执行错了能够回滚

隔离性是用MVCC保证的

持久性由内存+redolog保证，如果宕机了，可以由redolog恢复，当然binlog也可以。

• innodb 下把记录写入redolog刷盘，事务状态变为prepare
• 第二步如果prepare成功了，就将事务日志写入binlog，
• 第三步如果写入binlog成功，就进入commit状态，并在redolog中将prepare状态修改为commit状态

主从同步原理

通过订阅binlog，复制binlog

MVCC原理

含义：多版本并发控制，只在read commit和repeatable read会启用。读未提交和串行化不会用到！