（六）InnoDB索引优化_数据库_学习笔记

一、MySQL架构

大体来说，MySQL可以分为 Server 层和存储引擎层。

Server 层包括连接器、查询缓存、解析器、优化器和执行器等，涵盖了 MySQL 大多数核心服务功能。

存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Mermory 等多个存储引擎。

MySQL 是如何工作的？

1. 连接器
   当你在客户端输入 mysql –u $user –p $pwd 连接 mysql 的时候，接待你的就是连接器。连接器的作用就是和客户端建立连接、获取权限、维持和管理连接。
2. 查询缓存
   建立连接后，就可以执行select语句了。首先MySQL会去查看查询缓存，看下之前是否已经执行过这条查询语句。如果命中缓存，就直接返回。否则就扔给解析器。
3. 解析器
   MySQL需要知道你想做什么，因此我们就来到了解析器。解析器会做词法分析和语法分析。词法分析主要是分析每个词的含义，语法分析会判断你写的 SQL 语句是否满足 SQL 语法。
4. 优化器
   经过解析器，MySQL就知道你想做什么了。但是在开始执行之前，还需要经过优化器的处理。优化器会优化你的SQL语句。生成最终的执行方案 (execution plan)。然后进入执行器阶段。
5. 执行器
   执行器首先会判断你对这张表有没有相应的权限。如果没有，就报错。如果有，就调用相应的存储引擎接口，执行语句。然后将结果集返回给客户端。

二、存储引擎

数据的存储和提取是由存储引擎负责的，它负责和文件系统打交道。

MySQL 的存储引擎是插件式的。不同的存储引擎支持不同的特性。

选择合适的存储引擎对应用非常重要 。

# 查看MySQL支持哪些存储引擎
show engines;

# 查看默认存储引擎
select @@default_storage_engine;

# 查看某张表的存储引擎
select `engine` from information_schema.tables
where `TABLE_SCHEMA` = 'nba' and `table_name` = 'player';

1.MyISAM
MySQL 5.5 之前默认的存储引擎。
特点：
a. 查询速度很快
b. 支持表锁
c. 支持全文索引
d. 不支持事务

使用 MyISAM 存储表，会生成三个文件.
.frm # 存储表结构，是任何存储引擎都有的
.myd # 存放数据
.myi # 存放索引

索引和数据是分开存放的，这样的索引叫非聚集索引。

2.InnoDB
MySQL 5.5 以及以后版本默认的存储引擎。没有特殊应用，Oracle官方推荐使用InnoDB 引擎。

特点：
a. 支持事务
b. 支持行锁
c. 支持MVCC（多版本控制）
d. 支持崩溃恢复
e. 支持外键一致性约束

使用 InnoDB 存储表，会生成两个文件.
.frm # 存储表结构，是任何存储引擎都有的
.ibd # 存放数据和索引

索引和数据存放在一起，这样的索引叫聚集索引。

3.Memory
特点：
a. 所有数据都存放在内存中，因此数据库重启后会丢失
b. 支持表锁
c. 支持Hash和BTree索引
d. 不支持Blob和Text字段

Memory由于数据都放在内存中，以及支持Hash索引，它的查询（等值查询）速度是最快的。

一般使用 Memory 存放临时表。

临时表：在单个连接中可见，当连接断开时，临时表也将不复存在。

三、磁盘IO原理

机械磁盘

磁盘上的数据可以用一个三维地址标识: 柱面号, 盘号, 块号(磁道上的扇区)

读/写数据的步骤：

    1. 移动磁头到指定的柱面号，这个过程被称为定位或查找。
         由于是机械移动, 这部分耗时最高, 最大可达 0.1s.

    2. 根据盘面号确定从哪个磁盘读取数据

    3. 盘组开始旋转，将指定的块号移动到读/写头下
         磁盘旋转的速度很快, 一般为7200rpm。旋转一圈大约需要 0.0083s.

    4. 读写数据
         数据通过系统总线传送到内存。一般传输一个字节大概需要 0.02us. 
         读写 4KB 大约需要 80us.

