编写高性能的SQL语句注意事项.doc_第1页
编写高性能的SQL语句注意事项.doc_第2页
编写高性能的SQL语句注意事项.doc_第3页
编写高性能的SQL语句注意事项.doc_第4页
编写高性能的SQL语句注意事项.doc_第5页
已阅读5页,还剩4页未读 继续免费阅读

下载本文档

版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领

文档简介

_编写高性能的SQL语句注意事项n 前言在应用系统开发初期,由于开发数据库数据比较少,对于查询SQL语句,复杂视图的的编写等体会不出SQL语句各种写法的性能优劣,但是如果将应 用系统提交实际应用后,随着数据库中数据的增加,系统的响应速度就成为目前系统需要解决的最主要的问题之一。系统优化中一个很重要的方面就是SQL语句的 优化。对于海量数据,劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍,可见对于一个系统不是简单地能实现其功能就可,而是要写出高质量SQL语句,提高系统的可用性。在多数情况下,数据库使用索引来更快地遍历表,优化器主要根据定义的索引来提高性能。但是,如 果在SQL语句的where子句中写的SQL代码不合理,就会造成优化器删去索引而使用全表扫描,一般就这种SQL语句就是所谓的劣质SQL语句。在编写 SQL语句时我们应清楚优化器根据何种原则来删除索引,这有助于写出高性能的SQL语句。n 索引有哪些种类?常见的索引有B-TREE索引、位图索引、全文索引。B-TREE索引也称为平衡树索引(Balance Tree),它是一种按字段排好序的树形目录结构,主要用于提升查询性能和唯一约束支持;B-TREE索引包括很多扩展类型,如组合索引、反向索引、函数索引等等;B-TREE索引的内容包括根节点、分支节点、叶子节点。位图索引一般用于数据仓库应用。一个表中可以建多个索引,就如一本字典可以建多个目录一样(按拼音、笔划、部首等等)。一个索引也可以由多个字段组成,称为组合索引,如上图就是一个按部首+笔划的组合目录。n SQL语句中,什么条件会使用索引?当字段上建有索引时,通常以下情况会使用索引:INDEX_COLUMN = ? (或者、=、=)INDEX_COLUMN between ? and ?INDEX_COLUMN IN (?,?,.,?)INDEX_COLUMN like ?|%(后导模糊查询)T1. INDEX_COLUMN=T2. COLUMN1(两个表通过索引字段关联)n SQL语句中,什么条件不会使用索引?查询条件不能使用索引原因INDEX_COLUMN ?INDEX_COLUMN not in (?,?,.,?)不等于操作不能使用索引function(INDEX_COLUMN) = ?INDEX_COLUMN + 1 = ?INDEX_COLUMN | a = ?经过普通运算或函数运算后的索引字段不能使用索引,但是经过函数运算字段的字段要使用可以使用函数索引INDEX_COLUMN like %|?INDEX_COLUMN like %|?|%含前导模糊查询的Like语法不能使用索引INDEX_COLUMN is nullB-TREE索引里不保存字段为NULL值记录,因此IS NULL不能使用索引NUMBER_INDEX_COLUMN=12345CHAR_INDEX_COLUMN=12345在做数值比较时需要将两边的数据转换成同一种数据类型,如果两边数据类型不同时会对字段值隐式转换,相当于加了一层函数处理,所以不能使用索引。a.INDEX_COLUMN=a.COLUMN_1给索引查询的值应是已知数据,不能是未知字段值。注意:有时候我们会使用多个字段的组合索引,如果查询条件中第一个字段不能使用索引,那整个查询也不能使用索引。n 一般在什么字段上建索引?字段类型常见字段名需要建索引的字段主键ID,PK外键PRODUCT_ID,COMPANY_ID,MEMBER_ID,ORDER_ID,TRADE_ID,PAY_ID有对像或身份标识意义字段HASH_CODE,USERNAME,IDCARD_NO,EMAIL,TEL_NO,IM_NO索引慎用字段,需要进行数据分布及使用场景详细评估日期GMT_CREATE,GMT_MODIFIED年月YEAR,MONTH状态标志PRODUCT_STATUS,ORDER_STATUS,IS_DELETE,VIP_FLAG类型ORDER_TYPE,IMAGE_TYPE,GENDER,CURRENCY_TYPE区域COUNTRY,PROVINCE,CITY操作人员CREATOR,AUDITOR数值LEVEL,AMOUNT,SCORE长字符ADDRESS,COMPANY_NAME,SUMMARY,SUBJECT不适合建索引的字段描述备注DESCRIPTION,REMARK,MEMO,DETAIL大字段FILE_CONTENT,EMAIL_CONTENTn 索引使用经验总结如下:1. 尽量避免非操作符的使用在索引列上使用NOT 、 等非操作符,DBMS是不使用索引的,可以将查询语句转换为可以使用索引的查询。如:SELECT * FROM employee WHERE salary3000; 对这个查询,可以改写为不使用: SELECT * FROM employee WHERE salary3000;2. 避免对查询的列的操作任何对列的操作都可能导致全表扫描,这里所谓的操作包括数据库函数、计算表达式等等,查询时要尽可能将操作移至等式的右边,甚至去掉函数。如:SELECT * FROM record WHERE substr(cardno,1,4) = 5378SELECT * FROM record WHERE amount/30 1000由于where子句中对列的任何操作结果都是在SQL运行时逐行计算得到的,因此它不得不进行表扫描,而没有使用该列上面的索引;如果这些结果在查询编译时就能得到,那么就可以被SQL优化器优化,使用索引,避免表扫描,因此将SQL重写如下:SELECT * FROM record WHERE cardno like 5378%SELECT * FROM record WHERE amount 1000*30SELECT * FROM employss WHERE first_name|last_name =Beill Cliton上面这条语句完全可以查询出是否有Bill Cliton这个员工,但是这里需要注意,DBMS优化器对基于last_name创建的索引没有使用。 当采用下面这种SQL语句的编写,可以采用基于last_name创建的索引。 SELECT * FROM employee WHERE first_name =Beill and last_name =Cliton 遇到下面这种情况又如何处理呢?如果一个变量(name)中存放着Bill Cliton这个员工的姓名,对于这种情况我们又如何避免全程遍历,使用索引呢?可以使用一个函数,将变量name中的姓和名分开就可以了,但是有一点需要注意,这个函数是不能作用在索引列上。下面是SQL查询脚本: SELECT * FROM employee WHERE first_name = SUBSTR(&name,1,INSTR(&name, )-1) and last_name = SUBSTR(&name,INSTR(&name, )+1)3. 避免不必要的类型转换需要注意的是,尽最避免潜在的数据类型转换。如将字符型数据与数值型数据比较,会自动将字符进行转换,从而导致全表扫描。如:SELECT name FROM employee WHERE salary 60000 在这条语句中,如salary字段不是整型的,则优化器很难对其进行优化,因为60000是个整型数。4. 增加查询的范围限制增加查询的范围限制,避免全范围的搜索。5.合理使用IN与EXISTSA,B两个表, (1)当只显示一个表的数据如A,关系条件只一个如ID时,使用IN更快: SELECT * FROM A WHERE id IN (SELECT id FROM B) (2)当只显示一个表的数据如A,关系条件不只一个如ID,col1时,使用IN就不方便了,可以使用EXISTS:SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE id = A.id and col1 = A.col1)注意:in与exist not in与not exist 的区别 in与exist的区别:in 是把外表和内表作hash 连接,而exists是对外表作loop循环,每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。如果查询的两个表大小相当,那么用in和exists差别不大。如果两个表中一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in。not in与not exist 的区别:如果查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引;而not extsts 的子查询依然能用到表上的索引。所以无论那个表大,用not exists都比not in要快。6. 尽量去掉尽景去掉,避免全表扫描,如果数据是枚举值,且取值范闱同定,则修改为OR或者INa0 改为 a0 or a0a 改为 a7. 去掉WHERE子句中的IS NULL和IS NOT NULLWHERE字句中的IS NULL和IS NOT NULL将不会使用索引而是进行全表搜索,因此需要通过改变查洵方式,分情况讨论等办法,去掉Where子句巾的IS NULL和IS NOT NULLA IS NOT NULL 改为 A0 或者 A8. 尽量不要使用前导模糊查询由于前导模糊查询(前面有%的like查询)不能利用索引,所以速度会比较慢。9. SELECT子句中避免使用 * 当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用

温馨提示

  • 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
  • 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
  • 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
  • 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
  • 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
  • 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
  • 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。

评论

0/150

提交评论