开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> MySQL深入学习 --- 索引的创建和删除，索引设计原则，索引失效场景，查询优化，索引下推ICP -> 正文阅读

[大数据]MySQL深入学习 --- 索引的创建和删除，索引设计原则，索引失效场景，查询优化，索引下推ICP

往期：

六、索引的创建和删除

索引的分类：

从 功能逻辑 上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引
按照物理实现方式 ，索引可以分为 2 种：聚簇索引和非聚簇索引
按照 作用字段个数进行划分，分成单列索引和联合索引

不同的存储引擎支持的索引类型也不一样

InnoDB ：支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM ：支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory ：支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB ：支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive ：不支持 B-tree、Hash、Full-text 等索引；

6.1 索引的创建

1.创建表的时候创建

CREATE TABLE table_name [col_name data_type]
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC]

UNIQUE 唯一索引、FULLTEXT 全文索引、SPATIAL 空间索引
INDEX 与 KEY 是同义词，两者作用相同
index_name指定索引的名称，为可选参数，如果不指定，那么MySQL默认col_name为索引名
col_name为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择，可以选多个列组成联合索引
length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度
ASC或 DESC指定升序或者降序的索引值存储

CREATE TABLE book(
    book_id INT,
    book_name VARCHAR(100),
    year_publication YEAR,
    INDEX(year_publication) 			#普通索引，也是单列索引
    INDEX multi_idx(book_id,book_name) 	#多列索引
    UNIQUE INDEX uk_idx_id(book_id)		#唯一索引
);

CREATE TABLE student (
    id INT(10) UNSIGNED AUTO_INCREMENT,
    student_no VARCHAR(200),
    student_name VARCHAR(200),
    PRIMARY KEY(id)						#主键索引
);

CREATE TABLE `papers` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `title` varchar(200) DEFAULT NULL,
    `content` text,
    PRIMARY KEY (`id`),
    FULLTEXT KEY `title` (`title`,`content`)	#全文索引
) ENGINE=MyISAM DEFAULT CHARSET=utf8;	
#不同于like方式查询
SELECT * FROM papers WHERE content LIKE ‘%查询字符串%’;
#全文索引可以用match+against方式查询:
SELECT * FROM papers WHERE MATCH(title,content) AGAINST (‘查询字符串’);

CREATE TABLE test5(
    geo GEOMETRY NOT NULL,
    SPATIAL INDEX spa_idx_geo(geo)		#空间索引
) ENGINE=MyISAM;

使用全文索引前，搞清楚版本支持情况；
全文索引比 like + % 快 N 倍，但是可能存在精度问题；
如果需要全文索引的是大量数据，建议先添加数据，再创建索引。

2.在已存在的表上创建

1.使用ALTER TABLE语句创建索引

ALTER TABLE table_name ADD 
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name[length],...) [ASC | DESC]

2.使用CREATE INDEX创建索引

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (col_name[length],...) [ASC | DESC]

6.2 索引的删除

1.使用ALTER TABLE删除

ALTER TABLE table_name DROP INDEX index_name;

2.使用DROP INDEX删除

DROP INDEX index_name ON table_name;

七、索引设计原则

7.1 哪些情况适合索引

