编写高性能的SQL语句注意事项.doc

_编写高性能的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列引用