@x-power 2022-12-26T06:04:42.000000Z 字数 2705 阅读 244

MySQL

面试

MySQL可以分为server层和存储引擎层两部分

Server层包括连接器、查询缓存、分析器、优化器、执行器等，其涵盖MySQL的大多数核心服务功能，以及所有的内置函数，所有跨存储引擎的功能都在这一层实现，比如存储过程，触发器，视图等。
存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的存储引擎是InnoDB.

Server层基本架构

连接器：登录数据库的连接验证，完成经典的TCP握手之后，连接器就要开始认证身份。mysql -h $ip -P $port -u user -p $Password
查询缓存：连接器简历完成之后，会去到缓存中查询数据。查询缓存的存储默认key为查询语句，而value为结果。
分析器：主要做语法解析，并判断语法是否合规。
优化器：对语法的执行流程做优化决定使用哪个索引。select * from t1 join t2 using ID where t1.c=10 and t2.d=20;

既可以先到表t1中找到c=10，然后再根据ID的关联到t2中找d=20的记录。也可以相反操作。这两种执行方法的逻辑结果是一样的，但是执行的效率有所不同，而优化器就是选择效率高的方法。

执行器：负责具体语句的执行，首先判断是否有权限。select * from T where ID=10

如果ID字段没有索引的话，那么执行器的执行流程是这样的：
1. 调用Innodb引擎接口读取这个表的第一行，判断ID值是否为10，如果不是则跳过，如果是则将改行存在结果集中；
2. 调用引擎接口读取下一行，重复相同的判断逻辑，知道这个表的最后一行。
3. 执行器将上述便利过程中所有满足条件的行组成的记录集合作为结果集返回给客户端。

索引

索引的出现就是为了提高数据查询的效率，就想书的目录一样。对于数据库的表而言，索引其实就是它的目录。

如果一张表中没有主键索引，mysql就会默认创建一个长度为6字节的rowid主键。

主键索引、唯一索引、普通索引。

索引主要有：唯一索引、主键（聚集）索引、非聚集（普通）索引。

主键索引和普通索引的区别

一个表中只能有一个主键索引，但是可以有多个普通索引。
主键索引在存储记录上是屋里连续的，而普通索引（非聚集索引）是逻辑连续。
查询区别：主要在于若执行查询中需要较多的信息，普通索引会进行回表操作。
- 如果语句是select * from T where ID=500，即主键查询方式，则只需要搜索ID这一棵B+树。
- 如果语句是select * from T where k=5，即普通索引，则需要先搜索k索引树，得到的ID值为500，然后再搜索ID一次，这个操作被称为回表操作。

唯一索引和普通索引

两者查询性能差不多。
主要区别在于，更新的记录目标页不在内存中时。普通索引更新会使用change buffer。唯一索引，由于需要验证数据的唯一性，因此每次更新操作都需要读磁盘将数据加载进内存，设计IO操作。因此需要尽量减少唯一索引作为查询条件使用，以避免增加IO操作。

在不影响数据一致性的前提下，InnoDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据也了。在下次查询需要访问这个数据也的时候，将数据也读入内存，然后执行change buffer中与这个页有关的操作。通过该种方式就可以保证数据逻辑完整性。

change buffer中的操作应用到原数据页，得到最新结果的过程称为merge。
1. 访问这个数据页会触发merge。
2. 系统有后台线程会定期merge。
3. 执行数据库关闭操作的时候，也会执行merge操作。

change buffer和redo log的区别

change buffer主要用于减少读磁盘的次数，在必要时更新数据。
redo log则是减少内存更新后，写磁盘的次数。

索引规则

覆盖索引

InnoDB支持覆盖索引，即从辅助索引中就可以得到查询的记录，则不需要查询聚集索引中的记录。

select id,b from t where b=xxx此时id为主键，b为索引由于覆盖索引可以减少搜索的次数，显著提高查询性能，所以索引覆盖是一个常用的性能优化手段。

最左前缀原则

以最左边为起点任何连续的索引都能匹配上。
1. 如果第一个字段是范围查询需要单独简历一个索引
2. 在创建多列索引时，根据业务需求，where自居中使用最频繁的一列放在最左边

索引失效情况

where语句中包含or时，可能会导致索引失效。

如果or条件中包含非索引，就会使用全表扫描。如果两边都是索引，就会使用index_merge的优化技术。
where语句中索引如果使用了负向查询，可能会导致索引失效。

Not、!=、<>、!<、!>、NOT IN、NOT LIKE等。
对索引列进行运算，则一定会导致索引失效。

SELECT * from T where ID+1 = 3
在索引列上使用内置函数，一定会导致索引失效

SELECT *, SUBSTR(materiel_id,10) from container_load where SUBSTR(materiel_id,10) = 852729344
为满足最左匹配原则

select * from t where k2=2;
select * from t where k3=3;
slect * from t where k2=2 and k3=3;
// 以下这条只会部分走索引
slect * from t where k1=1 and k3=3;

redo log、undo log、bin log

undo log

undo log主要有两个作用：回滚和多版本控制。
在数据修改的时候，不仅记录了redo log，还记录了undo log，如果因为某些原因导致事务失败或者回滚了，可以使用undo log进行回滚。
1. 保证事务进行rollback时的原子性和一致性。undo log主要存储的也是逻辑日志，比如我们需要insert一条数据，那么undo log会记录一条对应的delete日志。我们要update一条记录时，他会记录一条相反的update记录。
2. 用于MVCC快照读的数据，在MVCC多版本控制中，通过读取undo log的历史版本数据可以实现不同事物版本号都拥有自己独立的快照版本数据。

redo log

MySQL里长说道WAL（WriteAheadLogging）技术，他的关键点就是先写日志，在写磁盘。

当有一条记录需要更新的时候，InnoDB引擎就会先把记录写到redo log里面，并且更新内存，这个时候更新就算是完成了，同时InnoDB会在闲暇的时候将操作记录更新到磁盘当中。