mysql索引知识点

1. MySQL 索引

MySQL 的索引是一种加快查询速度的数据结构，索引就好比书的目录一样能够快速的定位你要查找的位置.

MySQL 的索引底层是使用 B+ 树的数据结构进行实现，结构如下图所示:

        索引的一个数据页的大小是 16KB，从磁盘加载到内存中是以数据页的大小为单位进行加载，然后供查询操作进行查询。若是查询的数据不在内存中，才会从磁盘中再次加载到内存中.

       索引的实现有很多，比如 hash。hash 是以 key-value 的形式进行存储，适合于等值查询的场景，查询的时间复杂度为 O(1)。因为 hash 储存并不是有序的.

所以，对于范围查询就可能要遍历所有数据进行查询。而且不同值的计算还会出现 hash 冲突，所以 hash 并不适合于做 MySQL 的索引.

       有序数组在等值查询和范围查询性能都是非常好的，那为什么又不用有序数组作为索引呢？

        因为对于数组而言作为索引更新的成本太高，新增数据要把后面的数据都往后移一位，所以也不采用有序数组作为索引的底层实现.

       二叉树。主要是因为二叉树只有二叉，一个节点存储的数据量非常有限，需要频繁的随机 IO 读写磁盘。若是数据量大的情况下二叉的树高太高，严重影响性能，所以也不采用二叉树进行实现.

       B树，每个节点既是数据节点又是索引节点. 平衡多路搜索树降低了树的高度, 比二叉树要好很多;

       B树逻辑上相距很近的节点实际上相距很远, 无法很好的利用磁盘预读，不满足局部性原理的条件; 这类平衡多路搜索树就不满足文件系统和数据库的要求. 所以计算机文件系统和数据库系统没有选择它作为数据结构.

空间局部性原理：如果一个存储器的某个位置被访问，那么将它附近的位置也会被访问。

       而 B+ 树是多叉树，一个数据页的大小是 16KB，在 1-3 的树高就能存储 10 亿级以上的数据，也就是只要访问磁盘 1-3 次就足够了。并且B+树的叶子结点上一个叶子结点有指针指向下一个叶子结点，便于范围查询(满足局部性原理):

种类的索引

索引从数据结构进行划分的分为：B+ 树索引、hash 索引、R-Tree 索引、FULLTEXT 索引.

索引从物理存储的角度划分为：聚族索引和非聚族索引.

从逻辑的角度分为：主键索引、普通索引、唯一索引、联合索引以及空间索引.

2. 什么是回表

        在详细了解什么事回表之前，我们先来详细的深入了解一下什么是 InnoDB 的索引存储形式。InnoDB 的主键索引存储形式是聚族索引，索引与数据都在一起：

       InnoDB 的主键索引中叶子结点并不是存储行指针，而是存储行数据。二级索引中 MyISAM 也是一样的存储方式，InnoDB 的二级索引的叶子结点则是存储当前索引值以及对应的主键索引值。

        InnoDB 的二级索引带来的好处就是，减少了由于数据移动或者数据页分列导致行数据的地址变了而带来的维护二级索引的性能开销。因为 InnoDB 的二级索引不需要更新行指针：

        上面说到 InnoDB 引擎的主键索引存储的是行数据，二级索引的叶子结点存储的是索引数据以及对应的主键。所以，回表就是根据索引进行条件查询，回到主键索引树进行搜索的过程：

因为查询还要回表一次，再次查询主键索引树，所以实际中应该尽量避免回表的产生。

3. 解决回表

解决回表问题可以建立联合索引进行索引覆盖，如图所示根据 name 字段查询用户的 name 和 sex 属性出现了回表问题：

那么我们可以建立下面这个联合索引来解决：

create table user ( id int primary key, name varchar(20), sex varchar(5), index(name, sex)) engine = innodb;

建立了如上所示的 index(name,sex) 联合索引，在二级索引的叶子结点的位置就会同时也出现sex字段的值，因为能够获取到要查询的所有字段，因为就不用再回表查询一次。

4. 最左前缀原则

最左前缀原则可以是联合索引的最左 N 个字段，也可以是字符串索引的最左的 M 个字符。

