索引

1、什么是索引

索引是一种利用某种规则的数据结构与实际数据的关系加快数据查找的功能；索引数据节点中有着实际文件的位置，因为索引是根据特定的规则和算法构建的,在查找的时候遵循索引的规则可以快速查找到对应数据的节点，从而达到快速查找数据的效果；其实宏观来说索引其实是一种概念而不是具体的某项技术，只是我们在某个技术中运用得比较广泛和鲜明（比如说数据库）渐渐的有了特定领域的标签，其实在生活中索引的使用无处不在，比如说：书本里的目录；读书时的座位号，考试编号都有类似索引的功能;

总结来所有通过某规则数据结构和实际目标关联，根据特定规则算法快速寻址的功能都可以称之为索引；

2、为什么要用索引，以及索引的原理

首先我们看下在没有索引的情况下是怎么查找数据的：

我们用一个例子来解释比较直观

（1）没有索引的情况下访问数据：

image

（2）使用平衡二叉树结构（请看推荐资料）索引的情况下访问数据：

image

第一张图没有使用索引我们会进行顺序查找，依照数据顺序逐个进行匹配，进行了5次寻址才查询出所需数据，第二张图用了一个简单的平衡二叉树索引之后我们只用了3次，这还是数据量小的情况下，数据量大了效果更明显，所以总结来说创建索引就是为了加快数据查找速度；

3、主要种类

（1）索引结构上主要分为聚簇索引和非聚簇索引，聚簇索引是指索引的结构和排列规则是和实际数据的存储结构和排列规则是一样的（比如说书本的目录和内容就相当于聚簇索引，书本的内容顺序总是和目录顺序一样的）,每个表至多只能创建一个聚簇索引，下图为聚簇索引的一种结构表示；

image

（2）非聚簇索引则可能是通过其他算法规则构成的一种索引结构，索引的结构和实际数据存储的结构是不同的，比如说这张图中的索引类型就属于非聚簇索引;

image

4、索引按数据库功能分类

（1）唯一索引

概念：唯一索引限制了在表对应的唯一索引列上的值是唯一不可重复的；

特点：

1>一个表可以创建多个唯一索引;

2>唯一索引的值允许为null；

3>允许多个列建立组合唯一索引;

（2）主键索引

概念：主键索引属于唯一索引的一个特殊种类，一个表的某列创建了主键索引后会具备唯一索引的功能同时还会对该列生成主键约束,所以简单来说主键索引是一种带有主键约束的唯一索引；

特点：

1>数据库在创建主键同时会自动建立一个唯一索引。

2>每个表最多只能创建一个主键索引；

3>创建了主键索引的列不允许有重复的值，并且不能为null值；

4>创建了主键索引的列可以作为外键;

（3）聚集索引

概念：聚集索引属于聚簇索引的一个种类，在mysql中InnoDB数据引擎模式主索引就是使用的聚集索引，聚集索引的索引结构中就包含了表的数据；

特点：

1>一个表只能创建一个聚集索引;

2>聚集索引尽量建在不会经常发生变动的列上，因为一旦列变动同时也会引索引结构变化，而索引结构中也包含者数据的变动；

3>数据库在创建主键时如果这个表之前没有聚集索引，同时建立主键时候没有强制指定使用非聚集索引，则建立主键时候，同时建立一个唯一的聚集索引

5、什么时候不要用索引，什么时候要用索引

（1）什么场景不要用索引：

1>数据更新性能比查询性能要求要高的情况下不要使用索引，因为数据的更新的同时索引也要进行维护和更新（加了索引查询快但更新就会慢）;

2>不要盲目的给表建太多索引，因为索引本身的存储也要占用存储空间，一旦更新操作频繁反而降低新性能;

3>不要给不经常使用的列建索引，不怎么查询还建索引干嘛;

4>不要给高重复值的列建索引，索引本身就是为了提高查询速度，然而数据值高度重复，数据区别性不高，索引起不了效果）（比如说：性别）;

5>不要给img,tex.bit数据类型使用索引，因为这种字段一般使用很少，数据量太大;

（2）什么场景用索引

1>经常要用于查询的列 where id=?;

2>经常要用于排序(order by)，分组(group by)的列，因为索引已经排好序了;

3>有值唯一性限制的列，比如说主键、用户名；

6、索引在Mysql的使用方式

（1）在Mysql中索引是基于B+树(请看推荐资料)的数据结构实现的;

（2）MyISAM数据引擎，表索引文件和数据文件是分离开的，主索引和辅助索引一样，只是有主键的区别，主索引的键不能重复；InnoDB中表索引本身就是表数据文件，索引节点中存的是表的数据而不是数据引用地址，辅索引完全依赖于主索引，辅索引的每次查找都是找到对应主索引的节点再找到数据;

