Mysql、Oracle语句性能分析之索引、内存、性能优化

在应用系统开发初期，由于系统数据量比较少，sql语句的性能能优劣无法体现出来，但系统提交到生产环境后，随着数据库中数据量的不断增加，系统的响应速度会成为最重要的解决问题之一。实现同样的功能，高质量的sql会比劣势的sql语句执行响应时间提升上百倍，接下来在这里猿君主要介绍如何避免索引失效，对内存、性能优化，提升sql执行效率。

一、分析sql执行计划

1.通过explain查看

explain select * from stu;

2.执行计划关键字分析

字段	说明
id	id列数字越大越先执行，如果说数字一样大，那么就从上往下依次执行，id列为null的就表是这是一个结果集，不需要使用它来进行查询
select_type	这一列显示了对应行是简单还是复杂SELECT.取值如下:SIMPLE值意味着查询不包括子查询和UNION。查询有任何复杂的子部分，则最外层标记为PRIMARY
table	输出数据行所在的表的名称
type	type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
possible_keys	查询涉及到的字段上存在索引，则该索引将被列出，但不一定被查询实际使用
key	实际使用到的索引，如果为NULL，则没有使用索引。 查询中如果使用了覆盖索引，则该索引仅出现在key列表中，查询中如果使用了覆盖索引，则该索引仅出现在key列表中
key_len	表示索引中使用的字节数，查询中使用的索引的长度（最大可能长度），并非实际使用长度，理论上长度越短越好
ref	显示索引的那一列被使用了，如果可能，是一个常量const。
rows	根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数
extra	很多额外的信息会在 Extra 字段显示, 常见的有以下几种内容：1、using filesort:表示排序需要消耗额外资源，无法通过索引排序，需优化；2、useingtempary：表示使用到了临时表，建议优化；3、using index：使用到了所用，表示效率还可以；4、using join buffer：使用了链接缓存 ；5、using where:使用了过滤条件

二、索引相关性能优化

1.is null 与 is not null

1.不能用null做索引，任何包含有null值的列都不会包含在索引列中；
2.where 条件后含is null 或is not null 时，语句优化器是不允许使用索引的。

2.联接列

任何使用联接的列，优化器是不允许使用索引的。

1. 反例

select * from employee where first_name ||''|| last_name = 'Beill Cliton';

2. 正例

select * from employee where first_name = 'Beill' and last_name = 'Cliton';

3.带通配符

遵循左前缀原则

1. 反例

select * from employee where last_name like '%Cliton%';
select * from employee where last_name like '%Cliton'

2. 正例

select * from employee where last_name like 'Cliton%'

4.order by

任何在该子句的非索引 项或者有计算表达式都将降低查询速度，解决这个问题 的办法就是重写 order by 子句以使用索引，也可以为所使用的列建立另外一个索引，同时 应绝对避免在 order by 子句中使用表达式

5.not 关键字

NOT 可用来对任何逻辑运算 取反，如果要使用 NOT，则应在取反的短语前面加上括号，并在短语前面加上 NOT 运算符；NOT 运算符包含在另外一个逻辑运算符中，这就是不等于（<>）运算符。

1. 反例

select * from employee where salary <> 3000;

... where status <> 'VALID';

2. 正例

select * from employee where salary < 3000 or salary > 3000;

... where not(status = 'VALID');

6.in 和 exists操作

通过使用 EXISTS，Oracle 系统会首先检查主查询，然后运行子查询直到它找到第一 个匹配项，这就节省了时间。Oralce 系统在执行 IN 子查询时，首先执行子查询，并将获得 的结果列表放在一个加了索引的临时表中。在执行子查询之前，系统先将主查询挂起，待子
查询执行完毕，存放在临时表中以后再执行主查询。这也就是使用 EXISTS 比使用 IN 通常 查询速度快的原因。

7.避免在索引列上做运算操作

1. 反例

select ... from dept where SAL * 12 > 25000;

2.正例

select ... from dept where SAL > 25000/12;

8.索引建在多个列上

如果索引是建立在多个列上，只有在它的第一个列（leading column）被 where 子句 引用时，优化器才会选择使用该索引。
建索引

SQL> create table multiindexusage ( inda number , indb number , descr varchar2(10)); 
Table created. 
SQL> create index multindex on multiindexusage(inda,indb); 
Index created. 
SQL> set autotrace traceonly

1. 低效

select * from multiindexusage where indb = 1;

2. 高效

select * from multiindexusage where inda = 1;

9.避免改变索引列的类型

假设 empno 是一个数值类型的索引列：

select ... from emp where empno = '123';

实际上，经过了 Oracle 类型转换，语句转化为：

select ... from emp where empno = TO_NUMBER('123');

