一、索引基础知识

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

索引不是万能的！索引可以加快数据检索操作，但会使数据修改操作变慢。每修改数据记录，索引就必须刷新一次。为了在某种程序上弥补这一缺陷，许多SQL命令都有一个DELAY_KEY_WRITE项。这个选项的作用是暂时制止MySQL在该命令每插入一条新记录和每修改一条现有之后立刻对索引进行刷新，对索引的刷新将等到全部记录插入/修改完毕之后再进行。在需要把许多新记录插入某个数据表的场合，DELAY_KEY_WRITE选项的作用将非常明显。

另外，索引还会在硬盘上占用相当大的空间。因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

从理论上讲，完全可以为数据表里的每个字段分别建一个索引，但MySQL把同一个数据表里的索引总数限制为16个。

InnoDB数据表的索引

与MyISAM数据表相比，索引对InnoDB数据的重要性要大得多。在InnoDB数据表上，索引对InnoDB数据表的重要性要在得多。在 InnoDB数据表上，索引不仅会在搜索数据记录时发挥作用，还是数据行级锁定机制的基础。”数据行级锁定”的意思是指在事务操作的执行过程中锁定正在被处理的个别记录，不让其他用户进行访问。这种锁定将影响到(但不限于)SELECT…LOCK IN SHARE MODE、SELECT…FOR UPDATE命令以及INSERT、UPDATE和DELETE命令。出于效率方面的考虑，InnoDB数据表的数据行级锁定实际发生在它们的索引上，而不是数据表自身上。显然，数据行级锁定机制只有在有关的数据表有一个合适的索引可供锁定的时候才能发挥效力。

限制

如果WEHERE子句的查询条件里有不等号(WHERE coloum != …)，MySQL将无法使用索引。

类似地，如果WHERE子句的查询条件里使用了函数(WHERE DAY(column) = …)，MySQL也将无法使用索引。

在JOIN操作中(需要从多个数据表提取数据时)，MySQL只有在主键和外键的数据类型相同时才能使用索引。

如果WHERE子句的查询条件里使用比较操作符LIKE和REGEXP，MySQL只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。比如说，如果查询条件是LIKE ‘abc%’，MySQL将使用索引；如果查询条件是LIKE ‘%abc’，MySQL将不使用索引。

在ORDER BY操作中，MySQL只有在排序条件不是一个查询条件表达式的情况下才使用索引。(虽然如此，在涉及多个数据表查询里，即使有索引可用，那些索引在加快 ORDER BY方面也没什么作用)

如果某个数据列里包含许多重复的值，就算为它建立了索引也不会有很好的效果。比如说，如果某个数据列里包含的净是些诸如”0/1″或”Y/N”等值，就没有必要为它创建一个索引。

1. 存储结构

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

在这里插入图片描述

各个数据页可以组成一个双向链表，而每个数据页中的记录又可以组成一个单向链表。

每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。

以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。所以说，如果我们写

select * from user where username = 'xxxx'

这样没有进行任何优化的sql语句，默认会这样做：

定位到记录所在的页，要遍历双向链表，找到所在的页，从所在的页内中查找相应的记录。

由于不是根据主键查询，只能遍历所在页的单链表了，很明显，在数据量很大的情况下这样查找会很慢！

2. 索引查询为什么快

索引做了些什么可以让我们查询加快速度呢？其实就是将无序的数据变成有序(相对)：在这里插入图片描述

要找到id为8的记录简要步骤：

在这里插入图片描述

很明显的是：没有用索引我们是需要遍历双向链表来定位对应的页，现在通过“目录”就可以很快地定位到对应的页上了！

其实底层结构就是B+树，B+树作为树的一种实现，能够让我们很快地查找出对应的记录。

3. 索引增删改为什么慢

B+树是平衡树的一种。

平衡树：它是一棵空树或它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。如果一棵普通的树在极端的情况下，是能退化成链表的(树的优点就不复存在了)

在这里插入图片描述

B+树是平衡树的一种，是不会退化成链表的，树的高度都是相对比较低的(基本符合矮矮胖胖(均衡)的结构)【这样一来我们检索的时间复杂度就是O(logn)】！从上一节的图我们也可以看见，建立索引实际上就是建立一颗B+树。

