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 索引是有序的，会将相邻的数据都存储在一起）。
索引的使用条件

小型表: 大部分情况下简单的全表扫描比建立索引更高效
中到大型表: 索引就非常有效
特大型表: 建立和维护索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配，例如可以使用分区技术。
有相同爱好的可以进来一起讨论哦：企鹅群号：1046795523

学习视频资料：http://www.makeru.com.cn/live/1392_1164.html?s=143793