Mysql索引

这是一篇关于Mysql索引的思维导图，主要内容有为什么需要索引?索引的分类、如何避免回表查询?索引覆盖高性能索引策略。

Mysql索引

• 为什么需要索引？
  • 其他环境运行很快，生产环境随着数据量越来越大，查询效率更慢了，用户反馈某个查询界面需要5-10秒才能打开，抱怨声音很大
  • mysql.cnf配置文件里新增三行配置，记录生产环境慢sql的日志
    • 1、打开慢查询日志 slow_query_log=1 2、慢查询日志存储路径 slow_query_log_file=/var/log/mysql/log-slow-queries.log 3、SQL执行时间大于3秒，则记录日志 long_query_time=3
  • 运维人员/开发人员 找到具体的慢sql后，反馈给后端开发人员，列到工作事项里面去，组织一起讨论有没有sql优化空间和具体的优化方案(群众的力量远远优于埋头苦干的个人力量)
  • 当生产环境的数据量大到一定程度后，业务复杂度也越来越大，无法通过sql优化解决查询耗时过多问题下，就需要适用索引，加快查询效率
• 索引的分类
  • 从物理存储角度
    • 聚族索引
      • 叶子节点存放了索引列以及行数据(索引列通常是主键)
        • 1 A1 B1 C1 D1 E1；2 A2 B2 C2 D2 E2；3 A3 B2 C3 D3 E3;
      • 一个表只能有一个聚族索引，因为这是表数据实际存储的物理形式
    • 非聚族索引
      • 叶子存放了索引列以及主键
        • A1 1; A2 2; A3 3;
        • A1,C1 1; A2,C2 2; A3,C3 3;
      • 根据主键从聚族索引中查找行数据
  • 从数据结构角度
    • B树 索引
      • 默认的索引实现形式，适用B+树数据结构存储
      • 索引列顺序存储，便于范围查找
    • Hash 索引
      • 使用哈希表来实现
        • key是索引列的hash值
        • value指向对应行的指针
      • 局限性
        • 必须索引列全值匹配，不支持部分匹配
        • 不适用排序，索引数据不是顺序存储
        • hash冲突的代价
  • 从逻辑角度
    • 主键索引
      • 主键索引是一种特殊的唯一索引，不允许有空值，物理存储形式通常是聚族索引
    • 单列索引(普通索引)
    • 多列索引(复合索引)
      • 复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合
        • 在写sql的where条件时要特别注意"最左前缀"匹配，否则不能如期使用索引
    • 唯一索引或非唯一索引
      • 唯一索引的查询效率高于非唯一索引，插入速度要慢一些
      • 阿里巴巴JAVA开发规范》中有这么一段话 【强制】业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引
        • 说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。
• 如何避免回表查询？索引覆盖
  • 索引覆盖是什么？
    • 覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引不存储索引列的值，所以mysql只能用B+ tree索引做覆盖索引
    • 当能通过读取索引就可以得到想要的数据，那就不需要回表读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫做索引覆盖。
    • 索引包括所有需要查询的字段
    • 避免了对主键(聚族)索引的二次查询
      • 例如此时有一张表table1,有一个联合索引(a,b)；执行如下SQL select a,b from table1 在索引上就能找到结果，就不用回表去查询！ 而你执行的是 select a,b,c from table2 c列在索引上不存在，就需要回表查询。虽然会先通过联合索引先缩小查询范围，但对c列的过滤还是得回表查询
• 高性能索引策略
  • 独立的列
    • 索引列不是表达式的一部分
    • 索引列不是函数的一部分
  • 最左匹配原则
    • 需要根据业务场景
    • 需要考虑group by,order by场景
  • 使用短索引
    • 假如索引列很长，只需匹配部分前缀即可
    • 提升查询效率，减少磁盘空间
  • 避免索引列包含null值
    • 多列索引只要有一列包含null值就导致索引失效
    • 单列索引包含null值导致失效
  • 使用聚族索引
  • 使用索引覆盖