Sql查询语句优化心得MySQL优化.docx

张静懿

4页

0次

2025-04-30

免费下载

Sql

查询语句优化心得

MySQL

优化

2010-12-24 14:31

作为一个互联网开发工程师，数据库的知识是必不可少的，要是写几条查询效率很差的

SQL

，当数据库的数据到达一定级别以后，没几个人同时访问你的网站，就能把你的一台

服务器高挂啦！【个人认为：最为简单的测试方法就是把

SQL

语句在命令行下运行，若查

询的语句需要

0.03

秒以上的

SQL

语句都需要优化。】

如下的大多都来自网络：终归起来、都是一些简单

SQL

优化，不敢保证这说法绝对的权威

哦。

phpma

、用程序中，保证在实现功能的基础上，尽量减少对数据库的访问次数；通过搜索参数，

尽量减少对表的访问行数

最小化结果集，从而减轻网络负担；能够分开的操作尽量分开处

理，提高每次的响应速度；在数据窗口使用

SQL

时，尽量把使用的索引放在选择的首列；

算法的结构尽量简单；在查询时，不要过多地使用通配符如

SELECT * FROM T1

语句，要

用到几列就选择几列如：

SELECT COL1,COL2 FROM T1

；在可能的情况下尽量限制尽量

结果集行数如：

SELECT TOP 300 COL1,COL2,COL3 FROM T1,

因为某些情况下用户是不需

要那么多的数据的。不要在应用中使用数据库游标，游标是非常有用的工具，但比使用常

规的、面向集的

SQL

语句需要更大的开销；按照特定顺序提取数据的查找。

、避免使用不兼容的数据类型。例如

float

和

int

、

char

和

varchar

、

binary

和

varbinary

是不

兼容的。数据类型的不兼容可能使优化器无法执行一些本来可以进行的优化操作。例如

SELECT name FROM employee WHERE salary

＞

60000

在这条语句中

如

salary

字段是

money

型的

则优化器很难对其进行优化

因为

60000

是个整

型数。我们应当在编程时将整型转化成为钱币型

而不要等到运行时转化。

phpma.com

、尽量避免在

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 < DATEADD(yy,-21,GETDATE())

即：任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽

可能将操作移至等号右边。

、避免使用

或＜＞、

IS NULL

或

IS NOT NULL

、

，

NOT IN

等这样的操作符

因为这

会使系统无法使用索引

而只能直接搜索表中的数据。例如

: SELECT id FROM employee

WHERE id != "B%"

优化器将无法通过索引来确定将要命中的行数

因此需要搜索该表的所

有行。

、尽量使用数字型字段，一部分开发人员和数据库管理人员喜欢把包含数值信息的字段

设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查

询和连接回逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

、合理使用

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)

两者产生相同的结果，但是后者的效率显然要高于前者。因为后者不会产生大量锁定的表

扫描或是索引扫描。如果你想校验表里是否存在某条纪录，不要用

count(*)

那样效率很低，

而且浪费服务器资源。可以用

EXISTS

代替。如：

IF (SELECT COUNT(*) FROM table_name WHERE column_name = 'xxx')

可以写成：

EXISTS (SELECT * FROM table_name WHERE column_name = 'xxx')

经常需要写一个

T_SQL

语句比较一个父结果集和子结果集，从而找到是否存在在父结果集

中有而在子结果集中没有的记录，如：

1.SELECT a.hdr_key FROM hdr_tbl a---- tbl a

表示

tbl

用别名

代替

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)

三种写法都可以得到同样正确的结果，但是效率依次降低。

、尽量避免在索引过的字符数据中，使用非打头字母搜索。这也使得引擎无法利用索引。

见如下例子：

SELECT * FROM T1 WHERE NAME LIKE ‘%L%’

SELECT * FROM T1 WHERE SUBSTING(NAME,2,1)=’L’

SELECT * FROM T1 WHERE NAME LIKE ‘L%’

即使

NAME

字段建有索引，前两个查询依然无法利用索引完成加快操作，引擎不得不对全

表所有数据逐条操作来完成任务。而第三个查询能够使用索引来加快操作。

51ruan.com

、分利用连接条件，在某种情况下，两个表之间可能不只一个的连接条件，这时在

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

第二句将比第一句执行快得多。

