MySql 索引(index)

2020-04-07

MySql

1.索引的概念

1	是帮助MySql高效获取数据的数据结构。排好序的快速查找数据结构

1.1 创建索引

1
2
3

create index index_name on table_name(column_name);
或者
alter table table_name add index index_name(column_name);

1.2 删除索引

1	alter table table_name drop index index_name;

2.索引数据结构

1、BTree 索引（主要）
2、Hash索引
3、full-text全文索引
4、R-Tree索引

2.1 BTree数据结构图示

avatar

2.2 btree索引和hash索引的区别

** 1、BTREE（B树（可以是多叉树））{主流使用}。
** 2、HASH（key,value）这种方式对范围查询支持得不是很好。
hash索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。
可能很多人又有疑问了，既然 Hash 索引的效率要比 B-Tree 高很多，为什么大家不都用 Hash 索引而还要使用 B-Tree 索引呢？任何事物都是有两面性的，Hash 索引也一样，虽然 Hash 索引效率高，但是 Hash 索引本身由于其特殊性也带来了很多限制和弊端，主要有以下这些。

（1）Hash 索引仅仅能满足”=”,”IN”和”<=>”查询，不能使用范围查询。
由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。
（2）Hash 索引无法被用来避免数据的排序操作。
由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；
（3）Hash 索引不能利用部分索引键查询。
对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。
（4）Hash 索引在任何时候都不能避免表扫描。
前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。
（5）Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

3.索引类型

normal：表示普通索引；
unique：表示唯一的，不允许重复的索引，如果该字段信息保证不会重复例如身份证号用作索引时，可设置为unique；
full textl: 表示全文搜索的索引。FULLTEXT 用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以；
SPATIAL:空间索引,空间索引是对空间数据类型的字段建立的索引；

4.适合创建索引条件

1、主键自动建立唯一索引
2、频繁作为查询条件的字段应该建立索引
3、查询中与其他表关联的字段，外键关系建立索引
4、单键/组合索引的选择问题，组合索引性价比更高
5、查询中排序的字段，排序字段若通过索引去访问将大大提高排序效率
6、查询中统计或者分组字段

5.不适合创建索引条件

1、表记录少的
2、经常增删改的表或者字段
3、where条件里用不到的字段不创建索引
4、过滤性不好的不适合建索引

< MySql explain

MySql常用函数 >