【MySql进阶】索引详解（三）：索引的使用和创建原则、索引失效、索引优化

索引使用

索引SQL

创建索引

CREATE INDEX 索引名 ON 表名(字段名(length)); 
1

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。
1
2
3
4

删除索引

DROP INDEX 索引名 ON 表名;
1

查看索引

SHOW INDEX FROM 表名;
1

8.0新特性

降序索引

CREATE TABLE ts1(a int,b int,index idx_a_b(a,b desc));
1

降序索引只对查询中特定的排序顺序有效，如果使用不当，反而查询效率更低。

隐藏索引

在MySQL 5.7版本及之前，只能通过显式的方式删除索引。此时，如果发现删除索引后出现错误，又只能通过显式创建索引的方式将删除的索引创建回来。如果数据表中的数据量非常大，或者数据表本身比较大，这种操作就会消耗系统过多的资源，操作成本非常高。

从MySQL 8.x开始支持 隐藏索引（invisible indexes） ，只需要将待删除的索引设置为隐藏索引，使查询优化器不再使用这个索引（即使使用force index（强制使用索引），优化器也不会使用该索引），确认将索引设置为隐藏索引后系统不受任何影响，就可以彻底删除索引。 这种通过先将索引设置为隐藏索引，再删除索引的方式就是软删除 。

ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; # 切换成隐藏索引
ALTER TABLE tablename ALTER INDEX index_name VISIBLE; # 切换成非隐藏索引
1
2

索引创建原则

字段数值有唯一性限制的

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源：Alibaba）

说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。

频繁作为 WHERE 查询条件的字段

这个无需多讲

经常 GROUP BY 和 ORDER BY 的列

如果待排序的列有多个，那么可以在这些列上建立 组合索引 。

UPDATE、DELETE 的 WHERE 条件列

如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护

DISTINCT 字段需要创建索引

有时候我们需要对某个字段进行去重，使用 DISTINCT，那么对这个字段创建索引，也会提升查询效率。

索引会对数据按照某种顺序进行排序，所以在去重的时候也会快很多。

多表连接时创建索引

首先， 连接表的数量尽量不要超过 3 张 ，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。

其次， 对 WHERE 条件创建索引 ，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的。

最后， 对用于连接的字段创建索引 ，并且该字段在多张表中的 类型必须一致 。比如 course_id 在student_info 表和 course 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。

使用列的类型小的创建索引

即该字段存储的数据占用字节比较少，则创建索引时消耗的空间也小。

使用字符串前缀创建索引

字符串的排序一般是按照第一个字符先排序，首字符相同的再按照第二个以此类推。查找时对比两个字符串是否相同也是如此，因此我们如果截取部分前缀创建索引，利用where和like字段查询，也是可以使用到索引的。但是截取的不可太长也不可太短。

字符串一般比较长，创建索引空间开销很大，所以要截取前缀创建。

create table shop(address varchar(120) not null);
alter table shop add index(address(12));
1
2

如上所示，即为地址列创建了截取前缀长度为12的索引。

如果截取的多了，达不到节省空间的效果。如果截取的少了，重复内容太多，查询效率又大大下降。

我们可以通过以下方式，帮助选择截取的长度:

select count(distinct address) / count(*) from shop; -- 全部数据的选择度

select count(distinct left(address,10)) / count(*) as sub10, -- 截取前10个字符的选择度
count(distinct left(address,15)) / count(*) as sub11, -- 截取前15个字符的选择度
count(distinct left(address,20)) / count(*) as sub12, -- 截取前20个字符的选择度
count(distinct left(address,25)) / count(*) as sub13 -- 截取前25个字符的选择度
from shop;

-- 即非重复率
1
2
3
4
5
6
7
8
9

《阿里巴巴Java开发手册》中写道：

【 强制 】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本
区分度决定索引长度。
说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会 高达
90% 以上 ，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。

区分度高(散列性高)的列适合作为索引

即重复率低的列

使用最频繁的列放到联合索引的左侧

最左匹配原则

数据量小的表最好不要使用索引

在数据量不大的情况下，索引就发挥不出作用了

有大量重复数据的列上不要建立索引

当数据重复度大，比如 高于 10% 的时候，也不需要对这个字段使用索引 。

因为这样在回表的时候，会高次数的回表。

索引使用原则

索引列不能是表达式或函数的参数

比如：

where id - 1 = 5;
where TO_DAYS(creat_time) - TO_DAYS(CURRENT_DATE) <= 20;
1
2

匹配字符串前缀

对于字符串前缀索引来说，可以使用 等于 或者 匹配前缀去查询。

比如：

SELECT * FROM test2 WHERE `name` LIKE 'abc%';
SELECT * FROM test2 WHERE `name` = 'abcd';
1
2

但是匹配中间的字符串或者后缀的字符串，则不会走索引。

比如：

SELECT * FROM test2 WHERE `name` LIKE '%bc%';
SELECT * FROM test2 WHERE `name` LIKE '%bcd';
1
2

范围查找

单列索引：

SELECT * FROM test3
WHERE `code` >1 and `code` < 4;
1
2

若索引字段为code，且code不为主键，则对于以上sql语句，查找的顺序是这样的：

