会写SQL==精通MYSQL？NONONO，进来学习一下吧! 0.0

欢迎访问我的blog http://www.codinglemon.cn/

立个flag，8月20日前整理出所有面试常见问题，包括有：
Java基础、JVM、多线程、Spring、Redis、MySQL、Zookeeper、Dubbo、RokectMQ、分布式锁、算法。

9. MYSQL篇

9.1 系统数据量大了之后如何处理？MYSQL如何进行分库分表？

9.1.1 水平分库

概念： 以字段为依据，按照一定策略（hash、range等），将一个库中的数据拆分到多个库中。

结果：

1. 每个库的结构都一样；
2. 每个库的数据都不一样，没有交集；
3. 所有库的并集是全量数据；

场景： 系统绝对并发量上来了，分表难以根本上解决问题，并且还没有明显的业务归属来垂直分库。

分析： 库多了，io和cpu的压力自然可以成倍缓解。

9.1.2 水平分表

概念： 以字段为依据，按照一定策略（hash、range等），将一个表中的数据拆分到多个表中。

结果：

1. 每个表的结构都一样；
2. 每个表的数据都不一样，没有交集；
3. 所有表的并集是全量数据；

场景： 系统绝对并发量并没有上来，只是单表的数据量太多，影响了SQL效率，加重了CPU负担，以至于成为瓶颈。

分析： 表的数据量少了，单次SQL执行效率高，自然减轻了CPU的负担。

9.1.3 垂直分库

概念： 以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。

结果：

1. 每个库的结构都不一样；
2. 每个库的数据也不一样，没有交集；
3. 所有库的并集是全量数据；

场景： 系统绝对并发量上来了，并且可以抽象出单独的业务模块。

分析： 到这一步，基本上就可以服务化了。例如，随着业务的发展一些公用的配置表、字典表等越来越多，这时可以将这些表拆到单独的库中，甚至可以服务化。再有，随着业务的发展孵化出了一套业务模式，这时可以将相关的表拆到单独的库中，甚至可以服务化。

9.1.4 垂直分表

概念： 以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和扩展表）中。

结果：

1. 每个表的结构都不一样；
2. 每个表的数据也不一样，一般来说，每个表的字段至少有一列交集，一般是主键，用于关联数据；
3. 所有表的并集是全量数据；

场景： 系统绝对并发量并没有上来，表的记录并不多，但是字段多，并且热点数据和非热点数据在一起，单行数据所需的存储空间较大。以至于数据库缓存的数据行减少，查询时会去读磁盘数据产生大量的随机读IO，产生IO瓶颈。

分析： 可以用列表页和详情页来帮助理解。垂直分表的拆分原则是将热点数据（可能会冗余经常一起查询的数据）放在一起作为主表，非热点数据放在一起作为扩展表。这样更多的热点数据就能被缓存下来，进而减少了随机读IO。拆了之后，要想获得全部数据就需要关联两个表来取数据。但记住，千万别用join，因为join不仅会增加CPU负担并且会讲两个表耦合在一起（必须在一个数据库实例上）。关联数据，应该在业务Service层做文章，分别获取主表和扩展表数据然后用关联字段关联得到全部数据。（其实就是将热点数据与非热点数据分开）

9.2 你们的系统中是如何创建唯一主键的？

9.2.1 利用全球唯一id（UUID）

使用guid或者uuid作为主键id。

优点

1. 代码实现简单。
2. 本机生成，没有性能问题
3. 因为是全球唯一的ID，所以迁移数据容易

缺点

1. 每次生成的ID是无序的，无法保证趋势递增
2. UUID的字符串存储，查询效率慢
3. 存储空间大
4. ID无业务含义，不可读

9.2.2 数据库主键自增长

这个方案就是利用了MySQL的主键自增auto_increment，默认每次ID加1。对于我们分表的业务场景中通过为不同的表设置不同的步长以及初始值实现。

优点

1. 数字化，id递增
2. 查询效率高
3. 具有一定的业务可读

缺点

1. 存在单点问题，如果mysql挂了，就没法生成ID了。
2. 数据库压力大，高并发抗不住。
3. 自增主键存在长度限制

9.2.3 雪花算法

雪花算法生成64位的二进制正整数，然后转换成10进制的数。64位二进制数由如下部分组成：雪花算法生成64位的二进制正整数，然后转换成10进制的数。64位二进制数由如下部分组成：

1. 1位标识符：始终是0
2. 41位时间戳：41位时间截不是存储当前时间的时间截，而是存储时间截的差值（当前时间截 - 开始时间截 )得到的值，这里的的开始时间截，一般是我们的id生成器开始使用的时间，由我们程序来指定的
3. 10位机器标识码：可以部署在1024个节点，如果机器分机房（IDC）部署，这10位可以由 5位机房ID + 5位机器ID 组成
4. 12位序列：毫秒内的计数，12位的计数顺序号支持每个节点每毫秒(同一机器，同一时间截)产生4096个ID序号。

优点

1. 此方案每秒能够产生409.6万个ID，性能快。
2. 时间戳在高位，自增序列在低位，整个ID是趋势递增的，按照时间有序递增。
3. 灵活度高，可以根据业务需求，调整bit位的划分，满足不同的需求。

缺点

1. 依赖机器的时钟，如果服务器时钟回拨，会导致重复ID生成。

9.2.4 使用redis的自增

利用redis的incr原子性操作自增，一般算法为：

年份 + 当天距当年第多少天 + 天数 + 小时 + redis自增

优点

1. 有序递增，可读性强

缺点

1. 占用带宽，每次要向redis进行请求

9.3 ACID指什么？脏读、幻读、不可重复读分别代表什么含义？

9.3.1 事务的基本要素（ACID）

1. 原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。

2. 一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到。

3. 隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。

4. 持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

9.3.2 事务的并发问题

1. 脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据**（读取了回滚之前的数据）**

2. 不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。（读取了更新前和更新后的不一致的数据）

3. 幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。（一个事务(同一个read view)在前后两次查询同一范围的时候，后一次查询看到了前一次查询没有看到的行。）

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

9.4 MYSQL的事务隔离级别有哪几种？

9.4.1 读未提交（READ UNCOMMITTED）

又称为脏读，一个事务可以读取到另一个事务未提交的数据。这种隔离级别岁最不安全的一种，因为未提交的事务是存在回滚的情况。

9.4.2 读已提交（READ COMMITTED）

又称为不可重复读，一个事务因为读取到另一个事务已提交的修改数据，导致在当前事务的不同时间读取同一条数据获取的结果不一致。

举个例子，在下面的例子中就会发现SessionA在一个事务期间两次查询的数据不一样。原因就是在于当前隔离级别为 RC，SessionA的事务可以读取到SessionB提交的最新数据。

发生时间	SessionA	SessionB
1	begin;
2	select * from user where id=1;(张三)
3		update user set name=‘李四’ where id=1;(默认隐式提交事务)
4	select * from user where id=1;(李四)
5		update user set name=‘王二’ where id=1;(默认隐式提交事务)
6	select * from user where id=1;(王二)

9.4.3 可重复读（REPEATABLE READ，MySQL默认）

又称为幻读，一个事物读可以读取到其他事务提交的数据，但是在RR隔离级别下，当前读取此条数据只可读取一次，在当前事务中，不论读取多少次，数据任然是第一次读取的值，不会因为在第一次读取之后，其他事务再修改提交此数据而产生改变。因此也成为幻读，因为读出来的数据并不一定就是最新的数据。

举个例子：在SessionA中第一次读取数据时，后续其他事务修改提交数据，不会再影响到SessionA读取的数据值。此为可重复读。

9.4.4 串行化（SERIALIZABLE）

以上3种隔离级别都允许对同一条记录进行读-读、读-写、写-读的并发操作，如果我们不允许读-写、写-读的并发操作，可以使用SERIALIZABLE隔离级别。

9.5 回滚日志（undo log）

保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读。逻辑格式的日志，在执行undo的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，这一点是不同于redo log的。事务开始之前，将当前是的版本生成undo log，undo 也会产生 redo 来保证undo log的可靠性。

当事务提交之后，undo log并不能立马被删除，而是放入待清理的链表，由purge线程判断是否由其他事务在使用undo段中表的上一个事务之前的版本信息，决定是否可以清理undo log的日志空间。

注意：
MySQL5.6之前，undo表空间位于共享表空间的回滚段中，共享表空间的默认的名称是ibdata，位于数据文件目录中。当数据库中发生一些大的事务性操作的时候，要生成大量的undo信息，全部保存在共享表空间中的。因此共享表空间可能会变的很大，默认情况下，也就是undo 日志使用共享表空间的时候，被“撑大”的共享表空间是不会也不能自动收缩的。

MySQL5.6之后，undo表空间可以配置成独立的文件，但是提前需要在配置文件中配置，完成数据库初始化后生效且不可改变undo log文件的个数。但是如果初始化数据库之前没有进行相关配置，那么就无法配置成独立的表空间了。

9.6 MYSQL的底层数据结构是怎样的？

9.6.1 B+树

B+树，作为B树的升级版，MySQL在B树的基础上继续改造，使用B+树构建索引。B+树和B树最主要的区别在于非叶子节点是否存储数据的问题。

• B树：非叶子节点和叶子节点都会存储数据。
• B+树：只有叶子节点才会存储数据，非叶子节点只存储键值。叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。

范围查询：
假如我们想要查找9和26之间的数据。查找路径是磁盘块1->磁盘块2->磁盘块6->磁盘块7。

1. 首先查找值等于9的数据，将值等于9的数据缓存到结果集。这一步和前面等值查询流程一样，发生了三次磁盘IO。
2. 查找到15之后，底层的叶子节点是一个有序列表，我们从磁盘块6，键值9开始向后遍历筛选所有符合筛选条件的数据。
3. 第四次磁盘IO：根据磁盘6后继指针到磁盘中寻址定位到磁盘块7，将磁盘7加载到内存中，在内存中从头遍历比较，9<25<26，9<26<=26，将data缓存到结果集。
4. 主键具备唯一性（后面不会有<=26的数据），不需再向后查找，查询终止。将结果集返回给用户。

