「MySQL」MySQL索引的使用及优化

Posted on | In

1. 索引的基本概念

索引是为了加速对表中数据行的检索而创建的一种分散存储的数据结构。

  • 索引意义：
    1. 索引能极大的减少存储引擎需要扫描的数据量
    2. 索引可以把随机IO变成顺序IO
    3. 索引可以帮助我们在进行分组、排序等操作时，避免使用临时表
  • 增加索引会有利于查询效率，但会降低insert，update，delete的效率，但实际上往往不是这样的，过多的索引会不但会影响使用效率，同时会影响查询效率，这是由于数据库进行查询分析时，首先要选择使用哪一个索引进行查询，如果索引过多，分析过程就会越慢，这样同样的减少查询的效率，因此我们要知道如何增加，有时候要知道维护和删除不需要的索引。

2. 索引的适用场景

2.1 适合建索引的场景

  1. 表的主键自动建立唯一索引
  2. 表的字段唯一约束
  3. 直接条件查询的字段（在SQL中用于条件约束的字段）
  4. 查询中与其它表关联的字段
  5. 查询中排序的字段（排序的字段如果通过索引去访问那将大大提高排序速度）
  6. 查询中统计或分组统计的字段
  7. 表记录太少（如果一个表只有5条记录，采用索引去访问记录的话，那首先需访问索引表，再通过索引表访问数据表，一般索引表与数据表不在同一个数据块）
  8. 经常插入、删除、修改的表（对一些经常处理的业务表应在查询允许的情况下尽量减少索引）
  9. 数据重复且分布平均的表字段（假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。）
  10. 经常和主字段一块查询但主字段索引值比较多的表字段
  11. 对千万级MySQL数据库建立索引的事项及提高性能的手段

2.2 不适合建索引的场景

  1. 表记录太少（300万左右性能开始逐渐下降，虽然官方文档说撑得住5-8百万以上，但是根本也不能等到这个时候再去优化，性能肯定会受到影响）
  2. 经常增删改的表（why：提高了查询速度，同事却会降低了更新表的速度，入队表进行INSERT,UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存下索引文件）。
  3. 数据重复切分布平均的表字段，因此应该只为最经常查询和最经常排序的数据建立索引。注意，如果某个数据列包括许多重复的内容，为他建立索引就没有太大的实际效果了。（加入一个表有10万行的记录，有一个字段A只有True和False两个值，且每个值的分布概率大约为50%，那么对这种表的A字段建立索引一般不会提高数据库的查询速度。再比如对银行卡建立索引，毕竟银行卡没有重复的。索引的选择性是指索引列中不同值的数据与表中的记录数的比，如果一个表中有2000条记录，表索引列就有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1，这个索引的效率就越高。）

3. MySQl中索引的结构（B+树）

3.1 基本概念：

  1. 二叉树：一个节点最多两个子节点，一个节点只存储一个关键字，等于则命中，小于走左节点，大于走右节点；
  2. B树：多路搜索树，每个节点存储M/2到M个关键字，所有关键字在整颗树中出现，且只出现一次，非叶子节点可以命中；
  3. B+树：在B树基础上，为叶子节点增加链表指针，所有关键字都在叶子节点中出现(有序)，叶子节点才命中；
  4. B*树：在B+树基础上，为非叶子节点也增加兄弟链表指针，将节点的最低利用率从1/2提高到2/3；

3.2 B+树的特性：

  1. 所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的；
  2. 不可能在非叶子结点命中；
  3. 非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层；
  4. 更适合文件索引系统；

3.3 B+树的三个特点：

  1. 关键字数和子树相同
    • 在 B+ 树中，节点的关键字代表子树的最大值，因此关键字数等于子树数。
  2. 非叶子节点仅用作索引，它的关键字和子节点有重复元素
    • 除叶子节点外的所有节点的关键字，都在它的下一级子树中同样存在，最后所有数据都存储在叶子节点中。
    • 根节点的最大关键字其实就表示整个 B+ 树的最大元素。
  3. 叶子节点用指针连在一起
    • 叶子节点包含了全部的数据，并且按顺序排列，B+ 树使用一个链表将它们排列起来，这样在查询时效率更快。

