Mysql性能调优(一)

戏子和兔子12138

已于 2024-04-10 11:30:06 修改

阅读量562

点赞数 21

文章标签：数据库

于 2024-04-10 11:29:32 首次发布

本文链接：https://blog.csdn.net/qq_37466787/article/details/137217970

版权

索引介绍

数据结构


B-tree	叶节点具有相同的深度，叶节点的指针为空，所有索引元素不重复	节点中的数据索引从左到右递增排列
B+Tree	非叶子节点不存储data，只存储索引(冗余)	子节点包含所有索引字段，叶子节点用指针连接，提高区间访问的性能
Hash	一次hash计算就可以定位	仅能满足 “=”，“IN”，不支持范围查询

B-tree

B+tree

储存引擎


MyISAM	索引文件和数据文件是分离的(非聚集)	每次需要回表查询
InnoDB	索引实现(聚集)	表数据文件本身就是按B+Tree组织的一个索引结构文件，聚集索引-叶节点包含了完整的数据记录
建议	InnoDB表建主键，并且推荐使用整型的自增主键	非主键索引结构叶子节点存储的是主键值（节省空间）

MyISAM

B+tree 二级索引

联合索引：需要遵从最左原则

Explain

使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析你的查询语句或是结构的性能瓶颈
在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行这条SQL。注意：如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中。

示例表：
DROP TABLE IF EXISTS `actor`; 
CREATE TABLE `actor` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (1,'a','2017-12-22 15:27:18'), (2,'b','2017-12-22 15:27:18'), (3,'c','2017-12-22 15:27:18');

DROP TABLE IF EXISTS `film`;
CREATE TABLE `film` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `film` (`id`, `name`) VALUES (3,'film0'),(1,'film1'),(2,'film2');

DROP TABLE IF EXISTS `film_actor`;
CREATE TABLE `film_actor` (
  `id` int(11) NOT NULL,
  `film_id` int(11) NOT NULL,
  `actor_id` int(11) NOT NULL,
  `remark` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_film_actor_id` (`film_id`,`actor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1,1,1),(2,1,2),(3,2,1);

explain 扩展

explain extended

在 explain 的基础上额外提供一些查询优化的信息。紧随其后通过 show warnings 命令可以得到优化后的查询语句，从而看出优化器优化了什么。额外还有 filtered 列，是一个百分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的id值比当前表id值小的表）。

explain extended select * from film where id = 1;

show warnings;

explain partitions

相比 explain 多了个 partitions 字段，如果查询是基于分区表的话，会显示查询将访问的分区。

explain列拆解

id列：select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。

select_type列：

1）simple：简单查询。查询不包含子查询和union

mysql> explain select * from film where id = 2;

2）primary：复杂查询中最外层的 select

3）subquery：包含在 select 中的子查询（不在 from 子句中）

4）derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）

例子来了解 primary、subquery 和 derived 类型

mysql> set session optimizer_switch='derived_merge=off'; #关闭mysql5.7新特性对衍生表的合并优化

mysql> explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;

set session optimizer_switch='derived_merge=on'; #还原默认配置

5）union：在 union 中的第二个和随后的 select

table列:

表示 explain 的一行正在访问哪个表。当 from 子句中有子查询时，table列是 <derivenN> 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id。

type列:

表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL一般来说，得保证查询达到range级别，最好达到ref;NULL:mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表

const, system：mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的结果）。用于 primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。system是const的特例，表里只有一条元组匹配时为system

explain extended select * from (select * from film where id = 1) tmp;

eq_ref：primary key 或 unique key 索引的所有部分被连接使用，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。

explain select * from film_actor left join film on film_actor.film_id = film.id;

ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。

explain select * from film where name = 'film1';

关联表查询，idx_film_actor_id是film_id和actor_id的联合索引，这里使用到了film_actor的左边前缀film_id部分。

explain select film_id from film left join film_actor on film.id = film_actor.film_id;

range：范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。

explain select * from actor where id > 1;

index：扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些。

explain select * from film;

ALL：即全表扫描，扫描你的聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了。

explain select * from actor;

possible_keys列:这一列显示查询可能使用哪些索引来查

key列:显示mysql实际采用哪个索引来优化对该表的访问。如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。

key_len列:mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列.

ref列:表查找值所用到的列或常量.

rows列:ysql估计要读取并检测的行数，注意这个不是结果集里的行数。

Extra列:

1）Using index：使用覆盖索引

覆盖索引定义：mysql执行计划explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有using index；覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值

explain select film_id from film_actor where film_id = 1;

2）Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖

explain select * from actor where name = 'a';

3）Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围；

explain select * from film_actor where film_id > 1;

4）Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。

1. actor.name没有索引，此时创建了张临时表来distinct

explain select distinct name from actor;

