索引的分类
- 普通索引: 即针对数据库表任意字段创建索引;
- 唯一索引: 与普通索引类似,不同的就是:MySQL 数据库索引列的值必须唯一,但允许有空值;
- 主键索引: 它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创建主键索引;
- 组合索引: 为了进一步榨取 MySQL 的效率,就要考虑建立组合索引。即将数据库表中的多个字段联合起来作为一个组合索引。
MySQL 中的索引类型
- Normal :普通索引,大多数情况下均可使用。
- Unique :唯一索引,字段数据唯一,不允许重复的索引,如身份证号。主键在建立时,MySQL会自动创建唯一索引,其与普通的唯一索引的区别是不允许值为空。
- Full Text :全文索引,在检索长文本的时候,效果最好,例如搜索一篇很长的文章。在检索数据量比较大的时候,将数据放入一个没有全局索引的表中,然后在用Create Index创建的Full Text索引,要比先为一张表建立Full Text然后在写入数据要快的很多。
MySQL 中的索引方法
- BTREE(B树(可以是多叉树)) {主流使用};
- HASH(key,value) 这种方式对范围查询支持得不是很好。
-
hash 索引结构的特殊性,导致其检索效率非常高,检索时可以一步定位,不像B-Tree 索引需要从根节点到枝节点,最后才能访问到页节点这样多次的IO访问,所以 Hash 索引的查询效率要远高于 B-Tree 索引。
-
但是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值相等的情况后性能并不一定就会比B-Tree索引高。 对于选择性比较低的索引键,如果创建 Hash 索引,那么将会存在大量记录指针信息存于同一个 Hash 值。这样要定位某一条记录时就会非常麻烦,会浪费多次表数据的访问,而造成整体性能低下。
在实际操作过程中,应该选取表中哪些字段作为索引?
为了使索引的使用效率更高,在创建索引时,必须考虑在哪些字段上创建索引和创建什么类型的索引,有以下原则:
1.优先创建唯一性索引
2.为经常需要排序、分组和联合操作的字段建立索引
3.为常作为查询条件的字段建立索引
4.索引的数目不宜过多,需要限制。索引的数目不是越多越好。每个索引都需要占用磁盘空间,索引越多,需要的磁盘空间就越大。修改表时,对索引的重构和更新很麻烦。索引越多,更新表越费时。
5.尽量使用数据量少的索引(字段短),索引值过长,查询速度会受到影响。
6.尽量使用前缀来索引。如果索引字段的值很长,最好使用值的前缀来索引。
7.删除不再使用或者很少使用的索引
8.经常更新修改的字段不要建立索引(针对mysql说,因为字段更改同时索引就要重新建立,排序,而Orcale好像是有这样的机制字段值更改了,它不立刻建立索引,排序索引,而是根据更改个数,时间段去做平衡索引这件事的)
9、不推荐在同一列建多个索引
参考资料
https://blog.csdn.net/guo_qiangqiang/article/details/88794971
https://www.cnblogs.com/starinbrook/p/9365695.html