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 之前，降序扫描索引的成本很高。