MySQL常见知识点

MySQL常见知识点
1、 innodb与myisam引擎的区别：
innodb支持外键、事务、行锁、表锁、索引和数据在同一个文件，不支持全文索引。
Myisam不支持外键、事务，支持表锁，索引和数据在不同的文件，支持全文索引。

2、 最左匹配原则与where后面的条件顺序有关吗
最左：以最左边为起点的任何连续索引都可以匹配，与条件先后顺序无关。

3、 哈希索引与B+树索引的区别：
哈希索引适用于等值查询，不适用于范围查询。B+树索引适用于等值查询、范围查询和排序。
4、 表创建时无主键怎么办
首先会选择第一个not null修饰的uk索引引作为聚簇索引，若无，则会在记录中添加隐藏列的row_id
5、 如何判断SQL走的是哪个索引
同explain语句来判断，可以展示详细信息，包括使用的索引、查询类型、表的访问顺序等。
Explain SELECT * FROM table_name WHERE condition;。
Type字段（查询的类型）、key字段（使用的索引名称）、rows字段（扫描的行数）、extra字段（附加信息，包括临时表、排序等）。
6、 Delete语句后，空间会不会自动释放
不会，会将此记录记为已删除，页内已删除的记录作为一个垃圾链表，下次需要向此页插入数据的时候，优先使用此空间。
7、 深翻页问题如何解决
指在数据库查询中获取很多页数据时，需要跳过大量的数据行来获取指定页的数据。
使用合适的索引：通过创建适当的索引，可以让数据库对数据进行快速的定位和访问。
实现分页缓存：可以将查询结果缓存在应用程序的内存中，下次需要获取相同页的数据时，可以直接从缓存中获取，避免再次进行数据库查询。
使用游标查询：游标允许一次获取一部分数据，然后移动游标指针获取下一部分数据，这样就可以减少深翻页的开销。
考虑调整分页策略：增加每页数据量，减少需要获取的页数，从而减少数据跳过的数量。根据具体的业务需求和性能要求，选择合适的分页策略。
8、 innodb的锁
共享锁：多个事务可以同时持有共享锁，用于保护读一致性。
排他锁：他锁用于保护写操作的一致性，在一个事务持有排他锁时，其他事务无法获取同一个资源的共享锁或排他锁。只有当持有排他锁的事务释放锁之后，其他事务才能获得该资源的锁。
记录锁：也称为行锁，用于保护对单个数据行的操作。当一个事务锁定了一行数据时，其他事务无法读取或修改该行的数据
间隙锁：隙锁是用于防止其他事务在范围查询时插入新的记录，从而保证范围查询的一致性。
next-key锁：Next-Key锁是记录锁和间隙锁的结合，用于保证范围查询的一致性。
9、 innodb的事务原理
原子性（InnoDB通过日志（Log）来实现原子性。）
一致性（InnoDB通过在修改过程中对数据和索引进行加锁来保证数据的一致性，并且支持外键约束等完整性规则。）
隔离性（InnoDB使用多版本并发控制（MVCC）来实现隔离性。）
持久性（InnoDB通过将事务操作写入磁盘上的日志文件中）
InnoDB还采用了多版本并发控制（MVCC）来提高并发性能。InnoDB使用redo log和undo log来实现事务的持久性和回滚。redo log用于在事务提交前将事务操作记录到日志中，以保证数据的持久性。而undo log用于记录事务的前版本数据，用于在回滚、事务隔离和MVCC的实现中。
10、 binlog和redolog的区别：
用途不同：binlog用于记录数据库的逻辑变更，包括插入、更新和删除操作等，它是在SQL语句级别上进行记录的。而redolog用于记录InnoDB存储引擎的物理变更，即对数据页的修改等，它是在页面级别上进行记录的。
记录的内容不同：binlog中记录的是数据库语句的逻辑操作，以文本格式的形式存储，易于读取和理解。redolog中记录的是对数据库页的物理修改操作，以二进制格式的形式存储，其主要目的是为了在数据库崩溃时进行恢复操作。
存储位置不同：binlog是以文件的形式存储在磁盘上，通常只保存一定的时间范围或大小，并且可以通过配置进行轮转和清理。redolog是以循环写的方式存储在磁盘上，保证了数据的持久性和高效的写入性能。
写入时机不同：binlog是在事务提交之后才被写入，这样可以减少磁盘I/O的次数。redolog则是在事务进行中的每个修改操作都会生成对应的日志记录，保证了修改操作的持久性
恢复的方式不同：binlog主要用于恢复数据，可以执行对应的SQL语句来重新执行之前的操作。redolog主要用于崩溃恢复，可以通过重做日志来恢复最后一次正常关闭数据库服务之前的数据。
11、 mysql分布式解决方案
MySQL主从复制（Master-Slave Replication）：
这是最简单的MySQL分布式解决方案之一。通过配置一台主服务器（Master）和多台从服务器（Slaves），主服务器负责写入操作，而从服务器复制主服务器上的数据用于读取操作。这样可以实现读写分离，提高系统性能。

