MYSQL教程之使你的 SQL 语句完整优化

关于这个理由我把它放在最后一位。在很多业界专家中有一个相当一致的观点：MySQL不能很好的扩展。关于这点可能有很大的分歧，争论的焦点主要集中于水平可扩展性和垂直可扩展性上。MySQL则更倾向于垂直可扩展性。我们要做到不仅会写SQL，还要做到写出功能优秀的SQL语句。
（1）选择最无效率的表名按次(只在基于划定规矩的优化器中无效)：
Oracle的剖析器依照从右到左的按次处置FROM子句中的表名，FROM子句中写在最初的表(基本表drivingtable)将被开始处置，在FROM子句中包括多个表的情形下,你必需选择纪录条数起码的表作为基本表。假如有3个以上的表毗连查询,那就必要选择交织表(intersectiontable)作为基本表,交织表是指谁人被其他表所援用的表。
（2）WHERE子句中的毗连按次：
Oracle接纳自下而上的按次剖析WHERE子句,依据这个道理,表之间的毗连必需写在其他WHERE前提之前,那些能够过滤失落最年夜数目纪录的前提必需写在WHERE子句的开端。
（3）SELECT子句中制止利用‘*’：
Oracle在剖析的过程当中,会将‘*’顺次转换成一切的列名,这个事情是经由过程查询数据字典完成的,这意味着将泯灭更多的工夫。
（4）削减会见数据库的次数：
Oracle在外部实行了很多事情:剖析SQL语句,预算索引的使用率,绑定变量,读数据块等。
（5）在SQL*Plus,SQL*Forms和Pro*C中从头设置ARRAYSIZE参数,能够增添每次数据库会见的检索数据量,倡议值为200。
（6）利用DECODE函数来削减处置工夫：
利用DECODE函数能够制止反复扫描不异纪录或反复毗连不异的表。
（7）整合复杂,有关联的数据库会见：
假如你有几个复杂的数据库查询语句,你能够把它们整合到一个查询中(即便它们之间没有干系)。
（8）删除反复纪录：
最高效的删除反复纪录办法(由于利用了ROWID)例子：
DELETEFROMEMPEWHEREE.ROWID>(SELECTMIN(X.ROWID)FROMEMPXWHEREX.EMP_NO=E.EMP_NO);

（9）用TRUNCATE替换DELETE：
当删除表中的纪录时,在一般情形下,回滚段(rollbacksegments)用来寄存能够被恢复的信息.假如你没有COMMIT事件,ORACLE会将数据恢复到删除之前的形态(正确地说是恢复到实行删除命令之前的情况)而当使用TRUNCATE时,回滚段不再寄存任何可被恢复的信息。当命令运转后,数据不克不及被恢复.因而很少的资本被挪用,实行工夫也会很短。（TRUNCATE只在删除全表合用,TRUNCATE是DDL不是DML）。
（10）只管多利用COMMIT：
只需有大概，在程序中只管多利用COMMIT,如许程序的功能失掉进步,需求也会由于COMMIT所开释的资本而削减，COMMIT所开释的资本:
a.回滚段上用于恢单数据的信息。
b.被程序语句取得的锁。
c.redologbuffer中的空间。
d.Oracle为办理上述3种资本中的外部消费。
（11）用Where子句交换HAVING子句：
制止利用HAVING子句，HAVING只会在检索出一切纪录以后才对了局集举行过滤。这个处置必要排序,总计等操纵。假如能经由过程WHERE子句限定纪录的数量，那就可以削减这方面的开支。(非oracle中)on、where、having这三个都能够加前提的子句中，on是开始实行，where次之，having最初，由于on是先把不切合前提的纪录过滤后才举行统计，它就能够削减两头运算要处置的数据，按理说应当速率是最快的，where也应当比having快点的，由于它过滤数据后才举行sum，在两个表连接时才用on的，以是在一个表的时分，就剩下where跟having对照了。在这单表查询统计的情形下，假如要过滤的前提没有触及到要盘算字段，那它们的了局是一样的，只是where可使用rushmore手艺，而having就不克不及，在速率上后者要慢假如要触及到盘算的字段，就暗示在没盘算之前，这个字段的值是不断定的，依据上篇写的事情流程，where的感化工夫是在盘算之前就完成的，而having就是在盘算后才起感化的，以是在这类情形下，二者的了局会分歧。在多表连接查询时，on比where更夙兴感化。体系起首依据各个表之间的连接前提，把多个表分解一个一时表后，再由where举行过滤，然后再盘算，盘算完后再由having举行过滤。因而可知，要想过滤前提起到准确的感化，起首要分明这个前提应当在甚么时分起感化，然后再决意放在那边。
（12）削减对表的查询：
在含有子查询的SQL语句中,要出格注重削减对表的查询。例子：
SELECTTAB_NAMEFROMTABLESWHERE(TAB_NAME,DB_VER)=(SELECTTAB_NAME,DB_VERFROMTAB_COLUMNSWHEREVERSION=604)

