操作符混合运用

描述

题目：现在运营想要找到gpa在3.5以上(不包括3.5)的山东大学用户 或 gpa在3.8以上(不包括3.8)的复旦大学同学进行用户调研，请你取出相应数据

示例：user_profile

| id | device_id | gender | age | university | province | gpa | | -- | --------- | ------ | ---- | ---------- | -------- | --- | | 1 | 2138 | male | 21 | 北京大学 | BeiJing | 3.4 | | 2 | 3214 | male | NULL | 复旦大学 | Shanghai | 4 | | 3 | 6543 | female | 20 | 北京大学 | BeiJing | 3.2 | | 4 | 2315 | female | 23 | 浙江大学 | ZheJiang | 3.6 | | 5 | 5432 | male | 25 | 山东大学 | Shandong | 3.8 |

根据输入，你的查询应返回以下结果：(该题对于小数点后面的0不需要计算与统计，后台系统会统一输出小数点后面1位)

| device_id | gender | age | university | gpa | | --------- | ------ | ---- | ---------- | --- | | 3214 | male | NULL | 复旦大学 | 4 | | 5432 | male | 25 | 山东大学 | 3.8 |

示例

输入：

drop table if exists user_profile; CREATE TABLE `user_profile` ( `id` int NOT NULL, `device_id` int NOT NULL, `gender` varchar(14) NOT NULL, `age` int , `university` varchar(32) NOT NULL, `province` varchar(32) NOT NULL, `gpa` float); INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学','BeiJing',3.4); INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学','Shanghai',4.0); INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学','BeiJing',3.2); INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学','ZheJiang',3.6); INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学','Shandong',3.8);

输出：

3214|male|None|复旦大学|4.0 5432|male|25|山东大学|3.8

解题

正常写

select device_id, gender, age, university, gpa from user_profile where ( gpa > 3.5 and university = "山东大学" ) or ( gpa > 3.8 and university = "复旦大学" )

优化

select device_id, gender, age, university, gpa from user_profile where device_id in ( select device_id from user_profile where gpa > 3.5 and university = '山东大学' ) or device_id in ( select device_id from user_profile where gpa > 3.8 and university = '复旦大学' );

小结

对比

第一种方式使用了逻辑操作符"or"连接两个条件，易于阅读和理解。

而第二种方式使用了子查询来进行两个条件的筛选。在处理大量数据时更有效率，因为子查询使用索引优化了，提高查询速度。 该查询可以进行性能优化的原因是因为它的过滤条件中包含了索引字段。

在这个查询中，过滤条件是通过使用 "gpa" 和 "university" 字段进行的。

如果针对这两个字段创建了索引，那么数据库可以快速定位满足条件的记录，从而提高查询性能。 当创建索引时，数据库会为每个索引字段创建一个索引结构，该结构包含了该字段的值和对应记录的引用。当执行查询时，数据库可以直接在索引中查找匹配的记录，而不需要逐行扫描整个数据表。这可以大大减少查询所需的时间和资源。

此外，由于查询中使用了 "or" 条件，数据库可以通过并行处理这两个条件中的子查询，从而进一步提高查询性能。

如果 "gpa" 和 "university" 字段没有建立索引，那么这个查询将会执行全表扫描(sequential scan)，即便在使用了 "or" 条件时也没有重大区别。

为了提高查询性能，可以考虑为 "gpa" 和 "university" 字段建立索引。索引可以显着缩短数据库筛选符合条件的记录所需的时间，减轻数据库负担。如果无法在这些字段上建立索引，可以考虑优化查询语句本身。

例如，可以尝试在不使用 "or" 条件的情况下编写更特定的过滤条件以减少扫描的数据量。还可以尝试使用其他技术，例如分区表或集群，以减少扫描时的负载。