可以看到B+树可以保证等值和范围查询的快速查找，MySQL的索引就采用了B+树的数据结构。（在InnoDB中Data存储的为行数据，而MyIsam中存储的是磁盘地址。）

9.6.2 Hash表

哈希索引(hash index)基于哈希表实现，只有精确匹配索引所有列的查询才有效。做等值查询时有效。

9.7 MYSQL有哪些优化手段？

9.7.1 预发跑sql explain

在日常工作中，我们会有时会开慢查询去记录一些执行时间比较久的SQL语句，找出这些SQL语句并不意味着完事了，些时我们常常用到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。

• 实际SQL，查找用户名为Jefabc的员工
  select * from emp where name = ‘Jefabc’;
• 查看SQL是否使用索引，前面加上explain即可
  explain select * from emp where name = ‘Jefabc’;

expain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra。

概要描述：

• id: 选择标识符
• select_type: 表示查询的类型。
• table: 输出结果集的表
• partitions: 匹配的分区
• type: 表示表的连接类型
• possible_keys: 表示查询时，可能使用的索引
• key: 表示实际使用的索引
• key_len: 索引字段的长度
• ref: 列与索引的比较
• rows: 扫描出的行数(估算的行数)
• filtered: 按表条件过滤的行百分比
• Extra: 执行情况的描述和说明

总结：

• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
• EXPLAIN不考虑各种Cache
• EXPLAIN不能显示MySQL在执行查询时所作的优化工作
• 部分统计信息是估算的，并非精确值
• EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。

9.7.2 排除 缓存 sql nocache

因为在MySQL8.0之前我们的数据库是存在缓存这样的情况的，我之前就被坑过，因为存在缓存，我发现我sql怎么执行都是很快，当然第一次其实不快但是我没注意到，以至于上线后因为缓存经常失效，导致rt（Response time）时高时低。后面就发现了是缓存的问题，我们在执行SQL的时候，记得加上SQL NoCache去跑SQL，这样跑出来的时间就是真实的查询时间了。我说一下为什么缓存会失效，而且是经常失效。
如果我们当前的MySQL版本支持缓存而且我们又开启了缓存，那每次请求的查询语句和结果都会以key-value的形式缓存在内存中的，大家也看到我们的结构图了，一个请求会先去看缓存是否存在，不存在才会走解析器。缓存失效比较频繁的原因就是，只要我们一对表进行更新，那这个表所有的缓存都会被清空，其实我们很少存在不更新的表，特别是我之前的电商场景，可能静态表可以用到缓存，但是我们都走大数据离线分析，缓存也就没用了。大家如果是8.0以上的版本就不用担心这个问题，如果是8.0之下的版本，记得排除缓存的干扰。

9.7.3 看一下行数对不对 不对可以用analyze table t 矫正

我的总行数大概有10W行，但是我去用explain去分析sql的时候，就会发现只得到了9.4W，为啥行数只是个近似值呢？MySQL中数据的单位都是页，MySQL又采用了采样统计的方法，采样统计的时候，InnoDB默认会选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。
我们数据是一直在变的，所以索引的统计信息也是会变的，会根据一个阈值，重新做统计。
至于MySQL索引可能走错也很好理解，如果走A索引要扫描100行，B所有只要20行，但是他可能选择走A索引，你可能会想MySQL是不是有病啊，其实不是的。
一般走错都是因为优化器在选择的时候发现，走A索引没有额外的代价，比如走B索引并不能直接拿到我们的值，还需要回到主键索引才可以拿到，多了一次回表的过程，这个也是会被优化器考虑进去的。
他发现走A索引不需要回表，没有额外的开销，所有他选错了。
如果是上面的统计信息错了，那简单，我们用analyze table tablename 就可以重新统计索引信息了，所以在实践中，如果你发现explain的结果预估的rows值跟实际情况差距比较大，可以采用这个方法来处理。

9.7.4 添加索引 索引不一定是最优的 force index 强制走索引 不建议用

还有一个方法就是force index强制走正确的索引，或者优化SQL，最后实在不行，可以新建索引，或者删掉错误的索引。

9.7.5 覆盖索引避免回表，不要用*

如果在我们建立的索引上就已经有我们需要的字段，就不需要回表了，在电商里面也是很常见的，我们需要去商品表通过各种信息查询到商品id，id一般都是主键，可能sql类似这样：
select itemId from itemCenter where size between 1 and 6
因为商品id itemId一般都是主键，在size索引上肯定会有我们这个值，这个时候就不需要回主键表去查询id信息了。由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

9.7.6 联合索引

还是商品表举例，我们需要根据他的名称，去查他的库存，假设这是一个很高频的查询请求，你会怎么建立索引呢？
大家可以思考上面的回表的消耗对SQL进行优化。
是的建立一个，名称和库存的联合索引，这样名称查出来就可以看到库存了，不需要查出id之后去回表再查询库存了，联合索引在我们开发过程中也是常见的，但是并不是可以一直建立的，大家要思考索引占据的空间。

