1、概念
2.索引的优缺点
3、MYSQL索引数据结构
支持各种存储引擎
1)二叉搜索树
2）红黑树
3）B Trees
4）B Trees
4、索引分类
5.在什么情况下建立索引？
6.在什么情况下不建立索引？
7、操作索引
1）创建语法
2)创建语法二
3）全文检索
4）查看索引
5）删除索引
1、概念
-
MYSQL索引的官方定义如下：。从而获得索引的本质:索引是数据结构。(索引文件)
-
索引的目的是提高查询效率，可以类比字典。
-
例如，如果你想找到mysql。我们必须定位m字母，然后向下找到y字母，然后找到剩下的sql。
-
如果没有索引，你可能需要它a---z，如果我们想逐一查询，如果我们想找到它java或者oracle开头的条目呢？
-
你觉得没有索引就完成不了吗？
-
您可以理解为：快速搜索数据结构。
1)除了数据本身，数据库还维护满足特征搜索算法的数据结构，以某种方式指向数据，以便在这些数据结构的基础上实现先进的搜索算法，即索引。
2)索引本身很大，不可能全部存储在内存中，因为索引通常以索引文件的形式存储在磁盘中。
3)我们通常所说的索引，如果没有特别说明，都指B树（BTree），(多路搜索树不一定是二叉树)结构组织的索引。其中，默认使用聚合索引、次要索引、复合索引和前缀索引。B 树索引，统称索引。当然，除了B 除了这种类型的树索引，还有哈希索引(hash index)等
2.索引的优缺点
-
优势：
- 搜索：类似于大学图书馆建书目录索引，提高数据检索效率，降低数据库IO成本；
- 排序：通过索引排序数据，降低数据排序成本，降低数据排序成本CPU的消耗。
-
劣势：
- 事实上，索引也是一个表，它保存了主键和索引字段，并指向实体表的记录，因此索引列也占用了空间。
- 虽然索引大大提供了查询速度，但也降低了更新表的速度，但如果增加、删除和更改表，MySQL不仅要更新数据，还要更新索引文件。每次添加索引列的字段数据时，更新引起的键值变化后的索引信息都会进行调整。
- 如果你的索引是提高效率的因素MySQL大数据表需要时间来研究和建立最好的索引。或者优化查询。DBA职责）
3、MYSQL索引数据结构
-
B TREE 大多数引擎支持最常见的索引类型B 树索引
-
HASH索引：哈希表实现了底层数据结构。只有准确匹配索引列的查询才有效，不支持范围查询
-
R-TREE索引：空间索引是 MyISAM 引擎的特殊索引类型主要用于地理空间数据类型，通常使用较少
-
FULL Text全文索引：通过建立倒排索引，快速匹配文档，类似于 Lucene, Solr, ES
支持各种存储引擎
| 索引 | InnoDB | MyISAM | Memory |
|---|---|---|---|
| B Tree索引 | 支持 | 支持 | 支持 |
| Hash索引 | 不支持 | 不支持 | 支持 |
| R-Tree索引 | 不支持 | 支持 | 不支持 |
| Full-text | 5.6版本后支持 | 支持 | 不支持 |
1)二叉搜索树
28、73、36、23、18、32、12、40
二叉搜索树的缺点：顺序插入时，会形成链表，大大降低查询性能。大数据层次深，检索速度慢。
2）红黑树
在二叉树的基础上，有更多的树平衡，也被称为二叉衡树。与二叉树不同，极端情况将朝着一个方向发展。
3）B Trees
多路平衡查找树，在红黑树的基础上，每个节点可以存放多个数据。以下是最大度数为3（3阶）的B-TREE。
-
度数：一个节点的子节点个数；
-
最大度数（max-degree），一个节点的最多子节点个数；
-
N阶B树，最大度数为N，
- 每个节点最多可以存储N-1个key（关键字）
- 每个节点最多有N+1个指针
-
原理
- 左子树 < 根 < 右子树
- 插入关键字时，如果节点已满，则将其中间关键字分裂成两个结点，中间关键字被提升到该结点的父结点
-
在B树中，具体的数据都挂载在KEY下面，如下图所示：
思考：如果插入81，上图会发生什么变化？
B Trees的特性：
-
关键字集合分布在整颗树中；
-
任何一个关键字出现且只出现在一个结点中；
-
搜索有可能在非叶子结点结束；
-
其搜索性能等价于在关键字全集内做一次二分查找；
-
自动层次控制；
B Trees的搜索，从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子结点；重复，直到所对应的儿子指针为空，或已经是叶子结点；
4）B+ Trees
B Trees树是B Trees的变体，也是一种多路搜索树。与B Trees的区别：
-
B+ Trees只会在叶子节点上面挂载数据，而非叶子节点不会存放数据，只存放索引列的数据；
-
叶子节点形成一个意向链表
B+的特性：
-
所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的；
-
不可能在非叶子结点命中；
-
非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层；
-
更适合文件索引系统；
B+ Trees的搜索与B Trees基本相同，区别是B+Trees只有达到叶子结点才命中（B Trees可以在非叶子结点命中），其性能也等价于在关键字全集做一次二分查找；
在MySQL中，对B+ Trees进行了优化，在原B+ Trees的基础上，增加一个指向相依叶子节点的链表指针。也就是说，叶子节点之间是双向指针连接，从而提高区间范围性能，范围查找。
4、索引分类
-
单值索引：一个索引只包含单个列，一个表可以有多个单列索引（建议不超过5个索引）
-
复合索引：一个索引包含多个字段。（如帐号+密码两个字段建立一个复合索引）
-
主键索引（PRIMARY）：表中主键创建的索引，只能有一个，不允许为NULL。（创建主键索引时自动创建）
-
唯一索引（UNIQUE）：索引值的列必须唯一，可以有多个，但允许为NULL。（创建唯一索引时自动创建）
-
全文索引（FULLTEXT）：全文索引查找的是文本中的关键词，而不是比较索引中的值，可以有多个
在 InnoDB 存储引擎中，根据索引的存储形式，又可以分为以下两种：
| 分类 | 含义 | 特点 |
|---|---|---|
| 聚集索引（Clustered Index） | 将数据存储与索引放一块，索引结构的叶子节点保存了行数据 | 必须有，而且只有一个 |
| 二级索引（Secondary Index） | 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键 | 可以存在多个 |
说明：
-
二级索引，也叫非聚集索引；
-
为什么推荐InnoDB表必须有主键？
- 保证会有主键索引树的存在（因为数据存放在主键索引树上面）
- 聚集索引选取规则：
- 如果存在主键，主键索引就是聚集索引
- 如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引
- 如果表没有主键或没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引
-
为什么非主键索引结构叶子节点存储的是主键值？
- 一是保证一致性，更新数据的时候只需要更新主键索引树
- 二是节省存储空间
-
为什么推荐使用整型的自增主键？
- 一是方便查找比较
- 二是新增数据的时候只需要在最后加入，不会大规模调整树结构。如果是UUID的话，大小不好比较，新增的时候也极有可能在中间插入数据，会导致树结构大规调整，造成插入数据变慢
5、什么情况建立索引
-
主键自动建立唯一索引
-
频繁作为查询条件的字段应该建立索引。最好是保存以后不再变更的字段，因为在增、删、改操作时，会造成数据的变动，同时索引文件也会变动。而在删除操作时，会带来索引文件的重新调整。那么我们可以这样避免：在表增加一个状态字段is_delete=0，如果用户删除数据时，把is_delete=1即可。这样可以避免变动索引文件，从而减少资源的消耗。
-
查询中与其他表关联的字段，即外键关系建立索引
-
要控制索引的数量（不要超过5个索引），太多就性能瓶颈和维护成本，影响增删改的效率
-
一般在开发中建议建立复合索引，如以下字段：
-
-
id name account password email createtime
-
-
-
我们会建立account+password的复合索引，…where account=? and password=?
-
-
查询中排序的字段，排序字段若通过索引去访问将大大的提供排序速度。
-
查询中统计或者分组字段（group by也和索引有关）。
6、什么情况不建立索引
-
频繁更新的字段不适合作为索引，因为每次更新不单单是更新记录还在更新索引。
-
表的记录数太少
-
经常增删改的表
-
数据重复且平均的字段。
-
where字段用不到的字段不要建立索引
-
假如一个表有10万行记录，有一个字段A只有true和false两种值，并且每个值的分布概率大约为50%，那么对A字段建索引一般不会提高数据库的查询速度。索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1，这个索引的效率就越高。
7、操作索引
1）创建语法
# 索引命名规范：idx_xxx CREATE [UNIQUE] INDEX 索引名称 ON 表名 (字段|字段列表) ;
# 单值索引 CREATE INDEX 索引名称 ON 表名 (字段) ; CREATE INDEX index_username ON userinfo(username) ;
# 唯一索引 CREATE UNIQUE INDEX 索引名称 ON 表名 (字段列表) ; CREATE UNIQUE INDEX index_username ON userinfo(username) ;
# 复合索引,字段列表中的字段顺序是有讲究的 CREATE INDEX 索引名称 ON 表名(字段列表) ; CREATE INDEX index_username_password ON userinfo(username,password) ;
需要引入MySQL示例数据库
# emp_no是主键，以下查询会使用到主键索引 mysql> select * from employees where emp_no=10001;
# first_name字段并没有添加索引，因此查询速度会很慢 mysql> select * from employees where first_name='Facello';
# 创建索引 create index idex_employees_first_name on employees(first_name);
# 再次查询测试 mysql> select * from employees where first_name='Facello';
2）创建语法二
ALTER TABLE 表名 ADD [UNIQUE] INDEX 索引名称(字段|字段列表)
# 单值索引 ALTER TABLE 表名 ADD INDEX 索引名称(字段) ALTER TABLE userinfo ADD INDEX index_username(username)
# 唯一索引 ALTER TABLE 表名 ADD UNIQUE INDEX 索引名称(字段列表) ALTER TABLE userinfo ADD UNIQUE INDEX index_username ON (username)
# 该语句添加一个主键，这意味着索引值必须是唯一的，并且不能为NULL ALTER TABLE 表名 ADD INDEX 索引名称(字段列表) ALTER TABLE userinfo ADD INDEX index_username_password ON (username,password)
# 主键索引（特殊的唯一索引） ALTER TABLE 表名 ADD PRIMARY KEY(字段列表);
注意：
-
如果某个字段设置为主键约束（primary key），那么该字段默认就是主键索引。
-
主键索引是特殊的唯一索引
- 相同点：该列中的数据都不能有相同值；
- 不同点：主键索引不能有null值，但是唯一索引可以有null值。
3）全文检索
# 该语句指定了索引为FULLTEXT，用于全文检索 ALTER TABLE 表名 ADD FULLTEXT 索引名称(字段列表);
4）查看索引
SHOW INDEX FROM 表名;
5）删除索引
DROP INDEX 索引名称 ON 表名 ;