菜单

MySQL查询优化,MySQL优化

2018年11月16日 - MySQL

MySQL 查询优化的 Block Nested-Loop 与 Batched Key Access Joins

在MySQL中,可以动用批量密钥访问(BKA)连接算法,该算法使用对连接表的目访问和连续缓冲区。

BKA算法支持:内接连,外接连和半连接操作,包括嵌套外接连。

BKA的优点:更加高效的申扫描提高了连年性。

另外,先前单纯用于内连接的块嵌套循环(BNL)连接算法现已扩大,可用于外连接半连接操作,包括嵌套外连接

以下部分讨论了连续缓冲区管理,它是原始BNL算法扩展,扩展BNL算法和BKA算法的基础。
有关半连连策略的音信,请参见“使用半总是转换优化子查询,派生表和视图引用”

MySQL查询优化,MySQL优化

Nested Loop Join算法

用外层表的结果集作为循环的底子数据,然后循环打该结果集每次一样长获取数据作为下一个发明底过滤条件去查询数据,然后合并结果。如果起差不多个表join,那么应该以前方的说明底结果集作为循环数据,取结果集中之各一样执再次届下一个表中继续开展巡回匹配,获取结果集并返回给客户端。

伪代码如下

for each row in t1 matching range {
  for each row in t2 matching reference key {
     for each row in t3 {
      if row satisfies join conditions,
      send to client
    }
  }
 }

 

寻常的Nested-Loop
Join算法一次只能用一行数传内存循环,所以外层循环结果集有多少行,那么内存循环就要尽稍微坏。

1、简介

   
 一个吓之web应用,最着重之某些是所有好的走访性能。数据库MySQL是web应用之有些,也是决定其属性的根本组成部分。所以提升MySQL的性质至关重要。

   
 MySQL性能的升官而分为三局部,包括硬件、网络、软件。其中硬件、网络在企业的老本,需要白哗哗的银子,这里就未说啊。软件而密切分为多栽,在此间我们经过MySQL的查询优化从而达到性能的升级换代。

     最近拘留了部分有关查询优化的书本,同时为当网上看片长辈们写的稿子。

以下是上下一心收拾借鉴关于查询优化的部分总结:

Block Nested-Loop算法

MySQL
BNL算法原本就支持内连接,现在早就支持外连接半连接操作,包括嵌套外连接

BNL算法原理:将外层循环的行/结果集存入join
buffer,内存循环的各级一行数和一切buffer中的记录做比较,可以减小内层循环的扫描次数

选举个简单的事例:外层循环结果集来1000实践数据,使用NLJ算法需要扫描内层表1000潮,但要采用BNL算法,则先行取出外层表结果集的100尽存放到join
buffer,
然后因故外层表的各个一行数去和当下100推行结果集做比较,可以一次性和100履行数据进行比,这样内层表其实只有待循环1000/100=10涂鸦,减少了9/10。

伪代码如下

for each row in t1 matching range {
   for each row in t2 matching reference key {
    store used columns from t1, t2 in join buffer
    if buffer is full {
      for each row in t3 {
         for each t1, t2 combination in join buffer {
          if row satisfies join conditions,
          send to client
        }
        }
       empty buffer
     }
   }
 }

 if buffer is not empty {
    for each row in t3 {
     for each t1, t2 combination in join buffer {
       if row satisfies join conditions,
       send to client
      }
   }
 }

 

假定t1, t2厕join的列长度只跟为s, c也两者组合数, 那么t3表被围观的次数为

(S * C)/join_buffer_size + 1

 

扫描t3底次数就join_buffer_size的叠加而减去, 直到join
buffer能够容纳有的t1, t2成, 再长大join buffer size, query
的速度就不见面又转换快了。

 

optimizer_switch网变量的block_nested_loop标志控制优化器是否使用块嵌套循环算法。

默认情况下,block_nested_loop已启用。

在EXPLAIN输出中,当Extra值包含Using join buffer(Block Nested Loop)type值为ALL,index或range时,表示用BNL。

