搜索
首页数据库mysql教程分享几条sql语句命令优化技巧

本文章分享几条sql语句命令优化技巧,有需要了解的同学可参考一下。

1、应用程序中,保证在实现功能的基础上,尽量减少对的访问次数;通过
搜索参数,尽量减少对表的访问行数,最小化结果集,从而减轻网络负担;能够分
开的操作尽量分开处理,提高每次的响应速度;在数据窗口使用SQL时,尽量把使
用的索引放在选择的首列;算法的结构尽量简单;在查询时,不要过多地使用通配
符如SELECT * FROM T1语句,要用到几列就选择几列如:SELECT COL1,COL2 FROM
T1;在可能的情况下尽量限制尽量结果集行数如:SELECT TOP 300
COL1,COL2,COL3 FROM T1,因为某些情况下用户是不需要那么多的数据的。不要在
应用中使用数据库游标,游标是非常有用的工具,但比使用常规的、面向集的SQL
语句需要更大的开销;按照特定顺序提取数据的查找。 

2、 避免使用不兼容的数据类型。例如float和int、char和varchar、binary和
varbinary是不兼容的。数据类型的不兼容可能使优化器无法执行一些本来可以进
行的优化操作。例如: 
SELECT name FROM employee WHERE salary > 60000 
在这条语句中,如salary字段是money型的,则优化器很难对其进行优化,因为60000
是个整型数。我们应当在编程时将整型转化成为钱币型,而不要等到运行时转化。 

3、 尽量避免在WHERE子句中对字段进行函数或表达式操作,这将导致引擎放弃
使用索引而进行全表扫描。如: 
SELECT * FROM T1 WHERE F1/2=100
应改为: 
SELECT * FROM T1 WHERE F1=100*2

SELECT * FROM RECORD WHERE SUBSTRING(CARD_NO,1,4)=’5378’
应改为:
SELECT * FROM RECORD WHERE CARD_NO LIKE ‘5378%’

SELECT member_number, first_name, last_name FROM members
WHERE DATEDIFF(yy,datofbirth,GETDATE()) > 21
应改为:
SELECT member_number, first_name, last_name FROM members
WHERE dateofbirth 即:任何对列的操作都将导致表扫描,它包括数据库函数、计算表达式等等,查询
时要尽可能将操作移至等号右边。

4、 避免使用!=或<>、IS NULL或IS NOT NULL、IN ,NOT IN等这样的操作符,
因为这会使系统无法使用索引,而只能直接搜索表中的数据。例如: 
SELECT id FROM employee WHERE id != 'B%' 
优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。
 
5、 尽量使用数字型字段,一部分开发人员和数据库管理人员喜欢把包含数值信
息的字段
设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在
处理查询和连接回逐个比较字符串中每一个字符,而对于数字型而言只需要比较一
次就够了。

