mysql之优化器、执行计划、简单优化

mysql之优化器、执行计划、简单优化

2018-12-12 15:11 烟雨楼人 阅读(794) 评论(0) 编辑 收藏

引用连接：

https://blog.csdn.net/DrDanger/article/details/79092808
https://blog.csdn.net/wildpen/article/details/81335777

 

sql语句在sql层的流程：

用户传入sql-----查询缓存（命中缓存可直接返回结果）----解析器（生成sql解析树）----预处理器（可能sql等价改写）-----查询优化器（生成sql执行计划）----查询执行引擎----结果返回给用户。

1.优化器是什么?

优化器是数据库的一个核心子系统，你也可以把他理解为MySQL数据库中的一个核心模块或者一个核心功能模块。

2.优化器的目的:

优化器的目的是按照一定原则来得到她认为的目标SQL在当前情形下最有效的执行路径,优化器的目的是为了得到目标SQL的执行计划.

3.优化器分类:

传统关系型数据库里面的优化器分为CBO和RBO两种。

RBO--- Rule_Based Potimizer 基于规则的优化器:

RBO :RBO所用的判断规则是一组内置的规则，这些规则是硬编码在数据库的编码中的，RBO会根据这些规则去从SQL诸多的路径中来选择一条作为执行计划（比如在RBO里面，有这么一条规则：有索引使用索引。那么所有带有索引的表在任何情况下都会走索引）所以，RBO现在被很多数据库抛弃（oracle默认是CBO，但是仍然保留RBO代码，MySQL只有CBO）

RBO最大问题在于硬编码在数据库里面的一系列固定规则，来决定执行计划。并没有考虑目标SQL中所涉及的对象的实际数量，实际数据的分布情况，这样一旦规则不适用于该SQL，那么很可能选出来的执行计划就不是最优执行计划了。

CBO---Cost_Based Potimizer 基于成本的优化器:

CBO :CBO在会从目标诸多的执行路径中选择一个成本最小的执行路径来作为执行计划。这里的成本他实际代表了MySQL根据相关统计信息计算出来目标SQL对应的步骤的IO，CPU等消耗。也就是意味着数据库里的成本实际上就是对于执行目标SQL所需要IO,CPU等资源的一个估计值。而成本值是根据索引，表，行的统计信息计算出来的。(计算过程比较复杂)

4.Cardinality的解释：基数。

Cardinality是CBO特有的概念，它是指指定集合包含的记录数，说白了就是指定结果集的行数。Cardinality和成本值的估计息息相关，因为MySQL的指定结果集所消耗的io资源可以近似看做随着该结果集的递增而递增。
通过SHOW INDEX结果中的列Cardinality来观察。

对于Cardinality总结：

1. 列值代表的是此列中存储的唯一值的个数（如果此列为primary key 则值为记录的行数）

2. 列值只是个估计值，并不准确。

3. 列值不会自动更新，需要通过Analyze table来更新一张表或者mysqlcheck -Aa来进行更新整个数据库。

4. 列值的大小影响Join时是否选用这个Index的判断。

5.Selectivity:可选择率:

 可选择率也是CBO特有的概念，它是指施加指定条件后返回结果集的记录数占未施加任何谓词条件的原始结果集的记录数的比率(谓词条件，可以理解为where等限定词进行限定)
selectivity(可选择率)=施加指定谓词条件后返回结果集的记录数/未施加指定谓词条件的结果集的记录数，我们可以通过可选择率的估计，来确定一个列上是否需要添加索引，实际上，MySQL的CBO也是通过可选择率来确定是否走索引，值越大在做表连接的时候，就越有机会选择这个索引。

6.执行计划 

1． 查询优化器使用统计信息为SQL选择执行计划。

2． mysql没有数据直方图也无法手工删除统计信息。

3． 在服务器层有查询优化器，却没有保存数据和索引统计信息。统计信息由存储引擎实现，不同的存储引擎会存储不同的统计信息。

4． 统计信息分为索引的统计信息，表的统计信息。

统计信息的收集:mysql> analyze table t1;

Analyze table收集表和索引统计信息，适用于MyISAM和InnoDB。对于INNODB存储引擎，在以下情况下，会重新收集统计信息。

1. 表第一次打开的时候。

2. 表修改的行超过1/16 或者新插入20亿行的时候计算索引的统计信息。

3. 执行show index或者查询information schema下的表。

7.统计信息的查看:

索引统计信息:

Show index from table或information_schema.statistics表

表统计信息:

Show table status like或information_schema.tables表

8.查看执行计划

mysql> explain select * from employees.employees limit 1\G;

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE  查询类型

        table: employees  当前查询被扫描到的表  可能是表的别名

   partitions: NULL         

         type: ALL       表扫描方式

possible_keys: NULL      可能被使用到的索引

          key: NULL      被使用的索引

      key_len: NULL      使用过的索引的长度

          ref: NULL      

         rows: 299246   扫描的数据的行数(examin)

     filtered: 100.00   (返回比例)

        Extra: NULL      额外的说明