（13）经由过程外部函数进步SQL效力：
庞大的SQL常常就义了实行效力。可以把握下面的使用函数办理成绩的办法在实践事情中长短常成心义的。
（14）利用表的别号(Alias)：
当在SQL语句中毗连多个表时,请利用表的别号并把别号前缀于每一个Column上。如许一来,就能够削减剖析的工夫并削减那些由Column歧义引发的语法毛病。
（15）用EXISTS替换IN、用NOTEXISTS替换NOTIN：
在很多基于基本表的查询中，为了满意一个前提,常常必要对另外一个表举行连接。在这类情形下，利用EXISTS(或NOTEXISTS)一般将进步查询的效力。在子查询中，NOTIN子句将实行一个外部的排序和兼并。不管在哪一种情形下，NOTIN都是最低效的(由于它对子查询中的表实行了一个全表遍历)。为了不利用NOTIN，我们能够把它改写成外毗连(OuterJoins)或NOTEXISTS。
例子：
（高效）SELECT*FROMEMP(基本表)WHEREEMPNO>0ANDEXISTS(SELECT‘XFROMDEPTWHEREDEPT.DEPTNO=EMP.DEPTNOANDLOC=‘MELB)(低效)SELECT*FROMEMP(基本表)WHEREEMPNO>0ANDDEPTNOIN(SELECTDEPTNOFROMDEPTWHERELOC=‘MELB)

（16）辨认‘低效实行’的SQL语句：
固然今朝各类关于SQL优化的图形化工具层见叠出,可是写出本人的SQL工具来办理成绩一直是一个最好的办法：
SELECTEXECUTIONS,DISK_READS,BUFFER_GETS,ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2)Hit_radio,ROUND(DISK_READS/EXECUTIONS,2)Reads_per_run,SQL_TEXTFROMV$SQLAREAWHEREEXECUTIONS>0ANDBUFFER_GETS>0AND(BUFFER_GETS-DISK_READS)/BUFFER_GETS<0.8ORDERBY4DESC;

索引是表的一个观点部分，用来进步检索数据的效力，Oracle利用了一个庞大的自均衡B-tree布局。一般,经由过程索引查询数据比全表扫描要快。当Oracle找出实行查询和Update语句的最好路径时,Oracle优化器将利用索引。一样在联合多个表时利用索引也能够进步效力。另外一个利用索引的优点是,它供应了主键(primarykey)的独一性考证。那些LONG或LONGRAW数据范例,你能够索引几近一切的列。一般,在年夜型表中利用索引出格无效.固然,你也会发明,在扫描小表时，利用索引一样能进步效力。固然利用索引能失掉查询效力的进步,可是我们也必需注重到它的价值。索引必要空间来存储,也必要按期保护,每当有纪录在表中增减或索引列被修正时,索引自己也会被修正。这意味着每笔记录的INSERT，DELETE，UPDATE将为此多支付4、5次的磁盘I/O。由于索引必要分外的存储空间和处置，那些不用要的索引反而会使查询反响工夫变慢。按期的重构索引是有需要的：
ALTERINDEX<INDEXNAME>REBUILD<TABLESPACENAME>

（18）用EXISTS交换DISTINCT：
当提交一个包括一对多表信息(好比部门表和雇员表)的查询时，制止在SELECT子句中利用DISTINCT。一样平常能够思索用EXIST交换，EXISTS使查询更加敏捷，由于RDBMS中心模块将在子查询的前提一旦满意后，立即前往了局。例子：
(低效):SELECTDISTINCTDEPT_NO,DEPT_NAMEFROMDEPTD,EMPEWHERED.DEPT_NO=E.DEPT_NO(高效):SELECTDEPT_NO,DEPT_NAMEFROMDEPTDWHEREEXISTS(SELECT‘XFROMEMPEWHEREE.DEPT_NO=D.DEPT_NO);

