mysql中的SQL优化与执行计划_mysql gather1-CSDN博客

一，如何判断SQL的执行效率？

通过explain 关键字分析效率低的SQL执行计划。

比如： explain select sum(moneys) from sales a, company b

where a.company_id = b.company_id and a.year = 2006;

id : 1

select_type: SIMPLE

table:

type:

1.row:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	a	ALL	NULL	NULL	NULL	NULL	1000	Using where

2.row

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	b	ref	ind_company_id	ind_company_id	5	sakila.a.company_id	1	Using where;Using index

select_type: SIMPLE, 简单表，不使用表连接或子查询；PRIMARY，主查询，即外层的查询；UNION，UNION中的第二个查询或后面的查询；SUMQUERY，子查询中的第一个SELECT。

table: 输出结果集的表。

type: 表示表的连接类型，性能由好到坏，依次为，

system，表中只有一行，常量表。

const，单表中最多有一行匹配，如pramary key 或者 unique index

eq_ref，对于前面的每一行，此表中只查询一条记录，比如多表连接中，使用primary key 或 unique index

ref，与eq_ref 类似，区别在于不是primary key 或qunique index, 而是普通索引。

ref_or_null，与ref 类似，区别在于查询中包含对null的查询。

index_merge，索引合并优化

unique_subquery，in的后面是一个查询主键字段的子查询。

index_subquery，与unique_subquery类似，区别在于是查询非唯一索引字段的子查询。

range，单表中的范围查询。

index，对于前面的每一行，都通过查询索引来得到数据。

all，全表扫描。

possible_keys: 表示查询时，可能使用的索引。

key:表示实际使用的索引。

key_len:索引字段的长度。

rows:扫描行的数量。

Extra:执行情况的说明和描述。

二，如何通过查询数据库各操作的比例及索引使用次数来判断数据库索引及使用是否合理？

1，命令： >show status like 'Com_%';

结果：Com_xxx 表示每个xxx语句的执行次数。如：

Com_select, Com_insert,Com_update,Com_delete。

特别的，针对InnoDB：

Innodb_rows_read，select查询返回的行数

Innodb_rows_inserted，执行insert操作插入的行数等等。

通过以上可以查看该数据库的读写比例，以便优化。

2，命令：>show status like 'Handler_read%'

查看索引使用次数。

三，何时匹配到索引？

明确第一索引，第二索引的含义。回表，覆盖索引等优化方法。这里不再赘述。特别的，组合索引只能前缀匹配。同样，like 关键字也只能前缀匹配索引，通配符不能放在第一个字符。

四，何时不走索引？

1，如果mysql 估计索引使用比全表扫描更慢，则不使用索引。例如几乎获取全表数据的范围查询等等。

2，or 分开的条件，OR前的条件列有索引，后面的没有索引，那么涉及的索引都不会用到。

3，条件不是组合索引的第一部分，即不满足前缀左匹配的条件。

4，like 条件以%开始，则不走索引。

5，where 条件后如果是字符串，则一定要用引号括起来，不然自动转换其他类型后，不会走索引。

五，常用SQL优化

1，大批量插入数据，使用多值语句插入。

insert into test values (1,2),(2,3),(2,4)......

2，优化group by, 默认情况下，mysql 会对所有group by C1,C2,C3 ... 的字段排序，与order by C1,C2,C3 类似，所以在group by 中增加相同列的order by 性能没什么影响。

如果用户想避免排序带来的影响，可以显式指定不排序，后面加上order by NULL。

3，order by 后面的顺序与索引顺序相同，且与where 中使用的条件相同，且是索引，则才会走真正索引。

4，in + 子查询的 SQL 尽量用join 连接来代替。

5，OR 之间的每个条件列都必须用到索引。

六，深层一些的优化

考虑每次查询时的IO消耗，回表次数；考虑表设计时，数据结构的不同，比如varchar ,char 区别；考虑表设计时每行数据的大小，尽量保持在128K以内，让其在一页内，避免跨页，大数据行

mysql执行计划介绍

烂sql不仅直接影响sql的响应时间，更影响db的性能，导致其它正常的sql响应时间变长。如何写好sql，学会看执行计划至关重要。下面我简单讲讲mysql的执行计划，只列出了一些常见的情况，希望对大家有所帮助。

测试表结构：

复制代码代码如下:

CREATE TABLE `t1` (
  `c1` int(11) NOT NULL DEFAULT '0',
  `c2` varchar(128) DEFAULT NULL,
  `c3` varchar(64) DEFAULT NULL,
  `c4` int(11) DEFAULT NULL,
  PRIMARY KEY (`c1`),
  KEY `ind_c2` (`c2`),
  KEY `ind_c4` (`c4`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `t2` (

  `c1` int(11) NOT NULL DEFAULT '0',
  `c2` varchar(128) DEFAULT NULL,
  `c3` varchar(64) DEFAULT NULL,
  `c4` int(11) DEFAULT NULL,
  PRIMARY KEY (`c1`),
  KEY `ind_c2` (`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `t3` (
  `c1` int(11) NOT NULL DEFAULT '0',
  `c2` varchar(128) DEFAULT NULL,
  `c3` varchar(64) DEFAULT NULL,
  `c4` int(11) DEFAULT NULL,
  PRIMARY KEY (`c1`),
  KEY `ind_c2` (`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

1.查看mysql执行计划

explain select ......

2.执行计划包含的信息

(1).id

含义，指示select字句或操作表的顺序。

eg1：id相同，执行顺序从上到下，下面的执行计划表示，先操作t1表，然后操作t2表，最后操作t3表。

eg2：若存在子查询，则子查询（内层查询）id大于父查询(外层查询)，先执行子查询。id越大，优先级越高。

(2).select_type

含义：select语句的类型

类型：

a.SIMPLE：查询中不包含子查询或者UNION

b.查询中若包含任何复杂的子部分，最外层查询则被标记为：PRIMARY

c.在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY

d.在FROM列表中包含的子查询被标记为：DERIVED（衍生）

e.若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在 FROM子句的子查询中，

外层SELECT将被标记为：DERIVED

f.从UNION表获取结果的SELECT被标记为：UNION RESULT

eg：

id为1的table显示<derived2>,表示结果来源于衍生表2。

id为2表示子查询，读取t3表

id为3类型为union，是union的第二个select，最先执行；

id为NULL的类型为union result, <union 1,3>表示id为1的操作和id为3的操作进行结果集合并。

执行顺序3->2->1->NULL

(3).type

含义：获取记录行采用的方式，亦即mysql的访问方式。

a.ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

b.index：Full Index Scan，index与ALL区别为index类型只遍历索引，索引一般比记录要小。

因为索引中含有c1，查询c1,c2可以通过索引扫描实现。

c.range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询

备注：range类型肯定是使用了索引扫描，否则type为ALL

d.ref：非唯一性索引扫描，返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找

t2.c4为非唯一索引

e.eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

t2.c1为主键索引，主键索引也是唯一索引

f.const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，

MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system

(4).possible_keys

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

(5).key

含义：显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL

(6)key_len

含义：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度

(7)ref

含义：用于连接查询，表示具体某个表的某列被引用

(8)rows

含义：MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数,这个值是不准确的，只有参考意义。

(9)Extra

含义：显示一些辅助的额外信息

a.Using index,表示使用了索引

b.Using where，表示通过where条件过滤

c.Using temporary，表示使用了临时表，常见于分组和排序

d.Using filesort，表示无法使用索引排序，需要文件排序

eg1:t1.c3列没有索引

eg2：使用索引列t1.c2

使用 EXPLAIN PLAN 获取SQL语句执行计划

SQL查询语句的性能从一定程度上影响整个数据库的性能。很多情况下，数据库性能的低下差不多都是不良SQL语句所引起。而SQL语句的执行
计划则决定了SQL语句将会采用何种方式从数据库提取数据并返回给客户端，本文描述的将是如何通过EXPLAIN PLAN 获取SQL语句执行计划来获
取SQL语句的执行计划。

一、获取SQL语句执行计划的方式
     1. 使用explain plan 将执行计划加载到表plan_table，然后查询该表来获取预估的执行计划
     2. 查询动态性能视图v$sql_plan,v$sql_plan_statistics,v$sql_workarea 等来获取已缓存到库缓存中的真实执行计划
     3. 查询自动工作量资料库(Automatic Workload Repository)或查询Statspack，即从资料库中获取执行计划
     4. 启用执行计划跟踪功能，即autotrace功能
     5. 使用PL/SQL Developer提供的获取执行计划方法
     6. 使用Toad工具来获取执行计划

下面主要讨论使用explain plan获取执行计划的方法

二、explain plan工作实质、前提及操作方法
     1. 工作实质
      将SQL语句预估的执行计划加载到表plan_table,是对表plan_table 执行了DML操作，故不会执行隐式提交
      可以对select,insert,update,merge,delete,create table, create index,alter index等加载执行计划到plan_table

     2. 前提条件
      需要先创建plan_table，创建方法：@?/rdbms/admin/utlxplan
      对当前的SQL语句有执行权限以及对依赖的对象有相应操作的权限

     3. 使用方法：
       explain plan for select * from scott.emp where ename='SCOTT';    --未设置标记位
       explain plan set statement_id='TEST' for select * from scott.emp where ename='SCOTT'  --设置标记位为TEST

三、实战演习
1.环境

[sql] view plain copy

1. scott@ORCL> select * from v$version;

3. BANNER

4. ----------------------------------------------------------------

5. Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

2.创建测试表演示获取执行计划

[sql] view plain copy

1. scott@ORCL> create table t as select * from all_objects where rownum<=1000;

3. Table created.

5. --加载创建表的执行计划(DDL 执行计划)

6. scott@ORCL> explain plan set statement_id='T1' for create table t1 as select * from t;

8. Explained.

10. --使用下面的语句从plan_table 获取执行计划

11. col OPERATION format a25

12. col OPTIONS format a25

13. col OBJECT_NAME format a25

14. SELECT lpad(' ', 2 * (LEVEL - 1)) || operation operation

15. ,options

16. ,object_name

17. ,position pos

18. ,bytes

19. ,cost

20. FROM plan_table

21. START WITH id = 0

22. AND statement_id =upper( '&input_statement_id')

23. CONNECT BY PRIOR id = parent_id;

24.

25. Enter value for input_statement_id: T1

26. old 9: AND statement_id =upper( '&input_statement_id')

27. new 9: AND statement_id =upper( 'T1')

28.

29. OPERATION OPTIONS OBJECT_NAME POS BYTES COST

30. ------------------------- ------------------------- ------------------------- ---------- ---------- ----------

31. CREATE TABLE STATEMENT 8 79000 8

32. LOAD AS SELECT T1 1

33. TABLE ACCESS FULL T 1 79000 5

34.

35. --创建测试表t1并收集统计信息

36. scott@ORCL> create table t1 nologging as select * from t;

37.

38. scott@ORCL> exec dbms_stats.gather_table_stats('SCOTT','T1');

39.

40. --使用explain plan加载创建索引的执行计划

41. scott@ORCL> explain plan set statement_id='IDX' for create index i_t1 on t1(object_id);

42.

43. Explained.

44.

45. scott@ORCL> @Get_Plan

46. Enter value for input_statement_id: IDX

47. old 9: AND statement_id =upper( '&input_statement_id')

48. new 9: AND statement_id =upper( 'IDX')

49.

50. OPERATION OPTIONS OBJECT_NAME POS BYTES COST

51. ------------------------- ------------------------- ------------------------- ---------- ---------- ----------

52. CREATE INDEX STATEMENT 6 4000 6

53. INDEX BUILD NON UNIQUE I_T1 1

54. SORT CREATE INDEX 1 4000

55. TABLE ACCESS FULL T1 1 4000 5

56.

57. scott@ORCL> CREATE INDEX i_t1 ON t1 (object_id);

58.

59. scott@ORCL> delete from plan_table;

3.使用自顶向下的读取方法获取执行计划

[sql] view plain copy

1. --使用explain plan加载重建索引的执行计划

2. scott@ORCL> explain plan set statement_id='A_IDX' for alter index i_t1 rebuild;

4. Explained.

6. --执行下面的语句来获的A_IDX的执行计划，其结果是从上至下来读，从最内侧往最外侧读。

7. SELECT LPAD(' ', 2 * (LEVEL - 1)) || LEVEL || '.' || NVL(POSITION, 0) || ' ' ||

8. OPERATION || ' ' || OPTIONS || ' ' || OBJECT_NAME || ' ' ||

9. OBJECT_TYPE || ' ' ||

10. DECODE(ID, 0, STATEMENT_ID || ' Cost = ' || POSITION) || COST || ' ' ||

11. OBJECT_NODE "Query Plan"

12. FROM PLAN_TABLE

13. START WITH ID = 0

14. AND STATEMENT_ID = UPPER('&input_statement_id')

15. CONNECT BY PRIOR ID = PARENT_ID

16. AND STATEMENT_ID = UPPER('&input_statement_id');

17.

18. Enter value for input_statement_id: A_IDX

19. old 8: AND STATEMENT_ID = UPPER('&input_statement_id')

20. new 8: AND STATEMENT_ID = UPPER('A_IDX')

21. Enter value for input_statement_id: A_IDX

22. old 10: AND STATEMENT_ID = UPPER('&input_statement_id')

23. new 10: AND STATEMENT_ID = UPPER('A_IDX')

24.

25. Query Plan

26. ---------------------------------------------------------------------------------------------

27. 1.2 ALTER INDEX STATEMENT A_IDX Cost = 22

28. 2.1 INDEX BUILD NON UNIQUE I_T1

29. 3.1 SORT CREATE INDEX

30. 4.1 INDEX FAST FULL SCAN I_T1 INDEX 2

31.

32. --使用explain plan加载查询语句的执行计划

33. scott@ORCL> explain plan set statement_id='QUERY' for

34. 2 select ename,dname

35. 3 from emp join dept

36. 4 on emp.deptno=dept.deptno

37. 5 where dept.deptno=30;

38.

39. Explained.

40.

41. scott@ORCL> @Get_Plan2

42.

43. Query Plan

44. --------------------------------------------------------------------------------------------

45. 1.4 SELECT STATEMENT QUERY Cost = 44

46. 2.1 NESTED LOOPS 4

47. 3.1 TABLE ACCESS BY INDEX ROWID DEPT TABLE 1

48. 4.1 INDEX UNIQUE SCAN PK_DEPT INDEX (UNIQUE) 0

49. 3.2 TABLE ACCESS FULL EMP TABLE 3

  上面的例子的读取方法：
       执行4.1的索引唯一扫描
       将4.1的结果集返回给3.1
       执行3.2的全表扫描
       将3.1和3.2步骤的结果集返回给2.1
       执行2.1的嵌套循环
       返回最终结果集
       注意嵌套循环的查询方法
       Oracle 从第一个行源中读取第一行，然后和第二个行源中的所有记录行进行比对，所有匹配的记录放在结果集中，然后Oracle 将读第一
       个行源中的下一行。依次类推，直到第一行源中的所有行处理完毕。
4.使用构建树方式查看执行计划

[sql] view plain copy

1. scott@ORCL> delete from plan_table;

3. --使用explian plan加载SQL查询执行计划

4. scott@ORCL> explain plan set statement_id='QUERY2' for

5. 2 select ename,dname

6. 3 from emp join dept

7. 4 on emp.deptno=dept.deptno

8. 5 where emp.empno=7788;

10. Explained.

11.

12. --使用下面的SQl查询来生成构建树

13. col operation format a30

14. col options format a20

15. col "OBJECT NAME" format a25

16. col order format a10

17. col opt format a15

18. SELECT LPAD(' ', 2 * (LEVEL - 1)) || operation "OPERATION",

19. options "OPTIONS",

20. DECODE(TO_CHAR(id),

21. '0',

22. 'COST = ' || NVL(TO_CHAR(position), 'n/a'),

23. object_name) "OBJECT NAME",

24. id || '-' || NVL(parent_id, 0) || '-' || NVL(position, 0) "ORDER",

25. SUBSTR(optimizer, 1, 6) "OPT"

26. FROM plan_table

27. START WITH id = 0

28. AND statement_id = UPPER('&input_statement_id')

29. CONNECT BY PRIOR id = parent_id

30. AND statement_id = UPPER('&input_statement_id');

31.

32. OPERATION OPTIONS OBJECT NAME ORDER OPT

33. ------------------------------ -------------------- ------------------------- ---------- ---------------

34. SELECT STATEMENT COST = 2 0-0-2 ALL_RO

35. NESTED LOOPS 1-0-1

36. TABLE ACCESS BY INDEX ROWID EMP 2-1-1 ANALYZ

37. INDEX UNIQUE SCAN PK_EMP 3-2-1 ANALYZ

38. TABLE ACCESS BY INDEX ROWID DEPT 4-1-2 ANALYZ

39. INDEX UNIQUE SCAN PK_DEPT 5-4-1 ANALYZ

      查询结果中的order列与opt列
      order
           order列的指名了ID，父ID，以及执行计划中这一步骤的位置。
           ID列标识了这个步骤，但并没有说明执行的顺序
           父ID表明了这个步骤中的父步骤
           位置信息说明了父ID相同的子操作的执行顺序
      opt
           说明当前优化器使用的模式
      分析
           首先会从步骤3开始执行，步骤3通过索引唯一扫描PK_EMP将得到的结果集返回给父步骤2
           步骤2根据上一子步骤3得到的rowid访问表EMP并将结果集返回给父步骤1
           对于步骤2检索到的每一行数据，步骤1会将deptno传递给步骤5
           步骤5根据得到的deptno执行索引唯一扫描并将结果集返回给步骤4
           步骤4根据步骤5得到的rowid 访问表dept，并将结果集返回给父步骤1
           对于步骤3中剩余的行依次按上述方式将所有结果集返回给步骤1
           步骤1将获得的最终结果集返回给步骤0，SQL完成查询

      根据查询返回的结果来构建执行计划树
           从ID为1的列开始，作为根节点
           寻找所有父ID为1的所有子ID，如本例为2和4，将其纳入树中
           分别寻找以2和4为父ID的所有子ID，将其纳入树中
           如此循环直到所有的ID没有父ID

              ---------------
              NESTED LOOP (1)
              ---------------
               -        -
             -           -
            -              -
       ---------        ----------
       EMP (2)           DEPT(4)
       ---------        ----------
           -                -
          -                  -
   ---------              ----------
   PK_EMP(3)              PK_DEPT(5)
   ---------             ----------
5.通过Oracle 自带的SQL语句执行计划
  可以通过Oracle提供的SQl语句来获得当前会话最后一条SQL语句的执行计划

utlxpls.sql -->用于查看串行执行计划
utlxplp.sql -->用于查看并行执行计划

[sql] view plain copy

1. scott@ORCL> @?/rdbms/admin/utlxpls.sql --获得当前session plan_table 最后一条SQL语句的执行计划

3. PLAN_TABLE_OUTPUT

4. --------------------------------------------------------------------------------------------------

5. Plan hash value: 2385808155

7. ----------------------------------------------------------------------------------------

9. ----------------------------------------------------------------------------------------

10. | 0 | SELECT STATEMENT | | 1 | 28 | 2 (0)| 00:00:01 |

11. | 1 | NESTED LOOPS | | 1 | 28 | 2 (0)| 00:00:01 |

12. |* 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 14 | 1 (0)| 00:00:01 |

13. |* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |

14. | 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 56 | 1 (0)| 00:00:01 |

15. |* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |

16. ----------------------------------------------------------------------------------------

17.

18. Predicate Information (identified by operation id):

19. ---------------------------------------------------

20.

21. 2 - filter("EMP"."DEPTNO" IS NOT NULL)

22. 3 - access("EMP"."EMPNO"=7788)

23. 5 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

24.

25. 19 rows selected.

26.

27. --加载并行SQL的执行计划

28. scott@ORCL> explain plan for select /*+ parallel(t4,2) */ * from t4;

29.

30. Explained.

31.

32. scott@ORCL> @?/rdbms/admin/utlxplp.sql

33.

34. PLAN_TABLE_OUTPUT

35. ----------------------------------------------------------------------------------------------------------------

36. Plan hash value: 128826497

37.

38. --------------------------------------------------------------------------------------------------------------

40. --------------------------------------------------------------------------------------------------------------

41. | 0 | SELECT STATEMENT | | 400K| 7817K| 183 (4)| 00:00:03 | | | |

42. | 1 | PX COORDINATOR | | | | | | | | |

43. | 2 | PX SEND QC (RANDOM)| :TQ10000 | 400K| 7817K| 183 (4)| 00:00:03 | Q1,00 | P->S | QC (RAND) |

44. | 3 | PX BLOCK ITERATOR | | 400K| 7817K| 183 (4)| 00:00:03 | Q1,00 | PCWC | |

45. | 4 | TABLE ACCESS FULL| T4 | 400K| 7817K| 183 (4)| 00:00:03 | Q1,00 | PCWP | |

46. --------------------------------------------------------------------------------------------------------------

四、总结：
     1. explain plan并不执行当前的SQL语句，而是根据数据字典中记录的统计信息获取最佳的执行计划并加载到表plan_table。
     2. 由于统计信息，执行环境的变化，explain plan与实际的执行计划可能会有差异。
     3. 对于运行时将较长的SQL语句，不需要等到结果输出即可提前获得该SQL的执行计划,对于生产环境调试情况会减轻数据库负荷。
     4. 注意set statement_id标识符区分大小写。