MySQL常用索引

索引用于提高MySQL的检索速度。在MySQL中，常见的索引有：常规索引(INDEX)、唯一索引(UNIQUE INDEX)、主键索引(PRIMARY KEY)、空间索引（SPATIAL）、全文索引(FULLTEXT)。MySQL的大部分索引(PRIMARY KEY、UNIQUE、INDEX、FULLTEXT) 存储在 B-trees中，但以下索引是例外: 空间索引使用 R-trees; 内存表格支持哈希索引; InnoDB 使用反向列表（inverted lists）存储全文索引。

创建索引的语法如下

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

key_part: {col_name [(length)] | (expr)} [ASC | DESC]

index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}
  | ENGINE_ATTRIBUTE [=] 'string'
  | SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
}

index_type:
    USING {BTREE | HASH}

algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}

lock_option:
    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

1. 使用列前缀做索引

CREATE INDEX part_of_name ON customer (name(10));

• 对于字符串类型的列，可以使用col_name(length)语法，以字节来计算长度，将该列的前length部分作为索引
• CHAR, VARCHAR, BINARY, VARBINARY 类型的列可以使用该类型索引
• BLOB 和 TEXT 类型的列只能使用该类型的索引

2. 使用列函数做索引

MySQL 8.0.13及以上版本支持使用列函数作为索引，例如：

CREATE TABLE t1 (col1 INT, col2 INT, INDEX func_index ((ABS(col1))));
CREATE INDEX idx1 ON t1 ((col1 + col2));
CREATE INDEX idx2 ON t1 ((col1 + col2), (col1 - col2), col1);
ALTER TABLE t1 ADD INDEX ((col1 * 40) DESC);

使用该索引需要遵守以下规则：

• 表达式必须使用()括起来
• 不能使用单独的列
• 不能使用列的前缀表达式，可以使用 SUBSTRING() 和 CAST()
• 不能声明为外键

索引用法

CREATE TABLE employees (
  data JSON,
  INDEX idx ((CAST(data->>"$.name" AS CHAR(30))))
);
INSERT INTO employees VALUES
  ('{ "name": "james", "salary": 9000 }'),
  ('{ "name": "James", "salary": 10000 }'),
  ('{ "name": "Mary", "salary": 12000 }'),
  ('{ "name": "Peter", "salary": 8000 }');
# 注意where语句中也要使用CAST，否则索引编码与 data->>'$.name'的编码不一致，会导致索引不起作用
SELECT * FROM employees WHERE CAST(data->>'$.name' AS CHAR(30)) = 'James';

3. 唯一索引

唯一索引要求索引中的所有值必须是唯一的不能有重复。如果使用列的前缀作为唯一索引，必须保证该列的前缀是唯一的。
唯一索引允许该列中有多个NULL值

4. 全文索引

全文索引只支持InnoDB和MyISAM引擎，只能用于 CHAR, VARCHAR, TEXT 类型的列。

5. 多值索引

多值索引已数组的形式存储在一个列上。常规索引是一个索引对应一条记录，多值索引是多个索引记录对应一条记录。多值索引主要用于JSON的数组索引，例如：

数据：
{
    "user":"Bob",
    "user_id":31,
    "zipcode":[94477,94536]
}

# 创建表格及索引
CREATE TABLE customers (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    custinfo JSON,
    INDEX zips( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) )
    );
# 单独创建索引
ALTER TABLE customers ADD INDEX zips( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );

CREATE INDEX zips ON customers ( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );

多值索引也可以作为组合索引的一部分，例如：

ALTER TABLE customers ADD INDEX comp(id, modified,
    (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );

使用多值索引
在where子句中可以通过MEMBER OF()、JSON_CONTAINS()、JSON_OVERLAPS()来使用多值索引。例如：

SELECT * FROM customers WHERE 94507 MEMBER OF(custinfo->'$.zipcode');

SELECT * FROM customers WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));

SELECT * FROM customers WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));

多值索引的使用约束详见：https://dev.mysql.com/doc/refman/8.0/en/create-index.html

6. 空间索引

MyISAM, InnoDB, NDB, ARCHIVE引擎支持POINT 、GEOMETRY类型的表示空间的列。
在SPATIAL列上创建空间索引有以下约束：

• 只适用于MyISAM和InnoDB引擎
• 在MySQL 8.0.12中，在spatial类型的列上建索引只能使用关键词SPATIAL建索引
• 只能在单列上建索引
• 索引列必须是NOT NULL
• 禁止使用列前缀做索引
• 不能用于主键或唯一索引
  在SPATIAL列上创建非空间索引有以下约束：
• 除ARCHIVE引擎外都可以建立索引
• 如果该列不是主键，列值可以为NULL
• 索引类型由存储引擎决定，目前使用的是B-tree
• 只有InnoDB, MyISAM, MEMORY引擎上列值允许为NULL