MySQL数据库原理
MySQL
针对MySQL总结其基本原理、应用方法
MySQL数据库体系结构
索引
-
B+树原理——索引底层数据结构(搜索树 -> 红黑树 -> B树 -> B+树)
- 搜索树 + 平衡树 (B树)
- 数据全部存在底层叶子节点，其它节点只保留索引值
- 每个节点指向叶子的点的指针都指向该范围内的最小值
- 叶子节点间通过指针链接构成链表以适应范围查找，Key值从左到右递增排序
-
查找操作
- 利用二叉搜索树的特点找到对应目标索引的叶子节点
- 在叶子节点的范围值中进行二分查找
- 将查找到的数据返回，或者根据找到的索引值和主键回表取数据返回
-
插入、删除操作
- 为了维护B+树的平衡性，需要在操作之后进行分裂、合并、旋转等操作
-
与红黑树的区别——B+ 树访问磁盘数据有更高的性能
-
B+ 树有更低的树高
平衡树的树高 O(h)=O(logdN)，其中 d 为每个节点的出度。红黑树的出度为 2，而 B+ Tree 的出度一般都非常大，所以红黑树的树高 h 很明显比 B+ Tree 大非常多。 -
磁盘访问原理
操作系统一般将内存和磁盘分割成固定大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能完全载入一个节点。
如果数据不在同一个磁盘块上，那么通常需要移动制动手臂进行寻道，而制动手臂因为其物理结构导致了移动效率低下，从而增加磁盘数据读取时间。B+ 树相对于红黑树有更低的树高，进行寻道的次数与树高成正比，在同一个磁盘块上进行访问只需要很短的磁盘旋转时间，所以 B+ 树更适合磁盘数据的读取。 -
磁盘预读特性
为了减少磁盘 I/O 操作，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，并且只需要很短的磁盘旋转时间，速度会非常快。并且可以利用预读特性，相邻的节点也能够被预先载入。
-
-
MySQL索引分类
-
索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。
-
B+树索引——多数MySQL数据库存储引擎的默认索引类型
- 利用二叉搜索树，查找速度很快，不需要全表扫描
- 支持范围查找、排序、分组
- 支持多个字段建立的联合索引，遵循最左原则
- InnoDB下索引分为: 聚簇/主键索引、非聚簇/辅助索引，但完整表记录只存放在主键索引的叶子节点中，辅助索引的叶子节点只存放索引字段和对应的主键值
-
哈希索引
- 以O(1)的时间复杂度查找
- 数据不具备有序性，不支持排序、分组，只支持精确查找，无法用于部分查找和范围查找
- InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。
-
全文索引——MyISAM存储引擎
-
空间数据索引——MyISAM存储引擎(R-Tree)
-
-
索引优化
-
独立的列
在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引。 -
多列索引
在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好 -
索引列的顺序
让选择性最强的索引列放在前面。
索引的选择性是指：不重复的索引值和记录总数的比值。最大值为 1，此时每个记录都有唯一的索引与其对应。选择性越高，每个记录的区分度越高，查询效率也越高。 -
前缀索引
对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符。
前缀长度的选取需要根据索引选择性来确定。 -
覆盖索引
索引包含所有需要查询的字段的值。
具有以下优点:- 索引通常远小于数据行的大小，只读取索引能大大减少数据访问量。
- 一些存储引擎（例如 MyISAM）在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用（通常比较费时）。
- 对于 InnoDB 引擎，若辅助索引能够覆盖查询，则无需访问主索引。
-
-
索引的优点
- 大大减少了服务器需要扫描的数据行数。
- 帮助服务器避免进行排序和分组，以及避免创建临时表（B+Tree 索引是有序的，可以用于 ORDER BY 和 GROUP BY 操作。临时表主要是在排序和分组过程中创建，不需要排序和分组，也就不需要创建临时表）。
- 将随机 I/O 变为顺序 I/O（B+Tree 索引是有序的，会将相邻的数据都存储在一起）。
-
索引的使用条件
- 小型表: 大部分情况下简单的全表扫描比建立索引更高效
- 中到大型表: 索引就非常有效
- 特大型表: 建立和维护索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配，例如可以使用分区技术。
