第一节：MySQL索引简介、执行计划剖析、最佳实践入门

一. 索引简介

1. 什么是索引

　索引是排序好的快速查找的数据结构，用来帮助MySQL高效获取需要的数据结果。索引会影响到MySQL查找(WHERE的查询条件)和排序(ORDER BY)两大功能！理解索引工作的方式最好的办法就是把索引比喻成书的目录，当需要查看特定的章节时通过查看目录的方式往往要比查看整个书的内容要有效很多。

补充：

　(1). 除了数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

　(2). 一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。

2. 索引的分类

　我们平时所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种数据结构的索引之外，还有哈希索引（Hash Index）等。

A. 分类1

　单值索引：一个索引只包含单个列，一个表可以有多个单列索引。

　唯一索引：索引列的值必须唯一，但是允许空值。

　复合索引：一个索引包含多个字段。

B. 分类2

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

C. 分类3

　聚集索引(也叫主键索引)：通过主键创建的索引叫做主键索引，也叫聚集索引。

　辅助索引(非聚集索引)：除了主键索引以外的其它索引。

3. 索引的优缺点

A. 优点：

查找：类似大学图书馆的书目索引，提高数据检索的效率，降低数据库的IO成本。

　(2) 排序：通过索引対数据进行排序，降低数据排序的成本，降低了CPU的消耗。

　(3) 帮助服务器避免排序和临时表。

　(4) 可以将随机IO变成顺序IO。

B. 缺点：

　(1) 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。（详见下一节B+Tree结构）

　(2) 虽然索引大大提高了查询速度，但是同时会降低表的更新速度，例如对表频繁的进行 INSERT 、UPDATE 和 DELETE 。因为更新表的时候，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加的索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

　(3) 索引只是提高效率的一个因素，如果MySQL有大数据量的表，就需要花时间研究建立最优秀的索引。

4. 相关SQL语句

-----索引相关语句
--1. 查询索引
show index from TableName;
--如：查看article表的索引
show index from article;

--2. 删除索引
DROP INDEX [indexName] ON TableName;
--如：删除acticle表上的 idx_article_cv 索引
drop index idx_article_cv on article;

--3. 创建索引
CREATE [UNIQUE] INDEX indexName ON TableName(columnName(length));
--如：在article表上创建category_id的单个索引，名为：idx_article_c
CREATE INDEX idx_article_c ON article(category_id);
--如：在article表上创建category_id,views的联合索引，名为：idx_article_cv
CREATE INDEX idx_article_cv ON article(category_id,views);

---ALTER相关用法
/* 1、该语句添加一个主键，这意味着索引值必须是唯一的，并且不能为NULL */
ALTER TABLE TableName ADD PRIMARY KEY(column_list);

/* 2、该语句创建索引的键值必须是唯一的(除了NULL之外，NULL可能会出现多次) */
ALTER TABLE TableName ADD UNIQUE indexName(column_list);

/* 3、该语句创建普通索引，索引值可以出现多次 */
ALTER TABLE TableName ADD INDEX indexName(column_list);
--如：在article表中对views列添加索引，名为idx_article_v
alter table article add index idx_article_v(views);

/* 4、该语句指定了索引为FULLTEXT，用于全文检索 */
ALTER TABLE TableName ADD FULLTEXT indexName(column_list);

二. 执行计划

1. 什么是执行计划？

　SQL的执行计划，使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的，explain语句对select，delete，update，insert，replace语句有效。

补充explain的两个变种（了解即可）：

　(1). explain extended：会在 explain 的基础上额外提供一些查询优化的信息。紧随其后通过 show warnings 命令可以得到优化后的查询语句，从而看出优化器优化了什么。额外还有 filtered 列，是一个半分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的id值比当前表id值小的表）。

　(2). explain partitions：相比 explain 多了个 partitions 字段，如果查询是基于分区表的话，会显示查询将访问的分区。

数据准备：

--数据准备
use IndexTestDB;
--1. actor表
DROP TABLE IF EXISTS `actor`;
create table `actor`(
    `id` int(11) NOT NULL,
    `name` varchar(45) DEFAULT NULL,
    `update_time` datetime DEFAULT NULL,
    PRIMARY KEY (`id`)
)  ENGINE= INNODB DEFAULT CHARSET=utf8;
insert into `actor`(`id`,`name`,`update_time`) values(1,'a','2020-12-22 15:27:18'),(2,'b','2020-12-22 15:27:18'),(3,'c','2020-12-22 15:27:18'); 
 
 --2.film表
DROP TABLE IF EXISTS `film`;
create table `film`(
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(10) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_name` (`name`)
)  ENGINE= INNODB DEFAULT CHARSET=utf8;
insert into `film`(`id`,`name`) values(1,'file1'),(2,'file3'),(3,'file3'); 

--3. file_actor表 
DROP TABLE IF EXISTS `film_actor`;
create table `film_actor`(
    `id` int(11) NOT NULL,
    `film_id` int(11)  NOT NULL,
    `actor_id` int(11)  NOT NULL,
    `remark` varchar(255) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_film_actor_id` (`film_id`,`actor_id`)
)  ENGINE= INNODB DEFAULT CHARSET=utf8;
insert into `film_actor`(`id`,`film_id`,`actor_id`) values(1,1,1),(2,1,2),(3,2,1);

