mysql索引

索引基础理论知识

二分查找法:在有序数组中查找某一特定元素的搜索算法

 优点:比较次数少,查找速度快,平均性能好

 缺点:待查表为有序表,插入删除困难。所以适用于不常变动而查找频繁的有序列表

平衡树,平衡二叉树

特点:为一棵空树和左右两个子树的高度差的绝对值不超过1,且左右两个子树也是平衡二叉树

 不平衡树辉通过自旋,变成平衡树

 平衡树和二叉查找树最大的区别:前者是平衡的,后者未必

B树,balanced tree

 一个结点可以拥有多于2个结点的多叉查找树

 适合大量数据的读写操作,普遍运用在数据库和文件系统

一棵m阶(如4阶)的B树满足下列条件:

 树中每个结点至多有m个(4个)子结点

 除根结点和叶子结点外,其它每个节点至少有m/2个子结点

 根结点至少有2个结点

 所有叶子结点都出现在同一层,叶子结点不包含任何关键字信息

 有k个子结点的非终端结点恰好包含k-1个关键字

B+树,B+tree

 其基本定义和B-树相同,除了:

 有n棵子树的结点中含有n-1个关键字,每个关键字不保存数据,只用来索引,所有数据都保存在叶子结点

 所有的叶子结点中包含了全部关键字的信息,及指向这些关键字记录的指针,且叶子结点本身依关键字的大小自小到大顺序链接

 所有非终端结点可以看成是索引部分,结点中仅含其子树(根结点)中最大(或最小)的关键字

B+树、哈希索引结构及区别

 大量不同数据等值精确查询,hash索引效率通常比B+tree高

 hash索引不支持模糊查找

 hash索引不支持联合索引中的最左匹配规则

 hash索引不支持排序

 hash索引不支持范围查询

索引优点:提高数据检索效率

 提高表空间的join效率

 利用唯一性索引,保证数据的唯一性

 提高排序和分组效率

缺点:消耗更多物理存储

 数据变更时,索引也需要更新,降低更新效率

索引使用建议:经常检索的列;经常用于表连接的列;经常排序/分组的列

索引不使用建议:基数很低的列,更新频繁检索不频繁的列;blob/text等长内容列;很少用于检索的列


常见索引

主键(primary key)

 主键由表中的一个或多个字段组成,它的值用于唯一地标识表中的某一条记录

 在表引用中,主键在一个表中引用来自于另一个表中的特定记录

 保证数据的完整性

 加快数据的操作速度

 最多只能有一个主键,无论什么引擎

 主键值不能重复,也不能包含NULL

innodb主键特点:

 索引定义时,若不显式包含主键,会隐式加入主键值;若显示包含主键,会加入主键值

 5.6.9之后,优化器可以自动识别索引末尾的主键值,在之前需要显式加上主键列才可以被识别

主键设计建议:

 对业务透明,无意义,免受业务变化的影响

 主键要很少修改和删除

 主键最好是自增的

 不要具有动态属性,如:最后修改时间戳

聚集索引

 该索引中键值的逻辑顺序决定了表数据行的物理顺序

 每张表只能建一个聚集索引,除了tokudb引擎

 innodb中,聚集索引即表,表即聚集索引,是IOT表

 myisam没有聚集索引的概念,是HOT(堆组织)表

聚集索引的优先选择列:

 含有大量非重复的列

 使用between,>,>=,<,<=返回一个范围值得列

 被连续(顺序)访问的列

 返回大量结果集的查询

 经常被使用join或group by子句的查询访问的列

不建议使用的聚集索引

 修改频繁的列

 唯一值很小的列

 新增内容太过离散随机的列


innodb聚集索引选择顺序原则:

 显示声明的主键

 第一个不包含null列的唯一索引列

 内置的rowid

唯一索引(unique key)

 不允许具有索引值相同的行,从而禁止重复的索引或键值

 在唯一约束上,和主键一样

 和主键不同的方面(1.唯一索引允许有null;一个表只能有一个主键,但可以有多个唯一索引;innodb表主键必须是聚集索引,但聚集索引可能不是主键;唯一索引约束可临时禁用,但主键不行)

 注意:添加唯一索引后还有一种特殊情况,那就是如果该字段没有限制非空的话,存在插入NULL值的情况,此时,唯一索引并不起作用,也就是你可以插入n条该字段为null的数据。除此之外,如果插入空字符串的话, 例如 ‘’ ,‘ ’ 不管中间是多少个空字符串在插入的时候都算作‘’ ,即,空串不论多长,只能插入一条。

联合索引(multiple-column indexes)

 多列组成

 适合where条件中的多列组合

 有时可以避免回表(执行计划,先索引扫描,再通过rowid去取索引中未能提供的数据)

 mysql还不支持多列不同排序规则

覆盖索引(covering indexes)

 通过索引数据结构,即可直接返回数据,不需要回表

 执行计划中,显示关键字using index

部分索引/前缀索引(prefix indexes)

 部分索引的原因:1.char/varchar太长,全部作为索引,效率太差,存在浪费;2.blob/text类型不能整列作为索引列

 alter table t add index (c1(20))

 部分索引选择建议:统计平均值;2/8原则

外键/约束(foreign key constraints)

 确保存储在外键表中数据的一致性,完整性

 外键前提:本表列需与外键列类型相同(外键需时外表的主键)

 外键的选择原则:1.为关联字段创建外键;2.所有的键都必须唯一;3.避免使用复合键;4.外键总是关联唯一的键字段

全文索引(fulltext)

 5.6之前,只支持myisam。之后,开始支持innodb

 优先使用shpinx/lucene/solor等实现中文检索

mysql索引类型

逻辑分类:

 单列索引 (single column indexes)

 多列索引(combined indexes)

 唯一索引(unique)

 非唯一索引(nonunique)

物理存储分类:

 聚集索引(clustered index)

 非聚集索引(not-clustered index)

数据结构:

 Btree索引

 hash索引,只用于heap表

 空间索引,使用较少

 fractal tree索引,用于tokudb表


译者介绍:家华,从事mysqlDBA的工作,记录自己对mysql的一些总结
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值