Können Sie das mal übersetzen? Query Rewriting mit MySQL

Ab und zu kommt es vor, dass die eine oder andere Applikation ungünstig formulierte Statements an einen MySQL-Server schickt. Dies kann dann zu Performance-Problemen führen, da beispielsweise vom Optimizer ein ungünstiger Ausführungsplan generiert wird. Im besten Fall kann der Entwickler der Software dieses Problem schnell für einen Fix selbst lösen. In vielen Fällen, z.B. bei Kaufsoftware oder bei kompilierten Code, ist dies nicht so einfach möglich.

Flinte ins Korn?

In manchen Fällen kann hier auch der DBA eingreifen und mit dem Query-Rewrite-Plugin kurzfristig für Abhilfe sorgen.
Dazu muss dieses Plugin zunächst installiert werden. Die Datenbank liefert für diesen Zweck Skripte zur Installation und Deinstallation mit. Sie befinden sich im Ordner „share" unterhalb des Installationspfades der Datenbank.

mysql> \. /opt/mysql/mysql-8.0.19/share/install_rewriter.sql
…
Query OK, 1 row affected (0,02 sec) 

Die Installation des Plugins kann anhand der folgenden Variable überprüft werden.

mysql> show variables like 'rewriter_enabled';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| rewriter_enabled | ON    |
+------------------+-------+
1 row in set (0,00 sec) 

Befolge immer brav die Regeln!

Das Installationsskript aktiviert nicht nur das Plugin, sondern legt auch eine eigene DB „query_rewrite" an. In dieser befindet sich eine Tabelle „rewrite_rules", über welche das Regelwerk zum Umschreiben von Queries definiert werden kann.

mysql> desc query_rewrite.rewrite_rules;
+--------------------+------------------+------+-----+---------+----------------+
| Field              | Type             | Null | Key | Default | Extra          |
+--------------------+------------------+------+-----+---------+----------------+
| id                 | int              | NO   | PRI | NULL    | auto_increment |
| pattern            | varchar(5000)    | NO   |     | NULL    |                |
| pattern_database   | varchar(20)      | YES  |     | NULL    |                |
| replacement        | varchar(5000)    | NO   |     | NULL    |                |
| enabled            | enum('YES','NO') | NO   |     | YES     |                |
| message            | varchar(1000)    | YES  |     | NULL    |                |
| pattern_digest     | varchar(64)      | YES  |     | NULL    |                |
| normalized_pattern | varchar(100)     | YES  |     | NULL    |                |
+--------------------+------------------+------+-----+---------+----------------+
8 rows in set (0,00 sec) 

X für ein U, bzw. U für ein D

Die Regeln werden über einfache „INSERTS" angelegt. Literale können dabei mittels eines „?" dargestellt werden.  Nehmen wir einmal an, dass wir aus bestimmten Gründen DELETE-Statements einer Applikation (MySQL Demo-Datenbank sakila) in UPDATE Statements umwandeln wollen.
In der Tabelle „sakila.customer" werden Kundendaten verwaltet. Kunden können aktiv (active = 1) oder inaktiv (active = 0) sein.
Hier ein exemplarischer Kundendatensatz aus der Tabelle.

mysql> select * from customer where customer_id = 7;
+-------------+----------+------------+-----------+------------------------------------+------------+--------+---------------------+---------------------+
| customer_id | store_id | first_name | last_name | email                              | address_id | active | create_date         | last_update         |
+-------------+----------+------------+-----------+------------------------------------+------------+--------+---------------------+---------------------+
|           7 |        1 | MARIA      | MILLER    | MARIA.MILLER[at]sakilacustomer.org |         11 |      1 | 2006-02-14 22:04:36 | 2020-04-06 13:36:32 |
+-------------+----------+------------+-----------+------------------------------------+------------+--------+---------------------+---------------------+
1 row in set (0,00 sec)
 

Mittels der folgenden Regel möchten wir nun erreichen, dass ein DELETE-Statement in ein UPDATE-Statement gewandelt wird, welches das Feld „active" auf den Wert „0" setzt. Diese Regel legen wir wie folgt an:

mysql> insert into query_rewrite.rewrite_rules
(pattern, pattern_database, replacement, enabled, message)
values
('delete from customer where customer_id = ?', 'sakila',
'update customer set active = 0 where customer_id = ?', 'YES', 'Customer INactive');
Query OK, 1 row affected (0,00 sec)
 

Nach dem Erzeugen der Regel muss das Regelwerk mit dem folgenden Kommando neu geladen werden:

mysql> CALL query_rewrite.flush_rewrite_rules();
Query OK, 1 row affected (0,03 sec) 

