MySQL - 索引 - 拾石录

MySQL 索引

索引概述
设计索引的原则
BTREE 索引与 HASH 索引
MySQL 如何使用索引
创建和管理索引

索引概述

所有 MySQL 列类型可以被索引。对相关列使用索引是提高 SELECT 操作性能的最佳途径。根据存储引擎定义每个表的最大索引数和最大索引长度，所有存储引擎支持每个表至少 16 个索引，总索引长度至少为 256 字节。

在 MySQL 中，对于 MyISAM 和 InnoDB 表，前缀可以达到 1000 字节长。注意前缀的限制以字节为单位测量，而 CREATE TABLE 语句中的前缀长度解释为字符数，使用多字节字符集时要加以考虑。

FULLTEXT 索引可以用于全文搜索，只有 MyISAM 存储引擎支持，且只为 CHAR、VARCHAR 和 TEXT 列
空间列类型只有 MyISAM 支持，使用 R-树索引
MEMORY 存储引擎默认使用 hash 索引，但也支持 B-树索引

设计索引的原则

索引出现在 WHERE 或 JOIN 子句中的列：最适合索引的列是出现在 WHERE 子句中的列，或连接子句中指定的列，而不是出现在 SELECT 关键字后的选择列表中的列。
使用唯一索引：对于唯一值的列，索引的效果最好，而具有多个重复值的列，索引效果最差。例如存放年龄的列具有不同值，很容易区分各行；而用来记录性别的列只含有 M 和 F，对此列索引没有多大用处。
使用短索引：如果对串列进行索引，应该指定一个前缀长度。例如有一个 CHAR(200) 列，如果在前 10 个或 20 个字符内多数值是唯一的，就不要对整个列进行索引。对前缀索引能节省大量索引空间，也可能会使查询更快。
利用最左前缀：在创建一个 n 列的索引时，实际是创建了 MySQL 可利用的 n 个索引。多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这与索引一个列的前缀不同（索引一个列的前缀是利用该列的前 n 个字符作为索引值）。
不要过度索引：不要以为索引越多越好。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。在修改表的内容时，索引必须进行更新，索引越多所花时间越长。MySQL 在生成执行计划时要考虑各个索引，创建多余的索引给查询优化带来更多工作，甚至可能使 MySQL 选择不到最好的索引。只保持所需的索引有利于查询优化。
考虑列上的比较类型：索引可用于 <、<=、=、>=、> 和 BETWEEN 运算。在模式具有一个直接量前缀时，索引也用于 LIKE 运算。如果只将某个列用于其他类型的运算（如 STRCMP()），对其进行索引没有价值。

BTREE 索引与 HASH 索引

HASH 索引的特征：

只用于使用 = 或 <=> 操作符的等式比较（但很快）
优化器不能使用 hash 索引来加速 ORDER BY 操作
MySQL 不能确定在两个值之间大约有多少行
只能使用整个关键字来搜索一行（用 B-树索引，任何关键字最左面的前缀可用来找到行）

BTREE 索引可用于 >、<、>=、<=、BETWEEN、!=、<>，以及 LIKE 'pattern'（pattern 不以通配符开始）。

-- 适用于 BTREE 索引和 HASH 索引
SELECT * FROM t1 WHERE key_col = 1 OR key_col IN (15,18,20);

-- 只适用于 BTREE 索引
SELECT * FROM t1 WHERE key_col > 1 AND key_col < 10;
SELECT * FROM t1 WHERE key_col LIKE 'ab%' OR key_col BETWEEN 'bar' AND 'foo';

MySQL 如何使用索引

索引用于快速找出在某个列中有一特定值的行。不使用索引，MySQL 必须从第 1 条记录开始读完整个表直到找出相关的行。表越大，花费的时间越多。如果表中查询的列有一个索引，MySQL 能快速到达一个位置去搜寻数据。如果一个表有 1000 行，使用索引比顺序读取至少快 100 倍。

注意如果需要访问大部分行，顺序读取要快得多，因为此时避免了磁盘搜索。

大多数 MySQL 索引（PRIMARY KEY、UNIQUE、INDEX 和 FULLTEXT）在 B 树中存储。空间列类型的索引使用 R-树，MEMORY 表支持 hash 索引。

MySQL 会使用索引的场景：

匹配全值：对索引中所有列指定具体值
匹配值范围：对索引列指定范围
匹配最左前缀：只使用索引的最左列
仅对索引列进行查询（覆盖索引）
匹配列前缀：对索引列的开始部分进行匹配

MySQL 不会使用索引的场景：

以 % 开头的 LIKE 查询
数据类型出现隐式转换
复合索引但条件中没有最左列
使用 OR 连接的条件中有未索引的列
MySQL 评估使用全表扫描比使用索引更快

创建和管理索引

创建表时创建索引：

CREATE TABLE mytable (
    id INT NOT NULL,
    name CHAR(50) NOT NULL,
    email CHAR(100) NOT NULL,
    PRIMARY KEY (id),
    INDEX idx_name (name),
    UNIQUE INDEX idx_email (email)
) ENGINE=InnoDB;

创建复合索引：

CREATE INDEX idx_name_age ON mytable (name, age);
-- 利用了最左前缀：可被用于 name、name+age 的查询，但不能用于 age 的查询

使用前缀索引：

CREATE INDEX idx_city ON customers (city(10));

为已有表添加索引：

ALTER TABLE mytable ADD INDEX idx_name (name);
CREATE INDEX idx_name ON mytable (name);

查看索引：

SHOW INDEX FROM mytable;
SHOW INDEX FROM mytable FROM mydb;

查看索引使用情况：

-- 查看索引使用统计
SHOW STATUS LIKE 'Handler_read%';
-- Handler_read_rnd_next 值高意味着查询低效，可能需要更多索引

删除索引：

ALTER TABLE mytable DROP INDEX idx_name;
DROP INDEX idx_name ON mytable;

使用 EXPLAIN 验证索引使用：

EXPLAIN SELECT * FROM products WHERE vend_id = 1003;
-- 查看 type 列：ref 表示使用了索引查找，ALL 表示全表扫描
-- 查看 key 列：显示实际使用的索引，NULL 表示没用索引

定期分析表和优化表：

-- 分析表，更新索引统计信息
ANALYZE TABLE mytable;

-- 优化表，回收空间碎片
OPTIMIZE TABLE mytable;

MySQL - 索引

#MySQL 索引

#索引概述

#设计索引的原则

#BTREE 索引与 HASH 索引

#MySQL 如何使用索引

#创建和管理索引