mysql索引详解及优化

对索引其实本身有一定程度的研究，但是最近看了一些讲解索引的视频，发现还是有些知识盲区，所以干脆写一篇进行更好的总结
索引
索引是帮助MySQL高效获取数据的排好序的数据结构（容易忽略的点：排好序）（形象点就是教科书的目录）

索引存储在文件里（也就是说有IO操作）

索引结构：

这里说说在几种数据结构中，mysql为什么选择hash，B+Tree

1、二叉树
2、红黑树
3、hash
4、BTree
首先，如果数据没有索引，那么我们读取数据是这样的

上面我们发现读取数据特别耗时，那有没有比较节时的数据结构，我们可以看看二叉树

上面虽然优化了，但是mysql为什么选择 B+Tree，因为每次IO都是只读取树的一个节点，二叉树的节点是单节点，这样很浪费时间

二叉树与红黑树的比较

从上面我们发现，红黑树相比较于二叉树又进步了一些，但红黑树还是有些问题：那就是数据量大的话，红黑树的深度会很深，也就是说深度不可控，这样一来查找数据还是会很耗时

HASH

从上面我们发现，相比较于红黑树，hash可以固定“深度”，且映射到磁盘存储引用，这样查找数据直接告诉磁盘数据在哪，查找数据也挺快的，但是 hash 还是有些不足：那就是不能范围查找，比如我们查找Col1>1的数据，当然如果我们查询操作很少的话，我们也可以选择hash数据结构，因为它查找数据挺快的，这也是mysql的索引方法除了B+Tree还有hash

从上面看，我们发现BTree又进步了一些，查询速度提高，存储容量也没影响到。当然有人可能会这样想，那我们为什么不把数据全部都存在一个节点，这样深度不就是1了吗?

当然不行了！java拿取数据一般是这样的：java程序–>CPU—>内存---->硬盘，而内存与硬盘的交互是有大小限制的，是一页数据4k左右，所以不能把所有数据都放在一个节点来获取，一般来说节点会尽量预存4K容量。

看到这里，我们知道（4K=节点；节点=小节点*小节点的容量）一个节点是4K，而节点内有几个小节点，那么也就是说，只要我们每个的小节点的data容量越小，那么可以存的节点也就可以更多。

B+Tree通过把data不放在非叶子节点来增加度（小节点），一般会一百个以上使得深度是3~5，从而减少查询次数。并且，叶子节点之间会有指针，数据又是递增的，这使得我们范围查找可以通过指针连接查找，而不再从上面节点往下一个个找。

B+树和B树的区别

B+树的非叶子结点只包含导航信息，不包含实际的值，所有的叶子结点和相连的节点使用链表相连，便于区间查找和遍历。
B+ 树的优点在于：

IO次数更少：由于B+树在内部节点上不包含数据信息，因此在内存页中能够存放更多的key。 数据存放的更加紧密，具有更好的空间局部性。因此访问叶子节点上关联的数据也具有更好的缓存命中率。
遍历更加方便：B+树的叶子结点都是相链的，因此对整棵树的遍历只需要一次线性遍历叶子结点即可。而且由于数据顺序排列并且相连，所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。

但是B树也有优点，其优点在于，由于B树的每一个节点都包含key和value，因此经常访问的元素可能离根节点更近，因此访问也更迅速。下面是B 树和B+树的区别图:

为什么MySQL选择B+树做索引

1、
B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。

2、B+树的查询效率更加稳定：由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

3、B+树更便于遍历：由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。

4、B+树更适合基于范围的查询：B树在提高了IO性能的同时并没有解决元素遍历的我效率低下的问题，正是为了解决这个问题，B+树应用而生。B+树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作或者说效率太低。

MyISAM索引文件和数据文件是分离的，文章一开始也介绍了，数据.MYD+结构.frm+索引.MYI三个文件

那myisam的索引是什么样的？
主键索引与普通索引是两棵独立的索引B+树，通过索引列查找时，先定位到B+树的叶子节点，再通过指针定位到行记录。本质都是非聚簇索引，数据行和索引分开存储。
InnoDB索引实现(聚集)
1、数据文件本身就是索引文件
2、表数据文件本身就是按B+Tree组织的一个索引结构文件
3、聚集索引-叶节点包含了完整的数据记录
4、为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？
5、为什么非主键索引结构叶子节点存储的是主键值？(一致性和节省存储空间)

解释：
InnoDB引擎使用聚集索引，数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）
1、如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。如上图所示
这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。
2、 如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置：
此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

在使用InnoDB存储引擎时，如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键。

