数据库学习总结

1.MySQL的存储引擎有哪些？都有什么特点？

InnoDB

• InnoDB是MySQL的默认事务型引擎，用来处理大量短期事务；
• InnoDB的数据存储在表空间中，表空间由一系列的数据文件组成；
• InnoDB存储引擎为MySQL表提供了ACID事务支持、系统崩溃修复能力和多版本并发控制（即MVCCMulti-Version Concurrency Control）的行级锁；
• InnoDB采用MVCC来支持高并发，并且实现了四个标准的隔离级别，默认级别是REPEATABLEREAD（可重复读），通过间隙锁防止幻读的出现；
• InnoDB基于聚簇索引建立，对主键查询有很高的性能，二级索引中必须包含主键列，表上索引较多，主键应尽可能小；
• InnoDB的优化：磁盘读取数据时采用可预测性预读，自动在内存中创建hash索引以加速读操作的自适应哈希索引；

MyISAM

• MyISAM提供了大量的特性：全文索引、压缩、空间函数（GIS）；
• 但不支持事务和行级锁，崩溃后无法安全恢复；
• MyISAM将表存储在两个文件中：数据文件和索引文件
• MyISAM对整张表加锁，而不是针对行；读取时对需要读到的所有表加共享锁，写入时则对表加排它锁；
• MyISAM强调了快速读取操作，主要用于高负载的select

2.索引的种类有哪些？

B-Tree索引

哈希索引

空间数据索引（R-Tree）：MyISAM表支持空间索引，用做地理数据存储。

全文索引

3.索引的数据结构及算法理论实现是怎样的？

索引（Index）是帮助MySQL高效获取数据的数据结构。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

B-Tree

• 为了描述B-Tree，首先定义一条数据记录为一个二元组[key, data]，key为记录的键值，对于不同数据记录，key是互不相同的；data为数据记录除key外的数据。那么B-Tree是满足下列条件的数据结构：
• d为大于1的一个正整数，称为B-Tree的度。
• h为一个正整数，称为B-Tree的高度。
• 每个非叶子节点由n-1个key和n个指针组成，其中d<=n<=2d。
• 每个叶子节点最少包含一个key和两个指针，最多包含2d-1个key和2d个指针，叶节点的指针均为null。
• 所有叶节点具有相同的深度，等于树高h。
• key和指针互相间隔，节点两端是指针。
• 一个节点中的key从左到右非递减排列。

B+Tree

• B-Tree有许多变种，其中最常见的是B+Tree，例如MySQL就普遍使用B+Tree实现其索引结构，与B-Tree相比，B+Tree有以下不同点：
• 每个节点的指针上限为2d而不是2d+1。
• 内节点不存储data，只存储key；叶子节点不存储指针。

4.为什么使用B-Tree（B+Tree）而不是红黑树等数据结构？

红黑树等数据结构也可以用来实现索引，但是文件系统及数据库系统普遍采用B-/+Tree作为索引结构。

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

5.B-/+Tree索引的性能分析

• 一个node只需一次I/O。B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存），渐进复杂度为O(h)=O(logdN)。
• 一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小（通常不超过3）。
• B+Tree更适合外存索引，原因和内节点出度d有关。从上面分析可以看到，d越大索引的性能越好，而出度的上限取决于节点内key和data的大小；

6.不同引擎对于MySQL索引的实现分别是怎样的？

MyISAM索引实现

• MyISAM引擎使用B+Tree作为索引结构；
• 叶节点的data域存放的是数据记录的地址，也即是说MyISAM的索引文件仅仅保存数据记录的地址，即索引文件和数据文件是分离的；
• 在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。
• 因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录；

• MyISAM的索引方式也叫做“非聚集索引”；

InnoDB索引实现

InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

• 第一个重大区别是InnoDB的数据文件本身就是索引文件。在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。
• 第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。
• 了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助：
• 例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。
• 再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

7.索引使用的策略及优化

MySQL的优化主要分为结构优化（Scheme optimization）和查询优化（Query optimization）。

InnoDB的主键选择与插入优化

在使用InnoDB存储引擎时，如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键。如果从数据库索引优化角度看，使用InnoDB引擎而不使用自增主键绝对是一个糟糕的主意。

InnoDB使用聚集索引，数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）。

如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。

如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。