索引基础理论知识
二分查找法:在有序数组中查找某一特定元素的搜索算法
优点:比较次数少,查找速度快,平均性能好
缺点:待查表为有序表,插入删除困难。所以适用于不常变动而查找频繁的有序列表
平衡树,平衡二叉树
特点:为一棵空树和左右两个子树的高度差的绝对值不超过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的一些总结