想要了解mysql的索引，首先要了解mysql的物理存储结构

物理存储结构

mysql的物理存储结构其实就是上图所示，是一个双向链表，每个结点的都是一个数据页，

一行一行的数据就放在这个数据页中

索引

为什么会产生索引这么一个东西，很简单，就是加快查询速度啊！

其实索引就和书籍的目录一样，都是为了更快的可以找到所需要的数据

那数据库的索引究竟是什么样子那？

其实就和书籍的目录一样，就是将每一个数据页的最小数据放在目录上，根据这个数据来进行多次的二分查找

页分裂

目的：就是进行主键排序，保证所有的数据页的主键都是从小到大排序的

页分裂这个其实也很简单，数据库中那么多数据，一个数据页肯定存储不下，当存储的数据大于一个数据页的时候，就会将新的数据和这个数据页的整体数据进行排序，主键比较大的数据放在新建的数据页中

构成

其实我们从索引的作用就可以自己猜想出来索引的组成了

1. 首先，肯定是有对应数据页的主键的最小值，目的很简单，就是用于二分查找，提高查询的速度
2. 根据上一步拿到了想要找的数据所在的数据页，但是具体是那个不知道啊，所以就必须有 数据页页号

B+树

众所周知myslq数据库采用的B+树的数据结构，那究竟是为什那？

有以下三个原因

• 单一节点存储的元素更多，使得查询的IO次数更少，所以也就使得它更适合做为数据库MySQL的底层数据结构了。
• 所有的查询都要查找到叶子节点，查询性能是稳定的，而B树，每个节点都可以查找到数据，所以不稳定。
• 所有的叶子节点形成了一个有序链表，更加便于查找。

原则

最左匹配原则

比如说你建立了一个（a,b）的索引，就没有必要再建立a索引了，因为（a，b）这个所以已经包括了a索引了，所有没有必要再单独建立b索引，但是b索引还是要单独建立的，因为（a,b）是满足a且满足b的情况，与只索引b不是一个意思

当然，a和b你要考虑这两个索引的大小，尽量选择较小的那个，单独作为索引，因为这样速度相对来说会快一点

第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

等值原则

查询的字段和数据库的字段一样，而且是=进行匹配的，百分之一百会使用索引

最左前缀匹配原则

如果要用like来进行模糊查询，1%可以用上索引，但是%1就不行了

范围查找原则

如果where语句中有范围查询，那么只有联合索引的最左侧的索引进行范围查找才能用到索引

等值匹配+范围匹配原则

类型

主键索引

主键索引又被称为聚簇索引

主键索引的叶子节点存的是完整的数据页

在Key-Value的场景下，只有一个索引且是唯一索引，则适合直接使用业务字段作为主键索引。

主键索引首先要去查询主键目录，主键目录维护的就是每个数据页的页号和最小的主键值

非主键索引

别名：二级索引、辅助索引、普通索引

非主键索引的叶子节点内容是主键的值

叶子结点存储的是主键+字段值

在不影响排序结果的情况下，在取出主键后，回表之前，会在对所有获取到的主键排序

• 如果一个主键被定义了，那么这个主键就是作为聚集索引。

• 如果没有主键被定义，那么该表的第一个唯一非空索引被作为聚集索引。

• 如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个6个字节的列row_id，该列的值会随着数据的插入自增。

减少回表次数的方法

覆盖索引

一般来说，一个sql语句中只能用到一个二级索引，但是也有可能同时查多个索引树取个交集，再回表到主键索引，这个可能性是有的

区别

主键索引和普通索引的区别：主键索引只要搜索ID这个B+Tree即可拿到数据。普通索引先搜索索引拿到主键值，再到主键索引树搜索一次(回表)

那么我们是选择用业务字段来当做主键，还是一个自增字段来当做主键？

• 首先自增主键的插入数据模式，正符合了我们前面提到的递增插入的场景。每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。而有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。
• 除了考虑性能外，我们还可以从存储空间的角度来看。假设你的表中确实有一个唯一字段，比如字符串类型的身份证号，那应该用身份证号做主键，还是用自增字段做主键呢？由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级索引的叶子节点占用约 20 个字节，而如果用整型做主键，则只要 4 个字节，如果是长整型（bigint）则是 8 个字节。

显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

所以，从性能和存储空间方面考量，自增主键往往是更合理的选择

