引言

说白了,数据库的索引问题就是查找问题

数据库索引,是数据库管理系统中一个排序的数据结构,以协助快速查询,更新数据库中表的数据.索引的实现通常使用B树和变种的B+树(mysql常用的索引就是B+树)

除了数据之外,数据库系统还维护为满足特定查找算法的数据结构,这些数据结构以某种方式引用数据.这种数据结构就是索引

创建索引的好处

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

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

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

创建索引的坏处

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

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

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

应该在哪些列上创建索引呢

①经常需要搜索的列上

②作为主键的列上

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

④经常需要根据范围进行搜索的列上

⑤经常需要排序的列上

⑥经常使用在where子句上面的列上

不应该在哪些列上创建索引

①查询中很少用到的列

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

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

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

索引的分类和使用

按物理存储角度分：

聚集索引

表记录的排列顺序和索引的排列顺序一致,所以查询效率快,只要找到第一个索引值记录,其余连续性的记录在物理上一样连续存放.聚集索引的缺点就是修改慢,因为为了使表记录和索引的排列顺序一致,在插入记录的时候,会对数据页重新排序

非聚集索引

表记录和索引的排列顺序不一定一致,两种索引都采用B+树的结构,非聚集索引的叶子层并不和实际数据页相重叠,而采用叶子层包含一个指向表记录的指针.非聚集索引层次多,不会造成数据重排

按逻辑角度分



2）普通索引，最基本的索引，它没有任何的限制





4）复合索引（又叫做多列索引，联合索引）：多个字段上建立的索引，提高复合条件查询的速度



创建联合索引：create index idx_name_age on student(name,age);

查看索引：show index from student;









































数据库索引在什么情况下失效

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



（2）

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

比如：Index_SoftWareDetail索引包含（a，b，c） 三列，但是查询条件里面，没有a，b 列，只有c 列，那么 Index_SoftWareDetail索引也不起作用。

例如：bc   c   acb bac 都是不行的

（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。

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

　　接着再执行

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

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

索引的优化

①尽量不要使用左模糊和全模糊，如果需要可以使用搜索引擎来解决

②union,in和or都可以命中索引，建议使用in

③负向条件查询不能使用索引，可以优化为in查询

负向条件查询有：!=  <>  not in  not like等等

例如：select * from user where status!=1 and status!=2

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

④合理使用联合索引的最左前缀原则

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

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

注意：（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=?
但还是建议 where 后的顺序和联合索引一致，养成好习惯。

⑤把计算放到业务层而不是数据库层。（因为对索引列进行运算，不能命中索引）

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

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

⑦强制类型转换会全表扫描

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

select * from user where phone = 18838003017

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

⑧利用覆盖索引进行查询操作，避免回表

select uid,login_time from user where username=? and password=?

如果建立了(username,password,login_time)的联合索引,由于login_time已经建立在索引中了,被查询的username和password就不用去row上获取数据了,从而加速查询

⑨在order by和group by中要注意索引的有序性

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

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

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

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

⑩建立索引的列,不许为null

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

sql语句的优化

①能用到索引尽量用到索引.对索引的优化实际上就是sql语句的调优

②任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

③尽量使用where,而不要使用having

④尽量使用多表查询,不要使用子查询

⑤where后的and.or左右执行顺序是从右至左

运算符为and时--尽量把为假的放在右边

运算符为or时--尽量把为真的放在右边

 

 

  
点赞再看，养成习惯，微信搜索【三太子敖丙】我所有文章都在这里，本文 GitHub https://github.com/JavaFamily 已收录，有一线大厂面试完整考点，文末有福利。

