【MySQL】-- 索引
索引
概念
索引其实是一种数据结构，能够帮助我们快速的检索数据库中的数据
一、索引的分类
索引主要分为：普通索引、唯一索引、主键索引、组合索引、全文索引
1、普通索引
是最基本的索引，它没有任何限制。
2、唯一索引
索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一
3、主键索引
它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候指定了主键，就会创建主键索引， CREATE INDEX不能用来创建主键索引，使用 ALTER TABLE来代替。
4、组合索引(复合索引)
一个索引包含多个列，实际开发中推荐使用复合索引。
注：如果我们创建了(name, age,xb)的复合索引，那么其实相当于创建了(name, age,xb)、(name, age)、(name)三个索引，这被称为最佳左前缀
特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。
5、全文索引
FULLTEXT索引用于全文搜索。用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。
只有InnoDB和 MyISAM存储引擎支持 FULLTEXT索引和仅适用于 CHAR， VARCHAR和 TEXT列。
二、索引的优缺点
优点
-
提高数据检索的效率，降低数据库
IO成本。 -
通过索引对数据进行排序，降低数据的排序成本，降低
CPU的消耗。
缺点
- 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
- 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大
三、索引创建
1、何时要创建索引
- 主键自动创建唯一索引
- 作为条件进行较频繁的查询的字段
- 查询中排序的字段，查询中统计或者分组的字段。’
2、何时不要创建索引
- 表记录字段太少
- 频繁进行增删改的字段
- 唯一性太差的字段，不适合单独创建索引。即使频繁作为查询条件 比如性别，民族，政治面貌（可能总共就是那么几个或几十个值重复使用的字段）
四、索引使用的注意事项
-
模糊查询
尽量少使用模糊查询，如果要使用那么，通配符%可以出现在结尾，不能在开头。
name like‘张%’ ，索引有效name like‘%张’ ,索引无效，全表查询
-
or 会引起全表扫描
-
不要使用
NOT、!=、NOT IN、NOT LIKE等 -
尽量少使用
select *，而是根据需求来选择需要显示的字段 -
索引不会包含有
null值的列只要列中包含有null值都将不会被包含在索引中，复合索引中只要有一列含有null值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为null。
-
不要在列上进行运算，这将导致索引失效而进行全表扫描
-
使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个
char(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
五、索引结构方式
Hash索引
所谓Hash索引，当我们要给某张表某列增加索引时，将这张表的这一列进行哈希算法计算，得到哈希值，排序在哈希数组上。所以Hash索引可以一次定位，其效率很高。
-
Hash索引仅仅能满足=,IN和<=>查询，不能使用范围查询。
由于Hash索引比较的是进行Hash运算之后的Hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash算法处理之后的Hash值的大小关系，并不能保证和Hash运算前完全一样。 -
Hash索引无法被用来避免数据的排序操作。
由于Hash索引中存放的是经过Hash计算之后的Hash值，而且Hash值的大小关系并不一定和Hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算 -
对于组合索引
Hash索引不能利用部分索引键查询。
对于组合索引，Hash索引在计算Hash值的时候是组合索引键合并后再一起计算Hash值，而不是单独计算Hash值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash索引也无法被利用。 -
Hash索引在任何时候都不能避免表扫描。
Hash索引是将索引键通过Hash运算之后，将Hash运算结果的Hash值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同Hash值，所以即使取满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果 -
Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。
B-TREE
B-Tree 索引是 MySQL 数据库中使用最为频繁的索引类型。简单理解，它就像一棵树，B-Tree索引需要从根节点到枝节点，才能访问到页节点的具体数据。
B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索，根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找,通过比较节点页的值和要查找的值可以找到合适的指针进入下一层子节点，这些指针实际上定义了子节点页中值的上限和下限，最终存储引擎要么是找到对应的值，要么是该记录不存在。
B-tree 索引可以用于使用 =, >, >=, <, <= 或者 BETWEEN 运算符的列比较。如果 LIKE 的参数是一个没有以通配符起始的常量字符串的话也可以使用这种索引。
六、聚族非聚族索引
聚族索引
1、定义
聚集索引，来源于生活尝试。这中索引可以说是按照数据的物理存储进行划分的。对于一堆记录来说，使用聚集索引就是对这堆记录 进行 堆划分。即主要描述的是物理上的存储
2、举例
比如图书馆新进了一批书。那么这些书需要放到图书馆内。书如何放呢？一般都有一个规则，杂志类的放到101房间，文学类的放到102房间，理工类的放到103房间等等。这些存储的规则决定了每本书应该放到哪里。而这个例子中聚集索引为书的类别。
正式因为这种存储规则，才导致 聚集索引的唯一性。
3、误区
有的人认为，聚聚族引的字段是唯一的。这是因为sql server 中添加主键的时候，自动给主键所在的字段生成一个聚集索引。所以人们会认为聚集索引所加的字段是唯一的。
思考一下上面这个问题。杂志类的书放到101房间。那么如果杂志类的书太多，一个101房间存放不下。那么可能101，201两个房间来存放杂志类的书籍。如果这样分析的话，那么一个杂志类对应多个房间。放到表存储的话，那么这个类别字段 就不是唯一的了
非聚族索引
1、定义
非聚族索引，也可以从生活中找到映射。非聚族索引强调的是逻辑分类。可以说是定义了一套存储规则，而需要有一块控件来维护这个规则，这个被称之为索引表
2、举例
同学如果想去图书馆找一本书，而不知道这本书在哪里？那么这个同学首先应该找的就是 检索室吧。对于要查找一本书来说，在检索室查是一个非常快捷的的途径了吧。但是，在检索室中你查到了该书在XX室XX书架的信息。你的查询结束了吗？没有吧。你仅仅找到了目的书的位置信息，你还要去该位置去取书
对于这种方式来说，你需要两个步骤：
- 查询该记录所在的位置。
- 通过该位置去取要找的记录
区别
- 聚族索引：可以帮助把很大的范围，迅速减小范围。但是查找该记录，就要从这个小范围中Scan了。
- 非聚族索引：把一个很大的范围，转换成一个小的地图。你需要在这个小地图中找你要寻找的信息的位置。然后通过这个位置，再去找你所需要的记录。
索引与主键的区别
- 主键：主键是唯一的，用于快速定位一条记录。
- 聚族索引：聚族索引也是唯一的。（因为聚集索引的划分依据是物理存储）。而聚集索引的主要是为了快速的缩小查找范围，即记录数目未定。
主键和索引没有关系。他们的用途相近。如果聚集索引加上唯一性约束之后，他们的作用就一样了。
使用场景
| 动作描述 | 使用聚族索引 | 使用非聚族索引 |
|---|---|---|
| 列经常分组排序 | T | T |
| 返回某范围内的数据 | T | F |
| 很少的不同值 | F | F |
| 小数目不同值 | T | F |
| 大数目不同值 | F | T |
| 频繁更新的列 | F | T |
| 主键列 | T | T |
| 外键列 | T | T |
| 频繁修改索引列 | T | T |