9.7.7 最左前缀原则 按照索引定义的字段顺序写sql

大家在写sql的时候，最好能利用到现有的SQL最大化利用，像上面的场景，如果利用一个模糊查询 itemname like ’敖丙%‘，这样还是能利用到这个索引的，而且如果有这样的联合索引，大家也没必要去新建一个商品名称单独的索引了。很多时候我们索引可能没建对，那你调整一下顺序，可能就可以优化到整个SQL了。

mysql 建立多列索引（联合索引）有最左前缀的原则，即最左优先，如：

如果有一个 2 列的索引 (col1, col2)，则已经对 (col1)、(col1, col2) 上建立了索引；
如果有一个 3 列索引 (col1, col2, col3)，则已经对 (col1)、(col1, col2)、(col1, col2, col3) 上建立了索引；

如果建的索引是 (name, cid)。而查询的语句是 cid=1 AND name=’小红’。为什么还能利用到索引？

当按照索引中所有列进行精确匹配（“=” 或 “IN”）时，索引可以被用到，并且 type 为 const。理论上索引对顺序是敏感的，但是由于 MySQL 的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引，所以 MySQL 不存在 where 子句的顺序问题而造成索引失效

注意事项

1. 范围查询
mysql 会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配。范围列可以用到索引，但是范围列后面的列无法用到索引。即，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引

2. like 语句的索引问题
如果通配符 % 不出现在开头，则可以用到索引，但根据具体情况不同可能只会用其中一个前缀
在 like “value%” 可以使用索引，但是 like “%value%” 不会使用索引，走的是全表扫描

3. 不要在列上进行运算
如果查询条件中含有函数或表达式，将导致索引失效而进行全表扫描
例如 select * from user where YEAR(birthday) < 1990
可以改造成 select * from users where birthday <’1990-01-01′

4. 索引不会包含有 NULL 值的列
只要列中包含有 NULL 值都将不会被包含在索引中，复合索引中只要有一列含有 NULL 值，那么这一列对于此复合索引就是无效的。所以在数据库设计时不要让字段的默认值为 NULL

5. 尽量选择区分度高的列作为索引
区分度的公式是 count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是 1，而一些状态、性别字段可能在大数据面前区分度就是 0。一般需要 join 的字段都要求区分度 0.1 以上，即平均 1 条扫描 10 条记录

6. 覆盖索引的好处
如果一个索引包含所有需要的查询的字段的值，我们称之为覆盖索引。覆盖索引是非常有用的工具，能够极大的提高性能。因为，只需要读取索引，而无需读表，极大减少数据访问量

9.7.8 索引下推

你已经知道了前缀索引规则，那我就说一个官方帮我们优化的东西，索引下推。

select * from itemcenter where name like ‘敖%’ and size=22 and age = 20;

所以这个语句在搜索索引树的时候，只能用 “敖”，找到第一个满足条件的记录ID1，当然，这还不错，总比全表扫描要好。然后呢？当然是判断其他条件是否满足，比如size。在MySQL 5.6之前，只能从ID1开始一个个回表，到主键索引上找出数据行，再对比字段值。

而MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

9.7.9 给字符串加索引

9.7.9.1 前缀索引（比如邮箱）

我们存在邮箱作为用户名的情况，每个人的邮箱都是不一样的，那我们是不是可以在邮箱上建立索引，但是邮箱这么长，我们怎么去建立索引呢？MySQL是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。
我们是否可以建立一个区分度很高的前缀索引，达到优化和节约空间的目的呢？
使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。
上面说过覆盖索引了，覆盖索引是不需要回表的，但是前缀索引，即使你的联合索引已经包涵了相关信息，他还是会回表，因为他不确定你到底是不是一个完整的信息，就算你是www.demo@demo.com一个完整的邮箱去查询，他还是不知道你是否是完整的，所以他需要回表去判断一下。

9.7.9.2 倒序存储（身份证号）

我们所有人的身份证都是区域开头的，同区域的人很多，那怎么做良好的区分呢？REVERSE（）函数翻转一下，区分度可能就高了。

9.7.9.3 Hash

把字段hash为另外一个字段存起来，每次校验hash就好了，hash的索引也不大。

9.7.10 数据库的flush的时机是怎样的？

redo log大家都知道，也就是我们对数据库操作的日志，他是在内存中的，每次操作一旦写了redo log就会立马返回结果，但是这个redo log总会找个时间去更新到磁盘，这个操作就是flush。在更新之前，当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页“。

那什么时候会flush呢？

1. InnoDB的redo log写满了，这时候系统会停止所有更新操作，把checkpoint往前推进，redo log留出空间可以继续写。
2. 系统内存不足，当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。

你一定会说，这时候难道不能直接把内存淘汰掉，下次需要请求的时候，从磁盘读入数据页，然后拿redo log出来应用不就行了？

这里其实是从性能考虑的，如果刷脏页一定会写盘，就保证了每个数据页有两种状态：
一种是内存里存在，内存里就肯定是正确的结果，直接返回；
另一种是内存里没有数据，就可以肯定数据文件上是正确的结果，读入内存后返回。这样的效率最高。

