mysql之同一个字段做交集查询优化: 最近做了一个这样的需求，对同一个字段做条件筛选，开始比较纠结，怎么做效率更高，主要有如下两种方案：

通过多个 exists对同一个字段(这里正好是等值查询)做交集查询:
SELECT `id`,`name`,`cover_image`,`price`,`score`,`remark`,`brand`,`subclass`,`category_one`,`category_two`,`detail` FROM `d_products` WHERE (kind = '养老服务' and (name like '%水%' or detail like '%水%')) AND (EXISTS(SELECT id FROM d_product_attributes WHERE `d_product_attributes`.`product_id`  = `d_products`.`id` AND `d_product_attributes`.`attribute_value` = '卫浴') and EXISTS(SELECT id FROM d_product_attributes WHERE `d_product_attributes`.`product_id`  = `d_products`.`id` AND `d_product_attributes`.`attribute_value` = '介护老人') and EXISTS(SELECT id FROM d_product_attributes WHERE `d_product_attributes`.`product_id`  = `d_products`.`id` AND `d_product_attributes`.`attribute_value` = '防滑')) ORDER BY id desc LIMIT 10;


explain结果：
explain结果：
id   | select_type          | table                   | partitions    | type | possible_keys           |  key                 | key_len | ref  | rows | filtered | Extra
1	   PRIMARY	            d_japan_products		                    ref	    index_kind	            index_kind	            768	     const	 1276	20.99	Using where
3	  DEPENDENT SUBQUERY	d_japan_product_attributes		            ref	    attribute_value-index	attribute_value-index	768	     const	 669	10	    Using where
2	  DEPENDENT SUBQUERY	d_japan_product_attributes		            ref	    attribute_value-index	attribute_value-index	768	     const	 538	10	    Using where



通过单个exists 配合 or 范围查询实现:
SELECT `id`,`name`,`cover_image`,`price`,`score`,`remark`,`brand`,`subclass`,`category_one`,`category_two`,`detail` FROM `d_products` WHERE (kind = '养老服务' and (name like '%水%' or detail like '%水%')) AND (EXISTS(SELECT count(id) as id_count FROM `d_product_attributes` WHERE `d_product_attributes`.`product_id` = `d_products`.`id` AND (`d_product_attributes`.`attribute_value` = '卫浴' or `d_product_attributes`.`attribute_value` = '介护老人') group by `d_product_attributes`.`product_id` HAVING id_count = 2)) ORDER BY id desc LIMIT 10;


explain结果：
id   | select_type          | table                   | partitions    | type | possible_keys           |  key                 | key_len | ref  | rows | filtered | Extra
1	   PRIMARY	            d_japan_products		                   ref	    index_kind	            index_kind	            768	     const	1276	20.99	Using where
2	   DEPENDENT SUBQUERY	d_japan_product_attributes		           range	attribute_value-index	attribute_value-index	768		        1207	10	    Using index condition; Using where; Using temporary; Using filesort


总结：对比可以知道，通过第一种方式查询的更快一些，原因是 第一种 会用到等值查询配合 attribute_value 字段上的索引会非常快，一个 type 是 ref,另一个 type 是 range