随笔Mysql 索引原理（一）

最近在将自己平时在云笔记的一些东西挪到博客来

  • 索引优化，不得不从了解数据类型开始
  • 索引基础信息
  • 索引的本质
  • Mysql中索引的实现
  • 索引使用策略及优化
  • 索引选择性与前缀索引
  • InnoDB的主键选择与插入优化

索引优化，不得不从了解数据类型开始

  • 建议：越简单的数据类型需要的CPU周期就越少，使用Mysql设定的类型，通常比什么数据都使用varchar数据比较快。
  • 建议：尽量避免NULL，Mysql难以优化引用可空列的查询，会是索引，索引统计和值更加复杂。（因性能优化提升很小，因此除非它引出了问题，否则可不当做首先优化措施）
  • 字符类型，使用varchar和char，区别在可以前者为可变长度，后者为固定长度
    使用varchar时，列（varhcar（50））越短使用的内存则会越小，对排序或者基于内存的临时表开销会更小。
  • lob和Text类型：
  • 日期和时间类型,
  • datatime: 保存时间范围大，精度为秒，与时区无关，使用8字节存储
  • timestamp: 精度为秒，依赖时区，使用4字节存储，
  • 。。。。等等

索引基础信息

  • 数据结构-B树：一种平衡的多路查找树，具有属性：
  • 如果根节点不是叶结点，则至少需要两个子树
  • 每个非根的分支节点，都有k-1个元素和k个子分支节点
  • 所有叶结点都位于同一层次
  • B+数和普通B树的差异：
  • 有N颗子树的节点中包含N个关键字（）
  • 所有叶子节点包含所有关键字，及向含这些关键字数据的指针，叶子节点本身以关键字的大小自小到大顺序链接。
  • 所有分支节点可看成索引，节点中仅含有其子树最大或最小的关键字。
  • 索引的本质
  • 索引（Index）是帮助MySQL高效获取数据的数据结构

Mysql中索引的实现

  • InnoDB索引实现
  • 在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。
  • InnoDB的所有辅助索引都引用主键作为data域，而不是地址。
  • 聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。
  • 不建议：使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。
  • 建议：非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。
  • MyISAM索引实现
    ！待补！

索引使用策略及优化

  • 结构优化（Scheme optimization）
  • 查询优化（Query optimization

索引选择性与前缀索引

  • 不必建索引：
  • 表记录比较少，例如一两千条甚至只有几百条记录的表，没必要建索引，让查询做全表扫描就好了。
  • 索引的选择性较低。所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值：Index Selectivity = Cardinality / #
  • 使用前缀索引
  • 概念：前缀索引，是指将用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。
  • 缺点：不能用于ORDER BY和GROUP BY操作。也不能用于Covering index

InnoDB的主键选择与插入优化

  • 在使用InnoDB存储引擎时，如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键。
  • （待解决）排序（ORDER BY）相关的索引优化及覆盖索引（Covering index）