什么是索引
帮助 Mysql 高效获取数据的数据结构,索引就是数据结构,类似新华字典的索引目录,可以通过索引目录快速查到你想要的字,排好序的快速查找数据
为什么要建立索引
提高查询效率,没有排序之前一个一个往后找,通过索引进行排序之后,可以直接定义到想要的位置,排好序的快速查找数据结构 –> 就是索引
优势
索引类似大学图书馆建立的书目索引,提高数据检索的效率,降低数据库的 IO 成本,通过索引对数据项进行排序,降低数据排序成本,降低了 CPU 的消耗
劣势
一般来说, 索引本身也很大, 索引往往以文件的形式存储到磁盘上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录.所以索引也是要占磁盘空间的,虽然索引提高了查询速度,但是会降低更新表的速度,因为更新表时, MYSQL 不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,会调整因为更新所带来的键值变化后索引的信息
索引分类
单值索引
一个索引只包含单个列,一个表可以有多个单值索引,一般来说, 一个表建立索引不要超过 5 个
唯一索引
索引列的值必须唯一,但允许有空值
复合索引
一个索引包含多个列
全文索引
MySQL 全文检索是利用查询关键字和查询列内容之间的相关度进行检索，可以利用全文索引来提高匹配的速度。
索引为什么能快速查找数据
概述
在我们存数据时, 如果建立索引,数据库系统会维护一个满足特定查找算法的数据结构,这些数据结构以某种方式引用数据,可以在这些数据结构之上,实现高级查找算法,这种结构就是索引,一般来说, 索引本身也很大, 不可能全部存储在内存中, 因此索引往往以索引文件的形式存储在磁盘上,为了加快数据的查找,可以维护二叉查找树, 每个节点分别包含索引键值和一个指向对应数据记录的物理地址的指针,这样就可以运用二叉查找在一定的复杂度内获取相应的数据,从而快速的检索出符合条件的记录,除了二叉树还有 BTtree 索引,我平时所说的索引,如果没有特别指定, 都是指 B树 结构组织的索引,其中聚焦索引,次要索引,复合索引,前缀索引,唯一默认都是B+树索引,除 B+树索引 之外, 还有 哈希索引(Hash index) 等。
二叉查找树
特性
左子树的键值小于根的键值，右子树的键值大于根的键值
B-Tree
平衡多路查找树
特性
m阶B-Tree满足以下条件
0.根节点至少包括两个孩子
1.树中每个节点最多有m个孩子(m>=2)
2.除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子。
3.所有叶子节点都在同一层
4.ki(1=1…n)为关键字,且关键字按顺序升序排列k(i-1) < k 8 < 9
5.关键字的个数n满足：ceil(m/2)-1 <= n <= m-1 (非叶子节点关键字个数比指向孩子的指针少1个)
6.非叶子结点的指针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 表名(字段(长度))
show index from 表名
查看索引
show index from 表名
删除索引
drop index[索引名称] on 表名
更改索引
添加一个主键,索引必须是唯一索引,不能为NULL
alter table tab_name add primary key(column_list)
创建的索引是唯一索引,可以为NULL
alter table tab_name add unque index_name(column_list)
普通索引,索引值可出现多次
alter table tab_name add index index_name(column_list)
全文索引
alter table tab_name add fulltext index_name(column_list)
索引建立选择
适合建立索引
1.主键自动建立唯一索引：primary
2.频繁作为查询条件的字段应该创建索引：比如银行系统银行帐号,电信系统的手机号
3.查询中与其它表关联的字段,外键关系建立索引：比如员工,部门外键
4.频繁更新的字段不适合建立索引：每次更新不单单更新数据,还要更新索引
where条件里用不到的字段不建立索引，查询中排序的字段,排序的字段若通过索引去访问将大提升排序速度，索引能够提高检索的速度和排序的速度，查询中统计或分组的字段，分组的前提是必排序。
不适合建立索引
记录比较少
经常增删改的表，索引提高了查询的速度，同时却会降低更新表的速度,如果对表的INSERT,UPDATE和DELETE，因为建立索引后, 更新表时, MYSQL不仅要保存数据,还要保存一下索引文件
数据重复的表字段,如果某个数据列包含了许多重复的内容,为它建立索引 就没有太大在的实际效果，比如表中的某一个字段为国籍,性别，数据的差异率和重复率不高,这种建立索引就没有太多意义。