举个例子，假如现在有一个表的原始数据如下所示：

并根据 col3 、col2 的顺序建立联合索引，此时联合索引树结构如图下所示：

叶子结点中首先会根据 col3 的字符进行排序。若是 col3 相等，在 col3 相等的值里面再对 col2 进行排序，假如我们要查询 where col3 like 'Eri%'，就可以快速的定位查询到 Eric。

若是查询条件为 where col3 like '%se'，前面的字符不确定，表示任意字符都可以。这样就可以导致全表扫描进行字符的比较，就会使索引失效。

5. 调优案例一

其中第一个案例新人写了一个这样的 SQL。由于业务的原因就不粘贴全部 SQL，其中 SQL 的条件如下所示：

WHERE ( STATUS = '1' AND shop_code = 'XXX' )  GROUP BY act_id

并且他在表中建立的这样的一个索引 idx_status_shop_code，当时看到就吐血一地。

我给新人提出建议是把 shop_code 为 '' 空字符串的（数据库默认值）是设为一个特殊的 00000 这样的类型，不要把商店的 shop_code 默认值设置为空字符串。

并且，索引中 shop_code 字段放在前面，建 idx_shop_code_status_act_id 索引。

因为一般状态值 status 只有 0 和 1，区分度不大，而 shop_code 的区分度大。在执行 where 条件筛选的时候，区分度大的放在前面。第一层过滤的时候就能过滤掉大部分行数，减少扫描的行数提高效率。

并且将 act_id 和 store_code 也加入其中，SQL 中涉及分组（group by）操作，这样就能避免 filesort 排序，因为索引本来就是有序的：

并且从他的 SQL 中可以看到，他只要返回 act_id 而实际中只用到了act_id。所以，将act_id 也加入索引中就可以避免回表的操作。在索引中最后加入 act_id 有两大好处：避免回表以及避免 filesort 排序。

因为写 SQL 的不良习惯造成回表操作，平时也没有注意建立索引的一些原则以及理解索引的一些原理，所以新人对于一些优化的理解还有要一步一步的指导，毕竟自己也是从新人过来的。

对于索引的顺序的建立，以及出现 filesort 的解决方案在阿里的开发手册中也有提到：

order by 和 group by 都可以利用索引的有序性。

说真的，这本开发手册还是非常好用。里面很多经验总结，慢慢的遇到场景就能够瞬间顿悟，毕竟是众多阿里人的开发经验的结晶。

6. 调优案例二

第二个案例是关于字符串的。新人接到一个需求需要比较电话，但是一般电话在数据库中都会加密 md5。

所以，新人为了提高查询的效率新建 KEY idx_mobile_md5_type (mobile_md5,type) 使用 md5 全字段建立索引。

我是建议他使用

select count(distinct left(mobile_md5, 5))/count(*) from XXX.users

查找最大的区分度：

在实际中当 md5 值长度为 5 以及大于 5 的长度都不变了：

实际情况只要前五个字符就能达到 80% 的区分度，并且再加字段长度区分度也不变，所以个人提出只要建立前五个字符的索引即可，可以大大节约空间。

这个在阿里的开发手册也有提到，其实一般来说达到 90% 的区分度是比较好的。区分度越大，就类似于越趋向于唯一索引，过滤的行数就越多：

7. 调优案例三

最后一个字符串的案例就是 userId，这个 userId 使用有 20 位的长度的字符串左右，有点类似于身份证号码，大家都知道身份证号码的前多少位是基本一样的，区别大的在后面的几位（具体几位没去了解过）。

我们这边的场景也是一样，userId 前 10 位左右基本都是一样，反而只有后面的几位区别度高达 90% 以上。

所以，建议新人建立 userId 的反转之后的前几位索引即可，区别度可以通过：

select count(distinct left(REVERSE(userId), 7))/count(*) as '区分度' from XXX.users;

具体 SQL如下：

select u.city_code from XXX.city_role_user u where  role_key = 'XXX' and uc_id = 'XXX' and status = 1;

这个 SQL 有两个问题：

• 一个是把区分度不大的 role_key 放在前面。因为一般角色 key 在 PC 端只有几种，区别度很小；

