A-A+

SQL语句优化(mysql)

2015年07月24日 数据库 SQL语句优化(mysql)已关闭评论 阅读 489 次

SQL语句优化

1.     查询的模糊匹配

尽量避免在一个复杂查询里面使用 LIKE '%parm1%',百分号会导致相关列的索引无法使用,最好不要用。

解决办法:

其实只需要对该脚本略做改进,查询速度便会提高近百倍。改进方法如下:

  • 修改前台程序——把查询条件的供应商名称一栏由原来的文本输入改为下拉列表,用户模糊输入供应商名称时,直接在前台就帮忙定位到具体的供应商,这样在调用后台程序时,这列就可以直接用等于来关联了。
  • 直接修改后台——根据输入条件,先查出符合条件的供应商,并把相关记录保存在一个临时表里头,然后再用临时表去做复杂关联。
  •      索引问题

在做性能跟踪分析过程中,经常发现有不少后台程序的性能问题是因为缺少合适索引造成的,有些表甚至一个索引都没有。这种情况往往都是因为在设计表时,没去定义索引,而开发初期,由于表记录很少,索引创建与否,可能对性能没啥影响,开发人员因此也未多加重视。然一旦程序发布到生产环境,随着时间的推移,表记录越来越多,这时缺少索引,对性能的影响便会越来越大了。这个问题需要数据库设计人员和开发人员共同关注。

法则:不要在建立的索引的数据列上进行下列操作:

  • 避免对索引字段进行计算操作。
  • 避免在索引字段上使用not,<>,!=。
  • 避免在索引列上使用ISNULL和IS NOT NULL。
  • 避免在索引列上出现数据类型转换。
  • 避免在索引字段上使用函数。
  • 避免建立索引的列中使用空值。
  • 避免在索引上使用OR,可用Union all替代。
  •      在可以使用UNION ALL的语句里,使用了UNION

UNION 因为会将各查询子集的记录做比较,故比起UNION ALL ,通常速度都会慢上许多。一般来说,如果使用UNION ALL能满足要求的话,务必使用UNION ALL。还有一种情况大家可能会忽略掉,就是虽然要求几个子集的并集需要过滤掉重复记录,但由于脚本的特殊性,不可能存在重复记录,这时便应该使用UNION ALL,如xx模块的某个查询程序就曾经存在这种情况,见,由于语句的特殊性,在这个脚本中几个子集的记录绝对不可能重复,故可以改用UNION ALL)。

4.     在WHERE 语句中,尽量避免对索引字段进行计算操作

这个常识相信绝大部分开发人员都应该知道,但仍有不少人这么使用,我想其中一个最主要的原因可能是为了编写写简单而损害了性能,那就不可取了。

例如:where  mycount/3>12*3

应修改为:wheremycount>108

5.     对Where 语句的法则

  • 避免在WHERE子句中使用in,not  in,or 或者having。

可以使用not exist代替not in,可以使用join代替 exist和in。Having可以用where代替,如果无法代替可以分两步处理。

例子

SELECT A1,A2,A3…  FROM ORDERS WHERE CUSTOMER_NAME NOT IN

(SELECT CUSTOMER_NAME FROM CUSTOMER)

优化

SELECT A1,A2,A3…  FROM ORDERS WHERE CUSTOMER_NAME not exist

(SELECT CUSTOMER_NAME FROM CUSTOMER)

  • 不要以字符格式声明数字,要以数字格式声明字符值。(日期同样)否则会使索引无效,产生全表扫描。

例子使用:

SELECT emp.ename, emp.job FROM emp WHERE emp.empno = 7369;

不要使用:

SELECT emp.ename, emp.job FROM emp WHERE emp.empno =’7369’

  • Sql查询条件排序

在where条件中,条件排序规则:

  1. 若存在主键,主键应该排在where关键字后的第一个条件
  2. 若无主键但存在索引,索引字段应该排在where关键字后的第一个条件
  3. 多个查询条件时,将排除最多数据的条件放在where字段最前面,后面多个条件依次以每次排除数据的多少进行降序排列

另外,不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

6.     对Select语句的法则

在应用程序、包和过程中限制使用select * from table这种方式。看下面例子:

使用SELECTempno,ename,category FROM emp WHERE empno = 7369

而不要使用SELECT *FROM emp WHERE empno ='7369

