MySQL中的索引及怎么使用

1.9. 索引

索引能够提高对数据库表的查询速度，查询优化器通过索引能够快速定位数据，不需要对表全盘扫描。类似字典中的目录，查一个字不用从第一页开始找，先看目录然后去对应的页面找就可以。

索引比原表小的多，通常存储在内存中，所以查询速度比较快。数据量比较小几百上千的没必要使用索引，数据量比较大上百万的这种，有无索引对查询效率的影响就很大了。

注意：

索引会占用内存，其次它会降低写入速度，因为每次修改数据时都会自动重建索引。所以不要对整个表建立索引，而只是针对关键的查询建立索引。

1.9.1. 创建索引（CREATE INDEX）

使用 主键 或 唯一键 创建表时，MySQL 会自动创建一个名为 PRIMARY 的索引。 该索引称为聚集索引。

PRIMARY 索引是特殊的，因为索引本身与数据一起存储在同一个表中。查询操作将会按照索引的顺序来访问数据。性能上会更好，因为数据库引擎可以更有效地使用索引来执行查询，而不必在磁盘上进行大量的随机访问。

PRIMARY 索引以外的其他索引称为二级索引或非聚集索引。

创建索引的语法：

CREATE INDEX 字段名 ON 表名(索引名);

-- 索引名习惯以idx或ix做前缀，后面的名字最好有意义，

-- 不要别取 idx_1、idx_2 这种没人知道是什么意思的名字

索引的类型：

如果不指定索引类型，默认情况下，MySQL 将创建 B-Tree 索引。下面显示了基于表的存储引擎允许的索引类型：

存储引擎

允许的索引类型

InnoDB

BTREE

MyISAM

BTREE

MEMORY/HEAP

HASH, BTREE

索引查询实例：

解释性查询名为”NICK“的演员

EXPLAIN SELECT * FROM sakila.actor WHERE first_name = 'NICK';

-- 结果

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

| 1 | SIMPLE | actor | NULL | ALL | NULL | NULL | NULL | NULL | 201 | 10.00 | Using where |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

可以看到未使用索引的情况下，进行了全表扫描。

给名字字段创建索引后再进行查询

-- 创建索引

CREATE INDEX first_name ON actor(first_name);

-- 解释性查询

EXPLAIN SELECT * FROM sakila.actor WHERE first_name = 'NICK';

-- 输出结果

+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+

| 1 | SIMPLE | actor | NULL | ref | first_name | first_name | 182 | const | 3 | 100.00 | NULL |

+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+

使用索引后只查询了3行。

1.9.2. 删除索引（DROP INDEX）

创建了错误索引、在插入或更新大量数据之前（为了提高速度）可以删除索引。

DROP INDEX index_name ON table_name

主键的索引名字固定为 PRIMARY。要删除表 t 上的主键索引，请使用下面的语句：

DROP INDEX `PRIMARY` ON t;

1.9.3. 显示索引（SHOW INDEXES）

可以使用 SHOW INDEXES 语句获取一个表的索引以了解该表中的索引情况。 还可以使用 WHERE 子句对 SHOW INDEXES 的结果进行过滤。

-- 数据库.表名

SHOW INDEXES FROM sakila.film WHERE Non_unique = 0;

-- 结果

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

| film | 0 | PRIMARY | 1 | film_id | A | 1000 | NULL | NULL | | BTREE | | | YES | NULL |

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

1.9.4. 唯一索引（UNIQUE INDEX）

MySQL 唯一索引是一种特殊的索引，它不但可以加快从表中检索数据的速度，还能防止在指定的一个或多个列中出现重复值。

表中除了主键之外其他的字段可能也需要具有唯一值，如手机号，邮箱等，可以将这些字段定义为唯一索引。

创建唯一索引：

CREATE UNIQUE INDEX idx_tenant_email ON tenant(email);

-- 查看唯一索引

SHOW INDEXES FROM tenant;

+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |

+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

| tenant | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |

| tenant | 0 | idx_tenant_email | 1 | email | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |

+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

1.9.5. 索引提示

USE INDEX：

MySQL 查询优化器是 MySQL 数据库服务器的一个组件，为 SQL 语句制定最佳执行计划，通常根据索引基数进行决策。 有时候，创建了索引，但是 SQL 语句却不一定使用索引。 这是因为 MySQL 查询优化器的做出了它认为的更优的选择。

USE INDEX 告诉 MySQL 用列表中的其中一个索引去做本次查询，但是 MySQL 不一定会用。FORCE INDEX 强制 MySQL 使用一个特定的索引。USE INDEX语法：

-- 创建两个索引

CREATE INDEX idx_last_name

ON customer (last_name);

CREATE INDEX idx_last_name_first_name

ON customer (last_name, first_name);

-- 提示使用idx_last_name_first_name索引

SELECT *

FROM customer

