hi,你好!欢迎访问本站!登录
本站由网站地图腾讯云宝塔系统阿里云强势驱动
当前位置:首页 - 教程 - 数据库 - 正文 君子好学,自强不息!

实践(2)--MySQL机能优化_数据库

2020-09-19数据库搜奇网55°c
A+ A-

相干进修引荐:mysql教程

媒介

  • MySQL索引底层数据结构与算法
  • MySQL机能优化道理-前篇
  • 实践(1)--MySQL机能优化

上一篇 《实践(1)--MySQL机能优化》我们讲了数据库表设想的一些准绳,Explain东西的引见、SQL语句优化索引的最好实践,本篇继承来聊聊 MySQL 怎样挑选适宜的索引。

MySQL Trace 东西

MySQL 终究是不是挑选走索引或许一张表触及多个索引,终究是怎样挑选索引,能够运用 trace 东西来一查终究,开启 trace东西会影响 MySQL 机能,所以只能暂时剖析 SQL 运用,用完以后马上封闭。

案例剖析

讲 trace 东西之前我们先来看一个案例:

# 示例表CREATE TABLE`employees`(`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',`age` int(11) NOT NULL DEFAULT '0' COMMENT '岁数',`position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时刻',
PRIMARY KEY (`id`), KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
 )ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='员工纪录表'; 
INSERT INTO employees(name,age,position,hire_time)VALUES('ZhangSan',23,'Manager',NOW());INSERT INTO employees(name,age,position,hire_time)VALUES('HanMeimei', 23,'dev',NOW());INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());复制代码

MySQL 怎样挑选适宜的索引

EXPLAIN select * from employees where name > 'a';复制代码

假如用name索引须要遍历name字段团结索引树,然后还须要依据遍历出来的主键值去主键索引树里再去查出终究数据,本钱比全表扫描还高,能够用掩盖索引优化,如许只须要遍历name字段的团结索引树就可以拿到一切效果,以下:

EXPLAIN select name,age,position from employees where name > 'a' ;复制代码
EXPLAIN select * from employees where name > 'zzz' ;复制代码

关于上面这两种 name>'a'name>'zzz' 的实行效果,mysql终究是不是挑选走索引或许一张表触及多个索引,mysql终究怎样挑选索引,我们能够用trace东西来一查终究,开启trace东西会影响mysql机能,所以只能暂时剖析sql运用,用完以后马上封闭。

trace东西用法

开启/封闭Trace

#开启traceset session optimizer_trace="enabled=on",end_markers_in_json=on;
#封闭traceset session optimizer_trace="enabled=off";复制代码

案例1

实行这两句sql

select * from employees where name >'a' order by position;sELECT * FROM information_schema.OPTIMIZER_TRACE; 
复制代码

提出来trace值,详见诠释