另外,select关键字后面的列字段排序,最好与数据表中列的顺序相同,这样能一定效果的提高效率,虽然不明显,但还是建议按顺序排列。

若查询结果中有列数据在业务中是不需要的,请尽量不要写到select显示字段中。

7.     排序

避免使用耗费资源的操作,带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎 执行,耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序。

8.     求行数

避免使用count(*)获得表的记录数

例:

SELECT COUNT(*) FROM OrderInfo

可用以下语句替代

Sql Server:SELECT [rows] FROM sysindexes WHERE id = OBJECT_ID('dbo.Orderinfo')AND indid < 2

Mysql:SELECT COUNT(1) FROM OrderInfo;

9.     Join

Join时,当使用Left Join或Right Join时,将数据量大的表作为主表,如:

表A数据300万,表B数据150万,此时使用方式为:

A  left join B on A.id=B.id或B  left join A on B.id=A.id

禁止使用A,B  where A.id=B.id,这种方式会产生笛卡尔积,数据库内存中会产生巨量数据

10.             Between

如 mycountBetween 200 at 300,改为:mycount>200 and mycount<300,此处调整需看前者条件过滤数据多还是后者数据过滤多,可以进行适当的优化。

11.             IN

数据库中IN的效率根据不同的数据库类型存在一定的差异,在Mysql中效率很低,但在SQL Server中有了很大的改善,在Mysql中使用IN时,可以适当的考虑查询多次或使用Union ALL或Exisits

12.             视图、存储过程

使用视图或存储过程能一定程度的提高sql性能,因为每个单独的sql在执行时,会检查sql语法准确性,是否存在表名、列名等。若用视图或存储过程则不会检查sql语法。

13.             Mysql Limit优化

使用limit时,若使用limitm,n格式时,当m值越大时,效率会越来越低,如该表存在ID或排序索引情况是,可以在where语句中条件条件用于优化,如下所示:

Select id,A1,A2,A3… from talbeA limit 100,200;

优化后:

Select id,A1,A2,A3… from talbeA where id>120000 limit 100;

14.             MySql组合索引或主键

在Mysql中,运行多列组合成联合主键、唯一索引、索引,若当前我们的联合索引组成字段和顺序为A,B,C三列,此时使用A,A,B,A,B,C作为组合查询条件是能用到索引的,使用A,C,B,B,C是无法使用到索引的,所以请注意查询条件中的查询条件顺序。

数据表创建建议

1.     创建数据表

数据表列数不宜过多,超过一定数量后就可以做拆表工作,具体要根据业务情况,将变更较小、和查询条件不适用的列单独出数据表中,使用逻辑外键关联。数据表创建主要是要避免一条数据超过一个数据库页的情况,表中列越少,数据查询过程中跨数据库页的次数也就越少,可以提高数据库的查询效率。印象中一个表好像超过30多列就建议拆表,创建附属表。

建表时,能使用tinnyint就不要用int,尽量让数据表的列小,不要来就用很大的数据类型。

若要在数据表中保存文本、图片等大文件信息,可以将文本、图片存在一个服务器上,数据库中记录该文件、图片所在位置即可。

2.     索引创建

创建索引时,尽量不要再存在null的字段上创建索引,另外若该列数据只有指定的几个值,如状态列,则不需要创建索引,因为这种索引创建后没有实际的意义。(可以适当讲下索引的本质)

3.     编码和数据库引擎

Mysql数据存在数据库引擎,若是公司业务表,一般都用innodb,该种引擎存在事务,对事务有很好的支持。若该表至少存数据,数据记录后用于分析,该表最好使用myisam引擎。

关于Mysql数据库编码,大多情况使用utf-8,中文数据很多的情况下建议使用gb2312。Mysql存在多种编码:

数据库服务器编码:安装Mysql器后可设置,可以通过mysql客户端、配置文件设置

数据库编码:创建数据库时设置,可随时配置

数据表编码:创建数据表时设置,可随时配置

字段编码:创建数据字段时设置,可随时配置

编码的影响关系:字段编码>数据表编码>数据库编码>数据库服务器编码

程序连接数据库读写数据时,需要指定连接数据库的编码方式,建议连接数据库时的编码方式和数据库编码、数据表编码、字段编码统一,避免出现乱码的情况。

标签:

评论已关闭!

Copyright © 极品飞鸽 保留所有权利.   Theme  Ality 蜀ICP备14015766号-1

用户登录

分享到: