探讨MySQL性能提升策略，SQL语句优化技巧及索引添加的最佳实践

如何优化SQL语句，如何添加索引 1、主键即是聚集索引2、只要建立索引就能显著提升查询速度3、将所有需要提升查询速度的字段都纳入聚集索引，以增强查询速度（四）其他书中未提及的索引使用经验总结1、使用聚合索引比使用非聚合索引的主键查询速度快2、使用聚合索引比使用一般的主键进行排序时速度快，尤其是在小数据量情况下3、在聚合索引内搜索的时间将按数据占整个数据表的百分比成比例减少，而与聚合索引使用了多少个无关4、日期列不会因为包含分秒而降低查询速度（五）其他注意事项1.不要对常用的小型表建立索引2.不要将社会保障号码(SSN)或身份证号码(ID)选作键3.不要使用用户的键4.不要对memo/notes字段和大型文本字段（许多字符）建立索引5.使用系统生成的主键二、改善SQL语句1、LIKE语句是否属于SARG取决于所使用的通配符的类型2、or会导致全表扫描3、非操作符、函数引起的不满足SARG形式的语句4、IN的作用相当于OR5、尽量少用NOT6、exists和in的执行效率相同7、使用函数charindex()和前面加通配符%的LIKE执行效率相同8、union并不绝对比or的执行效率高9、字段提取要按照“需多少、提多少”的原则，避免“select”10、count()不比count(字段)慢11、orderby按聚集索引列排序效率最高12、高效的TOP ORACLE优化SQL语句，提升效率(2) 索引是表的一个概念部分，用于提高检索数据的效率。Oracle使用了一个复杂的自平衡B tree结构，通常通过索引查询数据比全表扫描要快。当Oracle找出执行查询和Update语句的最佳路径时，Oracle优化器将使用索引。同样，在联结多个表时使用索引也能够提升效率。另一个使用索引的好处是它提供了主键(primary key)的唯一性验证。对于LONG或LONG RAW数据类型，您能够索引几乎任何的列。通常在大型表中使用索引特别有效。当然，您也会发现在扫描小表时使用索引同样能提升效率。尽管使用索引能得到查询效率的提升，但我们必须注意到它的代价。索引需要空间来存储，也需要定期维护。每当有记录在表中增减或索引列被修改时，索引本身也会被修改。这意味着每条记录的INSERT、DELETE、UPDATE将为此多付出额外的磁盘I/O。因为索引需要额外的存储空间和处理那些不必要的索引反而会使查询反应时间变慢。定期的重构索引是有必要的。 ALTER INDEX REBUILD （）用EXISTS替换DISTINCT 当提交一个包含一对多表信息（如部门表和雇员表）的查询时，避免在SELECT子句中使用DISTINCT。一般能够考虑用EXISTS替换DISTINCT，使查询更为迅速，因为RDBMS核心模块将在子查询的条件一旦满足后立即返回结果。例子 （低效）SELECT DISTINCT DEPT_NO, DEPT_NAME FROM DEPT D, EMP E WHERE D.DEPT_NO = E.DEPT_NO （高效）SELECT DEPT_NO, DEPT_NAME FROM DEPT D WHERE EXISTS (SELECT X FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO); （）SQL语句用大写的，因为Oracle总是先解析SQL语句，把小写的字母转换成大写的再执行 （）在Java代码中尽量少用连接符+连接字符串 （）避免在索引列上使用NOT。通常我们要避免在索引列上使用NOT，NOT会产生在和在索引列上使用函数相同的影响。当Oracle碰到NOT时，它会停止使用索引，转而执行全表扫描 （）避免在索引列上使用计算。WHERE子句中假如索引列是函数的一部分，优化器将不使用索引而使用全表扫描 举例： 低效 SELECT… FROM DEPT WHERE SAL>; 高效: SELECT… FROM DEPT WHERE SAL>/; （）用>=替代> 高效 SELECT FROM EMP WHERE DEPTNO>= 低效: SELECT* FROM EMP WHERE DEPTNO> 两者的区别在于前者DBMS将直接跳到第一个DEPT等于的记录，而后者将首先定位到DEPTNO=的记录并且向前扫描到第一个DEPT大于的记录 （）用UNION替换OR（适用于索引列） 通常情况下，用UNION替换WHERE子句中的OR将会起到较好的效果。对索引列使用OR将造成全表扫描。注意以上规则只针对多个索引列有效。假如有column没有被索引，查询效率可能会因为您没有选择OR而降低。在下面的例子中，LOC_ID和REGION上都建有索引。 高效 SELECT LOC_ID, LOC_DESC, REGION FROM LOCATION WHERE LOC_ID= UNION SELECT LOC_ID, LOC_DESC, REGION FROM LOCATION WHERE REGION= MELBOURNE 低效: SELECT LOC_ID, LOC_DESC, REGION FROM LOCATION WHERE LOC_ID= OR REGION= MELBOURNE （）用IN来替换OR 这是一条简单易记的规则，但是实际的执行效果还须检验。在Oracle i下，两者的执行路径似乎是相同的： 低效: SELECT… FROM LOCATION WHERE LOC_ID= OR LOC_ID= OR LOC_ID= 高效 SELECT… FROM LOCATION WHERE LOC_IN IN(); （）避免在索引列上使用IS NULL和IS NOT NULL 避免在索引中使用任何可能为空的列。Oracle将无法使用该索引。对于单列索引，假如列包含空值，索引中将不存在此记录。对于复合索引，假如每个列都为空，索引中同样不存在此记录。假如至少有一个列不为空，则记录存在于索引中。举例，假如唯一性索引建立在表的A列和B列上，并且表中存在一条记录的A、B值为( null)，Oracle将不接受下一条具备相同A、B值（ null）的记录（插入）。然而，假如任何的索引列都为空，Oracle将认为整个键值为空，而空不等于空。因此，您能够插入条具备相同键值的记录，当然他们都是空！因为空值不存在于索引列中，所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引。

