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 类型的列只能使用该类型的索引
  1. 使用列函数做索引

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';
  1. 唯一索引

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

  1. 全文索引

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

  1. 多值索引

多值索引已数组的形式存储在一个列上。常规索引是一个索引对应一条记录，多值索引是多个索引记录对应一条记录。多值索引主要用于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

  1. 空间索引

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