数据库(三)索引

参考:https://blog.csdn.net/happyheng/article/details/53143345

          https://www.cnblogs.com/shijianchuzhenzhi/p/6383117.html

SQL索引在数据库优化中占有一个非常大的比例, 一个好的索引的设计,可以让你的效率提高几十甚至几百倍,在这里将带你一步步揭开他的神秘面纱。

  1.1 什么是索引?

  SQL索引有两种,聚集索引和非聚集索引,索引主要目的是提高了SQL Server系统的性能,加快数据的查询速度与减少系统的响应时间 

下面举两个简单的例子:

图书馆的例子:一个图书馆那么多书,怎么管理呢?建立一个字母开头的目录,例如:a开头的书,在第一排,b开头的在第二排,这样在找什么书就好说了,这个就是一个聚集索引,可是很多人借书找某某作者的,不知道书名怎么办?图书管理员在写一个目录,某某作者的书分别在第几排,第几排,这就是一个非聚集索引

字典的例子:字典前面的目录,可以按照拼音和部首去查询,我们想查询一个字,只需要根据拼音或者部首去查询,就可以快速的定位到这个汉字了,这个就是索引的好处,拼音查询法就是聚集索引,部首查询就是一个非聚集索引.

    看了上面的例子,下面的一句话大家就很容易理解了:聚集索引存储记录是物理上连续存在,而非聚集索引是逻辑上的连续,物理存储并不连续。就像字段,聚集索引是连续的,a后面肯定是b,非聚集索引就不连续了,就像图书馆的某个作者的书,有可能在第1个货架上和第10个货架上。还有一个小知识点就是:聚集索引一个表只能有一个,而非聚集索引一个表可以存在多个。

如果上面还没看明白,看下面的例子:

 1、聚集索引:
      聚集索引的意思可以理解为顺序排列,比如一个主键自增的表即为聚集索引,即id为1的存在于第一条,id为2的存在于第二条...假使数据库中是使用数组来存放的这张表中的数据,那么如果我需要查找第100条,那么直接第一条数据的地址加上100即为第一百条的地址,一次就能查询出来。
      因为数据库中的数据只能按照一个顺序进行排列,所以聚集索引一个数据库只能有一个。在mysql中,不能自己创建聚集索引,主键即为聚集索引,如果没有创建主键,那么默认非空的列为聚集索引,如果没有非空的列那么会自动生成一个隐藏列为聚集索引。
      所以一般在mysql中,我们创建的主键即为聚集索引,数据是按照我们的主键顺序进行排列。所以在根据主键进行查询时会非常快。
  
 2、非聚集索引:
      非聚集索引可以简单理解为有序目录,是一种以空间换取时间的方法。举个例子,在一个user表中,有一个id_num,即身份号,此不为主键id,那么这些数据在存储的时候都是无序的,比如
      id为1的id_num为100,id为2的id_num为97,id为3的id_num为98,id为4的id_num为99,id为5的id_num为96。。。id为67的id_num为56。。。那么如果我要查找id_num为56的人,那么只能一条一条的遍历,n条就需要查询n次,时间复杂度为O(n),这是非常耗费性能的。
       所以,现在就需要为id_num增加非聚集索引,添加了非聚集索引后,会给id_num进行排序(内部使用结构为B+树),并且排序后,我只需要查询此目录(即查询B+树),很快就知道为id为56的在数据库中的第67条,而不需要在去遍历表中的所有数据。
       所以,在非聚集索引中,不重复的数据越多,那么索引的效率越高。

   1.2 索引的存储机制

    首先,无索引的表,查询时,是按照顺序存续的方法扫描每个记录来查找符合条件的记录,这样效率十分低下,举个例子,如果我们将字典的汉字随即打乱,没有前面的按照拼音或者部首查询,那么我们想找一个字,按照顺序的方式去一页页的找,这样效率有多底,大家可以想象。

       聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致,其实理解起来非常简单,还是举字典的例子:如果按照拼音查询,那么都是从a-z的,是具有连续性的,a后面就是b,b后面就是c, 聚集索引就是这样的,他是和表的物理排列顺序是一样的,例如有id为聚集索引,那么1后面肯定是2,2后面肯定是3,所以说这样的搜索顺序的就是聚集索引。非聚集索引就和按照部首查询是一样是,可能按照偏房查询的时候,根据偏旁‘弓’字旁,索引出两个汉字,张和弘,但是这两个其实一个在100页,一个在1000页,(这里只是举个例子),他们的索引顺序和数据库表的排列顺序是不一样的,这个样的就是非聚集索引。

      原理明白了,那他们是怎么存储的呢?在这里简单的说一下,聚集索引就是在数据库被开辟一个物理空间存放他的排列的值,例如1-100,所以当插入数据时,他会重新排列整个物理空间,而非聚集索引其实可以看作是一个含有聚集索引的表,他只仅包含原表中非聚集索引的列和指向实际物理表的指针。他只记录一个指针,其实就有点和堆栈差不多的感觉了

 

  1.3 什么情况下设置索引 