字段的数值有唯一性的限制
- 唯一性索引的值是唯一的，可以更快通过索引确定某条记录
频繁作为WHERE 查询条件的字段
- 某个字段在SELECT语句的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了
经常 GROUP BY 和 ORDER BY 的列
- 排序操作会浪费很多时间，如果为其建立索引，可以有效的避免排序操作，相当于事先用索引为其排序
UPDATE、DELETE 的 WHERE 条件列
- 在UPDATE或DELETE时，首先要用WHERE查询出来，如果添加了索引，找到这个要修改或删除的记录的效率就会提高
- 但是后续的更新操作，更新的字段是非索引字段效率反而会高，因为非索引字段不需要维护索引
- 所以，WHERE条件加索引，尽量不要修改索引字段
DISTINCT字段需要创建索引
- 如果需要去重，对该字段创建索引也会提升查询效率
使用字符串前缀创建索引
- 如果索引字段的值很长，最好使用值的前缀来索引
区分度高(散列性高)的列适合作为索引
- 区分度高，指hash运算后尽量分布范围大，比如性别，只有男女就是区分度小，不适合加索引
在多个字段都要创建索引的情况下，联合索引优于单值索引
使用最频繁的列放到联合索引的左侧
- 按照最左前缀原则，MySQL扫描索引是从左往右的，排序也是从左往右的
- 比如：index(k1,k2)这样一个联合索引，索引树会先给k1排序，在保证k1有序的情况下再给k2排序

7.2 哪些情况不适合索引

在where中使用不到的字段，不要设置索引
小表最好不用使用索引
有大量重复数据的列上不要建立索引
- 举例：性别，这种区分度不高，不适合建索引
避免对经常更新的表创建过多的索引
不建议用无序的值作为索引
- 例如身份证、UUID(在索引比较时需要转为ASCII，并且插入时可能造成页分裂)、MD5、HASH、无序长字符串等
删除不再使用或者很少使用的索引
不要定义冗余或重复的索引

八、索引失效场景

首先建一个表：

CREATE TABLE `demo`(
	`id` INT(10) NOT NULL AUTO_INCREMENT,
    `age` INT(2) NOT NULL,
    `name` varchar(20) NOT NULL,
    `details` varchar(20) NOT NULL,
    `comment` varchar(20) NOT NULL,
    PRIMARY KEY(`id`),
    #INDEX idx_age(age),
    #INDEX idx_age_name(age,name),
    #INDEX idx_name(name),
    INDEX idx_age_name_details(age,name,details)
) ENGINE = INNODB DEFAULT CHARSET = utf8;

1.违反最左前缀原则

MySQL匹配是从左到右匹配的，对于idx_age_name_details(age,name,details)，在where条件列中从左往右不跳过索引中的列，就符合最左前缀原则，就是有效的索引

违背原则的案例：

未从最左列开始，索引失效
跳过索引中间列，只用了部分索引

结论：带头大哥不能死，中间兄弟不能断

2.在索引列上用运算、函数、类型转换导致失效

2.1 索引列上使用运算

where age + 1 = 18使用了函数，没走idx_age_name_details(age,name,details)索引，使用了全表扫描

2.2 索引列使用函数

where age=18 AND SUBSTRING(name,3)='zha' ，在索引第二个参数上使用了函数，所以索引只走了age一个参数。

可以看到Extra上写了using index condition，意思是搜索条件中虽然出现了索引列，但是有部分条件无法使用索引，会根据能用索引的条件先搜索一遍再匹配无法使用索引的条件。

2.3 索引列类型转换

首先在name上加个索引

可以明显发现，使用类型转换没有走索引

3.LIKE通配符的问题

在SQL语句中LIKE后面的通配符的%或_位置也有讲究

WHERE name LIKE 'zhang%'，通配符写在后面，会走索引
WHERE name LIKE '%zhang'，通配符写在前面，不会走索引，会全表扫描

4.范围条件右边的列索引失效

建立一个联合索引：idx_name_age_details(name,age,details)

很明显，在中间使用age>20后，索引的key_len就只算了name和age，没算details了

5.<>、NOT、IN、NOT EXISTS失效

查询条件为等值查询或范围查询时，索引可以根据查询条件去查找对应的条目。

但是用<>、NOT、IN、NOT EXISTS意味着全表扫描查询不等于，不属于的

注意：IS NULL可以使用索引，而IS NOT NULL不可以

如图，使用的都是全表扫描

6. OR 前后存在非索引的列导致索引失效

name上有索引，而 comment上没加索引，此时索引失效

九、查询优化