示例

mysql> explain SELECT  a.gender, b.dept_no FROM employees a, dept_emp b WHERE a.birth_date = b.from_date;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
|  1 | SIMPLE      | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 298936 |   100.00 | NULL                                               |
|  1 | SIMPLE      | b     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 331143 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

 

2、截取SQL语句

     1、全面查询日志

     2、慢查询日志

     3、二进制日志

     4、进程列表

  SHOW FULL PROCESSLIST;

  。。。

Batched Key Access 算法

对此多表join语句,当MySQL使用索引访问亚单join表的下,使用一个join
buffer来收集第一只操作对象生成的相关列值。BKA构建好key后,批量传给引擎层做索引查找。key是经MRR接口提交给引擎的,这样,MRR使得查询更有效率。

倘外部表扫描的凡主键,那么表中的笔录看都是于平稳的,但是倘若连接的列是非主键索引,那么对表中记录的看可能就是是深离散的。因此对此未主键索引的接入,Batched
Key Access
Join算法将会大幅度增强SQL的实施效率。BKA算法支持外连续,外接连和半连接操作,包括嵌套外接连。

Batched Key Access Join算法的工作步骤如下:

Batched Key Access Join算法的本色上的话要Simple Nested-Loops
Join算法,其来的规则为其中表及发目录,并且该索引为非主键,并且连接需要拜访中表主键上之目。这时Batched
Key Access Join算法会调用Multi-Range
Read(MRR)接口,批量底开展查找引键的匹配和主键索引上获取数据的操作,以这来增强联接的推行效率,因为读取数据是以一一磁盘IO而无是即兴磁盘IO进行的。

使用BKA时,join_buffer_size的值定义了针对存储引擎的每个请求被批量密钥的分寸。缓冲区越怪,对连操作的右侧表的顺序访问就更加多,这可发泄着提高性能。

要使用BKA,必须将optimizer_switch系统变量的batched_key_access标明设置为on。
BKA以MRR,因此mrr标志也亟须打开。目前,MRR的资本估算过于悲观。因此,mrr_cost_based啊须关闭才能够动用BKA。

以下设置启用BKA:

mysql> SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

 

在EXPLAIN输出中,当Extra值包含Using join buffer(Batched Key Access)还类型值为refeq_ref时不时,表示以BKA。

示例:

mysql> show index from employees;
+-----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employees |          0 | PRIMARY        |            1 | emp_no      | A         |      298936 |     NULL | NULL   |      | BTREE      |         |               |
| employees |          1 | idx_name       |            1 | last_name   | A         |        1679 |     NULL | NULL   |      | BTREE      |         |               |
| employees |          1 | idx_name       |            2 | first_name  | A         |      277495 |     NULL | NULL   |      | BTREE      |         |               |
| employees |          1 | idx_birth_date |            1 | birth_date  | A         |        4758 |     NULL | NULL   |      | BTREE      |         |               |
+-----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)


mysql> explain SELECT a.gender, b.dept_no FROM employees a, dept_emp b WHERE a.birth_date = b.from_date;
+----+-------------+-------+------------+------+----------------+----------------+---------+-----------------------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref                   | rows   | filtered | Extra |
+----+-------------+-------+------------+------+----------------+----------------+---------+-----------------------+--------+----------+-------+
|  1 | SIMPLE      | b     | NULL       | ALL  | NULL           | NULL           | NULL    | NULL                  | 331143 |   100.00 | NULL  |
|  1 | SIMPLE      | a     | NULL       | ref  | idx_birth_date | idx_birth_date | 3       | employees.b.from_date |     62 |   100.00 | NULL  |
+----+-------------+-------+------------+------+----------------+----------------+---------+-----------------------+--------+----------+-------+

#使用hint,强制走bka