View Code

2. 剖析执行计划

　运行下面语句，发现type为all，即全表扫描。

(1). id

id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。分三种请情况：

　id 相同，执行顺序由上至下。

　id 不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。

　id 相同和不相同，同时存在。永远是id大的优先级最高，id相等的时候顺序执行。

代码如下：

set session optimizer_switch='derived_merge=off'; #关闭mysql5.7新特性对衍生表的合并优化
explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;

(2). select_type

数据查询的类型，主要是用于区别，普通查询、联合查询、子查询等的复杂查询。

　A. SIMPLE ：简单的 SELECT 查询，查询中不包含子查询或者 UNION 。

　B. PRIMARY ：查询中如果包含任何复杂的子部分，最外层查询则被标记为 PRIMARY 。

　C. SUBQUERY ：在 SELECT 或者 WHERE 子句中包含了子查询。

　D. DERIVED ：在 FROM 子句中包含的子查询被标记为 DERIVED(衍生) ，MySQL会递归执行这些子查询，把结果放在临时表中。

PS: B C D 三个案例详见开头id说明那个位置的截图。

　E. UNION ：如果第二个 SELECT 出现在 UNION 之后，则被标记为 UNION ；若 UNION 包含在 FROM子句的子查询中，外层 SELECT 将被标记为 DERIVED 。

　F. UNION RESULT ：从 UNION 表获取结果的 SELECT 。

(3). table

　这一列表示 explain 的一行正在访问哪个表。

　当 from 子句中有子查询时，table列是<derivenN>格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id。

(4). type -重点

访问类型排列。即MySQL决定如何查找表中的行，查找数据行记录的大概范围。

从最好到最差依次是： system > const > eq_ref > ref > range > index > ALL 。除了 ALL 没有用到索引，其他级别都用到索引了。

　① Null：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表。

 explain select min(id) from film;

　② system ：表只有一行记录（等于系统表），这是 const 类型的特例，平时不会出现，这个也可以忽略不计。

　③ const ：表示通过索引一次就找到了， const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置于 where 列表中，MySQL就能将该查询转化为一个常量。

set session optimizer_switch='derived_merge=off';
explain select * from (select * from film where id = 1) tmp;

　④ eq_ref ：唯一性索引扫描，读取本表中和关联表表中的每行组合成的一行，查出来只有一条记录。除了 system 和 const 类型之外, 这是最好的联接类型。

explain select * from film_actor left join film on film_actor.film_id = film.id;

　⑤ ref ：非唯一性索引扫描，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，查出来可能有多条记录。

情况1：简单的select查询，name为普通索引（非唯一索引）

explain select * from film where name = 'film1';

情况2：关联表查询，idx_film_actor_id是film_id和actor_id的联合索引，这里使用到了film_actor 的左边前缀film_id部分。

explain select film_id from film left join film_actor on film.id = film_actor.film_id;

　⑥ range ：只检索给定范围索引行，一般就是在 WHERE 语句中出现了 BETWEEN 、 < > 、 in 等的查询。这种范围扫描索引比全表扫描要好，因为它只需要开始于索引树的某一点，而结束于另一点，不用扫描全部索引。

explain select * from actor where id > 1;

　⑦ index ： Full Index Scan ，全索引扫描， index 和 ALL 的区别为 index 类型只遍历索引树。也就是说虽然 ALL 和 index 都是读全表，但是 index 是从索引中读的， ALL 是从磁盘中读取的。

下面例子 film表中只有两个字段，一个是主键id，一个是name，name上有索引。

 explain select * from film;

　⑧ ALL ： Full Table Scan ，没有用到索引，全表扫描。意味着mysql需要从头到尾去查找所需要的行。

下面的actor表没有做到全表索引覆盖，所以下面的的查询需要全表扫描。

explain select * from actor;

PS: 一般来说，得保证查询至少达到 range 级别，最好达到 ref 。

(5). possible key 和 key

　A. possible_keys ：这一列显示查询可能使用哪些索引来查找。 explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。

　B. key ：实际使用的索引。如果为 NULL ，则没有使用索引。查询中如果使用了覆盖索引，则该索引仅仅出现在 key 列表中。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。

(6). key_len （重点）

　表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。 key_len 显示的值为索引字段的最大可能长度，并非实际使用长度，即 key_len 是根据表定义计算而得，不是通过表内检索出的。在不损失精度的情况下，长度越短越好。

计算规则可参考： https://blog.csdn.net/qq_34930488/article/details/102931490

　A. char和varchar

　　a. 列长度

　　b. 列是否为空：NULL(+1)，NOT NULL(+0)

　　c. 字符集：utf8mb4=4，utf8=3，gbk=2，latin1=1

