Mysql与Oracle查询优化（执行计划 ，索引）

Mysql与Oracle多表优化查询学习记录

优化从分析查询执行计划开始

1.数据库执行计划

一：Mysql执行计划

id,select_type,table,paritions,type,possible_keys,key,key_len,ref,rows,filtered,Extra

以上是mysql执行计划输出的字段，主要看type，key与filtered，extra。

执行计划的 select_type

查询的类型，主要是用于区分普通查询、联合查询、子查询等。

• SIMPLE：简单的 select 查询，查询中不包含子查询或者 union
• PRIMARY：查询中包含子部分，最外层查询则被标记为 primary
• SUBQUERY/MATERIALIZED：SUBQUERY 表示在 select 或 where 列表中包含了子查询，MATERIALIZED**：**表示 where 后面 in 条件的子查询
• UNION：表示 union 中的第二个或后面的 select 语句
• UNION RESULT：union 的结果

执行计划的 table

该行执行计划对应的表名。

执行计划的 paritions

该列显示的为分区表命中的分区情况。非分区表该字段为空（null）、

执行计划的 type

访问类型，SQL 查询优化中一个很重要的指标，结果值从好到坏依次是：system > const > eq_ref > ref > range > index > ALL。

• system：系统表，少量数据，往往不需要进行磁盘IO
• const：常量连接
• eq_ref：主键索引（primary key）或者非空唯一索引（unique not null）等值扫描
• ref：非主键非唯一索引等值扫描
• range：范围扫描
• index：索引树扫描
• ALL：全表扫描（full table scan）

执行计划的 possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

执行计划的 key

key列显示MySQL实际决定使用的键（索引）

执行计划的key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度不损失精确性的情况下，长度越短越好

执行计划的ref

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

执行计划的rows

表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

执行计划的filtered

表示返回结果的行数占需读取行数的百分比 ，感觉不够正确。

执行计划的Extra

Using filesort：MySQL 对数据使用一个外部的文件内容进行了排序，而不是按照表内的索引进行排序读取。

Using temporary：使用临时表保存中间结果，也就是说 MySQL 在对查询结果排序时使用了临时表，常见于order by 或 group by速度很慢，要使用索引排序。

Using index：表示 SQL 操作中使用了覆盖索引（Covering Index），避免了访问表的数据行，效率高。

Using index condition：表示 SQL 操作命中了索引，但不是所有的列数据都在索引树上，还需要访问实际的行记录。

Using where：表示 SQL 操作使用了 where 过滤条件。

Select tables optimized away：基于索引优化 MIN/MAX 操作或者 MyISAM 存储引擎优化 COUNT(*) 操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即可完成优化。

Using join buffer (Block Nested Loop)：表示 SQL 操作使用了关联查询或者子查询，且需要进行嵌套循环计算。

二：Oracle执行计划

数据库打印执行计划主要的两段数据

Id  | Operation  | Name    | Rows  | Bytes | Cost (%CPU)| Time 
Predicate Information (identified by operation id):

执行计划字段说明

ID: 一个序号，但不是执行的先后顺序。执行的先后根据缩进来判断。

Operation： 当前操作的内容。

Rows： 当前操作的Cardinality，Oracle估计当前操作的返回结果集。

**Cost（CPU）：**Oracle 计算出来的一个数值（代价），用于说明SQL执行的代价。

Time：Oracle 估计当前操作的时间。

Operation操作说明

Full Table Scan (FTS) 全表扫描

Index Lookup 索引扫描

index unique scan --索引唯一扫描

index range scan --索引局部扫描

index full scan --索引全局扫描

index fast full scan --索引快速全局扫描，不带order by情况下常发生

index skip scan --索引跳跃扫描，where条件列是非索引的前导列情况下常发生

Sort Merge Join (SMJ) --由于sort是非常耗资源的，所以这种连接方式要避免

Nested Loops (NL) --比较高效的一种连接方式

Predicate Information谓语说明

​ Access: 表示这个谓词条件的值将会影响数据的访问路劲，决定是否可能使用索引。

​ Filter：表示谓词条件的值不会影响数据的访问路劲，只起过滤的作用。

2.优化

最好是减少业务上的复杂性。

1.Oracle 强制索引

有时候数据库不会使用索引就需要要强制索引。特别是多表关联的关键字段要使用索引。

#只使用一个索引
select /*+INDEX(表名 索引名) */t.id   from   table  t  where t.id=''
#使用多个索引
select /*+INDEX(表名 索引名)INDEX(表名 索引名) */t.id,t.name  from   table  t

2. Mysql 强制索引

有时候数据库不会使用索引就需要要强制索引。特别是多表关联的关键字段要使用索引。

select * from table t   force index(索引名)
left join table c force index(索引名)

3.覆盖索引

select   a.id,sum(b.amt) from  item  a left join itemDetail b on a.id=b.pid 
#这时可以在b表建立  pid,amt 组合索引，数据库就不会做回表操作。加快查询速度。

当查询出来的字段较少时，如利用sum求明细表费用时,就可以在从表中建立一个索引，索引包含amt字段。这样子查询就无需做回表操作。

4.多表关联建立索引

根据关联字段在从表建立索引。

select * from  mainTable m   
left join  followTable  f  on   m.id=f.mid 

在关联的字段上，需要在从表(followTable)的mid建立索引。

5.多表连接中使用straight_join

straight_join类似left join，当两表关联右边表无数据用null填充。

straight_join能够更改表关联顺序，用straight_join能够使主表做为查询的驱动表。复杂查询中mysql将inner join 中比较小的表作为驱动表，而如果使用left join 但条件判断放在where后面，此时看表数据大小，小表就关联顺序就会优先于主表。

6.多表关联子查询

select * from maintable a 
left join   (select  ....,c.id  from tableb b  left join tablec  c)d  on a.id =d.id 

在关联子查询避免关联字段使用子查询从表的字段