mysql> explain SELECT /*+ bka(a)*/ a.gender, b.dept_no FROM employees a, dept_emp b WHERE a.birth_date = b.from_date;
+----+-------------+-------+------------+------+----------------+----------------+---------+-----------------------+--------+----------+----------------------------------------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref                   | rows   | filtered | Extra                                  |
+----+-------------+-------+------------+------+----------------+----------------+---------+-----------------------+--------+----------+----------------------------------------+
|  1 | SIMPLE      | b     | NULL       | ALL  | NULL           | NULL           | NULL    | NULL                  | 331143 |   100.00 | NULL                                   |
|  1 | SIMPLE      | a     | NULL       | ref  | idx_birth_date | idx_birth_date | 3       | employees.b.from_date |     62 |   100.00 | Using join buffer (Batched Key Access) |
+----+-------------+-------+------------+------+----------------+----------------+---------+-----------------------+--------+----------+----------------------------------------+
2 rows in set, 1 warning (0.00 sec)

 

3、查询优化中心分析命令

  1、EXPLAIN {PARTITIONS|EXTENDED}

  2、SHOW CREATE TABLE tab;

  3、SHOW INDEXS FROM tab;

  4、SHOW TABLE STATUS LIKE ‘tab’;

  5、SHOW [GLOBAL|SESSION] STATUS LIKE ‘’;

  6、SHOW VARIABLES

  。。。。

  ps:我自己都发方面都是无任何营养的物。下面才是实在的干货哈。

BNL和BKA算法的优化器Hint

除外行使optimizer_switch系统变量来支配优化程序于对话范围外使用BNL和BKA算法之外,MySQL还支持优化程序提示,以便在每个语句的基础及影响优化程序。
请参见“优化程序Hint”。

如若动BNL或BKA提示为表联接的另内部表启用连缓冲,必须为表联接的富有中表启用连缓冲。

图片 1

使用qb_name

SELECT /*+ QB_NAME(qb1) MRR(@qb1 t1) BKA(@qb2) NO_MRR(@qb3t1 idx1, id2) */ ...
  FROM (SELECT /*+ QB_NAME(qb2) */ ...
  FROM (SELECT /*+ QB_NAME(qb3) */ ... FROM ...)) ...

 

4、查询优化几独样子

  1、尽量避免全文扫描,给相应字段增加索引,应用索引来查询

  2、删除不用或另行的目录

  3、查询重写,等价转换(谓词、子查询、连接查询)

  4、删除内容更无必要的说话,精简语句

  5、整合重复执行的讲话

  6、缓存查询结果

5、索引优化

  5.1、索引优点:

    1、保持数据的完整性

    2、提高多少的询问性能

    3、改进表的连续操作(jion)

    4、对查询结果进行排序。没索引将会见采取中文件排序算法进行排序,效率比缓慢

    5、简化聚合数据操作

  5.2、索引缺点

    1、索引需要占用一定的积存空间

    2、数据插入、更新、删除时会见受索引的震慑,性能会落。因为数量变更索引为需要进行翻新

    3、多单目录,优化器需要耗时虽然可以选择

  5.3、索引选择

    1、数据量大时采用

    2、数据高度重复时,不行使

    3、查询取出数据超出20%,将采用全文扫描,不用索引

  5.4、索引细究

    资料查询:

    MySQL中之InnoDB、MyISAM都是B-Tree类型索引

    B-Tree包含:PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT

    B-Tree类型索引不支持(即字段使用以下符号时,将不利用索引):

    >, <, >=, <=, BETWEEN, !=, <>,like ‘%**’

    【在此先介绍一下掩索引】

    以自身自己了解的道介绍吧。覆盖索引并无是诸如主键索引、唯一索引一样真正是,它只是针对索引应用某些特定情景的一律种概念【另一样栽理解:查询的排是寻觅引列,因此列被索引覆盖】。它好突破传统的限定,使用上述操作符,且仍采用索引进行询问。

    因为查询的排列是寻觅引列,所以未需读取行,只需要读取列字段数据就可以了。【例如你看无异本书,需要找有一样内容,刚好那内容出现在目录中,那便不要一页页翻了,直接在目录中固定及第几页查找】

    如何激活覆盖索引呢?什么样才是一定情景也?

    索引字段,在select中起就是了。

    复合索引还可能有另外的特状况。例如,三排列复合索引,仅用在select、where、group
