Sql

优点

  • 大大提高数据查询速度。
  • 可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录。
  • 通过索引列对数据进行排序，降低数据的排序成本降低了CPU的消耗。
  • 被索引的列会自动进行排序，包括【单例索引】和【组合索引】，只是组合索引的排序需要复杂一些。
  • 如果按照索引列的顺序进行排序，对order 不用语句来说，效率就会提高很多。

缺点

  • 索引会占据磁盘空间。
  • 索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改查操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件。
  • 维护索引需要消耗数据库资源。

索引类型

  • 主键索引
    • 设定为主键后，数据库自动建立索引，InnoDB为聚簇索引，主键索引列值不能为空（Null）。
  • 唯一索引
    • 索引列的值必须唯一，但允许有空值（Null），但只允许有一个空值（Null）。
  • 复合索引
    • 一个索引可以包含多个列，多个列共同构成一个复合索引。
  • 全文索引
    • Full Text（MySQL5.7之前，只有MYISAM存储引擎引擎支持全文索引）。
    • 全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找允许在这些索引列中插入重复值和空值。全文索引可以在Char、VarChar 上创建。

需要创建索引的情况

  1. 主键自动创建索引
  2. 频繁作为查询条件的字段应该创建索引
  3. 查询中与其他表关联的字段，外键关系建立索引
  4. 查询中排序字段，排序字段需要建立索引（大大提高排序的速度）
  5. 频繁更新字段不适合创建索引
  6. where条件里用不到的字段不创建索引
  7. 创建单键/组合索引的选择问题。高并发下倾向于创建组合索引
  8. 统计或分组字段需要创建索引

不需要创建索引的情况

  1. 表记录太少不需要创建索引
  2. 经常dml（增、删、改）操作的表不需要创建索引
  3. 数据重复且分布平均的表字段没有必要创建索引

索引选择性

表字段记录数/表该字段不重复的记录数 越接近于1索引效果越好

增加索引

1
ALTER TABLE $TABLE_NAME ADD INDEX UNIQUE $index_name($col_name) USING BTREE;

索引失效情况

  1. join中使用or
    1. explain显示using temporary;using filesort
  2. order by 多个索引字段使用不同的排序规则（desc和asc）
    1. explain显示using filesort
  3. 使用函数
    1. explain显示using filesort
  4. WHERE 和ORDER BY后面是相同的列就使用单索引列；如果不同就使用联合索引。

Leave a comment

waline