前言
写数据库，我第一时间就想到了MySQL、Oracle、索引、存储过程、查询优化等等。
不知道大家是不是跟我想得一样，我最想写的是索引，为啥呢？
以下这个面试场景，不知道大家熟悉不熟悉：
面试官：数据库有几千万的数据，查询又很慢我们怎么办？
面试者：加索引。
面试官：那索引有哪些数据类型？索引是怎么样的一种结构？哪些字段又适合索引呢？B+的优点？聚合索引和非聚合索引的区别？为什么说索引会降低插入、删除、修改等维护任务的速度？……..
面试者：面试官怎么出我们公司门来着😂。
是的大家可能都知道慢了加索引，那为啥加，在什么字段上加，以及索引的数据结构特点，优点啥的都比较模糊或者甚至不知道。
那我们也不多BB了，直接开始这次的面试吧。
正文

  
我看你简历上写到了熟悉MySQL数据库以及索引的相关知识，我们就从索引开始，索引有哪些数据结构？

Hash、B+
大家去设计索引的时候，会发现索引类型是可以选择的。


  
为什么哈希表、完全平衡二叉树、B树、B+树都可以优化查询，为何Mysql独独喜欢B+树？

我先聊一下Hash：
大家可以先看一下下面的动图

注意字段值所对应的数组下标是哈希算法随机算出来的，所以可能出现哈希冲突。
那么对于这样一个索引结构，现在来执行下面的sql语句： 
select * from sanguo where name='鸡蛋'
可以直接对‘鸡蛋’按哈希算法算出来一个数组下标，然后可以直接从数据中取出数据并拿到所对应那一行数据的地址，进而查询那一行数据， 那么如果现在执行下面的sql语句：
select * from sanguo where name>'鸡蛋'
则无能为力，因为哈希表的特点就是可以快速的精确查询，但是不支持范围查询。
如果做成了索引，那速度也是很慢的，要全部扫描。

  
问个题外话，那Hash表在哪些场景比较适合？

等值查询的场景，就只有KV（Key，Value）的情况，例如Redis、Memcached等这些NoSQL的中间件。

  
你说的是无序的Hash表，那有没有有序的数据结构？

有序数组，它就比较优秀了呀，它在等值查询的和范围查询的时候都很Nice。

  
那它完全没有缺点么？

不是的，有序的适合静态数据，因为如果我们新增、删除、修改数据的时候就会改变他的结构。
比如你新增一个，那在你新增的位置后面所有的节点都会后移，成本很高。

  
那照你这么说他根本就不优秀啊，特点也没地方放。

此言差矣，可以用来做静态存储引擎啊，用来保存静态数据，例如你2019年的支付宝账单，2019年的淘宝购物记录等等都是很合适的，都是不会变动的历史数据。

  
有点东西啊小伙子，那二叉树呢？

二叉树的新增和结构如图：

二叉树的结构我就不在这里多BB了，不了解的朋友可以去看看数据结构章节。
二叉树是有序的，所以是支持范围查询的。
但是他的时间复杂度是O(log(N))，为了维持这个时间复杂度，更新的时间复杂度也得是O(log(N))，那就得保持这棵树是完全平衡二叉树了。

  
怎么听你一说，平衡二叉树用来做索引还不错呢？

此言差矣，索引也不只是在内存里面存储的，还是要落盘持久化的，可以看到图中才这么一点数据，如果数据多了，树高会很高，查询的成本就会随着树高的增加而增加。
为了节约成本很多公司的磁盘还是采用的机械硬盘，这样一次千万级别的查询差不多就要10秒了，这谁顶得住啊？

  
如果用B树呢？

同理来看看B树的结构：

可以发现同样的元素，B树的表示要比完全平衡二叉树要“矮”，原因在于B树中的一个节点可以存储多个元素。
B树其实就已经是一个不错的数据结构，用来做索引效果还是不错的。

  
那为啥没用B树，而用了B+树？

一样先看一下B加的结构：

我们可以发现同样的元素，B+树的表示要比B树要“胖”，原因在于B+树中的非叶子节点会冗余一份在叶子节点中，并且叶子节点之间用指针相连。

  
那么B+树到底有什么优势呢？

