目录

• 索引概述
  • 定义
  • 索引的优缺点
  • 常见索引
  • 索引语法
    • 创建索引
    • 查看索引
    • 删除索引
    • 修改索引
• 索引分类
  • 按列表属性分
    • 普通索引
    • 主键索引
    • 唯一索引
    • 复合索引（联合索引）
      • 索引的最左前缀匹配
      • 回表查询
      • 覆盖索引
  • 按物理存储角度分
    • 聚簇索引（聚集索引）
    • 非聚集索引（二级索引、或辅助索引）
    • 何时使用聚集索引或非聚集索引：
    • 总结：
• 一些使用技巧

索引概述

定义

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构（有序）

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用指向（类似指针）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

左边是数据表，一共有两列七行记录，最左边的0x07格式的数据是物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快 Col 2 的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据的物理地址的指针，这样就可以运用二叉查找快速获取到对应的数据了。

当然不同的算法和数据结构影响索引在不同情况的效率

索引长度

5.6默认是767字节

在5.7中默认设置innodb_large_prefix = ON

这个参数on时，大小为3072字节

mysql> show variables like 'innodb_large_prefix';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | ON    |
+---------------------+-------+
1 row in set (0.01 sec)

也就是说：

InnoDB单列索引长度不能超过767字节,联合索引不能超过3072字节

索引的优缺点

优点：

• 提高查询性能（B+树）
• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
• 使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能
• 减少锁等待和死锁

缺点：

• 增加维护成本（排序，B+数分页、合并）
• 占用更多的磁盘空间
• 过多的索引会增加优化器负担
• 降低修改性能

常见索引

索引是在MySQL的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型。MySQL目前提供了以下4中索引：

• B TREE索引：最常见的索引类型，大部分索引都支持B树索引。
• HASH索引：只有Memory引擎支持，使用场景简单。
• R-tree索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少；
• Full-text（全文索引）：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从MySQL 5.6版本开始支持全文索引；

MyISAM、InnoDB、Memory三种存储引擎对各种索引类型的支持：

索引	InnoDB引擎	MyISAM引擎	Memory引擎
B TREE索引	支持	支持	支持
HASH索引	不支持	不支持	支持
R-tree索引	不支持	支持	不支持
Full-text	5.6版本后支持	支持	不支持

innodb虽然后期支持全文索引，但支持性还是不好，官方也给出了解决方法。要使用全文索引还是尽量使用elasticsearch

索引语法

创建索引

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
 [USING index_type]
 ON table_name (index_col_name,...)

[UNIQUE|FULLTEXT|SPATIAL]：中括号中的这三个关键字表示创建的索引类型，它们分别表示唯一索引、全文索引、空间索引三种不同的索引类型。如果我们不指定任何关键字，则默认为普通索引。

index_name：表示索引的名称，自己定义。

index_type：表示索引的具体实现方式，在MySQL中，有两种不同形式的索引—B+TREE索引和HASH索引。在存储引擎为MyISAM和InnoDB的表中只能使用B+TREE，其默认值就是B+TREE；

index_col_name：表示需要创建索引的字段名称，我们还可以针对多个字段创建复合索引，只需要在多个字段名称之间以英文逗号隔开即可。

也可以用alter

alter table 表名 add [unique] index 索引名称 (列名[(length)]);

如果字段是char、varchar类型，length可以小于字段实际长度，如果是blog、text等长文本类型，必须指定length。

[unique]：中括号代表可以省略，如果加上了unique，表示创建唯一索引。

如果table后面只写一个字段，就是单列索引，如果写多个字段，就是复合索引，多个字段之间用逗号隔开。

查看索引

-- 查看MySQL索引的2种方式
 
show index from table_name;

show keys from table_name;

删除索引

drop index 索引名称 on 表名;

-- 或者用alter

alter table 表名 drop index 索引名称;

修改索引

alter table 表名 add [unique] index 索引名称 [表名](列名[(length)]);

索引分类

按列表属性分：

• 普通索引（单列索引）：一个索引只包含单个列（可空值可重复）
• 主键索引：特殊的唯一索引，一个表只能有一个主键且不允许有空值；索引列只能出现一次且必须唯一
• 唯一索引：索引列的值必须唯一，但允许有空值
• 复合索引（联合索引）：即一个索引包含多个列

从屋里存储的角度（物理顺序与键值逻辑）：

• 聚集索引
• 非聚集索引

按列表属性分

普通索引

单值索引即一个索引只包含单个列，一个表中可以有多个单列索引；语法如下：

CREATE TABLE customer (
    id INT(10) UNSIGNED  AUTO_INCREMENT,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200),
    PRIMARY KEY(id),
    KEY (customer_name)  
);


