您现在的位置是:首页 >  云笔记 >  开发笔记 >  文章详情

MySQL数据库中索引基础知识详解

onekbit@163.com   2019-03-26 10:26:18   12人已围观

第一、什么是索引

       MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。对数据库而言获取数据就是查询操作,那么高效的获取数据意思就是可以提高查询效率。如果从数据库系统的查询算法的角度进行优化。最基本的查询算法当然是顺序查找(linear search),这种复杂度为O(n)的算法在数据量很大时显然是糟糕的,好在计算机科学的发展提供了很多更优秀的查找算法,例如二分查找(binary search)、二叉树查找(binary tree search)等。如果稍微分析一下会发现,每种查找算法都只能应用于特定的数据结构之上,例如二分查找要求被检索数据有序,而二叉树查找只能应用于二叉查找树上,但是数据本身的组织结构不可能完全满足各种数据结构(例如,理论上不可能同时将两列都按顺序进行组织),所以,在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。

   举例说明一下:

  有一张person表,其中有2W条记录,记录着2W个人的信息。有一个Phone的字段记录每个人的电话号码,现在想要查询出电话号码为xxxx的人的信息。

  如果没有索引,那么将从表中第一条记录一条条往下遍历,直到找到该条信息为止。

  如果有了索引,那么会将该Phone字段,通过一定的方法进行存储,好让查询该字段上的信息时,能够快速找到对应的数据,而不必在遍历2W条数据了。

其中MySQL中的索引的存储类型有两种:BTREE、HASH。 也就是用树或者Hash值来存储该字段,要知道其中详细是如何查找的,就需要会算法的知识了。我们现在只需要知道索引的作用,功能是什么就行。

第二、索引有哪些分类

        注意:索引是在存储引擎中实现的,也就是说不同的存储引擎,会使用不同的索引
                  MyISAM和InnoDB存储引擎:只支持BTREE索引, 也就是说默认使用BTREE,不能够更换
                  MEMORY/HEAP存储引擎:支持HASH和BTREE索引
     1、索引我们分为四类来讲 单列索引(普通索引,唯一索引,主键索引)、组合索引、全文索引、空间索引、
     1.1、单列索引:一个索引只包含单个列,但一个表中可以有多个单列索引。 这里不要搞混淆了。
     1.1.1、普通索引:
                           MySQL中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值,纯粹为了查询数据更快一点。
     1.1.2、唯一索引:
                           索引列中的值必须是唯一的,但是允许为空值,
                 1.1.3、主键索引:
                            是一种特殊的唯一索引,不允许有空值。
          1.2、组合索引
                 在表中的多个字段组合上创建的索引,只有在查询条件中使用了这些字段的左边字段时,索引才会被使用,使用组合索引时遵循最左前缀集合。这个如果还不明白,等后面举例讲解时在细说 
       1.3、全文索引
                 全文索引,只有在MyISAM引擎上才能使用,只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引,介绍了要求,说说什么是全文索引,就是在一堆文字中,通过其中的某个关键字等,就能找到该字段所属的记录行,比如有"你是个大煞笔,二货 ..." 通过大煞笔,可能就可以找到该条记录。这里说的是可能,因为全文索引的使用涉及了很多细节,我们只需要知道这个大概意思,如果感兴趣进一步深入使用它,那么看下面测试该索引时,会给出一个博文,供大家参考。
         1.4、空间索引
               空间索引是对空间数据类型的字段建立的索引,MySQL中的空间数据类型有四种,GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时,使用SPATIAL关键字。要求,引擎为MyISAM,创建空间索引的列,必须将其声明为NOT NULL。

第三、索引如何操作

1、主键索引:即主索引,根据主键pk_clolum(length)建立索引,不允许重复,不允许空值;

ALTER TABLE 'table_name' ADD PRIMARY KEY('col');
2、唯一索引:用来建立索引的列的值必须是唯一的,允许空值

ALTER TABLE 'table_name' ADD UNIQUE('col');
3、普通索引:用表中的普通列构建的索引,没有任何限制

ALTER TABLE 'table_name' ADD INDEX index_name('col');
4、全文索引:用大文本对象的列构建的索引(下一部分会讲解)

ALTER TABLE 'table_name' ADD FULLTEXT('col');
5、组合索引:用多个列组合构建的索引,这多个列中的值不允许有空值

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3');


第四、索引的使用策略

什么时候要使用索引?

主键自动建立唯一索引;
经常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建立索引;
作为排序的列要建立索引;
查询中与其他表关联的字段,外键关系建立索引
高并发条件下倾向组合索引;


什么时候不要使用索引?

经常增删改的列不要建立索引;
有大量重复的列不建立索引;
表记录太少不要建立索引


第五、其他

分享到:


编辑发布时间:2021-02-19 14:47:05