mysql的索引优化
- expalin关键字的用法
- explain索引优化示例
- type列用法
- 执行查询的顺序
- 类型概述
- 索引概念
- 索引的定义
- 索引的分类
- 主键&唯一区别
- 唯一索引的创建和查询
- 创建一个唯一索引
- 查询一个唯一索引
- 场景题合集
- 唯一索引的场景题
- 主键索引的场景题（B+树）
- 联合索引的场景题
expalin关键字的用法
explain索引优化示例
- 下面是一个示例，假设我们有一个简单的表 users，包含两个字段 id 和 name，并且 id 字段上有一个索引：
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50)
);
INSERT INTO users (id, name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
CREATE INDEX idx_id ON users (id);
- 我们想要查询 id 为 2 的用户：
SELECT * FROM users WHERE id = 2;
- 我们可以使用 EXPLAIN 命令来分析这个查询的执行计划：
EXPLAIN SELECT * FROM users WHERE id = 2;
- 输出的信息
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE users NULL const idx_id idx_id 4 const 1 100.00 NULL
id：查询中每个 SELECT 子句的唯一标识符。如果查询中有多个子句，则每个子句都有一个唯一的标识符。这个字段并不直接影响查询的优化。
select_type：查询的类型，常见的值包括 SIMPLE、PRIMARY、DERIVED 等。SIMPLE 表示查询不包含子查询或联合查询，是最简单的查询类型。优化时通常关注于将查询转换为简单类型以提高性能。
通过分析这些字段，你可以了解查询执行情况、是否使用了索引以及查询效率。如有必要，优化查询语句和索引设计以提高性能。
type列用法
执行查询的顺序
const、eq_ref、ref、range、index、all。
类型概述
all：全表扫描，效率最低，应该尽量避免使用index：通过完整索引扫描进行查询，效率较低，可能会导致全表扫描range：通过索引范围进行查询，效率相对较低，但仍然比全表扫描要好ref：使用非唯一索引进行查询，可能会返回多行结果，但效率仍然比较高eq_ref：使用索引进行连接，只有一行匹配，效率较高const：通过常量条件进行查询，是最理想的访问方式，通常表示数据库能够直接定位到唯一的行
索引概念
索引的定义
在一个字典中，你可以按照字母顺序快速查找单词，而不需要逐个查找。这就类似于在数据库表中使用索引来快速查找数据。字典中的字母顺序类似于索引中的排序规则，而单词类似于表中的数据。
索引的分类
- 唯一索引
是指对一个表中的某个列创建索引，并且要求该列中的所有数据都是唯一的，也就是不能有重复的值。唯一索引可以提高查询效率、确保数据完整性，并防止重复的数据被插入到数据库中。(可有多个)。
- 主键索引
是唯一索引的一种特殊类型。创建主键会自动创建主键索引。中的每个值是非空，唯一的。(主动对主键建立 B+树索引，以便快速定位和查找)
- 聚簇索引(聚集索引)
按照索引的键值对表进行重排，使得数据的物理存储顺序和索引顺序一致。因此每个表只能有一个聚簇索引，且聚簇索引的键值不能包含空值。(一表只能有一个)。
- 非聚簇索引（普通索引）
表中各行数据存放的物理顺序与键值的逻辑顺序不匹配，索引和表格数据分开存放，索引内存储的是指向对应数据所在位置的指针或行标识。聚索引比非聚索引有更快的数据访问速度。
- 复合索引
联合索引同时考虑多个列的值，以创建一个索引结构。联合索引的作用是加快对多列组合条件的查询操作，例如在 WHERE 子句中涉及到多个列的查询条件。通过联合索引，数据库可以更快地定位到满足多个查询条件的数据行，提高查询性能。
主键&唯一区别
唯一索引的创建和查询
创建一个唯一索引
CREATE UNIQUE INDEX index_name ON table_name (column_name);
查询一个唯一索引
SELECT * FROM table_name WHERE unique_column = 'value';
场景题合集
唯一索引的场景题
假设有一个名为 users 的用户表，其中包含 id、username 和 email 等字段，现在要在 email 列上创建一个唯一索引，确保电子邮件地址的唯一性。下面是创建唯一索引并执行插入操作的 SQL 语句：
-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users (email);
-- 尝试插入一条新记录，保证电子邮件地址的唯一性
INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com');
-- 尝试再次插入相同的电子邮件地址，会返回唯一性约束违反的错误
INSERT INTO users (username, email) VALUES ('jane_doe', 'john@example.com');
在这个示例中，创建了一个名为 idx_email 的唯一索引，然后尝试插入两条记录，第一条记录的电子邮件地址是 ‘john@example.com’，插入成功；而第二条记录尝试使用相同的电子邮件地址，但由于唯一索引的限制条件，插入操作会失败，并返回唯一性约束违反的错误。
主键索引的场景题（B+树）
CREATE TABLE products (
product_id INT PRIMARY KEY,
name VARCHAR(100),
price DECIMAL(10, 2),
stock_quantity INT
);
在这个解决方案中，我们在 products 表上创建了一个名为 product_id 的主键索引。这个主键索引将建立 B+ 树索引结构，使得在执行商品查找、浏览和购买操作时能够快速定位到目标商品。
联合索引的场景题
CREATE INDEX idx_user_created_at ON posts (user_id, created_at);