磁盘读取数据是以盘块(block)为单位的, 一般为4KB。位于同一盘块的所有数据会被一次性全部读取出来。磁盘IO的代价主要花费在第 1 步。

结论：从磁盘上随机的速度是很慢很慢的，我们应该尽量少地随机读写磁盘！

四、InnoDB数据页格式

页是 InnoDB 磁盘管理的最小单位。在 InnoDB 存储引擎中, 页默认大小为16KB。
可以通过参数 innodb_page_size 将页的大小设置为 4K、8K 和 16K。

InnoDB 每次至少会将 1 个页的数据从磁盘读取到内存，每次至少也会将 1 个页的数据从内存写到磁盘。

在InnoDB存储引擎中，有很多种页类型。其中最重要的是数据页，也叫 B-tree Node。里面存储了索引和数据的信息。

• File Header
  主要存储表空间相关信息
• Page Header
  主要存储数据页的元信息
• Infimum + Supremum Records
  每个数据页中有两个虚拟行记录用来限定记录的边界，infimum record 是数据页上最小的记录，supremum record 是数据页上最大的记录。
• User Records
  用户数据，实际存储的行记录。
• Free Space
  空闲空间。
• Page Directory
  页目录，存放了记录的相对位置。
• File Trailer
  位于数据页的最后，用来检测页是否完整地写入磁盘。
  
  行记录是用链表形式组织的，最小最大记录相当于两个哨兵。Page Directory是一个数组，里面包含很多指向记录的指针(又叫 Slot)，S0指向最小记录的链表, Sn指向最大记录的链表。S1 ~ Sn-1 的每条链的长度范围为 [4, 8]。

File Header 里面有两个字段：FIL_PAGE_PREV 和 FIL_PAGE_NEXT 用来表示上一个页和下一个页，因此，页与页之间是用双链表链接的。页内的记录是由单链表从大到小依次链接的。

五、索引

1.索引是什么？
简单来说，索引的目的就是为了提高数据的查询效率，就像书的目录一样。

一本800页的书，如果想在书中查找某个知识点。在不借助目录的情况下，估计得找好一会儿。同样，对于数据库的表而言，索引就是它的 ”目录”。

索引：在 MySQL 中也叫做键 (key)，是存储引擎用于快速找到记录的一种数据结构。

2.哪些数据结构可以作为索引？

• 有序数组
• 哈希表 (hash索引)
• 平衡二叉树
• B树
• B+树

能够快速查找的数据结构都可以作为索引。

作为索引时，各个数据结构的优缺点吗？
有序数组：等值查找，区间查找都比较块。但是增删需要移动大量的元素，会很慢。

哈希表：增加、删除，等值查询都很快。但是区间查找，排序等操作会很慢。

平衡二叉树索引

不管是，增加，删除，还是等值查找，时间复杂度都是O(logn)，n 是数据页的数目。并且支持范围查找。
但是当数据量比较大，页的数目很多时，二叉树的高度会比较高。IO 的次数会比较多。查找效率低。

B树索引
B 树,它是一颗多路平衡查找树。我们描述一颗 B 树时需要指定它的阶数，阶数表示了一个结点最多有多少个孩子，一般用字母m表示。一颗m阶的B树定义如下：
1）除根结点外，每个结点的度[ceil(m/2), m]。
2）根结点的度[2, m]
3）每个结点中的关键字都按照从小到大的顺序排列，每个关键字的左子树中的所有关键字都小于它，而右子树中的所有关键字都大于它。
4）所有叶子结点都位于同一层，或者说根结点到每个叶子结点的路径长度都相同。

这是一棵4阶B树，从图中我们可以看出: 索引和数据是一起存放的。

