数据库学习笔记_数据库出度d 100-CSDN博客

本文链接：https://blog.csdn.net/yutingsun/article/details/100017620

本文详细介绍了数据库的事务特性，包括ACID四大特性及四种事务隔离级别，解释了读未提交、读已提交、重复读和序列化的区别。此外，讨论了封锁协议和不同级别的锁，以及数据库范式对数据完整性的贡献。还提到了数据库存储引擎MyISAM与InnoDB的差异，重点讨论了索引的作用、类型和优化策略，包括主键索引、唯一索引、普通索引、全文索引和组合索引，并分析了何时创建和避免创建索引。最后，文章提及了数据库优化的四大方面，如SQL语句优化、索引优化和数据库表结构优化。

摘要由CSDN通过智能技术生成

https://blog.csdn.net/snowbaby1234/article/details/81238760（参考面试题目总结）

一、事务的四大特性：A（原子性）、C（一致性）、I（隔离性）、D（持久性）。其中，隔离性有四种类型的事务隔离级别：https://blog.csdn.net/snowbaby1234/article/details/81238760

1、Read Uncommitted（读未提交不提交的读）即一个事务可以读取另一个未提交事务的数据（脏读，即看到了不应该看到的数据，解决方式为读提交）。

2、Read Committed（读提交提交的读）即一个事务要等另一个事务提交后才能读取数据（不可重复读，一个事务范围内两个相同的查询返回了不同数据，解决方式为重复读）。

3、Repeatable read（重复读可重复的读）即开始读取数据（事务开启）时，不再允许修改操作。即在同一个事务中保证多次读取的数据结果是一样的。（不可重复读对应的是修改，即UPDATE操作，重复读能解决此问题，但是可能出现幻读问题无法解决，因为幻读对应的是INSERT操作，则需要最高级别的序列化来解决问题）。

4、Serializable序列化是最高级别的事务隔离，在此级别下，事务串行化顺序执行，可以避免脏读、不可重复读、幻读，但是此级别效率低下，比较耗费数据库性能，一般不使用。。大多数数据库默认的事务隔离级别是Read committed，例如Sql Server ，Oracle。但是MySql的默认隔离级别是Repeatable read。

二、锁：悲观锁和乐观锁

封锁协议：一级：事务T在修改数据R之前必须先对其加X锁，直到事务结束才释放，解决丢失修改问题；二级：在一级的基础上增加T在读取R之前必须对其加S锁，读完后即可释放S锁，解决读脏数据问题；三级：在一级的基础上增加T在读取R之前必须对其加S锁，直到事务结束才释放，解决不可重复读的问题。

三、数据库范式（1NF、2NF、3NF、BCNF、4NF）：解决丢失修改、读脏数据、不可重复读、幻读的问题。

1NF是对属性的原子性，要求属性具有原子性，不可再分解；2NF是对记录的惟一性，要求记录有惟一标识，即实体的惟一性，即不存在部分依赖（表能表现出它的含义，即有唯一标识的含义）；3NF是对字段的冗余性，要求任何字段不能由其他字段派生出来，它要求字段没有冗余，即不存在传递依赖；

四、内连接，外连接，交叉连接。

五、MySQL存储引擎：MyISAM和InnoDB https://blog.csdn.net/yjclsx/article/details/81911027 https://blog.csdn.net/xiaomingdetianxia/article/details/72475924

MySQL默认存储引擎是：“InnoDB”，它是事务型的，支持事务处理，支持外键，支持并发控制，其包含行级锁和表级锁，并且崩溃修复速度比MyISAM引擎快。如果需要对事务的完整性要求比较高（银行），或者要求实现并发控制（售票），则应该选择InnoDB，并且如果需要频繁的更新、删除操作数据库，则也可以选择InnoDB（UPDATE,DELETE），因为其支持事务的提交和回滚。 |||||||||||||||||||||||| 但是如果需要快速的查询数据记录，则应该使用MyISAM存储引擎（INSERT，SELECT），其提供高速存储和检索，以及全文搜索能力，它强调的是性能。

每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。 .frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。基于磁盘的资源是InnoDB表空间数据文件和它的日志文件，InnoDB 表的大小只受限于操作系统文件的大小，一般为 2GB。|||| 存储引擎内部的结构如链接中内容

可移植性、备份及恢复

MyISAM：数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。
InnoDB：免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。

数据库存储引擎是数据库底层软件组织，DBMS使用数据引擎进行创建、查询、更新、删除数据。~~但是怎么实现索引查找，的呢？？？？？？？如 https://cloud.tencent.com/developer/article/1125452~~~~（细节：索引的应用）解释。~~

六、索引 https://blog.csdn.net/liangkaiping0525/article/details/82284236 （主要：索引） https://blog.csdn.net/u012954706/article/details/81241049（索引新手入门） https://cloud.tencent.com/developer/article/1125452（细节：索引的应用） https://blog.csdn.net/qq_35008624/article/details/81947773（B+树、B-树的区别等）

