MySQL索引相关

B+ 树

要了解索引,首先要了解 MySQL 的 B+树数据结构

为什么是 B+ 树?

和 B 树相比,多了箭头,称为叶子节点,其实就是一个单向链表,进行了排序,解决了回旋查找的问题

非叶子节点只存 key,叶子节点既存 key 又存 value(数据地址)


聚簇索引

聚簇索引就是把数据和索引放在一个文件里面,叶子节点保存行数据,数据按照索引的顺序来进行存储,也就是说索引项的顺序和表中记录的物理顺序一致,InnoDB 中,在聚簇索引之上创建的索引称为辅助索引,比如复合索引,前缀索引,唯一索引等

  • 聚簇索引是一个概念,默认实现是是主键
  • 如果表中没有定义主键,InnoDB 会选择一个唯一的非空索引代替
  • 如果没有唯一非空的索引,InnoDB 会在内部生成一个名为 GEN_CLUST_INDEX 的隐式的聚簇索引

在数据(索引)文件中,非叶子节点保存的是主键,叶子节点保存的是真实数据,因为只有一个文件

辅助索引中,非叶子节点保存的是字段的值(排序),叶子节点保存的是该条记录的主键 ID 值

所以,如果是主键索引,只需查一次,一定程度上会比 MyISAM 快,但是辅助索引要查到 ID 再到主键索引查数据,要查两次,一定程度上会比 MyISAN 慢

为什么不能 select *,要尽可能使用覆盖索引?

如果是查单个字段,在这个字段上建立了索引,只需查一次就能拿到该字段数据,如果 select *,那就要查到 ID,再根据 ID 来查询全部,查两次

非聚簇索引

将数据和索引分开存储,表数据存储顺序和索引顺序无关

在索引文件中,非叶子节点保存的是主键,叶子节点保存的是主键和数据在数据文件中的地址


索引设计原则

  • 尽量在只需要查询到的字段上加索引(where)
  • 基数小的字段不加索引(出现重复的越多越不加, 唯一才能查询快)
  • 如果字符串太长但是前面不重复, 使用短索引(根据前面几个字符就能确定唯一)
  • 不要过度使用索引, 占用磁盘空间, 更新操作影响性能
  • 建立索引的字段, 尽量不要有 null(not null + 默认值)
  • like 查询中前导模糊查询不能命中索引(like ‘%a%’ 不走索引,like ‘a%’ 走)
  • 如果字段是字符串, 查询时用整形, 索引失效
  • 复合索引(最左原则, 查询条件如果不包含第一个字段, 索引失效)
  • sql 查询 cpu 开销:or > in > union
  • or 查询如果前面的条件有索引, 后面没有, 索引失效
  • 否定查询索引失效(!=, <>, not in, not exists, not like)
  • 用 explain 来分析 sql 性能

索引失效原理

针对联合索引的情况,数据保存的时候,是有顺序的,两个字段 a 和 b,先按 a 排序,在 a 相同的情况下按 b 排序,所以如果直接查 b,是无序的,因此不走索引,要全表扫描,范围查找,模糊查询也是这个道理


索引优化

  • 最左前缀法则:如果建立的是复合索引,索引的顺序要按照建立时的顺序,即从左到右(和 B+ 树的数据结构有关),比如 abc,在这种情况下:
    • abc:有效
    • ac:a 有效 c 无效
    • bc:bc 都无效
    • c:无效
  • 不要对索引进行如下处理:
    • 计算:如加减乘除,不等,is null, is not null, or
    • 函数:如 sum(), round()等
    • 类型转换:如 id = ‘1’,本来是数字写成了字符串
  • 索引不要放在范围查找的右边
  • 减少 select * 的使用
  • like 模糊查询:只留右边的百分号,那是否两个百分号没得优化呢,也不是,可以使用覆盖索引,所谓覆盖索引就是 select 查询的字段和 where 里的字段是一致的,也会走索引
  • order by 优化,避免出现 Using filesort 文件内排序,所谓 filesort,就是说它没有按照索引本身来进行排序,而是在外面开辟了一块内存,把数据复制进去进行排序,这样内存就会被多占用一份空间,而内存是比较宝贵的资源,比较影响性能

其实也可以考虑不在 MySQL 里排序,而在代码里进行排序