by、order
by中,任意一个地方出现同等糟糕复合索引最左边列就好激活使用覆盖索引了。

    查看:

    EXPLAIN中Extra显示出Using index表示马上漫长告句以了覆盖索引。

    结论:

    不建议在查询的下以select*from进行查询了,应该写用因此的字段,并且多对应的目录,以提高查询性能。

    针对上述操作符实测结果:

    1、以select*from形式,where中是primary
key可以通杀【除like】(使用主键进行询问);index则统不得以。

    2、以select 字段a from tab where
字段a《以上操作符》形式测试,结果依然得以用索引查询。【采用了覆盖索引】

    其他索引优化措施:

    1、使用索引关键字作连续的极

    2、复合索引使用

    3、索引合并or and,将干到之字段合并成复合索引

    4、where、和group by涉及字段加索引

6、子查询优化

  于from中也免互关子查询,可以上拉子查询及父层。在多表连接查询考虑连接代价再摘。

  查询优化器对子查询一般采取嵌套执行之主意,即针对父亲查询中的诸一样推行,都执行一次子查询,这样子查询会执行好频繁。这种实践办法效率特别没有。

  子查询转化为总是查询优点:

  1、子查询不用执行好频繁

  2、优化器可以根据消息来抉择不同的不二法门及连续各个

  3、子查询的连天条件,过滤条件成为慈父查询的淘标准,以提高效率。

  优化:

  子查询合并,若多个子查询,能合并的玩命合并。

  子查询展开,即达拖累变成多表查询(时刻保证等价格变化)

  注意:

  子查询展开只能进展简单的询问,若子查询含有聚集函数、GROUP
BY、DISTINCT,则非可知上拉。

  select * from t1 (select*from tab where id>10) as t2 where
t1.age>10 and t2.age<25;

  select*from t1,tab as t2 where t1.age>10 and t2.age<25 and
t2.id>10;

  具体步骤:

  1、from与from合并,修改相应参数

  2、where与where合并,用and连接

  3、修改相应的谓词(in改=)

7、等价谓词重写:

  1、BETWEEEN AND改写为 >=
、<=之类的。实测:十万条数据,重写前后日,1.45s、0.06s

  2、in转换多独or。字段为索引时,两个都能就此到目录,or效率相对in好一些

  3、name like ‘abc%’改写成name>=’abc’ and name<’abd’;

  注意:百万级数据测试,name没有索引之前like比后同种查询快;给字段增加索引后,后面的赶快一点点,相差不深,因为个别种方式以询问的时刻还为此到了目录。

  。。。。

8、条件化简与优化

  1、将where、having(不在groupby和聚集函数时)、join-on条件能够合并之玩命合并

  2、删除不必要之括号,减少语法分许的or和and树层,减少cpu消耗

  3、常量传递。a=b and b=2转换为 a=2 and
b=2。尽量不采用变量a=b或[email protected]

  4、消除无因此底SQL条件

  5、where等号右侧尽量不出新表达式计算;where中毫无对字段进行表达式计算、函数的运用

  6、恒等转移、不等式变换。例:测试百万层数据a>b and
b>10变成a>b and a>10 and b>10优化显著

9、外接连优化

  即将外接连转为内接连

  优点:

  1、优化处理器处理外接连于内连步骤多且耗时

  2、外接连消除后,优化器选择多表连接各个来还多选,可以择优而挑选

  3、可以拿罗标准最好严厉的表作为表(连接各个最前头,是差不多交汇循环体的外循环层),

  可以减掉非必要之I/O开销,能加快算法执行的快慢。

  on a.id=b.id以及where
