mysql索引

在mysql中，索引是一种特殊的数据库结构，由数据表中的一列或多列组合而成，可以用来快速查询数据表中有某一特定值的记录。

通过索引，查询数据时不用读完记录的所有信息，而只是查询索引列即可。

通过索引，查询数据时不用读完记录的所有信息，而只是查询索引列。否则，数据库系统将读取每条记录的所有信息进行匹配。

可以把索引比作新华字典的音序表。例如，要查“库”字，如果不使用音序，就需要从字典的 400 页中逐页来找。但是，如果提取拼音出来，构成音序表，就只需要从 10 多页的音序表中直接查找。这样就可以大大节省时间。

因此，使用索引可以很大程度上提高数据库的查询速度，还有效的提高了数据库系统的性能。

索引的优缺点

索引有其明显的优势，也有其不可避免的缺点。

优点

索引的优点如下：

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

2、可以给所有的 MySQL 列类型设置索引。

3、可以大大加快数据的查询速度，这是使用索引最主要的原因。

4、在实现数据的参考完整性方面可以加速表与表之间的连接。

5、在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间

缺点

增加索引也有许多不利的方面，主要如下：

1、创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加。

2、索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。

3、当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。

使用索引时，需要综合考虑索引的优点和缺点。

面试时候经常会被问到mysql的索引结构，B+树相较二叉树，红黑树的优势等问题，接下来就分析下这些问题。

首先，让我们先看一张图：

从图中可以看到，我们为 user 表（用户信息表）建立了一个二叉查找树的索引。

图中的圆为二叉查找树的节点，节点中存储了键（key）和数据（data）。键对应 user 表中的 id，数据对应 user 表中的行数据。

二叉查找树的特点就是任何节点的左子节点的键值都小于当前节点的键值，右子节点的键值都大于当前节点的键值。顶端的节点我们称为根节点，没有子节点的节点我们称之为叶节点。

如果我们需要查找 id=12 的用户信息，利用我们创建的二叉查找树索引，查找流程如下：

利用二叉查找树我们只需要 3 次即可找到匹配的数据。如果在表中一条条的查找的话，我们需要 6 次才能找到。

上面我们讲解了利用二叉查找树可以快速的找到数据。但是，如果上面的二叉查找树是这样的构造：

这个时候可以看到我们的二叉查找树变成了一个链表。如果我们需要查找 id=17 的用户信息，我们需要查找 7 次，也就相当于全表扫描了。 导致这个现象的原因其实是二叉查找树变得不平衡了，也就是高度太高了，从而导致查找效率的不稳定。为了解决这个问题，我们需要保证二叉查找树一直保持平衡，就需要用到平衡二叉树了。 平衡二叉树又称 AVL 树，在满足二叉查找树特性的基础上，要求每个节点的左右子树的高度差不能超过 1。

下面是平衡二叉树和非平衡二叉树的对比：

由平衡二叉树的构造我们可以发现第一张图中的二叉树其实就是一棵平衡二叉树。

平衡二叉树保证了树的构造是平衡的，当我们插入或删除数据导致不满足平衡二叉树不平衡时，平衡二叉树会进行调整树上的节点来保持平衡。具体的调整方式这里就不介绍了。平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。

因为内存的易失性。一般情况下，我们都会选择将 user 表中的数据和索引存储在磁盘这种外围设备中。但是和内存相比，从磁盘中读取数据的速度会慢上百倍千倍甚至万倍，所以，我们应当尽量减少从磁盘中读取数据的次数。另外，从磁盘中读取数据时，都是按照磁盘块来读取的，并不是一条一条的读。如果我们能把尽量多的数据放进磁盘块中，那一次磁盘读取 *** 作就会读取更多数据，那我们查找数据的时间也会大幅度降低。如果我们用树这种数据结构作为索引的数据结构，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块。我们都知道平衡二叉树可是每个节点只存储一个键值和数据的。那说明什么？说明每个磁盘块仅仅存储一个键值和数据！那如果我们要存储海量的数据呢？

可以想象到二叉树的节点将会非常多，高度也会极其高，我们查找数据时也会进行很多次磁盘 IO，我们查找数据的效率将会极低！

为了解决平衡二叉树的这个弊端，我们应该寻找一种单个节点可以存储多个键值和数据的平衡树。也就是我们接下来要说的 B 树。

B 树（Balance Tree）即为平衡树的意思，下图即是一棵 B 树：

图中的 p 节点为指向子节点的指针，二叉查找树和平衡二叉树其实也有，因为图的美观性，被省略了。