、消除对大型表行数据的顺序存取

Sql

查询语句优化心得

MySQL

优化

2010-12-24 14:31

作为一个互联网开发工程师，数据库的知识是必不可少的，要是写几条查询效率很差的

SQL

，当数据库的数据到达一定级别以后，没几个人同时访问你的网站，就能把你的一台

服务器高挂啦！【个人认为：最为简单的测试方法就是把

SQL

语句在命令行下运行，若查

询的语句需要

0.03

秒以上的

SQL

语句都需要优化。】

如下的大多都来自网络：终归起来、都是一些简单

SQL

优化，不敢保证这说法绝对的权威

哦。

phpma

、用程序中，保证在实现功能的基础上，尽量减少对数据库的访问次数；通过搜索参数，

尽量减少对表的访问行数

最小化结果集，从而减轻网络负担；能够分开的操作尽量分开处

理，提高每次的响应速度；在数据窗口使用

SQL

时，尽量把使用的索引放在选择的首列；

算法的结构尽量简单；在查询时，不要过多地使用通配符如

SELECT * FROM T1

语句，要

用到几列就选择几列如：

SELECT COL1,COL2 FROM T1

；在可能的情况下尽量限制尽量

结果集行数如：

SELECT TOP 300 COL1,COL2,COL3 FROM T1,

因为某些情况下用户是不需

要那么多的数据的。不要在应用中使用数据库游标，游标是非常有用的工具，但比使用常

规的、面向集的

SQL

语句需要更大的开销；按照特定顺序提取数据的查找。

、避免使用不兼容的数据类型。例如

float

和

int

、

char

和

varchar

、

binary

和

varbinary

是不

兼容的。数据类型的不兼容可能使优化器无法执行一些本来可以进行的优化操作。例如

SELECT name FROM employee WHERE salary

＞

60000

在这条语句中

如

salary

字段是

money

型的

则优化器很难对其进行优化

因为

60000

是个整

型数。我们应当在编程时将整型转化成为钱币型

而不要等到运行时转化。

phpma.com

、尽量避免在

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 < DATEADD(yy,-21,GETDATE())

即：任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽

可能将操作移至等号右边。

、避免使用

或＜＞、

IS NULL

或

IS NOT NULL

、

，

NOT IN

等这样的操作符

因为这

会使系统无法使用索引

而只能直接搜索表中的数据。例如

: SELECT id FROM employee

WHERE id != "B%"

优化器将无法通过索引来确定将要命中的行数

因此需要搜索该表的所

有行。

、尽量使用数字型字段，一部分开发人员和数据库管理人员喜欢把包含数值信息的字段

设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查

询和连接回逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

、合理使用

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)

两者产生相同的结果，但是后者的效率显然要高于前者。因为后者不会产生大量锁定的表

扫描或是索引扫描。如果你想校验表里是否存在某条纪录，不要用

count(*)

那样效率很低，

而且浪费服务器资源。可以用

EXISTS

代替。如：

IF (SELECT COUNT(*) FROM table_name WHERE column_name = 'xxx')

可以写成：

EXISTS (SELECT * FROM table_name WHERE column_name = 'xxx')

经常需要写一个

T_SQL

语句比较一个父结果集和子结果集，从而找到是否存在在父结果集

中有而在子结果集中没有的记录，如：

1.SELECT a.hdr_key FROM hdr_tbl a---- tbl a

表示

tbl

用别名

代替

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)

三种写法都可以得到同样正确的结果，但是效率依次降低。

、尽量避免在索引过的字符数据中，使用非打头字母搜索。这也使得引擎无法利用索引。

见如下例子：

SELECT * FROM T1 WHERE NAME LIKE ‘%L%’

SELECT * FROM T1 WHERE SUBSTING(NAME,2,1)=’L’

SELECT * FROM T1 WHERE NAME LIKE ‘L%’

即使

NAME

字段建有索引，前两个查询依然无法利用索引完成加快操作，引擎不得不对全

表所有数据逐条操作来完成任务。而第三个查询能够使用索引来加快操作。

51ruan.com

、分利用连接条件，在某种情况下，两个表之间可能不只一个的连接条件，这时在

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

第二句将比第一句执行快得多。

、消除对大型表行数据的顺序存取

of 4

免费下载

查询优化

关注

评论