设计的一般原则

  1. 未来对表进行查询的时候，大概会如何来进行查询/或者等业务系统开发完毕，依据写好的SQL
    • 针对 SQL 里的 where 条件、order by 条件、group by 条件设计索引
    • 审视字段顺序，是否符合最左匹配原则
    • idx(a,b,c) ，SQL 包含 where a=? and b=? order by a,b group by a ，那么是可以用上索引的
  2. 选择字段的区分度大（字段基数问题）
    • 类似性别，只有0 和 1，基数是2，若是对该字段建立索引的话，不如全表扫描。因为索引树里面就只包含 0、1两种值，没办法进行快速的二分查找，还得回表。
    • 特殊：在积分补发的Job 中，定时扫描发放失败状态 task_status = 4 的数据，进行补发的场景中。
  3. 对varchar(255)的字段值
    • 值太大，放在索引树里太占磁盘空间了，可以考虑取前20个字符建立索引，对这个字段的每个值的前20个字符放在索引树里。 建立类似于KEY my_index(name(20),age,course)形式 的索引。因为索引树中的name不是完整字段值，定位到索引后，得回表取完整的name值，进行对比。 假如你要是order by name，那么此时你的name因为在索引树里仅仅包含了前20个字符，所以这 个排序是没法用上索引了！group by也是同理的。
  4. 别对索引字段用函数，导致失效
  5. 插入操作
    • 此时的数据一般都是无序的，且包含了 索引字段的数据。那么就得更新聚簇索引 和 其他二级索引树。因为插入的数据值，不可能是按照顺序来的，那么就会导致页分裂，非常耗时。所以一张表建议两三个索引即可。
    • 主键一定要是自增的，可以避免插入数据时，聚簇索引可以频繁的页分裂，只在最后面新增一个页插入数据即可。用UUID会导致聚簇索引频繁的页分裂。（因为上一页的主键 > 下一页的主键，当插入数据不满足该条件时，就得进行调整重新排序）