假设一行记录是16B，那么一页大概可以存1000行记录。二层的B树大概可以存1000 * 1000行记录，三层可以存 1000 * 1000 * 1000 行记录。大大减少了树的高度，也就是减少了一次查找IO的次数。

那么，它有什么缺点吗？
当一行记录的数据很多时，B数的高度会急剧增加，性能降低。
区间查找效率低。

B+树索引
B+ 树是在B树上做了些改进。一棵 m 阶的 B+ 树定义如下：
1）B+树包含2种类型的结点：内部结点 (也称索引结点) 和叶子结点。根结点即可以是内部结点，也可以是叶子结点。根结点的关键字个数可以只有1个。
2）B+树与B树最大的不同是内部结点不保存数据，只保存关键字，所有数据 (记录) 都保存在叶子结点中。
3） m阶B+树表示了内部结点最多有m个关键字。至少有 ceiling(m/2)个关键字。
4）内部结点中的key都按照从小到大的顺序排列，叶子结点中的记录也按照key的大小排列。
5）每个叶子结点都存有相邻叶子结点的指针，叶子结点依关键字大小依次链接。

这是一棵 3阶的 B+ 树。由于内部节点只存储索引，因而每个节点可以有很多很多关键字。这样不管一行记录的数据大小，都可以保证树的高度很低。

假设一页可以存储20条记录, 1000个索引。那么：
1层：20
2层：1000 * 20
3层：1000 * 1000 * 20

而且由于叶子节点是由链表按大小依次连接的 (在InnoDB 中是双链表)。范围查找的时候，也可以避免过多的IO次数。

3.索引的好处和坏处
好处
提高数据检索的效率，降低数据库的IO成本。
a. 查找
b. 排序
c. 分组
d. 表的连接

坏处
a. 占用额外的空间。有时候索引占用的空间甚至比数据占用的空间还多。
b. 虽然索引大大提高了查询的速度，但同时也降低了更新表的速度。因为数据库不仅仅要更新数据，还要更新对应的索引信息。

索引不是越多越好！索引太多，应用程序的性能可能会受到影响; 索引太少，查询速度会变慢。我们应该建立合适的索引，找到一个平衡点！

4.InnoDB索引介绍
InnoDB 支持以下三种索引：

• B+ 树索引
• 全文索引
  全文索引是搜索关键字的，类似于搜索引擎。
• 哈希索引
  哈希索引是自适应的，我们不能自己创建。

InnoDB 中的 B+ 树索引又可以分为聚集索引 (clustered index) 和 辅助索引 (secondary index)。它们之间的不同在于，聚集索引的叶子节点存储的是一整行记录的信息，而辅助索引的叶子节点只存放部分信息 (关键字和主键)。

聚集索引
聚集索引就是按照每张表的主键构建一棵 B+ 树，同时叶子节点中存放的是整张表的行记录数据。

聚集索引的叶子节点其实就是我们前面讲过的数据页。

在InnoDB中, 记录只能存放在聚集索引中，所以每张表有且只有一个聚集索引。在大多数情况下，查询优化器倾向于采用聚集索引。

Q：如果一张表中没有主键，该怎么办呢？
找第一个定义的唯一键去构建聚集索引。
如果没有定义唯一键，又该怎么办呢？Innodb会提供隐藏的主键，根据隐藏的主键去构建聚集索引。
所以，建议创建表的时候一定要创建主键。

辅助索引
对于辅助索引，叶子节点不包含行记录的全部数据，叶子节点除了包含键（索引）以外，还包含聚集索引的键，也就是主键的信息。

辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表可以有多个辅助索引。

5.语法
创建：
1) 创建表的时候指定。会自动给 primary key 和 unique 创建索引。
2) CREATE [UNIQUE] INDEX 索引名 ON 表名(字段列表);
3) ALTER 表名 ADD [UNIQUE] INDEX 索引名 (字段列表);
删除：
DROP INDEX 索引名 ON 表名;
查看：
SHOW INDEX FROM 表名;

