MySQL学习笔记 - 浅谈索引

1、索引简介

---

1.1、索引的利弊

• 利
  检索：可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录。
  排序：通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。
  索引下推：server层不能处理的索引，可以通过索引下推(ICP)在存储引擎层处理。
  索引覆盖：在满足覆盖索引条件时，可以避免回表查询。
  …
• 弊
  索引会占据磁盘空间。
  索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件。
  
  

1.2、索引的分类与创建

1.2.1、单列索引

• 创建

  -- 创建普通索引的两种方式
  CREATE INDEX indexName ON tableName(col_name(length));
  ALTER TABLE tableName ADD INDEX indexName(col_name(length));
  
  -- 创建唯一索引的两种方式
  CREATE UNIQUE INDEX indexName ON tableName(col_name(length));
  ALTER TABLE tableName ADD UNIQUE INDEX indexName(col_name);
  

1.2.2、组合索引

• 创建

  ALTER TABLE article ADD INDEX index_title_time (title(50), time(10));
  -- 注意，title可能长度不止50（假设为100），那么组合索引种title(50)即取title字段最左50长度作为索引，后50个就不走索引。
  -- 只要是组合索引，除了联合主键外，一定属于辅助索引。
  

1.2.3、全文索引

• 说明
  全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用【分词技术】等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。
• 创建

  -- 创建全文索引的两种方式
  CREATE FULLTEXT INDEX idx_name ON table_name(col_name(length));
  ALTER TABLE table_name ADD FULLTEXT idx_name(col_name);
  

1.2.4、空间索引

1.2.5、位图索引（Oracle）

1.3、索引的查看与删除

# 查看
SHOW INDEX FROM table_name;
SHOW INDEX FROM table_name \G; -- \G 代表格式化输出信息

# 删除
DROP INDEX idx_name ON table_name;

2、索引的原理

---

2.1、索引的存储结构

索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引结构。如：MyISAM 和 InnoDB 存储引擎默认并且只支持 B+ TREE 索引；MEMORY / HEAP存储引擎支持 HASH 和 BTREE 索引。

2.2、B树 / B+树

B树是为了磁盘或其它存储设备而设计的一种多叉平衡查找树。 B树的高度一般都是在2-4这个高度，树的高度直接影响IO读写的次数。如果是三层树结构，支撑的数据可以达到20G；如果是四层树结构，支撑的数据可以达到几十T！

B树 和 B+树 的最大区别在于非叶子节点是否存储数据的问题：
B树：是非叶子节点和叶子节点都会存储数据；
B+树：只有叶子节点才会存储数据，所有存储的数据都在同一行上，而且这些数据都是有指针指向的，也就是有顺序的。

2.3、聚焦索引（InnoDB）

• 主键索引

  

• 辅助索引

  

结论：如果是非主键查询，则需要搜索两次索引树（一次是辅助索引树，一次是主键索引树），最终取出来数据。据辅助索引树获取到的主键，去主键索引树中获取数据，这个过程称之为回表。

所以在聚焦索引中，主键索引一定要存在，且最好是整数类型，而不是选择长字符串uuid等作为主键，推荐使用雪花算法生成主键；
若主键索引不存在，则会在表中找到一个唯一字段作为作为主键；
若唯一字段也不存在，则自动生成伪列作为主键。

2.4、非聚焦索引（MyISAM）

非聚焦索引的主键索引与辅助索引都不是必须的，他们存储的都是数据空间对应的磁盘地址（索引）。

• 主键索引

  

• 辅助索引

  
  
  

2.5、组合索引

create table t1 (
	a int primary key,
	b int, c int, d int, e varchar(8)
);
alter table t1 add index idx_b_c_d(b,c,d);
select * from T1 where b = 12 and c = 14 and d = 3;

3、索引使用场景

---

3.1、索引推荐场景

• 主键自动建立唯一索引。

• 频繁作为查询条件的字段应该创建索引。

• 多表关联查询中，关联字段应该创建索引，如：

  -- on 两边的userid都要分别创建索引
  SELECT * FROM users LEFT JOIN orders ON users.userid = orders.userid;
  

• 查询中需要排序的字段，应该建立B+ tree有序索引

• 覆盖索引，好处是不需要回表

  -- 现存在一张 users 表，其包含一个组合索引 (name, age)
  
  -- 全表扫描，没有用到索引。它相当于遍历主键索引中所有叶子节点的值。
  SELECT * FROM users;
  -- 索引覆盖，直接获取索引中的数据作为结果，不需要回表。
  SELECT name, age FROM users;
  -- 不能覆盖索引，需要回表查询。
  SELECT name, age, sex FROM users;
  