动作描述

使用聚集索引 

 使用非聚集索引

 外键列

 应

 应

 主键列

 应

 应

 列经常被分组排序(order by)

 应

 应

 返回某范围内的数据

 应

 不应

 小数目的不同值

 应

 不应

 大数目的不同值

 不应

 应

 频繁更新的列

不应 

 应

 频繁修改索引列

 不应

 应

 一个或极少不同值

 不应

 不应

 

建立索引的原则:

1) 定义主键的数据列一定要建立索引。

2) 定义有外键的数据列一定要建立索引。

3) 对于经常查询的数据列最好建立索引。

4) 对于需要在指定范围内的快速或频繁查询的数据列;

5) 经常用在WHERE子句中的数据列。

6) 经常出现在关键字order by、group by、distinct后面的字段,建立索引。如果建立的是复合索引,索引的字段顺序要和这些关键字后面的字段顺序一致,否则索引不会被使用。

7) 对于那些查询中很少涉及的列,重复值比较多的列不要建立索引。

8) 对于定义为textimagebit的数据类型的列不要建立索引。

9) 对于经常存取的列避免建立索引 

9) 限制表上的索引数目。对一个存在大量更新操作的表,所建索引的数目一般不要超过3个,最多不要超过5个。索引虽说提高了访问速度,但太多索引会影响数据的更新操作。

10) 对复合索引,按照字段在查询条件中出现的频度建立索引。在复合索引中,记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录,系统再按照第二个字段的取值排序,以此类推。因此只有复合索引的第一个字段出现在查询条件中,该索引才可能被使用,因此将应用频度高的字段,放置在复合索引的前面,会使系统最大可能地使用此索引,发挥索引的作用。

 

  1.4 如何创建索引

  1.41 创建索引的语法:

1.1、创建普通索引:
模式:
CREATE INDEX 索引名 ON 表名(列名1,列名2,...);
或者
修改表: ALTER TABLE 表名ADD INDEX 索引名 (列名1,列名2,...);
或者
创建表时指定索引:CREATE TABLE 表名 ( [...], INDEX 索引名 (列名1,列名 2,...) );

eg:
CREATE INDEX name_index ON index_test(name);
此为在index_test表上的name列上创建一个索引name_index。

测试的表为:
CREATE TABLE index_test (
id INT  NOT NULL,
    name VARCHAR(50),
    idNum INT,
    PRIMARY KEY (id)
);

1.2、创建唯一索引:
表示唯一的,不允许重复的索引,如果该字段信息保证不会重复例如身份证号用作索引时,可设置为unique
下面三种模式都可以创建唯一索引:
  1、创建索引:CREATE UNIQUE INDEX 索引名 ON 表名(列的列表);
  2、在表上增加索引:ALTER TABLE 表名ADD UNIQUE 索引名 (列的列表);
  3、创建表时指定索引:CREATE TABLE 表名( [...], UNIQUE 索引名 (列的列表) );
eg:
CREATE UNIQUE INDEX id_num_index ON index_test(idNum);
也可以写成下面的形式:
 
