【MySQL】从索引数据结构 看 SQL优化

索引

MySQL如何查找数据【没有索引】

假设存在 org_user 表，没有设置主键，也没有设置索引：

当执行sql SELECT * FROM org_user where id = 7; 时，会进行 全表扫描。
mysql 会逐条取数据，然后根据 where 后面的条件进行比对。最终取出符合条件的数据。 每次取出数据都会进行一次 I/O 操作，取出几次，就进行几次I/O。当数据量达到一定大小，这种 全表扫描 就非常消耗性能。

所以 mysql 引出了索引的概念，我们可以给 频繁 查询的列添加 索引，以达到优化查询速度的目的。

什么是索引

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

MySQL 不同的存储引擎，使用索引时底层数据结构也存在差异。MySQL索引分三种：HASH 索引、BTREE 索引、全文索引。

本文所记述索引是使用量相对较多的 BTREE 索引。

BTREE 索引

BTREE 索引是通过B+ 树来实现的。
B+ 树的结构示意图：

B+树动态演示

区别：BTREE 索引和单纯B+数存在微小的区别，B+树在叶子节点之间维护了一个从小指向大的单向指针（如上图）。而BTREE 索引在叶子节点之间维护了双向指针。

BTREE索引结构特点

• 非叶子节点不存储具体数据，只存储索引(冗余)，为了可以放更多的索引
• 叶子节点包含所有索引字段
• 叶子节点用指针连接，提高区间访问的性能 图示：
  

BTREE索引为什么采用B+树实现

1. 相比于其他数据结构，树形结构在查询速度上有更大的优势，相比于Hash，树形结构在排序场景效果更好。
2. B树相比与其他树，在单端增长问题上表现较优。和平衡二叉树和红黑树相比，在大数据量时，B树的层级更少。
3. B+树相对与 B树，非叶子节点不保存数据，每个 磁盘页 可以保存更多节点。相同数据时，B+树的层级更少。且 B+树在叶子节点维护了双向指针，更适合范围查询。

mysql不同存储引擎索引结构的区别

• MyISAM
  • 非聚集索引：索引文件和数据文件是分离的，索引文件的叶子节点保存着数据在磁盘的地址指针。
  • 图示：
    
• InnoDB
  • 聚集索引：索引文件和数据文件是不分离。
    • 主键索引文件的叶子节点保存着详细的数据。
    • 非主键索引的叶子节点只保存对应的主键id，如果要查询其他字段（除主键和索引字段），需要回表查询（再次根据id查询主键索引树）。
  • 图示：
    

聚合索引(联合索引)和最左前缀原则

聚合索引(联合索引)：由多个字段共同组成一个索引。 图示：

在数据结构上会根据索引的先后顺序，依次将数据排序在b+树上。

最左前缀原则：最左优先，在检索数据时从联合索引的最左边开始匹配。
以下表为例，name, age, position 为联合索引

