mysql索引设计原则

索引设计原则

  1. 选择唯一性索引

    • 唯一性索引的值是唯一的,可以更快速的通过该索引来确定某条记录
  2. 为常作为查询条件的字段建立索引

    • 如果某个字段经常用来做查询条件,那么该字段的查询速度会影响整个表的查询速度。因此,为这样的字段建⽴立索引,可以提⾼高整个表的查询速度
  3. 限制索引的数目

    • 索引的数目不是越多越好
    • 每个索引都需要占⽤用磁盘空间,索引越多,需要的磁盘空间就越大
    • 修改表时,对索引的重构和更新很麻烦
    • 越多的索引,会使更新表变得很浪费时间
  4. 尽量使用数据量少的索引

    • 如果索引的值很长,那么查询的速度会受到影响
      • 例如:对一个char(100)类型的字段进行全文检索需要的时间肯定比对char(10)类型的字段需要的时间更多
  5. 为经常需要排序、分组和联合操作的字段建立索引

    • 经常需要order by、group by、distinct和union等操作的字段,排序操作会浪费很多时间。如果为其建立索引,可以有效的避免排序操作
  6. 尽量使用前缀索引

    • 如果索引字段的值很长,最好使用值的前缀来索引
      • 例如:text和blog类型的字段,进行全文检索会浪费时间。如果只检索字段的前面的若干个字符,这样可以提高检索速度
  7. 删除不再使用或者很少使用的索引

    • 表中数据被大量更新,或者数据的使用方式被改变后,原有的一些索引可能不再需要。数据库管理理员应当定期找出这些索引,将他们删除,从而减少索引对更新操作的影响
  8. 小表不应建议索引(超过200w数据的表,建立索引)

    • 包含大量的列并且不需要搜索非空值的时候可以考虑不建索引
  9. 经常被用来过滤记录的字段

    • primary key 字段,系统自动创建主键的索引
    • unique key 字段,系统自动创建对应的索引
    • foreign key 约束所定义的作为外键的字段
    • 在查询中用来连接表的字段
    • 经常用来作为排序(order by的字段)基准的字段
  10. 索引会占⽤用磁盘空间,创建不不必要的索引只会形成浪费

  11. 索引的创建必须考虑数据的操作方式

    • 内容很少变动,经常被查询,为它多创建几个索引无所谓
    • 经常性,例行性变动的表而言,则需要谨慎地创建确实必要的索引

复合索引和单一索引

  • 复合索引是指多字段联合索引,查询时经常需要这几个字段组合一起为条件再查询

  • 唯一索引主要是用主键ID索引,存储的结构顺序与物流结构一直

  • 添加和删除索引的情况

    • 表的主键、外键必须有索引
    • 数据量超过300w的表应该有索引
    • 经常与其他表进行连接的表,在连接字段上应该建立索引
    • 经常出现在where子句中的字段,特别是大表的字段,应该建立索引;
    • 索引应该建立选择性高的字段上;
    • 索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引;
    • 复合索引的建立需要进行仔细分析;尽量考虑用单字段索引代替:
      • 正确选择复合索引中主列列字段,一般是选择性较好的字段
      • 复合索引的几个字段是否经常同时以AND方式出现在where子句中?单字段查询是否极少甚⾄至没有?
        • 如果是,则可以建立复合索引;否则考虑单字段索引;
        • 如果复合索引中包含的字段经单独出现在where子句中,则分解为多个单字段索引?
        • 如果复合索引所包含的字段超过3个,那么仔细考虑其必要性,考虑减少复合的字段;
        • 如果既有单字段索引,又有这几个字段是哪个的复合索引,一般可以删除复合索引;
  • 频繁进行数据操作的表,不要建立太多的索引

  • 删除无用的索引,避免对执行计划造成负面影响

posted @ 2019-12-21 12:18  太虚真人  阅读(3017)  评论(1编辑  收藏  举报