← 返回学习路线

SYSTEM

设计原则

Java基础设计原则system
src/content/notes/system/设计原则.md

设计原则

索引的设计可以遵循一些已有的原则,创建索引的时候请尽量考虑符合这些原则,便于提升索引的使用效率

创建索引时的原则:

  • 对查询频次较高,且数据量比较大的表建立索引
  • 使用唯一索引,区分度越高,使用索引的效率越高
  • 索引字段的选择,最佳候选列应当从 where 子句的条件中提取,使用覆盖索引
  • 使用短索引,索引创建之后也是使用硬盘来存储的,因此提升索引访问的 I/O 效率,也可以提升总体的访问效率。假如构成索引的字段总长度比较短,那么在给定大小的存储块内可以存储更多的索引值,相应的可以有效的提升 MySQL 访问索引的 I/O 效率
  • 索引可以有效的提升查询数据的效率,但索引数量不是多多益善,索引越多,维护索引的代价越高。对于插入、更新、删除等 DML 操作比较频繁的表来说,索引过多,会引入相当高的维护代价,降低 DML 操作的效率,增加相应操作的时间消耗;另外索引过多的话,MySQL 也会犯选择困难病,虽然最终仍然会找到一个可用的索引,但提高了选择的代价
  • MySQL 建立联合索引时会遵守最左前缀匹配原则,即最左优先,在检索数据时从联合索引的最左边开始匹配

    N 个列组合而成的组合索引,相当于创建了 N 个索引,如果查询时 where 句中使用了组成该索引的前几个字段,那么这条查询 SQL 可以利用组合索引来提升查询效率

    -- 对name、address、phone列建一个联合索引
    ALTER TABLE user ADD INDEX index_three(name,address,phone);
    -- 查询语句执行时会依照最左前缀匹配原则,检索时分别会使用索引进行数据匹配。
    (name,address,phone)
    (name,address)
    (name,phone)	-- 只有name字段走了索引
    (name)
    
    -- 索引的字段可以是任意顺序的,优化器会帮助我们调整顺序,下面的SQL语句可以命中索引
    SELECT * FROM user WHERE address = '北京' AND phone = '12345' AND name = '张三';
    -- 如果联合索引中最左边的列不包含在条件查询中,SQL语句就不会命中索引,比如:
    SELECT * FROM user WHERE address = '北京' AND phone = '12345'; 

哪些情况不要建立索引:

  • 记录太少的表
  • 经常增删改的表
  • 频繁更新的字段不适合创建索引
  • where 条件里用不到的字段不创建索引