来自 数据库 2020-02-09 18:37 的文章
当前位置: 网上澳门金莎娱乐 > 数据库 > 正文

SQL 性能优化

今天工作中碰到一个sql问题,关于left join的,后面虽然解决了,但是通过此问题了解了一下sql的执行顺序

当我看到sql执行很慢的时候就在想为什么这么慢?

场景还原

不外乎数据大,sql语句复杂,没有索引。

为避免安全纠纷,把场景模拟。

如果要进行优化的话可以从对应的这三个问题出发:

有一个学生表-S,一个成绩表G

看看表是否可以进行拆分成小表,拆分sql语句,建立适合的索引。

CREATE TABLE `test_student` ( `id` bigint(20) NOT NULL COMMENT '学号',`sex` TINYINT DEFAULT '0' COMMENT '性别 0-男 1-女', `name` varchar(255) DEFAULT NULL COMMENT '姓名') ENGINE=InnoDB CHARSET=utf8mb4 COMMENT='学生表'; CREATE TABLE `test_score` ( `id` bigint(20) NOT NULL COMMENT '学号', `score` int NOT NULL COMMENT '分数',`level` TINYINT COMMENT '成绩 0-不及格 1-及格 2-优良 3-优秀') ENGINE=InnoDB CHARSET=utf8mb4 COMMENT='成绩表'; -- 初始化学生INSERT INTO test_student VALUES(1, 0, '张三'), (2, 0, '李四'), (3, 1, '王X芳');

-- 初始化成绩INSERT INTO test_score VALUES(1, 10, 0), (2, 20, 0), (3, 100, 3);

还有查看sql server官方API文档,了解sql的关键字执行顺序,了解执行顺序后才能更好的对sql进行拆分或者优化。

现在有一个需求,查出学生的考试成绩,可能的sql

 

SELECT ts.name AS '名字', tc.score AS '分数'FROM test_student tsLEFT JOIN test_score tcON ts.id = tc.s_id;

关于sql执行顺序

一切都很顺利,突然,刚考完试,就转来一个学生

标准的SQL 的解析顺序为:参考文章 

INSERT INTO test_student VALUES(4, 0, '新来的');

查询的逻辑执行顺序:

他没成绩,还是刚才的sql查

 (1) FROM < left_table> 

 (2) ON < join_condition> 

 (3) < join_type>  JOIN < right_table> 

 (4) WHERE < where_condition> 

 (5) GROUP BY < group_by_list> 

 (6) WITH {cube | rollup}

 (7) HAVING < having_condition> 

 (8) SELECT 

 (9) DISTINCT 

(10) ORDER BY < order_by_list> 

(11) < top_specification>  < select_list> 

老师只要看参加考试的学生的成绩,怎么办

 

1.用inner join

 

SELECT ts.name AS '名字', tc.score AS '分数'FROM test_student tsINNER JOIN test_score tcON ts.id = tc.s_id;

 标准的SQL 的解析顺序为:

2.加条件过滤

 (1).FROM 子句 组装来自不同数据源的数据

SELECT ts.name AS '名字', tc.score AS '分数'FROM test_student tsLEFT JOIN test_score tcON ts.id = tc.s_idAND tc.score is NOT NULL;

 (2).WHERE 子句 基于指定的条件对记录进行筛选

发现加了条件过滤还是不对,额,改用where呢

 (3).GROUP BY 子句 将数据划分为多个分组

SELECT ts.name AS '名字', tc.score AS '分数'FROM test_student tsLEFT JOIN test_score tcON ts.id = tc.s_idWHERE tc.score is NOT NULL;

 (4).使用聚合函数进行计算

bingo,那where为什么就对了呢。

 (5).使用HAVING子句筛选分组

这就涉及到sql的执行顺序了

 (6).计算所有的表达式

where与join on

 (7).使用ORDER BY对结果集进行排序

从上面的例子推断

  

where会从结果集中过滤出符合条件的记录,不符合条件的丢弃

执行顺序:

join操作:有时为了得到完整的结果,我们需要从两个或更多的表中获取结果。我们就需要执行 join。

 1.FROM:对FROM子句中前两个表执行笛卡尔积生成虚拟表vt1

除了我们在上面的例子中使用的 INNER JOIN,我们还可以使用其他几种连接。

 2.ON:对vt1表应用ON筛选器只有满足< join_condition> 为真的行才被插入vt2

下面列出了您可以使用的 JOIN 类型,以及它们之间的差异。

 3.OUTER(join):如果指定了 OUTER JOIN保留表(preserved table)中未找到的行将行作为外部行添加到vt2 生成t3如果from包含两个以上表则对上一个联结生成的结果表和下一个表重复执行步骤和步骤直接结束

JOIN: 如果表中有至少一个匹配,则返回行 LEFT JOIN: 即使右表中没有匹配,也从左表返回所有的行 RIGHT JOIN: 即使左表中没有匹配,也从右表返回所有的行 FULL JOIN: 只要其中一个表中存在匹配,就返回行(mysql不支持,可以考虑用视图实现)

 4.WHERE:对vt3应用 WHERE 筛选器只有使< where_condition> 为true的行才被插入vt4

这里推荐一个写的通俗易懂的简单学习SQL的各种连接Join

 5.GROUP BY:按GROUP BY子句中的列列表对vt4中的行分组生成vt5

sql顺序

 6.CUBE|ROLLUP:把超组(supergroups)插入vt6 生成vt6

从上面看到,在sql中,on是写在了where条件之前,那么数据库引擎分析执行sql时,是否on也是在where前面呢

 7.HAVING:对vt6应用HAVING筛选器只有使< having_condition> 为true的组才插入vt7

一般sql的写法顺序

 8.SELECT:处理select列表产生vt8

1.SELECT [列名称 *代表所有的列]

 9.DISTINCT:将重复的行从vt8中去除产生vt9

本文由网上澳门金莎娱乐发布于数据库,转载请注明出处:SQL 性能优化

关键词: