MySQL 之索引常见内容

索引的概念

  • 索引本质上一种数据结构
  • 索引的种类:
    • 从数据结构角度划分:
      1. B+ 树索引
      2. hash 索引
      3. FULLTEXT 索引
      4. R-Tree 索引
    • 从物理存储角度划分:
      1. 聚集索引
      2. 非聚集索引
    • 从逻辑角度划分:
      1. 主键索引
      2. 普通索引 (单列索引)
      3. 复合索引
      4. 唯一索引
      5. 空间索引
  • 索引的优势:
    • 提高数据的查询效率, 降低数据库的 IO 成本
    • 通过索引对数据进行排序, 降低数据排序成本, 降低 CPU 消耗
  • 索引的劣势:
    • 索引也是占磁盘空间的
    • 索引虽然加快了查询效率, 但是却降低了数据写操作的速度

索引创建的条件

  • 要在频繁作为条件查询的条件列上创建索引, 而不是在查询列中, 也就是说最适合出现索引的列是出现在 where 子句中的列或者连接子句中的列, 而不是出现在 select 关键字后的列
  • 尽量使用唯一索引. 索引的列的基数越大, 索引的效果越好. 例如: 这个列是用来记录性别的, 就只有 这两个值, 那么对该字段加索引的话就没有太大的用处
  • 使用短索引. 如果某一列中的值特别长, 而且要加索引的话, 应该指定一个前缀长度
  • 利用最左前缀. 这种情况出现在创建复合索引中, 例如以 a, b, c 的顺序创建了一个复合索引后, 要尽量以 a = ? 或者 a = ? and b = ? 或者 a = ? and b = ? and c = ? 这样的顺序来查询, 这样就可以利用到这个复合索引
  • 查询的时候与其他表关联的字段, 适合创建外键索引
  • 频繁更新的字段, 不适合添加索引
  • 尽量选择复合索引
  • 查询中的排序字段, 尽量添加索引, 会加快排序速度
  • 查询中统计或者分组的字段 (分组的前提是必排序)
  • 索引字段的值太长的时候, 尽量使用前缀索引

不适合创建索引的情况

  • 表记录太少
  • 经常增删改的表
  • 不是所有的表都需要创建索引. 通常来说, 常见的代码表, 配置表等, 除了主键索引外, 就没有必要再去创建其他索引了
  • 不要过度索引. 索引不是越多越好, 每个额外的索引都需要占用额外的磁盘空间, 并会降低写操作. 此外, MySQL 在生成一个执行计划时, 要考虑各个索引, 这个也要花时间
  • 如果某一列中包含了太多重复的内容, 则没必要创建索引

设计索引的一般步骤

  • 整理表中的 SQL, 重点包括 select, update, delete 操作的 where 条件所用到的列组合, 关联查询的关联条件等
  • 整理所有查询 SQL 的语句的执行频率
  • 整理所有设计的列的选择度, 列中的值的差异度越高越好
  • 给表选择合适的主键
  • 优先给执行频率最高的 SQL 创建索引
  • 按照执行频率, 确定是否需要为每个表创建索引
  • 索引合并, 利用复合索引来降低索引的总数, 充分利用最左前缀

索引的操作

  • 索引的命名规则:
    • 主键索引: pk_字段名
    • 唯一索引: uk_字段名
    • 普通索引: idx_字段名
  • 创建索引
      create [index|unique|fulltext](指的是索引类型) idx_...(索引名称) on 表名(字段名);
      // 或者是
      alter table 表名 add 索引类型 索引名称(字段名, 可以是多个字段名, 多个字段名之间用逗号隔开);
  • 查看索引
      show index from 表名;
  • 删除索引
      drop index 索引名称 on 表名;
本作品采用《CC 协议》,转载必须注明作者和本文链接
讨论数量: 1

索引你竟然分为:主键索引,唯一索引,全文索引,为什么还要分单值索引与复合索引le
要就是:普通索引,主键索引,唯一索引,全文索引
或者:聚会索引与非聚合索引

1个月前 评论
xiaosheng (楼主) 1个月前

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!