9.1 关联查询优化

JOIN 语句的原理

来看如下语句：

EXPLAIN SELECT * FROM t1 STRAIGHT_JOIN t2 ON (t1.a=t2.a);

如果直接使用join语句，MySQL优化器可能会选择表t1或t2作为驱动表。改用 straight_join让MySQL使用固定的连接方式执行查询，这样优化器只会按照我们指定的方式去join。所以，在这种方式下，t1是驱动表，t2是被驱动表

这条SQL语句对应的流程是：

从表t1中读入一行数据 R；
从数据行R中，取出a字段到表t2里去查找；
取出表t2中满足条件的行，跟R组成一行，作为结果集的一部分；
重复执行步骤1到3，直到表t1的末尾循环结束。

这个过程就跟我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引，所以我们称之为 “Index Nested-Loop Join” ，简称NLJ。

在这个流程中，对驱动表做了全表扫描

MySQL中Nested-Loop Join 一共有三种方法：

Simple Nested-Loop Join。简单嵌套循环连接
Index Nested-Loop Join。索引嵌套循环连接
Block Nested-Loop Join。缓存块嵌套循环连接

Simple Nested-Loop Join

整个流程是两层循环，遍历左边嵌套遍历右边，如果左边有一万条数据，右边有一万条数据，整个Join就是一万乘一万

Index Nested-Loop Join

对于开头提到的这种，遍历左边，当匹配索引时再去右边查询，减少了内层循环

Block Nested-Loop Join

这种方式利用缓冲池，t1一次性可以缓存多条数据到Join Buffer，然后将Join Buffer中的数据批量与t2进行对比，从而减少内循环次数

Join Buffer可以通过修改join_buffer_size参数调整缓存大小

小结

保证被驱动表的JOIN字段已经创建了索引
需要JOIN 的字段，数据类型保持绝对一致。
LEFT JOIN 时，选择小表作为驱动表，大表作为被驱动表 。减少外层循环的次数
INNER JOIN 时，MySQL会自动将 小结果集的表选为驱动表 。选择相信MySQL优化策略。
能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数)
不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用 JOIN 来代替子查询。
衍生表建不了索引

9.2 子查询优化

子查询执行效率不高，原因：

执行子查询时，会创建一个临时表保存内层查询，查询完毕后再撤销这些表。这样会过多消耗CPU和IO资源，导致慢查询
临时表不存在索引，查询性能不好

在MySQL中，可以使用连接（JOIN）查询来替代子查询

9.3 排序优化

SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引
- 目的是在WHERE 子句中避免全表扫描，在 ORDER BY 子句避免使用 FileSort排序。
- 当然，某些情况下全表扫描，或者 FileSort 排序不一定比索引慢。
- 但总的来说，我们还是要避免，以提高查询效率。
尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单列索引；如果不同就使用联合索引。
无法使用 Index 时，需要对 FileSort 方式进行调优。

INDEX a_b_c(a,b,c)
order by 能使用索引最左前缀
- ORDER BY a
- ORDER BY a,b
- ORDER BY a,b,c
- ORDER BY a DESC,b DESC,c DESC
如果WHERE使用索引的最左前缀定义为常量，则order by 能使用索引
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b = const ORDER BY c
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b > const ORDER BY b,c
不能使用索引进行排序
- ORDER BY a ASC,b DESC,c DESC	 	/* 排序不一致 */
- WHERE g = const ORDER BY b,c 		/*丢失a索引*/
- WHERE a = const ORDER BY c 		/*丢失b索引*/
- WHERE a = const ORDER BY a,d 		/*d不是索引的一部分*/
- WHERE a in (...) ORDER BY b,c	 	/*对于排序来说，多个相等条件也是范围查询*/

举例：

SELECT SQL_NO_CACHE * FROM student 
WHERE age = 30 AND stuno <101000 
ORDER BY NAME ;

对于这样的SQL，我们可以