# 索引 
show databases;
create database index_db;
use index_db;
create table t_civil(
	id int primary key auto_increment,
    id_card varchar(255) unique not null,
    name varchar(255) not null,
    age int,
    gender enum('female','male')
);
# 创建索引 
create index idx_name on t_civil(name);
alter table t_civil add index idx_name (name);
# 查看索引 
show index from t_civil;
# 删除索引 
drop index idx_name on t_civil;

回表
思考下面SQL语句的执行过程：
select id from t_civil where id = 1; # 聚集索引
select id_card from t_civil where id_card = ‘’; # 辅助索引
select name from t_civil where id = 1; # 聚集索引
select name from t_civil where id_card = ‘’; # 先辅助索引, 后聚集索引 （回表）
select * from t_civil where id = 1; # 聚集索引
select * from t_civil where id_card = ‘’; # 先辅助索引, 后聚集索引 （回表）

当通过辅助索引来寻找数据的时候，InnoDB会遍历辅助索引，并通过辅助索引的叶子节点，获取主键。然后再通过聚集索引来找到一个完整的行记录。这个过程我们称之为回表。
如果一个辅助索引的高度为3，聚集索引的高度为3。那么我们需要6次IO操作，才可以访问最终的数据。
应该尽量避免回表！

6.Explain 介绍
查询优化器：通过计算分析系统收集的统计信息，提供它认为最优的执行计划(execution plan)。

explain：查看这个执行计划的信息。
语法：explain + select 语句
例子：explain select * from t;

我们发现explain的结果有以下列的信息：
id, select_type, table, partitions, type, possible_keys, key, ken_len, ref, rows, filtered, Extra

• id: 每个 select 子句的标识。
• select_type: select 语句的类型。simple, primary, union, subquery, derived。

# 1. select_type
# simple 简单查询
use nba;
explain select * from player;
# primary, union 联合查询
explain
select * from t_girls union select * from t_boys;
# primary, subquery 非关联子查询
explain 
select * from player where height = (
	select max(height) from player
);
# primary, dependent subquery 关联子查询
explain 
select * from player as t1 where height > (
	select avg(height) from player as t2 where t2.team_id = t1.team_id
);
# derived 子查询是衍生表
explain 
select player_name, height from player join (
	select team_id, avg(height) as avg_height from player group by team_id
) as temp using(team_id)
where height > avg_height;

• table:显示这一行的数据是关于哪张表的。
• partitions: 匹配的分表。
• type: 连接类型，又叫 “访问类型”。
  常用类型有：ALL, index, range, ref, eq_ref, const, system, null(从左到右，性能越来越好)

# 2. type: null > system > const > eq_ref > ref > range > index > ALL 
# null: 不需要查询表 
explain select @@autocommit;
# system: 表中数据最多只有一行 
explain select * from (select 'xixi' as name, 16 as age) as temp;
# const: 满足查询条件的结果最多只有一条记录, 并且和关键字比较的值是常数。
explain select * from player where player_id = 10025;
# eq_ref: 满足查询条件的结果最多只有一条记录, 和关键字比较的值不是常数。
use index_db;
create table t1(
	id int primary key,
    a int
);
create table t2(
	id int primary key,
    b int
);
insert into t1 values (1,1),(2,2),(3,3),(4,4);
insert into t2 values (1,1),(2,2),(3,3),(4,4);
explain 
select * from t1 where a = (
	select b from t2 where t2.id = t1.id
);
# ref：可能有多条满足条件的查询结果, 关键字进行等值比较 
create index idx_a on t1(a);
explain select * from t1 where a = 1;
# range: 范围查找 
explain select * from t1 where a between 1 and 2;
# index: 遍历辅助索引 
explain select a from t1;
# ALL:遍历聚集索引(全表扫描)
alter table t1 add column b int;
show index from t1;
explain select * from t1;

