搜索
首页数据库SQLSQL查询如何优化?(详解)
SQL查询如何优化?(详解)Nov 30, 2019 pm 05:54 PM
sql查询

SQL查询如何优化?(详解)

为什么要优化

系统的吞吐量瓶颈往往出现在数据库的访问速度上,即随着应用程序的运行,数据库的中的数据会越来越多,处理时间会相应变慢,且数据是存放在磁盘上的,读写速度无法和内存相比

如何优化

1、设计数据库时:数据库表、字段的设计,存储引擎

2、利用好MySQL自身提供的功能,如索引,语句写法的调优

3、MySQL集群、分库分表、读写分离

关于SQL语句的优化的方法方式,网络有很多经验,所以本文抛开这些,设法在DAO层的优化和数据库设计优化上建树,并列举两个简单实例

例子1:ERP查询优化

现状分析:

1、缺少关联索引
2、Mysql本身的性能所限,对多个表的关联支持不好,目前的性能主要集中在列表查询上面,列表查询关联了很多表

应对方法:

1 增加必要的索引:通过explain查看执行记录,根据执行计划添加索引;
2 先统计业务数据主表主键,获取较小结果集,然后再利用结果集关联查询;
1) 先根据主表和条件查询显示业务数据的主键
2) 根据主键作为查询条件,再关联其他关联表,查询需要的业务字段
3) 在主表查询时,针对需要关联其他表的查询条件,需要做只有设置这个条件,才会做表关联的设置

例如 有如下表 TT_A   TT_B    TT_C  TT_D

假设未优化前的SQL是这样的

SELECT
    A.ID,
    ....
    B.NAME,
    .....
    C.AGE,
    ....
    D.SEX
    .....

FROM  TT_A A
LEFT JOIN TT_B B ON A.ID  = B.ITEM_ID
LEFT JOIN TT_C C ON B.ID = C.ITEM_ID
LEFT JOIN TT_D D ON C.ID = D.ITEM_ID
WHERE 1=1AND A.XX = ?AND A.VV = ?.....

那么优化后的SQL是

第一步

SELECT
    A.ID

FROM  TT_A A
WHERE 1=1AND A.XX = ?AND A.VV = ?第二步

SELECT
    A.ID,
    ....
    B.NAME,
    .....
    C.AGE,
    ....
    D.SEX
    .....
FROM  ( SELECT A.ID,..... FROM  TT_A  WHERE ID IN (1,2,3..)  ) A
LEFT JOIN TT_B B ON A.ID  = B.ITEM_ID
LEFT JOIN TT_C C ON B.ID = C.ITEM_ID
LEFT JOIN TT_D D ON C.ID = D.ITEM_ID
WHERE 1=1AND A.XX = ?AND A.VV = ?

小结:

这种优化适用于,列表查询,因为一个列表查询的条件一般都是和主表挂钩的,所以利用这一点,建立关键字段索引,同时通过查询条件的限制大大的缩小主表的数据量。这样关联其他表的时候就会快的多

例子2:文章搜索优化

假设你要做个贴吧的文章搜索功能,最简单直接的存储结构,就是利用关系数据库,创建这样一个存储文章的关系数据库表 TT_ARTICLES:

 

那么,假如现在的搜索关键字是“目标”,我们就可以利用字符串匹配的方式来对 CONTENT 列进行匹配查询:

select * from ARTICLES where CONTENT like '% 目标 %';

这很容易就实现了搜索功能。但是,这样的方式有着明显的问题,即使用 % 来进行字符串匹配是非常低效的,因此这样的查询需要遍历整个表(全表扫描)。几篇、几十篇文章的时 候,还不是什么问题,但是如果有几十万、几百万的文章,这种方式是完全不可行的。且不说单独的关系数据库表就不能容纳那么大的数据了,就是能够容纳,要扫描一遍,这里的时间代价是难以想象的