a.id=b.id的差异,on则表明展开连续,where则开展多少相比

  注意:前提必须是结果也NULL决绝(即规范限制不要NULL数据行,语意上是外连接)

  优化原则:

  精简查询,连接消除,等效转换,去除多余表对象连接

  例如:主键/唯一键作为连接条件,且中间表列只看做等值条件,可以错过丢中间表连接

10、其他查询优化

  1、以下将会导致放弃索引查询,采用全文扫描

    1.1、where
子句被利用!=或<>操作符  注意:主键支持。非主键不支持

    1.2、避免用or

      经测试,并非是使用了or就必将不能够动用索引,大多情况下是没因此到目录,但还有个别状况是用到的,因此具体情况具体分析。

      类似优化:

      select * from tab name=’aa’ or name=’bb’;

      =>

      select * from tab name=’aa’

      union all

      select * from tab name=’bb’;

      实测:

      1、十万数据测试,没任何索引的图景下,上面比较下的询问速率快一倍。

      2、三十万数据测试,aa与bb都是独自找引情况下,下面的询问速率比or快一点。

    1.3、避免下not in

      not in一般不能够下索引;主键字段可以

    1.4、where中尽量避免使用对null的论断

    1.5、like不能够放百分号 like ‘%.com’

      解决:

        1、若必须使用%前置,且数额长度不死,例如URL,可拿数据翻转存入数据库,再来查阅。LIKE
REVERSE‘%.com’;

        2、使用覆盖索引

 

    1.6、使用索引字段作为规范的时,假若是复合索引,则该以索引最左边前缀的字段名

  2、将exists代替in

    select num from a where num in(select num from b)

    select num from a where exists(select 1 from b where num=a.num)

    一百万长长的数,筛选59417长条数据用时6.65s、4.18s。没举行其他优化,仅仅只是将exists替换in。

  3、字段定义是字符串,查询时未尝带引号,不会见就此索引,将见面进行全文扫描。

  【以下是摘抄于半夜间乱弹琴博文http://www.cnblogs.com/lingiu/p/3414134.html,本人没进行相应的测试】

  4、尽量以表变量来代表临时表

  5、避免频繁创建同去临时表,以减小系统表资源的吃

  6、如果采用及了临时表,在储存过程的终极要以所有的临时表显式删除,先
truncate table ,然后 drop table ,这样好避系统表的较长时间锁定

  7、尽量避免使用游标,因为游标的频率比差,如果游标操作的数额超过1万履行,那么就是相应考虑改写

  8、大数据量,若数据量过好,应该考虑相应要求是否合理。

  9、尽量避免大事务操作,提高系统出现能力。

  。。。。。

11、博文总结

  经过这些天查资料敲代码的上,了解及了MySQL的查询优化并无是简简单单的比如有公式某个规则就是只是达成的。实验是检察标准的唯一标准,经过这几乎上之测试,得出的定论就是是:MySQL的查询优化是有充分方向,但是想使汲取一个万能够优化公式那是休可能的,毕竟每一样长条SQL查询语句之写法、结果在重要、以及说明的字段环境还无平等。能够达标看SQL查询语句就可知查获优化措施的大神,必定是周密研究了SQL查询优化并且有过一些年优化涉的老鸟。哈哈,我还才是单稍菜鸟。

  建议各位在上学SQL查询优化的童鞋们:不要仅仅只是看,要多敲代码,多测试,各种字段环境测试、各种数据量级别测试。

 

如上是协调的有些总结,也许有些欠缺。毕竟自己还特是单菜鸟,并且也非是DBA的动向,若大家发现发生欠缺之地方,或者不当的地方,请而会提取出来。

 

笔者:壹叶随风

宣示:转载时伸手于篇章页面明显位置于来原来和链接。

http://www.bkjia.com/Mysql/1197446.htmlwww.bkjia.comtruehttp://www.bkjia.com/Mysql/1197446.htmlTechArticleMySQL查询优化,MySQL优化 1、简介
一个吓的web应用,最根本之某些凡是具有美妙之走访性能。数据库MySQL是web应用的片,也是控制其性…

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图