Versuchen wir nun einen Datensatz zu löschen und trifft der Hash-Wert unseres Kommandos auf den Hash-Wert unserer Regel (Spalte „pattern") zu, so wird die Query umgeschrieben.

mysql> delete from customer where customer_id = 7;
Query OK, 1 row affected, 1 warning (0,00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                          |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1105 | Query 'delete from customer where customer_id = 7' rewritten to 'update customer set active = 0 where customer_id = 7' by a query rewrite plugin |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

mysql> select * from customer where customer_id = 7;
+-------------+----------+------------+-----------+------------------------------------+------------+--------+---------------------+---------------------+
| customer_id | store_id | first_name | last_name | email                              | address_id | active | create_date         | last_update         |
+-------------+----------+------------+-----------+------------------------------------+------------+--------+---------------------+---------------------+
|           7 |        1 | MARIA      | MILLER    | MARIA.MILLER[at]sakilacustomer.org |         11 |      0 | 2006-02-14 22:04:36 | 2020-04-06 13:36:32 |
+-------------+----------+------------+-----------+------------------------------------+------------+--------+---------------------+---------------------+
1 row in set (0,00 sec) 

Das Ergebnis ist wie erwartet. Der Kunde hat den Status inaktiv (active = 0).
Allerdings führen leichte Nuancen in den Queries bereits dazu, dass die Hash-Werte nicht mehr übereinstimmen. Im folgenden Beispiel wurde im DELETE-Kommando lediglich ein Spaltenname anderes definiert (CUSTOMER_ID).

mysql> select * from customer where customer_id = 8;
+-------------+----------+------------+-----------+------------------------------------+------------+--------+---------------------+---------------------+
| customer_id | store_id | first_name | last_name | email                              | address_id | active | create_date         | last_update         |
+-------------+----------+------------+-----------+------------------------------------+------------+--------+---------------------+---------------------+
|           8 |        2 | SUSAN      | WILSON    | SUSAN.WILSON[at]sakilacustomer.org |         12 |      1 | 2006-02-14 22:04:36 | 2006-02-15 04:57:20 |
+-------------+----------+------------+-----------+------------------------------------+------------+--------+---------------------+---------------------+
1 row in set (0,00 sec)

mysql> delete from customer where CUSTOMER_ID = 8;
Query OK, 1 row affected (0,00 sec)
mysql> select * from customer where customer_id = 8;
Empty set (0,00 sec) 

Da die Hash-Werte nicht mehr identisch waren, wurde das Original-Statement ausgeführt. Der Datensatz wurde gelöscht.

Gehen Sie direkt auf Los, …

Es lassen sich aber auch andere Probleme mit diesem Plugin lösen. Im folgenden Beispiel hat ein Entwickler eine ungünstige WHERE-Klausel formuliert. Er nutzt die UPPER-Funktion auf der Spalte „title". Zwar sind alle Titel in der Tabelle „sakila.film" in Großschreibweise definiert, jedoch führt der Einsatz der Funktion dazu, dass der Optimizer den auf der Spalte angelegten Index nicht mehr nutzen möchte.

mysql> explain select count(*) from sakila.film where title like 'T%';
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | film  | NULL       | range | idx_title     | idx_title | 767     | NULL |   46 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0,00 sec)

mysql> explain select count(*) from sakila.film where upper(title) like 'T%';
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | film  | NULL       | index | NULL          | idx_title | 767     | NULL | 1000 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0,00 sec) 

Dies kann bei einer großen Datenmenge zu einer erheblichen Laufzeitverlängerung führen. Auch dieses Problem kann über eine Rewrite-Regel behoben werden.

mysql> insert into query_rewrite.rewrite_rules(pattern, pattern_database, replacement, enabled) values ('select count(*) from sakila.film where upper(title) like ?', 'sakila',
    -> 'select count(*) from sakila.film where title like ?', 'YES');
Query OK, 1 row affected (0,00 sec)

mysql> CALL query_rewrite.flush_rewrite_rules();
Query OK, 1 row affected (0,02 sec)

mysql> select count(*) from sakila.film where upper(title) like '%T';
+----------+
| count(*) |
+----------+
|       78 |
+----------+
1 row in set, 1 warning (0,00 sec)

mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                               |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1105 | Query 'select count(*) from sakila.film where upper(title) like '%T'' rewritten to 'select count(*) from sakila.film where title like '%T'' by a query rewrite plugin |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec) 

Wie zu erkennen ist, wurde die Regel nach dem Anlegen der Regel genutzt. Das Statement wurde in eine optimizer-freundlichere Form gebracht.

Fazit

Das Rewrite-Plugin kann an der einen oder anderen Stelle schnell Probleme lösen. Die Aktivierung kann online erfolgen. Für komplexere Regelwerke eignet es sich jedoch nicht wirklich. Hier gibt es verschiedene Proxy-Dienste, die deutlich mehr können. Diese sind jedoch nicht einfach im laufenden Betrieb einsetzbar und brauchen daher etwas länger in der Bereitstellung.