MySQL数据库原理 收藏避免迷路

欧清辣哨

MySQL

[backcolor=rgba(255, 255, 255, 0.5)]针对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
    
  
  

三叶草

来错板块了吧