4.mysql重点知识

1、能说下myisam 和 innodb的区别吗？

myisam引擎是5.1版本之前的默认引擎，支持全文检索、压缩、空间函数等，但是不支持事务和行级锁，所以一般用于有大量查询少量插入的场景来使用，而且myisam不支持外键，并且索引和数据是分开存储的。
MyISAM将一张表的结构和内容分为三个文件：
1、表结构文件，后缀名为frm
2、索引文件，后缀名为MYI
3、数据文件，后缀名为MYD
innodb是基于聚簇索引建立的，和myisam相反它支持事务、外键，并且通过MVCC来支持高并发，索引和数据存储在一起。
相比MyISAM，InnoDB只有两个文件：
1、表结构文件，后缀名为frm
2、数据、索引文件，后缀名为ibd

具体的区别
https://blog.csdn.net/qq_35642036/article/details/82820178

2、说下mysql的索引有哪些吧，聚簇和非聚簇索引又是什么？

索引按照数据结构来说主要包含B+树和Hash索引。
假设我们有张表，结构如下：
create table user( id int(11) not null, age int(11) not null, primary key(id), key(age) );
B+树是左小右大的顺序存储结构，节点只包含id索引列，而叶子节点包含索引列和数据，这种数据和索引在一起存储的索引方式叫做聚簇索引，一张表只能有一个聚簇索引。假设没有定义主键，InnoDB会选择一个唯一的非空索引代替，如果没有的话则会隐式定义一个主键作为聚簇索引。

这是主键聚簇索引存储的结构，那么非聚簇索引的结构是什么样子呢？非聚簇索引(二级索引)保存的是主键id值，这一点和myisam保存的是数据地址是不同的。

最终，我们一张图看看InnoDB和Myisam聚簇和非聚簇索引的区别

为什么要用B+tree
https://blog.csdn.net/oschina_40730821/article/details/105784034

3、那你知道什么是覆盖索引和回表吗？

回表：
两个B+树索引分别如上图：
（1）id为PK，聚集索引，叶子节点存储行记录；
（2）name为KEY，普通索引，叶子节点存储PK值，即id；
既然从普通索引无法直接定位行记录，那普通索引的查询过程是怎么样的呢？
通常情况下，需要扫码两遍索引树。
例如：
select * from t where name=‘lisi’;
是如何执行的呢？

image
如粉红色路径，需要扫码两遍索引树：
（1）先通过普通索引定位到主键值id=5；
（2）在通过聚集索引定位到行记录；
这就是所谓的回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。
覆盖索引指的是在一次查询中，如果一个索引包含或者说覆盖所有需要查询的字段的值，我们就称之为覆盖索引，而不再需要回表查询。
而要确定一个查询是否是覆盖索引，我们只需要explain sql语句看Extra的结果是否是“Using index”即可。
以上面的user表来举例，我们再增加一个name字段，然后做一些查询试试。
explain select * from user where age=1; //查询的name无法从索引数据获取 explain select id,age from user where age=1; //可以直接从索引获取

4、锁的类型有哪些呢

mysql锁分为共享锁和排他锁，也叫做读锁和写锁。
读锁是共享的，可以通过lock in share mode实现，这时候只能读不能写。
写锁是排他的，它会阻塞其他的写锁和读锁。从颗粒度来区分，可以分为表锁和行锁两种。
表锁会锁定整张表并且阻塞其他用户对该表的所有读写操作，比如alter修改表结构的时候会锁表。
行锁又可以分为乐观锁和悲观锁，悲观锁可以通过for update实现，乐观锁则通过版本号实现。

5、你能说下事务的基本特性和隔离级别吗？

事务基本特性ACID分别是：
原子性指的是一个事务中的操作要么全部成功，要么全部失败。
一致性指的是数据库总是从一个一致性的状态转换到另外一个一致性的状态。比如A转账给B100块钱，假设中间sql执行过程中系统崩溃A也不会损失100块，因为事务没有提交，修改也就不会保存到数据库。
隔离性指的是一个事务的修改在最终提交前，对其他事务是不可见的。
持久性指的是一旦事务提交，所做的修改就会永久保存到数据库中。
而隔离性有4个隔离级别，分别是：

