MySQL优化方案对比
背景：
存在两张表，第一张表是item表，存储了人力资源分配单据的概要信息（存储了id、company_id、sn），第二张表是detail表，存储了人力资源分配单据的详细信息（存储了id、company_id、sn和person_id）
id是item表和detail表的primary key
item表存在company_id+sn的联合索引，detail表存在company_id+sn+person_id的联合索引
每个company_id对应一个公司，一个公司会存在多张人力资源分配单据，每张单据都有唯一的sn号
一张单据在item表里有唯一的一行，存储了id+company_id+sn信息
一张单据在detail表里会有n行，n=penson_id的数量，person_id可以有多个，但不能重复；但不同单据间可以有重复的person_id
概要页只会展示item表信息
company_id是唯一的
在同一company_id下，person_id和sn是唯一的；在不同company_id下，person_id和sn不唯一
同一company_id下只会存在于一张表，但一张表内可能存在多个company_id
某公司的项目人力分配单据数量特别大（十万级别）
补充一点：item表和detail表不能合并，因为item表存储了简洁信息，用于概要展示，detail表除了上述四个字段，其实还有很多信息，例如人名、年龄、工种等等，detail表用于每张单据的详情展示
再补充一点：可能会有同事搜索几十个人的场景，也就是IN里有几十个person_id
查询需求：在某公司的概要页（例如company_id = 123的公司），需要支持根据单据中是否存在某个人或某些人（当搜索两个人时，只要有一个人在该单据中，该单据就会被展示出来）进行查询。
具体举例：item表如下：
detail表如下：
按照上述存储情况，在company_id为123的公司搜索item表，要求与item表对应的detail表任意包含了person_id = P10001或P10002的人，则搜索语句方案一为：
SELECT * FROM item WHERE company_id = 123 AND EXISTS (SELECT 1 FROM detail
WHERE detail.company_id = item.company_id
AND detail.sn=item.sn
AND detail.person_id in (P10001, P10002));
搜索结果是拿出item表里id = 1 和 2的两行展示在概要页
由于company_id = 123这家公司的单据量特别大（特别大可以理解为item表存了十万行，即有十万张人力资源分配单，每张人力资源分配单上有五百个person_id，即该单据在detail表里存了五百行，因此十万张人力资源分配单会在detail表上存100000 * 500 = 50000000，共五千万条），因此存在深分页的问题，以下为两个优化方案：
优化方案一：
SELECT * FROM item WHERE company_id = 123 AND id IN(select distinct item.id from item
LEFT JOIN detail
ON item.company_id = detail.company_id
AND item.sn = detail.sn
WHERE
item.company_id= 123
AND detail.person_id in (123, 321));
优化方案二：
SELECT * FROM item WHERE company_id = 123 AND sn IN (
SELECT DISTINCT sn FROM detail
WHERE detail.company_id = 123
AND detail.person_id in (123, 321));
希望对比出三个方案哪个更好，并给出明确理由
最好可以造数据用Explain解释，只要回答得有理有据，博主会追加悬赏！
基于你得sql之上我感觉是优化了一下，共勉：
SELECT
i.*
FROM
item i
WHERE
i.company_id = 123
AND (
SELECT
count(1)
FROM
detail d
WHERE
d.company_id = 123
AND d.sn = i.sn
AND d.person_id IN ('P1001', 'P1002')
) > 0
可能理解的不对，但有个问题，给出的例子里面，item表的字段在detail表中都存在。有点类似于数据冗余了，可否直接从detail表中入手？
三种方式区别不大、有考虑将联合索引变更为聚簇索引吗
我认为应该是 方案1 最优，在有索引加持下，总体来说都差不多，耗时： 方案1 < 方案3 < 方案2
下面先用 explain 来解释 （本地建的表，item 表 19万， detail表 5450万 数据， 并建立索引 index0）
这里主要关注几个点：type（访问类型），key（引用索引）， extra（附加信息）
下面分析博主给的3条sql 的explain，
1、首先分析 访问类型（type），
这里补充一下知识点：
ref：表示索引访问，会将索引跟参考值比较（如这里的 123），找到所有符合条件的行；
eq_ref: 同上，只是最多会返回一行
其它：all （全表扫描）， index（扫描全索引），range（扫描固定范围 内的索引），const、system （可以理解成常量）
它们的执行耗时是： all > index > range > ref > eq_ref > const、 system (当然也不完全是，还要根据具体场景，有时候 index > all)
key字段表示的是 该查询使用的索引。
从 type 上看都差不多，但是 方案2 中间还有一个 eq_ref 操作，猜测可能会 有一点额外耗时，但是相差不大。其他访问都使用了索引 index0。
2、从extra 来分析
这里再补充下知识点：
useing where： 该查询使用了where 条件
useing index：只使用索引数据，不需要回表查询
useing temporary：使用临时表，如 distinct、order by 等，是一个常见的优化点。
从这点来看的话， 方案1 优于 方案3，因为方案3 使用到了 临时表（useing temporary）
一般还要关注一个指标 rows，表示为了找到满足条件的行，估计需要扫描多少行，这里都一样，就不分析了。
理论上得出的结论是： 方案1 最优，其次是 方案3，最后是 方案2
实际测试：
测试环境： win10 + mysql 5.5（mysql 关闭内存）
数据量：item 表 19万， detail表 5450万 数据，company_id = ‘123’ 在两张表中分别是 10 w、5000w 行
分别把 3 条sql 跑 10次，取平均值，结果如下：
方案1： 27ms， 方案2：35ms， 方案3： 31ms
把where 条件增加到 50个，查询时间变化不大。
综上：方案1最优。
我觉得方案1最优，给我肯定不会写成方案2，方案3。因为方案1中where中detail三个字段都有索引，并且exists只要有一条数据满足就会返回了,in还要查所有满足条件的。还有一点比较奇怪的是detail中居然没有记item的id,如果用item的id来做关联，我觉得会更快
可以先查出detail符合查询条件的company_id和sn（company_id和sn加索引）,代码去重，然后根据company_id和sn去item表查符合条件的数据（company_id和sn加索引），这样应该是对数据库影响应该很小的
从例子来看detail表中拥有item表的所有信息，而你需要item表的数据，但是参数需要detail表，为啥不直接查detail表呢
SELECT * FROM item WHERE company_id = 123 AND EXISTS (SELECT 1 FROM detail
WHERE detail.company_id = item.company_id
AND detail.sn=item.sn
AND detail.person_id in (P10001, P10002));
不是很明白为什么一定要在条件WHERE后面再写查询语句，但正常是不会这样写的，原因你可以了解下编译器在编译SQL语句时的顺序，网上有资料，这里不阐述。
你可以试试下面写法，应该会降低服务器的计算开支：
select DISTINCT a.* from item a
left outer join detail b on a.company_id=b.company_id and a.sn=b.sn
where a.company_id=123 and b.person_id in (123,321)
这查询结果应该跟你的优化方案一 一样，速度可以看看有没有优化一点
目前测试了一下，数据库 mysql 5.7 ，操作系统 centos 7 （虚拟机）， 固态硬盘 金士顿KC2500, 跑了一上午 没有插入太多数据，detail表中插入了 480多万条数据， item表中 28条数据。
测了4种情况，综合下来，还是方案3 最优。
具体的测试结果，图太多了，就不一一展示了。
因为我对MySQL调优，也在学习中。但是执行下来，三种方案，执行下来都是只有 0.001s，所以测试结果是通过 explain 综合推断而出。
如果，有不一样的地方，请指正。
占个板凳
从运算时间效率上来看，方案一可能更好一些。
按描述， 后 IN （） 里的数据应该不大吧？
IN 适合数据 IN（） 内数据量少的查询。
另外， 第二个查询语句 的 detail 没有直接的约束， 查询效率应该低于 第三个查询语句
从表面上看， 应该是优化方案二 更优。
可以网上找开源库的随机数据，我记得github上面有一个，然后生成大量的数据进行测试，不然很多时候光说说还是没什么用的，实践最重要，更何况你们这个数据库要投入生产
是否可以使用mysql + Elasticsearch 重新设计一下查询方案呢？
目前我没环境可以造这么大量的数据进行实机测试,而且综合评估的话还得看磁盘读取效率、内存速度等,所以我还是保留上一个问题中回答的观点,使用exists,因为它只要扫到任意一行满足条件的就会true,不会把所有记录都扫到
ui