索引

什么是索引

• 帮助 MySQL 高效获取数据的一种数据结构
• 索引就是一种数据结构
• 类似新华字典的索引目录, 可以通过索引目录快速的查到你想要的字

• 排好序的快速查找数据这就是索引

为什么要建立索引

• 提高查询效率，没有排序之前是一个一个往后找的，通过索引进行排序之后，可以直接定义到想要的位置
• 排好序，快速查找数据的一种结构，就是索引

优势

• 索引类似大学图书馆里面建立的书目索引，提高数据检索的效率，降低数据库的 IO 成本
• 通过索引对数据项进行排序，降低数据排序成本, 降低了 CPU 的消耗

劣势

• 一般来说, 索引本身也很大, 索引往往以文件的形式存储到磁盘上
• 索引也是一张表, 该表保存了主键与索引字段, 并指向实体表的记录, 所以索引也是要占磁盘空间的
• 虽然索引提高了查询速度, 但是会降低更新表的速度
• 因为更新表时, MySQL 不仅要保存数据, 还要保存一下索引文件每次更新添加了数据索引文件中需要调整，其它的索引需要调整位置等等...
• 在更新的时候不仅仅需要维护索引还需要维护其它的索引位置，还有真实的表中的数据

索引分类

单值索引

• 一个索引只包含单个列, 一个表可以有多个单值索引
• 一般来说, 一个表建立的索引不要超过 5 个

唯一索引

• 索引列的值必须唯一, 但允许有空值

复合索引

• 一个索引包含多个列组成

全文索引

• MySQL 全文检索是利用查询关键字和查询列内容之间的相关度进行检索，可以利用全文索引来提高匹配的速度

索引为什么能快速的查找数据

• 在我们存数据时, 如果建立索引，数据库系统会维护一个满足特定查找算法的数据结构，这些数据结构以某种方式来引用数据
• 可以在这些数据结构之上, 实现高级查找算法, 这种结构就是索引
• 一般来说, 索引本身也很大, 不可能全部存储在内存中, 因此索引往往以索引文件的形式存储在磁盘上
• 为了加快数据的查找, 可以维护二叉查找树, 每个节点分别包含索引键值和一个指向对应数据记录的物理地址的指针
• 这样就可以运用二叉查找在一定的复杂度内获取相应的数据, 从而快速的检索出符合条件的记录
• 除了二叉树还有 BTtree 索引
• 我平时所说的索引, 如果没有特别指定, 都是指 B 树结构组织的索引
• 其中聚焦索引, 次要索引, 复合索引, 前缀索引, 唯一索引默认都是 B+树索引
• 除 B+树索引之外, 还有哈希索引(Hash index)等

二叉查找树

特性

• 左子树的键值小于根的键值
• 右子树的键值大于根的键值

B-Tree

• 平衡多路查找树

特性

• m 阶 B-Tree 满足以下条件
• 根节点至少包括两个孩子
• 树中的每个节点最多有 m 个孩子(m >= 2)
• 除了根节点和叶子节点外，其它每个节点至少有 Ceil(m / 2)个孩子
• 所有叶子节点都在同一层
• ki(1 = 1 …n)为关键字, 且关键字按顺序升序排列k(i - 1) < k 8 < 9
• 关键字的个数 n 满足：ceil(m / 2) - 1 <= n <= m -1(非叶子节点关键字的个数比指向孩子的指针少1个)
• 非叶子节点的指针p[1], p[2], …p[m]其中 p1 指向关键字小于k[1]的子树 3 < 8
• p[m]指针关键字大于k[m - 1]的子树 15 > 12
• p[i]指向关键字属于(k[i - 1], k[i])的子树 9, 10 是位于 8 和 12之间

B+Tree

B+树是B树的变体, 基本与 B-Tree 相同

不同点

• 非叶子节点的子树指针与关键字的个数相同
• 非叶子节点的子树指针, 指向关键字值[k[i], k[i + 1]]的子树(10 < 18 < 20)
• 非叶子节点仅用来做索引, 数据都保存在叶子节点中
• 所有的叶子节点均有一个链指针指向下一个叶子节点
• 链接起来, 能够方便的我们在直接在叶子节点做范围统计
• 而不是再回到子节点中
• 一旦定位到某个叶子节点, 便可以从该叶子节点横向的去跨子树去做统计

采用 B+Tree 做为主流索的数据结构的原因

• 更适合用来做存储索引
• B+树的磁盘读写代价更低
• 内部的结构并没有指向关键字的具体指针
• 不存放数据, 只存放索引信息
• 内部节点相对 B树更小
• B+树的查询效率更加稳定
• 内部节点并不是最终指向文件内容的节点, 只是叶子节点中关键字的索引
• 所以它任何关键字的查找, 必须走一条从根节点到叶子节点的路
• 所有关键字查询的长度相同, 导致每一个数据查询的效率也几乎是相同
• B+树更有利于对数据库的扫描
• B树在提高 IO 性能的同时, 并没有解决元素遍历效率低下的问题
• B+树只需要遍历叶子节点, 就可以解决对全部关键字信息的扫描
• 对数据库中, 频繁使用的范围查询, 性能更高

基本语法

创建索引

create [UNIQUE] index 索引名称 ON 表名(字段(长度))

ALTER 表名 add [UNIQUE] index[索引名称] ON(字段(长度))

示例如下

CREATE INDEX fk_emp_det_index ON employee(dep_id);

查看索引

SHOW INDEX FROM 表名

示例如下

SHOW INDEX FROM employee;

删除索引

DROP INDEX[索引名称] ON 表名;

示例如下

DROP INDEX fk_emp_det_index ON employee;

更改索引

添加一个主键, 索引必须是唯一索引, 值不能为 NULL

ALTER TABLE tab_name ADD primary KEY(column_list);

创建的索引是唯一索引, 可以为 NULL

ALTER TABLE tab_name ADD UNIQUE index_name(column_list);

普通索引, 索引值可出现多次

ALTER TABLE tab_name ADD INDEX index_name(column_list);

全文索引

ALTER TABLE tab_name ADD FULLTEXT index_name(column_list);

如下将给出一个建立的示例其它的都是照葫芦画瓢了

ALTER TABLE employee ADD primary KEY(id);

索引建立的选择

适合建立索引

1. 主键自动建立唯一索引，primary key
2. 频繁作为查询条件的字段应该创建索引，比如银行系统，银行帐号，电信系统的手机号
3. 查询中与其它表关联的字段, 外键关系建立索引，比如员工与部门的外键
4. 频繁更新的字段不适合建立索引，每次更新不单单要更新数据, 还要更新索引
5. where 条件里用不到的字段不建立索引
6. 查询中排序的字段, 排序的字段若通过索引去访问将大幅度的提升排序的速度，索引能够提高检索的速度和排序的速度
7. 查询中统计或分组的字段，分组的前提是必须排序

不适合建立索引

1. 记录比较少
2. 经常增删改的表, 虽然索引提高了查询的速度，同时却会降低更新表的速度, 如果对表的 INSERT, UPDATE 和 DELETE 比较多不建议建立
3. 因为建立索引后, 更新表时, MySQL 不仅要保存数据, 还要保存一下索引文件

数据重复的表字段

1. 如果某个数据列包含了许多重复的内容, 为它建立索引，就没有太大在的实际效果
2. 比如表中的某一个字段为国籍，性别
3. 数据的差异率和重复率不高，这种建立索引就没有太多意义