2. film.name建立了idx_name索引，此时查询时extra是using index,没有用临时表

explain select distinct name from film;

5）Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。

1. actor.name未创建索引，会浏览actor整个表，保存排序关键字name和对应的id，然后排序name并检索行记录

2. film.name建立了idx_name索引,此时查询时extra是using index

explain select * from film order by name;

6）Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段是

explain select min(id) from film;

索引实践

示例表：
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('LiLei',22,'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());

1.全值匹配

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei';

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22;

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';

2.最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

EXPLAIN SELECT * FROM employees WHERE name = 'Bill' and age = 31;

EXPLAIN SELECT * FROM employees WHERE age = 30 AND position = 'dev';

EXPLAIN SELECT * FROM employees WHERE position = 'manager';

3.不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei';

EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei';

给hire_time增加一个普通索引：

ALTER TABLE `employees` ADD INDEX `idx_hire_time` (`hire_time`) USING BTREE ;

EXPLAIN select * from employees where date(hire_time) ='2018-09-30';

转化为日期范围查询，有可能会走索引：

EXPLAIN select * from employees where hire_time >='2018-09-30 00:00:00' and hire_time <='2018-09-30 23:59:59';

还原最初索引状态

ALTER TABLE `employees` DROP INDEX `idx_hire_time`;

4.存储引擎不能使用索引中范围条件右边的列

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager';

5.尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句

6.mysql在使用不等于（！=或者<>），not in ，not exists 的时候无法使用索引会导致全表扫描

< 小于、 > 大于、 <=、>= 这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引

EXPLAIN SELECT * FROM employees WHERE name != 'LiLei';

7.is null,is not null 一般情况下也无法使用索引

8.like以通配符开头（'$abc...'）mysql索引失效会变成全表扫描操作

EXPLAIN SELECT * FROM employees WHERE name like '%Lei'

EXPLAIN SELECT * FROM employees WHERE name like 'Lei%'

9.字符串不加单引号索引失效

EXPLAIN SELECT * FROM employees WHERE name = '1000';

EXPLAIN SELECT * FROM employees WHERE name = 1000;

10.少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引，详见范围查询优化

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' or name = 'HanMeimei';

11.范围查询优化

给年龄添加单值索引

ALTER TABLE `employees` ADD INDEX `idx_age` (`age`) USING BTREE ;

explain select * from employees where age >=1 and age <=2000;

没走索引原因：mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。比如这个例子，可能是由于单次数据量查询过大导致优化器最终选择不走索引

优化方法：可以将大的范围拆分成多个小范围

explain select * from employees where age >=1 and age <=1000;

explain select * from employees where age >=1001 and age <=2000;

ALTER TABLE `employees` DROP INDEX `idx_age`;

索引优化

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=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

 INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'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());

 ‐‐ 插入一些示例数据
 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('zhuge',i),i,'dev');
 set i=i+1;
 end while;
 end;;
 delimiter ;
 call insert_emp();

1、联合索引第一个字段用范围不会走索引

EXPLAIN SELECT * FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';

结论：联合索引第一个字段就用范围查找不会走索引，mysql内部可能觉得第一个字段就用范围，结果集应该很大，回表效率不高，还不

如就全表扫描

2、强制走索引

EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei' AND age = 22 AND position ='manager';

结论：虽然使用了强制走索引让联合索引第一个字段范围查找也走索引，扫描的行rows看上去也少了点，但是最终查找效率不一定比全表扫描高，因为回表效率不高;

3、覆盖索引优化

EXPLAIN SELECT name,age,position FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';

4、in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描

EXPLAIN SELECT * FROM employees WHERE name in ('LiLei','HanMeimei','Lucy') AND age = 22 AND position='manager';

EXPLAIN SELECT * FROM employees WHERE (name = 'LiLei' or name = 'HanMeimei') AND age = 22 AND position ='manager';

5、like KK% 一般情况都会走索引

EXPLAIN SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager';

索引下推：

在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可

以有效的减少回表次数。使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是 'LiLei' 开头的索引之后，同时还会在索引里过滤age和 position这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据。索引下推会减少回表次数，对于innodb引擎的表索引下推只能用于二级索引，innodb的主键索引（聚簇索引）树叶子节点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果。

合适的索引

EXPLAIN select * from employees where name > 'a';

如果用name索引需要遍历name字段联合索引树，然后还需要根据遍历出来的主键值去主键索引树里再去查出最终数据，成本比全表扫描还高，可以用覆盖索引优化，这样只需要遍历name字段的联合索引树就能拿到所有结果，如下：

EXPLAIN select name,age,position from employees where name > 'a' ;

sql深入优化

Order by与Group by优化

Case1：