InnoDB 存储引擎采用了聚集（clustered）的方式，因此每张表的存储都是按主键的顺序进行存放。如果没有显式地在表定义时指定主键，InnoDB存储引擎会为每一行生成一个6字节的ROWID，并一次作为主键。

mysql 在频繁的更新、删除操作，会产生碎片。而含碎片比较大的表，查询效率会降低。此时需对表进行优化，这样才会使查询变得更有效率。
真实innodb数据页
页是MySQL管理存储空间的基本单位，一个页的大小一般是16KB，记录其实是被存放在页中的，如果记录占用的空间太大还可能造成行溢出现象，这会导致一条记录被分散存储在多个页中。
页的本质就是一块16KB大小的存储空间，InnoDB为了不同的目的而把页分为不同的类型，其中用于存放记录的页也称为数据页，我们先看看这个用于存放记录的页长什么样。数据页代表的这块16KB大小的存储空间可以被划分为多个部分，不同部分有不同的功能，各个部分如图所示：

名称 中文名 占用空间大小 简单描述
File Header 文件头 38字节 一些描述页的信息
Page Header 页头 56字节 页的状态信息
Infimum + Supremum 最小记录和最大记录 26字节 两个虚拟的行记录
User Records 用户记录 不确定 实际存储的行记录内容
Free Space 空闲空间 不确定 页中尚未使用的空间
Page Directory 页目录 不确定 页中的记录相对位置
File Trailer 文件结尾 8字节 校验页是否完整

在页的7个组成部分中，我们自己存储的记录会按照我们指定的行格式存储到User Records部分。但是在一开始生成页的时候，其实并没有User Records这个部分，每当我们插入一条记录，都会从Free Space部分，也就是尚未使用的存储空间中申请一个记录大小的空间划分到User Records部分，当Free Space部分的空间全部被User Records部分替代掉之后，也就意味着这个页使用完了，如果还有新的记录插入的话，就需要去申请新的页了，这个过程的图示如下：

头信息和数据record
重点说记录头信息的作用，所以为了大家理解上的方便，我们只在page_demo表的行格式演示图中画出有关的头信息属性以及c1、c2、c3列的信息（其他信息没画不代表它们不存在啊，只是为了理解上的方便在图中省略了～），简化后的行格式示意图就是这样：

试着向page_demo表中插入几条记录：

delete_mask

这个属性标记着当前记录是否被删除，占用1个二进制位，值为0的时候代表记录并没有被删除，为1的时候代表记录被删除掉了。

what？被删除的记录还在页中么？是的，你看到的并不是它实际上的样子，你以为它删除了，可它还在真实的磁盘上[摊手]（忽然想起冠希～），只是打了个标记表示这条记录删除了而已～

小贴士：
这些被删除的记录之所以不立即从磁盘上移除，是因为移除它们之后把其他的记录在磁盘上重新排列需要性能消耗，
所以只是打个删除标记而已，而且这部分存储空间之后还可以重用，也就是说之后如果有新记录插入到表中的话，
可能把这些被删除的记录占用的存储空间覆盖掉。
如果你想彻底的从磁盘上移除这些被删除的记录，可以使用这个语句：

optimize table 表名;

执行这个命令后服务器会重新规划表中记录的存储方式，把被标记为删除的记录从磁盘上移除。

一张表中可以有成千上万条记录，一个页只有16KB，所有的数据页其实是一个双链表，就像这样：

所以当插入数据，一个页溢出后会出现分页以及数据结构的各种改变操作

联合索引的底层存储结构
如索引结构为<员工id,员工名称,日期>
员工id:1001
员工名称：staff
日期：1996-08-03

会依次比较员工id,员工名称,日期,先比较id，如果小于的，则一定在左边，如果等于再看第二项，名称，如果小于则也在左边，依次找到最后的左走还是右走。

最左匹配原则
1.简单说下什么是最左匹配原则
顾名思义：最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。
例如：b = 2 如果建立(a,b)顺序的索引，是匹配不到(a,b)索引的；但是如果查询条件是a = 1 and b = 2或者a=1(又或者是b = 2 and b = 1)就可以，因为优化器会自动调整a,b的顺序。再比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，因为c字段是一个范围查询，它之后的字段会停止匹配。
以前面图为例
可以看到员工id的值是有顺序的，而员工名称的值是没有顺序的，因为是第二个索引，被第一个索引所安排了。所以b = 2这种查询条件没有办法利用索引，因为联合索引首先是按a排序的，b是无序的。

回表查询
回表查询指的是先通过非聚集索引找到主键，然后再通过聚集索引，根据主键找到数据

索引覆盖
explain的输出结果Extra字段为Using index时，能够触发索引覆盖，只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。
例子：