注意，主从复制并不能实现真正的数据分布和负载均衡，因为写操作依然只能在主服务器上执行。

MySQL主从链式复制（Master-Slave Chain Replication）：
主从链式复制是在主从复制的基础上做了改进，通过将从服务器配置为另一个从服务器的主服务器，形成一个多级链式复制结构。这样可以实现数据分发，并且在链式结构中可以加入多个从服务器进行读取操作，从而提高读性能。

注意，链式复制的主从延迟会累积，导致从服务器之间的数据同步可能不是实时的。

MySQL分区（Partitioning）：
分区是指将数据按照一定的规则分散存储在多个数据库实例中。可以根据数据的范围、哈希、列表等方式对表进行分区，并将每个分区分配到不同的物理节点上。这样可以实现数据分布和负载均衡。

MySQL提供了多种分区策略，如范围分区、哈希分区和列表分区等。

MySQL集群（MySQL Cluster）：
MySQL集群是一种基于共享存储的方式，将数据分布在多个节点上，提供高可用和高性能。MySQL集群采用NDB存储引擎，其使用多个节点共享数据和负载。在集群中的每个节点都持有完整的数据副本，因此可以实现数据的高可用性和容错性。

12、MVCC是什么
MVCC是多版本并发控制，为每次事务生成一个新版本数据，每个事务都由自己的版本，从而不加锁就决绝读写冲突，这种读叫做快照读。只在读已提交和可重复读中生效。

实现原理由四个东西保证，他们是

undolog日志：记录了数据历史版本

readView:事务进行快照读时动态生成产生的视图，记录了当前系统中活跃的事务id，控制哪个历史版本对当前事务可见

隐藏字段DB_TRC_ID： 最近修改记录的事务ID

隐藏字段DB_Roll_PTR： 回滚指针，配合undolog指向数据的上一个版本。
13、聚簇索引和非聚簇索引的区别
聚簇索引：聚簇索引的叶子节点存放的是主键值和数据行；辅助索引（在聚簇索引上创建的其它索引）的叶子节点存放的是主键值或指向数据行的指针。

优点：根据索引可以直接获取值，所以他获取数据更快；对于主键的排序查找和范围查找效率更高；

缺点：如果主键值很大的话，辅助索引也会变得很大；如果用uuid作为主键，数据存储会很稀疏；修改主键或乱序插入会让数据行移动导致页分裂；所以一般我们定义主键时尽量让主键值小，并且定义为自增和不可修改。

非聚簇索引（辅助索引）：叶子节点存放的是数据行地址，先根据索引找到数据地址，再根据地址去找数据

14、MySQL如何做慢SQL优化
（1）尽量减少select的数据列，尽量使用覆盖索引

（2）orderby查找时使用索引进行排序，否则的话需要进行回表

（3）groupby查询时，同样要用索引，避免使用到临时表

（4）分页查询时，如果limit 后面的数字太大，可以使用子查询查出主键，再limit主键后n条数据就能走覆盖索引

（5） 使用复杂查询时，使用关联查询来代替子查询，并且最好使用内连接

（6）使用count函数时直接使用count的话count(*)的效率最高

count(*)或count(唯一索引)或count(数字):表中总记录数，count(字段)不会统计null

(7) 在写update语句时，where条件要使用索引，否则会锁会从行锁升级为表锁

（8）表中数据是否太大，是不是要分库分表

15、MySQL整个查询的过程
（1）客户端向 MySQL 服务器发送一条查询请求
（2）服务器首先检查查询缓存，如果命中缓存，则返回存储在缓存中的结果。否则进入下一阶段
（3）服务器进行 SQL 解析、预处理、再由优化器生成对应的执行计划
（4）MySQL 根据执行计划，调用存储引擎的 API 来执行查询
（5）将结果返回给客户端，同时缓存查询结果
注意：只有在8.0之前才有查询缓存，8.0之后查询缓存被去掉了

16、哪些情况索引会失效
（1）where条件中有or，除非所有查询条件都有索引，否则失效

（2）like查询用%开头，索引失效

（3）索引列参与计算，索引失效

（4）违背最左匹配原则，索引失效

（5）索引字段发生类型转换，索引失效

（6）mysql觉得全表扫描更快时（数据少），索引失效

17、三大范式
第一范式：每个列都不可以再拆分。

第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。

第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。

18、InnoDB 什么情况下会产生死锁
事务1已经获取数据A的写锁，想要去获取数据B的写锁，然后事务2获取了B的写锁，想要去获取A的写锁，相互等待形成死锁。
mysql解决死锁的机制有两个：1.等待， 直到超时 2.发起死锁检测，主动回滚一条事务
死锁检测的原理是构建一个以事务为顶点、 锁为边的有向图， 判断有向图是否存在环， 存在即有死锁。
我们平时尽量减少事务操作的资源和隔离级别。