B+树是一颗平衡树，如果我们对这颗树增删改的话，那肯定会破坏它的原有结构。要维持平衡树，就必须做额外的工作。正因为这些额外的工作开销，导致索引会降低增删改的速度。

4. 最左匹配原则

最左匹配原则：

索引可以简单如一个列(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肯定是排不了序了)

5. 自动优化顺序

不需要考虑=、in等的顺序，mysql会自动优化这些条件的顺序，以匹配尽可能多的索引列。

比如索引(a, b, c, d)，查询条件写

c > 3 and b = 2 and a = 1 and d < 4
a = 1 and c > 3 and b = 2 and d < 4

这两顺序都是可以的，MySQL会自动优化为a = 1 and b = 2 and c > 3 and d < 4，依次命中a、b、c。

二、索引名词解释

1. 单列索引、组合索引、索引合并

索引分单列索引和组合索引(复合索引)。

单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。
组合索引(复合索引)，即一个索引包含多个列。创建索引时，需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。其效率大于索引合并。索引可以覆盖多个数据列，如像INDEX(columnA, columnB)索引。这种索引的特点是MySQL可以有选择地使用一个这样的索引。如果查询操作只需要用到columnA数据列上的一个索引，就可以使用复合索引INDEX(columnA, columnB)。不过，这种用法仅适用于在复合索引中排列在前的数据列组合。比如说，INDEX(A, B, C)可以当做A或(A, B)的索引来使用，但不能当做B、C或(B, C)的索引来使用。
索引合并指的是使用多个单列索引组合搜索

2. 聚集索引和非聚集索引

非聚集索引也叫做二级索引

聚集索引就是以主键创建的索引，聚集索引在叶子节点存储的是表中的数据
非聚集索引就是以非主键创建的索引，非聚集索引在叶子节点存储的是主键和索引列

使用非聚集索引查询出数据时，拿到叶子上的主键再去查到想要查找的数据。(拿到主键再查找这个过程叫做回表)

非聚集索引在建立的时候也未必是单列的，可以多个列来创建索引。

此时就涉及到了哪个列会走索引，哪个列不走索引的问题了(最左匹配原则) 创建多个单列(非聚集)索引的时候，会生成多个索引树(所以过多创建索引会占用磁盘空间)

在这里插入图片描述

3. 覆盖索引

覆盖索引，select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。

我们前面知道了，如果不是聚集索引，叶子节点存储的是主键+列值，最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢。

覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！

比如说：

现在创建了索引(username,age)，在查询数据的时候：

select username , age from user where username = 'xxxx' and age = 20

很明显地知道，我们上边的查询是走索引的，并且，要查询出的列(username,age这两列)在叶子节点都存在！所以，就不用回表了。

三、索引类型简介

1. Normal 普通索引

普通索引，大多数情况下都可以使用。

普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHEREcolumn=）或排序条件（ORDERBYcolumn）中的数据列创建索引。根据MySQL引擎(InnoDB或者MyISAM )不同，非主键索引的工作方式不同。

InnoDB是聚集索引，非主键索引存放的是主键索引的值
MyISAM是非聚集索引，非主键索引存放的是数据的地址

2. Unique 唯一索引

表示唯一的，不允许重复的索引，如果该字段信息保证不会重复例如身份证号用作索引时，可设置为unique。

这么做的好处：

简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；
MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。比如我想要一个userId对应数据库中的一条记录，那么可以跟userId建立唯一索引。

3. Full Text 全文索引

文本字段上的普通索引只能加快对出现在字段内容最前面的字符串(也就是字段内容开头的字符)进行检索操作。如果字段里存放的是由几个、甚至是多个单词构成的较大段文字，普通索引就没什么作用了。这种检索往往以LIKE %word%的形式出现，这对MySQL来说很复杂，如果需要处理的数据量很大，响应时间就会很长。

仅可用于 MyISAM 表，InnoDB数据表不支持全文索引, 用于在一篇文章中，检索文本信息的, 针对较大的数据，生成全文索引很耗时空间。不过 FULLTEXT 用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。比如有一个字段是备注，可能查找的时候，会把这个备注当做条件，那么可以在这个备注上面做全文索引

4. SPATIAL 空间索引