索引的主要作用是优化数据库查询，提高数据库查询性能。

索引的种类：

Mysql常见索引类别有：主键索引、唯一索引、普通索引、全文索引、组合索引

PRIMARY KEY（主键索引）  ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
UNIQUE(唯一索引)     ALTER TABLE `table_name` ADD UNIQUE (`column`)
INDEX(普通索引)     ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
FULLTEXT(全文索引)      ALTER TABLE `table_name` ADD FULLTEXT ( `column` )
组合索引   ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

Mysql各种索引区别：
普通索引：最基本的索引，没有任何限制
唯一索引：与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。
主键索引：它是一种特殊的唯一索引，不允许有空值。
全文索引：仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间。
组合索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。

最左前缀原则：顾名思义，最左优先，比如，我们建立了一个以（a,b,c）为组合的索引，那么将会得到：a, ab,abc三种索引。

若我们按列“b”进行查找，或者按列（bc）查找，都不会使用到索引，只有以上三种索引可以使用。

适合创建索引的情况：

1、表的主键、外键必须有索引；外键是唯一的，而且经常会用来查询
2、数据量超过300的表应该有索引；
3、经常与其他表进行连接的表，在连接字段上应该建立索引；经常连接查询，需要有索引
4、经常出现在Where子句中的字段，加快判断速度，特别是大表的字段，应该建立索引，建立索引，一般用在select ……where f1 and f2 ，我们在f1或者f2上建立索引是没用的。只有两个使用联合索引才能有用
5、经常用到排序的列上，因为索引已经排序。
6、经常用在范围内搜索的列上创建索引，因为索引已经排序了，其指定的范围是连续的。

不适合创建索引的情况：

第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
第二，对于那些只有很少数据值的列也不应该增加索引。因为本来结果集合就是相当于全表查询了，所以没有必要。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。
第五，不会出现在where条件中的字段不该建立索引。

索引无效的情况：

1、如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因，要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引）
2、对于多列索引，不是使用的第一部分，则不会使用索引（没有遵循最左前缀原则）
3、like查询是以%开头，对于使用 like 的查询,后面如果是常量并且只有%号不在第一个字符,索引才可能会被使用
4、如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
5、如果mysql估计使用全表扫描要比使用索引快,则不使用索引6、使用函数7、排序条件为索引，则select字段必须也是索引字段，否则无法命中

索引的底层数据结构：在InnoDB、MyISAM存储引擎中使用B+树的引擎结构。主要是基于Hash表和B+树。

BTree索引是最常用的mysql数据库索引算法，因为它不仅可以被用在=,>,>=,<,<=和between这些比较操作符上，而且还可以用于like操作符，只要它的查询条件是一个不以通配符开头的常量；如果一通配符开头，或者没有使用常量，则不会使用索引。
Hash索引只能用于对等比较，例如=,<=>（相当于=）操作符。由于是一次定位数据，不像BTree索引需要从根节点到枝节点，最后才能访问到页节点这样多次IO访问，所以检索效率远高于BTree索引。

B+树的结构，为什么不使用B-树、红黑树、二叉搜索树？

B+树对比B-树有如下好处：

io次数少：b+树中间节点只存索引，不存在实际的数据，所以可以存储更多的数据。索引树更加的矮胖，io次数更少。
性能稳定：b+树数据只存在于叶子节点，查询性能稳定
范围查询简单：b+树不需要中序遍历，遍历链表即可。

先从B-Tree分析，根据B-Tree的定义，可知检索一次最多需要访问h个节点

数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入

为了达到这个目的，在实际实现B-Tree还需要使用如下技巧：

每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O

B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存），渐进复杂度为O(h)=O(logdN)。

一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小（通常不超过3）

综上所述，用B-Tree作为索引结构效率是非常高的。

而红黑树，h明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，所以红黑树的I/O渐进复杂度也为O(h)，效率明显比B-Tree差很多

上文还说过，B+Tree更适合外存索引，原因和内节点出度d有关。从上面分析可以看到，d越大索引的性能越好，而出度的上限取决于节点内key和data的大小：

dmax=floor(pagesize/(keysize+datasize+pointsize))

floor表示向下取整。由于B+Tree内节点去掉了data域，因此可以拥有更大的出度，拥有更好的性能。

红黑树也可用来实现索引，但是文件系统及数据库系统普遍采用B/+Tree,何也?

一般来说，索引本身也很大，不可能全存内存，往往以索引文件的形式存在磁盘。索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数

聚集索引和非聚集索引：

MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，data域保存数据记录的地址。然后以data域的值为地址，读取相应数据记录。因为MyISAM存储结构中，索引结构和数据是分别单独存放在两个文件中，索引文件仅保存数据记录的地址。所以在这种存储结构中，一个表可以有多个索引。MyISAM的索引方式也叫做“非聚集”的索引。

nnoDB的数据文件本身就是索引文件，所以一张表中只有一个索引，在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。另一个与MyISAM索引的不同是InnoDB的辅索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。