• 统计或分组字段，应该考虑创建索引
  
  

3.2、索引不推荐场景

• 记录太少（索引会存在存储开销）
• 频繁更新
• 查询字段使用频率不高
  
  

3.3、组合索引的优点

• 建一棵索引树，其效果可以相当于创建了多棵索引树，只要满足最左前缀原则即可使用到该索引。如：

  -- 现创建一张 users 表，并创建组合索引 (name, age)，其相当于创建了：
  -- (name)字段索引
  CREATE INDEX idx_name ON users(name);
  SELECT * FROM users WHERE name='zhangsan'; -- 可以使用索引
  SELECT * FROM users WHERE age='18'; -- 不可以使用索引
  -- (name, age)字段组合索引
  ALTER TABLE users ADD INDEX index_name_age (name, age);
  SELECT * FROM users WHERE name='zhangsan' and age='18'; -- 可以使用索引
  

• 可以优化排序

  SELECT * FROM users ORDER BY name; -- 可以使用索引
  SELECT * FROM users ORDER BY age; -- 不可以使用索引
  SELECT * FROM users ORDER BY name, age; -- 可以使用索引 
  

• 可以使用覆盖索引优化查询

  SELECT name FROM users; -- 使用索引覆盖，未使用索引查询
  SELECT name, age FROM users; -- 使用索引覆盖，未使用索引查询
  SELECT name, age, sex FROM users; -- 不可以使用索引覆盖
  

4、执行计划

---

数据准备

-- 产品表
create table product (
	pid int(11) PRIMARY KEY AUTO_INCREMENT,
	pname varchar(20),
	price double,
	pdate date,
	cid int(11)
);
alter table product add index idx_cid_pname(cid, pname(10));
alter table product add index idx_pname(pname(10));
alter table product add index idx_price(price);
insert into product values (1, '泰国大榴莲', 98, '2020-06-01', 1);
insert into product values (2, '泰国大枣', 38, '2020-06-02', 1);
insert into product values (3, '新疆切糕', 68, '2020-06-03', 2);
insert into product values (4, '十三香', 10, '2020-06-04', 2);
insert into product values (5, '山东大枣', 20, '2020-06-05', 2);
insert into product values (6, '品如', 98, '2020-06-06', 3);
insert into product values (7, '羊毛大衣', 200, '2020-06-07', 3);

-- 种类表
CREATE TABLE category (
	id int(11) PRIMARY KEY AUTO_INCREMENT,
	cname varchar(100),
	country varchar(16)
);
alter table category add index idx_country(country);
insert into category values (1, '国外食品', 'Thailand');
insert into category values (2, '国内食品', 'China');
insert into category values (3, '国内服装', 'China');

-- 供应商表
create table provider(
	pvid int primary key,
    pvname varchar(16),
	pid int not null
);
alter table provider add union index idx_pid(pid);
... -- 数据略

4.1、前序知识准备（扇区、块、页、索引节点）

• 扇区：磁盘存储数据的最小单位，常见为512Byte。在linux下可以通过指令查看：

  fdisk -l
  

• 块：操作系统在操作磁盘数据时的逻辑单位。1块 $=2^n$扇区，常见为4K。在linux下可以通过指令查看：

  stat /boot/|grep "IO Block"
  

• 页：内存以页为单位与操作系统进行IO交互，1页 $=2^n$块。Linux操作系统默认为4K，在MySQL中可以通过Innodb_page_size设置大小，一般设置为16K。

  getconf PAGE_SIZE
  

• 索引节点：长度为页的倍数。指针默认长度为6bit，假设key为bigint类型(8bit)，则一个索引节点的大小为6+8 = 14bit。
  
  

4.2、查看执行计划

不管一张表中有多少个索引，在查询的时候，最多只能使用一个索引。（index-merge情况除外）所以我们需要选择最佳的索引。

• 语法

  -- 例1
  EXPLAIN SELECT * FROM product WHERE cid=3 AND pname='品如' \G;
  
  -- 例2
  EXPLAIN SELECT m.pname, m.price, g.cname, g.country FROM product m LEFT JOIN category g
  ON m.cid=g.id WHERE m.cid=2 AND g.country='China';
  

• 结果

  
  
  
  

4.3、解析执行计划

4.3.1、id

每个单位查询的SELECT语句都会自动分配的一个唯一标识符，表示查询中操作表的顺序。
id相同：执行顺序由上到下
id不同：如果是子查询，id号会自增，id越大，优先级越高。
id为null：表示这是一个结果集，不需要使用它来进行查询。

4.3.2、select_type

单位查询（如普通查询、联合查询、子查询等复杂结构）的查询类型。常见查询类型如下：

