MySQL查询优化,MySQL优化

发布时间:2018-11-15  栏目:MySQL  评论:0 Comments

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

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

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

BKA的优点:更加便捷之阐明扫描提高了连续属性。

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

以下一些讨论了连接缓冲区管理,它是原始BNL算法扩展,扩展BNL算法和BKA算法的根底。
有关半老是策略的信息,请参见“使用半接连转换优化子查询,派生表和视图引用”

  • Nested Loop Join
    算法

  • Block Nested-Loop
    算法

  • Batched Key Access
    算法

  • BNL和BKA算法的优化器Hint

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算法的办事步骤如下:

  • 1) 将表面表中相关的列放入Join Buffer中。

  • 2) 批量的拿Key(索引键值)发送至Multi-Range Read(MRR)接口。

  • 3) Multi-Range
    Read(MRR)通过接的Key,根据其相应的ROWID进行排序,然后重新拓展数据的读取操作。

  • 4) 返回结果集吃客户端。

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地图