【数据库】索引

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/xing1584114471/article/details/96727154

(一)索引

   1.1 概念

      索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。更通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度。在没有索引的情况下,数据库会遍历全部数据后选择符合条件的;而有了相应的索引之后,数据库会直接在索引中查找符合条件的选项。

   1.2 创建索引的好处

     (1)通过创建索引,可以在查询的过程中,提高系统的性能

     (2)通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性

     (3)在使用分组和排序子句进行数据检索时,可以减少查询中分组和排序的时间

   1.3 创建索引的坏处

     (1)创建索引和维护索引要耗费时间,而且时间随着数据量的增加而增大

     (2)索引需要占用物理空间,如果要建立聚簇索引,所需要的空间会更大

     (3)在对表中的数据进行增加删除和修改时需要耗费较多的时间,因为索引也要动态地维护

   1.4应该被创建索引的列

     (1)经常需要搜索的列上

     (2)作为主键的列上

     (3)经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度

     (4)经常需要根据范围进行搜索的列上

     (5)经常需要排序的列上

     (6)经常使用在where子句上面的列上

   1.5 不应该被创建索引的列

     (1)查询中很少用到的列

     (2)对于那些具有很少数据值的列.比如人事表的性别列,bit数据类型的列

     (3)对于那些定义为text,image的列.因为这些列的数据量相当大

     (4)当对修改性能的要求远远大于搜索性能时.因为当增加索引时,会提高搜索性能,但是会降低修改性能

(二)索引的分类

   2.1 按照物理存储角度分类

         2.1.1 聚集索引

         该索引中键值的逻辑顺序决定了表中相应行的物理顺序。 聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿,后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序,因此一个表只能包含一个聚集索引。

         适用环境:

              (1)经常要搜索范围值的列;使用聚集索引找到包含第一个值的行后,便可以确保包含后续索引值的行在物理相邻。例如,如果应用程序执行 的一个查询经常检索某一日期范围内的记录,则使用聚集索引可以迅速找到包含开始日期的行,然后检索表中所有相邻的行,直到到达结束日期。这样有助于提高此 类查询的性能。

              (2)如果对从表中检索的数据进行排序时经常要用到某一列,则可以将该表在该列上聚集(物理排序);避免每次查询该列时都进行排序,从而节省成本。

              (3)索引值唯一时,使用聚集索引查找特定的行;例如,使用唯一雇员 ID 列 emp_id 查找特定雇员的最快速的方法,是在 emp_id 列上创建聚集索引或 PRIMARY KEY 约束。

      聚集索引的辅助索引数据域存储的也是相应记录主键的值而不是地址,所以当以辅助索引查找时,会先根据辅助索引找到主键,再根据主键索引找到实际的数据。所以Innodb不建议使用过长的主键,否则会使辅助索引变得过大。建议使用自增的字段作为主键,这样B+Tree的每一个结点都会被顺序的填满,而不会频繁的分裂调整,会有效的提升插入数据的效率。辅助索引查询两次

  图1:

        2.1.2 非聚集索引

       该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。但非聚集索引的叶子层并不与实际的数据页相重叠,而采用叶子层包含一个指向表中的记录在数据页中的指针的方式。非聚集索引比聚集索引层次多,添加记录不会引起数据顺序的重组。

       适用环境:

              (1)在查询结果中某此列包含了大量数目不同的值

              (2)查询的结束返回的是少量的结果集

              (3)order by 子句中使用了该列应当优先使用非聚集索引。

       非聚集索引的主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求key是唯一的,而辅助索引的key可以重复。如果我们在非主键列上建立一个辅助索引,则此索引的结构同样也是一颗B+Tree,data域保存数据记录的地址。在搜索数据是会首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其data域的值,然后以data域的值为地址,读取相应数据记录。辅助索引查询一次

图2:

 

2.2 按照逻辑角度分类

       2.2.1 主键索引

       它是一种特殊的唯一索引,不允许有控制,一般是在建表的时候创建主键索引;

       2.2.2 普通索引

       它是一种最基本的索引,没有任何限制;

       2.2.3 唯一索引

      它与普通索引类似,但是唯一索列上的值必须唯一,但是允许有空值;

       注意:

              (1)主键索引一定是唯一索引,但是唯一索引不一定是主键

              (2)一个表中可以有多个唯一性索引,但只能有一个主键

              (3)主键列不允许有空值,但是唯一性索引的列允许有空值

              (4)索引可以提高查询速度

       2.2.4  复合索引(多列索引、联合索引)

       它是在多个字段上创建索引,提高负荷条件查询的速度;满足最左前缀原则[自左向右依次包含];