id列:一般情况下如果不带子查询,id不增加.如果有子查询的,自查询数字增加,先执行子查询,即数据大的先执行.(从大到小).

select_type列:simple 简单查询(不带子查询)       PRIMARY 子查询最外层查询

SUBQUERY(不在 from后面子查询)     DEPENDENT SUBQUERY

DERIVED(在 from子句中的子查询)      union：在union关键字的后的select语句

union result：采用匿名临时进行检索结果

table列:这列表示正在访问某个表

type:查询类型: 查询效率依次下降.     (system---const---eq_ref---ref---fulltext---ref_of_null----index_merge---unique_subquery---index_subquery---range---index----all)

ALL ----全表扫描    index----按照索引的次序进行全表扫描     range-----索引范围扫描

ref-----索引直接获取单个数据  const----- -通过主键直接返回数据

执行计划列:

id列:用于表示select的列，如果都是简单查询则都是1，如果有子查询，则会进行增加，数据大的先执行。

查看执行计划的规则：（最大最上规则）

1.id相同，执行顺序由上至下

2.id不同，如果是子查询，id序号会递增，id值越大优先级越高，越先被执行。

3.id既有相同的，又有不同的。id如果相同认为是一组，执行顺序由上至下； 在所有组中，id值越大优先级越高，越先执行。

possible_keys列:可能会使用那些索引

key列:使用到的索引

Key_len列:索引长度列.可根据索引的使用长度来判断复合索引的使用情况.

Ref列:索引中查找到的值所用的列或者常量

rows列:MySQL需要扫描的行数

table列:这列表示正在访问某个表.

Select type 列的值有:

   1.PRIMARY:查询中包含任何复杂的子部分，最外层的查询

  2.SUBQUERY：SELECT或WHERE中包含的子查询部分（不是from后的子查询).

  3.DERIVED：在FROM中包含的子查询被标记为DERIVER(衍生)， MySQL会递归执行这些子查询，把结果放到临时表中。

  4.UNION：若第二个SELECT出现UNION，则被标记为UNION, 若UNION包含在FROM子句的子查询中，外层子查询将被标记为DERIVED。

  5.UNION RESULT：从UNION表获取结果的SELECT

  6.simple：简单查询，不包括子查询和union.

type列:查询类型：（12种）

system---const---eq_ref---ref---fulltext---ref_of_null----index_merge---unique_subquery---index_subquery---range---index----all        

说明：按照此顺序查询效率依次下降。  （subquery:子查询）

Extra列的值:

  1.using filesort需要使用额外的排序得到结果(进行内存排序或者硬盘排序)

  2.using index 优化器只需要使用索引就可以返回结果（覆盖索引）

  3.using index condition 优化器使用index condition pushdown优化

  4.using join buffer 优化器需要在使用join buffer 

  5.using mrr 优化器使用mrr优化

  6.using tpporary 优化器需要使用临时表

  7.using where 优化器使用where过滤

 

 9.type列字段的详细说明：

一般来说，得保证查询至少达到range级别，最好能达到ref。

system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现

const：如果通过索引依次就找到了，const用于比较主键索引或者unique索引。 因为只能匹配一行数据，所以很快。如果将主键置于where列表中，MySQL就能将该查询转换为一个常量

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

ref：非唯一性索引扫描，索引直接获取单个数据，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配 某个单独值的行，然而它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体

range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在你的where语句中出现between、<</font>、>、in等的查询，这种范围扫描索引比全表扫描要好，因为只需要开始于缩印的某一点，而结束于另一点，不用扫描全部索引

index：Full Index Scan ，index与ALL的区别为index类型只遍历索引树，这通常比ALL快，因为索引文件通常比数据文件小。 （也就是说虽然ALL和index都是读全表， 但index是从索引中读取的，而ALL是从硬盘读取的）

all：Full Table Scan，遍历全表获得匹配的行

 

10.Extra列值详解

 1、Using filesort： 说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”

2、Using temporary：  使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by

3、Using index： 表示相应的SELECT操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。 如果同时出现using where，表明索引被用来执行索引键值的查找； 如果没有同时出现using where，表明索引用来读取数据而非执行查找动作 覆盖索引（Covering Index）： 理解方式1：SELECT的数据列只需要从索引中就能读取到，不需要读取数据行，MySQL可以利用索引返回SELECT列表中 的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖 理解方式2：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此他不必读取整个行。 毕竟索引叶子节点存储了他们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了，一个索引 包含了（覆盖）满足查询结果的数据就叫做覆盖索引 注意： 如果要使用覆盖索引，一定要注意SELECT列表中只取出需要的列，不可SELECT *, 因为如果所有字段一起做索引会导致索引文件过大查询性能下降

6、impossible where： WHERE子句的值总是false，不能用来获取任何元组