• simple：表示不需要union操作或者不包含子查询的简单select查询。
  
• primary：一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary。只有一个。
• derived：from子句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套select。
  
• union：union/union all连接的多个select查询，第一个查询select_type为primary，第二个以后的查询都是union。
• union result：包含union的结果集，在union和union all语句中，因为它不需要参与查询，所以id字段为null。
  
• dependent union：与union一样，出现在union或union all语句中，但是这个查询要受到外部查询的影响。
• subquery：除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery。
  
• dependent subquery：与dependent union类似，表示这个subquery的查询要受到外部表查询的影响。
  
  

4.3.3、table

查询表的表名：
若表存在别名，则为别名；
若查询的是派生表，则为派生表对应的<select_type + id>，如<devired2>。

4.3.4、type

单位查询到连接类型（访问类型）。除了ALL以外，其他type都可以使用到索引。除了index_merge以外，其他的Type只可以用到一个索引，类型如下：

• system
  表中记录只有1条或表中没有数据时。

• const
  使用唯一索引或者主键，返回记录一定是一行记录的等值where条件。
  

• eq_ref
  通常出现在多表的join查询，表示对于前表的每个结果，都只能匹配到后表的一行结果，并且查询的比较操作通常是 =号，查询效率较高。

  需要满足以下几种情况：多表关联、等值连接、等值连接的两个表的列是唯一索引列或主键列。
  

• ref
  针对非唯一性索引。
  

• ref_or_null
  与ref方法类似，只是增加了null值的比较。实际用的不多。

• fulltext
  全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用”分词技术“等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。一定要注意的是，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引。
  

• unique_subquery
  用于where中的in形式子查询，子查询返回不重复值。

• index_subquery
  用于where中的in形式子查询，使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。

• range
  索引范围扫描，常见于使用> 、< 、is null 、between and 、in 、like 等运算符的查询中。
  

• index_merge
  表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取所个索引，性能可能大部分时间都不如range。
  

• index
  全部索引扫描。index不是为了搜索而存在的，而是为了索引覆盖而使用的。把索引从头到尾扫一遍，常见于使用索引列的结果就可以满足查询而不需要读取数据文件，可以使用索引排序或者分组的查询。相当于一本书，你只需要把整本书的目录看完就可以满足需求。
  

• ALL
  全表扫描数据文件。相当于一本书，你从头到尾看了一遍。
  
  
  

4.3.5、possible_keys

此次查询中可能选用的索引，一个或多个。

4.3.6、key

此次查询中确切使用到的索引。select_type 为 index_merge 时，这里可能出现两个以上的索引，并以逗号分隔。其他的 select_type 这里只会出现一个。

4.3.7、key_len

用于处理查询的索引长度（单位：字节）
前文提到，主键不要使用uuid，最好使用int类型，是因为字符串类型占的长度明显比int大。开发中推荐使用雪花算法生成主键。
单列索引：整个索引长度算进去
多列索引：查询不一定都能使用到所有的列，具体使用到了多少个列的索引，这里就会计算进去，没有使用到的就不会计算进去。且只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到 key_len中。

类型	计算长度(utf-8)
varchar	(len + 1) * 3
char	len * 3 + 1
int	4

4.3.8、ref

引用到的上一个表的列

• const：常量等值查询

  EXPLAIN SELECT * FROM product WHERE cid=6 AND pname='品如'; -- [const, const]
  

• 连接查询：子表该字段会显示主表的关联字段。

  EXPLAIN SELECT * FROM product m LEFT JOIN category g ON m.cid=g.id;
  -- m-ref: []
  -- g-ref: [kkb.m.cid] 即[database.table.column]
  

• func
  由一些func输出，或发生了内部隐式转换。
  
  

4.3.9、rows

显示此查询一共扫描了多少行。此处只是一个预估值，不是很准。

4.3.10、Extra

额外的信息，常见信息如下：

• using index
  仅使用索引树中的信息，而不需要进行附加搜索来读取实际行（使用二级覆盖索引即可获取数据）。若一个索引列中包含了本次查询中所有的列时，就可以使用此策略。

• using where
  表示MySQL将对存储引擎层(storage engine)提取的结果还需要在Server层进行再过滤，此时过滤无法用到索引。
  
  

• using index condition

  索引下推（ICP）
  MySQL5.6后，MySQL将之前的server层分成了server层和引擎层，ICP技术基于此诞生。ICP实现了index filter技术，将本应在 server 层进行的 table filter 中可以进行 index filter 的部分，放到了引擎层并使用 index filter 进行处理，避免或减少回表 table filter 的数据。