6、 合理使用EXISTS,NOT EXISTS子句。如下所示:
1.SELECT SUM(T1.C1)FROM T1 WHERE(
(SELECT COUNT(*)FROM T2 WHERE T2.C2=T1.C2>0)
2.SELECT SUM(T1.C1) FROM T1WHERE EXISTS(
  SELECT * FROM T2 WHERE T2.C2=T1.C2)
两者产生相同的结果,但是后者的效率显然要高于前者。因为后者不会产生大量锁
定的表扫描或是索引扫描。
如果你想校验表里是否存在某条纪录,不要用(*)那样效率很低,而且浪费服
务器资源。可以用EXISTS代替。如:
IF (SELECT COUNT(*) FROM table_name WHERE column_name = 'xxx')
可以写成:
IF EXISTS (SELECT * FROM table_name WHERE column_name = 'xxx')

经常需要写一个T_SQL语句比较一个父结果集和子结果集,从而找到是否存在在父
结果集中有而在子结果集中没有的记录,如:
1.SELECT a.hdr_key FROM hdr_tbl a---- tbl a 表示tbl用别名a代替
WHERE NOT EXISTS (SELECT * FROM dtl_tbl b WHERE a.hdr_key = b.hdr_key) 

2.SELECT a.hdr_key FROM hdr_tbl a
LEFT JOIN dtl_tbl b ON a.hdr_key = b.hdr_key WHERE b.hdr_key IS NULL 

3.SELECT hdr_key FROM hdr_tbl
WHERE hdr_key NOT IN (SELECT hdr_key FROM dtl_tbl) 
  三种写法都可以得到同样正确的结果,但是效率依次降低。

7、 尽量避免在索引过的字符数据中,使用非打头字母搜索。这也使得引擎无法
利用索引。  
见如下例子:
SELECT * FROM T1 WHERE NAME LIKE ‘%L%’
SELECT * FROM T1 WHERE SUBSTING(NAME,2,1)=’L’
SELECT * FROM T1 WHERE NAME LIKE ‘L%’
即使NAME字段建有索引,前两个查询依然无法利用索引完成加快操作,引擎不得不
对全表所有数据逐条操作来完成任务。而第三个查询能够使用索引来加快操作。

8、 分利用连接条件,在某种情况下,两个表之间可能不只一个的连接条件,这
时在 WHERE 子句中将连接条件完整的写上,有可能大大提高查询速度。
例:
SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD B WHERE A.CARD_NO = B.CARD_NO 
SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD B WHERE A.CARD_NO = B.CARD_NO
AND A.ACCOUNT_NO=B.ACCOUNT_NO
第二句将比第一句执行快得多。

9、 消除对大型表行数据的顺序存取
  尽管在所有的检查列上都有索引,但某些形式的WHERE子句强迫优化器使用
顺序存取。如:
SELECT * FROM orders WHERE (customer_num=104 AND order_num>1001) OR 
order_num=1008
解决办法可以使用并集来避免顺序存取:
SELECT * FROM orders WHERE customer_num=104 AND order_num>1001 
UNION 
SELECT * FROM orders WHERE order_num=1008 
这样就能利用索引路径处理查询。

10、 避免困难的正规表达式
  LIKE关键字支持通配符匹配,技术上叫正规表达式。但这种匹配特别耗费时
间。例如:SELECT * FROM customer WHERE zipcode LIKE “98_ _ _” 
即使在zipcode字段上建立了索引,在这种情况下也还是采用顺序扫描的方式。如
果把语句改为SELECT * FROM customer WHERE zipcode >“98000”,在执行查询
时就会利用索引来查询,显然会大大提高速度。
11、 使用视图加速查询
把表的一个子集进行排序并创建视图,有时能加速查询。它有助于避免多重排序
操作,而且在其他方面还能简化优化器的工作。例如: 
SELECT cust.name,rcvbles.balance,……other columns 
FROM cust,rcvbles 
WHERE cust.customer_id = rcvlbes.customer_id 
AND rcvblls.balance>0 
AND cust.postcode>“98000” 
ORDER BY cust.name 
如果这个查询要被执行多次而不止一次,可以把所有未付款的客户找出来放在一个
视图中,并按客户的名字进行排序: 
CREATE VIEW DBO.V_CUST_RCVLBES
AS 
SELECT cust.name,rcvbles.balance,……other columns 
FROM cust,rcvbles 
WHERE cust.customer_id = rcvlbes.customer_id 
AND rcvblls.balance>0 
ORDER BY cust.name 

然后以下面的方式在视图中查询: 
SELECT * FROM V_CUST_RCVLBES
WHERE postcode>“98000” 
视图中的行要比主表中的行少,而且物理顺序就是所要求的顺序,减少了磁盘
I/O,所以查询工作量可以得到大幅减少。

12、 能够用BETWEEN的就不要用IN
SELECT * FROM T1 WHERE ID IN (10,11,12,13,14)
改成:
SELECT * FROM T1 WHERE ID BETWEEN 10 AND 14
因为IN会使系统无法使用索引,而只能直接搜索表中的数据。

13、 DISTINCT的就不用GROUP BY
  SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID
  可改为:
  SELECT DISTINCT OrderID FROM Details WHERE UnitPrice > 10
   

14、 部分利用索引
  1.SELECT employeeID, firstname, lastname
FROM names
WHERE dept = 'prod' or city = 'Orlando' or division = 'food'

  2.SELECT employeeID, firstname, lastname FROM names WHERE dept =
'prod'
UNION ALL
SELECT employeeID, firstname, lastname FROM names WHERE city = 'Orlando'
UNION ALL
SELECT employeeID, firstname, lastname FROM names WHERE division =
'food'
如果dept 列建有索引则查询2可以部分利用索引,查询1则不能。

15、 能用UNION ALL就不要用UNION
UNION ALL不执行SELECT DISTINCT函数,这样就会减少很多不必要的资源

16、 不要写一些不做任何事的查询
如:SELECT COL1 FROM T1 WHERE 1=0
  SELECT COL1 FROM T1 WHERE COL1=1 AND COL1=2
这类死码不会返回任何结果集,但是会消耗系统资源。

17、 尽量不要用SELECT INTO语句。
SELECT INTO 语句会导致表锁定,阻止其他用户访问该表。

18、 必要时强制查询优化器使用某个索引
  SELECT * FROM T1 WHERE nextprocess = 1 AND processid IN (8,32,45) 
改成:
SELECT * FROM T1 (INDEX = IX_ProcessID) WHERE nextprocess = 1 AND
processid IN (8,32,45)
则查询优化器将会强行利用索引IX_ProcessID 执行查询。
   
19、 虽然UPDATE、DELETE语句的写法基本固定,但是还是对UPDATE语句给点建
议:
a) 尽量不要修改主键字段。
b) 当修改VARCHAR型字段时,尽量使用相同长度内容的值代替。
c) 尽量最小化对于含有UPDATE触发器的表的UPDATE操作。
d) 避免UPDATE将要复制到其他数据库的列。
e) 避免UPDATE建有很多索引的列。
f) 避免UPDATE在WHERE子句条件中的列。

声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
如何向新的MySQL用户授予权限如何向新的MySQL用户授予权限May 09, 2025 am 12:16 AM

