如何创建高性能索引

为什么需要索引?

避免去扫全表,存储引擎先走索引找到对应值,然后根据索引记录找到对应的数据行.

优点的话,因为索引使用B+树结构 使得查询数据不再是随机IO,且避免去扫描全表

索引的策略(使用)

单列

不要在where 后面针对索引列计算,因为无法自动解析计算,所以需要避免索引列的一些计算

image-20211013193141024

前缀索引

使列的前缀区分度趋近于这个列的完整列的区分度,这个时候可以考虑使用前缀索引,避免了索引过大,但同时区分度

差不多的情况.

image-20211013193656198

image-20211013193530306

索引合并的优化?

image-20211013194453445

当服务器对多个索引进行联合操作时候,通常会消耗大量的CPU,内存等,有的时候or的索引列区分度不高,查询优

化器评估可能还不如扫全表来的成本低,可能就会放弃走索引. 所以遇到extra using union 合并索引优化的时候

需要看看自己的索引建立的是否合理?

如何选择合适的索引顺序?

如果没有排序或者分组的情况下,将区分度最高的列放在最前面是合理的,但如果涉及排序或者分组的时候,还是应该像前缀索引那样针对查询的结果集的区分度来做判断

image-20211013200842505

聚簇索引

索引和数据行聚合在一起的,通常数据行不可能放在两个地方,所以聚簇索引的索引一般都是主键

聚簇索引默认是主键,如果表中没有定义主键,InnoDB 会选择一个唯一的非空索引代替

image-20211014153327555

  • 缺点: 当主键发生变化的时候,要插入一个已经满的数据行内时,会发生“页分裂”的问题, 导致数据行不连续,导致

    全表扫描变慢,其次产生的”页分裂“ 也会占有额外的内存.

  • 优点: 在B+树中查询会更快些,且使用覆盖索引扫描的查询可以直接用叶子结点上的主键

覆盖索引

索引包含了要查询的列,那么就没有必要回表查询(数据行).

优点: 如果走覆盖索引,返回的行数明显会少很多, 对服务器的缓存 IO 负载也会好很多

什么是“延迟关联“?
超多分页的问题

随着偏移量 offset 的增加,MySQL 需要花费大量的时间来扫描需要丢弃的数据。本质上就是 offset 过大导致

的大量回表 I/O 查询。

通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据。

image-20211014162823668

使用索引来排序

image-20211014165259909

image-20211014165042661

当第一列是范围查询的时候也不能使用索引作排序查询

1
2
3
....
where retal_date>"2005-05-25" order by inventory_id,customer_id
....

注意避免 冗余 & 重复& 未使用索引

索引和锁

在返回给服务器之前的,能够通过索引过滤无效行的形式去避免无效行的锁,返回到服务器数据可以通过where 过滤

之后释放锁.

image-20211016095647397

use index 索引返回过滤 1-5 行的数据 , use where 索引返回行给服务器,应用 where 过滤

image-20211016100036309

虽然查询只返回了24行的数据,但实际获取的是14行之间的排他锁,InnoDB 会锁住第一行,因为查询优化器 选择

的执行计划是索引范围扫描

索引案例学习

IN or > ?

image-20211016103048834

explain 结果来看无法区分 这二者的区别,但是 可以从值的范围和多个等于条件来得出不同,第二个就是等值查询

那二者在查询效率上有什么区别吗?

简单的来说第二种等值查询后续列可以继续使用索引,但是第一种范围查询后续列不可以使用索引,因为查询只能使

用索引的最左匹配.所以应该尽可能将范围查询放在索引列的后面.以便优化器可以使用尽可能多的索引列.

总结
  • 服务器从存储中读取的块尽可能多的包含所需要的行

  • 顺序IO 比随机IO快,特别是对机械硬盘,如果服务器能够顺序获取数据,那服务器则无需额外的排序

  • 覆盖索引是很快的,如果索引列能返回所有的查询数据,则存储引擎无需回表查询数据行

请我吃🍗