因为内部发生的类型转换，这个索引将不会被用到。为了避免 Oracle 对 sql 进行隐式 的类型转换，最好把类型转换用显示表现出来。注意当字符和数值比较时，Oracle 会优先 转换数值类型到字符类型。

10.需要当心的where子句

某些 select 语句中的 where 子句不使用索引：

1. '!='将不使用索引，索引只能告诉你什么存在于表中，而不能告诉你什么不存在于表中；
2. '||'是字符连接函数，就像其它函数那样，停用了索引；
3. '+'是数学函数，就像其它数学函数那样，停用了索引；
4. 相同的索引列不能互相比较，这将会启动全表扫描；

11.避免在索引列上使用mysql/oracle的内置函数

业务需求：查询最近七天内登陆过的用户(假设loginTime加了索引)

1. 反例

select userId,loginTime from loginuser whereDate_ADD(loginTime,Interval7DAY) >=now();

原因：索引列上使用mysql的内置函数，索引失效
2. 正例

select userId,loginTime from loginuser where loginTime >= Date_ADD(NOW(),INTERVAL - 7DAY);

三、与内存相关的优化

1.union 与union all

UNION 在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进 行排序运算，删除重复的记录再返回结果；所以一般采用UNION ALL 操作符替代 UNION，因为 UNION ALL 操作只是简单的 将两个结果合并后就返回。

1. 反例

select * from A union select * from B;

2. 正例

select * from A union all select * from B;

2.sql书写影响

同一功能同一性能不同写法 sql 的影响
如，一个 sql 在 A 程序员写的为：

select * from employee;

B 程序员写为：

select * from scott.employee;（带表所有者的前缀）

C 程序员写为：

select * from EMPLOYEE; （大写表名）

D 程序员写为：

select * from employee; （中间多了空格）

以上四个 sql 在 Oracle 分析整理之后产生的结果及执行的时间是一样的，但是从 Oracle 共享内存 SGA 的原理，可以得出 Oracle 对每个 sql 都会对其进行一次分析，并且 占用共享内存，如果将 sql 的字符串及格式写得完全相同则 Oracle 只会分析一次，共享内 存也只会留下一次的分析结果，这不仅可以减少分析 sql 的时间，而且也可以减少共享内存 重复的信息，oracle 也可以准确统计 sql 的执行频率。

3.避免使用耗费资源的操作

带有 DISTINCT，UNION，MINUS，INTERSECT，ORDER BY 的 sql 语句回启动 sql 引擎执行耗费资源的排序（SORT）功能。DISTINCT 需要一次排序操作，而其它的至少需 要执行两次排序。通常，带有 UNION，MINUS，INTERSECT 的 sql 语句都可以用其它方
式重写。如果你的数据库的 sort_area_size 调配的好，使用 UNION，MINUS，INTERSECT 也是可以考虑的，毕竟它们的可读性很强。

4.mysql的limit使用

在日常使用分页时会用到limit来实现分页，但是当偏移量特别大的时候，查询效率就变得低下。

1. 反例

selectid，name，age from employee limit 20000，40

2. 正例

//方案一 ：返回上次查询的最大记录(偏移量)
selectid，namefrom employee whereid>20000 limit 40.

//方案二：orderby + 索引
selectid，namefrom employee order by id limit 20000，40

四、其它性能相关的优化

1.删除重复记录

最高效的删除重复记录方法（因使用了 ROWID）例子：

delete from emp E where E.ROWID > (select MIN(X.ROWID) from emp X where X.emp_no = E.emp_no);

2.用 TRUNCATE 替代 DELETE（truncate会清空整个表，且数据无法恢复，谨慎使用）

当删除表中的记录时，在通常情况下，回滚段（rollback segments）用来存放可以被 恢复的信息。如果你没有 COMMIT 事务，Oracle 会将数据恢复到删除之前的状态（准确 地说是恢复到执行删除之前的状态），而当运用 TRUNCATE 时，回滚段不再存放任何可被 恢复的信息。当命令运行后，数据不能被恢复，因此很少的资源被调用，执行时间也会很短 （TRUNCATE 只在清空全表适用，TRUNCATE 是 DDL 而不是 DML）。

3.on、where和having

避免使用 having 子句，having 只会在检索出所有记录之后才对结果集进行过滤，这个 处理需要排序、总计等操作。如果能通过 where 子句限制记录的数目，那就能减少这方面 的开销。sql 语句中 on、where、having 这三个都可以加条件的子句中，on 是最先执行， where 次之，having 最后，因为 on 是先把不符合条件的记录过滤后才进行统计，它就可 以减少中间运算要处理的数据，按理说应该是速度最快的，where 也应该比 having 快点的。

4.使用表的别名（Alias）

当在 sql 语句中连接多个表时，请使用表的别名并把别名前缀于每个 column 上。这样 一来，就可以减少解析的时间并减少那些由 column 歧义引起的语法错误。

