Skip to content

14. MySQL索引的设计和使用

1. 使用⚓

创建索引

CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_option] ...

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH}

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'

使用EXPLAIN查看索引是否被使用：

MariaDB [MYISAM_TEST]> EXPLAIN SELECT * FROM CHILD WHERE PID=4 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: CHILD
         type: ref
possible_keys: PARENT_ID
/* 索引被使用 */
          key: PARENT_ID
      key_len: 3
          ref: const
         rows: 1
        Extra: Using index
1 row in set (0.00 sec)

删除索引：

DROP [ONLINE|OFFLINE] INDEX index_name ON tbl_name

2. 设计原则⚓

  • 最适合索引的列是出现在 WHERE 子句中的列，或连接子句中指定的列。
  • 使用惟一索引。考虑某列中值的分布。索引的列的基数越大，索引的效果越好。
  • 使用短索引。如果对字符串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。
  • 利用最左前缀。 在创建一个 n 列的索引时， 实际是创建了 MySQL 可利用的 n 个索引。多列索引可起几个索引的作用， 因为可利用索引中最左边的列集来匹配行。 这样的列集称为最左前缀。
  • 不要过度索引。每个额外的索引都要占用额外的磁盘空间， 并降低写操作的性能。 在修改表的内容时， 索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。
  • 对于 InnoDB 存储引擎的表，尽量自己指定主键。InnoDB 表的普通索引都会保存主键的键值，所以主键要尽可能选择较短的数据类型， 可以有效地减少索引的磁盘占用， 提高索引的缓存效果。

3. BTREE 索引与 HASH 索引⚓

MEMORY 存储引擎的表可以选择使用 BTREE 索引或者 HASH 索引， 两种不同类型的索引各有其不同的适用范围。

HASH 索引有一些重要的特征需要在使用的时候特别注意：

  • 只用于使用=或<=>（NULL的安全比较）操作符的等式比较（但是很快）。
  • 优化器不能使用 HASH 索引来加速 ORDER BY 操作。
  • MySQL 不能确定在两个值之间大约有多少行。 如果将一个 MyISAM 表改为 HASH 索 引的 MEMORY 表，会影响一些查询的执行效率。
  • 只能使用整个关键字来搜索一行。

而对于 BTREE 索引， 当使用>、 <、 >=、 <=、 BETWEEN、!=或者<>， 或者 LIKE 'pattern' （其 中'pattern'不以通配符开始）操作符时，都可以使用相关列上的索引。

IN范围查询适用于 BTREE 索引和 HASH 索引：

SELECT * FROM t1 WHERE key_col IN (15,18,20);

InnoDB 和 MyISAM 不支持HASH索引类型，如果指定了使用HASH类型的索引，会被替换为BTREE。

注意：如果需要访问大部分行，顺序读取要快得多，因为此时应避免磁盘搜索。