CREATE TABLE `org_user`  (
  `id` int(11) NOT NULL,
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `age` int(2) NOT NULL,
  `position` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `sex` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `org` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `index_name_age_position`(`name`, `age`, `position`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8

分析之前 我们需要简单了解下 EXPLAIN

explain 怎么看是否走了索引，联合索引走了哪几位？

1. type属性 system > const > eq_ref > ref > range > index > ALL。

• type 为 eq_ref\ref\range\index 这几项说明查询走的索引。

2. key_len 的长度可以用来判断走的是联合索引中的前几位。 kan_len的规则如下：
   1.字符串：

• char(n)：如果存汉字长度就是 3n 字节
• varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度 2.数值类型
• tinyint：1字节
• smallint：2字节
• int：4字节
• bigint：8字节 3.时间类型
• date：3字节
• timestamp：4字节
• datetime：8字节 4.null 值：1个字节 通过mysql EXPLAIN 解释语句分析sql

EXPLAIN SELECT * FROM `org_user` where name = 'zhouxh';

可以看出来上面的sql走的索引（其中name字段长度20，所以name索引的key_len = 62）,通过key_len 可以看出以上sql，使用了联合索引中的name 同理 通过分析以下sql

EXPLAIN SELECT * FROM `org_user` where name = 'zhouxh' AND position = '前端开发';
EXPLAIN SELECT * FROM `org_user` where name = 'zhouxh' AND age = 24;
EXPLAIN SELECT * FROM `org_user` where age = 24 AND position = '前端开发';
EXPLAIN SELECT * FROM `org_user` where name = 'zhouxh' AND age = 24 and position = '前端开发';
EXPLAIN SELECT * FROM `org_user` where name like 'zhouxh%' AND age = 24 and position = '前端开发';
EXPLAIN SELECT * FROM `org_user` where name like '%zhouxh' AND age = 24 and position = '前端开发';

可以得到以下
最左前缀原则的索引命中情况 （name, age, position 为联合索引）

	where 条件	索引使用情况
1	where name = 'xxx'	使用到 name
2	where name = 'xxx' and age = xx	使用到 name 和 age
3	where name = 'xxx' and age = xx and position = 'xxx'	索引全命中
4	where name = 'xxx' and position = 'xx'	只用都name
5	where age = xx 或者 where age = xx and position = 'xx' 或者 where position = 'xxx'	没有使用索引
6	where name = 'xxx' and age > xx and position = 'xxx'	使用到 name 和 age
7	where name like 'xxx%' and age = xx and position = 'xxx'	MySQL5.6之后：索引全名中/之前只命中name
8	where name like '%xxx' and age = xx and position = 'xxx'	没有使用索引
9	where name = 'x%xx%' and age = xx and position = 'xxx'	索引全名中
10	where name = 'xx' and age like '%xx' and position = 'xxx'	使用name 和 age

原因总结：

1. 当name字段确定时（name = xxx），age字段是有序排列的，此时无论是对age字段进行范围限定（>、<）还是 取确定值（=）都可以从B+树上给很快的找到。
2. 当name字段不确定（where 后面没有限定name），此时age字段再B+树中排列无序（Bill、HanMeimei、leff、lilei 中可能都有相同的age），此时age无法快速的定位，强行使用索引，还不如全表扫描来的快。
3. 当name字段是后置模糊查询时（name like 'xxx%'），MySQL5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。使用了索引下推优化后，查询在联合索引里匹配到名字是 'LiLei' 开头的索引之后，同时还会在索引里过滤age和position这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据。索引下推会减少回表次数，对于innodb引擎的表索引下推只能用于二级索引。
4. 当name字段是前置模糊查询时（name like '%xxx'），此时name字段是无序的，所以无法命中索引。

SQL 优化

1、联合索引第一个字段用范围不一定会走索引

EXPLAIN SELECT * FROM `org_user` where name > 'zhouxh' AND age = 24 and position = '前端开发';

索引命中不确认：在一定数据量的表中，第一个字段就是范围查询时，如果mysql内部分析后认为范围查询的数据集比较大，他会进行全表扫描，否则 mysql 会走索引。
如何优化
覆盖索引法：要求所有查询的字段都在联合索引树上中存在。
原因：此时所有数据都可以在联合索引树上获取，不需要回表查询，速度更快。

EXPLAIN SELECT id, name, age, position FROM `org_user` where name > 'zhouxh' AND age = 24 and position = '前端开发'; 

2、Order by与Group by优化

1. MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。
2. order by满足两种情况会使用Using index。
   • order by语句使用索引最左前列。
   • 使用where子句与order by子句条件列组合满足索引最左前列。
3. 尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则.
4. 如果order by的条件不在索引列上，就会产生Using filesort.
5. 能用覆盖索引尽量用覆盖索引
6. group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于groupby的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中的限定条件就不要去having限定了.

EXPLAIN SELECT * FROM `org_user` where name = 'zhouxh' AND age = '11' ORDER BY position;

不遵从 最左前缀原则时：

EXPLAIN SELECT * FROM `org_user` where name = 'zhouxh' ORDER BY position;

Using filesort文件排序两种方式【面试可能会问哦】

• 单路排序：
  • 将所有的查询数据都 load 到内存缓冲区中进行排序。
• 双路排序
  • 只将排序字段和主键 load 到内存缓冲区中进行排序，然后再根据 主键 回表，取出查询的字段。 mysql 如何选择：
• MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断，字段总大小 < 系统变量 ，则使用单路排序（速度快，耗内存）。否则，双路排序。

3、分页查询优化

我们经常有这样的场景对表某个字段(非主键索引)进行排序，然后进行分页。
最粗暴的写法自然是:

SELECT * FROM `org_user` order by name limit 100 , 10;

这样的写法在查询少数量集的时候，是可以命中索引的。
但是一旦跳转页数变得较大，页面就会变的非常卡顿。

SELECT * FROM `org_user` order by name limit 10000 , 10;

我们通过 EXPLAIN 可以发现，虽然 name 字段设置了索引，但是实际排序的时候并没有用上。
原因：
扫描二级索引并查找不在二级索引上的行数据时，需要回表。mysql认为这样的查找成本比全表扫描的成本还要高，所以mysql内部自动优化成全表扫描。

如何优化
可以在通过二级索引排序时，只查询id，然后通过id手动查询需要的行数据。

SELECT * from `org_user` a inner join (SELECT id FROM `org_user` order by name limit 10000 , 10) b on a.id = b.id;

优化后：排序时使用的是 filesort 排序，且查询时都走了索引，效率更高。

最后的最后 附上阿里巴巴对于索引的硬性规定：

1. 【强制】业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。
   • 说明：不要以为唯一索引影响了insert速度，这个速度损耗可以忽略，但提高查找速度是明显的；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。
2. 【强制】超过三个表禁止join。需要join的字段，数据类型保持绝对一致；多表关联查询时，保证被关联的字段需要有索引。
   • 说明：即使双表join也要注意表索引、SQL性能。
3. 【强制】在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。
   • 说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为20的索引，区分度会高达90%以上，可以使用count(distinct left(列名, 索引长度))/count(*)的区分度来确定。
4. 【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。
   • 说明：索引文件具有B-Tree的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。