图中的每个节点称为页，页就是我们上面说的磁盘块，在 MySQL 中数据读取的基本单位都是页，所以我们这里叫做页更符合 MySQL 中索引的底层数据结构。

从上图可以看出，B 树相对于平衡二叉树，每个节点存储了更多的键值（key）和数据（data），并且每个节点拥有更多的子节点，子节点的个数一般称为阶，上述图中的 B 树为 3 阶 B 树，高度也会很低。

基于这个特性，B 树查找数据读取磁盘的次数将会很少，数据的查找效率也会比平衡二叉树高很多。

假如我们要查找 id=28 的用户信息，那么我们在上图 B 树中查找的流程如下：

B+ 树是对 B 树的进一步优化。让我们先来看下 B+ 树的结构图：

根据上图我们来看下 B+ 树和 B 树有什么不同：

通过上图可以看到，在 InnoDB 中，我们通过数据页之间通过双向链表连接以及叶子节点中数据之间通过单向链表连接的方式可以找到表中所有的数据。

MyISAM 中的 B+ 树索引实现与 InnoDB 中的略有不同。在 MyISAM 中，B+ 树索引的叶子节点并不存储数据，而是存储数据的文件地址。

摘自： http://www.liuzk.com/410.html

MySQL 前缀索引能有效减小索引文件的大小，提高索引的速度。但是前缀索引也有它的坏处：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)。

集一个索引包含多个列（最左前缀匹配原则）

索引列的值必须唯一，但允许有空值

全文索引为FUllText，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值，全文索引可以在CHAR,VARCHAR,TEXT类型列上创建

设定主键后数据会自动建立索引，InnoDB为聚簇索引

即一个索引只包含单个列，一个表可以有多个单列索引

覆盖索引是指一个查询语句的执行只用从所有就能够得到，不必从数据表中读取，覆盖索引不是索引树，是一个结果，当一条查询语句符合覆盖索引条件时候，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后的回表 *** 作，减少了I/O效率

查看索引

列名解析：

删除索引

查看：

删除前:

删除后：

普通的索引，没有什么介绍

查看:（注意和前缀索引Sub_part的区别）

当索引的列是unique的时候，会生成唯一索引，唯一索引关于null有下列两种情况

SQLSERVER 下的唯一索引的列，允许null值，但最多允许有一个空值

MYSQL下的唯一索引的列，允许null值，并且允许多个空值

查看:

会建立两个索引，一个非聚簇索引，一个是唯一索引

结果:

可以插入两个空值（明人不说暗话，我喜欢MySQL）

一方面，它不会索引所有字段所有字符，会减小索引树的大小.

另外一方面，索引只是为了区别出值，对于某些列，可能前几位区别很大，我们就可以使用前缀索引。

一般情况下某个前缀的选择性也是足够高的，足以满足查询性能。对于BLOB，TEXT，或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。

查看：

查看：

复合索引的最左前缀匹配原则 ：

对于复合索引，查询在一定条件才会使用该索引

减少开销。 建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写 *** 作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！

覆盖索引。 对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io *** 作。减少io *** 作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。

效率高。 索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w10% 10% *10%=1w。

在模糊搜索中很有效，搜索全文中的某一个字段，可以参考这篇博文

: https://zhuanlan.zhihu.com/p/88275060

我们先进行下面一个实验看看InnoDB下的主键索引的一个现象。

查看:

我们插入进去的时候，数据的id都是乱序的，为什么这里最后select查询出来的结果都是进行了排序？

这是因为InnoDB索引底层实现的是B+tree，B+tree具有下列的特点：

所以上面的排序是为了使用B+tree的结构 ，B+tree为了范围搜索，将主键按照从小到大排序后，拆分成节点。后续还有新的节点进入的时候，和B-tree相同的 *** 作，会进行分裂。

一般来说，聚簇索引的B+tree都是三层

InnoDB中主键索引一定是聚簇索引，聚簇索引一定是主键索引。

为什么这里辅助索引叶子结点不直接存储数据呢？

MYISAM只有非聚簇索引，索引最终指向的都是物理地址。

Q:既然有回表的存在，那么聚簇索引的优势在哪里？

Q:主键索引作为聚簇索引需要注意什么

在查询语句中使用LIke关键字进行查询时，如果匹配字符串的第一个字符为"%"，索引不会使用。如果“%”不是在第一位，索引就会使用

多列索引是在表的多个字段上创建的索引，满足最左前缀匹配原则，索引才会被使用

查询语句只有Or关键字时候，如果OR前后的两个条件都是索引，这这次查询将会使用索引，否则Or前后有一个条件的列不是索引，那么查询中将不使用索引

---