由于 B+ 树的中间节点不含有实际数据，只有子树的最大数据和子树指针，因此磁盘页中可以容纳更多节点元素，也就是说同样数据情况下，B+ 树会 B 树更加“矮胖”，因此查询效率更快。
B+ 树的查找必会查到叶子节点，更加稳定。
有时候需要查询某个范围内的数据，由于 B+ 树的叶子节点是一个有序链表，只需在叶子节点上遍历即可，不用像 B 树那样挨个中序遍历比较大小。

3.4 B+ 树的三个优点：

  • 层级更低，IO 次数更少
  • 每次都需要查询到叶子节点，查询性能稳定
  • 叶子节点形成有序链表，范围查询方便

4. 索引的优化

4.1 优化法则（口诀）：

  • 全值匹配我最爱，最左前缀要遵守
  • 带头大哥不能死，中间兄弟不能断
  • 索引列上无计算，范围之后全失效
  • like百分写最右，覆盖索引不写星
  • 不等控制还有or，索引失效要少用
  • var引号不能丢，SQL优化也不难

4.2 具体描述

  1. 全值匹配
    • 怎么建索引就怎么用索引，where后面的条件越来越多精度越来越高，精度越来越高带来的就是长度和花费的代价也就越来越多
  2. 最佳左前缀法则
    • 指的是查询从索引的最左前列开始并且不跳过索引中的列。
    • 例如：复合索引A->B->C，如果把开头A去掉的话，B，C也就都失效了（带头大哥不能死）；如果把中间B去掉的话，则只会走索引A，而C就失效了（中间兄弟不能断）。
  3. 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换)，会导致索引失效而转向全表扫描
  4. 存储引擎不能使用索引中范围条件右面的列
    • 例如：select t from test where A=1 and B>2 and C=3，那么B>2后的查询条件失效。
  5. 尽量使用覆盖索引————只访问索引的查询（索引列和查询列一致），减少 SELECT *。
  6. MySQL在使用不等于(!= 或<>)的时候无法使用索引会导致全表扫描
  7. is null，is not null 也无法使用索引
  8. like以通配符开头('%abc...')mysql索引失效回变成全表扫描的操作（使用覆盖索引可解决），只有通配符在右面('abc...%')的才能避免索引失效。
  9. 字符串不加单引号索引失效
  10. 少用or，用它来连接时会索引失效

5. in 和 exists区别及应用场景

5.1 in 和 exists的区别:

  • 如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。
  • 其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键)，如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了 ，另外IN时不对NULL进行处理。
  • in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。

5.2 not in 和not exists

  • 如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；
  • 而not extsts 的子查询依然能用到表上的索引。
  • 所以无论那个表大，用not exists都比not in要快

6. order by 和 group by 优化

索引的主要作用就是查找和排序，ORDER BY 子句尽量使用Index方式排序，能避免使用FileSort方式排序，尽可能在索引列上外城排序操作，遵照索引键的最佳左前缀。

6.1 提高ORDER BY速度的技巧

  1. ORDER BY时不要使用SELECT *，只查需要的字段。
  2. 增大sort_buffer_size参数大小（根据系统能力去提高，因为这个参数是针对每个进程的）
  3. 增大max_length_for_sort_data参数大小

6.2 GROUP BY的优化

  1. GROUP BY实质上是先排序后进行分组，遵照索引的最佳左前缀。
  2. 当无法使用索引列，考虑增大max_length_for_sort_data和sort_buffer_size的参数设置。
  3. WHERE 高于 HAVING，能写在WHERE解决的条件就不要去HAVING限定了。

注意：group by 表面上叫分组，但是分组之前比排序。所以说group by和order by两者排序的法则和索引优化的原则几乎是一致的。当然也有不一样的地方，group by 还有having的存在。如果group by错乱，会导致临时表的产生。(就是说group by的顺序不对，建好的索引我用不上，我内部使用了内排序产生了filesort，为了把这些数据挪出来内部建了一张临时表来进行分组)

一般性建议：

  • 对于单值索引，尽量选择针对query过滤性更好的索引
  • 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段的顺序中，位置越靠左越好。
  • 在选择组合索引的时候，尽量选择可能包含当前query中的where子句中更多字段的索引
  • 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。

参考链接：https://www.zhihu.com/people/hen-six-49/activities