MySQL包含OpenGIS 类相对应的数据类型，其中一些类型保存单一几何值

GEOMETRY 几何体
POINT 点
LINESTRING 线
POLYGON 多边形

普通索引对于一维数据(key->data)是无往不利，可是面对空间数据(lon,lat -> data)就有些无能为力了，如果查询(116.27636, 40.041285)附近的点：空间索引通过四叉树、R 树等数据结构，还有 GeoHash 算法将二维数据转化为一维使用普通B树索引来实现，它们都能实现对空间范围内的快速搜索。

创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建

四、索引方法简介

1. BTREE B+树索引

BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉平衡树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型，上面基础知识部分有介绍。

2. HASH 哈希索引

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，根据这个哈希值来定位，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。

哈希索引没办法利用索引完成排序
不支持最左匹配原则
在有大量重复键值情况下，哈希索引的效率也是极低的---->哈希碰撞问题。
不支持范围查询

在这里插入图片描述

五、创建索引的注意事项

1. 最左前缀匹配原则

非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。

比如a 1="" and="" b="2" c=""> 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2. =和in可以乱序

比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

3. 尽量选择区分度高的列作为索引

区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少。

唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

4. 索引列不能参与计算，保持列“干净”

比如 from_unixtime(create_time) = ’2014-05-29’ 就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成 create_time = unix_timestamp(’2014-05-29’);

5. 尽量的扩展索引，不要新建索引

比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

6. 命名规则

主键索引：pk_
唯一索引：uk_
普通索引：idx_

六、添加索引的三种方法

1. 新建表中添加索引

普通索引

CREATE TABLE t_dept (
	NO INT NOT NULL PRIMARY KEY,
	NAME VARCHAR ( 20 ) NULL,
	sex VARCHAR ( 2 ) NULL,
	info VARCHAR ( 20 ) NULL,
INDEX index_no ( NO ) 
)

唯一索引

CREATE TABLE t_dept (
	NO INT NOT NULL PRIMARY KEY,
	NAME VARCHAR ( 20 ) NULL,
	sex VARCHAR ( 2 ) NULL,
info VARCHAR ( 20 ) NULL,
UNIQUE INDEX index_no ( NO ) ）

全文索引

CREATE TABLE t_dept (
	NO INT NOT NULL PRIMARY KEY,
	NAME VARCHAR ( 20 ) NULL,
	sex VARCHAR ( 2 ) NULL,
	info VARCHAR ( 20 ) NULL,
FULLTEXT INDEX index_no (
NO)

多列索引

CREATE TABLE t_dept (
	NO INT NOT NULL PRIMARY KEY,
	NAME VARCHAR ( 20 ) NULL,
	sex VARCHAR ( 2 ) NULL,
	info VARCHAR ( 20 ) NULL,
KEY index_no_name ( NO, NAME ) 
)

1. 已建表中添加索引

普通索引

ALTER TABLE t_dept ADD INDEX index_name ( NAME );

唯一索引

ALTER TABLE t_dept ADD UNIQUE INDEX index_name ( NAME );

全文索引

ALTER TABLE t_dept ADD FULLTEXT index_name ( NAME );

多列索引

ALTER TABLE t_dept ADD INDEX index_name_no ( NAME, NO );

再过30分钟你就能了解MySQL中的索引啦

一、索引基础知识

1. 存储结构

2. 索引查询为什么快

3. 索引增删改为什么慢

4. 最左匹配原则

5. 自动优化顺序

二、索引名词解释

1. 单列索引、组合索引、索引合并

2. 聚集索引和非聚集索引

3. 覆盖索引

三、 索引类型简介

1. Normal 普通索引

2. Unique 唯一索引

3. Full Text 全文索引

4. SPATIAL 空间索引

四、索引方法简介

1. BTREE B+树索引

2. HASH 哈希索引

五、创建索引的注意事项

1. 最左前缀匹配原则

2. =和in可以乱序

3. 尽量选择区分度高的列作为索引

4. 索引列不能参与计算，保持列“干净”

5. 尽量的扩展索引，不要新建索引

6. 命名规则

六、添加索引的三种方法

1. 新建表中添加索引

1. 已建表中添加索引

三、索引类型简介