Mysql索引入门篇（Innodb）

一、索引介绍

索引：帮助mysq快速查找数据的一种数据结构。

思考：怎样的数据结构可以使得mysql高效快速的查找数据呢？

下面是几种数据结构存储数据的简单比较：

链表：线性的存储数据，假设一个节点存储一数据，那么有多少条数据就会有多少个节点，生产环境下数据量最少都是几十万级别，一个对象1M的话，几十万对象根本不敢想。

1、二叉树

        二叉树是每个结点最多有两个子树的树结构，满足节点左边的节点永远比自己小，右边的节点永远比自己大的特性。二叉树和链表相比的优势是树深（mysql中1个单位的树深看作需要进行一次磁盘IO）较浅，但是若数据恰好呈从小到大的顺序的话，就会发现和链表一样，所以也不具备存储海量数据的条件，结构如下图：

2、红黑树

        红黑树又叫平衡二叉树，顾名思义会对普通二叉树做平衡。如下图中一次插入1、2、3三个数据，如果是普通二叉树的效果会类似于链表。

但是如果是红黑树的话，会自动做平衡，如下图：

所以红黑树相比二叉树来说，单个树深可以存储更多的数据，并且使得在插入、删除和查找操作中，其时间复杂度都能保持在O(log n)。但是同时可能发现在极端情况（数据从小到大排列）下，树的深度顶多减小了二分之一，对于大数据量来说，虽然听上去效率提升了一倍，但是在生产环境中还远远不够。

3、B树

        B树是一种自平衡树，能够保持数据有序。这种数据结构能够让查找数据、顺序访问、插入数据及删除的动作，都在对数时间内完成。B树可以拥有多于2个子节点（通常来说每个节点中的每个数据，都能拥有一个字节点）。与自平衡二叉查找树不同，B树为系统大块数据的读写操作做了优化，常被应用在数据库和文件系统的实现上。

​

图中随机顺序插入1-5个节点，可以发现B树同样符合数据左小右大的特点。同时B树的单个节点中可以存储多个数据，每个数据又能拥有一个字节点。假设一个节点中能存储100个数据，那么树深为3的话，整个树就能存储100万条数据，是不是很惊人！理论来说B树已经够mysql使用了，那么为什么mysql底层存储数据结构不选择B树呢？

4、B+树

        B+树和B树的数据结构类似，但是在B树的基础下进行了优化，主要优化了一下两点：

        1、非叶子节点只存储索引数据，所有的具体数据都存储在叶子节点；

        2、增加了一个相邻叶子节点的链表指针，形成了带有顺序指针的B+tree，提高区间访问的性能，利于排序；

如下图（网上找的，侵删）：

​

B+树由于非叶子节点只存储索引数据，而例如主键自增id索引，对比整条数据来说所占空间是非常小的，例如Innodb存储引擎中单节点的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个节点中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为103）。也就是说一个深度为3的B+Tree索引可以维护103 * 10^3 * 10^3 = 10亿 条记录。

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2-4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘IO。

附带一个学习数据结构的网站，简单直观：Data Structure Visualization

二、Explain关键字

1、介绍

Explain关键字可以获取Mysql执行select语句的信息，包括多个查询（左右连接、子查询）的执行顺序，以及分辨是否走索引。

2、使用

在 select 语句之前加上关键字 Explain，例如：

explain select * from tab where id = 1;

3、信息含义

id：表示执行id，id越大越先执行，id相同排在前面的先执行

select_type：表示select的类型，常见取值有：

        ● simple: 简单查询，没有使用连接或子查询

        ● primary: 主查询

        ● union: union中的第二个语句

        ● subquery: 子查询，例如select * from (select * from tab)；

type：表示查询类型，性能由好到差为：null, system, const, eq_ref, ref, range, index, all

        ● null：例如主键id的情况下select min(id) from tab；由于B+树左小右大的特点，最小的id在B+树中排在左边第一位，这个时候mysql只需要扫面主键索引树的第一个节点就能直接得出结论，速度极快；

        ● system：表中只有1条数据或空表；

        ● const：使用主键索引或者唯一索引且返回结果中只有1条数据，例如select * from id = 1；

        ● eq_ref：出现在要连接过个表的查询计划中，驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引，且必须为not null，唯一索引和主键是多列时，只有所有的列都用作比较时才会出现eq_ref

        ● ref：和eq_ref相比，不需要驱动表只返回一行数据，只要使用相等条件检索时就可能出现，常见与辅助索引的等值查找。或者多列主键、唯一索引中，使用第一个列之外的列作为等值查找也会出现。

        ● range：索引返回扫描，常见于!=, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等运算符的查询中。

        ● all：全表扫描，性能低

possible_keys：可能会走的索引

key：实际会走的索引

key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好

filtered：表示返回结果的行数占需读取行数的百分比，filtered值越大越好

extra：额外的信息

        ● using where/using index：覆盖索引，不需要回表查询数据；

        ● using index condition：普通索引，但是需要回表查询数据；

三、索引性能优化

        假设有表user，字段id，name，age，features_map四个字段，并且有个主键索引id和联合索引（name，age）

1、最左前缀法

        联合索引遵守最左前缀法。即查询从索引的最左列开始，并且不跳过索引中的列，如果跳跃其中某一列，索引将会部分失效（后面的字段索引失效)，最左前缀法和where后的字段顺序无关，因为在select语句执行过程中，server层的优化器会自动优化sql，将字段顺序和联合索引字段顺序做匹配，当然，最好还是保证开发自己做好这些优化，尽可能节省mysql资源。

        例如：

select * from user where name = '张三' and age = '18'；
select * from user where name = '张三'；

        这两条sql都遵循最左前缀法

select * from user where  age = '18'；

        这条sql由于没有使用联合索引中的name，所以不会走索引扫描；

2、覆盖索引

        覆盖索引是指查询使用了索引且返回需要的列，在该索引列中已经全部能够找到。

        例如：        

select name,age from user where name = '张三' and age = '18'；

        该sql中select的字段在索引中全部都有，则视为走覆盖索引，由于要查询的字段在联合索引的索引树中全部包含，则不需要进行回表扫描（联合索引即二级索引，叶子节点不会存储完整的数据信息，而是存储主键索引的id。优点是可以节省空间，并且可以和主键查询出来的数据强一致性不必在数据变更时进行维护，缺点是拿到主键索引id后要去主键索引树中扫描一次从而获取完整数据，即回表扫描）；

select * from user where name = '张三' and age = '18'；

        该sql中由于select的字段包含id，name，age，features_map字段，并不是全部在联合索引中，所以需要拿到主键id进行回表扫描，效率相对较低。

        因此，在查询中要尽量使用覆盖索引，减少select *。

3、前缀索引

       在某些场景下，需要对大字段加索引（varchar、text）时，查询时浪费大量的磁盘IO，影响查询效率。此时可以只对字符串的一部分前缀建立索引，节约索引空间，提高索引效率。   

        例如上述user表中features_map字段很大，要加索引的话可以对它的前几个字符加索引，前提是要保证它的前几个字段尽量不重复，否则如果大量重复，加的意义就不大。

        像邮箱等字段就很适合加前缀索引，语法：

create index idx_features_map on user(features_map(10));

4、其他

         ● order by：有文件排序和索引排序两种情况

                Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。

                Using index：走覆盖索引，利用索引的有序性直接返回，不需要在sort buffer进行排序

                order by尽量走覆盖索引，如果走文件排序的话性能较低

         ● group by：同样满足最左前缀法，并且最好走覆盖索引