于是,我们就要引入“倒排索引”的技术了。在前面所述的场景下, 我们可以把这个概念拆分为两个部分来解释: 好,那上面的 ARTICLES 表依然存在,但现在需要添加一个关键字表 KEYWORDS,并且,KEYWORD 列需要添加索引,因此这条关键字的记录可以被迅速找到:

当然,我们还需要一个关联关系表把 KEYWORDS 表和 ARTICLES 表结合起来, KEYWORD_ID 和 ARTICLE_ID 作为联合主键 

你看,这其实是一个多对多的关系,即同一个关键字可以出现在多篇文章中,而一篇文章可 以包含多个不同的关键字。这样,我们可以先根据被索引了的关键字,从 KEYWARDS 表 中找到相应的 KEYWORD_ID,进而根据它在上面的关联关系表找到 ARTICLE_ID,再根据 它去 ARTICLES 表中找到对应的文章。

小结:

这看起来是三次查找,但是因为每次都走索引,就免去了全表扫描,在数据量较小的时候速 度并不慢,并且,在使用 SQL 实现的时候,这个过程完全可以放到一个 SQL 语句中。在数 据量较小的时候,上面的方法已经足够好用了。 这样解决了全表扫描和字符串 % 匹配查询造成的性能问题。

总结:

在技术面试的时候,如果你能举出实际的例子,或者是直接说自己开发过程的问题和收获会让面试分会加很多,回答逻辑性也要强一点,不要东一点西一点,容易把自己都绕晕的。例如,问为怎么优化SQL你不要一上来就直接回答加索引,你可以这样回答:

面试官您好,首先我们的项目DB数据量遇到了瓶颈,导致列表查询非常缓慢,给用户的体验不好,为了解决这个问题,有很多种方法,例如最基本的数据库表设计,基本的SQL优化,MYSQL的集群,读写分离,分库分表,架构上增加缓存层等,他们的优缺点……,综合这些然后再结合我们项目特点,最后我们在技术选型的时候选了谁。

如果你这样有条不紊,有理有据的回答了问题而且还说出这么多问题外的知识点,面试官会觉得你不只是一个会写代码的人,而是你逻辑清晰,你对技术选型,有自己的理解和思考

本文来自 SQL教程 栏目,欢迎学习!

以上是SQL查询如何优化?(详解)的详细内容。更多信息请关注PHP中文网其他相关文章!

声明
本文转载于:cnblogs。如有侵权,请联系admin@php.cn删除
美团面试题:慢SQL有遇到过吗?是怎么解决的?美团面试题:慢SQL有遇到过吗?是怎么解决的?Aug 24, 2023 pm 03:41 PM

MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录MySQL中查询时间超过(大于)设置阈值(long_query_time)的语句,记录到慢查询日志中。

PHP和PDO: 如何执行复杂的SQL查询语句PHP和PDO: 如何执行复杂的SQL查询语句Jul 28, 2023 pm 03:43 PM

PHP和PDO:如何执行复杂的SQL查询语句在处理数据库操作时,PHP提供了一种强大的扩展库PDO(PHPDataObjects),用于简化与数据库的交互。PDO支持多种数据库,比如MySQL、SQLite等,同时也提供了丰富的功能和方法,方便开发人员进行各种数据库操作。本文将介绍如何使用PDO执行复杂的SQL查询语句,并附上相应的代码示例。连接数据库

PHP编程中有哪些常见的表格操作?PHP编程中有哪些常见的表格操作?Jun 12, 2023 am 09:46 AM

在Web开发中,表格是最基本也是最常用的一个元素,而PHP是一种流行的服务器端编程语言,在表格操作中有许多常见的技巧和方法。本文将介绍PHP编程中常见的表格操作。显示数据表格在PHP中,可以使用HTML中的表格标签来显示数据表格,值得注意的是,表格必须在PHP脚本中生成。以下是基本的HTML表格标签示例:<table><tr>

为什么我的Go程序在执行SQL查询时出错?为什么我的Go程序在执行SQL查询时出错?Jun 09, 2023 pm 06:10 PM

