【Doctor.NEO科技圈】索引思维导图

• 索引是什么？

  • 索引是一种数据结构
    

• 

  ​数据结构

  • ​BTree O(logn)

    • 

      ​B-Tree

    • 

      ​B+Tree ​优势：范围查询

  • 

    ​Hash O(1) 优势 等值查询

  • ​全文索引

  • ​【拓展】Fractal tree

  • ​【拓展】红黑树

  • ​RTree

  • ​【拓展】LSM Tree

    • ​​延伸阅读1

    • ​延伸阅读2

    • ​​延伸阅读3

    • 写放大问题及应对

      • ​资料1

      • ​资料2

    • ​MongoDB Official BenchMark

• ​算法

  • ​查找

    • ​KMP

  • ​插入

  • ​删除

  • ​排序

    • ​索引顺序扫描

    • ​filesort

      • ​两遍扫描算法(Two passes)

      • ​一次扫描算法(single pass)

• 维护索引
  

  • 创建

    • ​不同引擎区别

      • 

        ​innodb

        • ​聚簇：索引和列一起

      • 

        ​myisam

        • ​非聚簇

    • ​警惕 InnoDB 和 MyISAM 创建 Hash 索引陷阱

      • ​私密导图

      • ​MyISAM 索引键长度不能超过1000 字节

    • ​索引设计原则

  • ​存储原理

    • ​主存(内存)

    • ​硬盘(外存)

    • ​局部性原理与磁盘预读

  • 执行计划

    • explain

      • ​id

        查询的序号，包含一组数字，表示查询中执行select子句或操作表的顺序。

        若没有子查询和联合查询，id则都是1。

        Mysql会按照id从大到小的顺序执行query，在id相同的情况下，则从上到下执行。

        • ​id相同：执行顺序由上至下

        • ​id不同：如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

        • ​id相同又不同（两种情况同时存在）：id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

      • ​select_type

        SIMPLE

        PRIMARY

        UNION

        SUBQUERY

        DERIVED 物化视图

      • ​type

        结果值从好到坏依次是：

        system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

        
        

        一般保证查询至少达到range级别，最好能达到ref

        • system：表只有一行记录（等于系统表）

        • const：表示通过索引一次就找到了

        • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配

        • ref：非唯一性索引扫描，返回匹配某个单独值的所有行

        • range：只检索给定范围的行，使用一个索引来选择行

        • index：Full Index Scan，index与ALL区别为index类型只遍历索引树

        • ​ALL：Full Table Scan

      • ​key

        • ​​实际使用的索引

          • ​如果为NULL，则没有使用索引

          • ​​如果使用了覆盖索引，则该索引仅出现在key列表中

      • ​rows

        • ​根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

      • extra

        • ​不适合在其他字段中显示，但是十分重要的额外信息

          1、Distinct：MySQL发现第1个匹配行后，停止为当前的行组合搜索更多的行。

          2、Not exists：MySQL能够对查询进行LEFT JOIN优化，发现1个匹配LEFT JOIN标准的行后，不再为前面的的行组合在该表内检查更多的行。

          3、range checked for each record (index map: #)：MySQL没有发现好的可以使用的索引，但发现如果来自前面的表的列值已知，可能部分索引可以使用。对前面的表的每个行组合，MySQL检查是否可以使用range或index_merge访问方法来索取行。

          4、Using filesort：MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。

          5、Using index：从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。当查询只使用作为单一索引一部分的列时，可以使用该策略。

          6、Using temporary：为了解决查询，MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。

          7、Using where：WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误。

          8、Using sort_union(…), Using union(…), Using intersect(…)：这些函数说明如何为index_merge联接类型合并索引扫描。

          9、Using index for group-by：类似于访问表的Using index方式，Using index for group-by表示MySQL发现了一个索引，可以用来查询GROUP BY或DISTINCT查询的所有列，而不要额外搜索硬盘访问实际的表。并且，按最有效的方式使用索引，以便对于每个组，只读取少量索引条目。

          ————————————————

          原文链接：https://blog.csdn.net/jobschen/article/details/50878854

• ​失效情况

  创建索引但是无法命中索引的8种情况。

  https://www.cnblogs.com/petrolero/p/9816397.html

  • ​where 条件隐式转换，导致和列的数据类型不一致

    • ​字符串和数字

    • ​时间date和时间戳time

  • ​使用join，但多个表之间字符编码不同

  • ​B-tree索引 is null 不会走

  • ​使用不等于查询 （!= 或者<>）

  • ​非最左前缀的like，如like %字段名% 、like %字段名

    • ​补充资料

  • ​in关键字部分场景(未验证)

    • ​补充资料

  • ​联合索引：从前往后依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用

  • ​【罕见】查询的数量是大表的大部分，应该是30％以上

  • ​【罕见】索引本身失效

  • ​【罕见】使用函数在索引列上，或者对索引列进行运算，包括(+，-，*，/，! 等)，如：select * from test where id - 1 = 9;

  • ​如果条件中有or，即使其中有条件带索引也不会使用

• ​参考

  • ​codinglabs

  • ​​覃超

  • ​​石杉

  • ​架构师之路

• 高级特性

  • ​覆盖索引 必须是BTree

    • ​概念和优化查询

      • ​补充资料

    • ​使用详解和坑注意

  • 索引长度和区分度

  • 自适应hash索引

  • 分析与优化

    • 索引使用策略及优化

    • 由浅入深探究mysql索引结构原理、性能分析与优化
      

    • Mysql Performance Optimization Indexing

• 有什么用？

  • 提升查找速度

  • ​应用案例

    • 查询：​limit优化

    • ​删除：大表删除

    • ​排序：

  • 在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法