索引是用来加快从数据库中查询数据的速度的。

需要注意的是索引的使用会增加插入和更新的时间，因为在插入数据的同时也会更新索引。所以在创建索引时确保只在那些频繁作为查询条件的列中增加。

创建索引

创建索引时有几个需要注意的点：

  • 不要在频繁写，而读取频率较低的表上使用索引，和之前说的那样，索引提高了读速度，而损耗了写速度
  • 不要在 low cardinality 的列上使用索引，Cardinality 直接翻译是基数，可以理解成为这一列取值的散列程度，如果一个列包含的值只有少数几个，那么索引的效果也无法达到
  • 不要在固定大小的表上使用索引，小数量集的表增加索引并不会带来多大的性能提升，所以尤其需要注意的是那些可能随着时间数据量增长很快的表，比如 users 表

在建表时

CREATE INDEX idx_name ON table_name(column1, column2);

ALTER TABLE `table_name` ADD INDEX idx_name (`column1`);

创建唯一索引

ALTER TABLE `table_name` ADD UNIQUE uni_name (`column1`)

显示查看索引

查看表索引

SHOW INDEX FROM table_name;

在查询的结果中可以看到索引的名字，列名，散列程度（Cardinality），索引类型（BTREE） 等等。

查询 Schema 中所有的索引

SELECT DISTINCT
    TABLE_NAME,
    INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_schema';

删除索引

DROP INDEX idx_name ON table_name;

ALTER TABLE table_name DROP INDEX idx_name;

Single index vs Composite index

组合索引和单一索引一样，不过组合索引是需要组合多列。

假设有用户表 users

ID | first_name | last_name    | class      | position |
--------------------------------------------------------
 1 | Teemo      | Shroomer     | Specialist | Top      |
 2 | Cecil      | Heimerdinger | Specialist | Mid      |
 3 | Annie      | Hastur       | Mage       | Mid      |
 4 | Fiora      | Laurent      | Slayer     | Top      |
 5 | Garen      | Crownguard   | Fighter    | Top      |

然后在 class 和 position 列上创建组合索引

CREATE INDEX class_pos_index ON users (class, position);

然后数据库会创建一个组合索引的排序，类似：

class-position       Primary Key
--------------------------------
AssassinMid       -> 10
ControllerSupport -> 16
ControllerSupport -> 18
ControllerSupport -> 8
FigherTop         -> 7
FigherTop         -> 9
FighterJungle     -> 13
FighterJungle     -> 21
FighterJungle     -> 23

假设需要查询班级中的 Top，那么会提升速度：

SELECT * FROM users
WHERE
  class = 'Specialist'
AND
  position = 'Top';

因为按照了 class-position 来排序，所以查询速度得到了提升。数据库能够在 O(log_2(n)) 时间内查找到 Specialist-Top 而不需要读取全表。

需要注意的是即使查询条件只有 class 字段，组合索引依然能够提升速度，因为class 在组合索引的第一个位置。

但是单纯的查询 position

SELECT * FROM users WHERE position = 'Top';

则享受不到组合索引带来的好处。所以组合索引的列顺序非常关键。

创建组合索引的一些注意点：

  • 如果特定列固定的出现在查询条件中，那么对这些列创建组合索引比较好
  • 如果要创建 field1 上的索引，也要创建 (field1, field2) 上的索引，那么只创建一个组合索引 (field1, field2) 已经足够
  • 和 Single indexes 一样，组合索引的 Cardinality 一样重要。显然当两个 field 有高的 Cardinality，组合索引的 Cardinality 也会很高。但是某一些情况下低 Cardinality 的列也会有高的 Cardinality 组合索引

reference