7、select tables optimized away： 在没有GROUP BY子句的情况下基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作， 不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化

8、distinct： 优化distinct操作，在找到第一匹配的元祖后即停止找同样值的操作

 

当order by 和 group by无法使用索引时，增大max_length_for_sort_data参数设置和增大sort_buffer_size参数的设置。

 

11.优化示例

 1.更改INSERT INTO为 INSERT DELAYED INTO

更改INSERT INTO为 INSERT DELAYED INTO，是客户端提交数据给MySQL，MySQL返回OK状态给客户端。而这是并不是已经将数据插入表，而是存储在内存里面等待排队。当mysql有空余时，再插入。

这样的好处是，提高插入的速度，客户端不需要等待太长时间。坏处是，不能返回自动递增的ID，以及系统崩溃时，MySQL还没有来得及插入数据的话，这些数据将会丢失。

通过phpMyAdmin观测MySQL的进程，提交后，会有一些用户为DELAYED，状态为Waiting for INSERT的进程。过一会，数据完全插入后就消失了。

2.优化group by语句

由group by id 改为 group by id order by null

默认情况下，MySQL 对所有 GROUP BY col1，col2….的字段进行排序。这与在查询中指定 ORDER BY col1，col2…类似。因此，如果显式包括一个包含相同的列的 ORDER BY 子句，则对 MySQL 的实际执行性能没有什么影响。 如果查询包括 GROUP BY 但用户想要避免排序结果的消耗，则可以指定 ORDER BY NULL禁止排序。

3.优化order by 语句

MySQL 可以使用一个索引来满足 ORDER BY 子句，而不需要额外的排序。 WHERE 条件和 ORDER BY 使用相同的索引，并且 ORDER BY 的顺序和索引顺序相同，并且 ORDER BY 的字段都是升序或者都是降序。

以下情况不使用索引：

1.order by 字段混合desc和asc.  例如：order by t1 desc, t2 asc；

2.用于查询行的关键字与order by中使用的不同。例如：...where t1=a order by t2;

3.对不同的关键字使用order by. 例如：order by t1,t2;

4.优化嵌套查询

使用子查询消除方法。（将子查询改为join连接）

Select * from t1 where id not in (select id from t2)改为：

Select * from t1 left join t2 on t1.id=t2.id where t2.id is null;

5.优化or条件

对于含有 OR 的查询子句，如果要利用索引，则 OR 之间的每个条件列都必须用到索引; 如果没有索引，则应该考虑增加索引。  

6.一些sql提示

1.use index  ---指定索引

在查询语句中表名的后面，添加 USE INDEX 来提供希望 MySQL 去参考的索引列表，就可 以让 MySQL 不再考虑其他可用的索引。 

Select * from lbghaha where d=’e’;

mysql> select * from lbghaha use index(ind_haha) where d='e'; ---指定索引执行

2.IGNORE INDEX     --忽略索引

如果用户只是单纯地想让 MySQL 忽略一个或者多个索引，则可以使用 IGNORE INDEX 作为 HINT（暗示）。

mysql> select * from lbghaha ignore index(ind_haha) where d='e';

3.FORCE INDEX

为强制 MySQL 使用一个特定的索引，可在查询中使用 FORCE INDEX 作为 HINT（暗示）。例如， 当不强制使用索引的时候，因为 id 的值都是大于 0 的，因此 MySQL 会默认进行全表扫描， 而不使用索引。

mysql> select * from lbghaha force index(ind_haha) where d='e';

 12.大数据量数据入库应该如何优化

对于数据库层面如何优化:

优化参数:

1.重建索引

2.trx_commit sync_bin sql_log_bin 和IO有关参数

3.批量提交SQL ,不要 insert commit; insert commit; 而是 for insert(10000) commit;

优化硬件:

OLTP环境,可以购买更好的硬盘来支撑业务,更好的网卡来支撑流量

 

业务优化

1.多线程处理,使用多线程同时读写数据

2.设置队列,做大量数据的缓冲工作(队列  内存缓存(redis memcache))

 

架构优化

1.使用一些mysql的特定的存储引擎来支撑大压力的数据入库

2.或者不用mysql,用一些nosql或者hadoop来支撑

 

大批量数据入库方法：

对于 MyISAM 存储引擎的表：

当用 load 命令导入数据的时候，适当的设置可以提高导入的速度。 （对innodb无效）

Alter table lbg disable keys;      ---取消索引

Loading the data               ---导入数据

Alter table lbg enable keys;       --索引生效

Innodb储存引擎导入大批量数据快速的方法：

(1)因为 InnoDB 类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺 序排列，可以有效地提高导入数据的效率。

(2)在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行 SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。

(3)如果应用使用自动提交的方式，建议在导入前执行 SET AUTOCOMMIT=0，关闭自

动提交，导入结束后再执行 SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。

 

参考网址见:https://www.cnblogs.com/lbg-database/p/10108513.html