1. MySQL认为系统“空闲”的时候，只要有机会就刷一点“脏页”到磁盘上。
2. MySQL正常关闭，这时候，MySQL会把内存的脏页都flush到磁盘上，这样下次MySQL启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

那我们怎么做才能把握flush的时机呢？

Innodb刷脏页控制策略，我们每个电脑主机的io能力是不一样的，你要正确地告诉InnoDB所在主机的IO能力，这样InnoDB才能知道需要全力刷脏页的时候，可以刷多快。
这就要用到innodb_io_capacity这个参数了，它会告诉InnoDB你的磁盘能力，这个值建议设置成磁盘的IOPS，磁盘的IOPS可以通过fio这个工具来测试。正确地设置innodb_io_capacity参数，可以有效的解决这个问题。
这中间有个有意思的点，刷脏页的时候，旁边如果也是脏页，会一起刷掉的，并且如果周围还有脏页，这个连带责任制会一直蔓延，这种情况其实在机械硬盘时代比较好，一次IO就解决了所有问题，但是现在都是固态硬盘了，innodb_flush_neighbors=0这个参数可以不产生连带制，在MySQL 8.0中，innodb_flush_neighbors参数的默认值已经是0了。

9.7.11 索引字段不要做函数操作

索引字段不要做函数操作，会破坏索引值的有序性，优化器会放弃走树结构。如果触发隐式转换，那也会走cast函数，会放弃走索引。字符集不同可能走不上索引，convert 也是函数所以走不上。

9.8 聚集索引与非聚集索引

9.8.1 聚集索引（类似于拼音检索）

索引中键值的逻辑顺序决定了表中相应行的物理顺序。

聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿，后者按姓氏排列数据。 聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。例如，如果应用程序执行 的一个查询经常检索某一日期范围内的记录，则使用聚集索引可以迅速找到包含开始日期的行，然后检索表中所有相邻的行，直到到达结束日期。这样有助于提高此 类查询的性能。同样，如果对从表中检索的数据进行排序时经常要用到某一列，则可以将该表在该列上聚集（物理排序），避免每次查询该列时都进行排序，从而节 省成本。

9.8.2 非聚集索引（类似于笔画索引）

索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。

其实按照定义，除了聚集索引以外的索引都是非聚集索引，只是人们想细分一下非聚集索引，分成普通索引，唯一索引，全文索引。如果非要把非聚集索引类比成现实生活中的东西，那么非聚集索引就像新华字典的偏旁字典，他结构顺序与实际存放顺序不一定一致。

非聚集索引的存储结构与前面是一样的，不同的是在叶子结点的数据部分存的不再是具体的数据，而数据的聚集索引的key。所以通过非聚集索引查找的过程是先找到该索引key对应的聚集索引的key，然后再拿聚集索引的key到主键索引树上查找对应的数据，这个过程称为回表。

9.9 change buffer

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作，通过这种方式就能保证这个数据逻辑的正确性。需要说明的是，虽然名字叫作change buffer，实际上它是可以持久化的数据。也就是说，change buffer在内存中有拷贝，也会被写入到磁盘上。将change buffer中的操作应用到原数据页，得到最新结果的过程称为merge。除了访问这个数据页会触发merge外，系统有后台线程会定期merge。在数据库正常关闭（shutdown）的过程中，也会执行merge操作。

显然，如果能够将更新操作先记录在change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用buffer pool的，所以这种方式还能够避免占用内存，提高内存利用率。

那么，什么条件下可以使用change buffer呢？

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。
要判断表中是否存在这个数据，而这必须要将数据页读入内存才能判断，如果都已经读入到内存了，那直接更新内存会更快，就没必要使用change buffer了。
因此，唯一索引的更新就不能使用change buffer，实际上也只有普通索引可以使用。

change buffer用的是buffer pool里的内存，因此不能无限增大，change buffer的大小，可以通过参数innodb_change_buffer_max_size来动态设置，这个参数设置为50的时候，表示change buffer的大小最多只能占用buffer pool的50%。

将数据从磁盘读入内存涉及随机IO的访问，是数据库里面成本最高的操作之一，change buffer因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

change buffer的使用场景

因为merge的时候是真正进行数据更新的时刻，而change buffer的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做merge之前，change buffer记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。

因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer的使用效果最好，这种业务模型常见的就是账单类、日志类的系统。

反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在change buffer，但之后由于马上要访问这个数据页，会立即触发merge过程。这样随机访问IO的次数不会减少，反而增加了change buffer的维护代价，所以，对于这种业务模式来说，change buffer反而起到了副作用。

9.10 MYSQL中MVCC是什么？主要起什么作用？

MVCC，全称Multi-Version Concurrency Control，即多版本并发控制。MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。

MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读

在学习MVCC多版本并发控制之前，我们必须先了解一下，什么是MySQL InnoDB下的当前读和快照读?

• 当前读：像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁

• 快照读：像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC,可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。