建立索引去掉filesort，idx_age_name(age,name)
进一步优化，idx_age_stuno_name(age,stuno,name)
对于filesort的优化
- 尝试提高 sort_buffer_size
- 尝试提高 max_length_for_sort_data
- Order by 时select * 是一个大忌。最好只Query需要的字段

9.4 Group By优化

group by 使用索引的原则几乎跟order by一致，group by 即使没有过滤条件用到索引，也可以直接使用索引
group by 先排序再分组，遵照索引的最佳左前缀法则
当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置
where效率高于having，能写在where限定的条件就不要写在having中了
减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。

9.5 优先考虑覆盖索引

覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。

比如说：select * from demo where id = 1，id是主键，主键建立的聚簇索引树中包含了所有表的字段，不需要回表查询，这属于一种覆盖索引
又比如说：建立了一个联合索引idx_name_age_details(name,age,details)

执行sql语句select name,age from demo where age = xxx AND name = xxx AND details = xxx。

在Extra中显示Using index就表示使用了覆盖索引，无需回表查询了

覆盖索引的好处：

避免Innodb表进行索引的二次查询(回表)
可以把随机IO变成顺序IO加快查询速度

9.6 字符串的前缀索引

Alibaba的规范中，字符串是要建立前缀索引的

【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。

选择前缀的长度很重要，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

举例：

alter table teacher add index index1(email);
#或
alter table teacher add index index2(email(6));

对于index1：
1. 从index1索引树找到满足索引值是’ zhangssxyz@xxx.com ’的这条记录，取得ID2的值；
2. 到主键上查到主键值是ID2的行，判断email的值是正确的，将这行记录加入结果集；
3. 取index1索引树上刚刚查到的位置的下一条记录，发现已经不满足email=’ zhangssxyz@xxx.com ’的条件了，循环结束。
4. 这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。
对于index2：
1. 从index2索引树找到满足索引值是’zhangs’的记录，找到的第一个是ID1；
2. 到主键上查到主键值是ID1的行，判断出email的值不是’ zhangssxyz@xxx.com ’，这行记录丢弃；
3. 取index2上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出ID2，再到ID索引上取整行然后判断，这次值对了，将这行记录加入结果集
4. 重复上一步，直到在idxe2上取到的值不是’zhangs’时，循环结束。
5. 这个过程中，反复扫描了多次，性能不是很好

结论：

前缀索引不是越短越好，需要保证区分度，散列性

注意：前缀索引对覆盖索引的影响

使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素。

十、索引下推ICP

Index Condition Pushdown(ICP)是MySQL 5.6中新特性，是一种在存储引擎层使用索引过滤数据的一种优化方式。ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数。

10.1 举例：

拿之前的表举例，我们建立了一个联合索引idx_name_age_details(name,age,details)

然后此时来了一个需求，需要匹配姓张的年龄为20岁的用户：

SELECT * FROM demo WHERE name LIKE '张%' AND age = 20

在Mysql5.6之前，流程是这样的：

首先找到名字姓张的，然后忽略age，拿到这4个ID，直接回表查询

总共需要回表四次

在Mysql5.6之后，流程是这样的：

首先找到名字姓张的，然后直接在联合索引的下一个字段age进行过滤，跳过了age不为18的两个记录

总共只需要回表两次

10.2 使用ICP前后内部过程

不使用ICP索引扫描的过程：

storage层：只将满足index key条件的索引记录对应的整行记录取出，返回给server层
server 层：对返回的数据，使用后面的where条件过滤，直至返回最后一行。

使用ICP索引扫描的过程：

storage层：首先将index key条件满足的索引记录区间确定，然后在索引上使用index filter进行过滤。将满足的index filter条件的索引记录才去回表取出整行记录返回server层。不满足index filter条件的索引记录丢弃，不回表、也不会返回server层。
server层：对返回的数据，使用table filter条件做最后的过滤。