阅读 74

mysql索引

物理存储结构

image-20210401131310090

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的机制,会将辅助索引匹配记录的主键取出来在内存中进行排序,然后在回表
  • 按照情况建立联合索引来避免排序所带来的性能损耗,允许的情况下也可以建立覆盖索引来避免回表
文章分类
后端
文章标签