MySQL 8 优化案例：多个IN值的参数优化

##

MySQL 8 优化案例：Why Are Queries with Many IN Values More Expensive After Upgrading to MySQL 8.x?

作者毫无保留的用案例告诉我们如何优化多个in值的SQL语句，强推

原文：https://www.percona.com/blog/why-are-queries-with-many-in-values-more-expensive-after-upgrading-to-mysql-8-x/

About the Author：Przemysław Malkowski
Przemek joined Support Team at Percona in August 2012. Before that he spent over five years working for Wikia.com (Quantcast Top 50) as System Administrator where he was a key person responsible for seamless building up MySQL powered database infrastructure. Besides MySQL he worked on maintaining all other parts of LAMP stack, with main focus on automation, monitoring and backups.

Some of our Percona Support customers report performance degradation after a major MySQL upgrade, and there can be many different reasons for this. These days, the most common major upgrade is from MySQL 5.7 (which recently reached EOL) to 8.0, and I am going to emphasize one important case that affects many database instances.

Range optimization problem

Queries having many IN values use Equality Range Optimization in MySQL query optimizer. Let’s say our query looks like this:

SELECT COUNT(*) FROM test.sbtest1 WHERE id IN (10,50,200,...,30822);