MySQL常用索引

2022/5/12 19:27:57

本文主要是介绍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


这篇关于MySQL常用索引的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!


扫一扫关注最新编程教程