索引概念及重要性
索引是数据库管理系统中用于加速数据检索的数据结构。它通过创建指向表中数据行的指针列表来工作，使得数据库能够快速定位到符合查询条件的记录，从而极大地提升查询效率。对于频繁进行读取操作的大型数据库来说，合理设计和使用索引至关重要。
MySQL索引类型
- 普通索引 (
INDEX/KEY)：最常见的索引类型，用于加速数据检索，无唯一性限制。 - 唯一性索引 (
UNIQUE INDEX/UNIQUE KEY)：确保索引列的值唯一，允许有NULL值，但不能重复。 - 主键索引 (
PRIMARY KEY)：为表定义一个唯一标识符，不允许NULL值，且每个表只能有一个主键索引。 - 全文索引 (
FULLTEXT INDEX/FULLTEXT KEY)：专门用于全文搜索的大文本字段，提供更高效的文本搜索能力。 - 空间索引 (
SPATIAL INDEX/SPATIAL KEY)：用于优化对空间数据类型（如几何类型）的查询。
索引的分类
- 单列索引：基于单个列创建的索引。
- 复合索引：基于多个列联合创建的索引，查询优化器会根据索引中的第一个列开始匹配。
创建索引的方法
- 创建表时定义索引:
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
INDEX index_name(column1),
UNIQUE INDEX index_name(column2),
PRIMARY KEY (column3),
FULLTEXT INDEX index_name(column4),
SPATIAL INDEX index_name(column5)
);
- 在现有表上添加索引:
ALTER TABLE table_name
ADD INDEX index_name(column_list),
ADD UNIQUE INDEX index_name(column_list),
ADD PRIMARY KEY(column),
ADD FULLTEXT INDEX index_name(column_list),
ADD SPATIAL INDEX index_name(column_list);
- 在现有表上添加索引，通过CREATE INDEX语句单独创建:
CREATE INDEX index_name ON table_name(column_list);
CREATE UNIQUE(也可为其他类型) INDEX index_name ON table_name(column_list);
删除索引
DROP INDEX index_name ON table_name;
ALTER TABLE table_name DROP INDEX index_name;
使用索引的策略和注意事项
- 选择性高：索引的选择性越高（即不同值的数量与总行数的比例越大），索引的效果越好。
- 索引覆盖：当查询所需的所有列都包含在索引中时，可以避免回表查询，称为“覆盖索引”，能进一步提升性能。
- 避免过度索引：过多的索引会占用存储空间，增加写操作的开销，如插入、更新和删除操作会变得更慢。
- 监控和分析：定期使用
EXPLAIN分析查询计划，查看索引使用情况，并根据实际情况调整索引策略。
请根据实际应用场景综合考虑上述因素，合理设计和使用索引，以达到最佳的数据库性能。
通过 show create table table_name;可查看索引是否创建索引
通过 show index(或keys|indexs) from table_name;可查看全部索引
在MySQL中除了可以查看数据表中的索引信息，还可以通过EXPLAIN关键字分析SQL语句的执行情况，例如分析SQL语句执行时是否使用了索引。EXPLAIN可以分析的语句有SELECT、UPDATE、DELETE、INSERT和REPLACE。
在MySQL中，EXPLAIN是一个非常有用的工具，用于帮助开发者理解查询优化器如何执行SQL语句，这对于性能调优至关重要。它能够提供关于查询如何被执行的详细信息，包括是否以及如何使用索引，表的连接顺序，数据读取方式等。下面通过一个示例来说明如何使用 EXPLAIN关键字，以及它提供的部分关键信息的含义。
示例
假设我们有一个名为 employees的数据表，包含以下列：id(主键), first_name, last_name, department_id(部门ID，已建立索引)，现在我们要查找所有属于“IT”部门的员工。
SQL查询:
SELECT * FROM employees WHERE department_id = 1;
为了分析这个查询的执行计划，我们可以使用 EXPLAIN前缀：
EXPLAIN查询:
EXPLAIN SELECT * FROM employees WHERE department_id = 1;
解释输出
运行 EXPLAIN后，你将看到一个结果集，每一行代表查询执行计划的一个部分。下面是对输出中一些关键列的解释（实际输出列可能根据MySQL版本有所不同）：
- id: 查询中每个表的唯一标识符，如果有多个表参与查询，这些表可能会被分配不同的id。id的顺序不代表执行顺序。
- select_type: 查询的类型，比如SIMPLE（简单查询，不包含UNION或子查询）、PRIMARY（最外层的查询）、DERIVED（来自子查询的结果集）等。
- table: 正在查询的表名。
- type: 访问类型，这表明了MySQL如何查找表中的行。常见的类型有ALL(全表扫描)、index(索引全扫描)、range(索引范围扫描)、ref(使用索引作为条件查询)、eq_ref(对于每个索引键值，表中有且只有一条匹配行)等。我们希望看到的是
eq_ref或ref，特别是当涉及到JOIN操作或者WHERE条件时，这通常意味着索引被有效利用了。 - possible_keys: 可能使用的索引。如果此列为NULL，则没有相关的索引可用于查询。
- key: 实际使用的索引。如果MySQL决定使用某个索引来优化查询，那么这个名字会在这里显示。
- key_len: 使用的索引长度，可以帮助了解是索引的哪一部分被使用了。
- ref: 显示了哪些列或常量被用来对比索引列，以确定行的位置。
- rows: 预计需要检查的行数，越小越好。
- Extra: 提供了额外的信息，比如"Using index"表示查询可以直接从索引中获取所有需要的数据，无需访问实际的行数据；"Using where"表示MySQL服务器将在存储引擎检索行后再进行WHERE过滤。
在这个示例中，如果你看到 key列显示为 department_id，并且 type列是 eq_ref或 ref，这表明MySQL成功地使用了 department_id索引来高效地执行查询。同时，如果 rows值很低，说明查询非常高效，因为它只需要检查很少的行。
在MySQL中，当使用 UNIQUE INDEX创建复合索引（即包含多个列的索引）时，唯一性是基于所有索引列的值组合来判断的，而不是单个列。这意味着，只有当索引列值的组合是唯一的时，数据才被视为唯一，允许存在这样的情况：不同行在索引的第一列上有相同的值，只要这些行在其他索引列上的值不同，整体组合仍然是唯一的。
例如，假设有一个表 users，包含字段 first_name和 last_name，并且你创建了一个基于这两个列的唯一复合索引：
CREATE UNIQUE INDEX uk_users_name ON users(first_name, last_name);
在这种情况下，以下数据插入是允许的：
| first_name | last_name |
|---|---|
| John | Doe |
| John | Smith |
因为尽管 first_name列有重复值"John"，但与各自的 last_name结合后("Doe"和"Smith")，整个组合是唯一的。
然而，如果尝试插入两条完全相同组合的记录，如两条"John Doe"，MySQL将阻止第二次插入，以维护索引的唯一性。因此，复合唯一索引确保的是索引列值集合的整体唯一性，而非单个列的唯一性。
第6章 索引和视图.pptx
https://www.alipan.com/s/N1ZVsLsDesT
点击链接保存，或者复制本段内容，打开「阿里云盘」APP ，无需下载极速在线查看，视频原画倍速播放。