索引的原理
1. 在数据之外, 数据库系统还维护着满足特定查找算法的数据结构, 这些数据结构以某种方式引用 (指向) 数据, 这样就可以在这些数据结构上实现高级查找算法. 这种
数据结构, 就是索引.
2. 在数据之外, 数据库系统还维护着满足特定查找算法的数据结构, 这些数据结构以某种方式引用 (指向) 数据, 这样就可以在这些数据结构上实现高级查找算法. 这种
数据结构, 就是索引.
添加索引的时机
1. 较频繁的作为查询条件的字段应该创建索引 (where 子句中出现的列, 在 join 子句中出现的列)
2. 唯一性太差的字段, 不适合单独创建索引, 即使频繁作为查询条件, 即索引列的基数越大 (重复越少), 索引的效果越好
3. 更新非常频繁的字段不适合创建索引
4. 使用短索引, 如果对字符串列进行索引, 应该指定一个前缀长度, 可节省大量索引空间, 提升查询速度
5. 添加索引的 SQL : CREATE INDEX index_name ON table_name (column_list)
常见的使用索引和不使用索引的 sql 语句
1. MySQL 只对以下操作符使用索引: <, <=, =,>,>=, between, in 和某时的 like(不以 % 或_开头) , 具体如下:
- 2. SELECT sname FROM stu WHERE age+10=30; // 不会使用索引, 因为所有索引列参与了计算
- 3. SELECT sname FROM stu WHERE LEFT(date,4) <1990; // 不会使用索引, 因为使用了函数运算, 原理与上面相同
- 4. SELECT * FROM houdunwang WHERE uname LIKE'后盾 %' // 走索引
- 5. SELECT * FROM houdunwang WHERE uname LIKE "% 后盾 %" // 不走索引
- // 正则表达式不使用索引, 字符串与数字比较也不使用索引, 如下
- CREATE TABLE a (a char(10));
- EXPLAIN SELECT * FROM a WHERE a="1" // 走索引
- EXPLAIN SELECT * FROM a WHERE a=1 // 不走索引
- // 如果 sql 有 or, 即使有条件带索引也不会用. or 要求所有字段都必须建立索引, 故应尽量避免使用 or 关键字
- select * from dept where dname='xxx' or loc='xx' or deptno=45
最左前缀原则
1. 最左前缀原则指的是在 sql where 子句中的查询条件精确匹配组合索引的左边连续一个或几个列时才能正确使用索引, 只要非顺序出现, 断层都无法用到多列索引.
2. 最左前缀匹配原则, 非常重要的原则, mysql 会一直向右匹配直到遇到范围查询 (>,<,between,like) 就停止匹配, 比如 a = 1 and b = 2 and c> 3 and d = 4 如果建立
(a,b,c,d) 顺序的索引, d 是用不到索引的, 如果建立 (a,b,d,c) 的索引则都可以用到, a,b,d 的顺序可以任意调整.
3. = 和 in 可以乱序, 比如 a = 1 and b = 2 and c = 3 建立 (a,b,c) 索引可以任意顺序任意长度 (只要 a 存在),mysql 的查询优化器会帮你优化成索引可以识别的形式.
数据库实现的索引方式
B-Tree
1. 是一种多路搜索树 (并不是二叉的)
2. 每个 k 对应一个 data, 存储空间较大
3. B - 树的搜索, 从根节点开始, 对节点内的关键字 (有序) 序列进行二分查找, 如果命中则结束, 否则进入查询关键字所属范围的子节点; 重复, 直到所对应的子指针为空, 或者已经是叶子节点;
4. B - 树的特性:
关键字集合分布在整颗树中;
任何一个关键字出现且只出现在一个节点中;
搜索有可能在非叶子结点结束;
其搜索性能等价于在关键字全集内做一次二分查找;
自动层次控制;
B+Tree
1. MySQL 就普遍使用 B+Tree 实现其索引结构.
2. B+Tree 与 B-Tree 的不同
为所有叶子节点增加一个链指针 (提高区间查找的效率);
所有关键字都在叶子节点出现, B + 树只有达到叶子节点才命中
内节点不存储 data, 只存储 key
3. B + 树的特性:
所有关键字都出现在叶子节点的链表中 (稠密索引), 且链表中的关键字恰好是有序的;
不可能在非叶子节点命中;
非叶子节点相当于是叶子节点的索引 (稀疏索引), 叶子节点相当于是存储 (关键字) 数据的数据层;
更适合文件索引系统;
索引的物理存储
一般来说, 索引本身也很大, 不可能全部存储在内存中, 因此索引往往以索引文件的形式存储的磁盘上. 这样的话, 索引查找过程中就要产生磁盘 I/O 消耗, 相
于内存存取, I/O 存取的消耗要高几个数量级, 所以评价一个数据结构作为索引的优劣最重要的指标索引的结构组织要尽量减少查找过程中磁盘 I/O 的存取次数.
B+Tree 优于 B-Tree , 主要原因有以下三个方面:
B+tree 的磁盘读写代价更低: B+tree 的内部节点并没有指向关键字具体信息的指针. 因此其内部节点相对 B 树更小. 那么一个盘快中所能容纳的关键字也就
越多
B+tree 的查询效率更加稳定: 由于非终节点并不是最终指向文件内容的节点, 而只是叶子节点中关键字的索引. 所以任何关键字的查找必须走一条从根节点
到叶子节点的路.
B + 树只要遍历叶子节点就可以实现整棵树的遍历. 而且在数据库中基于范围的查询是非常频繁的, 而 B 树不支持这样的操作 (或者说效率太低)
索引的缺点
1. 创建索引和维护索引要耗费时间, 这种时间随着数据量的增加而增加
2. 索引需要占物理空间, 除了数据表占数据空间之外, 每一个索引还要占一定的物理空间, 如果要建立聚簇索引, 那么需要的空间就会更大
3. 当对表中的数据进行增加, 删除和修改的时候, 索引也要动态的维护, 降低了数据的维护速度
来源: http://www.bubuko.com/infodetail-2610298.html