MySQL索引机制(详细+原理+解析)

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前后有一个条件的列不是索引，那么查询中将不使用索引

mysql的索引类型及使用索引时的注意事项有：

一、普通索引。这是最基本的索引，它没有任何限制。它有以下几种创建方式：

1、创建索引

代码如下:

CREATE INDEX indexName ON mytable(username(length))

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length，下同。

2、修改表结构

代码如下:

ALTER mytable ADD INDEX [indexName] ON (username(length)) -- 创建表的时候直接指定

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   INDEX [indexName] (username(length))   ) 

-- 删除索引的语法：

DROP INDEX [indexName] ON mytable

二、唯一索引。它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

代码如下:

CREATE UNIQUE INDEX indexName ON mytable(username(length))

-- 修改表结构

ALTER mytable ADD UNIQUE [indexName] ON (username(length))

-- 创建表的时候直接指定

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   UNIQUE [indexName] (username(length))   )

三、主键索引。它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：

代码如下:

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   PRIMARY KEY(ID)   )

当然也可以用 ALTER 命令。记住：一个表只能有一个主键。

四、组合索引。为了形象地对比单列索引和组合索引，为表添加多个字段：

代码如下:

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   city VARCHAR(50) NOT NULL,   age INT NOT NULL  )

为了进一步榨取MySQL的效率，就要考虑建立组合索引。就是将 name, city, age建到一个索引里：

代码如下:

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age)[code]

建表时，usernname长度为 16，这里用 10。这是因为一般情况下名字的长度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，提高INSERT的更新速度。

如果分别在 usernname，city，age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于我们的组合索引。虽然此时有了三个索引，但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。

建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：usernname,city,age   usernname,city   usernname  为什么没有 city，age这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引，下面的几个SQL就会用到这个组合索引：

[code]

SELECT * FROM mytable WHREE username="admin" AND city="郑州"  SELECT * FROM mytable WHREE username="admin"

引入一个面试问题:

看完以下以后再回顾，会发现迎刃而解

Mysql 可以为每一张表设置 存储引擎 这里我们只说 InnoDB 存储引擎.

由于实际情况，数据页只能按照一棵 B+树 进行排序， 因此每张表只能拥有一个 聚集索引（即 主键）。

栗子：

每个叶子节点的索引行中包含了一个书签（bookmark）. 该书签是用来告诉 InnoDB存储引擎哪里可以找到该索引对应的数据行或者说 行数据！ 由于InnoDB存储引擎表， 是按照主键来构建的， 所以 ，该书签内其实包含或者说指向了 数据行所对应的聚集索引键

也就是说 辅助索引的 叶结点保存了 指向对应数据的 聚集索引， 可以通过该聚集索引 找到对应的数据行

辅助索引的存在并不影响数据在聚集索引中的组织，因为每张表上可以有多个辅助索引。

当通过辅助索引来寻找数据时，InnoDB 存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引(聚集索引)的主键，然后再通过聚集索引找到一个完整的数据行。

例如：

聚集索引辅助索引关系:

： 又叫做组合索引 ， 辅助索引的一种 ， 和普通创建索引的方式一样，不同的是 可以同时添加多列来作为索引项

从本质上来说，联合索引也是一课B+树

个人理解： 所谓最左原则， 是因为 存储引擎构建组合索引时 是根据最左边的那一列索引项进行排序的 ，所以使用组合索引，必须满足 条件中必须存在 最左边那一列的索引项，这样 才可以找到对应的索引，继而 去寻找对应的数据

： 又叫做 索引覆盖，InnoDB中支持覆盖索引，即 从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。

比如 这里没有根据最左原则使用组合索引，但是 优化器依然进行选择

共勉，欢迎指导谢谢~

---