（19）SQL语句用年夜写的；由于Oracle老是先剖析SQL语句，把小写的字母转换成年夜写的再实行。
（20）在Java代码中只管罕用毗连符“＋”毗连字符串。
（21）制止在索引列上利用NOT一般，我们要制止在索引列上利用NOT,NOT会发生在和在索引列上利用函数不异的影响。当Oracle“碰到”NOT,他就会中断利用索引转而实行全表扫描。
（22）制止在索引列上利用盘算。WHERE子句中，假如索引列是函数的一部分。优化器将不利用索引而利用全表扫描。
举例:
低效：SELECT…FROMDEPTWHERESAL*12>25000;高效:SELECT…FROMDEPTWHERESAL>25000/12;

（23）用>=替换>：
高效：SELECT*FROMEMPWHEREDEPTNO>=4低效:SELECT*FROMEMPWHEREDEPTNO>3

二者的区分在于，前者DBMS将间接跳到第一个DEPT即是4的纪录尔后者将起首定位到DEPTNO=3的纪录而且向前扫描到第一个DEPT年夜于3的纪录。
（24）用UNION交换OR(合用于索引列)：
一般情形下，用UNION交换WHERE子句中的OR将会起到较好的效果。对索引列利用OR将形成全表扫描。注重，以上划定规矩只针对多个索引列无效。假如有column没有被索引，查询效力大概会由于你没有选择OR而下降。鄙人面的例子中，LOC_ID和REGION上都建有索引。
高效：SELECTLOC_ID。LOC_DESC，REGIONFROMLOCATIONWHERELOC_ID=10UNIONSELECTLOC_ID，LOC_DESC，REGIONFROMLOCATIONWHEREREGION=“MELBOURNE”
低效:SELECTLOC_ID，LOC_DESC，REGIONFROMLOCATIONWHERELOC_ID=10ORREGION=“MELBOURNE”

（25）用IN来交换OR：
这是一条复杂易记的划定规矩，可是实践的实行效果还须查验，在Oracle8i下，二者的实行路径仿佛是不异的:　
低效:
SELECT….FROMLOCATIONWHERELOC_ID=10ORLOC_ID=20ORLOC_ID=30
高效：
SELECT…FROMLOCATIONWHERELOC_ININ(10,20,30);
（26）制止在索引列上利用ISNULL和ISNOTNULL：
制止在索引中利用任何能够为空的列，Oracle将没法利用该索引。关于单列索引，假如列包括空值，索引中将不存在此纪录。关于复合索引，假如每一个列都为空，索引中一样不存在此纪录。假如最少有一个列不为空，则纪录存在于索引中。举例：假如独一性索引创建在表的A列和B列上，而且表中存在一笔记录的A，B值为(123，null)，Oracle将不承受下一条具有不异A，B值（123,null）的纪录(拔出)。但是假如一切的索引列都为空，Oracle将以为全部键值为空而空不即是空。因而你能够拔出1000条具有不异键值的纪录,固然它们都是空!由于空值不存在于索引列中,以是WHERE子句中对索引列举行空值对照将使ORACLE停用该索引。
低效:(索引生效)
SELECT…FROMDEPARTMENTWHEREDEPT_CODEISNOTNULL;
高效：(索引无效)
SELECT…FROMDEPARTMENTWHEREDEPT_CODE>=0;
（27）老是利用索引的第一个列：
假如索引是创建在多个列上，只要在它的第一个列(leadingcolumn)被where子句援用时，优化器才会选择利用该索引。这也是一条复杂而主要的划定规矩，当仅援用索引的第二个列时，优化器利用了全表扫描而疏忽了索引。
（28）用UNION-ALL交换UNION(假如有大概的话)：
当SQL语句必要UNION两个查询了局汇合时，这两个了局汇合会以UNION-ALL的体例被兼并，然后在输入终极了局行进行排序。假如用UNIONALL替换UNION，如许排序就不是需要了。效力就会因而失掉进步。必要注重的是，UNIONALL将反复输入两个了局汇合中不异纪录。因而列位仍是要从营业需求剖析利用UNIONALL的可行性。UNION将对了局汇合排序,这个操纵会利用到SORT_AREA_SIZE这块内存。关于这块内存的优化也是相称主要的。上面的SQL能够用来查询排序的损耗量：
低效：SELECTACCT_NUM,BALANCE_AMTFROMDEBIT_TRANSACTIONSWHERETRAN_DATE=31-DEC-95UNIONSELECTACCT_NUM,BALANCE_AMTFROMDEBIT_TRANSACTIONSWHERETRAN_DATE=31-DEC-95高效:SELECTACCT_NUM,BALANCE_AMTFROMDEBIT_TRANSACTIONSWHERETRAN_DATE=31-DEC-95UNIONALLSELECTACCT_NUM,BALANCE_AMTFROMDEBIT_TRANSACTIONSWHERETRAN_DATE=31-DEC-95

