MySQL VIEW Sub Select Performance Problem

Dienstag, 28. Oktober 2008

Bei der Arbeit an stoodl musste ich festetellen, dass es mit Views in MySQL einige Performance Probleme gibt, wie hier beschrieben. Dabei werden in den Views die Index nicht verwendet, sobald die View ein Sub Select enthält. Dies hätte ich eigentlich nicht erwartet. Alle Optionen wie FORCE INDEX bleiben ohne Wirkung. Es gibt nun die Möglichkeit, statt der View jeweils die ganze Query auszuschreiben, was aber eher unschön ist.

Die ganzen Beispiele und Tests wurden mit der Version MySQL 5.0.41 gemacht.

Hier zuerst einmal ein Beispiel des Problems. Wir haben eine Tabelle group mit dem Primary Key id und erstellen eine View group_view mit einer komplett nichtssagenden Sub Select Anweisung:


CREATE VIEW group_view AS

    SELECT

       id, (SELECT 2) AS test

    FROM `group`

Nun machen wir eine einfache Select Abfrage mit einer Where Klausel nach einer id. In der Select Abfrage haben wir auch die nichtsagende SELECT 2 Abfrage, damit wird die gleiche Abfrage wie in der View haben.


SELECT id, (SELECT 2) AS test  FROM `group` WHERE id = 150;

Die Abfrage liefert wie erwartet eine Zeile zurück. Explain liefert folgende Ausgabe.


+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+

| id | select_type | table | type  | possible_keys |
key     | key_len |
ref   | rows |
Extra       |

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+

|  1 | PRIMARY     | group
| const |
PRIMARY       |
PRIMARY | 4      
| const |    1 | Using index | 

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+

Wie erwartet wird der Index verwendet. Nun machen wir die gleiche Abfrage über die View.


SELECT id,test FROM group_view WHERE id = 150;

Das Resultat ist wie erwartet das Gleiche. Doch Explain liefert eine ziemlich unterschiedliche Ausgabe.


+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+

| id | select_type |
table      |
type  | possible_keys |
key     | key_len | ref  |
rows |
Extra       |

+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+

|  1 | PRIMARY     |
<derived2> | ALL   |
NULL         
| NULL    |
NULL    | NULL |  167 | Using where
| 

|  2 | DERIVED     |
group      | index |
NULL         
| PRIMARY |
4       | NULL
|  167 | Using index | 

+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+

Es wird ein Tablescan durchgeführt, und alle 167 Zeilen gelesen anstatt dass der Index verwendet wird. Aufgrund der Sub Select Abfrage (welche eigentlich einen Konstanten Wert liefert) wird der Index nicht verwendet. Das heisst, die SELECT Abfrage über die View wird nicht gleich optimiert, wie die direkte Abfrage. Daher sollte man beim verwenden von Views Sub Select vermeiden, da sonst der Index nicht verwendet wird.

Braucht man Sub Select in Views, habe ich bisher zwei Lösungen gefunden. Die erste ist (nicht wirklich eine Lösung), statt dem verwenden der View immer die ganze Query zu schreiben. Die zweite Lösung, welche ich gefunden habe, ist in der View Funktionen für die Sub Selects zu verwenden.

Hier nun ein Beispiel einer solchen Funktion. Diese multipliziert einfach den Eingabewert mit zwei und gibt ihn zurück.


CREATE FUNCTION test (value INT) 

    RETURNS INT

    BEGIN

        DECLARE
calc INT;

        SET calc
= 2 * value;

        RETURN
calc;

    END

Die Funktion verwenden wir nun beim erstellen der View.


CREATE VIEW group_view AS

    SELECT id, TEST(2) AS test

    FROM `group`;

Der Wert von test ist nun immer 4. Nun schauen wir uns erneut die Explain Ausgabe der View an.


mysql> EXPLAIN SELECT id, test FROM group_view WHERE id = 150;

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+

| id | select_type | table | type  | possible_keys |
key     | key_len |
ref   | rows |
Extra       |

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+

|  1 |
SIMPLE      | group |
const |
PRIMARY       |
PRIMARY | 4      
| const |    1 | Using index | 

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+

Nun wird wie erwartet der Index verwendet. Die Funktion kann auch komplexe Sub Selects enthalten, und das Ganze funktioniert noch immer. Um Performance Probleme mit Views zu verhindern, sollte man also keine Sub Selects verwenden oder diese in Funktionen verschachteln.

Der Output von Explain zeigt leider nicht, was genau in den Funktionen geschieht. Doch einige Geschwindigkeitstest haben gezeigt, dass in den Funktionen die Index verwendet werden.

Comments

Add New

[ Zurück ]

Comments

3.26 Copyright (C) 2008 Compojoom.com / Copyright (C) 2007 Alain Georgette / Copyright (C) 2006 Frantisek Hliva. All rights reserved."