说白了MVCC就是为了实现读-写冲突不加锁，而这个读指的就是快照读, 而非当前读，当前读实际上是一种加锁的操作，是悲观锁的实现

当前读，快照读和MVCC的关系

• 准确的说，MVCC多版本并发控制指的是 “维持一个数据的多个版本，使得读写操作没有冲突” 这么一个概念。仅仅是一个理想概念
• 而在MySQL中，实现这么一个MVCC理想概念，我们就需要MySQL提供具体的功能去实现它，而快照读就是MySQL为我们实现MVCC理想模型的其中一个具体非阻塞读功能。而相对而言，当前读就是悲观锁的具体功能实现
• 要说的再细致一些，快照读本身也是一个抽象概念，再深入研究。MVCC模型在MySQL中的具体实现则是由 3个隐式字段，undo日志 ，Read View 等去完成的，具体可以看下面的MVCC实现原理。

MVCC能解决什么问题，好处是？

数据库并发场景有三种，分别为：

• 读-读：不存在任何问题，也不需要并发控制
• 读-写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读
• 写-写：有线程安全问题，可能会存在更新丢失问题，比如第一类更新丢失，第二类更新丢失

MVCC带来的好处是？

多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。 所以MVCC可以为数据库解决以下问题:

• 在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能

• 同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题

9.11 MYSQL中有哪些锁？分别有哪些作用？

1. 全局锁： 全库逻辑备份时使用。

2. 表锁：

   1. lock table read/write：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。这些存储引擎通过总是一次性同时获取所有需要的锁以及总是按相同的顺序获取表锁来避免死锁。表级锁更适合于以查询为主，并发用户少，只有少量按索引条件更新数据的应用，如Web 应用
   2. MDL（metadata lock)： MySQL5.5引入 自动添加 读锁不互斥 写锁互斥；多个事务之前操作，如果查询的时候修改字段容易让线程池饱满；MySQL的information_schema 库的 innodb_trx 表 找到对应长事务 kill掉；alter table里面设定等待时间；MyISAM是不支持表锁的

3. 行锁：
   需要的时候才加上，并不是马上释放，等事务结束才释放，两阶段锁协议。

   1. 随时都可以执行锁定，InnoDB会根据隔离级别在需要的时候自动加锁；
   2. 锁只有在执行commit或者rollback的时候才会释放，并且所有的锁都是在同一时刻被释放。

行锁开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。最大程度的支持并发，同时也带来了最大的锁开销。

在 InnoDB 中，除单个 SQL 组成的事务外，锁是逐步获得的，这就决定了在 InnoDB 中发生死锁是可能的。行级锁只在存储引擎层实现，而Mysql服务器层没有实现。 行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

死锁：
超时时间：innodb_lock_wait_timeout，默认是50s太久 但是如果设置太短会误判 一般采用死锁监测
死锁机制：事务回滚，innodb_deadlock_detect = on

热点行:死锁消耗CPU：临时关闭，关掉死锁会出现大量重试，控制并发度，更多的机器 开启比较少的线程 消耗就少了；采用分治思想。

4. 间隙锁: 当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。
   很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

举例说明：比如select * from stu where age>18 and age<60,此时如果往表中插入一条age=19的数据会发生等待。

5. 读写锁：

读：

1. lock in share mode
2. for update
3. 行锁

写：
1. 根据锁机制

innodb如何加锁：

1. Record lock: 对索引项加锁。
2. Gap lock:对索引项之间的‘间隙’,第一条记录前的间隙，或最后一条记录后的间隙 加锁
3. Next-Key:前两种的组合，对记录及前面的间隙加锁。

9.12 MYSQL中有哪些日志？

MySQL 中有六种日志文件，分别是：
重做日志（redo log）、回滚日志（undo log）、二进制日志（binlog）、错误日志（errorlog）、慢查询日志（slow query log）、一般查询日志（general log），中继日志（relay log）。

9.12.1 undo log

undo log 有两个作用：提供回滚和多版本并发控制下的读(MVCC)，也即非锁定读。在数据修改的时候，不仅记录了redo，还记录了相对应的 undo，如果因为某些原因导致事务失败或回滚了，可以借助该 undo 进行回滚。
undo log 和 redo log 记录物理日志不一样，它是逻辑日志。可以认为当 delete 一条记录时，undo log 中会记录一条对应的 insert 记录，反之亦然，当 update 一条记录时，它记录一条对应相反的 update 记录。
undo log 是采用段(segment)的方式来记录的，每个 undo 操作在记录的时候占用一个 undo log segment。
另外，undo log 也会产生 redo log，因为 undo log 也要实现持久性保护。
当事务提交的时候，InnoDB 不会立即删除 undo log，因为后续还可能会用到 undo log，如隔离级别为 repeatable read 时，事务读取的都是开启事务时的最新提交行版本，只要该事务不结束，该行版本就不能删除，即 undo log 不能删除。
当事务提交之后，undo log 并不能立马被删除，而是放入待清理的链表，由 purge 线程判断是否有其他事务在使用 undo 段中表的上一个事务之前的版本信息，决定是否可以清理 undo log 的日志空间。

9.12.2 redo log

