我在三列上的两个表之间有一个连接。连接需要几个小时才能完成，所以我在每个表的所有三列上都添加了一个复合索引。然后，有时连接会非常快，有时仍然很慢。使用explain，我注意到当它选择使用复合索引连接时速度很快，而当它只在其中一列上选择索引时速度很慢。但每一次运行都使用相同的数据。sql选择使用哪个索引时是否涉及随机性？为什么会不一致？如果有帮助的话：它是一个mysql数据库，用python从pandas查询。
6xfqseft1#
问：sql选择使用哪个索引是否涉及随机性？不涉及随机性本身。优化器利用表和索引统计信息（行数和基数）以及查询中的 predicate 来进行估计，例如需要检索的行数。mysql还评估每个可能的访问计划的连接操作、排序操作等的成本（例如，使用哪个索引，访问表的顺序），从而得出每个计划的估计成本。然后优化器比较成本，并使用成本最低的计划。有些参数（mysql系统变量）会影响成本估算(例如，调整i/o操作的预期成本。）问：为什么会前后不一致？对于innodb表，收集统计数据时会出现一些随机性。innodb使用了一种抽样技术，对一小部分“随机”页面进行“深入”分析。这些样本页的结果被外推到整个表的估计值中。一些innodb调优参数（mysql系统变量）影响（增加/减少）收集统计数据时采样的页数。对较小数量的页面进行采样可以更快，但是较小的样本使得样本集更可能不能完全代表整个表。使用更多的样本在一定程度上缓解了这种情况，但采样需要更长的时间。这是一种权衡。注意，当表中10%的行被dml操作更改时，innodb会自动重新收集统计信息(在某些情况下，可能不会触发统计信息的自动收集，例如，创建一个新的（空）表并用 LOAD DATA 语句，这可能导致未收集任何统计数据。）因此，对观察到的行为最可能的解释是，在不同的时间，优化器可以使用不同的统计信息。注意，通过在sql文本中包含提示，可以影响优化器选择使用特定索引的计划。我们通常不需要这样做，也不想这样做。但在某些情况下，如果优化器选择了一个低效的计划，我们可以帮助得到一个更好的计划。一些参考资料（来自mysql 5.7参考手册）https://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.htmlhttps://dev.mysql.com/doc/refman/5.7/en/innodb-performance-optimizer-statistics.html
LOAD DATA
1条答案
按热度按时间6xfqseft1#
问：sql选择使用哪个索引是否涉及随机性？
不涉及随机性本身。优化器利用表和索引统计信息（行数和基数）以及查询中的 predicate 来进行估计，例如需要检索的行数。
mysql还评估每个可能的访问计划的连接操作、排序操作等的成本（例如，使用哪个索引，访问表的顺序），从而得出每个计划的估计成本。
然后优化器比较成本，并使用成本最低的计划。有些参数（mysql系统变量）会影响成本估算(例如，调整i/o操作的预期成本。）
问：为什么会前后不一致？
对于innodb表，收集统计数据时会出现一些随机性。innodb使用了一种抽样技术，对一小部分“随机”页面进行“深入”分析。这些样本页的结果被外推到整个表的估计值中。
一些innodb调优参数（mysql系统变量）影响（增加/减少）收集统计数据时采样的页数。对较小数量的页面进行采样可以更快，但是较小的样本使得样本集更可能不能完全代表整个表。使用更多的样本在一定程度上缓解了这种情况，但采样需要更长的时间。这是一种权衡。
注意，当表中10%的行被dml操作更改时，innodb会自动重新收集统计信息(在某些情况下，可能不会触发统计信息的自动收集，例如，创建一个新的（空）表并用
LOAD DATA语句，这可能导致未收集任何统计数据。）因此，对观察到的行为最可能的解释是，在不同的时间，优化器可以使用不同的统计信息。
注意，通过在sql文本中包含提示，可以影响优化器选择使用特定索引的计划。我们通常不需要这样做，也不想这样做。但在某些情况下，如果优化器选择了一个低效的计划，我们可以帮助得到一个更好的计划。
一些参考资料（来自mysql 5.7参考手册）
https://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-performance-optimizer-statistics.html