在Go语言编程中,使用SQL查询是常见的任务。然而,有时候在执行SQL查询时会出现错误,导致程序无法正确执行。为了解决这些错误,我们需要深入了解SQL查询和Go语言的交互方式。下面是一些可能出现的错误和对应的解决方法。缺少数据库驱动在Go语言中,您需要使用特定的数据库驱动程序来连接和操作数据库。如果您尝试执行数据库查询,而未正确安装和配置该数据库驱动程序

如何在PHP开发中优化SQL查询语句和索引使用?如何在PHP开发中优化SQL查询语句和索引使用?Nov 02, 2023 pm 12:12 PM

如何在PHP开发中优化SQL查询语句和索引使用?在PHP开发中,数据库查询是非常常见的操作。然而,当数据量增大时,查询性能可能受到影响,导致应用程序变慢。为了提高查询性能,我们需要优化SQL查询语句和索引的使用。本文将介绍一些优化技巧和最佳实践,以帮助您在PHP开发中提高SQL查询性能。一、使用正确的索引:索引是数据库中提高查询性能的重要组成部分。在设计数据

PHP查询语句使用示例PHP查询语句使用示例Mar 23, 2024 am 11:27 AM

PHP是一种功能强大的服务器端脚本语言,被广泛应用于Web开发中。在Web开发中,我们常常需要与数据库进行交互,执行查询语句来获取数据。本文将为您介绍PHP中如何编写查询语句以及使用示例。1.连接数据库在使用PHP进行数据库查询之前,首先需要建立与数据库的连接。一般情况下,我们会使用MySQL数据库作为示例,连接数据库的代码如下:$servername=

PHP编程中的多数SQL查询效率优化实践PHP编程中的多数SQL查询效率优化实践Jun 23, 2023 am 10:37 AM

随着网络技术的发展,PHP编程已经成为了许多公司网站开发的主流。在PHP编程中,SQL查询效率是需要每一个程序员关注和处理的问题。SQL查询效率低会导致网站响应缓慢、系统负载过高或者产生其他不友好的效果。因此,本文将着重介绍PHP编程中多种SQL查询效率优化实践,以提高程序的执行效率和整个系统的响应速度。数据库索引数据库索引是提高数据库查询速度的一种基本方法

PHP语言开发中如何处理SQL查询错误?PHP语言开发中如何处理SQL查询错误?Jun 09, 2023 pm 06:13 PM

PHP语言是一种常用的服务器端脚本语言,用于网站开发等领域。在PHP语言开发中,SQL查询是常见的操作,但由于各种原因,SQL查询可能会出现错误。因此,开发人员需要在代码中处理这些错误,以确保应用程序的稳定性和正确性。一般来说,在PHP语言中处理SQL查询错误有以下几种常见的方式:1.使用try/catch块处理异常在PHP语言中,可以使用try/catch

See all articles

热AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover

AI Clothes Remover

用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool

Undress AI Tool

免费脱衣服图片

Clothoff.io

Clothoff.io

AI脱衣机

AI Hentai Generator

AI Hentai Generator

免费生成ai无尽的。

热工具

适用于 Eclipse 的 SAP NetWeaver 服务器适配器

适用于 Eclipse 的 SAP NetWeaver 服务器适配器

将Eclipse与SAP NetWeaver应用服务器集成。

SublimeText3 Linux新版

SublimeText3 Linux新版

SublimeText3 Linux最新版

MinGW - 适用于 Windows 的极简 GNU

MinGW - 适用于 Windows 的极简 GNU

这个项目正在迁移到osdn.net/projects/mingw的过程中,你可以继续在那里关注我们。MinGW:GNU编译器集合(GCC)的本地Windows移植版本,可自由分发的导入库和用于构建本地Windows应用程序的头文件;包括对MSVC运行时的扩展,以支持C99功能。MinGW的所有软件都可以在64位Windows平台上运行。

WebStorm Mac版

WebStorm Mac版

好用的JavaScript开发工具

VSCode Windows 64位 下载

VSCode Windows 64位 下载

微软推出的免费、功能强大的一款IDE编辑器