{  "steps": [
    {      "join_preparation": { --第一阶段:SQL预备阶段        "select#": 1,        "steps": [
          {            "expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`name` AS `name`,`employees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time` from `employees` where (`employees`.`name` > 'a') order by `employees`.`position`"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {      "join_optimization": { --第二阶段:SQL优化阶段        "select#": 1,        "steps": [
          {            "condition_processing": { --前提处置惩罚              "condition": "WHERE",              "original_condition": "(`employees`.`name` > 'a')",              "steps": [
                {                  "transformation": "equality_propagation",                  "resulting_condition": "(`employees`.`name` > 'a')"
                },
                {                  "transformation": "constant_propagation",                  "resulting_condition": "(`employees`.`name` > 'a')"
                },
                {                  "transformation": "trivial_condition_removal",                  "resulting_condition": "(`employees`.`name` > 'a')"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {            "table_dependencies": [ --表依靠概况
              {                "table": "`employees`",                "row_may_be_null": false,                "map_bit": 0,                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {            "ref_optimizer_key_uses": [
            ] /* ref_optimizer_key_uses */
          },
          {            "rows_estimation": [ --预估表的接见本钱
              {                "table": "`employees`",                "range_analysis": {                  "table_scan": { --全表扫描                    "rows": 3,  --扫描行数                    "cost": 3.7 --查询本钱
                  } /* table_scan */,                  "potential_range_indexes": [ --查询大概运用的索引
                    {                      "index": "PRIMARY",   --主键索引                      "usable": false,                      "cause": "not_applicable"
                    },
                    {                      "index": "idx_name_age_position", --辅佐索引                      "usable": true,                      "key_parts": [                        "name",                        "age",                        "position",                        "id"
                      ] /* key_parts */
                    },
                    {                      "index": "idx_age",                      "usable": false,                      "cause": "not_applicable"
                    }
                  ] /* potential_range_indexes */,                  "setup_range_conditions": [
                  ] /* setup_range_conditions */,                  "group_index_range": {                    "chosen": false,                    "cause": "not_group_by_or_distinct"
                  } /* group_index_range */,                  "analyzing_range_alternatives": { --剖析各个索引运用本钱                    "range_scan_alternatives": [
                      {                        "index": "idx_name_age_position",                        "ranges": [                          "a < name"    --索引运用局限
                        ] /* ranges */,                        "index_pes_for_eq_ranges": true,                        "rowid_ordered": false, --运用该索引猎取的纪录是不是依据主键排序                        "using_mrr": false,                        "index_only": false,    --是不是运用掩盖索引                        "rows": 3,  --索引扫描行数                        "cost": 4.61, --索引运用本钱                        "chosen": false, --是不是挑选该索引                        "cause": "cost"
                      }
                    ] /* range_scan_alternatives */,                    "analyzing_roworder_intersect": {                      "usable": false,                      "cause": "too_few_roworder_scans"
                    } /* analyzing_roworder_intersect */
                  } /* analyzing_range_alternatives */
                } /* range_analysis */
              }
            ] /* rows_estimation */
          },
          {            "considered_execution_plans": [
              {                "plan_prefix": [
                ] /* plan_prefix */,                "table": "`employees`",                "best_access_path": { --最优接见途径                  "considered_access_paths": [ --终究挑选的接见途径
                    {                      "rows_to_scan": 3,                      "access_type": "scan",    --接见范例:为sacn,全表扫描                      "resulting_rows": 3,                      "cost": 1.6,                      "chosen": true,   --一定挑选                      "use_tmp_table": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,                "condition_filtering_pct": 100,                "rows_for_plan": 3,                "cost_for_plan": 1.6,                "sort_cost": 3,                "new_cost_for_plan": 4.6,                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {            "attaching_conditions_to_tables": {              "original_condition": "(`employees`.`name` > 'a')",              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,              "attached_conditions_summary": [
                {                  "table": "`employees`",                  "attached": "(`employees`.`name` > 'a')"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {            "clause_processing": {              "clause": "ORDER BY",              "original_clause": "`employees`.`position`",              "items": [
                {                  "item": "`employees`.`position`"
                }
              ] /* items */,              "resulting_clause_is_simple": true,              "resulting_clause": "`employees`.`position`"
            } /* clause_processing */
          },
          {            "reconsidering_access_paths_for_index_ordering": {              "clause": "ORDER BY",              "index_order_summary": {                "table": "`employees`",                "index_provides_order": false,                "order_direction": "undefined",                "index": "unknown",                "plan_changed": false
              } /* index_order_summary */
            } /* reconsidering_access_paths_for_index_ordering */
          },
          {            "refine_plan": [
              {                "table": "`employees`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {      "join_execution": {   --第三阶段:SQL实行阶段                         
                         
        "select#": 1,        "steps": [
          {            "filesort_information": [
              {                "direction": "asc",                "table": "`employees`",                "field": "position"
              }
            ] /* filesort_information */,            "filesort_priority_queue_optimization": {              "usable": false,              "cause": "not applicable (no LIMIT)"
            } /* filesort_priority_queue_optimization */,            "filesort_execution": [
            ] /* filesort_execution */,            "filesort_summary": {              "rows": 3,              "examined_rows": 3,              "number_of_tmp_files": 0,              "sort_buffer_size": 200704,              "sort_mode": "<sort_key, packed_additional_fields>"
            } /* filesort_summary */
          }
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}复制代码

结论:全表扫描的本钱低于索引扫描,所以MySQL终究挑选全表扫描。

案例2

select * from employees where name > 'zzz' order by position;SELECT * FROM information_schema.OPTIMIZER_TRACE; 
复制代码

结论:检察trace字段可知索引扫描的本钱低于全表扫描,所以MySQL终究挑选索引扫描。

罕见SQL深切优化

Order byGroup by 优化

案例1

EXPLAIN select * from employees where name = 'ZhangSan' and position = 'dev' order by age复制代码

剖析:

应用最左前缀轨则:中心字段不能断,因而查询用到了 name索引 ,从 key_len = 74 也能看出,age 索引列用在排序历程历程当中,因为 Extra 字段里没有 using filesort

案例2

EXPLAIN select * from employees where name = 'ZhangSan' order by position复制代码

剖析:

从 explain 的实行效果来看:key_len = 74,查询运用了 name 索引,因为用了 position 举行排序,跳过了 age,涌现了 Using filesort

案例3

EXPLAIN select * from employees where name = 'ZhangSan' order by age,position复制代码

剖析:

查询只用到索引name,age 和 position 用于排序,无Using filesort

案例4

EXPLAIN select * from employees where name = 'ZhangSan' order by position,age复制代码

剖析:

和案例3中explain的实行效果一样,然则涌现了Using filesort ,因为索引的竖立次序为 name,age,position , 然则排序的时刻 age 和 position 倒置位置了。

案例5

EXPLAIN select * from employees where name = 'ZhangSan' and age = 18 order by position,age复制代码

剖析:

与案例4对照,在Extra中并未涌现** Using filesort **,因为 age 为常量,在排序中被优化,所以索引未倒置,不会涌现 Using filesort

案例6

EXPLAIN select * from employees where name = 'ZhangSan' order by age asc, position desc;复制代码

剖析:

虽然排序的字段列与索引次序一样,且 order by 默许升序,这里 position desc 变成列降序,致使与索引的排序体式格局差别,从而发生 Using filesort 。MySQL8 以上版本有降序索引能够支撑该种查询体式格局。

案例7

EXPLAIN select * from employees where name in ('ZhangSan', 'hjh') order by age, position;复制代码

剖析:

关于排序来讲,多个相称前提也是局限查询。

案例8

EXPLAIN select * from employees where name > 'a' order by name;复制代码

能够用掩盖索引优化

EXPLAIN select name,age,position from employees where name > 'a' order by name;复制代码

优化总结

  1. MySQL支撑两种体式格局的排序 filesortindex。Using index 是指MySQL 扫描索引自身完成排序。index 效力高,filesort 效力低。
  2. order by 满足两种状况会运用 Using index.
    • order by 语句运用索引最左前例
    • 运用 where 子句与 order by 子句前提列组合满足索引最左前例
  3. 只管在索引列上完成排序,遵照索引竖立索引竖立的次序)时的最左前缀轨则。
  4. 假如 order by 的前提不在索引列上,就会发生 Using filesort。
  5. 能用掩盖索引只管用掩盖索引。
  6. group by 和 order by 很相似,实在质是先排序后分组,遵照索引竖立次序的最左前缀轨则。关于 group by 的优化假如不须要排序的能够加上 order by null 制止排序。注重:where 高于 having,能写在 where 中的限制前提就不要去 having 限制了。

Using filesort文件排序道理

filesort文件排序体式格局

  • 单路排序:是一次性掏出满足前提行的一切字段,然后在 sort buffer 中举行排序;用 trace 东西能够看到 sort_mode 信息里显现 < sort_key, additional_fields > 或许 < sort_key, packed_additional_fields >。
  • 双路排序(又叫回表排序形式):是起首依据响应的前提掏出响应的排序字段能够直接定位运转数据的行ID,然后在 sort buffer 中举行排序,排序完后须要再次取回别的须要的字段;用 trace 东西能够看到 sort_mode 信息里显现 < sort_key, rowid >

MySQL 经由过程比较体系变量 max_length_for_sort_data (默许1024字节) 的大小和须要查询的字段总大小来推断运用那种排序形式。

  • 假如max_length_for_sort_data 比查询的字段的总长度大,那末运用单路排序形式;
  • 假如max_length_for_sort_data 比查询字段的总长度小,那末运用双路排序形式。

考证种种排序体式格局

EXPLAIN select * from employees where name = 'ZhangSan' order by position;复制代码

检察下这条sql对应trace效果以下(只展现排序部份):

set session optimizer_trace="enabled=on",end_markers_in_json=on; #开启traceselect * from employees where name = 'ZhangSan' order by position;select * from information_schema.OPTIMIZER_TRACE;复制代码
      "join_execution": { --SQL实行阶段
        "select#": 1,
        "steps": [
          {
            "filesort_information": [
              {
                "direction": "asc",
                "table": "`employees`",
                "field": "position"
              }
            ] /* filesort_information */,
            "filesort_priority_queue_optimization": {
              "usable": false,
              "cause": "not applicable (no LIMIT)"
            } /* filesort_priority_queue_optimization */,
            "filesort_execution": [
            ] /* filesort_execution */,
            "filesort_summary": { --文件排序信息
              "rows": 1, --估计扫描行数
              "examined_rows": 1, --参数排序的行
              "number_of_tmp_files": 0, --运用暂时文件的个数,这个只假如为0代表悉数运用的sort_buffer内存排序,不然运用的磁盘文件排序
              "sort_buffer_size": 200704, --排序缓存的大小
              "sort_mode": "<sort_key, packed_additional_fields>" --排序体式格局,这里用的单路排序
            } /* filesort_summary */
          }
        ] /* steps */
      } /* join_execution */复制代码

修正体系变量 max_length_for_sort_data (默许1024字节) ,employees 表一切字段长度总和一定大于10字节

set max_length_for_sort_data = 10; 
select * from employees where name = 'ZhangSan' order by position;select * from information_schema.OPTIMIZER_TRACE;复制代码

trace排序部份效果:

      "join_execution": {
        "select#": 1,
        "steps": [
          {
            "filesort_information": [
              {
                "direction": "asc",
                "table": "`employees`",
                "field": "position"
              }
            ] /* filesort_information */,
            "filesort_priority_queue_optimization": {
              "usable": false,
              "cause": "not applicable (no LIMIT)"
            } /* filesort_priority_queue_optimization */,
            "filesort_execution": [
            ] /* filesort_execution */,
            "filesort_summary": {
              "rows": 1,
              "examined_rows": 1,
              "number_of_tmp_files": 0,
              "sort_buffer_size": 53248,
              "sort_mode": "<sort_key, rowid>" --排序体式格局,这里用饿的双路排序
            } /* filesort_summary */
          }
        ] /* steps */
      } /* join_execution */   
复制代码

单路排序的细致历程:

  1. 从索引 name 找到第一个满足 name='ZhangSan' 前提的主键 id;
  2. 依据主键id掏出整行,掏出一切字段的值,存入sort_buffer中
  3. 从索引name找到下一个满足 name='ZhangSan' 前提的主键 id;
  4. 反复步骤2、3直到不满足 name='ZhangSan';
  5. 对 sort_buffer 中的数据依据字段 position 举行排序;
  6. 返回效果给客户端

双路排序的细致历程:

  1. 从索引 name 找到第一个满足 name='ZhangSan' 的主键id;
  2. 依据主键id掏出整行,把排序字段 position 和 主键id 这两个字段放到 sort_buffer 中
  3. 从索引 name 取下一个满足 name='ZhangSan' 纪录的主键id;
  4. 反复步骤3、4直到不满足 name='ZhangSan';
  5. 对 sort_buffer 中的字段 position 和 主键id依据 position 举行排序;
  6. 遍历排序好的 id 和 字段 position,依据 id 的值回到原表中掏出一切的字段的值返回给客户端。

对照两个排序形式,单路排序会把一切须要查询的字段都放到 sort_buffer 中,而双路排序只会把主键和须要排序的字段放到 sort_buffer 中举行排序,然后再经由过程主键回到原表查询须要的字段。

假如MySQL排序内存设置的比较小而且没有前提继承增添了,能够恰当把 max_length_for_sort_data 设置小点,让优化器挑选运用双路排序算法,能够在 sort_buffer 中一次排序更多的行,只是须要再依据主键回到原表取数据。

假如MySQL排序内存有前提能够设置比较大,能够恰当增大 max_length_for_sort_data 的值,让优化器优先挑选全字段排序(单路排序),把须要的字段放到 sort_buffer 中,如许排序后就会直接从内存里返回查询效果了。

所以,MySQL 经由过程 max_length_for_sort_data 这个参数来掌握排序,在差别场景运用差别的排序形式,从而提拔排序效力。

注重:假如悉数运用sort_buffer 内存排序平常状况下效力会高于磁盘文件排序,但不能因为这个就随意增大 sort_buffer(默许1M),MySQL许多参数设置都做过优化的,不要随意马虎调解。

分页查询优化

在这我们先往 employess 插进去一些测试数据

drop procedure if exists insert_emp; 
delimiter ;; 
create procedure insert_emp()begin
    declare i int; 
    set i=1; 
    while(i<=100000) do
        insert into employees(name,age,position) values(CONCAT('hjh',i),i,'dev');        set i=i+1; 
    end while;end;;
delimiter ; 
call insert_emp();复制代码

许多时刻我们营业体系完成分页功用大概会用以下SQL完成

select * from employees limit 10000,10;复制代码

示意从表 employees 中掏出从 10001 行入手下手的 10 行纪录。看似只查询了 10 条纪录,现实这条 SQL 是先读取 10010 条纪录,然后扬弃前 10000 条纪录,然后读到背面 10 条想要的数据。因而要查询一张大表比较靠后的数据,实行效力是异常低的。

罕见的分页场景优化技能

  1. 依据自增且一连的主键排序的分页查询
  2. 依据非主键字段排序的分页查询

案例1: 依据自增且一连的主键排序的分页查询

起首来看一个依据自增且一连主键排序的分页查询的例子:

select * from employees limit 9000,5;复制代码

该 SQL 示意查询从第 9001入手下手的五行数据,没增添零丁 order by,示意经由过程主键排序。我们再看表 employees ,因为主键是自增而且一连的,所以能够改写成依据主键去查询从第 9001入手下手的五行数据,以下:

select * from employees where id > 9000 limit 5;复制代码

查询效果是一致的,我们再对照一下实行计划:

EXPLAIN select * from employees limit 9000,5;复制代码
EXPLAIN select * from employees where id > 9000 limit 5;复制代码

明显改写后的 SQL 走了索引,而且扫描的行数大大削减,实行效力更高。 然则,这条改写的 SQL 在许多场景并不有用,因为表中大概某些纪录被删后,主键空白,致使效果不一致,以下图实验所示(先删除一条前面的纪录,然后再测试原 SQL 和优化后的 SQL):

两条 SQL 的效果并不一样,因而,假如主键不一连,不能运用上面形貌的优化要领。

别的假如原SQL是order by 非主键的字段,依据上面说饿的要领改写会致使两条SQL的效果不一致。所以这类改写得满足以下两个前提:

  • 主键自增且一连
  • 效果是依据主键排序的

案例2: 依据非主键字段排序的分页查询

再看一个依据非主键字段排序的分页查询,SQL 以下:

select * from employees ORDER BY name limit 9000,5;复制代码
 EXPLAIN select * from employees ORDER BY name limit 90000,5;复制代码

发明并没有运用 name 字段的索引(key 字段对应的值为 null),详细缘由上前面讲过 : 扫描全部索引并查找到没索引的行(大概要遍历多个索引树)的本钱比扫描全表的本钱更高,所以优化器摒弃运用索引。 晓得不走索引的缘由,那末怎样优化呢? 实在关键是让排序时返回的字段只管少,所以能够让排序和分页操纵先查出主键,然后依据主键查到对应的纪录,SQL 改写以下:

select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;复制代码

须要的效果与原 SQL 一致,实行时刻削减了一半以上,我们再对照优化前后sql的实行计划:

EXPLAIN select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;复制代码

原 SQL 运用的是 filesort 排序,而优化后的 SQL 运用的是索引排序。

Join关联查询优化

#示例表CREATE TABLE `t1` (    `id` INT (11) NOT NULL AUTO_INCREMENT,    `a` INT (11) DEFAULT NULL,    `b` INT (11) DEFAULT NULL,
    PRIMARY KEY (`id`),    KEY `idx_a` (`a`)
) ENGINE = INNODB AUTO_INCREMENT = 10001 DEFAULT CHARSET = utf8;CREATE TABLE t2 LIKE t1;复制代码

往t1表插进去1万行纪录,往t2表插进去100行纪录

#t1 1万条纪录drop procedure if exists insert_emp_t1; 
delimiter ;; 
create procedure insert_emp_t1()begin
    declare i int; 
    set i=1; 
    while(i<=10000) do
        insert into t1(a,b) values(i,i);        set i=i+1; 
    end while;end;;
delimiter ; 
call insert_emp_t1();

#t2 100条纪录drop procedure if exists insert_emp_t2; 
delimiter ;; 
create procedure insert_emp_t2()begin
    declare i int; 
    set i=1; 
    while(i<=100) do
        insert into t2(a,b) values(i,i);        set i=i+1; 
    end while;end;;
delimiter ; 
call insert_emp_t2();复制代码

MySQL 的表关联罕见有两种算法

  1. Nested-Loop Join 算法
  2. Block Nested-Loop Join 算法

案例1:嵌套轮回衔接 Nested-Loop Join(NLJ)算法

一次一行轮回地从第一张表(称为驱动表)中读取行,在这行数据中取到关联字段,依据关联字段在另一张表(被驱动表)里掏出满足前提的行,然后掏出两张表的效果合集。

EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;复制代码

从实行计划中能够看到这些信息:

  • 驱动表是 t2,被驱动表是 t1。先实行的就是驱动表(实行计划效果的id假如一样则按从上到下次序实行sql);优化器平常会优先挑选小表做驱动表。所以运用 inner join 时,排在前面的表并不一定就是驱动表
  • 运用了 NLJ 算法。平常 join 语句中,假如实行计划 Extra 中未涌现 Using join buffer 则示意运用的 join 算法是 NLJ。

上面SQL的大抵流程以下

  1. 从表 t2 中读取一行数据;
  2. 从第1步的数据中,掏出关键字字段 a,到表 t1 中查找;
  3. 掏出表 t1 中满足前提的行,跟 t2 中猎取到的效果兼并,作为效果返回给客户端;
  4. 反复上面 3 步。

全部历程会读取 t2 表的一切数据(扫描100行),然后遍历这每行数据中字段 a 的值,依据 t2 表中的 a 的值索引扫描 t1 表中对应的行(扫描 100次 t1 表的索引,1次扫描能够以为终究只扫描 t1 表一行完全数据,也就是统共 t1 表也扫描了100行)。因而全部历程扫描了 200 行

假如被驱动表的关联字段没有索引,运用NLJ算法机能会比较低(下面有细致诠释),MySQL 会挑选 Block Nested-Loop Join 算法。

案例2:基于块的嵌套轮回衔接 Block Nested-Loop Join(BNL)算法

驱动表的数据读入到 join_buffer 中,然后扫描被驱动表,把被驱动表每一行掏出来跟 join_buffer 中的数据做对照。

EXPLAIN select * from t1 inner join t2 on t1.b= t2.b;复制代码

Extra 中 的Using join buffer (Block Nested Loop)申明该关联查询运用的是 BNL 算法。

上面sql的大抵流程以下:

  1. 把 t2 的一切数据放入到 join_buffer 中
  2. 把表 t1 中每一行掏出来,跟 join_buffer 中的数据做对照
  3. 返回满足 join 前提的数据

全部历程对表 t1 和 t2 都做了一次全表扫描,因而扫描的总行数为10000(表 t1 的数据总量) + 100(表 t2 的数据总量) = 10100。而且 join_buffer 里的数据是无序的,因而对表 t1 中的每一行,都要做 100 次推断,所以内存中的推断次数是 100 * 10000= 100 万次

被驱动表的关联字段没索引为何要挑选运用 BNL 算法而不运用 Nested-Loop Join 呢?

假如上面第二条sql运用 Nested-Loop Join,那末扫描行数为 100 * 10000 = 100万次,这个是磁盘扫描

很明显,用BNL磁盘扫描次数少许多,比拟于磁盘扫描,BNJ 的内存盘算会快得多。

因而MySQL关于被驱动表的关联字段没索引的关联查询,平常都邑运用 BNL 算法。假如有索引平常挑选 NLJ 算法,有索引的状况下 NLJ 算法比 BNL算法机能更高。

关于关联SQL的优化

  • 关联字段加索引,让mysql做join操纵时只管挑选NLJ算法
  • 小表驱动大表,写多表衔接sql时假如明白晓得哪张表是小表能够用straight_join写法牢固衔接驱动体式格局,省去mysql优化器本身推断的时刻

straight_join诠释

straight_join功用同join相似,但能让左侧的表来驱动右侧的表,能转变优化器关于联表查询的实行次序。

比方 : select * from t2 straight_join t1 on t2.a = t1.a; 代表制订mysql挑选 t2 表作为驱动表。

  • straight_join只适用于inner join,并不适用于left join,right join。(因为left join,right join已代表指 定了表的实行次序)
  • 只管让优化器去推断,因为大部份状况下mysql优化器是比人要智慧的。运用straight_join一定要郑重,因 为部份状况下工资指定的实行次序并不一定会比优化引擎要靠谱。

in 和 exsits 优化

准绳:小表驱动大表,即小的数据集驱动大的数据集。

in:当B表的数据集小于A表的数据集时,in优于exists

select * from A where id in(select id from B) 
#等价于:for(select id from B){    select * from A where A.id = B.id
}复制代码

exists:当A表的数据集小于B表的数据集时,exists优于in

将主查询A的数据,放到子查询B中做前提考证,依据考证效果(true或false)来决议主查询的数据是不是保存

select * from A where exists (select 1 from B whereB.id=A.id) 
#等价于:for(select * from A){    select * from B where B.id = A.id
}

#A表与B表的ID字段应竖立索引复制代码
  1. EXISTS (subquery)只返回TRUE或FALSE,因而子查询中的SELECT * 也能够用SELECT 1替代,官方说法是现实实行时会 疏忽SELECT清单,因而没有区分;
  2. EXISTS子查询的现实实行历程大概经过了优化而不是我们明白上的逐条对照;
  3. EXISTS子查询每每也能够用JOIN来替代,何种最优须要详细问题详细剖析;

Count(*) 查询优化

暂时封闭mysql查询缓存,为了检察sql屡次实行的实在时刻。

set global query_cache_size=0;set global query_cache_type=0;复制代码
EXPLAIN select count(1) from employees; 
EXPLAIN select count(id) from employees;EXPLAIN select count(name) from employees; 
EXPLAIN select count(*) from employees;复制代码

四个sql的实行计划一样,申明这四个sql实行效力应当差不多,区分在于依据某个字段count不会统计字段为null值的数据行。

为何mysql终究挑选辅佐索引而不是主键群集索引?

因为二级索引相对主键索引存储数据更少,检索机能应当更高

罕见的优化要领以下:

  • 查询MySQL本身保护的总行数
  • show table status
  • 将总数保护到Redis里
  • 增添计数表

查询MySQL本身保护的总行数

关于myisam存储引擎的表做不带where前提的count查询机能是很高的,因为myisam存储引擎的表的总行数会被 mysql存储在磁盘上,查询不须要盘算。

关于innodb存储引擎的表mysql不会存储表的总纪录行数,查询count须要及时盘算。

show table status

假如只须要晓得表总行数的估计值能够用以下sql查询,机能很高

将总数保护到Redis里

插进去或删除表数据行的时刻同时保护redis里的表总行数key的计数值(用incr或decr敕令),然则这类体式格局大概不准,很难保证表操纵和redis操纵的事件一致性。

增添计数表

插进去或删除表数据行的时刻同时保护计数表,让他们在同一个事件里操纵。

想相识更多编程进修,敬请关注php培训栏目!

以上就是实践(2)--MySQL机能优化的细致内容,更多请关注ki4网别的相干文章!

  选择打赏方式
微信赞助

打赏

QQ钱包

打赏

支付宝赞助

打赏

  移步手机端
实践(2)--MySQL机能优化_数据库

1、打开你手机的二维码扫描APP
2、扫描左则的二维码
3、点击扫描获得的网址
4、可以在手机端阅读此文章

本文来源:搜奇网

本文地址:https://www.sou7.cn/300803.html

关注我们:微信搜索“搜奇网”添加我为好友

版权声明: 本文仅代表作者个人观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。请记住本站网址https://www.sou7.cn/搜奇网。