5.用 EXISTS 替换 DISTINCT

1. 反例

select DESTINCT dept_no, dept_name from dept D, emp E where D.deptno = E.deptno;

2. 正例

select dept_no, dept_name from dept D where EXISTS (select 'X' from emp E where E.deptno = D.deptno);

6.SQL 语句使用大写

因为 Oracle 总是先解析 sql 语句，把小写的字母转换成大写的再执行。

7.用 >= 替代 >

1. 高效

select * from emp where deptno >= 4;

2. 低效

select * from emp where deptno > 3;

两者的区别在于，前者 DBMS 将直接跳到第一个 deptno 等于 4 的记录，而后者将首 先定位到 deptno=3 的记录并且向前扫描到第一个 deptno 大于 3 的记录。

8.优化 GROUP BY

提高 group by 语句的效率，可以通过将不需要的记录在 group by 之前过滤掉。下面 两个查询返回相同结果，但第二个就明显快了许多。

1. 低效

select job, AVG(SAL) from emp group by job having job = 'PRESIDENT' or job = 'MANAGER';

2. 高效

select job, AVG(SAL) from emp where job = 'PRESIDENT' or job = 'MANAGER' group by job;

9.如果数据量较大，优化你的修改/删除语句。

1. 反例

//一次删除10万或者100万+？
delete from user where id <100000;
//或者采用单一循环操作，效率低，时间漫长
for（User user：list）{
delete from user；
}

2. 正例

//分批进行删除,如每次500
delete user where id<500
delete product where id>=500 and id<1000；
一次性删除太多数据，可能会有lock wait timeout exceed的错误，所以建议分批操作。

五、总结索引失效情况

1. 隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误. 由于表的字段 tu_mdn定义为 varchar2(20),但在查询时把该字段作为 number 类型 以 where 条件传给 Oracle,这样会导致索引失效.

错误的例子：select * from test where tu_mdn=13333333333; 
 正确的例子：select * from test where tu_mdn='13333333333';

2. 对索引列进行运算导致索引失效,我所指的对索引列进行运算包括(+，-，*，/，! 等)。错误的例子：select * from test where id-1=9; 正确的例子：select * from test where id=10;
3. 使用 Oracle 内部函数导致索引失效.对于这样情况应当创建基于函数的索引. 错误的例子：select * from test where round(id)=10; 说明，此时 id 的索引已经不 起作用了正确的例子：首先建立函数索引，create index test_id_fbi_idx on test(round(id)); 然后 select * from test where round(id)=10; 这时函数索引起作用了 4. 以下使用会使索引失效，应避免使用； a. 使用 <> 、not in 、not exist、!=
   b. like “%_” 百分号在前（可采用在建立索引时用 reverse(columnName)这种方法 处理）c. 单独引用复合索引里非第一位置的索引列.应总是使用索引的第一个列，如果索引 是建立在多个列上, 只有在它的第一个 列被 where 子句引用时，优化器才会选择使用该索 引。 d. 字符型字段为数字时在 where 条件里不添加引号. e. 当变量采用的是 times 变量，而表的字段采用的是 date 变量时.或相反情况。
4. 不要将空的变量值直接与比较运算符（符号）比较。 如果变量可能为空，应使用 IS NULL 或 IS NOT NULL 进行比较，或者使用 ISNULL 函数。
5. 不要在 SQL 代码中使用双引号。 因为字符常量使用单引号。如果没有必要限定对象名称，可以使用（非 ANSI SQL 标 准）括号将名称括起来。
6. 将索引所在表空间和数据所在表空间分别设于不同的磁盘 chunk 上，有助于提高索引查 询的效率。
7. Oracle 默认使用的基于代价的 SQL 优化器（CBO）非常依赖于统计信息，一旦统计信 息不正常，会导致数 据库查询时不使用索引或使用错误的索引。 一般来说，Oracle 的自动任务里面会包含更新统计信息的语句，但如果表数据发生了 比较大的变化（超过 20%）,可以考虑立即手动更新统计信息，例如：analyze table abc compute statistics，但注意，更新统计 信息比较耗费系统资源，建议在系统空闲时执行。 9.
8. Oracle 在进行一次查询时，一般对一个表只会使用一个索引。因此，有时候过多的索引可能导致 Oracle 使用错误的索引，降低查询效率。例如某表 有索引 1（Policyno） 和索引 2 （ classcode），如果查询条件为 policyno = ‘ xx ’ and classcode = ‘ xx ’ ，则系统有可能会使用索引 2，相较于使用索引 1，查询效 率明显降低。
9. 优先且尽可能使用分区索引。

获取更多学习资料请扫描下方二维码，关注公众号“Java极速进阶间”