1. index key
   用于确定SQL查询在索引中的连续范围（起始范围 + 结束范围）的查询条件。由于一个范围，至少包含一个起始和一个终止，因此 Index key 也被拆分为 index first key 与 index last key，分别用于定位索引查询的起始，以及索引查寻的终止条件。也就是说根据索引来确定扫描的范围。
2. index filter
   在使用了index key确定了起始和终止范围后，在此范围内，还有一些记录不符合where条件，如果这些条件可以使用索引进行过滤，那么就是index filter。也就是说用索引来进行where条件过滤。这个过程称之为索引下推(ICP)。
3. table filer
   where中的条件不能使用索引进行处理的，只能访问table，进行条件过滤了。
   
   

• using temporary

  官方解释：为了解决查询，MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。很明显就是通过where条件一次性检索出来的结果集太大了，内存放不下了，只能通过添加临时表来辅助处理。

  要解决查询，MySQL需要创建一个临时表来保存结果，多出现在 GROUP BY 或 ORDER BY 查询中，若该类查询无法利用索引完成 group by 或 order by ，则会提示 Using temporary。（cname不含索引）
  

• using filesort

  官方解释：MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配 WHERE 子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。

  当Query 中包含 ORDER BY，且无法利用索引完成排序操作的时候，MySQL Query Optimizer 不得不选择相应的排序算法来实现。数据较少时从内存排序，否则从磁盘排序。Extra不会显式地告诉客户端用哪种排序，仅仅提示 using filesort。
  

• distinct
  MySQL 正在寻找不同的值，因此在找到第一个匹配行后，它将停止搜索当前行组合的更多行。
  
  

5、索引优化口诀

全值匹配我最爱，最左前缀要遵守。
带头大哥不能死，中间兄弟不能断。
索引列上少计算，范围之后全失效。
模糊通配靠右边，覆盖索引不写星。
不等空值还有或，索引失效要少用。

-- 案例索引
ALTER TABLE product ADD INDEX idx_pname_pdate_cid(pname, pdate, cid);

• 全值匹配我最爱，最左前缀要遵守

  -- 以下都是全值匹配
  select * from product where pname='品如'; -- cid
  select * from product where pname='品如' and pdate='2020-06-06'; -- cid、pname
  select * from product where pname='品如' and pdate='2020-06-06' and cid=3; -- cid、pname、pdate
  

• 带头大哥不能死，中间兄弟不能断

  select * from product where pdate='2020-06-06' and cid=3; -- 带头大哥阵亡，没有列用到索引。
  select * from product where pname='品如' and cid=3; -- pname
  

• 索引列上少计算

  -- 不要进行计算、函数、自动/手动类型转换，不然会导致索引失效而转向全表扫描
  select * from product where left(pname, 1)='品如'; -- 没有用到索引
  

  
• 范围之后全失效

  -- 不能继续使用索引中范围条件（bettween、<、>、in等）右边的列
  select * from product where pname='品如' and pdate>'2020-06-05' and cid=3; -- pname、pdate
  

• 模糊通配靠右边

  -- LIKE %不可以写在最开始，否则索引失效。
  select * from product where pname='品如' and pdate like '2020%' and cid=3; -- pname、pdate、cid
  select * from product where pname='品如' and pdate like '%2020' and cid=3; -- pname
  select * from product where pname='品如' and pdate like '%2020%' and cid=3; -- pname
  select * from product where pname='品如' and pdate like '2020%6%6%' and cid=3; -- pname、pdate、cid
  -- 若一定要用到前置%，则尽量去实现覆盖索引以提高效率。（本问题多用于面试）
  select pname, pdate from product where pdate like '%2020%'; -- 覆盖索引
  

• 覆盖索引不写星

  select * from product; -- 不能覆盖索引
  select pname, pdate, cid from product; -- 覆盖索引
  select pname, cid from product; -- 诸如类似都可以覆盖索引
  select pid, pname, cid from product; -- pid为主键，组合索引的叶子结点就会存放主键信息，所以你懂的，也会覆盖索引
  select pname, price from product; -- 由于price不在索引idx_pname_pdate_cid中，不能覆盖索引
  

• 不等空值还有或，索引失效要少用

  -- 非主键索引字段使用or时，会导致索引失效而转向全表扫描
  select * from product where pname='品如' or pdate='2020-06-06'; -- 没有使用索引
  -- 主键索引字段使用or时，会使用range
  select * from product where pid=1 or pid=2; -- 索引type为range
  
  -- is null 或 is not null 在MySQL不同版本结果可能不同。
  -- 当前学习版本(5.6.48)，is null正常走索引，is not null 和 <>不走索引