数据库原理以及常见问题
本文最后更新于 2025-03-23，文章超过7天没更新，应该是已完结了~
什么是事务？事务的基本特性ACID?
事务指的是满足ACID特性的一组操作，可以通过Commit提交一个事务，可以用Rollback进行回滚。
事务的基本特性ACID:
A原子性(atomicity)指的是一个事务的操作要么全部成功，要么全部失败。回滚可以用回滚日志（Undo Log）来实现，回滚日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可。
C一致性(consisency)指的是数据库总是从一个一致性状态转换到另一个一致性的状态。在一致性状态下，所有事务对同一个数据的读取结果都是相同的。
I隔离性(isolation)指的是一个事物的修改在最终提交之前，对其它事务是不可见的。
D持久性(durability)指的是一旦事务提交，所做的修改就会永久保存到数据库中。系统发生崩溃可以用重做日志（Redo Log）进行恢复，从而实现持久性。与回滚日志记录数据的逻辑修改不同，重做日志记录的是数据页的物理修改。
数据库中并发一致性问题？
丢失修改
T1和T2两个事务都对一个数据进行修改，T1先修改，T2随后修改，T2的修改覆盖了T1的修改。
读脏数据
T1修改一个数据(未提交)，T2随后读取了这个数据，如果T1撤销了这次修改，那么T2读取到的是脏数据。
不可重复读
T2读取一个数据，T1对该数据进行了修改，如果T2再次读取这个数据，此时读取的结果和第一次读取的结果不同。
幻读
T1读取某个范围的数据，T2在这个范围内插入新的数据，T1再读取这个范围的数据，此时读取的结果和第一次读取的结果不同。
事务的隔离等级？
未提交读(READ UNCOMMITTED)事务中的修改，即使没有提交，对其它事务也是可见的。
提交读(READ COMMITTED)一个事务只能读取已经提交的事务所做的修改。也即，一个事务所做的修改在提交之前对其它事务是不可见的。
可重复读(REPEATABLE READ)保证在同一个事务中多次读取同样的数据的结果是一样的。（MySQL默认隔离级别）但是与标准SQL不同的是，InnoDB存储引擎在REPEATABLE READ事务隔离级别下，使用Next-Key Lock的锁算法，因此避免了幻读的产生。所以InnoDB存储引擎在默认的事务隔离级别下已经能完全保证事务的隔离性要求，即达到SQL标准的SERIALIZABLE隔离级别
可串行化(SERIALIZABLE)强制事务串行执行。
SERIALIZABLE： 读的时候加共享锁,其他事务可以并发读,但是不能写。写的时候加排它锁,其他事务不能并发写也不能并发读。 REPEATABLE READ & READ COMMITTED： 为了解决不可重复读,MySQL 采用了 MVVC (多版本并发控制) 的方式。 我们在数据库表中看到的一行记录可能实际上有多个版本,每个版本的记录除了有数据本身外,还要有一个表示版本的字段,记为 row trx_id,而这个字段就是使其产生的事务的 id,事务 ID 记为 transaction id,它在事务开始的时候向事务系统申请,按时间先后顺序递增。
ACID靠什么保证的呢？
原子性：由undo log日志保证，它记录了需要回滚的日志信息，事务回滚时根据undo log日志撤销执行成功的sql。
一致性：一般由代码层面来保证。
隔离性：由MVCC来保证
持久性：由内存+redo log来保证，mysql修改数据同时在内存和redo log记录这次操作，事务提交时通过redo log刷盘，宕机的时候可以从redo log中恢复数据。
Buffer Pool 和undo log、redo log、bin log 概念以及关系？
Buffer Pool 是 MySQL的一个非常重要的组件，因为针对数据库的增删改操作都是Buffer Pool中完成的。
Undo log记录的是数据操作前的样子
redo log记录的是数据被操作后的样子(redo log是Innodb存储引擎独有)
bin log记录的是整个操作记录（主从复制的重要元素）
悲观锁和乐观锁
悲观锁：悲观锁的实现，往往依靠数据库提供的锁机制。
乐观锁：乐观锁，大多是基于数据版本（ Version ）记录机制实现。何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。
此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如 果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。
MVCC(多版本并发控制)
英文全称为Multi-Version Concurrency Control，以乐观锁为理论基础的MVCC，它的实现没有固定的规范，每个数据库都有不同的实现方式。
MVCC主要是为了提高数据库并发性能,用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。 它是一种用来解决读-写冲突的无锁并发控制机制。在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能，还可以解决脏读、幻读、不可重复读等事务隔离问题，但不能解决更新丢失问题。
MVCC是利用在每条数据后面加了隐藏的两列：创建版本号和删除版本号，每个事务在开始的时候都会有一个递增的版本号
新增
insert into user (id,name,age)values(1,"张三",10);
更新
update user set age = 11 where id = 1;
更新操作使用delete+add方式实现，首先将当前数据标志为删除
然后新增一条数据
删除
delete from user where id = 1;
直接将删除版本号更新为当前版本的事务号
查询操作为了避免查询到旧数据或已经被其他事务更改过的数据，需要满足如下条件：
查询时当前事务的版本需要大于等于创建版本（防止查询旧数据）
查询时当前事务的版本号需要小于删除的版本号（防止查询已被更新或删除的数据）
即：create_version <= current_version < delete_version
这样就可以避免查询到其他事务修改的数据
MVCC相关补充
MVCC手段只适用于MySQL隔离级别中的读已提交（Read committed）和可重复读（Repeatable Read)，Read uncimmitted由于存在脏读，即能读到未提交事务的数据行，所以不适用MVCC.：原因是MVCC的创建版本和删除版本只要在事务提交后才会产生。
串行化由于是会对所涉及到的表加锁，并非行锁，自然也就不存在行的版本控制问题。
SQL优化的实践经验？
对查询进行优化，尽量避免使用全表扫描，首先应考虑在where及order by涉及的列上建立索引
如果索引有多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。不遵守则索引失效。
尽量使用覆盖索引，即只访问索引查询：索引列和查询列一致，减少select *
索引范围条件右边的索引列会失效。
explain select * from user where name = 'zhangsan' and age > 20 and pos = 'cxy';
#pos索引失效尽量避免在where 字句中使用 != 或 <>操作符，否则索引失效。
尽量避免在where字句中使用or来连接条件，如果其中一个字段没有索引，则另一字段的索引失效。可以替换成这样查询：
select id from t where num = 10
union all
select id from t where Name = 'admin'尽量避免头部模糊查询，如('%abc')，尽量用尾部模糊查询
尽量避免在索引列上做任何操作，如计算、函数、类型转换等操作，会导致索引失效
记住字符串加单引号，否则索引会失效
一些建议：
索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个
应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。
尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连 接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
任何地方都不要使用 select from t ，用具体的字段列表代替“”，不要返回用不到的任何字段。
从准备更新一条数据到事务的提交的流程描述？
索引有什么用？
优点：
加快数据查找的速度
为用来排序或者是分组的字段添加索引，可以加快分组和排序的速度
加快表与表之间的连接
缺点：
建立索引需要占用物理空间
会降低表的增删改查的效率，因为每次对表记录进行增删改，需要动态维护索引
MySQL主从同步如何实现
复制（replication）是MySQL数据库提供的一种高可用高性能的解决方案，一般用来建立大型的应用。总体来说，replication的工作原理分为以下3个步骤：
主服务器（master）把数据更改记录到二进制日志（binlog）中。
从服务器（slave）把主服务器的二进制日志复制到自己的中继日志（relay log）中。
从服务器重做中继日志中的日志，把更改应用到自己的数据库上,以达到数据的最终一致性。
复制的工作原理并不复杂,其实就是一个完全备份加上二进制日志备份的还原。不同的是这个二进制日志的还原操作基本上实时在进行中。这里特别需要注意的是,复制不是完全实时地进行同步,而是异步实时。这中间存在主从服务器之间的执行延时,如果主服务器的压力很大,则可能导致主从服务器延时较大。
数据库引擎有哪些,各自有什么区别
InnoDB 引擎是 MySQL 的事务安全存储引擎,具有提交、回滚和崩溃恢复功能来保护用户数据；行级锁定读取增加了多用户并发性和性能；将用户数据存储在聚集索引中,以减少基于主键的常见查询的 I/O；还支持 FOREIGN KEY 维护数据完整性。
MyISAM引擎的表占用空间较小,表级锁定限制了读/写工作负载的性能,因此它通常用于只读或以读取为主的场景。
Memory引擎是将所有数据存储在 RAM 中,以便在需要快速查找非关键数据的环境中进行快速访问
Archive引擎非常适合存储大量的独立的,作为历史记录的数据,因为它们不经常被读取。它 拥有高效的插入速度,但其对查询的支持相对较差。
Cluster/NDB是高冗余的存储引擎,用多台数据机器联合提供服务以提高整体性能和安全性。适合数据量大,安全和性能要求高的应用。
Federated引擎提供连接单独的 MySQL 服务器,从多个物理服务器创建一个逻辑数据库的能力,非常适合分布式或数据集市环境。
聚集索引和非聚集索引区别
两者的区别就是数据与索引是否分离。在InnoDB存储引擎中,可以将B+树索引分为聚簇索引和辅助索引（非聚簇索引）。无论是何种索引,每个页的大小都为16KB,且不能更改。 聚簇索引是根据主键创建的一棵B+树,聚簇索引的叶子节点存放了表中的所有记录（聚集索引相当于整张表，而整张表也是聚集索引）。辅助索引是根据索引键创建的一棵B+树,与聚簇索引不同的是,其叶子节点仅存放索引键值,以及该索引键值指向的主键。也就是说,如果通过辅助索引来查找数据,那么当找到辅助索引的叶子节点后,很有可能还需要根据主键值查找聚簇索引来得到数据,这种查找方式又被称为书签查找。
因为辅助索引不包含行记录的所有数据,这就意味着每页可以存放更多的键值,因此其高度一般都要小于聚簇索引。
在InnoDB中,一个表有且仅有一个聚簇索引（因为原始数据只留一份,而数据和聚簇索引在一起）,并且该索引是建立在主键上的,即使没有指定主键,也会特殊处理生成一个聚簇索引；其他索引都是辅助索引,使用辅助索引访问索引外的其他字段时都需要进行二次查找。 而在MyISAM中,所有索引都是非聚簇索引,叶子节点存储着数据的地址,对于主键索引和普通索引在存储上没有区别。
为什么使用B+树作为存储数据的结构
即使红黑树增删改查性能十分优秀，红黑树本质还是二叉树，在数据量非常大时，需要访问+判断的节点数还是会比较多，同时数据是存在磁盘上的，访问需要进行磁盘IO，导致效率较低，而B+树是多叉的，可以有效减少磁盘IO次数。
B+树相对B树的好处：
B+树内节点不存储数据，所有 data 存储在叶节点导致查询时间复杂度固定为 log n。而B-树查询时间复杂度不固定，与 key 在树中的位置有关，最好为O(1)。
B+树叶节点两两相连可大大增加区间访问性，可使用在范围查询等，而B-树每个节点 key 和 data 在一起，则无法区间查找。
B+树更适合外部存储。由于内节点无 data 域，每个节点能索引的范围更大更精确磁盘是分 block 的，一次磁盘 IO 会读取若干个 block，具体和操作系统有关，那么由于磁盘 IO 数据大小是固定的，在一次 IO 中，单个元素越小，量就越大。这就意味着B+树单次磁盘 IO 的信息量大于B-树