read uncommit 读未提交，可能会读到其他事务未提交的数据，也叫做脏读。
用户本来应该读取到id=1的用户age应该是10，结果读取到了其他事务还没有提交的事务，结果读取结果age=20，这就是脏读。

read commit 读已提交，两次读取结果不一致，叫做不可重复读。
不可重复读解决了脏读的问题，他只会读取已经提交的事务。
用户开启事务读取id=1用户，查询到age=10，再次读取发现结果=20，在同一个事务里同一个查询读取到不同的结果叫做不可重复读。

repeatable read 可重复复读，这是mysql的默认级别，一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的数据，叫做幻读。

serializable 串行，一般是不会使用的，他会给每一行读取的数据加锁，会导致大量超时和锁竞争的问题。

6、那ACID靠什么保证的呢？

A原子性由undo log日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql
C一致性一般由代码层面来保证
I隔离性由MVCC来保证
D持久性由内存+redo log来保证，mysql修改数据同时在内存和redo log记录这次操作，事务提交的时候通过redo log刷盘，宕机的时候可以从redo log恢复

7、那你说说什么是幻读，什么是MVCC？

要说幻读，首先要了解MVCC，MVCC叫做多版本并发控制，实际上就是保存了数据在某个时间节点的快照。
我们每行数实际上隐藏了两列，创建时间版本号，过期(删除)时间版本号，每开始一个新的事务，版本号都会自动递增。
还是拿上面的user表举例子，假设我们插入两条数据，他们实际上应该长这样。
id name create_version delete_version
这时候假设小明去执行查询，此时current_version=3
select * from user where id<=3; 复制代码
同时，小红在这时候开启事务去修改id=1的记录，current_version=4
update user set name=‘张三三’ where id=1; 复制代码
执行成功后的结果是这样的
id name create_version delete_version
如果这时候还有小黑在删除id=2的数据，current_version=5，执行后结果是这样的。
id name create_version delete_version
由于MVCC的原理是查找创建版本小于或等于当前事务版本，删除版本为空或者大于当前事务版本，小明的真实的查询应该是这样
select * from user where id<=3 and create_version<=3 and (delete_version>3 or delete_version is null); 复制代码
所以小明最后查询到的id=1的名字还是’张三’，并且id=2的记录也能查询到。这样做是为了保证事务读取的数据是在事务开始前就已经存在的，要么是事务自己插入或者修改的。
明白MVCC原理，我们来说什么是幻读就简单多了。举一个常见的场景，用户注册时，事务A 按照一定条件进行数据读取， 期间事务B 插入了相同搜索条件的新数据，事务A再次按照原先条件进行读取时，发现了事务B 新插入的数据 称为幻读

8、 那你知道什么是间隙锁吗？

间隙锁是可重复读级别下才会有的锁，结合MVCC和间隙锁可以解决幻读的问题。我们还是以user举例，假设现在user表有几条记录

当我们执行：
select * from user where age=20 for update;
insert into user(age) values(10); #成功
insert into user(age) values(11); #失败
insert into user(age) values(20); #失败
insert into user(age) values(21); #失败
insert into user(age) values(30); #失败
只有10可以插入成功，那么因为表的间隙mysql自动帮我们生成了区间(左开右闭)
(negative infinity，10],(10,20],(20,30],(30,positive infinity)
由于20存在记录，所以(10,20]，(20,30]区间都被锁定了无法插入、删除。
如果查询21呢？就会根据21定位到(20,30)的区间(都是开区间)。
需要注意的是唯一索引是不会有间隙索引的。

9、你们数据量级多大？分库分表怎么做的？

阿里巴巴《Java 开发手册》提出单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表，当然这个还说跟内存大小有关系。

首先分库分表分为垂直和水平两个方式，一般来说我们拆分的顺序是先垂直后水平。
垂直分库
基于现在微服务拆分来说，都是已经做到了垂直分库了。
垂直分表
如果表字段比较多，将不常用的、数据较大的等等做拆分。
水平分表
首先根据业务场景来决定使用什么字段作为分表字段(sharding_key)，比如我们现在日设备1台设备一天运行6个小时 数据是1秒一条 一天大约发送20000条数据，因为疫情原因，目前每天大约是同时在线的设备有20台设备，一天大约40W数据，一个月是900W数据量。分表的测试是2个月，按照月份分区 。使用mycat分表分库，读写分离。

