Java进阶——MySQL之SQL优化下

我也确实“挺懒”的哈，据上篇文章出版已经快一个月了，今天来把这下出版了吧。其实主要还是太“忙”了。今天的分享主要包括：
1.分页
2.连接
3.count

1. 分页查询

在MySQL中实现分页很简单一个limit就可以搞定，例如：

select * from emp limit 20,10;

对于这么行简单的SQL，MySQL其实先读取了30行数据，然后去掉前面20行，最终输出最后的10行数据，对于分页不深，我们觉得并无不妥，但是如果我们分页太深，性能损耗是极大的。这也正是为什么 ，越往后翻页越慢的原因了。其实针对大数据量的分页，我们是有很多优化的（说实话，现在公司因为数据量太小，我都是这么写的，我同事更狠查全量直接来个1百万，我都快忘了我会优化），下面我简单分享点我之前用过的分页。
1）如果我们的业务不需要其他排序，这时候我们可以建立递增的主键，这样我们主键就是有序的，我们在做分页的时候完全可以写成这样：

select * from emp where id>20 limit 10;

现实中我们的过滤条件会打断ID的连续，我们之前是这样实现的，我们在接口中会加一个lastId的字段，用于接收上一页最大的ID，首页的时候ID为0，这样就实现了高效的分页（我们之前DB的相应是毫秒级的过200就是报警）
2）并非所有的业务都能实现上述分页，因为我们有太多的业务需要对某些字段排序，这样的话就导致上述方案无法实施，这里我再提供一种方案，这种方案没有实现质的提升，仅仅只是提升内存效率实现的性能提升。

select * from emp ORDER BY name limit 1000,10;

对于这么一条SQL，执行步骤大致如下：1.查出数据库所有数据，2.根据name字段做字典序排序，3.取出前1010行，并抛去前1000行返回。
这样在前面两个步骤，由于返回的全字段整个过程IO开销太大。所以我们优化也就在这个点了：

select * from emp e inner join (select id from emp ORDER BY name limit 1000,10)em on e.id=em.id;

2. 连接查询
   多表之间的连接查询，MySQL主要有两种算法实现：嵌套循环连接 Nested-Loop Join(NLJ) 算法、 基于块的嵌套循环连接 Block Nested-Loop Join( BNL )算法，假如现在我们有两张表t1、t2，两张表中两个字段a、b其中a有索引，b没有索引；t1表数据10000，t2表数据100。
   2.1嵌套循环连接 Nested-Loop Join(NLJ) 算法

select*from t1 inner join t2 on t1.a= t2.a;

在这种情况之下，执行是这样的，首先数据库会以数据量小的表作为驱动表也就是t2，遍历出所有数据100行，用其中的每行数据的a字段作为条件查询t1表符合要求数据，由于a存在索引，所以查t1时间可以为O(1)，这样整个过程也就扫面200次。
2.2基于块的嵌套循环连接 Block Nested-Loop Join( BNL )算法

select*from t1 inner join t2 on t1.b= t2.b;

如果用了一个没有索引的字段B作为关联，那情况就完全不一样了，第一步还是老样子，查出t2中100行数据，但是查关联数据的时候由于没有索引，数据是乱的，也不知道数据分布情况，所以只能挨个找得遍历10000次，整个过程100*10000次数有点多呀。
关于优化不用我多说了吧。
2.3另外这里提下关于in和exsits
如果in中是子查询，其实就相当于以子查询为驱动表的连接查询。exsits则相反。
5. count
count倒没什么可以说的，曼起来都满，更多的需要从架构上设计减少查询。可以提一下的是count二级索引比主键快。