create table user (
id int primary key,
name varchar(20),
sex varchar(5),
index(name)
)engine=innodb;
第一个SQL语句：　　

能够命中name索引，索引叶子节点存储了主键id，通过name的索引树即可获取id和name，无需回表，符合索引覆盖，效率较高。
第二个SQL语句：

能够命中name索引，索引叶子节点存储了主键id，但sex字段必须回表查询才能获取到，不符合索引覆盖，需要再次通过id值扫码聚集索引获取sex字段，效率会降低。
但如果把(name)单列索引升级为联合索引(name, sex)就不同了。
create table user (
id int primary key,
name varchar(20),
sex varchar(5),
index(name, sex)
)engine=innodb;

都能够命中索引覆盖，无需回表。

索引优化

那么知道索引结构，对下面的说法理解就不难了
1、应尽量避免在where子句中使用！=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

2、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

3、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0

4、尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or num=20

可以这样查询：

select id from t where num=10

union all

select id from t where num=20

5、下面的查询也将导致全表扫描：(不能前置百分号)

select id from t where name like ‘?c%’

若要提高效率，可以考虑全文检索。

6、in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

7、如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

select id from t where num=@num

可以改为强制查询使用索引：

select id from t with(index(索引名)) where num=@num

8、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2=100

应改为:

select id from t where num=100*2

9、应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name,1,3)=’abc’–name以abc开头的id

select id from t where datediff(day,createdate,’2005-11-30′)=0–’2005-11-30′生成的id

应改为:

select id from t where name like ‘abc%’

select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′

10、不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
注：插入数据的时候同时也会修改索引，如果它已经按a创建了索引，a除以2这个索引物理上并没有存在，自然走不了索引

11、在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使 用，并且应尽可能的让字段顺序与索引顺序相一致。

12、不要写一些没有意义的查询，如需要生成一个空表结构：

select col1,col2 into #t from t where 1=0

这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：

create table #t(…)

13、很多时候用 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)

in的应用原理

在select * from A where id in(select id from B); 中，in()中的子查询只执行一次，走A表索引，它查询出B表中的所有ID值并缓存起来，即将B表的所有值作为key进行在A表查询索引；之后，在内存中检查A表的id是否与B表中的id值相等，如果相等则则将A表的记录加入到结果集中，直到遍历完A表中的所有记录。

它查询的过程类似于一下过程

Array A={select * from A};
Array B={select id from B};
for (int i=0;i<A.length;i++){
     for (int j=0;j<B.length;j++){
        if (A[i].id == B[j].id){ 
           resultSet.add(A[i]);
              break;
           }
    }
}

而在select * from A where exists (select 1 from B where A.id=B.id);之中 exists 后的子查询他是不返回列表的值的.只是返回一个ture或false的结果,其运行方式是先运行主查询一次 再去子查询里查询与其对应的结果 如果是ture则输出,反之则不输出.再根据主查询中的每一行去子查询里去查询.
exists()会执行A.length次，它并不缓存exists()结果集，因为exists()结果集的内容并不重要，重要的是其内查询语句的结果集空或者非空，空则返回false，非空则返回true。
它的查询过程类似于以下过程：

