Mysql ：sql优化_sql block nested loop-CSDN博客

本文链接：https://blog.csdn.net/shangmingtao/article/details/78809133

目录
表结构数据
估计检查较多时可能不使用索引
多列索引最左前缀select尽量覆盖索引字段
多列索引where子句条件查询
不要在索引列使用表达式或做运算
order by用索引排序
JOIN原理
JOIN使用索引
OR要慎用
like xx要避免使用
select count from table要避免使用会导致全表搜索
group by distinct 未完成
参考文章

表结构&数据

CREATE TABLE `test` (
  `id` varchar(11) NOT NULL,
  `age` int(11) NOT NULL,
  `age2` int(11) NOT NULL,
  `age3` int(11) NOT NULL,
  `name` varchar(32) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_age` (`age`,`age2`,`age3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> select * from test;
+----+-----+------+------+-------+
| id | age | age2 | age3 | name  |
+----+-----+------+------+-------+
| 11 |   1 |    1 |    9 | name1 |
| 23 |   1 |    2 |    8 | name2 |
| 34 |   2 |    3 |    7 | name3 |
| 45 |   2 |    4 |    6 | name4 |
| 56 |   4 |    5 |    5 | name5 |
| 67 |   4 |    6 |    4 | name1 |
+----+-----+------+------+-------+
6 rows in set (0.00 sec)

估计检查较多时可能不使用索引

这里写图片描述

12中不同点在于查询的值不一样,从表中数据内容可以看到>1的数据占到了表中数据2/3.但是>4的数据为0;所以执行1sql时mysql认为估计查询行数较多.这里到底多少算多我也不很清楚.
另外说一句,这里的> , < 在mysql实现时实际就是排序,所以2中type = range

多列索引最左前缀&select尽量覆盖索引字段

这里写图片描述

123符合最左前缀,456不符合最左前缀.
比对123中的rows字段,1中的rows=2,原因是age=4的条数为两条.但是大家实际使用要注意这个字段是个估计值,并不是完全准确;
从type字段我们可以看出,456也使用了索引,和123的区别是type一个值为ref一个为index.刚刚不是说456不符合最左前缀嘛,为啥还会使用索引呢,原因是在456中我们查询的所有字段(id , age , age2 , age3)均为index_age中的字段,所以虽然where语句不能使用索引(不满足最左前缀,不过where所有列均为多列索引中的列),但是select的字段我们可以通过扫描索引完成.同时我们看到extra中也提示我们使用了索引.如果还不明白的话,我们再来看789,name字段类型varchar(32)且不再索引index_age中,然后再查询所有索引中所有字段+name,此时由于多了一个name字段不在index_age中,type变为ALL,key变为null.extra中也不在出现using index.

通过以上例子我们总结一下,第一:使用多列索引时要符合最左前缀. 第二:查询时不要使用select * ,要尽量查询索引字段.

多列索引where子句条件查询

这里写图片描述

通过比较1234的不同,发现只有2没有用到索引.再讲原因之前有个概念要讲清楚,第一个如果创建了一个多列索引(a,b,c),那么相当于我们创建了三个索引(a),(a,b),(a,b,c).但是这三个索引可以在一个sql中使用.实际上在本例中1用到了(age,age2,age3)的索引,3用到了(age)的索引,4用到了(age,age2)的索引.2用不到索引.规律的话就是从使用条件查询的列开始之后的列(包括使用条件查询的列)都不会用到索引.

不要在索引列使用表达式或做运算

这里写图片描述

13分别用了运算符和表达式,所以type为ALL,全表数据扫描,不会用到索引

order by用索引排序

mysql有两种排序方式,分别是index和filesort,index是利用索引排序,filesort是全表二分查找我记得.相比较来讲index效率更高

这里写图片描述

12对比发现虽然排序字段都是age,但是一个是select age 一个是select age,name. 2中没有用到索引排序是因为查询字段中包含非索引列.
13对比我们发现虽然select的都是age列,但是3中排序字段是两个,一个是PK主键,一个是age.mysql每条sql只能用一个索引,所以此时age的排序用了索引,id的排序就只能用filesort方式了.

总结:1.查询字段为非索引列时不能用到索引; 2.如果排序字段分别存在于两个索引中只有一个可以用到索引排序

这里写图片描述

通过4我们可以看到同时使用ASC和DESC是不能使用索引的.
通过45对比或者46对比我们发现虽然同时使用了ASC和DESC,但是如果where条件中将其中一个排序字段转换为常量的话是可以使用索引排序的
56不同点对比:type,ref,rows不同.通过观察我们发现5中ref=const证明用到了多列索引(age,age2,age3)中的(age)做了等值判断,也因此rows=2而不是6.同时extra中没有using filesort,证明5中还用到了(age,age2)进行排序.而6中虽然用了索引排序,但是age2=4的等值判断并没有用到索引.原因是where age2 = 4不符合(age,age2,age3)的最左前缀.
56相同点对比,发现都用到了using where.(最开始我对using where也很不理解,一直以为只要用到using where就不会使用索引,但实际情况不是这样的.Using where只是过滤元组，和是否读取数据文件或索引文件没有关系.参见:MySQL查询优化概念辨析—Using where 和 Using index using where这说明服务器在存储引擎收到行后将进行过滤。有些where中的条件会有属于索引的列，当它读取使用索引的时候，就会被过滤，所以会出现有些where语句并没有在extra列中出现using where这么一个说明。参见:mysql explain中的type列含义和extra列的含义).5中过滤是通过读取索引时过滤的,而6中的存储引擎收到行后进行的过滤.

这里写图片描述

7:当where和order by共同符合最左前缀时,是可以使用多列索引排序的.
78对比:当where和order by分别用到两个索引列时,是不能使用多列索引排序的.但是where子句可以用到索引做等值查找
89对比:当=左边有表达式时,mysql不在去判断age和id是否符合最左前缀,而是直接忽略掉了age值判断id是否有索引,这里id是PK,所以直接用id进行索引排序.
8,10对比:当order by有表达式时和89对比一样,没有试图判断是否符合最左前缀,而是使用age做了索引等值查找,排序则为filesort
11:不会用到索引进行排序,因为age>4阻断了最左前缀,所以会选择filesort.
12:where中的age和order by中的age3不符合最左前缀,不会使用索引排序.
13:order by 中的age和age3不符合最左前缀,不会使用索引排序.

JOIN原理

此部分转载自 : MySQL JOIN原理
MySQL是只支持一种JOIN算法Nested-Loop Join（嵌套循环链接），不像其他商业数据库可以支持哈希链接和合并连接，不过MySQL的Nested-Loop Join（嵌套循环链接）也是有很多变种，能够帮助MySQL更高效的执行JOIN操作：

(1).Simple Nested-Loop Join

这里写图片描述

这个算法相对来说就是很简单了，从驱动表中取出R1匹配S表所有列，然后R2，R3,直到将R表中的所有数据匹配完，然后合并数据，可以看到这种算法要对S表进行RN次访问，虽然简单，但是相对来说开销还是太大了

(2).Index Nested-Loop Join

这里写图片描述

索引嵌套联系由于非驱动表上有索引，所以比较的时候不再需要一条条记录进行比较，而可以通过索引来减少比较，从而加速查询。这也就是平时我们在做关联查询的时候必须要求关联字段有索引的一个主要原因。
这种算法在链接查询的时候，驱动表会根据关联字段的索引进行查找，当在索引上找到了符合的值，再回表进行查询，也就是只有当匹配到索引以后才会进行回表。至于驱动表的选择，MySQL优化器一般情况下是会选择记录数少的作为驱动表，但是当SQL特别复杂的时候不排除会出现错误选择。
在索引嵌套链接的方式下，如果非驱动表的关联键是主键的话，这样来说性能就会非常的高，如果不是主键的话，关联起来如果返回的行数很多的话，效率就会特别的低，因为要多次的回表操作。先关联索引，然后根据二级索引的主键ID进行回表的操作。这样来说的话性能相对就会很差。

(3).Block Nested-Loop Join

这里写图片描述

在有索引的情况下，MySQL会尝试去使用Index Nested-Loop Join算法，在有些情况下，可能Join的列就是没有索引，那么这时MySQL的选择绝对不会是最先介绍的Simple Nested-Loop Join算法，而是会优先使用Block Nested-Loop Join的算法。
Block Nested-Loop Join对比Simple Nested-Loop Join多了一个中间处理的过程，也就是join buffer，使用join buffer将驱动表的查询JOIN相关列都给缓冲到了JOIN BUFFER当中，然后批量与非驱动表进行比较，这也来实现的话，可以将多次比较合并到一次，降低了非驱动表的访问频率。也就是只需要访问一次S表。这样来说的话，就不会出现多次访问非驱动表的情况了，也只有这种情况下才会访问join buffer。
在MySQL当中，我们可以通过参数join_buffer_size来设置join buffer的值，然后再进行操作。默认情况下join_buffer_size=256K，在查找的时候MySQL会将所有的需要的列缓存到join buffer当中，包括select的列，而不是仅仅只缓存关联列。在一个有N个JOIN关联的SQL当中会在执行时候分配N-1个join buffer。

JOIN使用索引

我们建立一张新的表,表结构和数据如下:

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(32) NOT NULL,
  `age` int(11) NOT NULL,
  `password` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> select * from user;
+----+-------+-----+----------+
| id | name  | age | password |
+----+-------+-----+----------+
|  0 | name0 |   0 | 0        |
|  1 | name1 |   1 | 1        |
|  2 | name2 |   2 | 2        |
|  3 | name3 |   3 | 3        |
|  4 | name4 |   4 | 4        |
|  5 | name5 |   5 | 5        |
|  6 | name6 |   6 | 6        |
|  7 | name7 |   7 | 7        |
|  8 | name8 |   8 | 8        |
|  9 | name9 |   9 | 9        |
+----+-------+-----+----------+
10 rows in set (0.00 sec)

这里写图片描述

1中test表作为驱动表,user表作为非驱动表.mysql优先选择了行数少的为驱动表.此时user表id为PK.所以mysql选择Block Nested-Loop Join方式,查出test表中所有记录,逐条到user表中通过user表中主键索引进行匹配.每次可以匹配到一条.
2中user表作为驱动表,test表作为非驱动表.当有where条件时mysql优先选择了条数多的为驱动表.依然通过Block Nested-Loop Join,但是这次不是查出驱动表所有记录,而是通过驱动表(user表)的索引(id)仅查出一条记录到非驱动表中(test表)通过test表中的索引age匹配到唯一一条记录.这种方式效率最高.原因是仅进行了两次索引查询就完成了.
3中那么字段不管在user表还是test表都没有索引,这是mysql采用的就是Block Nested-Loop Join方式,讲test表中记录加入到join buffer中.分批到user表中匹配.

这里写图片描述

4中用了left join意味着我们自己指定了驱动表(user)和非驱动表(test),此时依然采用Index Nested-Loop Join,原理同1中的一样.查出user表中所有记录,逐条到test表中通过test表中的索引(age)进行匹配,每次匹配到一条.
5加上where条件后,同2中原理一样.先通过user表中索引找到id=3的一条数据,然后用这条数据到test表中通过test表中索引(age)进行匹配.最终匹配到一条.

结论:
1.驱动表是否有索引仅在有where条件时起作用.如果没有where条件,即便有索引也会对驱动表所有数据进行访问且不会用到索引.
2.非驱动表关联字段一定要有索引.而且这里的索引尽量要保证选择性=1,如果做不到选择性大于1就会出现如下情况(下图).这里效率相差 = 驱动表数据总条数(1) * 本次查询非驱动表重复数据条数(2);
这里写图片描述