• possible_keys：可以选择的索引。
• key: 实际选择的索引。
• key_len: 使用索引的长度 (以字节为单位)。
• ref: 与索引比较的字段
• rows: 大概要检索的行数
• Extra: 额外信息。
  using filesort: MySQL中无法利用索引完成的排序操作称为 ”文件排序”，常见于排序和分组查询。
  using temporary: 表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询。
  using filesort 和 using temporary, 这两项比较耗时, 需要特别小心。

7.联合索引
联合索引是指对表的多个列进行索引。前面的讨论的都是只对表上的一个列进行索引。
联合索引的创建方法和单个索引创建的方法一样，不同之处仅在于有多个索引列。

联合索引的键 (1, 1), (1, 2), (2, 1), (2, 4), (3, 1), (3, 2) 是按照 (a, b) 的顺序进行存放的。

那联合索引有什么好处呢？

好处一：最左前缀法则。
B+ 树这种数据结构，可以利用索引的 ”最左前缀” 来定位记录。

我们还是利用公民表来分析这个问题。建立 (name, age) 来分析这个问题。

create index idx_name_age on t_civil(name, age); 

explain select * from t_civil where name = 'xixi' and age = 18; # idx_name_age
explain select * from t_civil where name = 'xixi'; # idx_name_age
explain select * from t_civil where age = 18; # PRIMARY
explain select * from t_civil where name like 'ab%'; # idx_name_age
explain select * from t_civil where name like '%ab'; # PRIMARY

练习：下面哪些 SQL 语句可以使用联合索引？

 1)  select * from t where a = 1 and b = 2;
 2)  select * from t where a = 1;
 3)  select * from t where b = 2;

explain select * from t where a = 1 and b = 2; # Y
explain select * from t where a = 1; # Y
explain select * from t where b = 2; # N

我们发现叶子节点上 b 的值为 1, 2, 1, 4, 1, 2，显然不是有序的。
因此对 b 列的查询使用不到 (a, b) 联合索引。

联合索引的第二个好处是：键是已经排好序的。

我们来考察这样一个例子：
很多情况下，我们需要查询某个用户的购物情况，并按照时间进行排序，取出最近 3 次的购买记录。
下面两条 SQL 语句分别会用到什么索引呢？
1) select * from buy_record where user_id = 2;
2) select * from buy_record where user_id = 2 order by buy_date desc limit 3;

create table buy_record (	
    id int primary key auto_increment,    
    user_id int not null,    
    buy_date date,
    money decimal(10, 2)
);

insert into buy_record(user_id, buy_date) values (1, '2019-01-01'), (2, '2019-01-01'), (3, '2019-01-01'), (1, '2019-02-01'),
(3, '2019-02-01'), (1, '2019-03-01'), (1, '2019-04-01');

alter table buy_record add index idx_uid(user_id);
alter table buy_record add index idx_uid_date(user_id, buy_date);
show index from buy_record;
explain select * from buy_record where user_id = 2; # idx_id
explain select * from buy_record where user_id = 2 order by buy_date desc limit 3; # idx_uid_date

你会发现第二条语句使用了联合索引，因为根据联合索引取出数据，已经是有序的了，无需再排序。若强制使用 user_id 索引，则需要一次额外的排序操作。

select * from buy_record force index(idx_uid) where user_id = 1 order by buy_date desc limit 3;

练习：
对于联合索引 (a, b, c) 下面哪些 SQL 可以利用联合索引直接得到结果，而不需要额外的排序操作？
1) select … from t where a = xxx order by b;
2) select … from t where a = xxx and b = xxx order by c;
3) select … from t where a = xxx order by c;

drop index idx_a_b on t;
create index idx_a_b_c on t(a, b, c);
show index from t;
explain select * from t where a = 2 order by b; # N 不需要额外的排序操作
explain select * from t where a = 2 and b = 2 order by c; # N
explain select * from t where a = 2 order by c; # Y