（29）用WHERE替换ORDERBY：
ORDERBY子句只在两种严厉的前提下利用索引。
ORDERBY中一切的列必需包括在不异的索引中并坚持在索引中的分列按次。
ORDERBY中一切的列必需界说为非空。
WHERE子句利用的索引和ORDERBY子句中所利用的索引不克不及并列。
比方:表DEPT包括以以下:
DEPT_CODEPKNOTNULL
DEPT_DESCNOTNULL
DEPT_TYPENULL
低效:(索引不被利用)
SELECTDEPT_CODEFROMDEPTORDERBYDEPT_TYPE
高效:(利用索引)
SELECTDEPT_CODEFROMDEPTWHEREDEPT_TYPE>0
（30）制止改动索引列的范例:
当对照分歧数据范例的数据时，ORACLE主动对列举行复杂的范例转换。假定EMPNO是一个数值范例的索引列：SELECT…FROMEMPWHEREEMPNO=‘123。实践上,经由Oracle范例转换,语句转化为:SELECT…FROMEMPWHEREEMPNO=TO_NUMBER(‘123)。
侥幸的是,范例转换没有产生在索引列上,索引的用处没有被改动。如今,假定EMP_TYPE是一个字符范例的索引列：SELECT…FROMEMPWHEREEMP_TYPE=123。
这个语句被Oracle转换为:SELECT…FROMEMPWHERETO_NUMBER(EMP_TYPE)=123。由于外部产生的范例转换，这个索引将不会被用到!为了不Oracle对你的SQL举行隐式的范例转换，最好把范例转换用显式体现出来。注重当字符和数值对照时，Oracle会优先转换数值范例到字符范例。
（31）必要小心的WHERE子句:
某些SELECT语句中的WHERE子句不利用索引。这里有一些例子：
(1)‘!=将不利用索引。记着,索引只能告知你甚么存在于表中,而不克不及告知你甚么不存在于表中。
(2)‘||是字符毗连函数。就象其他函数那样,停用了索引。
(3)‘+是数学函数。就象其他数学函数那样,停用了索引。
(4)不异的索引列不克不及相互对照,这将会启用全表扫描。
（32）a.假如检索数据量凌驾30%的表中纪录数，利用索引将没有明显的效力进步。
b.在特定情形下，利用索引大概会比全表扫描慢，但这是统一个数目级上的区分。而一般情形下,利用索引比全表扫描要块几倍以致几千倍！
（33）制止利用泯灭资本的操纵：
带有DISTINCT,UNION,MINUS,INTERSECT,ORDERBY的SQL语句会启动SQL引擎实行泯灭资本的排序(SORT)功效。DISTINCT必要一次排序操纵，而其他的最少必要实行两次排序。一般，带有UNION,MINUS,INTERSECT的SQL语句都能够用其他体例重写。假如你的数据库的SORT_AREA_SIZE分配得好。利用UNION,MINUS,INTERSECT也是能够思索的,究竟它们的可读性很强。
（34）优化GROUPBY：
进步GROUPBY语句的效力，能够经由过程将不必要的纪录在GROUPBY之前过滤失落。上面两个查询前往不异了局但第二个分明就快了很多。
低效:SELECTJOB,AVG(SAL)FROMEMPGROUPJOBHAVINGJOB=‘PRESIDENTORJOB=‘MANAGER高效:SELECTJOB,AVG(SAL)FROMEMPWHEREJOB=‘PRESIDENTORJOB=‘MANAGERGROUPJOB

与数据库相关的流程的逐渐标准化，使得解决方案提供商能以更便捷的方式提供服务、部署应用程序、规划容量和管理资源。DBaaS模式还有助于减少数据和数据库的冗余度并提升整体服务质量。

Mirror可以算是SQLServer的Dataguard了。但是能不能被大伙用起来就不知道了。

是要和操作系统进行Socket通讯的场景。否则建议慎重！

其实可以做一下类比，Oracle等数据库产品老早就支持了java编程，而且提供了java池参数作为用户配置接口。但是现在有哪些系统大批使用了java存储过程？！连Oracle自己的应用都不用为什么？！

XML字段类型更好的解决了XML数据的操作。XQuery确实不错，但是个人对其没好感。（CSDN的开发者应该是相当的熟了！）

财务软件要用SQL也只是后台的数据库而已,软件都是成品的,当然多学东西肯定是有好处的..

记得在最开始使用2k的时候就要用到这个功能，可惜2k没有，现在有了作解决方案的朋友会很高兴吧。

我个人认为就是孜孜不懈的学习