分析：
利用最左前缀法则：中间字段不能断，因此查询用到了name索引，从key_len=74也能看出，age索引列用在排序过程中，因为Extra字段里没有using filesort

Case 2：

分析：
从explain的执行结果来看：key_len=74，查询使用了name索引，由于用了position进行排序，跳过了
age，出现了Using filesort。

Case 3：

分析：
查找只用到索引name，age和position用于排序，无Using filesort。

Case 4：

分析：
和Case 3中explain的执行结果一样，但是出现了Using filesort，因为索引的创建顺序为
name,age,position，但是排序的时候age和position颠倒位置了。

Case 5：

分析：
与Case 4对比，在Extra中并未出现Using filesort，因为age为常量，在排序中被优化，所以索引未颠倒，不会出现Using filesort。

Case 6：

分析：
虽然排序的字段列与索引顺序一样，且order by默认升序，这里position desc变成了降序，导致与索引的排序方式不同，从而产生Using filesort。Mysql8以上版本有降序索引可以支持该种查询方式。

Case 7：

分析：

对于排序来说，多个相等条件也是范围查询

Case 8：

可以用覆盖索引优化

优化总结：
1、MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。
2、order by满足两种情况会使用Using index。
1) order by语句使用索引最左前列。
2) 使用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 ，那么使用双路排序模∙式。

索引设计原则

1、代码先行，索引后上

2、联合索引尽量覆盖条件

3、不要在小基数字段上建立索引

4、长字符串我们可以采用前缀索引

5、where与order by冲突时优先where

6、基于慢sql查询做优化

分页查询优化

 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=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

分页场景优化技巧：

首先来看一个根据自增且连续主键排序的分页查询的例子：

select * from employees limit 90000,5;

该 SQL 表示查询从第 90001开始的五行数据，没添加单独 order by，表示通过 主键排序 。我们再看表 employees ，因为主键是自增并且连续的，所以可以改写成按照主键去查询从第 90001开始的五行数据，如下：

select * from employees where id > 90000 limit 5;

再对比一下执行计划

显然改写后的 SQL 走了索引，而且扫描的行数大大减少，执行效率更高。但是，这条改写的SQL 在很多场景并不实用，因为表中可能某些记录被删后，主键空缺，导致结果不一致.所以这种改写得满足以下两个条件：主键自增且连续,结果是按照主键排序的.
根据非主键字段排序的分页查询:

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的执行计划：

原 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 DEFAULT CHARSET=utf8;
 create table t2 like t1;

 ‐‐ 插入一些示例数据
 ‐‐ 往t1表插入1万行记录
 drop procedure if exists insert_t1;
 delimiter ;;
 create procedure insert_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_t1();

 ‐‐ 往t2表插入100行记录
 drop procedure if exists insert_t2;
 delimiter ;;
 create procedure insert_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_t2();

mysql的表关联常见有两种算法

Nested-Loop Join 算法

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 时，排在前面的表并不一定就是驱动表。当使用left join时，左表是驱动表，右表是被驱动表，当使用right join时，右表时驱动表，左表是被驱动表，当使用join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表。使用了 NLJ算法。一般 join 语句中，如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ。

上面sql的大致流程如下：

1. 从表 t2 中读取一行数据（如果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 万次。这个例子里表 t2 才 100 行，要是表 t2 是一个大表，join_buffer 放不下怎么办呢？ join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t2 的所有数据话，策略很简单，

就是分段放。比如 t2 表有1000行记录， join_buffer 一次只能放800行数据，那么执行过程就是先往 join_buffer 里放800行记录，然后从 t1 表里取数据跟 join_buffer 中数据对比得到部分结果，然后清空 join_buffer ，再放入 t2 表剩余200行记录，再次从 t1 表里取数据跟 join_buffer 中数据对比。所以就多扫了一次 t1 表。被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 Nested-Loop Join 呢？如果上面第二条sql使用 Nested-Loop Join，那么扫描行数为 100 * 10000 = 100万次，这个是磁盘扫描。很显然，用BNL磁盘扫描次数少很多，相比于磁盘扫描，BNL的内存计算会快得多。因此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 一定要慎重，因

为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。

对于小表定义的明确

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 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 where B.id = A.id)
#等价于:
for(select * from A){
select * from B where B.id = A.id
}

#A表与B表的ID字段应建立索引

**count(*)查询优化**

‐‐ 临时关闭mysql查询缓存，为了查看sql多次执行的真实时间
mysql> set global query_cache_size=0;
mysql> set global query_cache_type=0;

mysql> EXPLAIN select count(1) from employees;
mysql> EXPLAIN select count(id) from employees;
mysql> EXPLAIN select count(name) from employees;
mysql> EXPLAIN select count(*) from employees;

注意：以上4条sql只有根据某个字段count不会统计字段为null值的数据行