其实很简单，我们看一下上面的数据结构，最开始的Hash不支持范围查询，二叉树树高很高，只有B树跟B+有的一比。
B树一个节点可以存储多个元素，相对于完全平衡二叉树整体的树高降低了，磁盘IO效率提高了。
而B+树是B树的升级版，只是把非叶子节点冗余一下，这么做的好处是为了提高范围查找的效率。
提高了的原因也无非是会有指针指向下一个节点的叶子节点。
小结：到这里可以总结出来，Mysql选用B+树这种数据结构作为索引，可以提高查询索引时的磁盘IO效率，并且可以提高范围查询的效率，并且B+树里的元素也是有序的。

  
那么，一个B+树的节点中到底存多少个元素最合适你有了解过么？

额这个这个？卧*有点懵逼呀。
过了一会还是没想出，只能老实交代：这个不是很了解咳咳。

  
你可以换个角度来思考B+树中一个节点到底多大合适？

B+树中一个节点为一页或页的倍数最为合适。

  
为啥？

因为如果一个节点的大小小于1页，那么读取这个节点的时候其实也会读出1页，造成资源的浪费。
如果一个节点的大小大于1页，比如1.2页，那么读取这个节点的时候会读出2页，也会造成资源的浪费。
所以为了不造成浪费，所以最后把一个节点的大小控制在1页、2页、3页、4页等倍数页大小最为合适。

  
你提到了页的概念，能跟我简单说一下么？

首先Mysql的基本存储结构是页(记录都存在页里边)：



各个数据页可以组成一个双向链表
而每个数据页中的记录又可以组成一个单向链表
- 每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录
以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。

所以说，如果我们写 select * from user where username='丙丙'这样没有进行任何优化的sql语句，默认会这样做：

定位到记录所在的页
- 需要遍历双向链表，找到所在的页
从所在的页内中查找相应的记录
- 由于不是根据主键查询，只能遍历所在页的单链表了

很明显，在数据量很大的情况下这样查找会很慢！看起来跟回表有点点像。

  
哦？回表你聊一下。

卧槽，该死，我嘴干嘛。
回表大概就是我们有个主键为ID的索引，和一个普通name字段的索引，我们在普通字段上搜索：
select * from table where name = '丙丙'
执行的流程是先查询到name索引上的“丙丙”，然后找到他的id是2，最后去主键索引，找到id为2对应的值。
回到主键索引树搜索的过程，就是回表。不过也有方法避免回表，那就是覆盖索引。

  
哦？那你再跟我聊一下覆盖索引呗？

！！！ 我这个嘴。。。
这个其实比较好理解，刚才我们是 select * ，查询所有的，我们如果只查询ID那，其实在Name字段的索引上就已经有了，那就不需要回表了。
覆盖索引可以减少树的搜索次数，提升性能，他也是我们在实际开发过程中经常用来优化查询效率的手段。
很多联合索引的建立，就是为了支持覆盖索引，特定的业务能极大的提升效率。

  
索引的最左匹配原则知道么？

最左匹配原则：

索引可以简单如一个列 (a)，也可以复杂如多个列 (a,b,c,d)，即联合索引。
如果是联合索引，那么key也由多个列组成，同时，索引只能用于查找key是否存在（相等），遇到范围查询 (>、<、between、like左匹配)等就不能进一步匹配了，后续退化为线性查找。
因此，列的排列顺序决定了可命中索引的列数。

例子：

如有索引 (a,b,c,d)，查询条件 a=1 and b=2 and c>3 and d=4，则会在每个节点依次命中a、b、c，无法命中d。(c已经是范围查询了，d肯定是排不了序了)

总结
索引在数据库中是一个非常重要的知识点！
上面谈的其实就是索引最基本的东西，N叉树，跳表、LSM我都没讲，同时要创建出好的索引要顾及到很多的方面：

