数据库优化

方法步骤

查看SQL执行频率(Mysql|Oracle)

-- Mysql 查看当前session中所有统计参数的值
-- 查看工作时间、执行DML次数、连接次数、SELECT次数等
show [global] status;

-- Oracle
SELECT * FROM V$SQLAREA;
SELECT * FROM V$SQL;
-- 查看AWR报告

定位低效SQL语句(Mysql|Oracle)

• 查询慢查询日志

-- Mysql
-- 使用--log-show-queries启动. sql执行时间超过多久进行记录

-- 查看当前执行sql的情况
show processlist;

-- Oracle 
-- 查看当前正在执行的sql

explain分析执行计划(Mysql|Oracle)

mysql使用

EXPLAIN SELECT * FROM jg_cust JOIN jg_zp ON jg_cust.id = jg_zp.cust;

字段	含义
id	序列号, 执行select子句或者操作表顺序
select_type	表示SELECT类型: SIMPLE(简单表, 不使用表连接或者子查询)、PARMARY(主查询、即外层的查询)、UNION(UNION中第二个或者后面的查询语句)、SUBQUERY(子查询中第一个SELECT)
table	输出的表
partitions
type	类型
possible_keys	可能用到的索引
key	实际用到的索引
key_len	索引长度
ref	引用
rows	扫描的行数
filtered
Extra	执行情况的说明和描述

oracle使用

默认情况下，Oracle会将执行计划插入如到一张名为PLAN_TABLE的表中。可以使用脚本utlexplain.sql来创建自己的计划表。这个脚本位于Oracle软件安装目录的子目录$ORACLE_HMOE/rmdbs/admin/中。

从Oracle 10g开始，Oracle会创建一个全局临时表PLAN_TABLE供所有用户使用，所以通常情况下不需要创建自己的计划表。由于此默认的计划表是一个全局临时表，所以你无法看到其他会话插入的执行计划，你的执行计划也会随着自己会话的结束而自动消失。

EXPLAIN PLAN FOR SELECT * FROM DUAL ;

列名	类型	描述
STATEMENT_ID	VARCHAR2(30)	在EXPLAIN PLAN的SET STATEMENT_ID子句提供的SQL语句的唯一标志符。
PLAN_ID	NUMBER	执行计划的在全局表plan_table中的唯一标识符
TIMESTAMP	DATE	EXPLAN PLAN语句执行的日期和时间
REMARKS	VARCHAR2(80)	注释
OPERATION	VARCHAR2(30)	执行的操作类型。如TABLE ACCESS,SORT或HASH JOIN
OPTIONS	VARCHAR2(225)	操作的附加信息，例如，以TABLE SCAN为例，选项可能是FULL或BY ROWID
OBJECT_NODE	VARCHAR2(128)	如果是分布式查询，这一列表示用于引用对象的数据库链接名称。如果并行查询，它的值可能对应一个临时的结果集。
OBJECT_OWNER	VARCHAR2(30)	对象的名字
OBJECT_NAME	VARCHAR2(30)	对象名称
OBJECT_ALIAS	VARCHAR2(65)	对象的别名
OBJECT_INSTANCE	NUMERIC	对象在SQL语句中的位置
OBJECT_TYPE	VARCHAR2(30)	对象的类型（表，索引等）
OPTIMIZER	VARCHAR2(255)	解释SQL语句时生效的优化器
SEARCH_COLUMNS	NUMBERIC	未使用
ID	NUMERIC	执行计划的ID号
PARENT_ID	NUMERIC	上一个步骤的ID号
DEPTH	NUMERIC	操作的深度
POSITION	NUMERIC	如果两个步骤有相同的父步骤，有更低POSITION值的步骤将被先执行
COST	NUMERIC	优化器估算出来的此操作的相对成本
CARDINALITY	NUMERIC	优化器预期这一步将饭后的记录数
BYTES	NUMERIC	预计这一步将返回的字节数
OTHER_TAG	VARCHAR2(255)	标识OTHER列中的值的类型。
PARTITION_START	VARCHAR2(255)	访问的分区范围的起始分区
PARTITION_STOP	VARCHAR2(255)	访问的分区范围的结束分区
PARTITION_ID	NUMERIC	计算PARTITION_START和PARTITION_STOP列的值对的ID
OTHER	LONG	对于分布式查询，这列可能是包含发往远程数据库的SQL语句的文本。对于并行查询，它比啊是并行从属进程执行的SQL语句。
DISTRIBUTION	VARCHAR2(30)	描述记录是如何从一组并行查询从属进程分配到后续的“消费者”从属进程的。
CPU_COST	NUMERIC	估算出来的操作的CPU成本
IO_COST	NUMERIC	估算出来的的操作的IO成本
TEMP_SPACE	NUMERIC	估算出来的这一步操作所使用的临时存储的空间大小
ACCESS_PREDICATES	VARCHAR2(4000)	SQL语句中，确定如何在当前步骤中提取记录的子句。
FILTER_PREDICATES	VARCHAR2(4000)	SQL语句中确定对见记录进行过滤的子句路，如WHERE子句在非索引列上的条件。
PROJECTION	VARCHAR2(4000)	决定将返回的记录的子句，通常是SELECT后面的字段列表
TIME	NUMBER(20,2)	优化器为这一步执行估算的时间消耗
QBLOCK_NAME	VARCHAR2(30)	查询块的唯一标识符。

#什么影响

全表扫描

1. 表上的索引失效或无法被使用的情形(如对谓词使用函数、计算、NULL值、不等运算符、类型转换)

   select * from t where t.id + 1 = 3

2. 查询条件返回了整个表的大部分数据

   当表上所返回的数据行数接近于表上的30%时，Oracle 倾向于使用全表扫描

   select count(pad) from t where n<=990; -- 表数量为 991 条

3. 使用了并行方式访问表(parallel 强行启用Oracle的多线程处理功能)

   select /*+ parallel(3) */ count(pad) from t where n<=10;

4. 使用full 提示

   select /*+ full(t) */ count(pad) from t where n<=10;

5. 统计信息缺失时使得Oracle认为全表扫描比索引扫描更高效

6. 表上的数据块小于DB_FILE_MULTIBLOCK_READ_COUNT值的情形可能产生全表扫描