USE INDEX (idx_last_name_first_name)

WHERE last_name = 'BARBEE';

USE INDEX 子句放在 FROM 子句之后。查询优化器不一定会使用建议的命名索引。

FORCE INDEX语法：

FORCE INDEX 子句要求 MySQL 查询优化器必须使用指定的索引。

SELECT *

FROM film

FORCE INDEX (idx_fk_language_id)

WHERE language_id = 1;

1.9.6. 复合索引

复合索引也称为组合索引，就是在多个字段上建立的索引，复合索引允许最多使用16个字段。

复合索引语法：

CREATE INDEX index_name

ON table_name(column_1, column_2, column_3);

复合索引规则：

定义多字段索引时，应将 WHERE 子句中常用的字段放在索引列表的开头，将不常用的列放在索引列列表的后面。否则， MySQL 优化器可能不会使用索引。

-- 上述语法中，MySQL 优化器在以下情况下会考虑使用索引：

WHERE column_1 = c1 and column_2 = c2 and column_3 = c3;

-- 或者

WHERE column_1 = c1 and column_2 = c2;

-- 或者

WHERE column_1 = c1;

-- 但是，在以下情况下不会考虑使用索引：

WHERE column_3 = c3;

-- 或者

WHERE column_2 = c2 and column_3 = c3;

注意：如果条件列不构成索引的最左前缀，则查询优化器无法使用索引执行查找。

1.9.7. 聚集索引

聚集索引是一种特殊的索引，索引中键值的顺序决定了表中相应行的物理顺序。聚集索引就像词典中的目录，目录和词语都是按照字母顺序排序的。

由于表中的数据只能按照一种顺序进行存储，因此一个表中最多只能有一个聚集索引。

InnoDB 表上的 MySQL 聚集索引：

聚集索引有助于一个 InnoDB 表优化数据操作，每个InnoDB 表都需要一个聚集索引。 如果定义了主键，MySQL 会使用主键作为聚集索引，如果没有主键，会使用第一个所有值都是NOT NULL的UNIQUE索引作为聚集索引。

如果以上说的情况都不存在，会在内部生成一个隐藏的聚集索引，名称为 GEN_CLUST_INDEX，该索引建立在一个包含 ROW ID 值的列上。

1.9.8. 索引基数

一个索引的基数是指这个索引的列中的唯一值的数量。它是根据统计信息生成的一个估计值，不一定是准确的，索引基数是查询优化器决定是否使用索引的一个重要依据。索引基数越高，使用索引越有效。索引的基数很低，全表扫描可能比使用索引更有效。使用SHOW INDEXES命令可以查看索引基数，Cardinality 列中值就是索引基数。

1.9.9. 隐藏索引

MySQL 8 引入了隐藏索引（invisible index），隐藏索引是实际存在的，但是对MySQL查询优化器是不可见的。删除索引前可以先将其隐藏，如果没有影响再去真正删除索引。

创建隐藏索引：

CREATE INDEX index_name

ON table_name(c1, c2, ...) INVISIBLE;

-- 修改索引的可见性

ALTER TABLE table_name

ALTER INDEX index_name [VISIBLE | INVISIBLE];

使用 VISIBLE 和 INVISIBLE 标识索引是否可见。不能在主键字段上的索引设置为隐藏，会报错。

隐藏索引开关：

查询优化器默认不使用隐藏索引，但是可以通过系统变量 optimizer_switch 中的 use_invisible_indexes 修改。

-- 查看当前的设置

SELECT @@optimizer_switch;

-- 修改默认行为

SET SESSION optimizer_switch="use_invisible_indexes=on";

1.9.10. 字符串前缀索引

可以为字符串列的指定长度的前缀创建前缀索引。相比为整个字符串列创建索引，前缀索引能减少磁盘的使用量，提高索引的写入速度。

前缀索引语法：

CREATE INDEX index_name

ON table_name (column_name(length));

如果列为 CHAR， VARCHAR 和 TEXT 等非二进制字符类型， length 指定用于前缀索引的字符数。如果列为 BINARY， VARBINARY 和 BLOB 等二进制字符类型， length 指定用于前缀索引的字节数。例如，一个字段下存放了大量的字符串数据，前几个字符几乎相同，经常使用以固定字符开头进行模糊查询的，就可以考虑创建前缀索引。

1.9.11. 索引顺序

在创建索引的时候指定索引的顺序。默认情况下，索引按照升序存储。

CREATE TABLE test (

v INT,

-- 默认升序

INDEX idx_v_asc (v),

-- 指定降序排序

INDEX idx_v_desc (v DESC)

);

直到 MySQL 8.0， MySQL 才真正支持降序索引。虽然之前的 MySQL 版本也支持 DESC 关键字，但是 MySQL 会忽略它。 这意味着， 在MySQL 8 之前，降序扫描索引的成本很高。