SQL的逻辑和优化（面试必知）

我们在开发中经常需要操作的就是SQL语句，SQL语句大大决定了这个功能，在复杂的业务无非复杂的SQL语句，像MQ，Redis其实也是辅助SQL的功能，那么如何对SQL进行解析和优化呢，今天就通过一个简单的例子来帮助理解SQL的逻辑和优化

1.SQL的解析

辅助理解文章 SQL函数大全 大白猫的博客 SQL的优化方案 大白猫的博客

sql本质其实是四种语句，n些函数，接下来使用一个很简单的表实现解析和优化，sql如下

CREATE TABLE students (
  student_id INT PRIMARY KEY,
  name VARCHAR(100),
  score DECIMAL(5, 2),
  class VARCHAR(50),
  department_name VARCHAR(100)
);

插入数据

INSERT INTO students (student_id, name, score, class, department_name) VALUES
(1, '小妹', 95.50, '一班', '科学系'),
(2, '小李', 88.75, '二班', '数学系'),
(3, '瑶瑶', 78.25, '一班', '艺术系'),
(4, '坤坤', 92.00, '三班', '科学系'),
(5, '晓丽', 85.00, '二班', '数学系'),
(6, '张明', 76.50, '三班', '艺术系'),
(7, '白素', 89.25, '一班', '科学系');

这是一个创建表的sql，并插入了七条数据，具体介绍如下

student_id 是一个整型字段，作为主键来唯一标识每个学生
name 学生姓名 是可变长度的字符串字段
class 学生班级 是可变长度的字符串字段
department_name 学生系名 是可变长度的字符串字段
score 学生分数 是一个十进制字段，可以存储最多总共5位数字，其中包括小数点后的2

实现增删改查功能

增（Insert） - 向

students

表中插入一条新记录

INSERT INTO students (student_id, name, score, class, department_name)
VALUES (1, '张三', 88.5, '1班', '计算机科学与技术');

删（Delete） - 从

students

表中删除一个用户，假设我们知道用户的

UserID

是

1

DELETE FROM students WHERE student_id = 1;

改（Update） - 更新一个学生的分数，比如我们要将

UserID

为

1

的用户的分数 改为 90.0分

UPDATE students SET score = 90.0 WHERE student_id = 1;

查（Select） - 从

students

表中查询用户信息；以下是几个查询示例

从students表中查询所有记录：
SELECT * FROM students;

查询特定系的所有学生信息：
SELECT * FROM students WHERE department_name = '计算机科学与技术';

2.exists和in的作用

如果是单表的sql查询，那倒也没啥难度，但是我们在业务中一般要对一堆数据进行属性拆分，分成各种各样的表，表内包含对应的字段属性，那么我们需要对其进行连表查询

我们都知道多表关联本身就是嵌套for之后判断条件查询，所以如果将需要关联的表使用join的关联，会大大的影响查询的效率，让笛卡尔积变得很大，那么怎么解决这个问题呢

其中讲解下exists和in这两个函数，为什么呢，应为他们两个可以避免过多地内联查询，减少笛卡尔积，加快查询效率，大致解释如下:

exists(里面的sql最外层可以嵌套主sql，先执行该sql在嵌套主sql，该sql联表越大越好)
 
in(里面的sql最外层可以嵌套主sql，先执行该sql在嵌套主sql，该sql联表越小越好)

其中我们需要了解两个名称，父查询和子查询

子查询不依赖父查询，父查询需要依赖子查询的结果

（IN）实例1：帮我查询和瑶瑶分数一样的学生名字(不包含瑶瑶自己)

SELECT name FROM students 
WHERE score IN 
(SELECT score FROM students WHERE name = '瑶瑶') 
AND name <> '瑶瑶';

还有如下

查询比瑶瑶分数高的同学名称
SELECT name 
FROM students 
WHERE score > (SELECT score FROM students WHERE name = '瑶瑶');
查询比瑶瑶分数低的同学名称
SELECT name 
FROM students 
WHERE score < (SELECT score FROM students WHERE name = '瑶瑶');

（IN）实例2：找出与瑶瑶分数相等并且与瑶瑶在同一个班级的同学的名称（不包含瑶瑶自己）

SELECT name
FROM students
WHERE score IN (
    SELECT score
    FROM students
    WHERE name = '瑶瑶'
)
AND class IN (
    SELECT class
    FROM students
    WHERE name = '瑶瑶'
)
AND name <> '瑶瑶';

（IN）实例3：创建一个根据学生ID判断地区的表（不包含瑶瑶自己）

-- 创建一个包含地区信息的表
CREATE TABLE student_regions (
    student_id int(8),
    region VARCHAR(255)
);

实现逻辑的SQL如下

select region 
from student_regions 
where student_id IN
(SELECT id
FROM students
WHERE score IN (
    SELECT score
    FROM students
    WHERE name = '瑶瑶'
)
AND name <> '瑶瑶';
)

