MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。
拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

索引分单列索引和组合索引。

  • 单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。
  • 组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
上面都在说使用索引的好处，但过多的使用索引将会造成滥用。

因此索引也会有它的缺点：

虽然索引大大提高了查询速度，同时却会降低更新表的速度，
如对表进行INSERT、UPDATE和DELETE。
因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。

索引的分类

在一个表中，主键索引只能有一个，唯一索引可以有多个

  • 主键索引（PRIMARY KEY）唯一标识，主键不可重复，只能有一个列作为主键索引
  • 唯一索引（UNIQUE KEY）避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引
  • 常规索引（KEY/INDEX）默认的，index,key关键字来设置
  • 全文索引（FULLTEXT）在特定的数据库引擎下才有，MYISAM快速定位数据

索引原则

索引不是越多越好
不要对经常变动的数据加索引
小数据量的表不需要加索引
索引一般加在常用来查询的字段上

索引的数据结构

Hash 类型的索引
B + tree：INNODB的默认数据结构

普通索引

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

1
2
3
CREATE INDEX indexName ON table_name (column_name)
#修改表结构(添加索引)
ALTER table tableName ADD INDEX indexName(columnName)

唯一索引

它与前面的普通索引类似，不同的就是：

  • 索引列的值必须唯一，但允许有空值。
  • 如果是组合索引，则列值的组合必须唯一。
    它有以下几种创建方式：
    1
    2
    3
    4
    #创建索引
    CREATE UNIQUE INDEX indexName ON mytable(username(length)) 
    #修改表结构
    ALTER table mytable ADD UNIQUE [indexName] (username(length))

主键索引

顾名思义，主键索引就是专门为主键字段创建的索引，也属于索引的一种。

主键索引是一种特殊的唯一索引，不允许值重复或者值为空。
创建主键索引通常使用 PRIMARY KEY 关键字。不能使用 CREATE INDEX 语句创建主键索引。

全文索引

  • 全文索引主要用来查找文本中的关键字，只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。
  • 在 MySQL 中只有 MyISAM 存储引擎支持全文索引。
  • 全文索引允许在索引列中插入重复值和空值。
  • 不过对于大容量的数据表，生成全文索引非常消耗时间和硬盘空间。
  • 创建全文索引使用 FULLTEXT 关键字。

实际使用区分

索引在逻辑上分为以上 5 类，但在实际使用中，索引通常被创建成单列索引和组合索引。

单列索引

单列索引就是索引只包含原表的一个列。在表中的单个字段上创建索引，单列索引只根据该字段进行索引。
单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。

例:
下面在 tb_student 表中的 address 字段上建立名为 index_addr 的单列索引，address 字段的数据类型为 VARCHAR(20)，索引的数据类型为 CHAR(4)。SQL 语句如下：

1
CREATE INDEX index_addr ON tb_student(address(4));

这样，查询时可以只查询 address 字段的前 4 个字符，而不需要全部查询。

多列索引

组合索引也称为复合索引或多列索引。
相对于单列索引来说，组合索引是将原表的多个列共同组成一个索引。
多列索引是在表的多个字段上创建一个索引。
该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。
但是，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。

例如，在表中的 id、name 和 sex 字段上建立一个多列索引，那么，只有查询条件使用了 id 字段时，该索引才会被使用。
例 6
下面在 tb_student 表中的 name 和 address 字段上建立名为 index_na 的索引，SQL 语句如下：

1
CREATE INDEX index_na ON tb_student(name,address);

提示：

  • 一个表可以有多个单列索引，但这些索引不是组合索引。
  • 一个组合索引实质上为表的查询提供了多个索引，以此来加快查询速度。

比如：
在一个表中创建了一个组合索引(c1，c2，c3)，在实际查询中，
系统用来实际加速的索引有三个：单个索引(c1)、双列索引(c1，c2)和多列索引(c1，c2，c3)。

返回 MySql 系列

建立索引的正确姿势

索引查询时的回表问题

意思就是指一条SQL语句在MySQL内部，要经过两次查询过程才能获取到数据。这是跟索引机制有关的
如下：
ID字段先建立了一个主键索引，然后又基于name字段建立了一个普通索引，此时MySQL默认会选用主键索引作为聚簇索引，将表数据和主键索引存在同一个文件中，也就是主键索引的每个索引节点，都直接对应着行数据。而基于name字段建立的索引，其索引节点存放的则是指向聚簇索引的ID值。
SELECT * FROM zz_user WHERE name = “子竹”;
首先会走name字段的索引，然后找到对应的ID值，然后再基于查询到的ID值，再走ID字段的主键索引，最终得到一整条行数据并返回
因此尽量可以基于主键做查询，如果实在需要使用非主键字段查询，那么尽量要写明查询的结果字段，而并非使用*

建立索引时，需要遵守的一些原则

①经常频繁用作查询条件的字段应酌情考虑为其创建索引。
②表的主外键或连表字段，必须建立索引，因为能很大程度提升连表查询的性能。
③建立索引的字段，一般值的区分性要足够高，这样才能提高索引的检索效率。
④建立索引的字段，值不应该过长，如果较长的字段要建立索引，可以选择前缀索引。
⑤建立联合索引，应当遵循最左前缀原则，将多个字段之间按优先级顺序组合。
⑥经常根据范围取值、排序、分组的字段应建立索引，因为索引有序，能加快排序时间。
⑦对尽量使用联合索引代替单值索引，联合索引比多个单值索引查询效率要高。