日订单1000万，我们大部分的场景来源于C端，我们可以用user_id作为sharding_key，数据查询支持到最近3个月的订单，超过3个月的做归档处理，那么3个月的数据量就是9亿，可以分1024张表，那么每张表的数据大概就在100万左右。
比如用户id为100，那我们都经过hash(100)，然后对1024取模，就可以落到对应的表上了。

10、那分表后的ID怎么保证唯一性的呢？

因为我们主键默认都是自增的，那么分表之后的主键在不同表就肯定会有冲突了。有几个办法考虑：
snowflake算法，分布式ID算法

1. 设定步长，比如1-1024张表我们分别设定1-1024的基础步长，这样主键落到不同的表就不会冲突了。
2. 分布式ID，自己实现一套分布式ID生成算法或者使用开源的比如雪花算法这种。
3. 分表后不使用主键作为查询依据，而是每张表单独新增一个字段作为唯一主键使用，比如订单表订单号是唯一的，不管最终落在哪张表都基于订单号作为查询依据，更新也一样。

11、 分表后非sharding_key的查询怎么处理呢？

1. 可以做一个mapping表，比如这时候商家要查询订单列表怎么办呢？不带user_id查询的话你总不能扫全表吧？所以我们可以做一个映射关系表，保存商家和用户的关系，查询的时候先通过商家查询到用户列表，再通过user_id去查询。
2. 打宽表，一般而言，商户端对数据实时性要求并不是很高，比如查询订单列表，可以把订单表同步到离线（实时）数仓，再基于数仓去做成一张宽表，再基于其他如es提供查询服务。
3. 数据量不是很大的话，比如后台的一些查询之类的，也可以通过多线程扫表，然后再聚合结果的方式来做。或者异步的形式也是可以的。
   List<Callable<List>> taskList = Lists.newArrayList(); for (int shardingIndex = 0; shardingIndex < 1024; shardingIndex++) { taskList.add(() -> (userMapper.getProcessingAccountList(shardingIndex))); } List list = null; try { list = taskExecutor.executeTask(taskList); } catch (Exception e) { //do something } public class TaskExecutor { public List executeTask(Collection<? extends Callable> tasks) throws Exception { List result = Lists.newArrayList(); List<Future> futures = ExecutorUtil.invokeAll(tasks); for (Future future : futures) { result.add(future.get()); } return result; } }
   • 1

12、说说mysql主从同步怎么做的吧？

首先先了解mysql主从同步的原理

1. master提交完事务后，写入binlog
2. slave连接到master，获取binlog
3. master创建dump线程，推送binglog到slave
4. slave启动一个IO线程读取同步过来的master的binlog，记录到relay log中继日志中
5. slave再开启一个sql线程读取relay log事件并在slave执行，完成同步
6. slave记录自己的binglog
   

由于mysql默认的复制方式是异步的，主库把日志发送给从库后不关心从库是否已经处理，这样会产生一个问题就是假设主库挂了，从库处理失败了，这时候从库升为主库后，日志就丢失了。由此产生两个概念。

全同步复制
主库写入binlog后强制同步日志到从库，所有的从库都执行完成后才返回给客户端，但是很显然这个方式的话性能会受到严重影响。
半同步复制
和全同步不同的是，半同步复制的逻辑是这样，从库写入日志成功后返回ACK确认给主库，主库收到至少一个从库的确认就认为写操作完成。

1、面试题

你们有没有做MySQL读写分离？如何实现mysql的读写分离？MySQL主从复制原理的是啥？如何解决mysql主从同步的延时问题？

2、面试官心里分析

这个，高并发这个阶段，那肯定是需要做读写分离的，啥意思？因为实际上大部分的互联网公司，一些网站，或者是app，其实都是读多写少。所以针对这个情况，就是写一个主库，但是主库挂多个从库，然后从多个从库来读，那不就可以支撑更高的读并发压力了吗？

3、面试题剖析

（1）如何实现mysql的读写分离？

其实很简单，就是基于主从复制架构，简单来说，就搞一个主库，挂多个从库，然后我们就单单只是写主库，然后主库会自动把数据给同步到从库上去。

（2）MySQL主从复制原理的是啥？

主库将变更写binlog日志，然后从库连接到主库之后，从库有一个IO线程，将主库的binlog日志拷贝到自己本地，写入一个中继日志中。接着从库中有一个SQL线程会从中继日志读取binlog，然后执行binlog日志中的内容，也就是在自己本地再次执行一遍SQL，这样就可以保证自己跟主库的数据是一样的。

这里有一个非常重要的一点，就是从库同步主库数据的过程是串行化的，也就是说主库上并行的操作，在从库上会串行执行。所以这就是一个非常重要的点了，由于从库从主库拷贝日志以及串行执行SQL的特点，在高并发场景下，从库的数据一定会比主库慢一些，是有延时的。所以经常出现，刚写入主库的数据可能是读不到的，要过几十毫秒，甚至几百毫秒才能读取到。

而且这里还有另外一个问题，就是如果主库突然宕机，然后恰好数据还没同步到从库，那么有些数据可能在从库上是没有的，有些数据可能就丢失了。

所以mysql实际上在这一块有两个机制，一个是半同步复制，用来解决主库数据丢失问题；一个是并行复制，用来解决主从同步延时问题。

这个所谓半同步复制，semi-sync复制，指的就是主库写入binlog日志之后，就会将强制此时立即将数据同步到从库，从库将日志写入自己本地的relay log之后，接着会返回一个ack给主库，主库接收到至少一个从库的ack之后才会认为写操作完成了。

所谓并行复制，指的是从库开启多个线程，并行读取relay log中不同库的日志，然后并行重放不同库的日志，这是库级别的并行。
• 并行是指从库多线程apply binlog库级别并行应用binlog，同一个库数据更改还是串行的(5.7版并行复制基于事务组)设置
• 设置sql线程数为10
set global slave_parallel_workers=10

1）主从复制的原理
2）主从延迟问题产生的原因
3）主从复制的数据丢失问题，以及半同步复制的原理
4）并行复制的原理，多库并发重放relay日志，缓解主从延迟问题

（3）mysql主从同步延时问题（精华）

线上确实处理过因为主从同步延时问题，导致的线上的bug，小型的生产事故

show status，Seconds_Behind_Master，你可以看到从库复制主库的数据落后了几ms

其实这块东西我们经常会碰到，就比如说用了mysql主从架构之后，可能会发现，刚写入库的数据结果没查到，结果就完蛋了。。。。

所以实际上你要考虑好应该在什么场景下来用这个mysql主从同步，建议是一般在读远远多于写，而且读的时候一般对数据时效性要求没那么高的时候，用mysql主从同步

所以这个时候，我们可以考虑的一个事情就是，你可以用mysql的并行复制，但是问题是那是库级别的并行，所以有时候作用不是很大

所以这个时候。。通常来说，我们会对于那种写了之后立马就要保证可以查到的场景，采用强制读主库的方式，这样就可以保证你肯定的可以读到数据了吧。其实用一些数据库中间件是没问题的。

一般来说，如果主从延迟较为严重

1、分库，将一个主库拆分为4个主库，每个主库的写并发就500/s，此时主从延迟可以忽略不计
2、打开mysql支持的并行复制，多个库并行复制，如果说某个库的写入并发就是特别高，单库写并发达到了2000/s，并行复制还是没意义。28法则，很多时候比如说，就是少数的几个订单表，写入了2000/s，其他几十个表10/s。
3、重写代码，写代码的同学，要慎重，当时我们其实短期是让那个同学重写了一下代码，插入数据之后，直接就更新，不要查询
4、如果确实是存在必须先插入，立马要求就查询到，然后立马就要反过来执行一些操作，对这个查询设置直连主库。不推荐这种方法，你这么搞导致读写分离的意义就丧失了

13、那主从的延迟怎么解决呢？

这个问题貌似真的是个无解的问题，只能是说自己来判断了，需要走主库的强制走主库查询。

14 mysql查询优化

以下通过explain显示出mysql执行的字段内容：
id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.id相同，执行顺序从上往下，id不同，id值越大，优先级越高，越先执行
select_type: SELECT 查询的类型.
table: 查询的是哪个表
partitions: 匹配的分区
type: join 类型
possible_keys: 此次查询中可能选用的索引
key: 此次查询中确切使用到的索引.
ref: 哪个字段或常数与 key 一起被使用
rows: 显示此查询一共扫描了多少行. 这个是一个估计值.
filtered: 表示此查询条件所过滤的数据的百分比
extra: 额外的信息

1. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
   2 最佳左前缀原则——如果索引了多列，要遵守最左前缀原则。指的是查询要从索引的最左前列开始并且不跳过索引中的列。
2. 不在索引列上做任何操作（计算，函数，（自动或者手动）类型装换），会导致索引失效而导致全表扫描
3. mysql使用不等于(!= 或者<>)的时候，无法使用索引，会导致索引失效
   2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
   select id from t where num is null
   可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
   select id from t where num=0

3.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20

5.in 和 not in 也要慎用，否则会导致全表扫描，如：
select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3
不使用NOT IN和<>操作
NOT IN和<>操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id<>3则可使用id>3 or id<3来代替。
6.下面的查询也将导致全表扫描：
select id from t where name like ‘%abc%’

7.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where num/2=100
应改为:
select id from t where num=100*2

8.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where substring(name,1,3)=‘abc’–name以abc开头的id
应改为:
select id from t where name like ‘abc%’

9.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

10.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
11.尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间，
其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

12.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
15索引失效的例子
1、like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。
2、or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效

15 mysql优化

主要从这些方面回答 1.服务器硬件 2。数据库结构优化 3 索引优化 4 sql优化 5使用缓存

15.1 服务器硬件

这个不需要多说了吧，花钱就行。配置越高数据库的性能瓶颈就越高

15.2 数据库结构优化

1. 遵循三大范式
   1NF:原子性 字段不可再分,否则就不是关系数据库;
   例如：地址： 山东省青岛市城阳区**街道@@ 要拆分成 省 市 区，街道和具体的地址@@
   2NF:唯一性 一个表只说明一个事物;

学号（主键）	课程（主键）	教师姓名	成绩	学生姓名	专业
202001	C语言程序设计	老张	80	张三	计算机科学与技术
202002	JAVA程序设计	老李	87	李四	网络工程
202003	数据结构	老王	90	王舞	软件工程

3NF:每列都与主键有直接关系，不存在传递依赖;

学号（主键）	姓名	性别	年级	专业	班主任姓名	班主任性别	班主任年龄
202001	张三	男	大一	计算机科学与技术	老张	男	33
202002	李四	男	大二	网络工程	老李	男	34
202003	王舞	女	大三	软件工程	老王	男	35

总结：

个人认为，三大范式（1NF、2NF、3NF），其实是将一张表不停拆分、细化的过程，但在实际情况中，不停的对表进行拆分，在执行查询操作时就需要进行联表查询，所以，拆分的表多了，也会导致查询的效率也会大打折扣。所以一般最多拆成3张表，并且，为了查询方便，可能也会故意的将一些与主键无关的属性放在表中（在设计过程中，三大范式不是需要严格遵守的，只是提供了一种设计规范，供人参考使用）

2. 在表中建立索引，优先考虑where、order by使用到的字段。

3. 尽量使用数字型字段（如性别，男：1 女：2），若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

4. 用varchar/nvarchar 代替 char/nchar
   尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
   不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了， 不管是否插入值（NULL也包含在内），都是占用 100个字符的空间的，如果是varchar这样的变长字段， null 不占用空间。

5. 大字段拆分单独创建一个表，比如一个表中如果有大文本，可以将他在新建一个表中关联。

15.3 索引优化

在表中建立索引，优先考虑where、order by使用到的字段。同时要避免索引失效

15.4 sql优化

索引与sql优化可以参照下面的地址
https://blog.csdn.net/qq_39390545/article/details/107020686

15.5 分表分库，读写分离

15.6 使用缓存

缓存技术 redis，mongodb

16、索引失效常见的场景

索引失效

其他

https://blog.csdn.net/ThinkWon/article/details/104778621