8.覆盖索引
InnoDB 存储引擎支持覆盖索引 (covering index), 即从辅助索引中就可以得到要查询的信息，而不需要回表。

注意：覆盖索引不属于索引的分类，只是一个名称。我们可以根据充当索引的字段的不同分为聚集索引和辅助索引，根据索引的数量分为联合索引和单列索引。

使用覆盖索引的好处是，覆盖索引不包含整行记录的信息，故其大小一般情况下远小于聚集索引，因此可以减少大量的 IO 操作。

对于辅助索引而言，其叶子节点包含主键信息。

思考：若主键为 (id1, id2), 辅助索引为 (a, b)。
判断下面哪些 SQL 语句需要回表？
1) select b from t where a = xxx;
2) select a from t where b = xxx;
3) select id2, b from t where a = xxx;
4) select id1, a from t where b = xxx;

explain select b from t1 where a = 3; # idx_a_b, ref 不需要回表 
explain select a from t1 where b = 3; # idx_a_b, index 需要回表
explain select id2, b from t1 where a = 3; # idx_a_b, ref 不需要回表 
explain select id1, a from t1 where b = 3; # idx_a_b, index 需要回表 

覆盖索引的另一个好处是对某些统计问题而言的。

例子：还是对于用户购买记录表 buy_record , 执行如下查询：
select count(*) from buy_record;
你会发现 InnoDB 不会选择通过查询聚集索引来进行统计。原因是辅助索引小于聚集索引，这样可以减少 IO 的操作。

思考：下面 SQL 哪些会用到哪个索引, 是否使用文件排序file_sort ?

1. select count(*) from t1 group by a;
2. select count(*) from t1 group by b;
3. select count(*) from t1 group by id1;
4. select count(*) from t1 group by id2;
5. select count(*) from t1 group by c;

#思考：下面 SQL 哪些会用到哪个索引, 是否使用文件排序?
explain select count(*) from t1 group by a; # idx_a_b, N
explain select count(*) from t1 group by b; # idx_a_b, Y
explain select count(*) from t1 group by id1; # primary, N
explain select count(*) from t1 group by id2; # idx_a_b, Y
explain select count(*) from t1 group by c; # primary, Y

9.何时该创建索引？

• 字段的值是唯一的(MySQL会自动帮我们创建索引)
• 频繁作为where条件的字段
• 频繁作为group by和order by的字段
• 频繁作为delete和update中的where条件字段
• 频繁作为distinct中的字段
• 多表连接的时候,作为连接条件的字段

10.何时不该创建索引？

• 字段起不到定位作用, 不在where, group by, order by, distinct中出现的字段。
• 表中的记录太少。
• 字段的值区分度不高。比如国籍，性别。
  特例：比如女儿国中女人的数目100w, 男人的数目为4个, 需要经常去查询男人.
  这种情形就应该给性别这个字段创建索引。
• 频繁更新的字段, 不一定要创建索引。

11.优化器选择不使用索引的情况（索引失效）
有些情况虽然建立了索引，但是优化器并没有选择索引去查找数据，而是通过扫描聚集索引，也就是全表扫描来获取数据。

• 索引字段参与了计算

select * from product_comment where user_id + 1 = 9528;

• 对索引字段使用函数

select * from product_comment where substr(comment_text, 1, 3) = 'abc';

• or前面的条件可以使用索引, or后面的条件没有索引

select * from product_comment where user_id = 9527 or comment_text like 'abc%';

• 模糊查询, 以%开头

explain select * from product_comment where comment_text like '%abc';

• 不等于和is not null作为条件

select * from product_comment where user_id != 9527;

• 区间查找的范围太大

explain select * from product_comment where user_id between 0 and 500000;  

实践策略

• 尽量使用覆盖索引, 比如尽量不要使用 select *。
• 尽量使用最左前缀法则
• 不要在索引列上做运算
• 范围查找尽量不要太大
• 尽量不要使用不等于

#个人学习记录，如发现有错误之处，欢迎与我交流