索引的使用以及使用索引优化sql

索引就是一种快速查询和检索数据的数据结构，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. 删除和查看索引

删除索引：

DROP INDEX idx_age ON users;

查看表中的索引：

SHOW INDEX FROM users;

二、如何使用索引提高查询性能

1. ORDER BY 优化使用索引，GROUP BY也可以使用

场景：按用户年龄排序

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

SELECT * FROM users ORDER BY age;

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

CREATE INDEX idx_age ON users(age);

在需要排序或者分组的字段列上建立索引，可以避免排序时的全表扫描，提高查询效率。

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

场景：按创建时间查询

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

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

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

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

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

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

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

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

4. 使用覆盖索引

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

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

CREATE INDEX idx_name_age_cover ON users(name, age);

使用覆盖索引的查询：

SELECT name, age FROM users WHERE age = 25;

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

5.查询中的WHERE条件列加索引：

例：

SELECT * FROM users WHERE age = 30;

给where条件列建立索引可以加快数据检索速度。

6.在JOIN的关联列上建立索引：

例：

SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;

为orders.customer_id和customers.id列建立索引，给join关联的两个列设置索引，可以提高JOIN的性能。

总结

索引的优点：

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