在建立索引时还需有些注意点

❶值经常会增删改的字段，不合适建立索引，因为每次改变后需维护索引结构。
❷一个字段存在大量的重复值时，不适合建立索引，比如之前举例的性别字段。
❸索引不能参与计算，因此经常带函数查询的字段，并不适合建立索引。
❹一张表中的索引数量并不是越多越好，一般控制在3，最多不能超过5。
❺建立联合索引时，一定要考虑优先级，查询频率最高的字段应当放首位。
❻当表的数据较少，不应当建立索引，因为数据量不大时，维护索引反而开销更大。
❼索引的字段值无序时，不推荐建立索引，因为会造成页分裂，尤其是主键索引。

联合索引的最左前缀原则

举个栗子理解，比如此时基于X、Y、Z字段建立了一个联合索引，实际上也相当于建立了三个索引：X、X、Y，因此只要查询中使用了这三组字段，都可以让联合索引生效。

但如若查询中这三个字段不以AND形式出现，而是单独作为查询条件出现，那单值索引性能会好一些，但三个不同的索引，维护的代价也会高一些。

其实联合索引的最左前缀原则，道理很简单的，就是组成联合索引的多个列，越靠左边优先级越高，同时也只有SQL查询条件中，包含了最左的字段，才能使用联合索引

因为将查询频率越高的字段放首位，就代表着查询时命中索引的几率越大。
同时，MySQL的最左前缀原则，在匹配到范围查询时会停止匹配，比如>、<、between、like这类范围条件，并不会继续使用联合索引，举个栗子：

SELECT * FROM tb WHERE X="..." AND Y > "..." AND Z="...";

重点
当执行时，虽然上述SQL使用到X、Y、Z作为查询条件，但由于Y字段是>范围查询，因此这里只能使用X索引，而不能完全匹配使用X、Y、Z索引。对于X、Y索引呢，严格意义上来说，Y字段的>范围查询，也会基于索引来完成，所以这里其实能用到X和X、Y字段的索引（也正是因为基于Y字段做了范围查询，从而阻断了Z字段基于索引匹配）。

索引失效与使用索引的正确姿势

  1. 查询中带有OR会导致索引失效
  2. 模糊查询中like以%开头导致索引失效
  3. 字符类型查询时不带引号导致索引失效
  4. 索引字段参与计算导致索引失效
  5. 字段被用于函数计算导致索引失效
  6. 违背最左前缀原则导致索引失效
  7. 不同字段值对比导致索引失效
  8. 反向范围操作导致索引失效
    一般来说，如果SQL属于正向范围查询，例如>、<、between、like、in...等操作时，索引是可以正常生效的，但如果SQL执行的是反向范围操作，例如NOT IN、NOT LIKE、IS NOT NULL、!=、<>...等操作时，就会出现问题

在MySQL中还有一种特殊情况会导致索引失效，也就是当走索引扫描的行数超过表行数的30%时，MySQL会默认放弃索引查询，转而使用全表扫描的方式检索数据，因此这种情况下走索引的顺序磁盘IO，反而不一定有全表的随机磁盘IO快。

MySQL索引为何使用B+树结构

MySQL一次磁盘IO不仅仅只会读取一条表数据，而是会读取多条数据，那到底读多少条数据呢？在InnoDB引擎中，一次默认会读取16KB数据到内存。

当本次磁盘IO读取到的所有数据全部筛选完成后，紧接着会看一下表中是否还有其他数据，如果还有则继续触发磁盘IO检索数据，如果没有则将内存中的结果集返回。
有人或许会疑惑，为什么这里已经读到了符合条件的数据，还需要继续发生磁盘IO呢？因为表中的字段没有建立唯一索引或唯一约束，因此MySQL不确定是否还有其他同名的数据，所以需要将整个表全部扫描一遍，才能得到最终结论。
全表扫描由于走的是线性查询，因此数据越多，开销越大，此时先来看看二叉搜索树。

因此建立索引的原因就在于此处，为了避免查询时走全表扫描，因此全表扫描的开销会随着数据量增长而越来越大。

索引为何不选择二叉树？

二叉树不适合作为索引结构的原因：

  1. 如果索引的字段值是按顺序增长的，二叉树会转变为链表结构。
  2. 由于结构转变成了链表结构，因此检索的过程和全表扫描无异。
  3. 由于树结构在磁盘中，各节点的数据并不连续，因此无法利用局部性原理。

索引为何不选择红黑树？

红黑树不适合作为索引结构的原因：

  1. 虽然对比二叉树来说，树高有所降低，但数据量一大时，依旧会有很大的高度。
  2. 每个节点中只存储一个数据，节点之间还是不连续的，依旧无法利用局部性原理。

对于上述两个缺点罗列的很明白，其本质上的原因就在于：单个节点中只能存储一个数据，因此一方面树会随着数据量增长越来越高，第二方面也无法利用局部性原理减少磁盘IO。

B+树结构

数据存储类似于链表的结构，通过指针关联不同数据。

  1. b+树的叶子节点包含所有的索引值，并指向数据
  2. b+树的所有叶子节点相连