金三银四，每年的这个时候都是求职招聘的黄金期，很不幸，我也加入了这个浩浩荡荡的求职大军中。每每面试，都会问到：你是如何使用索引的？聚集索引和非聚集索引的区别是什么？为什么使用索引会查询快？等等等等一系列的索引问题，恰巧平时使用索引都是哪个字段常用做查询就加上索引，不知其所以然。回来深入研究，把学习笔记和大家分享，希望给你带来帮助。
在学习索引之前，我们抛出以下六个问题，如果你都能解答出来，那这篇文章就不用看了，你的索引已经学成了
为什么要给表加上主键？为什么加索引后会使查询变快？为什么加索引后会使写入、修改、删除变慢？什么情况下要同时在两个字段上建索引？聚合索引为什么比非聚合索引查询快？索引越多越好吗？
索引的原理
一个表中没有主键，数据是无序的放置在磁盘存储器上，一行一行的排列的很整齐，加上了主键，表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构(B+Tree)，换句话说整个表就变成了了一个索引，也就是聚集索引，这就是为什么一个表只能有一个主键，一个表只能有一个聚集索引，因为主键的作用就是把表的数据格式转换成索引的格式放置。
图来源于网络根据索引定位到值所在的叶结点，然后再通过叶结点取到数据行。所以为什么加索引查询效率会高，就是这个原因。
图来源于网络然而，事物都是有两面的，索引能让数据库查询数据的速度上升，而使写入数据的速度下降，原因很简单的，因为平衡树这个结构必须一直维持在一个正确的状态，增删改数据都会改变平衡树各节点中的索引数据内容，破坏树结构，因此，在每次数据改变时，DBMS必须去重新梳理树（索引）的结构以确保它的正确，这会带来不小的性能开销，也就是为什么索引会给查询以外的操作带来副作用的原因(索引不是越多越好的第一个原因)。
非聚集索引和聚集索引一样，同样是采用平衡树作为索引的数据结构。索引树结构中各节点的值来自于表中的索引字段，假如给user表的name字段加上索引，那么索引就是由name字段中的值构成，在数据改变时，DBMS需要一直维护索引结构的正确性。如果给表中多个字段加上索引，那么就会出现多个独立的索引结构，每个索引（非聚集索引）互相之间不存在关联。如下图
图来源于网络每次给字段建一个新索引，字段中的数据就会被复制一份出来，用于生成索引。因此，给表添加索引，会增加表的体积，占用磁盘存储空间，这也就是索引不是越多越好的第二个原因
非聚集索引和聚集索引的区别在于，通过聚集索引可以查到需要查找的数据，而通过非聚集索引可以查到记录对应的主键值，再使用主键的值通过聚集索引查找到需要的数据，如下图
图来源于网络不管以任何方式查询表，最终都会利用主键通过聚集索引来定位到数据，聚集索引（主键）是通往真实数据所在的唯一路径。这就是聚合索引比非聚合索引效率高的原因。
是否不使用聚集索引就能查询出所需要的数据呢？答案是肯定的，这种非主流的方法称之为「覆盖索引」查询，也就是平时所说的复合索引或者多字段索引查询。当为字段建立索引以后，字段中的内容会被同步到索引之中，如果为一个索引指定两个字段，那么这个两个字段的内容都会被同步至索引之中
举例：一个表，有id，姓名，生日，年龄等字段，姓名和生日组成复合索引index_birthday_and_user_name，通过非聚集索引index_birthday_and_user_name查找birthday等于-11-1的叶节点的内容，然而，叶节点中除了有user_name表主键ID的值以外，user_name字段的值也在里面，因此不需要通过主键ID值的查找数据行的真实所在，直接取得叶节点中user_name的值返回即可。通过这种覆盖索引直接查找的方式，可以省略不使用覆盖索引查找的后面两个步骤，大大的提高了查询性能，如下图
图来源于网络使用索引的技巧
1.索引不会包含有NULL的列
只要列中包含有NULL值，都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此符合索引就是无效的。
2.使用短索引
对串列进行索引，如果可以就应该指定一个前缀长度。例如，如果有一个char（）的列，如果在前10个或20个字符内，多数值是唯一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
3.索引列排序
mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么orderby中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作，尽量不要包含多个列的排序，如果需要最好给这些列建复合索引。
4.like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，注意正确的使用方式。like‘%aaa%’不会使用索引，而like‘aaa%’可以使用索引。
5.不要在列上进行运算
6.不使用NOTIN、、！=操作，但,=，=，,=,BETWEEN,IN是可以用到索引的
7.索引要建立在经常进行select操作的字段上。
这是因为，如果这些列很少用到，那么有无索引并不能明显改变查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
8.索引要建立在值比较唯一的字段上。
9.对于那些定义为text、image和bit数据类型的列不应该增加索引。因为这些列的数据量要么相当大，要么取值很少。
10.在where和join中出现的列需要建立索引。
11.where的查询条件里有不等号(wherecolumn!=…),mysql将无法使用索引。
12.如果where字句的查询条件里使用了函数(如：whereDAY(column)=…),mysql将无法使用索引。
13.在join操作中(需要从多个数据表提取数据时)，mysql只有在主键和外键的数据类型相同时才能使用索引，否则及时建立了索引也不会使用
全文索引
旧版的MySQL的全文索引只能用在MyISAM,不过新版的MySQL5.6.24上InnoDB引擎也加入了全文索引
字段类型：char、varchar和text
创建全文索引（1）创建表的同时创建全文索引CREATETABLEarticle(idINTAUTO_INCREMENTNOTNULLPRIMARYKEY,titleVARCHAR(),bodyTEXT,FULLTEXT(title,body))建立的索引名是“title”（2）通过altertable的方式来添加ALTERTABLE`student`ADDFULLTEXTINDEXft_stu_name(`name`)#ft_stu_name是索引名，可以随便起或者：ALTERTABLE`student`ADDFULLTEXTft_stu_name(`name`)（3）直接通过createindex的方式CREATEFULLTEXTINDEXft_email_nameON`student`(`name`)也可以在创建索引的时候指定索引的长度：CREATEFULLTEXTINDEXft_email_nameON`student`(`name`(20)删除全文索引（1）直接使用dropindexDROPINDEX索引名称ON表名;（2）使用altertable的方式ALTERTABLE表名DROPINDEX索引名称;使用全文索引跟普通索引稍有不同，使用全文索引的格式：MATCH(columnName)AGAINST(string)SELECT*FROM`student`WHEREMATCH(`name`)AGAINST(聪)当查询多列数据时：建议在此多列数据上创建一个联合的全文索引，否则使用不了索引的。SELECT*FROM`student`WHEREMATCH(`name`,`address`)AGAINST(聪广东)使用全文索引需要注意的是：(基本单位是词)分词，全文索引以词为基础的，MySQL默认的分词是所有非字母和数字的特殊符号都是分词符(外国人嘛)MySQL中与全文索引相关的几个变量使用命令：mysqlSHOWVARIABLESLIKEft%;#ft就是FullText的简写
ft_boolean_syntax+-()~*:
#改变INBOOLEANMODE的查询字符，不用重新启动MySQL也不用重建索引ft_min_word_len4#最短的索引字符串，默认值为4，(通常改为1)修改后必须重建索引文件，重新建立索引命令：repairtabletablenamequick
ft_max_word_len84#最长的索引字符串，默认值为84，修改后必须重建索引文件
ft_query_expansion_limit20#查询括展时取最相关的几个值用作二次查询
ft_stopword_file(built-in)#全文索引的过滤词文件，共个默认词
（1）ft_boolean_syntax(+-()~*:
)使用的详解
INBOOLEANMODE自己百度查
SELECT*FROM`student`WHEREMATCH(`name`)AGAINST(聪INBOOLEANMODE)
（2）+:用在词的前面，表示一定要包含该词，并且必须在开始位置。
eg:+Apple匹配：Apple,tommy,Apple
（3）-:不包含该词，所以不能单独用，这样是查不到任何row的，必须搭配其他语法使用
eg:MATCH(girl_name)AGAINST(-林志玲+张筱雨)匹配到：所有不包含林志玲，但包含张筱雨的记录
（4）空(也就是默认情况)，表示可选的，包含该词的顺序较高。
applebanana找至少包含上面词中的一个的记录行
+apple+juice两个词均在被包含
+applemac包含词“apple”，但是如果同时包含“mac”，它的排列将更高一些
+apple-macintosh包含“apple”但不包含“macintosh”
（5）:提高该字的相关性，查询的结果会排在比较靠前的位置。
（6）:降低相关性，查询的结果会排在比较靠后的位置。
(7)：可以通过括号来使用字条件。
eg:+aaa+(bbbccc)//找到有aaa和bbb和ccc，aaa和bbb，或者aaa和ccc(因为bbb，ccc前面没有+，所以表示可有可无)，然后aaabbbaaabbbcccaaaccc
（8）~:将其相关性由正转负，表示拥有该字会降低相关性，但不像「-」将之排除，只是排在较后面。
eg:+apple~macintosh先匹配apple，但如果同时包含macintosh，就排名会靠后。
（9）*:通配符，这个只能接在字符串后面。
MATCH(girl_name)AGAINST(+*ABC*)#错误，不能放前面MATCH(girl_name)AGAINST(+张筱雨*)#正确
（10）:整体匹配，用双引号将一段句子包起来表示要完全相符，不可拆字。
eg:tommyhuang可以匹配tommyhuangxxxxx但是不能匹配tommyishuang。
B+树索引和哈希索引的区别
B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针相互链接
哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。
B+树索引和哈希索引的明显区别是：
·如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；当然了，这个前提是，键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据；
·从示意图中也能看到，如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索；
·同理，哈希索引也没办法利用索引完成排序，以及like‘xxx%’这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）；
·哈希索引也不支持多列联合索引的最左匹配规则；
·B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题。
索引为什么使用B+Tree，而不使用二叉树来实现
其实从算法逻辑上讲，二叉查找树的查找速度和比较次数都是最小的，但是从Mysql的角度讲，我们不得不考虑一个现实问题：磁盘IO。
查找都是索引操作，一般来说索引非常大，尤其是关系型数据库这种，当数据量比较大的时候，索引的大小有可能几个G甚至更多，数据量大的索引能达到亿级别，所以为了减少内存的占用，数据库索引是存储在外部磁盘上的。
当我们利用索引查询的时候，不可能把整个索引全部加载到内存，只能逐一加载每个磁盘页，磁盘页对应索引树的节点，为了减少磁盘IO的次数，就需要尽量降低树的高度