Mysql优化——索引结构与原理

索引

索引是帮助MySQL高效获取数据的排好序的数据结构

索引的数据结构

索引的数据结构主要有4种：

1. 二叉树
2. 红黑树
3. Hash表
4. B-Tree

其中Mysql使用的索引数据结构为：Hash表和B-Tree，其中B-Tree的变种B+Tree尤为重要。

Hash表

特点

1. 对索引的key进行一次hash计算就可以定位出数据存储的位置。
2. 很多时候Hash索引要比B+ 树索引更高效。
3. 仅能支持 “=”，“IN”，不支持范围查询。
4. hash冲突问题。
   

B-Tree

特点：

1. 叶节点具有相同的深度，叶节点的指针为空
2. 所有索引元素不重复
3. 节点中的数据索引从左到右递增排列
   

B+Tree(B-Tree变种)

特点：

1. 非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引
2. 叶子节点包含所有索引字段
3. 叶子节点用指针连接，提高区间访问的性能
4. 节点是递增排序的
5. 叶子节点之间存在双向指针
   
   节点特色：
6. 每个节点16KB
7. 非叶子节点由索引和地址链接组成，索引是bigint类型占8B，地址链接占6B，可以存储个数据16KB/(8+6)B=1170，也就是每个非叶子节点可以存储1170个索引数据
8. 叶子节点由索引和数据组成，每条数据一般占少于1KB，所以叶子节点大概可以存储16个索引数据
9. 3层B+ Tree一共可以索引约2千万（1170117016 ）条数据 。
10. 非叶子节点是常驻内存的

双向指针作用：便于快速进行范围查找，查找逻辑是先找到一个边界的叶子节点，然后再逐个节点查找下去（通过双向指针连接下一条数据）

InnoDB索引的类型

聚集索引：聚簇索引，索引的叶子节点包含了完整的数据记录，每张表只有一个聚集索引

非聚集索引：索引的叶子节点只存储主键id，原因：保证数据的一致性和节省存储空间。

联合索引：复合索引，非聚集索引的一种，索引的叶子节点只存储

备注：
推荐使用整型的自增主键，原因如下：

1. 索引对比效率较高，UUID对比 比 整型对比效率高
2. 当没有设置主键时，数据库会自己找一个字段作为主键
3. 自增 比 非自增效率高，非自增主键添加时可能会操作索引需要平衡节点操作（耗时）

回表

当查询非聚集索引时，为了返回完整数据需要用主键id查询聚集索引。
回表操作是耗时操作

InnoDB索引的数据结构

Mysql有多种存引擎，其中 InnoDB是最常用的引擎，其索引的数据结构是B+ Tree

最左前缀原理（重点）

对于联合索引，指的是查询从索引的最左前列开始并且不跳过索引中的列。
比如有下图所示的联合索引：

当执行
select * from 表 where name=‘Bill’ and age > 30 可以使用索引

select * from 表 where age > 30 and name=‘Bill’ 不可以使用索引

Explain详解

explain 两个变种

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

 mysql> explain extended select * from film where id = 1;

mysql> show warnings;

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

explain中的列

接下来我们将展示 explain 中每个列的信息。

1. id列
   id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。
   id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。

2. select_type列
   select_type 表示对应行是简单还是复杂的查询。
   1）simple：简单查询。查询不包含子查询和union
   2）primary：复杂查询中最外层的 select
   3）subquery：包含在 select 中的子查询（不在 from 子句中）
   4）derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）
   5）union：在 union 中的第二个和随后的 select

3. table列
   这一列表示 explain 的一行正在访问哪个表。
   当 from 子句中有子查询时，table列是 格式，表示当前查询依赖 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
一般来说，得保证查询达到range级别，最好达到ref

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

const, system：mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的结果）。用于 primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。system是const的特例，表里只有一条元组匹配时为system。

eq_ref：primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。

ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。

1. 简单 select 查询，name是普通索引（非唯一索引）
2. 关联表查询，idx_film_actor_id是film_id和actor_id的联合索引，这里使用到了film_actor的左边前缀film_id部分。

range：范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。

index：扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些。

ALL：即全表扫描，扫描你的聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了。

7. key_len列（重点）
   这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。
   举例来说，film_actor的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个int列组成，并且每个int是4字节。通过结果中的key_len=4可推断出查询使用了第一个列：film_id列来执行索引查找。
   mysql> explain select * from film_actor where film_id = 2;

key_len计算规则如下：

• 字符串，char(n)和varchar(n)，5.0.3以后版本中，n均代表字符数，而不是字节数，如果是utf-8，一个数字或字母占1个字节，一个汉字占3个字节
  • char(n)：如果存汉字长度就是 3n 字节
  • varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为varchar是变长字符串
• 数值类型
  • tinyint：1字节
  • smallint：2字节
  • int：4字节
  • bigint：8字节
• 时间类型
  • date：3字节
  • timestamp：4字节
  • datetime：8字节
• 如果字段允许为 NULL，需要1字节记录是否为 NULL

索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。