ALTER TABLE index_test ADD UNIQUE id_num_index(idNum);
此为在index_test表的idNum列上创建一个唯一索引id_num_index

在创建了唯一索引之后,列中即不能重复,比如,现在我给表中插入一条重复的值,会报:
Error Code: 1062. Duplicate entry '3' for key 'id_num_index'
即在id_num_index唯一索引上出现了重复。

1.42 删除索引语法:

以下两种模式都可以删除索引:

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name

eg:
DROP INDEX name_index ON index_test;
此为删除在index_test表上的name_index索引   

1.43 显示索引信息:

SHOW INDEX FROM index_test;

即返回index_test表中的所有索引。


在返回的字段中,

Table:表的名称
Non_unique:是否不唯一,0为唯一,1不为唯一
Key_name:索引的名称
Seq_in_index:索引中的列序列号,从1开始
Column_name:列名称
Collation:列以什么方式存储在索引中。在MySQL中,有值‘A’(升序)或NULL(无分类)。
Cardinality:索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数,所以即使对于小型表,该值也没有必要是精确的。基数越大,当进行联合时,MySQL使用该索引的机会就越大。
Sub_part:如果列只是被部分地编入索引,则为被编入索引的字符的数目。如果整列被编入索引,则为NULL。
Packed:指示关键字如何被压缩。如果没有被压缩,则为NULL。
Null:如果列含有NULL,则含有YES。如果没有,则该列含有NO。
Index_type:用过的索引方法(BTREE, FULLTEXT, HASH, RTREE)。

Comment:更多评注。

Mysql索引主要有两种结构:B+Tree索引和Hash索引

Hash索引

MySQL中,只有Memory(Memory表只存在内存中,断电会消失,适用于临时表)存储引擎显示支持Hash索引,是Memory表的默认索引类型,尽管Memory表也可以使用B+Tree索引。hsah索引把数据的索引以hash形式组织起来,因此当查找某一条记录的时候,速度非常快。当时因为是hash结构,每个键只对应一个值,而且是散列的方式分布。所以他并不支持范围查找和排序等功能。

B+树索引

B+tree是mysql使用最频繁的一个索引数据结构,是Inodb和Myisam存储引擎模式的索引类型。相对Hash索引,B+树在查找单条记录的速度比不上Hash索引,但是因为更适合排序等操作,所以他更受用户的欢迎。毕竟不可能只对数据库进行单条记录的操作。

带顺序访问指针的B+Tree

B+Tree所有索引数据都在叶子结点上,并且增加了顺序访问指针,每个叶子节点都有指向相邻叶子节点的指针。
这样做是为了提高区间查询效率,例如查询key为从18到49的所有数据记录,当找到18后,只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点,极大提到了区间查询效率。

大大减少磁盘I/O读取

数据库系统的设计者巧妙利用了磁盘预读原理,将一个节点的大小设为等于一个页,这样每个节点只需要一次I/O就可以完全载入。

为了达到这个目的,在实际实现B- Tree还需要使用如下技巧:

每次新建节点时,直接申请一个页的空间,这样就保证一个节点物理上也存储在一个页里,加之计算机存储分配都是按页对齐的,就实现了一个node只需一次I/O。

B-Tree中一次检索最多需要h-1次I/O(根节点常驻内存),渐进复杂度为O(h)=O(logdN)。一般实际应用中,出度d是非常大的数字,通常超过100,因此h非常小(通常不超过3)。而红黑树这种结构,h明显要深的多。由于逻辑上很近的节点(父子)物理上可能很远,无法利用局部性,所以红黑树的I/O渐进复杂度也为O(h),效率明显比B-Tree差很多。


面试题:数据库什么情况下索引会失效

1.条件中用or,即使其中有条件带索引,也不会使用索引查询(这就是查询尽量不要用or的原因,用in吧)

注意:使用or,又想索引生效,只能将or条件中的每个列都加上索引

2.对于多列索引,不是使用的第一部分,则不会使用索引。

3.like的模糊查询以%开头,索引失效

4.如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不会使用索引

5.如果MySQL预计使用全表扫描要比使用索引快,则不使用索引

阅读更多
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页