(三)索引失效

     (1)条件中用or(这就是为什么少用or的原因)

     (2)对于多列(复合、联合)索引,不是使用的第一部分,则不会使用索引。(最左匹配原则或者叫做最左前缀原则)

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

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

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

     (6)判断索引列是否不等于某个值时。‘!=’操作符。比如:select * from SoftWareDetailInfo where SoftUseLine != 0

     (7)对索引列进行运算。这里运算包括+-*/等运算。也包括使用函数。

select * from SoftWareDetailInfo where SoftUseLine +0= 0;//此时索引不起作用。 

select * from SoftWareDetailInfo where count(SoftUseLine) = 0;//此时索引也不起作用。

        也就是说如果不是直接判断索引字段列,而是判断运算或其它函数处理后的索引列索引均不起作用。

     (8)索引字段进行判空查询时。也就是对索引字段判断是否为NULL时。语句为is null 或is not null。

//此时就不检索time字段上的索引表了。也就是索引在这条语句执行时失效了。
select * from SoftWareDetailInfo where CreateTime is null;

//此时就会检索索引表了。索引又起作用了。
select * from SoftWareDetailInfo where CreateTime = '2015-04-11 00:00:00' 

     (9)范围列可以用到索引(联合索引必须是最左前缀),但是范围列后面的列无法用到索引

(四)索引优化

     (1)尽量不要使用左模糊和全模糊,如果需要可以使用搜索引擎来解决;

     (2)union,in和or都可以命中索引,建议使用in;

     (3)负向条件 [!= 、 <> 、 not in  、not like。。] 查询不能使用索引,可以优化为in查询;

将 -> select * from user where status!=1 and status!=2;

优化为 -> select * from user where status in (0,3,4);

     (4)合理使用联合索引的最左前缀原则

 如果在(a,b,c)三个字段上建立联合索引,那么它能够加快 a | (a,b) | (a,b,c) 三组查询速度。

 比如说把(username,password)建立了联合索引,因为业务上几乎没有password的单条件查询,而有很多username的单条件查询需求,所以应该建立(username,password)的联合索引,而不要建立(password,username)的联合索引

     (5)把计算放到业务层而不是数据库层。

因为对索引列进行运算,不能命中索引

     (6)表数据比较少、更新十分频繁、数据区分度不高的字段上不宜建立索引。

       一般区分度在80%以上的时候就可以建立索引,区分度可以使用 count(distinct(列名))/count(*) 来计算。

     (7)强制类型转换会全表扫描

例如:如果phone字段是varchar类型,则下面的sql不能命中索引

select * from user where phone = 18838003017

可以优化为:select * from user where phone = ‘18838003017’

     (8)在order by和group by中要注意索引的有序性

如果order by是组合索引的一部分,应该将该字段放在组合索引的最后

例如:where a=? and b=? order by c ->可以建立联合索引(a,b,c)

如果索引中有范围查找,则索引的有序性无法利用

例如:where a>10 order by b ->索引(a,b)无法排序

     (10)建立索引的列,不许为null

单列索引不存 null 值,复合索引不存全为 null 的值,如果列允许为 null,可能会得到“不符合预期”的结果集,所以,请使用 not null 约束以及默认值。

注意:

     (1)建立联合索引的时候,要把查询频率较高的列放在最左边

     (2)如果建立了(a,b)索引,就不必再独立建立a索引。同理如果建立了(a,b,c)联合索引,就不必再独立建立a,(a,b)索引

     (3)存在非等号和等号混合判断条件时,在建索引时,请把等号条件的列前置。如     where a>? and b=?,那么即使 a 的区分度更高,也必须把 b 放在索引的最前列。

     (4)最左前缀原则,并不是要求where后的顺序和联合索引的一致。下面的 SQL 语句也可以命中 (login_name, passwd) 这个联合索引。

    selectuid, login_time from user where passwd=? andlogin_name=?

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

参考文章:
https://blog.csdn.net/u012071918/article/details/78698838

https://blog.csdn.net/qq_36071795/article/details/83956068

https://www.jianshu.com/p/5681ebd5b0ef

展开阅读全文

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