一个数据页满了，按照B+Tree算法，新增加一个数据页，叫做页分裂，会导致性能下降。空间利用率降低大概50%。当相邻的两个数据页利用率很低的时候会做数据页合并，合并的过程是分裂过程的逆过程。

覆盖索引

如果查询条件使用的是普通索引（或是联合索引的最左原则字段），查询结果是联合索引的字段或是主键，那么就不用回表操作，直接在联合索引 索引树就可以查找到数据，可以直接返回结果

必须索引包含了where条件部分和select返回部分的所有字段，才能实现覆盖索引

最好是用上limit 或者where之类的语句，来限定回表到聚簇索引的次数

唯一索引

唯一索引就是字面意思，一个表中只有一个索引，如果这个索引不是主键索引，那么它就只可能是非主键索引（即我们常说的二级索引或普通索引）

联合索引

就是依次按照每个字段进行二分查找，先定位到第一个字段的值在哪个页里，然后如果一个页里有很多条数据都一样，就会根据第二个字段来找，以此类推

联合索引的叶子节点放的是也是页，但是不是整个页数据，而是联合索引中的那些字段

设计索引

1. 尽量让使用基数比较大的字段，就是值比较多的字段用上索引，充分发挥B+树的优势

   基础比较大的字段是说，比如一个字段，虽然在所有的行中都有值，但是其实都是0和1，那么就没有必要为这个字段设置索引

   因为你的索引字段中如果只有1和0两个数字，也就没有办法利用二分查找，所以设置这个字段为索引也就没有什么意义

2. 尽量对字段的类型比较小的字段进行索引

3. 尽量不要让查询语句中有函数或者计算，这样会导致用不上索引

4. 尽量主键是自增的，不要用uuid之类的，因为如果主键是自增的，就会自然进行页分裂，但是如果不是，就会导致频繁的页分裂

5. 索引不要太多，一般两三个联合索引就可以覆盖一个表的全部查询了

   一个表中的索引太多的话，会导致虽然查询非常方便，但是增删改每次都要维护巨大的索引树，性能会急速下降

6. 尽量范围查询放在最后面，保证索引都可以用

where 筛选和order by排序实际上大部分情况下，是没有办法同时用到索引的

这个时候，推荐让where用上索引，因为where用上索引，会在数据量小的情况下，先将符合条件的where的数据加载到内存中，然后根据order by条件进行排序，在内存中排序会比直接在磁盘排序要快得多

重点

尽量利用一两个复杂的多字段的联合索引，支撑下80%以上的查询，然后利用一两个辅助索引支撑下剩余20%的非典型索引，保证99%以上的查询都能充分利用索引，就能保证你的查询和性能！

技巧

1. 比如你经常需要查询在七天之内登录的用户，完全可以将这个情况当做一个字段，新增这个字段，比如1就是七天之内登录，0就是七天之内没有登录，直接将其转换为枚举值了

注意问题

对索引字段做函数操作，优化器会放弃走树搜索功能

如果一个字段做了范围索引，那么之后的索引都会失效，所以一般建议范围索引放在最后面

排序

使用

经常用的多字段排序，可以直接按照顺序建立一个联合索引，由于索引本身就有排序，所以速度很快，直接根据索引树查找即可

要求必须要都是升序或者降序，mysql默认为升序，降序的话需要在order by后面+desc

where和orderby

当where和orderby只能选择一个使用索引的时候，如果数据量小，可以选择先where查找出来数据，之后再进行排序和分页，成本不会太大

原理

MySQL会为每个线程分配一个内存（sort_buffer）用于排序该内存大小为sort_buffer_size

• 如果排序的数据量小于sort_buffer_size，排序将会在内存中完成
• 如果排序数据量很大，内存中无法存下这么多数据，则会使用磁盘临时文件来辅助排序，也称外部排序
• 在使用外部排序时，MySQL会分成好几份单独的临时文件用来存放排序后的数据，然后在将这些文件合并成一个大文件

mysql会通过遍历索引将满足条件的数据读取到sort_buffer，并且按照排序字段进行快速排序

• 如果查询的字段不包含在辅助索引中，需要按照辅助索引记录的主键返回聚集索引取出所需字段
• 该方式会造成随机IO，在MySQL5.6提供了MRR的机制，会将辅助索引匹配记录的主键取出来在内存中进行排序，然后在回表
• 按照情况建立联合索引来避免排序所带来的性能损耗，允许的情况下也可以建立覆盖索引来避免回表