sql数据库安装教程(数据库多表查询sql语句)

我是小zSQL可以说是分析师最基础的傍身之技了,容易上手,但是真要写一条高效的查询语句,还是需要下点功夫的。这篇文章,是对SQL常用查询优化法则的总结,值得细看~文章目录法则一:只返回需要的结果法则二:确保查询使用了正确的索引法则三:尽量避免使用子查询法则四:不要使用 OFFSET 实现分页法则五:了解 SQL 子句的逻辑执行顺序总结SQL 作为关系型数据库的标准语言,是分析师必不可少的技能之一。SQL 本身并不难学,编写查询语句也很容易,但是想要编写出能够高效运行的查询语句却有一定的难度。查询优化是一个复杂的工程,涉及从硬件到参数配置、不同数据库的解析器、优化器实现、SQL 语句的执行顺序、索引以及统计信息的采集等,甚至应用程序和系统的整体架构。本文介绍几个关键法则,可以帮助我们编写高效的 SQL 查询;尤其是对于初学者而言,这些法则至少可以避免我们写出性能很差的查询语句。以下法则适用于各种关系型数据库,包括但不限于:MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite 等。法则一:只返回需要的结果 一定要为查询语句指定 WHERE 条件,过滤掉不需要的数据行。通常来说,OLTP 系统每次只需要从大量数据中返回很少的几条记录;指定查询条件可以帮助我们通过索引返回结果,而不是全表扫描。绝大多数情况下使用索引时的性能更好,因为索引(B-树、B 树、B*树)执行的是二进制搜索,具有对数时间复杂度,而不是线性时间复杂度。以下是 MySQL 聚簇索引的示意图:

sql数据库安装教程(数据库多表查询sql语句)Clustered index举例来说,假设每个索引分支节点可以存储 100 个记录,100 万(1003)条记录只需要 3 层 B-树即可完成索引。通过索引查找数据时需要读取 3 次索引数据(每次磁盘 IO 读取整个分支节点),加上 1 次磁盘 IO 读取数据即可得到查询结果。相反,如果采用全表扫描,需要执行的磁盘 IO 次数可能高出几个数量级。当数据量增加到 1 亿(1004)时,B-树索引只需要再增加 1 次索引 IO 即可;而全表扫描则需要再增加几个数量级的 IO。同理,我们应该避免使用 SELECT * FROM, 因为它表示查询表中的所有字段。这种写法通常导致数据库需要读取更多的数据,同时网络也需要传输更多的数据,从而导致性能的下降。法则二:确保查询使用了正确的索引 如果缺少合适的索引,即使指定了查询条件也不会通过索引查找数据。因此,我们首先需要确保创建了相应的索引。一般来说,以下字段需要创建索引:经常出现在 WHERE 条件中的字段建立索引可以避免全表扫描;将 ORDER BY 排序的字段加入到索引中,可以避免额外的排序操作;多表连接查询的关联字段建立索引,可以提高连接查询的性能;将 GROUP BY 分组操作字段加入到索引中,可以利用索引完成分组。即使创建了合适的索引,如果 SQL 语句写的有问题,数据库也不会使用索引。导致索引失效的常见问题包括:在 WHERE 子句中对索引字段进行表达式运算或者使用函数都会导致索引失效,这种情况还包括字段的数据类型不匹配,例如字符串和整数进行比较;使用 LIKE 匹配时,如果通配符出现在左侧无法使用索引。对于大型文本数据的模糊匹配,应该考虑数据库提供的全文检索功能,甚至专门的全文搜索引擎(Elasticsearch 等);如果 WHERE 条件中的字段上创建了索引,尽量设置为 NOT NULL;不是所有数据库使用 IS [NOT] NULL 判断时都可以利用索引。执行计划(execution plan,也叫查询计划或者解释计划)是数据库执行 SQL 语句的具体步骤,例如通过索引还是全表扫描访问表中的数据,连接查询的实现方式和连接的顺序等。如果 SQL 语句性能不够理想,我们首先应该查看它的执行计划,通过执行计划(EXPLAIN)确保查询使用了正确的索引。法则三:尽量避免使用子查询 以 MySQL 为例,以下查询返回月薪大于部门平均月薪的员工信息:EXPLAINANALYZESELECTemp_id,emp_nameFROMemployeeeWHEREsalary>(SELECTAVG(salary)FROMemployeeWHEREdept_id=e.dept_id);->Filter:(e.salary>(select#2))(cost=2.75rows=25)(actualtime=0.232..4.401rows=6loops=1)->Tablescanone(cost=2.75rows=25)(actualtime=0.099..0.190rows=25loops=1)->Select#2(subqueryincondition;dependent)->Aggregate:avg(employee.salary)(actualtime=0.147..0.149rows=1loops=25)->Indexlookuponemployeeusingidx_emp_dept(dept_id=e.dept_id)(cost=1.12rows=5)(actualtime=0.068..0.104rows=7loops=25)从执行计划可以看出,MySQL 中采用的是类似 Nested Loop Join 实现方式;子查询循环了 25 次,而实际上可以通过一次扫描计算并缓存每个部门的平均月薪。以下语句将该子查询替换为等价的 JOIN 语句,实现了子查询的展开(Subquery Unnest):EXPLAINANALYZESELECTe.emp_id,e.emp_nameFROMemployeeeJOIN(SELECTdept_id,AVG(salary)ASdept_averageFROMemployeeGROUPBYdept_id)tONe.dept_id=t.dept_idWHEREe.salary>t.dept_average;->Nestedloopinnerjoin(actualtime=0.722..2.354rows=6loops=1)->Tablescanone(cost=2.75rows=25)(actualtime=0.096..0.205rows=25loops=1)->Filter:(e.salary>t.dept_average)(actualtime=0.068..0.076rows=0loops=25)->Indexlookupontusing<auto_key0>(dept_id=e.dept_id)(actualtime=0.011..0.015rows=1loops=25)->Materialize(actualtime=0.048..0.057rows=1loops=25)->Groupaggregate:avg(employee.salary)(actualtime=0.228..0.510rows=5loops=1)->Indexscanonemployeeusingidx_emp_dept(cost=2.75rows=25)(actualtime=0.181..0.348rows=25loops=1)改写之后的查询利用了物化(Materialization)技术,将子查询的结果生成一个内存临时表;然后与 employee 表进行连接。通过实际执行时间可以看出这种方式更快。以上示例在 Oracle 和 SQL Server 中会自动执行子查询展开,两种写法效果相同;在 PostgreSQL 中与 MySQL 类似,第一个语句使用 Nested Loop Join,改写为 JOIN 之后使用 Hash Join 实现,性能更好。另外,对于 IN 和 EXISTS 子查询也可以得出类似的结论。由于不同数据库的优化器能力有所差异,我们应该尽量避免使用子查询,考虑使用 JOIN 进行重写。

法则四:不要使用 OFFSET 实现分页 分页查询的原理就是先跳过指定的行数,再返回 Top-N 记录。分页查询的示意图如下:

●终于把PEST分析模型给整明白了!

●品牌知名度分析实例

发表评论

登录后才能评论