我有以下表格：
客户 cus_id （主键） driver_licence_id （int唯一）
汽车 car_id （主键） cus_id （外键） customer 并建议该客户拥有这辆车） date_created （日期时间）
零件（汽车的不同零件，如发动机、Spark塞等） part_id （主键） manufacturers_id （内景）
车内零件（哪些零件在车内） part_in_car_id （主键） car_id （外键） car 表） part_id （外键） parts 表）
我基本上是在尝试获取所有 manufacturers_id 在一辆属于某个特定公司的车里 driver_licence_id . 下面的sql查询按我所希望的方式工作，但是它接管了 1 second 执行。我总共有100多万行。我测试的查询结果是20行。
SELECT GROUP_CONCAT(p.manufacturers_id ORDER BY p.manufacturers_id) as mids
FROM car c INNER JOIN
parts_in_car pic
ON c.car_id = pic.car_id JOIN
parts p
ON pic.part_id = p.part_id JOIN
customers cus
ON c.cus_id = cus.cus_id
WHERE cus.driver_licence_id = 5555555
group by c.car_id, c.date_created
ORDER BY c.date_created我试着为这个查询做以下索引。有人能告诉我要创建什么索引吗。
# Customer
CREATE INDEX customer_driver_licence_id_idx
ON customer (driver_licence_id);
# cars
CREATE INDEX cars_cus_id_idx
ON cars (cus_id);
# parts
CREATE INDEX parts_manufacturers_id_idx
ON parts (manufacturers_id);
# parts_in_car
CREATE INDEX parts_in_car_part_id_idx
ON parts_in_car (part_id);
CREATE INDEX parts_in_car_car_id_idx
ON parts_in_car (car_id);更新：问题是 group by 我已经建立了索引（车号，添加日期）来解决这个问题
# EXPLAIN SELECT
+-------+-------------------------------------+
| table | key |
+-------+-------------------------------------+
| a | cus_id |
| o | cars_cus_id_car_id_date_created_idx |
| pip | parts_in_car_car_id_idx |
| p | PRIMARY |
+-------+-------------------------------------+
4条答案
按热度按时间daupos2t1#
对于此查询：
您需要索引：
customers(license_id, cus_id)
car(cus_id, car_id, date_created)parts_in_car(car_id, part_id)parts(part_id, manufacturers_id).vaj7vani2#
索引策略取决于您的工作负载（即大多数执行的查询）和列的基数级别（即列中的重复值）。
所有工作负载（即执行的查询）都使用索引。它们不只是用于某些特定的查询。这样做的原因是，如果查询太多，引擎可能会错误地为其他一些查询选择索引，并导致它们运行缓慢，同时有助于改进您在设计查询时所考虑的一个查询。如果这一个查询是执行次数最多的查询之一，那么当然值得权衡。
与表或其他索引中的基数相比，引擎更喜欢基数较低的索引。因此，当您将多列组合到一个索引（复合索引）中时，需要考虑每列增加索引基数的程度。
https://www.mysqltutorial.org/mysql-index/mysql-index-cardinality/
确保表上有聚集索引（即主键），并使其成为单列数字主键（而不是复合主键或非数字主键）。此主键将自动包含所有索引。引擎需要某种从索引到表的Map方式，主键在辅助索引中用于此目的。如果没有主键，引擎将尝试在列中找到一些唯一性，从而降低效率。
https://www.mysqltutorial.org/mysql-index/mysql-clustered-index/
覆盖索引包括执行查询所需的表中的所有列。当有覆盖索引时，引擎将使用它来代替表b/c，因为它拥有完成查询的所有列。即使覆盖索引的基数与表相同，这仍然是首选的，因为它的列数比原始表少，索引大小将很小（因此i/o更少）。
https://blog.toadworld.com/2017/04/06/speed-up-your-queries-using-the-covering-index-in-mysql
因此，如果不知道所有这些参数，就很难设计索引。我可以建议测试以下覆盖索引，但是如果列的基数显著增加，请添加另一个没有该列的索引。
fdx2calv3#
使用子选择是否会更快？
下面是一些伪代码：
select manufacturer_id from manufacturer_parts where part_id in (select part_id from part_car where car_id in (select car_id from cars where driverlicense = ?)mpgws1up4#
这可能需要两种类型：
这样可以更快地获得类似的结果：
因为它现在可以对这两个步骤进行单一排序。
请提供
EXPLAIN SELECT.... 同时，我猜优化器更愿意从唯一一个带有筛选的表开始：每一个都是一个“覆盖索引”，因此所有的工作都可以在同一个时间内完成
INDEXB树。注：指数差异c（与戈登的建议相比）可能有帮助，也可能没有。我是根据修改过的GROUP BY.简单的
JOINs（与INNER JOIN)，优化器几乎总是从有筛选的表开始(WHERE...). 之后，查询中表的顺序由ON条款。因此，决定所需的4个指标相对简单。在其他情况下，不清楚执行连接的最佳顺序，有些索引可能需要翻转。
特别是，如果您删除
WHERE，最佳的起点是c从中的两列开始GROUP BY. 这可能会消除一种。下一个也会来cus或者pic.p会追上来的pic.many:many是
parts_in_car一张“多对多”的table？如果是的话，摆脱你的pk；这会影响表演。更多讨论请参见：http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table