TograntpermissionstonewMySQLusers,followthesesteps:1)AccessMySQLasauserwithsufficientprivileges,2)CreateanewuserwiththeCREATEUSERcommand,3)UsetheGRANTcommandtospecifypermissionslikeSELECT,INSERT,UPDATE,orALLPRIVILEGESonspecificdatabasesortables,and4)

如何在MySQL中添加用户:逐步指南如何在MySQL中添加用户:逐步指南May 09, 2025 am 12:14 AM

toadduserInmysqleffectection andsecrely,theTheSepsps:1)USEtheCreateuserStattoDaneWuser,指定thehostandastrongpassword.2)GrantNectalRevileSaryPrivilegesSustate,usiveleanttatement,AdheringTotheTeprinciplelastPrevilegege.3)

mysql:添加具有复杂权限的新用户mysql:添加具有复杂权限的新用户May 09, 2025 am 12:09 AM

toaddanewuserwithcomplexpermissionsinmysql,loldtheSesteps:1)创建eTheEserWithCreateuser'newuser'newuser'@''localhost'Indedify'pa ssword';。2)GrantreadAccesstoalltablesin'mydatabase'withGrantSelectOnMyDatabase.to'newuser'@'localhost';。3)GrantWriteAccessto'

mysql:字符串数据类型和coltrationsmysql:字符串数据类型和coltrationsMay 09, 2025 am 12:08 AM

MySQL中的字符串数据类型包括CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT,排序规则(Collations)决定了字符串的比较和排序方式。1.CHAR适合固定长度字符串,VARCHAR适合可变长度字符串。2.BINARY和VARBINARY用于二进制数据,BLOB和TEXT用于大对象数据。3.排序规则如utf8mb4_unicode_ci忽略大小写,适合用户名;utf8mb4_bin区分大小写,适合需要精确比较的字段。

MySQL:我应该在Varchars上使用什么长度?MySQL:我应该在Varchars上使用什么长度?May 09, 2025 am 12:06 AM

最佳的MySQLVARCHAR列长度选择应基于数据分析、考虑未来增长、评估性能影响及字符集需求。1)分析数据以确定典型长度;2)预留未来扩展空间;3)注意大长度对性能的影响;4)考虑字符集对存储的影响。通过这些步骤,可以优化数据库的效率和扩展性。

mysql blob:有什么限制吗?mysql blob:有什么限制吗?May 08, 2025 am 12:22 AM

mysqlblobshavelimits:tinyblob(255bytes),blob(65,535 bytes),中间布洛布(16,777,215个比例),andlongblob(4,294,967,967,295 bytes).tousebl观察性:1)考虑performance impactsandSandStorelargeblobsextern; 2)管理backbackupsandreplication carecration; 3)usepathsinst

MySQL:自动化用户创建的最佳工具是什么?MySQL:自动化用户创建的最佳工具是什么?May 08, 2025 am 12:22 AM

自动化在MySQL中创建用户的最佳工具和技术包括:1.MySQLWorkbench,适用于小型到中型环境,易于使用但资源消耗大;2.Ansible,适用于多服务器环境,简单但学习曲线陡峭;3.自定义Python脚本,灵活但需确保脚本安全性;4.Puppet和Chef,适用于大规模环境,复杂但可扩展。选择时需考虑规模、学习曲线和集成需求。

mysql:我可以在斑点内搜索吗?mysql:我可以在斑点内搜索吗?May 08, 2025 am 12:20 AM

是的,YouCansearchInIdeAblobInMysqlusingsPecificteChniques.1)转换theblobtoautf-8StringWithConvertFunctionWithConvertFunctionandSearchusiseLike.2)forCompresseBlyblobs,useuncompresseblobs,useuncompressbeforeconversion.3)acpperformance impperformance imperformance imptactsanddataEccoding.4)

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脱衣机

Video Face Swap

Video Face Swap

使用我们完全免费的人工智能换脸工具轻松在任何视频中换脸!

热工具

EditPlus 中文破解版

EditPlus 中文破解版

体积小,语法高亮,不支持代码提示功能

安全考试浏览器

安全考试浏览器

Safe Exam Browser是一个安全的浏览器环境,用于安全地进行在线考试。该软件将任何计算机变成一个安全的工作站。它控制对任何实用工具的访问,并防止学生使用未经授权的资源。

WebStorm Mac版

WebStorm Mac版

好用的JavaScript开发工具

mPDF

mPDF

mPDF是一个PHP库,可以从UTF-8编码的HTML生成PDF文件。原作者Ian Back编写mPDF以从他的网站上“即时”输出PDF文件,并处理不同的语言。与原始脚本如HTML2FPDF相比,它的速度较慢,并且在使用Unicode字体时生成的文件较大,但支持CSS样式等,并进行了大量增强。支持几乎所有语言,包括RTL(阿拉伯语和希伯来语)和CJK(中日韩)。支持嵌套的块级元素(如P、DIV),

Dreamweaver CS6

Dreamweaver CS6

视觉化网页开发工具