CREATE INDEX idx_customer_name ON customer(customer_name); 
ALTER TABLE customer DROP INDEX idx_customer_name;

主键索引

CREATE TABLE customer (
    id INT(10) UNSIGNED  AUTO_INCREMENT,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200),
    PRIMARY KEY(id) 
);

CREATE TABLE customer2 (
    id INT(10) UNSIGNED,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200),
    PRIMARY KEY(id) 
);

#无法定义多个主键
mysql> ALTER TABLE customer add PRIMARY KEY customer(customer_no);
ERROR 1068 (42000): Multiple primary key defined

#auto_increment字段必须在int类型且为primary key的列才可以使用
mysql> ALTER TABLE customer drop PRIMARY KEY;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

mysql> ALTER TABLE customer2 drop PRIMARY KEY;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

唯一索引

索引列的值必须唯一，但允许有空值，语法如下：

drop table if exists customer
CREATE TABLE customer (
    id INT(10) UNSIGNED  AUTO_INCREMENT,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200),
    PRIMARY KEY(id),
    KEY (customer_name),
    UNIQUE (customer_no)
);


CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no);
DROP INDEX idx_customer_no on customer ;

复合索引（联合索引）

复合索引即一个索引中包含多个列，在数据库操作期间，复合索引所需要的开销更小（相对于相同的多个列建立单值索引）；

CREATE TABLE customer (
   id INT(10) UNSIGNED  AUTO_INCREMENT,
   customer_no VARCHAR(200),
   customer_name VARCHAR(200),
   PRIMARY KEY(id),
   KEY (customer_name),
   UNIQUE (customer_name),
   KEY (customer_no,customer_name)
);


CREATE INDEX idx_no_name ON customer(customer_no,customer_name); 
DROP INDEX idx_no_name on customer ;

索引的最左前缀匹配

MySQL 建立多列索引（联合索引）有最左匹配的原则，即最左优先：如果有一个 2 列的索引 (a, b)，则已经对 (a)、(a, b) 上建立了索引；如果有一个 3 列索引 (a, b, c)，则已经对 (a)、(a, b)、(a, b, c) 上建立了索引；

也就是说实际上建立联合索引（a,b,c）

相当于建立了索引：（a）、（a,b）、（a,b,c）

假设现在有三个字段建立了复合索引

CREATE INDEX idx_a_b_c ON demo_table(a, b, c);

那么当where条件是a或a、b或者啊a、b、c时，都可以命中索引，除此之外都不能命中索引，例如：a、c或者b、c等；

当然有一个例外，当你 select 的字段里有复合索引里的字段，那么where语句不需要满足最左前缀匹配，MySQL 也会走索引。
比如：select a from demo_table where b = "xxx";
不过这时走索引不是为了加速查询（这时候索引对查询效率提升效果几乎没有），而是为了利用下面要讲的，覆盖索引，来减少对数据的检索。

回表查询

想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表，也就是回到聚集索引构建的b+tree去搜索的过程。

#例如有这样一张表，表里面数据若干
create table t_table(
  id int primary,
  b int not null,
  c varchar(16)
  index (b)  
)
#但我执行sql
   select * from t_table where id=2;
#这条语句就不需要回表
#是根据主键的查询方式，则只需要搜索 ID 这棵 B+ 树。主键是唯一的，根据这个唯一的索引，MySQL 就能确定搜索的记录。

#执行下面这句时，就需要回表
  select * from t_table where b=2;
#原因是通过 b 这个普通索引查询方式，则需要先搜索 b 索引树，然后得到主键ID的值，再到ID索引树搜索。这个过程虽然用了索引，但实际上底层进行了两次索引查询，这个过程就称为回表。

回表查询要多经历几次io，消耗时间更多，性能相对较差

覆盖索引

概念：

如果索引包含所有满足查询需要的数据，则该索引称为覆盖索引(Covering Index)，也就是平时所说的不需要回表操作。

判断标准：

使用explain，可以通过输出的extra列来判断，对于一个索引覆盖查询，显示为using index，MySQL查询优化器在执行查询前会决定是否有索引覆盖查询

覆盖索引（covering index）的原理很简单，就像你拿到了一本书的目录，里头有标题和对应的页码，当你想知道第267页的标题是什么的时候，完全没有必要翻到267页去看，而是直接看目录。
同理，当你要select的字段，已经在索引树里面存储，那就不需要再去检索数据库，直接拿来用就行了。

假如a、b、c三个字段建了复合索引，那么对于下面这条sql，就可以走覆盖索引:

select b,c from demo_table where a = "xxx";

explain一下，你就会发现extra字段是“Using index”，或者使用explain FORMAT=JSON … ，输出一个json结果的结果，看“using_index”属性，你会发现是“true”，这都意味着使用到了覆盖索引。

