mysql索引

发表于 | 分类于

索引

离散性越好，列的选折性就越好，越适合作为索引。离散性差的作为索引适得其反（例如性别）。数据量少也不适合做索引

索引分类

按逻辑

  1. 普通索引 : 没有任何约束，主要用于提高查询效率
  2. 唯一索引 : 普通索引 + 数据唯一性约束, 一个表可有多个
  3. 主键索引 : 唯一索引 + NOT NULL, 一个表只有一个
  4. 全文索引 : 主要是 ElasticSearch 等用的较多

按物理实现方式

  1. 聚集索引 : 找到了索引的位置，在它后面就是要找的数据行
  2. 非聚集索引 : 索引项是按照顺序存储的，索引项指向的内容是随机存储的.

聚集索引的叶子节点存储的就是我们的数据记录，非聚集索引的叶子节点存储的是数据位置。非聚集索引不会影响数据表的物理存储顺序。

一个表只能有一个聚集索引，因为只能有一种排序存储的方式，但可以有多个非聚集索引，也就是多个索引目录提供数据检索。

使用聚集索引的时候，数据的查询效率高，但如果对数据进行插入，删除，更新等操作，效率会比非聚集索引低。

按字段个数分类

联合索引：
  1. 联合索引 ： eg: create index idx_name_phoneNum on user(name,phoneNum,age) 示例（a,b,c）
  2. 单列索引 : 一种特殊的联合索引 create index idx_name on user(name)

联合索引 用到 范围匹配后面的索引 就会失效

对 索引中关键字的对比 ，从左往右 依次进行比较 abc < acd
所以使用 like %abc %在左边 不好，因为会查询整个 B+tree 最左匹配

索引原理

mysql B+树

正确创建合适的索引是数据库优化的基础

索引本质

索引为了 加速对表中数据行的检索 而创建的 一种分散存储的数据结构

在 RDBMS 系统中 数据的索引都是 硬盘级索引，只有一少部分在内存中

hash 索引 等值匹配非常快，但范围查询不可以。所以一般常用在 key-value 型数据库中

二叉树性结构存在问题

  1. 树的高度的太高,IO次数太多
  2. 页为单位，默认 4K MYSQL 默认 16K， 可以加载多个关键字，但一次加载关键字太少

B 树 （多路 平衡 树）

关键字的个数 = 路数 -1

关键字都有数据区,会多余读取

B + 树 （加强版 多路 平衡 树）

mysql 匹配过程 采用了 左闭合 的比较规则

读取的关键字 以及 子节点的引用

只有最后一层有数据区

IO能力， 排序能力， 扫表能力，查询效率稳定可靠