最左前缀匹配原则。这是非常重要、非常重要、非常重要（重要的事情说三遍）的原则，MySQL会一直向右匹配直到遇到范围查询 （>,<,BETWEEN,LIKE）就停止匹配。
尽量选择区分度高的列作为索引，区分度的公式是 COUNT(DISTINCT col)/COUNT(*)。表示字段不重复的比率，比率越大我们扫描的记录数就越少。
索引列不能参与计算，尽量保持列“干净”。比如， FROM_UNIXTIME(create_time)='2016-06-06' 就不能使用索引，原因很简单，B+树中存储的都是数据表中的字段值，但是进行检索时，需要把所有元素都应用函数才能比较，显然这样的代价太大。所以语句要写成 ： create_time=UNIX_TIMESTAMP('2016-06-06')。
尽可能的扩展索引，不要新建立索引。比如表中已经有了a的索引，现在要加（a,b）的索引，那么只需要修改原来的索引即可。
单个多列组合索引和多个单列索引的检索查询效果不同，因为在执行SQL时，MySQL只能使用一个索引，会从多个单列索引中选择一个限制最为严格的索引(经指正，在MySQL5.0以后的版本中，有“合并索引”的策略，翻看了《高性能MySQL 第三版》，书作者认为：还是应该建立起比较好的索引，而不应该依赖于“合并索引”这么一个策略)。
“合并索引”策略简单来讲，就是使用多个单列索引，然后将这些结果用“union或者and”来合并起来

思路文献参考：
《MySQL实战》
《高性能MySQL》
最后部分内容来自->java3y《索引和锁》
丁奇《MySQL实战》
絮叨
之前在B站传了第一个视频：

大家反馈效果还是ok的，我后续会多多尝试的，也希望把改进的建议留言反馈给我。
我去年拍摄了第一个超级粗糙的vlog：

因为拍摄剪辑手法都很垃圾，我就删了，但是最近又想着放上去，在纠结哈哈，想看留个言我就传了哈哈，我们下期间。

今天丙丙也开始了来杭16天后的第一次上班，很开心我们公司在杭州第一批复工的名单中，我已经16天没和人这样说过话了，太开心了，不过不能开空调还得开窗户通风，真的是超级超级冷。
这熟悉的工位，这熟悉的显示器，我的眼角又……
Tip：本来有很多我准备的资料的，但是都是外链，或者不合适的分享方式，博客的运营小姐姐提醒了我，所以大家去公众号回复【资料】好了。

白嫖不好，创作不易，各位的点赞就是丙丙创作的最大动力，我们下篇文章见！
持续更新，未完待续……


絮叨
另外，敖丙把自己的面试文章整理成了一本电子书，共 1630页！目录如下

现在免费送给大家，在我的公众号三太子敖丙回复 【888】 即可获取。

我是敖丙，一个在互联网苟且偷生的程序员。
你知道的越多，你不知道的越多，人才们的  【三连】 就是丙丙创作的最大动力，我们下期见！
注：如果本篇博客有任何错误和建议，欢迎人才们留言！


文章持续更新，可以微信搜索「 三太子敖丙 」第一时间阅读，回复【资料】有我准备的一线大厂面试资料和简历模板，本文 GitHub https://github.com/JavaFamily 已经收录，有大厂面试完整考点，欢迎Star。

这个是从博客园上面看到的文章，感觉对数据库索引解释的很好，总共有四篇文章。

1.数据库索引技术—索引介绍

2.数据库索引技术—聚集索引和非聚集索引介绍

3.数据库索引技术—聚集索引和非聚集索引的区别和建立原则

4.数据库索引技术—复合索引介绍

文章地址：https://www.cnblogs.com/ashou706/archive/2010/06.html

什么是数据库索引？
数据库索引是对数据库表中一列或多列进行排序的一种结构，使用索引可以快速访问数据库表中的特定信息。数据库索引就像书的目录，能加快数据库的查询速度。
索引分为聚簇索引（聚集索引）和非聚簇索引（非聚集索引）。聚簇索引是按照数据存放的物理位置为顺序的，聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。
索引包含从表或视图中一个或多个列生成的键，以及映射到指定数据的存储位置的指针。通过创建良好的索引以支持查询，可以显著提高数据库查询和应用程序的性能。索引可以减少为返回结果集而必须读取的数据量。索引还可以强制表中的行具有唯一性，从而确保表的数据完整性。