按物理存储角度分

聚簇索引（聚集索引）

1. 如果表这只了主键，则主键就是聚集索引
2. 如果表没有主键，则会默认第一个NOT NULL，且唯一（UNIQUE）的列作为聚簇索引
3. 以上都没有，则会默认创建一个隐藏的实例级row_id作为聚簇索引

聚集索引表记录的排列顺序和索引的排列顺序一致（以InnoDB聚集索引的主键索引来说，叶子节点中存储的就是行数据，行数据在物理储器中的真实地址就是按照主键索引树形成的顺序进行排列的），所以查询效率快，只要找到第一个索引值记录，其余就连续性的记录在物理也一样连续存放。聚集索引对应的缺点就是修改慢，因为为了保证表中记录的物理和索引顺序一致，在记录插入的时候，会对数据页重新排序（因为在真实物理存储器的存储顺序只能有一种，而插入新数据必然会导致主键索引树的变化，主键索引树的顺序发生了改变，叶子节点中存储的行数据也要随之进行改变，就会发生大量的数据移动操作，所以效率会慢）。因为在物理内存中的顺序只能有一种，所以聚集索引在一个表中只能有一个。

特点总结：

• 叶子节点存放的是整行的数据
• 一张表只能有一个聚集索引，因为实际的数据页只能按一颗b+tree进行排序，对应着物理内存中的顺序只能有一种
• 聚集索引的顺序决定表数据行的物理顺序

优势:

• 不用回表查询就可以直接找到数据，节省更多的查询时间
• 围查询性能得到提升 where 2<a<23
• 排序性能提升

非聚集索引（二级索引、或辅助索引）

非聚集索引（非聚簇索引）：以主键以外的列值作为键值构建的B+树索引，我们称之为非聚集索引。非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键

非聚集索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。

实按照定义，除了聚集索引以外的索引都是非聚集索引，只是人们想细分一下非聚集索引，分成普通索引，唯一索引，全文索引。

非聚集索引制定了表中记录的逻辑顺序，但是记录的物理和索引不一定一致（在逻辑上数据是按顺序排存放的，但是物理上在真实的存储器中是散列存放的），两种索引都采用B+树结构，非聚集索引的叶子层并不和实际数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页中的指针方式。非聚集索引层次多，不会造成数据重排。所以如果表的读操作远远多于写操作，那么就可以使用非聚集索引。

特点：

• 辅助索引存放的是键值和主键值
• 每张表可以存放多个辅助索引

何时使用聚集索引或非聚集索引：

动作描述	使用聚集索引	使用非聚集索引
列经常被分组排序	应	应
返回某范围内的数据	应	不应
一个或极少不同值	不应	不应
小数目的不同值	应	不应
大数目的不同值	不应	应
频繁更新的列	不应	应
外键列	应	应
主键列	应	应
频繁修改索引列	不应	应

总结：

• 聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个
• 聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续
• 聚集索引：物理存储按照索引排序；聚集索引是一种索引组织形式，索引的键值逻辑顺序决定了表数据行的物理存储顺序
• 非聚集索引：物理存储不按照索引排序；非聚集索引则就是普通索引了，仅仅只是对数据列创建相应的索引，不影响整个表的物理存储顺序.
• 索引是通过B+树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。

一些使用技巧

1.索引不会包含有NULL的列

​ 只要列中包含有NULL值，都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此符合索引就是无效的。

2.使用短索引

​ 对串列进行索引，如果可以就应该指定一个前缀长度。例如，如果有一个char（255）的列，如果在前10个或20个字符内，多数值是唯一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

3.索引列排序

​ mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作，尽量不要包含多个列的排序，如果需要最好给这些列建复合索引。

4.like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，注意正确的使用方式。like ‘%aaa%’不会使用索引，而like ‘aaa%’可以使用索引。

5.不要在列上进行运算

6.不使用NOT IN 、<>、！=操作，但<,<=，=，>,>=,BETWEEN,IN是可以用到索引的

7.索引要建立在经常进行select操作的字段上。

​ 这是因为，如果这些列很少用到，那么有无索引并不能明显改变查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

8.索引要建立在值比较唯一的字段上。

9.对于那些定义为text、image和bit数据类型的列不应该增加索引。因为这些列的数据量要么相当大，要么取值很少。

10.在where和join中出现的列需要建立索引。

11.where的查询条件里有不等号(where column != …),mysql将无法使用索引。

12.如果where字句的查询条件里使用了函数(如：where DAY(column)=…),mysql将无法使用索引。

13.在join操作中(需要从多个数据表提取数据时)，mysql只有在主键和外键的数据类型相同时才能使用索引，否则及时建立了索引也不会使用