和大多数关系型数据库一样，InnoDB 记录了对数据文件的物理更改，并保证总是日志先行，也就是所谓的 WAL，即在持久化数据文件前，保证之前的 redo 日志已经写到磁盘。由于 redo log 是顺序整块写入，所以性能要更好。

重做日志两部分组成：一是内存中的重做日志缓冲(redo log buffer)，是易失的；二是重做日志文件(redo log file)，是持久的。redo log 记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来。

在一条语句进行执行的时候，InnoDB 引擎会把新记录写到 redo log 日志中，然后更新内存，更新完成后就算是语句执行完了，然后在空闲的时候或者是按照设定的更新策略将 redo log 中的内容更新到磁盘中。

更详细的步骤，需要了解两个关键词：checkpoint 和 LSN(Log Sequence Number)，前者检查点简单来说就是把脏页刷到磁盘的时间点，这个时间点之前的数据都已经保存到了持久存储。而 LSN 是 InnoDB 使用的一个版本标记的计数，它是一个单调递增的值。数据页和 redo log 都有各自的 LSN。每次把 redo log 中的内容写入到实际的数据页之后，就会把 LSN 也同步过去。如果发生了宕机，我们可以根据数据页中的 LSN 值和 redo log 中 LSN 的值判断需要恢复的 redo log 的位置和大小。redo log 同样也有自己的缓存，所以也涉及到刷盘策略，是通过innodb_flush_log_at_trx_commit这个参数控制的。

当对应事务的脏页写入到磁盘之后，redo log 的使命也就完成了，重做日志占用的空间就可以重用（被覆盖）。

sync_binlog 可以优化日志写入时机

1. sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。
   2。 sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

9.12.3 binlog

最早接触 binlog 是做数据库主从同步的时候，知道是通过同步 binlog 实现的。binlog 是 没有 MySQL server层维护的一种二进制日志，与 innodb 引擎中的 redo/undo log 是完全不同的日志。其主要是用来记录对 MySQL 数据更新或潜在发生更新的 SQL 语句，并以 “事务”的形式保存在磁盘中。

binlog 主要有以下作用：

1. 复制：MySQL 主从复制在 Master 端开启 binlog，Master 把它的二进制日志传递给 slaves 并回放来达到 master-slave 数据一致的目的
2. 数据恢复：通过 mysqlbinlog 工具恢复数据
3. 增量备份

几个知识点：

1. binlog 不会记录不修改数据的语句，比如Select或者Show
2. binlog 会重写日志中的密码，保证不以纯文本的形式出现
3. MySQL 8 之后的版本可以选择对 binlog 进行加密
4. 具体的写入时间：在事务提交的时候，数据库会把 binlog cache 写入 binlog 文件中，但并没有执行fsync()操作，即只将文件内容写入到 OS 缓存中。随后根据配置判断是否执行 fsync。
5. 删除时间：保持时间由参数expire_logs_days配置，也就是说对于非活动的日志文件，在生成时间超过expire_logs_days配置的天数之后，会被自动删除。

binlog的格式：binlog 日志有 Row、Statement、Mixed 三种格式。可以通过 my.cnf 配置文件及 set global binlog_format='ROW/STATEMENT/MIXED’进行修改，命令行 show variables like ‘binlog_format’ 命令查看 binglog 格式。

1. Row格式：Row 格式仅保存记录被修改细节，不记录 sql 语句上下文相关信息。新版本的 MySQL 默认是 Row 格式。

优点：能非常清晰的记录下每行数据的修改细节，不需要记录上下文相关信息，因此不会发生某些特定情况下的存储过程、函数或者触发器的调用触发无法被正确复制的问题，任何情况都可以被复制，且能加快从库重放日志的效率，保证从库数据的一致性

缺点：由于所有的执行的语句在日志中都将以每行记录的修改细节来记录，因此，可能会产生大量的日志内容，干扰内容也较多。比如一条 update 语句，如修改多条记录，则 binlog 中每一条修改都会有记录，这样造成 binlog 日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中，实际等于重建了表。

2. Statement格式：每一条会修改数据的 sql 都会记录在 binlog 中。

优点：只需要记录执行语句的细节和上下文环境，避免了记录每一行的变化，在一些修改记录较多的情况下相比 Row 格式能大大减少 binlog 日志量，节约 IO，提高性能。另外还可以用于实时的还原。主从版本可以不一样，从服务器版本可以比主服务器版本高。

缺点：为了保证 sql 语句能在 slave 上正确执行，必须记录上下文信息，以保证所有语句能在 slave 得到和在 master 端执行时候相同的结果。另外，主从复制时，存在部分函数（如 sleep）及存储过程在 slave 上会出现与 master 结果不一致的情况，而相比 Row 记录每一行的变化细节，绝不会发生这种不一致的情况。

3. Mixed格式：以上两种格式混合。