优缺点？
建立索引的目的是加快对表中记录的查找或排序。为表设置索引是要付出代价的：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间（因为索引也要随之变动）。
优点：创建索引可以大大提高系统的性能。
   1、通过创建唯一性索引，可以保证数据库表中每一条数据的唯一性。
   2、可以大大加快数据库的检索速度，这也是创建数据库索引的主要原因。
   3、可以加快表和表之间的连接，特别是在实现数据库的参照完整性方面。
   4、在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。
   5、通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统性能。
缺点：
          1、创建索引和维护索引要耗费时间，这种时间随着数据的增加而增加。
   2、索引需要占物理空间，除了数据库表占数据空间外，每一个索引还要占一定的物理空间，如果要建立聚集索引，那么需要的空间会更大。
   3、当对表中数据进行增加删除和修改时，索引也需要动态的维护，这样就降低了数据的维护速度。
聚集索引和非聚集索引
聚集索引是根据数据行的键值在表或视图中排序和存储这些数据行。索引定义中包含聚集索引列。每个表只能有一个聚集索引列。因为数据行本身只能按一个顺序排序。只有当表包行聚集索引时，表中的数据行才按排序顺序存储。
如果表具有聚集索引，则该表称为聚集表。如果表没有索引，则其数据存在一个成为堆的无序结构中。
非聚集索引具有独立于数据行的结构。非聚集索引包行非聚集索引键值，并且每个键值顶部都有指向包行该键值的数据行的指针。从非聚集索引中的索引行指向数据行的指针成为行定位器。行定位器的结构取决于数据页是存储在堆中还是聚集表中。对于堆，行定位器是指向行的指针。对于聚集表，行定位器是聚集索引键。

创建索引的注意事项
应该创建索引的这些列具有以下特点：
1、在经常需要搜索的列上。可以加快搜索的速度。
2、在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
3、在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的。
4、在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的的排序，加快排序查询的时间
5、在经常使用WHERE子句的适合
不应该创建索引的列：
1、对于那些在查询中很少使用或者参考的列不应该创建索引。既然这些这些列很少使用到，因此有索引或无索引并不能提高查询速度。相反，由于增加了索引，反而降低了系统维护速度还增大了空间需求。
2、对于那些只有很少数据值的列也不应该增加索引。
3、对于那些定义为text，image，和bit数据类型的不应该增加索引。因为这种列的数据量要么相当大，要么取值很少，不利于使用索引	。
4、当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的，当增加索引时，会提高检索性能，但是会降低修改性能。

优化数据库索引
1、重新组织和重新生成索引
2、无论何时对基础数据执行插入、更新或删除造作操作，SQLServer数据库引擎都会自动维护索引。随着时间的推移，这些修改可能会导致索引中的信息分散在数据库中（含有碎片）。当索引包含的页中逻辑排序与数据文件中的物理排序不匹配时，就存在碎片。碎片非常多的可能会降低查询性能，导致应用程序响应缓慢。
可以用dbcc showconting（表名） 来查看碎片信息。

减少索引碎片
1、删除并重新创建聚集索引。
2、重新创建聚集索引将对数据进行重新分布，从而使数据页填满。填充度可以使用 CREATE INDEX 中的 FILLFACTORY选项进行配置。这种方法的缺点是索引在删除和重新创建期间为脱机状态，并且操作属原子级。如果中断索引创建，则不能重新创建索引。
3、使用DBCC INDEXDEFRAG按逻辑顺序重新排序索引的叶级叶。由于这是联机操作，因此在语句运行时仍可以使用索引。中断此操作时不会丢失已经完成的任务。此方法的缺点是在重新组织数据方面不如索引重新生成的操作的效果好，而且不更新统计信息。

创建数据库作业的前提
在创建作业前，需要SQLServer的代理服务（SQLServer Agent 服务）正在运行。
SQLServer 代理可以通过SQLServer Management Studio快速停止和启动。重新启动SQLServer Agent服务。

代理作业流程
创建名称；添加步骤；计划时间；	执行。