深入浅出索引下

• 深入浅出索引(下)
  • 什么是回表？
    • eg：select * feom T where K=3 注T为表名,k列建立普通索引
      • 执行过程，先到非主键索引K上找到 k=3的记录，再根据主键Id去主键索引找到对应的行记录。这个过程回到主键索引树查询的过程称之为回表
      • 如果查询的数据仅在主键索引上存在，那么回表操作不可避免
  • 什么是覆盖索引？
    • 查询的数据在非主键索引上存在，不需要回表查询称之为索引覆盖？
      • eg :select id from T where K=3
        • 执行过程，先到非主键索引K上查询K=3的记录，同时查询到主键Id，直接返回，不需要执行回表操作
        • 覆盖索引是常见性能优化手段，能够减少树的搜索次数
        • 思考问题
          • 对于一张市民信息表，有没有必要对身份证号(非主键)和地址做联合索引？
            • 1 如果是根据身份证号查市民信息那么仅维护身份证号索引即可
            • 2如果需要根据身份证号查地址，那么有必要建立联合索引，可以减少回表操作
  • 什么是最左前缀原则？
    • 首先需要知道，索引不是越多越好，维护索引需要消耗性能和存储空间，而对于不频繁查询的字段建立索引有点浪费，如何复用索引提高系统性能就需要用到最左前缀
    • eg 现在有联合索引 (name,age)

      

      • 图中索引项是根据索引字段的顺序排列的，如果说我们查询name=张三的数据，那么根据Id可以定位到ID4，然后向后遍历即可，如果是模糊匹配 like "%张"，那么也能用上索引
      • 最左前缀是指查询字段满足联合索引的“左匹配顺序”，比如索引(a,b,c) 查询字段 a,ab,abc 均走索引，b,a 走索引 c,a a走索引c不走索引,查询字段的顺序不受影响，会被优化器优化顺序
      • 如何提高索引复用(利用最左匹配)？
        • 1、通过调整顺序，尽量少的维护索引个数
          • eg:已存在索引 a,b 则不需要建立索引a 如果需要可以单独建立索引b
        • 2、考虑索引占用的空间
          • eg：name字段比age字段大，那么考虑 创建 name,age 联合索引 和 age单独 索引 而不是 age,name name 这是因为后者占用空间比前者大，而索引占用空间大，意味着每个节点能容纳的“页大小”越小，所具有的子节点越少，增加了磁盘io的次数
  • 什么是索引下推？
    • eg：select * from T where name like "%张" and age=10

      

      • MySQL 5.6之前，首先根据非主键索引name 找到相应的主键记录ID4，再回表查询行记录比较字段，5.6之后，支持索引下推优化，能够在非主键索引同时过滤age!=10的记录，减少了回表次数【每条虚线表示回表1次】，回表次数从4->2
    • tip：设计数据库时，满足语句需求，尽量少的访问资源及减少资源消耗
  • 问题
    • 通过两个alter语句重建索引K，之后再通过两个alter语句重建主键索引是否合理？
      • 为什么要重建索引?
        • 索引K可能因为删除或者页分裂等原因，导致数据页有空洞，空间利用率下降，重建索引会使数据重新按序插入，使得页面更加紧凑，节省空间
        • 不论是删除主键还是创建主键都会导致表重建，那么如果连续执行以上语句，重建索引就白做了，建议使用 alter table T engine = InnoDB