经过前面的对比，可以发现 Row 和 Statement 各有优势，如果可以根据 sql 语句取舍可能会有更好地性能和效果。Mixed 便是以上两种形式的结合。不过，新版本的 MySQL 对 Row 模式也做了优化，并不是所有的修改都会完全以 Row 形式来记录，像遇到表结构变更的时候就会以 Statement 模式来记录，如果 SQL 语句确实就是 update 或者 delete 等修改数据的语句，那么还是会记录所有行的变更；因此，现在一般使用 Row 即可。

9.13 MYSQL主从复制

复制是 MySQL 最重要的功能之一，MySQL 集群的高可用、负载均衡和读写分离都是基于复制来实现。复制步骤如下：

1. Master 将数据改变记录到二进制日志(binary log)中。
2. Slave 上面的 IO 进程连接上 Master，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容。
3. Master 接收到来自 Slave 的 IO 进程的请求后，负责复制的 IO 进程会根据请求信息读取日志指定位置之后的日志信息，返回给 Slave 的 IO 进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到 Master 端的 binlog 文件的名称以及 binlog 的位置。
4. Slave 的 IO 进程接收到信息后，将接收到的日志内容依次添加到 Slave 端的 relaylog 文件的最末端，并将读取到的 Master 端的 binlog 的文件名和位置记录到 masterinfo 文件中，以便在下一次读取的时候能够清楚的告诉 Master 从某个 binlog 的哪个位置开始往后的日志内容。
5. Slave 的 SQL 进程检测到 relaylog 中新增加了内容后，会马上解析 relaylog 的内容成为在 Master 端真实执行时候的那些可执行的内容，并在自身执行。

9.14 count(1) AND count(*) 对比

一般情况下，Select Count ()和Select Count(1)两着返回结果是一样的。
假如表沒有主键(Primary key), 那么count(1)比count()快，如果有主键的話，那主键作为count的条件时候count(主键)最快。如果你的表只有一个字段的话，那么count()就是最快的。count() 跟 count(1) 的结果一样，都包括对NULL的统计，而count(column) 是不包括NULL的统计。

9.15 主备延迟

产生主备延迟的根本原因是备库上消费 binlog 的速度赶不上主库产生 binlog 的速度。比如：

1. 大事务，例如一次性delete很多数据；
2. 大表的DDL；
3. 备库压力大。例如有些像运维、订单等统计分析在备机上跑；
4. 主备库的服务器的配置不同，主库的服务器配置好，备库的服务器配置差。

两种解决方式：

1. 强制走主
2. Sleep（在MySQL中执行select sleep(N)可以让此语句运行N秒钟）

9.16 join的使用

有的时候我们在操作数据库时会将两个或多个数据表关联起来通过一些条件筛选数据，在关联表时我们要遵循一些原则，这样会使我们编写的SQL 语句在效率上快很多。
小表驱动大表，即小的数据集驱动大的数据集。

9.17 如果表自增id用完怎么办

数据库表的自增 ID 达到上限之后，再申请时它的值就不会在改变了，继续插入数据时会导致报主键冲突错误。因此在设计数据表时，尽量根据业务需求来选择合适的字段类型。解决方法：

1. 使用bigint：8个字节
2. InnoDB 系统自增 row_id：如果你创建的 InnoDB 表没有指定主键，那么 InnoDB 会给你创建一个不可见的，长度为 6 个字节的 row_id。InnoDB 维护了一个全局的 dict_sys.row_id 值，所有无主键的 InnoDB 表，每插入一行数据，都将当前的 dict_sys.row_id 值作为要插入数据的 row_id，然后把 dict_sys.row_id 的值加 1。row_id 写入表中的值范围，是从 0 到 2^48-1。如果row_id用完了，则会从头开始循环。
3. thread_id：其实，线程 id 才是 MySQL 中最常见的一种自增 id。平时我们在查各种现场的时候，show processlist 里面的第一列，就是 thread_id。thread_id 的逻辑很好理解：系统保存了一个全局变量 thread_id_counter，每新建一个连接，就将 thread_id_counter 赋值给这个新连接的线程变量。thread_id_counter 定义的大小是 4 个字节，因此达到 2^32-1 后，它就会重置为 0，然后继续增加。但是，你不会在 show processlist 里看到两个相同的 thread_id。是因为 MySQL 设计了一个唯一数组的逻辑。

9.18 MYSQL IO性能瓶颈如何解决

1. 设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count参数，减少binlog的写盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险。
2. 将sync_binlog 设置为大于1的值（比较常见是100~1000）。这样做的风险是，主机掉电时会丢binlog日志。
3. 将innodb_flush_log_at_trx_commit设置为2。这样做的风险是，主机掉电的时候会丢数据。

9.19 MYSQL常见命令

1. show processlist：查看空闲忙碌链接
2. wait_timeout：set global wait_timeout=100 -----设置数据库线程sleep超时时间，定时断开链接；mysql_reset_connection 恢复链接状态。
3. innodb_flush_log_at_trx_commit：
   0：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作。
   1：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去，该模式为系统默认。
   2：每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作。
4. sync_binlog：这个参数是对于MySQL系统来说是至关重要的，他不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。对于“sync_binlog”参数的各种设置的说明如下：
   1. sync_binlog=0（MYSQL默认），当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。
   2. sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。