Rechercher dans le manuel MySQL
8.2.1.13 Constant-Folding Optimization
Comparisons between constants and column values in which the
constant value is out of range or of the wrong type with
respect to the column type are now handled once during query
optimization rather row-by-row than during execution. The
comparisons that can be treated in this manner are
>
, >=
,
<
, <=
,
<>
/!=
,
=
, and <=>
.
Consider the table created by the following statement:
The WHERE
condition in the query
SELECT * FROM t WHERE c < 256
contains
the integral constant 256 which is out of range for a
TINYINT UNSIGNED
column. Previously, this
was handled by treating both operands as the larger type, but
now, since any allowed value for c
is less
than the constant, the WHERE
expression can
instead be folded as WHERE 1
, so that the
query is rewritten as SELECT * FROM t WHERE
1
.
This makes it possible for the optimizer to remove the
WHERE
expression altogether. If the column
c
were nullable (that is, defined only as
TINYINT UNSIGNED
) the query would be
rewritten like this:
Folding is performed for constants compared to supported MySQL column types as follows:
Integer column type. Integer types are compared with constants of the following types as described here:
Integer value. If the constant is out of range for the column type, the comparison is folded to
1
orIS NOT NULL
, as already shown.If the constant is a range boundary, the comparison is folded to
=
. For example (using the same table as already defined):Floating- or fixed-point value. If the constant is one of the decimal types (such as
DECIMAL
,REAL
,DOUBLE
, orFLOAT
) and has a nonzero decimal portion, it cannot be equal; fold accordingly. For other comparisons, round up or down to an integer value according to the sign, then perform a range check and handle as already described for integer-integer comparisons.A
REAL
value that is too small to be represented asDECIMAL
is rounded to .01 or -.01 depending on the sign, then handled as aDECIMAL
.String types. Try to interpret the string value as an integer type, then handle the comparison as between integer values. If this fails, attempt to handle the value as a
REAL
.
DECIMAL or REAL column. Decimal types are compared with constants of the following types as described here:
Integer value. Perform a range check against the column value's integer part. If no folding results, convert the constant to
DECIMAL
with the same number of decimal places as the column value, then check it as aDECIMAL
(see next).DECIMAL or REAL value. Check for overflow (that is, whether the constant has more digits in its integer part than allowed for the column's decimal type). If so, fold.
If the constant has more significant fractional digits than column's type, truncate the constant. If the comparison operator is
=
or<>
, fold. If the operator is>=
or<=
, adjust the operator due to truncation. For example, if column's type isDECIMAL(3,1)
,SELECT * FROM t WHERE f >= 10.13
becomesSELECT * FROM t WHERE f > 10.1
.If the constant has fewer decimal digits than the column's type, convert it to a constant with same number of digits. For underflow of a
REAL
value (that is, too few fractional digits to represent it), convert the constant to decimal 0.String value. If the value can be interpreted as an integer type, handle it as such. Otherwise, try to handle it as
REAL
.
FLOAT or DOUBLE column.
FLOAT(
orm
,n
)DOUBLE(
values compared with constants are handled as follows:m
,n
)If the value overflows the range of the column, fold.
If the value has more than
n
decimals, truncate, compensating during folding. For=
and<>
comparisons, fold toTRUE
,FALSE
, orIS [NOT] NULL
as described previously; for other operators, adjust the operator.If the value has more than
m
integer digits, fold.
Limitations. This optimization cannot be used in the following cases:
With comparisons using
BETWEEN
orIN
.With
BIT
columns or columns using date or time types.During the preparation phase for a prepared statement, although it can be applied during the optimization phase when the prepared statement is actually executed. This due to the fact that, during statement preparation, the value of the constant is not yet known.
Deutsche Übersetzung
Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.
Vielen Dank im Voraus.
Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-constant-folding-optimization.html
Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.
Referenzen
Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.