几种MySQL索引的比较和区别(Primary、Normal、Unique、FullText、Spatial)

小咪咪 2021-12-18 14:41 201阅读 0赞

关于MySQL索引底层原理，可以参考我之前的博文：[https://blog.csdn.net/weixin\_36372879/article/details/90668838][https_blog.csdn.net_weixin_36372879_article_details_90668838]

--------------------

## 主键索引(Primary, pk\_) ##

必须为主键字段创建一个索引，这个Mysql索引就是所谓的“主索引”。  
一般情况下，数据库的主键为依次递增的uuid。这样可以在插入的时候，加快建立索引的速度。

## 普通索引(Normal, idx\_) ##

普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHEREcolumn=）或排序条件（ORDERBYcolumn）中的数据列创建索引。  
根据MySQL引擎(InnoDB或者MyISAM )不同，非主键索引的工作方式不同。

*  InnoDB是聚集索引，非主键索引存放的是主键索引的值
 *  MyISAM是非聚集索引，非主键索引存放的是数据的地址

## 唯一索引(Unique, uk\_) ##

如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。这么做的好处：一是简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；二是MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。  
比如我想要一个userId对应数据库中的一条记录，那么可以跟userId建立唯一索引

## 全文索引(FullText) ##

仅可用于 MyISAM 表， 用于在一篇文章中，检索文本信息的, 针对较大的数据，生成全文索引很耗时好空间。  
不过 FULLTEXT 用于搜索很长一篇文章的时候，效果最好。  
用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。  
比如有一个字段是备注，可能查找的时候，会把这个备注当做条件，那么可以在这个备注上面做全文索引

## 空间索引(Spatial) ##

MySQL包含OpenGIS 类相对应的数据类型，其中一些类型保存单一几何值

*  GEOMETRY 几何体
 *  POINT 点
 *  LINESTRING 线
 *  POLYGON 多边形

普通索引对于一维数据(key->data)是无往不利，可是面对空间数据(lon,lat -> data)就有些无能为力了，如果查询(116.27636, 40.041285)附近的点：  
空间索引通过 四叉树、R 树等数据结构，还有 GeoHash 算法将二维数据转化为一维使用普通B树索引 来实现，它们都能实现对空间范围内的快速搜索。  
具体可以参考：[https://www.cnblogs.com/zhenbianshu/p/6817569.html][https_www.cnblogs.com_zhenbianshu_p_6817569.html]

## 建立索引命名规约 ##

![在这里插入图片描述][20190702153427305.png]

*  主键索引：pk\_
 *  唯一索引：uk\_
 *  普通索引：idx\_

[https_blog.csdn.net_weixin_36372879_article_details_90668838]: https://blog.csdn.net/weixin_36372879/article/details/90668838
[https_www.cnblogs.com_zhenbianshu_p_6817569.html]: https://www.cnblogs.com/zhenbianshu/p/6817569.html
[20190702153427305.png]: /images/20211218/21db21522d9e458caa892f8ba018fc77.png