Array A=(select * from A);  
for(int i=0;i<A.length;i++) {  
   if(exists(A[i].id) {  //执行select 1 from B where B.id=A.id是否有记录返回  
       resultSet.add(A[i]);  
   }  
}  
return resultSet;

当B表比A表数据大时适合使用exists()，因为它没有那么多遍历操作，只需要再执行一次查询就行，而且走的是B表的索引。
如：A表有10000条记录，B表有1000000条记录，那么exists()会执行10000次去判断A表中的id是否与B表中的id相等。
如：A表有10000条记录，B表有100000000条记录，那么exists()还是执行10000次，因为它只执行A.length次，可见B表数据越多，越适合exists()发挥效果。
再如：A表有10000条记录，B表有100条记录，那么exists()还是执行10000次，还不如使用in()遍历10000*100次，因为in()是在内存里遍历比较，而exists()需要查询数据库，我们都知道查询数据库所消耗的性能更高，而内存比较很快

结论：IN 是把外表和内表作hash连接，而 EXISTS 是对外表作loop循环，每次loop循环再对内表进行查询。一直以来认为 EXISTS 比IN 的效率高的说法是不准确。如果查询的两个表大小（数据量）相当，EXISTS 和 IN 的效率差别不大

14、并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段 sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

15、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。

16.应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

17、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

18、尽可能的使用 /n 代替 /n ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

19、任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

20、尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

21、避免频繁创建和删除临时表，以减少系统表资源的消耗。

22、临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使 用导出表。

23、在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

24、如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

25、尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

26、使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

27、与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时 间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

28、在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

29、尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

30、尽量避免大事务操作，提高系统并发能力。

31、使用合理的分页方式以提高分页的效率
（limt用法：
查询8条数据，索引从5到12，第6条记录到第13条记录：select * from t_user limit 5,8;即第一个参数是起始位置，第二个参数是总共查询的长度）

如 select * from table limit 0,10 这个没有问题 当 limit 200000,10 的时候数据读取就很慢

原因本质： 1）limit语句的查询时间与起始记录（offset）的位置成正比 2）mysql的limit语句是很方便，但是对记录很多:百万，千万级别的表并不适合直接使用。

例如： limit10000,20的意思扫描满足条件的10020行，扔掉前面的10000行，返回最后的20行，问题就在这里。 ​ LIMIT 2000000, 30 扫描了200万+ 30行，怪不得慢的都堵死了，甚至会导致磁盘io 100%消耗。 ​ 但是: limit 30 这样的语句仅仅扫描30行。

优化的方法如下：可以取前一页的最大行数的id（通过索引定位），然后根据这个最大的id来限制下一页的起点。比如此列中，上一页最大的id是866612。sql可以采用如下的写法：

select id,name from table_name

where id> 866612 limit 20

分页查询很慢，怎么优化

当需要从数据库查询的表有上万条记录的时候，一次性查询所有结果会变得很慢，特别是随着数据量的增加特别明显，这时需要使用分页查询。对于数据库分页查询，也有很多种方法和优化的点。
示例：针对查询偏移量的测试：

select * from orders_history where type=8 limit 100,100;	
select * from orders_history where type=8 limit 1000,100;	
select * from orders_history where type=8 limit 10000,100;	
select * from orders_history where type=8 limit 100000,100;	
select * from orders_history where type=8 limit 1000000,100;

三次查询时间如下：

三次查询时间如下：

查询100偏移：25ms 24ms 24ms

查询1000偏移：78ms 76ms 77ms

查询10000偏移：3092ms 3212ms 3128ms

查询100000偏移：3878ms 3812ms 3798ms

查询1000000偏移：14608ms 14062ms 14700ms

随着查询偏移的增大，尤其查询偏移大于10万以后，查询时间急剧增加。
原因。：limit是全表扫描，这种分页查询方式会从数据库第一条记录开始扫描，所以越往后，查询速度越慢，而且查询的数据越多，也会拖慢总查询速度。

使用子查询优化
这种方式先定位偏移位置的 id，然后往后查询，这种方式适用于 id 递增的情况。

select * from orders_history where type=8 limit 100000,1;	
select id from orders_history where type=8 limit 100000,1;	
select * from orders_history where type=8 and 	
id>=(select id from orders_history where type=8 limit 100000,1)	
limit 100;	
select * from orders_history where type=8 limit 100000,100;

4条语句的查询时间如下：

第1条语句：3674ms

第2条语句：1315ms

第3条语句：1327ms

第4条语句：3710ms

针对上面的查询需要注意：

比较第1条语句和第2条语句：使用 select id 代替 select * 速度增加了3倍

比较第2条语句和第3条语句：速度相差几十毫秒

比较第3条语句和第4条语句：得益于 select id 速度增加，第3条语句查询速度增加了3倍

这种方式相较于原始一般的查询方法，将会增快数倍

使用 id 限定优化
这种方式假设数据表的id是连续递增的，则我们根据查询的页数和查询的记录数可以算出查询的id的范围，可以使用 id between and 来查询：

select * from orders_history where type=2 	
and id between 1000000 and 1000100 limit 100;

查询时间：15ms 12ms 9ms

这种查询方式能够极大地优化查询速度，基本能够在几十毫秒之内完成。限制是只能使用于明确知道id的情况，不过一般建立表的时候，都会添加基本的id字段，这为分页查询带来很多便利。

还可以有另外一种写法：

select * from orders_history where id >= 1000001 limit 100;

当然还可以使用 in 的方式来进行查询，这种方式经常用在多表关联的时候进行查询，使用其他表查询的id集合，来进行查询：

select * from orders_history where id in	
(select order_id from trade_2 where goods = 'pen')	
limit 100;

这种 in 查询的方式要注意：某些 mysql 版本不支持在 in 子句中使用 limit。

使用临时表优化
这种方式已经不属于查询优化，这儿附带提一下。

对于使用 id 限定优化中的问题，需要 id 是连续递增的，但是在一些场景下，比如使用历史表的时候，或者出现过数据缺失问题时，可以考虑使用临时存储的表来记录分页的id，使用分页的id来进行 in 查询。这样能够极大的提高传统的分页查询速度，尤其是数据量上千万的时候。