文章概览：

mysql索引数据结构

sql执行分析

常见sql优化

一、mysql索引结构

InnoDB存储引擎的索引存储有B+树、Hash两种数据结构存储，Hash的索引查找速度是最快的，但使用场景非常具有局限性（表查询只能进行等值查询，不适用范围查找），所以本文重点讨论的是Mysql底层默认使用的B+树的数据结构。

A.聚集索引

聚集索引指的是非叶子节点只存储主键值，叶子节点存储所有的数据。

B.非聚集索引

非聚集索引也叫辅助索引，叶子节点不存储所有数据，只存储主键值

C.联合索引

联合索引也是辅助索引，非叶子节点存储多个字段，按照创建索引的顺序排列

Mysql底层采用B+树变种后的数据结构来存储索引。叶子节点是一个双向链表，当定位到某个节点后可以向前或向后指针移动进行范围检索。虚线框为大的节点，mysql默认一个大节点的大小为16K，基本上mysql利用这种结构，大概层级高度为3的话将近可以存储一千万级别的数据量。

二、sql执行分析

mysql提供了explain工具可以分析sql语句的执行计划。直接在sql语句前面加explain 既可以，如：

下面重点讨论一下explain几个重要的属性列。

sql语句如下：

id: 最大的先执行，id相同的按照行顺序执行。

select_type: 有simple、primary、subquery、derived等几种,primary表示复杂查询里面的最外层。subquery为子查询.

table:当前行访问的具体哪个表.

type:type表示的是表关联关系的类型或者是访问行数据的类型，一般来讲type列值的性能由高到低的顺序为system>const>eq_ref>ref>range>index>all.通常sql优化的绝大多数手段就是将type=all的值尽量优化到range级别。

possible_keys:表示查询过程中可能使用哪个索引。

key:实际查询使用的哪个索引

key_len:索引长度,计算规则：

字符串：char---n长度,varchar---3n+2,n表示varchar的长度

数值类：tinyint---1,smallint---2,int---4,bigint-8

时间类型：date---3,timestamp---4,datetime---8

    注意：mysql内部索引最大长度支持为768，索引会按照最左原则一次尝试去获取索引列，直到超出范围前。

ref:记录索引的列

rows:执行过程中大致需要遍历的表的行数。

Extra:额外的信息描述，索引的使用情况、排序的算法等，后面有样例进行说明.

三、常见的sql优化

样例表：

日程任务表：核心字段，id-主键，title-标题，task_date-任务时间，create_date-创建时间等

1) 按照标题查找全值匹配：

没加索引前：

加索引后：

如果是经常需要拿来查询的列，那么久可以考虑给这一列加一个辅助索引。

2）like匹配

第一种like 关键词+%会走索引，第二种like %+关键词+%不会走索引，为什么呢？索引的分析离不开索引树的树结构B+树。如果一开始就有%那么节点匹配不好定位大致的位置，而一开始就是先关键词的话，可以按照索引排好序的规则先大致定位出节点的位置，任何一次按顺序进行匹配。

mysql的 %+关键词+%这种搜索没有办法进行索引优化，如果确实业务需要的话，而且对性能要求高那么只能考虑类型elasticsearch这种方案了。

3）多字段按照最左索引法则

建立联合索引：

a.按照时间查找

可以看到key_len只是2，说明只是匹配了finish_flag字段，后面create_date没有利用上索引，这是为什么呢？mysql对索引的使用是有条件的，任何在值匹配的过程中不能使用函数包裹，否则就不会走索引。create_date用date函数包裹了。还有一些隐藏的函数如：你id是整形的数值，在比较时候使用id='1'mysql默认底层会用cast函数进行数值转换。

以上的这种场景可以使用范围查找来进行优化，如：

如果直接绕过第一个属性字段呢：

可以看到type是all，联合索引没有使用到。根据联合索引左右原则，B+树首先得按照第一个字段开始匹配，否则树没法定位到节点上。

本文先介绍到这里，后续继续讨论有关order by、group by、多表联合查询的优化案例。