当前位置：首页 > news >正文

索引的使用和优化

news 2024/10/27 5:24:49

索引就是一种快速查询和检索数据的数据结构，mysql中的索引结构有：B+树和Hash。

索引的作用就相当于目录的作用，我么只需先去目录里面查找字的位置，然后回家诶翻到那一页就行了，这样查找非常快，

创建一个表结构：

CREATE TABLE users (id INT PRIMARY KEY,name VARCHAR(50),age INT,email VARCHAR(50)
);

1. 单列索引的创建与使用

场景：查询用户的年龄

我们需要查询某个年龄段的用户，如age = 30。

-- 创建单列索引

-- 创建索引用index关键字
create index idx_age on users(age);

-- 使用索引查询,

-- 直接查询已经设置索引的关键字age：
SELECT * FROM users WHERE age = 30;

-- 效果：当在age列上建立索引后，数据库不再进行全表扫描，而是通过索引快速找到匹配的记录。

2. 唯一索引的创建与使用

场景：确保电子邮件唯一

在用户表中，要求email字段的值唯一。

使用unique Index 关键字创建唯一索引，确保被创建的字段唯一值。

CREATE UNIQUE INDEX idx_email ON users(email);

-- 插入示例

 INSERT INTO users (id, name, age, email) VALUES (102, 'Alice', 25, 'bob@example.com');
INSERT INTO users (id, name, age, email) VALUES (103, 'Bob', 30, 'bob@example.com');

插入设置有唯一索引的列，如果插入的是重复的值的话会提示报错。

3. 复合索引的创建与使用

场景：按姓名和年龄组合查询用户

我们需要查询姓名为Tom且年龄为25的用户。

创建复合索引：

创建复合索引
create  index indexNameAndAge on users(name,age);

-- 使用复合索引将两个复合索引创建的列用and关联查询条件即可

SELECT * FROM users WHERE name = 'Diana Bell' AND age = 36;

注意事项：最左前缀原则：

查询name = 'Diana Bell'时，复合索引依然有效。
但如果只查询age = 36，则索引无法使用。

4. ORDER BY 优化使用索引

场景：按用户年龄排序

对用户的年龄进行升序排序。

SELECT * FROM users ORDER BY age;

索引优化： 在age列上建立索引，可以避免排序时的全表扫描。

CREATE INDEX idx_age ON users(age);

5. 避免在索引列使用函数

场景：按创建时间查询

假设我们有一个用户注册时间的字段create_time。

错误示例： 使用YEAR函数查询注册年份为2023的用户。

SELECT * FROM users WHERE YEAR(create_time) = 2023;

6. 删除和查看索引

删除索引：

DROP INDEX idx_age ON users;

查看表中的索引：

SHOW INDEX FROM users;

7. 使用 explain 分析索引使用情况

在优化SQL时，可以使用EXPLAIN查看查询的执行计划，判断索引是否生效。

explain SELECT * from users WHERE email='hannah.montana@example.com';

type：如果为ALL，说明是全表扫描。

key：表示查询使用了哪个索引。

8. 使用覆盖索引

场景：只查询用户的姓名和年龄

假设我们经常查询用户的name和age，希望加快查询速度。

CREATE INDEX idx_name_age_cover ON users(name, age);

使用覆盖索引的查询：

SELECT name, age FROM users WHERE age = 25;

覆盖索引效果： 因为name和age列已经包含在索引中，不需要再访问表的数据页（避免回表），提高查询性能。查询语句所需的列都包含在了创建的索引中，不需要再去查询实际的数据行，从而提高查询性能。

总结

创建索引：
- 单列索引：适用于单字段查询。
- 复合索引：适用于多字段组合查询。
- 唯一索引：保证列值唯一。
- 覆盖索引：避免回表，提高性能。
使用索引的技巧：
- 在常用的WHERE、JOIN、ORDER BY列上建立索引。
- 避免对索引列使用函数或表达式。
- 使用EXPLAIN查看查询的执行计划，确保索引生效。