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 协议》，转载必须注明作者和本文链接