索引

简介

索引是数据库中用于提高查询性能的数据结构。它类似于书籍的目录，可以帮助数据库引擎快速地定位到所需的数据，而不必进行全表扫描。通过在数据库表的列上创建索引，可以加快查询速度，并降低查询的资源消耗。

类型

B-Tree 索引：最常见的索引类型，适用于各种查询条件，支持全文检索。
哈希索引：适用于精确查找，只支持等值查询，不支持范围查询。
全文索引：用于全文搜索，可以在文本字段上执行全文搜索。

主要概念

覆盖索引：

当查询的列都包含在索引中时，MySQL 可以直接从索引中获取数据，而不必访问表本身，这称为覆盖索引。它可以大大提高查询性能。

特点

排序和范围查询：索引可以支持排序操作，使得排序查询更为高效。此外，索引还可以加速范围查询，例如使用 BETWEEN、IN、LIKE 等条件。

创建命令

语法：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    ON table_name (column_name [(length)] [ASC|DESC], ...)
    [USING index_type]

选项简介：

CREATE INDEX：这是指示 MySQL 创建索引的关键字。
UNIQUE、FULLTEXT、SPATIAL：这些是可选的关键字，用于指定索引的类型。如果不指定，默认为 B-Tree 索引。具体含义如下：
- UNIQUE：创建唯一索引，索引列的值必须唯一，不允许重复值。
- FULLTEXT：创建全文索引，用于全文搜索。
- SPATIAL：用于空间数据类型的索引。
index_name：索引的名称，用于在表中标识索引。
table_name：要在其上创建索引的表的名称。
column_name：要包含在索引中的列的名称。可以指定多个列，用逗号分隔。
(length)：对于索引的前缀长度。对于文本和二进制列，可以指定前缀长度以限制索引的大小。如果不指定，则索引将包括整个列。
[ASC|DESC]：可选的排序顺序，默认为 ASC（升序）。你可以指定每个列的排序顺序。
USING index_type：可选的关键字，用于指定索引的类型。常见的类型包括：
- BTREE：B-Tree 索引，这是默认类型。
- HASH：哈希索引，适用于等值查找。
- FULLTEXT：全文索引，用于全文搜索。

示例：

CREATE INDEX idx_name ON users (last_name, first_name);

在名为 users 的表上创建一个名为 idx_name 的索引，该索引涵盖 last_name 和 first_name 列。

使用索引的注意点

索引的优化

定期检查和优化索引，删除不再使用或不必要的索引。
尽量避免创建过多的索引，因为它会增加写操作的开销。
避免在大型文本字段上创建索引，因为这会增加索引的大小并降低性能。
对于频繁的查询，可以考虑使用内存表和临时表。
对于频繁更新的表，索引可能会带来额外的开销，因为每次更新都需要更新索引。
索引可以占用大量的磁盘空间，尤其是对于大型表格和复合索引。
使用 EXPLAIN 关键字可以分析查询的执行计划，确定是否使用了索引以及哪些索引被使用。
通过分析执行计划，可以找出查询的瓶颈，并相应地进行优化。

EXPLAIN用法

EXPLAIN 语句用于分析查询的执行计划，帮助理解查询是如何执行的以及哪些索引被使用。通过分析执行计划，可以确定查询的瓶颈，并进行相应的优化。

EXPLAIN SELECT column1, column2 FROM table_name WHERE condition;

使用 EXPLAIN 后，MySQL 将返回一组数据，该数据描述了查询的执行计划。

其中几个重要字段简介：

id：查询中每个操作的唯一标识符。如果查询是复合查询（例如联接），则每个操作都有一个唯一的标识符。
select_type：查询的类型，可能包括 SIMPLE（简单查询）、PRIMARY（主查询）和 SUBQUERY（子查询）等。
table：查询涉及的表的名称。
type：查询使用的连接类型。常见的类型包括 ALL（全表扫描）、index（通过索引扫描表）和 range（使用索引范围扫描）等。
possible_keys：可能用于该查询的索引列表。
key：实际用于该查询的索引。
key_len：索引中使用的字节数。
rows：MySQL 估计查询将扫描的行数。
Extra：附加信息，可能包括 Using where（使用了 WHERE 条件）、Using index（使用了覆盖索引）等。

示例：

创建一张表：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT
);

INSERT INTO users (first_name, last_name, age) VALUES
('John', 'Smith', 30),
('Jane', 'Smith', 25),
('Michael', 'Johnson', 35),
('Emily', 'Jones', 28);

使用EXPLAIN分析查询语句：

EXPLAIN SELECT first_name, age FROM users WHERE last_name = 'Smith'\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
   partitions: NULL
         type: ref
possible_keys: last_name
          key: last_name
      key_len: 53
          ref: const
         rows: 2
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

id：1 表示这是查询中的第一个操作。
select_type：SIMPLE 表示这是一个简单的 SELECT 查询。
table：users 表示该查询涉及到 users 表。
type：ref 表示查询将使用索引进行查找。
possible_keys：last_name 表示可能用于查询的索引。
key：last_name 表示实际用于查询的索引。
key_len：53 表示索引使用的字节数。
ref：const 表示此列使用了常量值。
rows：2 表示 MySQL 估计查询将扫描的行数。
filtered：100.00 表示过滤条件过滤了所有的行。
Extra：Using index 表示查询使用了覆盖索引。

上面的查询使用了名为 last_name 的索引，它是一个覆盖索引，并且只扫描了 2 行数据，过滤了所有行。这表明查询执行效率很高，因为它利用了索引直接定位到符合条件的行，而不必扫描整个表。

简介​

类型​

主要概念​

特点​

创建命令​

使用索引的注意点​

索引的优化​

EXPLAIN用法​

简介

类型

主要概念

特点

创建命令

使用索引的注意点

索引的优化

EXPLAIN用法