IN的逻辑是：首先执行父查询，之后再子查询进行循环，也就是只要子查询内的数据少，父查询的数据量多，就可以使用in实现sql查询的效率提升

(EXISTS) 实例4：帮我查询和瑶瑶分数一样和不一样的学生名字

-- 查询分数与瑶瑶相同的学生名字
SELECT name
FROM students
WHERE EXISTS (SELECT 1 FROM students s WHERE s.name = '瑶瑶' AND s.score = students.score);

-- 查询分数与瑶瑶不同的学生名字
SELECT name
FROM students
WHERE NOT EXISTS (SELECT 1 FROM students s WHERE s.name = '瑶瑶' AND s.score = students.score);

-- 查询分数与瑶瑶不同的学生名字
SELECT name
FROM students
WHERE EXISTS (SELECT 1 FROM students s WHERE s.name = '瑶瑶' AND s.score != students.score);

(EXISTS) 实例5：找出与瑶瑶分数相等并且与瑶瑶在同一个班级的同学的名称（不包含瑶瑶自己）

SELECT name
FROM students s1
WHERE EXISTS (
    SELECT 1
    FROM students s2
    WHERE s2.name = '瑶瑶'
    AND s2.score = s1.score
    AND s2.class = s1.class
)
AND s1.name <> '瑶瑶';

（EXISTS）实例6：创建一个根据学生ID判断地区的表（不包含瑶瑶自己）

SELECT sr.region
FROM student_regions sr
WHERE EXISTS (
    SELECT 1
    FROM students s
    WHERE s.name <> '瑶瑶'
    AND s.score = (
        SELECT score
        FROM students
        WHERE name = '瑶瑶'
    )
    AND s.student_id = sr.student_id
);

EXISTS的逻辑是：首先执行子查询，之后再父查询进行循环，也就是只要父查询内的数据少，子查询的数据量多，就可以使用EXISTS实现sql查询的效率提升

那么我们在开发中如何使用这两个函数提高效率呢

1.先where连接起来这些表把条件都加上(确认数据是否正确)
2.在查询需要返回的数据找到对应的表进行关联，之后可以在exists或者in中实现，注意必须把唯一的关联起来
3.实现exists和in的嵌套子查询，了解逻辑提高效率(提高效率)

当然也可以使用chatGPT让其自己进行优化

3.索引的介绍以及使用（包含注意事项）

这只是第一步的优化，接下来需要使用索引进行优化查询

索引会大大的提高的查询的性能，也可能会导致插入、更新和删除操作的性能稍微降低，因为数据库管理系统需要维护索引的结构

索引的类型（当然主键包含主键索引，一个表只能有一个，代表数据的唯一标识）：

普通索引 (NORMAL INDEX):
特点: 这是最基本的索引类型，用于加速SELECT语句的执行；它们没有唯一性限制，允许重复的值
用途: 提高检索数据的速度，常用于经常需要搜索的列

唯一索引 (UNIQUE INDEX):
特点: 保证索引列的唯一性，不允许重复的值；如果试图插入具有重复索引值的数据，会导致错误
用途: 用于确保表中某列的唯一性，通常用于主键列

组合索引 (COMPOSITE INDEX):
特点: 包含多个列的索引，允许在这些列的组合上进行快速检索；组合索引的顺序很重要，因为它影响查询的性能。
用途: 加速涉及多列的查询，例如在WHERE子句中使用多个列进行过滤

全文索引 (FULLTEXT INDEX):
特点: 用于在文本数据上执行全文本搜索，而不仅仅是简单的匹配；支持全文搜索的数据库引擎可以更好地处理自然语言查询
用途: 适用于需要进行全文搜索的文本字段，如文章内容或博客评论

空间索引 (SPATIAL INDEX):
特点: 用于优化空间数据类型的检索，如地理信息系统 (GIS) 中的地理坐标数据
用途: 适用于需要处理地理空间数据的应用，例如地图应用或位置基础的服务

可视化如下，先找到对应的表点击设置

点击索引添加索引如下

其中包含名称，字段，索引类型，索引方法，注释等五个属性

介绍如下

名称 (Name):

在 "Navigator" 窗格中找到您想要添加索引的表
右键单击表，然后选择 "Alter Table..." 选项
在 "Indexes" 选项卡中，找到 "Name" 字段，输入索引的名称

字段 (Fields):
在 "Fields" 选项卡中，您可以选择要添加索引的字段；单击要添加的字段，然后单击右侧的 "Add" 按钮

索引类型 (Index Type):
在 "Indexes" 选项卡中，选择适当的索引类型；通常可以选择普通索引、唯一索引或全文索引

索引方法 (Index Method):
MySQL Workbench通常会默认选择适当的索引方法；如果需要自定义索引方法，您可以在 "Index Method" 中进行调整

注释 (Comment):
在 "Indexes" 选项卡中，您可以为索引添加注释；这对于描述索引的目的或功能非常有用