• 另一个就是前面说的 uc_id 字符串问题。

我是建议，把 where 条件的 uc_id 放在前面，建立索引也是如此。并且 uc_id 是由 20 位的数字组成，前面的 10 位左右都是一样的，只有后面的几位区分度才大。

所以，个人也建议通过查询区分度。并且建立翻转字符串后的索引来达到节省空间，并且还可以提升查询效率，最后就是 city_code 也加入索引中建立联合索引就可以避免回表操作。

所以，这就要 SQL 优化的关键点有三个：区分度大的放在前面、字符串减少长度、避免回表。

8. 其它的 code review

通过 code review 新人的代码，还发现一些问题。就是不遵循接口的单一原则，比较喜欢写通用的接口，一个接口多个场景使用，通常使用 select * 返回数据。对于一些 where 条件的查询需要大量的回表操作，但是一些接口中只需要用到其中 select 回来的一个字段，所以导致慢 SQL，慢接口的产生。

并且，在实际的编码中主要是面向于实现。对于一些整体的模块没有把控，类似于一些可以使用到策略模式、建造者模式等设计模式的地方都没有使用，代码的扩展性比较差。

还要在代码中大量的使用 Java 8 的 stream 流操作，代码的可读性差，对于 stream 流其实可以用来并行流处理还是挺高效的，因为 stream 流的底层使用到了 Fork/Join。

在服务器配置允许的条件下，使用如下代码，数据量大的时候是可以有效率提升的。下面引用 redspider 的一个案例：

public class StreamParallelDemo {    public static void main(String[] args) {        System.out.println(String.format("本计算机的核数：%d", Runtime.getRuntime().availableProcessors()));
        // 产生100w个随机数(1 ~ 100)，组成列表        Random random = new Random();        List < Integer > list = new ArrayList < > (1000 _0000);
        for (int i = 0; i < 1000 _0000; i++) {            list.add(random.nextInt(100));        }
        long prevTime = getCurrentTime();        list.stream().reduce((a, b) - > a + b).ifPresent(System.out::println);        System.out.println(String.format("单线程计算耗时：%d", getCurrentTime() - prevTime));
        prevTime = getCurrentTime();        list.stream().parallel().reduce((a, b) - > a + b).ifPresent(System.out::println);        System.out.println(String.format("多线程计算耗时：%d", getCurrentTime() - prevTime));
    }
    private static long getCurrentTime() {        return System.currentTimeMillis();    }}
索引失效

其它:

in 走索引，但是in的取值范围过大时，索引会失效，走全表扫描;

not in 不走索引;

如果明确知道只有一条结果返回，limit 1 能够提高效率;

索引效果不明显场景

数据库表记录比较少，建索引的优化效果就不明显;

数据区分度不高，建索引效果也不明显;

索引覆盖:

mysql 根据条件查询数据，索引列中就已经包含了我们需要的数据;

索引idx_index(A, B), 根据A, B条件查询就已经能找到我们所需要的列, 不需要再回表，就表示索引覆盖;

B树的应用场景

定义

一棵m阶的B-树,或为空树,或为满足下列特性的m叉树:

(1)树中每个结点至多有m棵子树(m>=2)。

(2)除非根结点为叶子结点,否则至少有两棵子树。

(3)除根之外的所有非终端结点至少有┌m/2┐棵子树。

(4)每个结点存放至少m/2-1（取上整）和至多m-1个关键字；(至少2个关键字）

(5)非叶子结点的关键字个数=指向儿子的指针个数-1；

(6)所有的非终端结点的结构如下:

其中,k1,k2,...,kn为n个按从小到大顺序排列的键值;

(7)所有叶子结点在同一个层次上,且不含有任何信息。

B树的特点，非叶子结点存储了数据，如果直接搜索某一个数据节点的情况下，性能会比B+树好，节约了IO次数，根据条件直接匹配非叶子结点的数据节点而不用搜寻到叶子结点; 这种情况也不需要考虑数据局部性原理.

参考: 

手把手教你调优

MySQL 索引优化指南 - heaventouch - 博客园

一文彻底搞懂MySQL基础：B树和B+树的区别_mysql b树和b+树的区别-CSDN博客