（3）InnoDB中必须要有一个聚集索引，而聚集索引在基于主键创建的，所以InnoDB的表必须要有主键，如果没有显示指定主键，Mysql会自动创建一个隐式主键；

（4）InnoDB除了主索引（聚集索引）之外的索引都是以辅助索引的形式存在，辅助索引每次检索的时候是通过找到主索引再找到数据;

7、推荐资料

磁盘和主存数据读取原理：http://blog.csdn.net/abcd1101/article/details/55281514

平衡二叉树、B树、B+树：https://zhuanlan.zhihu.com/p/27700617

MySQL索引背后的数据结构及算法原理：http://blog.codinglabs.org/art

以上出自：https://zhuanlan.zhihu.com/p/27789389

阿里规范：

  1. 【强制】业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。

说明：不要以为唯一索引影响了insert速度，这个速度损耗可以忽略，但提高查找速度是明显的；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。

  1. 【强制】超过三个表禁止join。需要join的字段，数据类型必须绝对一致；多表关联查询时，保证被关联的字段需要有索引。

说明：即使双表join也要注意表索引、SQL性能。

  1. 【强制】在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。

说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为20的索引，区分度会高达90%以上，可以使用count(distinct left(列名, 索引长度))/count(*)的区分度来确定。

  1. 【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

说明：索引文件具有B-Tree的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

  1. 【推荐】如果有order by的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现file_sort的情况，影响查询性能。

正例：where a=? and b=? order by c; 索引：a_b_c
反例：索引中有范围查找，那么索引有序性无法利用，如：WHERE a>10 ORDER BY b; 索引a_b无法排序。

  1. 【推荐】利用覆盖索引来进行查询操作，避免回表。

说明：如果一本书需要知道第11章是什么标题，会翻开第11章对应的那一页吗？目录浏览一下就好，这个目录就是起到覆盖索引的作用。

正例：能够建立索引的种类分为主键索引、唯一索引、普通索引三种，而覆盖索引只是一种查询的一种效果，用explain的结果，extra列会出现：using index。

  1. 【推荐】利用延迟关联或者子查询优化超多分页场景。

说明：MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写。

正例：先快速定位需要获取的id段，然后再关联： SELECT a.* FROM 表1 a, (select id from 表1 where 条件 LIMIT 100000,20 ) b where a.id=b.id

  1. 【推荐】 SQL性能优化的目标：至少要达到 range 级别，要求是ref级别，如果可以是consts最好。 说明：

1）consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。 2）ref 指的是使用普通的索引（normal index）。

3）range 对索引进行范围检索。 反例：explain表的结果，type=index，索引物理文件全扫描，速度非常慢，这个index级别比较range还低，与全表扫描是小巫见大巫。

  1. 【推荐】建组合索引的时候，区分度最高的在最左边。

说明：存在非等号和等号混合时，在建索引时，请把等号条件的列前置。如：where c>? and d=? 那么即使c的区分度更高，也必须把d放在索引的最前列，即索引idx_d_c。

正例：如果where a=? and b=? ，如果a列的几乎接近于唯一值，那么只需要单建idx_a索引即可。

  1. 【推荐】防止因字段类型不同造成的隐式转换，导致索引失效。

  2. 【参考】创建索引时避免有如下极端误解：

1）宁滥勿缺。认为一个查询就需要建一个索引。

2）宁缺勿滥。认为索引会消耗空间、严重拖慢更新和新增速度。

3）抵制惟一索引。认为业务的惟一性一律需要在应用层通过“先查后插”方式解决。

以上出自：https://blog.csdn.net/qq_41699100/article/details/86628814

查看索引生效情况： explain

expain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra

概要描述：
id:选择标识符
select_type:表示查询的类型。
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容

  • Mysql、索引
    索引 数据库中的查询操作非常普遍，索引就是提升查找速度的一种手段 索引的类型 从数据结构角度分 1.B+索引：传统...
    一凡呀阅读 3,025评论 0赞 8
  • SQL Server中的索引
    SQL SERVER提供了多种索引。如果以存储结构结构来区分，有聚集索引和非聚集索引；如果以数据的唯一性来区分，则...
    不知名的蛋挞阅读 6,045评论 0赞 5
  • 数据库索引创建原则
    索引的基本原理，以及数据是如何被访问的 （一）SQLS如何访问没有建立索引的数据表 Heap译成汉语叫做“堆”，其...
    安易学车阅读 3,488评论 0赞 8
  • 《高性能MySQL》读后感——聚簇索引
    聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。比如，InnoDB的聚簇索引使用B+Tree的数据结构存储...
    sherlock_6981阅读 1,885评论 0赞 2
  • 《高性能MySQL》读后感——聚簇索引
    聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。比如，InnoDB的聚簇索引使用B+Tree的数据结构存储...
    大头8086阅读 17,529评论 7赞 40