索引方法有两个Btree和Hash，一个是使用B树结构实现索引，另一个是使用Hash结构实现索引，这两个大部分是使用Btree，Hash只有在等值查询有优势

索引字段可以加很多实现组合索引，但是需要遵循最左匹配原则

只需要在经常查询的字段加上索引即可，创建删除索引点点就可以实现，也不需要命令了

索引的使用情况如下

哪些情况需要添加索引

1.WHERE 子句中的过滤条件:
需要在经常用于WHERE子句中的字段上创建索引，以加速数据的检索；例如，如果您经常根据学生的department_name过滤数据，可以在该字段上创建索引

2.JOIN 操作中的连接字段:
在执行连接操作（如JOIN）时，需要在连接字段上创建索引，以提高连接的性能；连接字段通常是外键

3.ORDER BY 子句中的排序字段:
如果查询需要按某一字段进行排序，可以在排序字段上创建索引；这可以加速排序操作，特别是对大型数据集

4.GROUP BY 子句中的分组字段:
如果您执行聚合查询，例如使用GROUP BY子句，可以在分组字段上创建索引，以加速分组操作

5.唯一性约束字段:
主键字段和唯一性约束字段应该自动创建唯一索引，以确保数据的唯一性

6.外键字段:
外键字段通常用于连接不同表之间的关系，应在外键字段上创建索引以加速连接操作

7.全文搜索字段:
如果您进行全文搜索，可以在全文搜索字段上创建全文索引以提高搜索性能

8.较大的文本字段:
在较大的文本字段上创建全文索引，以支持全文搜索操作，如在博客文章或评论字段上

哪些情况不添加索引

1.低选择性的列:
如果在具有低选择性的列上创建索引，可能会导致索引失效。例如，在性别列上创建索引通常没有太大意义，因为它只有两个不同的值

2.短文本列:
在非选择性的短文本列上创建索引通常不会提高查询性能，因为它们不会显著减少检索的数据量

3.频繁更新的列:
如果某个列经常被更新，索引的维护成本可能会超过性能收益，导致索引失效

4.复合索引的字段顺序:
在复合索引中，字段的顺序很重要；如果查询不按照索引的字段顺序进行检索，索引可能不会被使用

5.表太小:
对于非常小的表，创建索引可能不值得，因为数据库系统可能会选择全表扫描，而不是使用索引

哪些行为可能会导致索引失效呢

模糊查询（不以通配符开头的LIKE查询）:

当使用LIKE操作符进行模糊查询时，如果通配符（%）出现在搜索模式的开头，索引通常会失效。例如，WHERE name LIKE '%John%' 不会使用索引。但如果通配符出现在模式的末尾（例如，WHERE name LIKE 'John%'），索引可能仍然有效。

列上的函数操作:
如果在索引列上应用了函数操作，例如UPPER(name) = 'JOHN'，索引可能会失效，因为索引无法直接应用到经过函数处理的列上。

数据类型不匹配:
当查询中的值与索引列的数据类型不匹配时，索引可能会失效。例如，如果索引列是整数，但查询使用字符串进行比较，索引可能不会生效。

OR 条件:
在查询中使用多个OR条件时，索引可能会失效。例如，WHERE age = 25 OR name = 'John' 可能无法充分利用索引。

不等式条件:
不等式条件（如<, >, <=, >=）在某些情况下也可能导致索引失效，尤其是在非等值条件的情况下。

多列索引的顺序:
对于多列索引，索引的列顺序很重要。如果查询的条件不按照索引列的顺序进行筛选，索引可能不会被充分利用。

数据分布不均匀:
当数据分布不均匀时，索引可能会失效。如果某个值在数据中出现得太频繁或太稀少，数据库可能会选择不使用索引。

大量的NULL值:
如果索引列包含大量的NULL值，索引可能会失效，因为数据库可能不会将NULL值包括在索引中。

过度索引化:
过度添加索引可能会导致性能下降，因为数据库系统需要维护多个索引，增加了写操作的开销。

短时间内大量的插入、更新或删除操作:
大规模的数据更改操作可能导致索引失效，因为数据库系统需要不断更新索引结构。

这样的话索引的添加就讲完了，本质是根据Btree数和Hash结构等构建一个索引数进行查询提高性能

4.分库分表

阿里规定当单表数据达到500万或者单表容量达到2GB需要分库分表

怎么实现分库分表的插入和查询以及全局ID的分表呢

全局ID无非hash分表插入数据，范围分表插入数据，之类的

1.插入数据的时候在mybatis动态sql加入判断插入不同的表
查询时需要传入分表字段进行动态sql的拼接

2.停服直接手动进行换表
查询时需要手动进行sql的换表

3.使用分库分表中间件（这点暂时没有详情）

所以如果想要优化sql的话首先对sql语句进行优化，不行的话考虑再查询常用的字段加上索引，最后数据量过大使用分库分表提升效率