　　d. 列类型为字符，varchar +2 ， char +0

　char和varchar最终计算公式 key_len = (表字符集长度) * 列长度 +1 (null) + 2（varchar）

　B. 数值类型

　　a. tinyint 非空为1，可空为2

　　b. smallint 非空为2，可空为3

　　c. int 非空为4，可空为5

　　d. bigint　　非空为8，可空为9

　C. 时间类型

　　a. date：非空3字节，可空4字节

　 b. timestamp：非空4字节，可空5字节

　 c. datetime：非空8字节，可空9字节

举例：

(7). ref

　显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

(8). rows

　根据表统计信息及索引选用情况，大致估算出找到所需的记录需要读取的行数。

注：并不是结果集中的实际行数。

(9). Extra

　包含不适合在其他列中显示但十分重要的额外信息。

　A. using index ：使用覆盖索引的时候就会出现 （我们所喜欢的）

explain select film_id from film_actor where film_id = 1;

　B. using where：使用 where 语句来处理结果，查询的列未被索引覆盖。

explain select * from actor where name = 'a';

　C. using index condition：查找使用了索引，查询的列不完全被索引覆盖，where条件中是一个前导列的范围； (mysql5.6以后的新特性)

explain select * from film_actor where film_id > 1;

　D. using index & using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据 （我们所喜欢的）

　E. Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。

情况1：actor.name没有索引，此时创建了张临时表来distinct

explain select distinct name from actor;

情况2： film.name建立了idx_name索引，此时查询时extra是using index,没有用临时表

 explain select distinct name from film;

　F. Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。

情况1：actor.name未创建索引，会浏览actor整个表，保存排序关键字name和对应的id，然后排序name并检索行记录。

explain select * from actor order by name;

情况2： film.name建立了idx_name索引,此时查询时extra是using index

 explain select * from film order by name;

　G. Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段是

 explain select min(id) from film;

　H. Using join buffer ：使用了连接缓存。

　I. impossible where ： WHERE 子句的值总是false，不能用来获取任何元组。

三. 最佳实践入门

数据准备：

　　name-age-position 三个字段组成联合索引，id为主键索引，hire_time字段上没有索引。

CREATE TABLE `employees` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
 `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
 `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
 `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

 INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
 INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei',23,'dev',NOW());
 INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

1. 全值匹配

2. 最左前缀法则

　如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。 (注：在覆盖索引面前，最左前缀原则无效)

(1). 全表扫描，索引失效

EXPLAIN SELECT * FROM employees WHERE age = 22 AND position ='manager';
EXPLAIN SELECT * FROM employees WHERE position = 'manager';

(2). 用到了name索引

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei';

3. 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei';

4. .存储引擎不能使用索引中范围条件右边的列

　用到了name 和 age索引，position失效， key_len= 3*24+2+4=78

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager';

5. 尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少select *语句

(1). 索引覆盖

EXPLAIN SELECT name,age FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager';

(2). 索引未覆盖

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager';

6. mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描

EXPLAIN SELECT * FROM employees WHERE name != 'LiLei';

7. is null,is not null 也无法使用索引

EXPLAIN SELECT * FROM employees WHERE name is null

8. like以通配符开头（'%abc...'）mysql索引失效会变成全表扫描操作

(1). %在左边，索引失效

EXPLAIN SELECT * FROM employees WHERE name like '%Lei';

(2). %在右边，索引有效

EXPLAIN SELECT * FROM employees WHERE name like 'Lei%';

PS: 解决like'%字符串%'索引不被使用的方法？

使用覆盖索引！！！！！

EXPLAIN SELECT name,age,position FROM employees WHERE name like '%Lei%';

9. 字符串不加单引号索引失效

(1). 有效

EXPLAIN SELECT * FROM employees WHERE name = '1000';

(2). 失效

EXPLAIN SELECT * FROM employees WHERE name = 1000;

10. 少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引.

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' or name = 'HanMeimei';

11. 范围查询优化

准备：事先插入1万条数据，给age字段添加单独的索引

ALTER TABLE `employees`
ADD INDEX `idx_age` (`age`) USING BTREE ;

(1). 大范围查找

剖析：

　　没走索引原因：mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。比如这个例子，可能是由于单次数据量查询过大导致优化器最终选择不走索引。

(2). 缩小范围优化查找，使用到了索引

最后总结：

作者 : Yaopengfei(姚鹏飞)
博客地址 : http://www.cnblogs.com/yaopengfei/
声明1 : 如有错误，欢迎讨论，请勿谩骂^_^。
声明2 : 原创博客请在转载时保留原文链接或在文章开头加上本人博客地址，否则保留追究法律责任的权利。

posted @ 2020-12-01 13:21 Yaopengfei 阅读(727) 评论(4) 编辑收藏举报

刷新页面返回顶部

奔跑的路上

广泛汲取，用心打磨，精益求精。

第一节：MySQL索引简介、执行计划剖析、最佳实践入门

公告