Mysql重点思考(中)--mysql前缀和覆盖优化

1.创建索引

1.1 创建一个B+tree索引

• 给name创建一个B+tree索引

CREATE INDEX idx_name ON table_name (name);

• 给name，age创建一个联合索引

CREATE INDEX idx_name_age ON table_name (name, age);

1.2 创建一个hash索引

CREATE INDEX idx_hash ON table_name (column_name) USING HASH;

table_name 是表的名称，column_name 是要在其上创建哈希索引的列名称。执行此 SQL 语句后，数据库管理系统将在 column_name 列上创建一个哈希索引，适用于等值筛选的场景，以提高等值查询的性能。

1.2.1 等值筛选

等值筛选是数据库查询操作的一种，指的是通过某个列的精确值来筛选出符合条件的行。例如，查询员工表中工资等于某个特定值的员工记录，或者查询客户表中客户名等于某个特定值的客户记录，这些都属于等值筛选操作。

1.2.2 hash索引缺点

• 不支持范围查询和排序
• 不适用于模糊查询
• 不支持索引覆盖
• 内存消耗较大
• 哈希冲突

1.3 创建一个全文索引

CREATE FULLTEXT INDEX idx_content ON articles (content);

1.3.1 全文索引的适用场景

1.3.2 全文索引的缺点

• 占用更多的存储空间
• 索引更新开销较高
• 无法处理所有文本类型
• 不支持所有的搜索操作
• 可能存在词汇歧义问题

2. 查找索引

2.1 describe语句

DESCRIBE T;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| ID    | int     | NO   | PRI | NULL    |       |
| k     | int     | NO   | MUL | 0       |       |
| s     | varchar | NO   |     | ''      |       |
+-------+---------+------+-----+---------+-------+

在上面的示例中，k 列有一个索引，并且在 Key 列中显示为 MUL。这表示 k 列上有一个普通索引。

2.2 show index语句

SHOW INDEX FROM T;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| T     |          0 | PRIMARY  |            1 | ID          | A         |           6 |     NULL |   NULL |      | BTREE      |         |               |
| T     |          1 | k        |            1 | k           | A         |           6 |     NULL |   NULL |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

在上面的示例中，显示了表 T 的两个索引：PRIMARY 主键索引和 k 列上的普通索引。

3. 删除索引

DROP INDEX index_name ON table_name;

4. 注意事项

4.1 针对创建索引

4.1.1 DISTINCT 字段需要创建索引

有时候我们需要对某个字段进行去重，使用 DISTINCT，那么对这个字段创建索引，也会提升查询效率。这是因为索引会对数据按照某种顺序进行排序，所以在去重的时候也会快很多。

4.1.2 字段的数值有唯一性的限制

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源：Alibaba）

4.1.3 频繁作为 WHERE 查询条件的字段

某个字段在SELECT语句的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了。尤其是在数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。
比如学生表数据表（含100万条数据），我们肯定会经常用到学生的学号和姓名等。

4.1.4 经常 GROUP BY 和 ORDER BY 的列

索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者使用ORDER BY对数据进行排序的时候，就需要对分组或者排序的字段进行索引 。如果待排序的列有多个，那么可以在这些列上建立组合索引 。

4.2 针对不创建索引

4.2.1 有大量重复数据的列上不要建立索引

举例1：要在 100 万行数据中查找其中的 50 万行（比如性别为男的数据），一旦创建了索引，你需要先访问 50 万次索引，然后再访问 50 万次数据表，这样加起来的开销比不使用索引可能还要大。

4.2.2 不建议用无序的值作为索引

例如身份证、UUID(在索引比较时需要转为ASCII，并且插入时可能造成页分裂)、MD5、HASH、无序长字符串等。

5. 索引优化

5.1 前缀索引优化

• 假设，你现在维护一个支持邮箱登录的系统，用户表是这么定义的

create table SUser(
ID bigint unsigned primary key,
email varchar(64),
name varchar(64),
...
)engine=innodb;

• 使用邮箱登录，所以业务代码中一定会出现类似于这样的语句

select f1, f2 from SUser where email='xxx';

• 如果email这个字段上没有索引，那么这个语句就只能做全表扫描。

-- 全部索引，索引字段为email整个字符串
alter table SUser add index index1(email);

• 你可以定义字符串的一部分作为索引

-- 前缀索引，6表示索引字段为email的前6位
alter table SUser add index index2(email(6));

5.2 覆盖索引优化

mysql> create table T (
    -> ID int primary key,
    -> k int NOT NULL DEFAULT 0,
    -> s varchar(16) NOT NULL DEFAULT '',
    -> index k(k))
    -> engine=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from T;
+-----+---+----+
| ID  | k | s  |
+-----+---+----+
| 100 | 1 | aa |
| 200 | 2 | bb |
| 300 | 3 | cc |
| 500 | 5 | ee |
| 600 | 6 | ff |
| 700 | 7 | gg |
+-----+---+----+
6 rows in set (0.00 sec)

• 如果我执行 select *fromTwhere k between 3 and 5，需要执行几次树的搜索操作，会扫描多少行？
  

• 要避免回表过程，可以考虑在辅助索引 k 上创建覆盖索引，即包含查询结果所需的所有列。这样，在执行查询时就可以直接从覆盖索引中获取数据，而不必再次访问主键索引。

CREATE INDEX idx_k_covering ON T(k, s);
SELECT * FROM T WHERE k BETWEEN 3 AND 5;

使用情况	使用覆盖索引前	使用覆盖索引后
回表次数	需要回表，执行额外的查找操作	不需要回表，直接从索引中获取所需数据
IO 操作	需要额外的IO操作，访问主键索引获取完整数据	不需要额外的IO操作，直接从覆盖索引获取完整数据
CPU 消耗	需要额外的CPU消耗，执行回表操作和数据合并	不需要额外的CPU消耗，直接从索引中获取完整数据
查询性能	性能较低，需要额外的IO和CPU消耗	性能较高，减少了IO和CPU消耗
存储空间	索引和数据存储在不同的位置，需要额外的存储空间	索引和数据存储在一起，不需要额外的存储空间

索引失效