先找到code = 1的记录，再找到code = 4的记录，因为所有记录都是用链表连接的，很容易就可以在叶子节点中取到它们中间的数据，而二级索引中叶子节点保存的为记录对应的主键值。

此时再回表到聚簇索引中，根据主键查询即可。

联合索引：

SELECT * FROM test3
WHERE `code` >1 and `code` < 4 and id >4;
1
2

对于以上sql语句，若索引为 code,id，则查找顺序是这样的：

通过条件 14进行查找。

我们知道，构建联合索引时，只有在code相同的时候，才会再使用id排序。

因此 1

精准匹配+范围匹配

SELECT * FROM test3 
WHERE code = 1 AND id > 4;
1
2

对于以上sql语句，若索引为 code,id，则查找顺序是这样的：

先找到code=1的一串数据，因为code相同，则id是排序好的

所以可以走索引。

排序使用索引

排序字段创建了索引也不一定会走索引，因为可能回表次数太多，mysql会评估排序的代价是否大于回表的代价。

以如下这张表进行举例：

CREATE TABLE `test1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `code` int DEFAULT NULL,
  `num` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_code` (`code`),
  KEY `idx_num` (`num`)
) ENGINE=InnoDB AUTO_INCREMENT=807 DEFAULT CHARSET=utf8;
1
2
3
4
5
6
7
8

单列索引

SELECT * FROM test1 ORDER BY `code` ;
1

上方的语句，虽然code有索引，但是也不会走索引。

这是因为，select *时，虽然code有创建索引，但是在叶子节点上，只是code是排序好的，id并不是，所以需要回表操作，mysql认为回表的代价过大，所以干脆直接 filesort了。但是如果查询的数量不多，回表代价不大，则也还会走索引，就比如如果在第一个代码块的sql后面加入 limint 1 或 2，一两条记录的回表代价则会小得多（实测 limit 3 都不行）。

在使用了where字段了之后也是一样的，在我自测时，加入了where code >70000之后，则会走索引，但是where code > 60000则不走索引，其中 code>70000的记录共90条，而code> 60000则200条。但是code>60000时加入limit小于25的则又走索引。

因此，mysql认为回表代价大小的判断方式仍然未知，但是可以确定的是，如果回表代价过大，在使用order by排序索引字段时，mysql不会走索引。

---

SELECT id,code FROM test1 ORDER BY `code` ;
SELECT id FROM test1 ORDER BY `code` ;
SELECT code FROM test1 ORDER BY `code` ;
1
2
3

上方的语句，则会走code的索引。

这是因为code的二级索引本来就会保存code和id，就无需回表操作了。

联合索引

现在创建一个联合索引idx_code_num

SELECT * FROM test1 ORDER BY `code` ;
1

以上这个语句是走索引的，诶这是为什么？起初我以为是联合索引的原因，最后发现，这张表只有三个字段，联合索引中已经把id、code、num都存储了，因此就不需要回表了。

现在基于这个联合索引，再添加一个int 字段 num2

再执行以上的语句，则不会走索引了。

---

SELECT * FROM test1 WHERE code = 1 ORDER BY `num`;
1

上面这个语句对于这个联合索引来说，查找顺序是，先找到code = 1的一串记录，此时因为对于code相等的值，再根据num排序，所以这串记录直接就是根据num排序好的，可以只回表一次，因此这个语句就是走索引的。

---

SELECT * FROM test1 WHERE code > 1 ORDER BY `code`;
1

对于这个语句，只有where时是走索引的，在排序时是不走索引的

也是因为需要回表的代价太大，和单列索引的原因相同，只要使用limit截取或者code>1的数据很少的时候，回表代价比较小，就会走索引了。

索引字段不能发生类型转换

# 未使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;
# 使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123';
1
2
3
4

name=123发生类型转换，索引失效。

回表的代价

在使用二级索引的查找过程中，查找步骤是：在二级索引中查找到需要查询的数据的主键值，再回表至聚簇索引中查询。因为一个字段在二级索引中是顺序排列的，但是在聚簇索引中不一定。在二级索引中根据索引字段查找，并按照顺序读取的方式我们称作顺序I/O，对于不是顺序排列的主键回表读取数据的方式为随机I/O。

一般情况下，顺序I/O比随机I/O的性能高很多，需要回表的记录越多，使用二级索引的性能就越低，甚至让某些查询宁愿使用全表扫描也不使用 二级索引 。

什么时候采用全表扫描、什么时候采用二级索引+回表的形式执行查询，是查询优化器的工作。查询优化器会事先对表中的记录计算一些统计数据，然后再利用这些统计数据根据查询的条件来计算一下需要回表的记录数，需要回表的记录数越多，就越倾向于使用全表扫描，反之倾向于使用 二级索引 + 回表 的方式。当然优化器做的分析工作不仅仅是这么简单，但是大致上是个这个过程。

一般情况下，限制查询获取较少的记录数会让优化器更倾向于选择使用 二级索引 + 回表 的方式进行查询，比如where符合条件的记录少、比如limit限制。

索引覆盖

但是也不是所用情况使用二级索引都需要回表，就像前面 排序使用索引 中说的，如果查询的字段刚好都在索引里，那就不需要回表了。

我们把这种只需要用到索引的查询方式称为 索引覆盖 。