避免在索引中包含任何可能为空的列，否则Oracle将无法有效利用该索引。对于单列索引，若列中包含空值，则索引中不会包含此记录；对于复合索引，若每个列都为空，则索引中同样不会包含此记录；只有当至少有一个列不为空时，记录才会存在于索引中。例如，若唯一性索引建立在表的A列和B列上，且表中存在一条记录的A、B值为(空)，Oracle将拒绝下一条具有相同A、B值（空）的记录（插入）。然而，如果所有索引列都为空，Oracle将认为整个键值为空——而空并不等同于空，因此您可以插入多条具有相同键值的记录，当然，它们都是空的！因为空值不包含在索引列中，所以WHERE子句中对索引列进行空值比较将导致Oracle放弃使用该索引。

低效（索引失效）： SELECT… FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;

高效（索引有效）： SELECT… FROM DEPARTMENT WHERE DEPT_CODE>=;

（务必）始终使用索引的第一个列 如果索引建立在多个列上，只有当它的第一个列（主列）被WHERE子句引用时，优化器才会选择使用该索引。这是一条简单而重要的规则。当仅引用索引的第二个列时，优化器会进行全表扫描，而忽略索引。

（尽可能）用UNION ALL替换UNION（如果可行的话） 当SQL语句需要UNION两个查询结果集时，这两个结果**以UNION ALL的方式合并，然后在输出最终结果前进行排序。如果用UNION ALL替代UNION，那么排序就不再是必要的，效率因此得到提升。需要注意的是，UNION ALL将重复输出两个结果集中相同的记录，因此在使用UNION ALL之前，请从业务需求分析其可行性。UNION将对结果集进行排序，这个操作会使用到SORT_AREA_SIZE这块内存，对这块内存的优化也是相当重要的。以下SQL可以用来查询排序的消耗量： lishixinzhi/Article/program/Oracle/201311/16789