MySQL
MySQL
1、索引
1）聚簇索引
- 一个表只能有一个
- 索引搜索直接指向包含所有行数据页，节省磁盘 I/O 操作
- 数据表就是按顺序存储的，物理上是连续的
- 物理存储按照索引排序
2）非聚簇索引
- 一个表可以有多个
- 物理存储不按照索引排序
3）最左前缀匹配
- 读取创建索引时 指定的字段顺序(从左到右)
- 在构建B+tree非叶子节点时,其会按照 字段从左到右的顺序进行组合,并依次对字段存储的数据按照从小到大的顺序进行排序;最终保证整体索引数据的有序性
- 比如创建了一个（a, b, c）联合索引，就相当于创建了(a), (a, b) , (a, b, c) 2、日志
4）索引底层数据结构为什么使用B+树而不是B树？
-
B树的所有分支节点和子节点都有data区，遍历的时候IO操作频繁，效率低下，而B+树只有叶子节点才保存了数据，效率高。
-
B+树更擅长范围查询，因为B+树的叶子节点是双向链表且不需要从根节点开始 而B树的范围查询是中序遍历
-
B+树的分支节点，没有数据，只有索引，所以占内存小，可以把所有的索引加载到内存中
5）建立索引场景
适合加索引的场景：
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询与其它表关联的字段，外键关系建立索引（多表关联查询）
- 单键、组合索引的选择问题，组合索引性价比更高（mysql自动选择最优）
- 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
- 查询中统计或者分组字段（（分组order by )包含(排序group by））
不适合建立索引的场景：
- 表记录太少
- 经常增删改的表或者字段（例如电商项目的用户余额）
- Where条件里用不到的字段不创建索引
- 过滤性不好的不适合建索引，比如性别
2、三大范式
第一范式： 每个列都不可以再拆分。
第二范式： 在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。
第三范式： 在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。
3、存储引擎
1） InnoDB
- InnoDB 支持事务，是事务安全的
- InnoDB 支持行锁，提高了多用户并发的性能
- InnoDB 支持外键
- InnoDB 有 undo log ， redo log
- InnoDB 所有的文件都是存放在一起的
- 如果你的数据执行大量的 INSERT 或 UPDATE，出于性能方面的考虑，应该使用 InnoDB 表
聚簇索引+辅助索引
2）MyISAM
- MyISAM 不支持事务，强调的是性能，每次查询具有原子性
- MyISAM 支持表锁
- MyISAM 不支持外键
- 如果执行大量的SELECT，MyISAM是更好的选择
- MyISAM 索引和数据是分开存放的
4、MySQL日志模块
1）bin log
- 存在于mysql的server层
- 采用追加的方式记录
- 记录数据库 结构变更，或者增删改
2）redo log
- 属于InnoDB 私有的
- 采用循环写，循环擦除方式记录
- 当发生数据修改（增、删、改）的时候，InnoDB引擎会先将记录写到redo log中，并更新内存，此时更新就算完成了。同时InnoDB引擎会在合适的时机将记录刷到磁盘中。
3）undo log
- 原子性底层就是通过undo log实现
- undo log是逻辑日志，可以理解为：记录和事务操作相反的SQL语句，事务执行insert语句，undo log就记录delete语句。它以追加写的方式记录日志，不会覆盖之前的日志。
5、事务与隔离级别
1）什么是事务？
事务是把一组密不可分的操作系列集合在一起，这些操作要么全部执行，要么全部不执行。
2）事务的四大特性
- 原子性（Atomicity）：事务作为一个整体被执行 ，要么全部执行，要么全部不执行；
- Innodb 通过undo log 来实现原子性（追加）
- 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行；
- 读写锁和MVCC实现
- 持久性（Durability）：一个事务一旦提交，对数据库的修改应该永久保存
- 持久性是通过redo log来实现的（循环写）
- 一致性（Consistency）：保证数据库状态从一个一致状态转变为另一个一致状态；
- 一致性就是原子性持久性和隔离性一起实现的
3）事务四种隔离级别
- 读未提交 ： 一个事务还没提交，但它的变更也会被其他的事务看到
- 读已提交 ： 一个事务未提交，那么它的变更就不会被其他的事务看到
- 可重复读（默认） ： 一个事务执行过程中看到的，与开始事务时看到的事务是一致的
- 串行化 ： 对于同一行记录，读会加读锁，写会加写锁
4）四种并发问题
- 脏读 ：如果mysql中一个事务A读取了另一个并行事务B未最终提交的写数据, 那事务A的这次读取就是脏读。
- 读提交
- 不可重复读 ： 如果在一个事务中多次读取同一个数据, 正好在两次读取之间, 另外一个事务确实已经完成了对该数据的修改并提交, 那问题就来了: 可能会出现多次读取结果不一致的现象。针对的是单条记录 修改
- 可重复读
- 幻影读 ： 在一次事务里面，多次查询之后，结果集的个数不一致的情况叫做幻读。而多出来或者少的哪一行被叫做幻行。针对的是整个表 插入和删除
- next-key 锁 ：记录锁（行锁）、间隙锁（间隙锁 给一个范围内的数据加锁）
- 丢失更新 ： 事务A还没有提交之后，但是这个时候事务B更新了数据，那么事务A就丢失更新了。
- 乐观锁+悲观锁
6、锁
1）表级别和行级别
MyISAM 只支持表锁，InnoDB 支持表锁和行锁，默认为行锁。
表级锁：开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发量最低。
行级锁：开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发度最高。
2 ）死锁
死锁解决
- 业务逻辑
- 指定锁的获取顺序
- 大事务拆分成各个小事务
- 在同一事务中，一次锁定尽量多的资源，减少死锁概率
- 给表建立合适的索引以及降低事务的隔离级别等
- 数据库配置
- 超时时间 innodb_lock_wait_timeout
- 死锁检测主动回滚
排查死锁步骤：
- 查看死锁日志show engine innodb status;
- 找出死锁Sql
- 分析sql加锁情况
- 模拟死锁案发
- 分析死锁日志
- 分析死锁结果
3）在高并发情况下，如何做到安全的修改同一行数据？
使用悲观锁：
- 当前线程要进来修改数据时，别的线程都得拒之门外
# 没用索引/主键的话就是表锁，否则就是是行锁
select * from User where name=‘jay’ for update
以上这条sql语句会锁定了User表中所有符合检索条件（name=‘jay’）的记录。本次事务提交之前，别的线程都无法修改这些记录。
使用乐观锁：
- 乐观锁思想就是，有线程过来，先放过去修改，如果看到别的线程没修改过，就可以修改成功，如果别的线程修改过，就修改失败或者重试。
- 实现方式：乐观锁一般会使用版本号机制或CAS算法实现。
数据库的乐观锁需要自己实现，在表里面添加一个 version 字段，每次修改成功值加 1，这样每次修改的时候先对比一下，自己拥有的 version 和数据库现在的 version 是否一致，如果不一致就不修改，这样就实现了乐观锁。
7、 MVCC多版本并发控制
主要是为了提高数据库的并发性能。
行记录添加了两个字段
- trx_id： 当前事务的 ID
- roll_ptr： 回滚指针，指向下一个最新的 undo 日志版本
版本
- 当前最新数据上存储有 roll_ptr 字段，指向下一个最新的 undo 日志版本，undo 日志上各条数据的回滚指针也会依次指向下一个版本的数据，以此形成一个版本链条
ReadView
- m_ids： 当前系统中活跃的事务（说明事务未提交）
- min_trx_id： 当前活跃事务中最小的那个
- max_trx_id： 下一个即将生成的事务 ID（并非当前活跃事务中最大的）
- creat_trx_id：生成 RV 的事务ID（这样就指定当前的 RV 属于哪个事务）
判断当前数据是否可见的 4 大准则
- creat_trx_id 值 = trx_id 属性值 ，数据可见
- min_trx_id 值 > trx_id 属性值 ，数据可见
- max_trx_id < trx_id 属性值，数据不可见
- 如果 min_trx_id <= trx_id <= max_trx_id。如果 trx_id 在存活事务范围内，数据不可见；如果不属于存活的事务，数据可见
读提交和可重复读，都利用了MVCC，只不过实现不一样。
读提交是每一次select 都会创建一个readview，而可重复读，每个事务只创建一个readview。
8、优化
1、最左前缀法则
- 如果建立的是复合索引，索引的顺序要按照建立时的顺序，及从左到右
2、不要对索引做以下处理
- 计算，如： +、-、*、/、!=、<>、is null、is not null、or
- 函数，如：sum（）、round（）等
- 手动/自动类型转换，如：id="1", 本来就是数字给写成字符串了
3、索引不要放在范围查询的右边
- 比如复合索引：a->b->c ， 当where a="" and b>10 and c="", 这时候只能用到a 和 b ，c用不到索引，因为在范围之后索引都失效
4、减少select * 使用
- 使用覆盖索引：select 查询字段和 where 中使用的字段一致。
5、like 模糊查询
- like '%张三'、like '%张三%' 索引都会失效
6、使用 EXPLAIN 命令优化你的 SELECT 查询，对于复杂、效率低的 sql 语句，我们通常是使用 explain sql 来分
析这条 sql 语句，这样方便我们分析，进行优化。
7、当你的 SELECT 查询语句只需要使用一条记录时，要使用 LIMIT 1
8、使用 BETWEEN AND 替代 IN
9、为搜索字段创建索引
10、选择正确的存储引擎，InnoDB 、MyISAM 、MEMORY 等
12、对于枚举类型的字段(即有固定罗列值的字段)，建议使用ENUM而不是VARCHAR，如性别、星期、类型、类别等
13、拆分大的 DELETE 或 INSERT 语句
14、选择合适的字段类型，选择标准是 尽可能小、尽可能定长、尽可能使用整数。
15、字段设计尽可能使用 NOT NULL
16、进行水平切割或者垂直分割
sql优化的步骤：
- show status 命令了解各种 sql 的执行频率
- 通过慢查询日志定位那些执行效率较低的 sql 语句
- explain 分析低效 sql 的执行计划（这点非常重要，日常开发中用它分析Sql，会大大降低Sql导致的线上事故）
9、问题
1） char 和 varchar 的区别是什么？
char(n) ：固定长度类型
- 比如订阅 char(10)，当你输入"abc"三个字符的时候，它们占的空间还是 10 个字节，其他 7 个是空字节。
- 优点：效率高；
- 缺点：占用空间；
- 适用场景：存储密码的 md5 值，固定长度的，使用 char 非常合适。取数据的时候，char类型的要用trim()去掉多余的空格。
- char的字符长度范围：0-255。
varchar(n) ：可变长度
- 存储的值是每个值占用的字节再加上一个用来记录其长度的字节的长度。varchar的数据类型长度支持到了65535，因为起始位和结束位占去了3个字节，所以其整体最大长度为65532字节。
所以，从空间上考虑 varcahr 比较合适；从效率上考虑 char 比较合适，二者使用需要权衡。
2）一张自增表里面总共有 7 条数据，删除了最后 2 条数据，重启 mysql 数据库，又插入了一条数据，此时 id 是几？
表的引擎如果是 MyISAM ，那 id 就是 8。
表的引擎如果是 InnoDB，那 id 就是 6。
InnoDB 表只会把自增主键的最大 id 记录在内存中，所以重启之后会导致最大 id 丢失。
3）对左连接、右连接、内连接、 全连接的理解？
在对两张表进行连接查询的时候，会有这几个连接查询，它们返回结果如下。
左连接（left join）：返回左表全部，右表没有的返回空。
右连接（right join）：返回右表全部，左表没有的返回空。
内连接（inner join）：返回两张表中完全匹配的。
全连接（full join）：返回左表和右边所有的行，没有的返回空
10、主从复制
1）一主多从复制原理（读写分离）
- 首先，MySQL主库在事务提交时会把数据变更记录在二进制日志文件BinLog中，主库上的sync_binlog参数控制Binlog日志刷新到磁盘。
- 主库推送BinLog中的事件到从库中的终极日志RelayLog，之后从库根据中继日志RelayLog重做数据变更操作，通过逻辑复制以此来达到主库和从库的一致。
MySQL通过 3个线程来完成主从库间的数据复制：其中Binlog Dump线程跑在主库上， I/O线程和SQL线程跑在从库上。当在从库上启动复制（START SLAVE）时，首先创建 I/O线程连接主库，主库随后创建Binlog Dump线程读取数据库事件并发送给 I/O线程，I/O线程获取到事件数据后更新到从库的中继日志Relay Log中去，之后从库上的SQL线程读取中继日志Relay Log中更新的数据库事件并应用，如下图所示：
2）二进制文件
- BinLog
- 二进制日志文件（Binlog）会把 MySQL 中的所有数据修改操作以二进制的形式记录到日志文件中，包括Create、Drop、Insert、Update、Delete操作等，但二进制日志文件（Binlog）不会记录Select操作，因为Select操作并不修改数据。
- RelayLog
- 中继日志文件Relay Log的文件格式、内容和二进制日志文件Binlog一样，唯一的区别在于从库上的SQL线程在执行完当前中继日志文件Relay Log中的事件之后，SQL线程会自动删除当前中继日志文件Relay Log，避免从库上的中继日志文件Relay Log占用过多的磁盘空间。
3）怎么实现？
- 采用AOP的方式，通过方法名判断，方法名中有get、select、query开头的则连接slave，其他的则连接master数据库。
- 使用 sharding-jdbc 实现
参考链接：
浙公网安备 33010602011771号