Query Turbo für SQL

von Peter Soth

Query Turbo für SQL : Elasticsearch, Apache Lucene bzw. Apache Solr vs. SQL Queries

In diesem Blogpost möchte ich aufzeigen, dass sich Suchmaschinen (in diesem Blogpost gehe ich auf Elasticsearch ein, das wie Apache Solr auf Apache Lucene basiert) auch ausgesprochen effektiv als Turbo für SQL-Queries verwenden lassen. SQL-Queries neigen ab einer gewissen Komplexität (hierarchisch, viele Aggregationen bzw. Joins) dazu, langsam zu werden. So entstehen beispielsweise in Portalen komplexe SQL Statements bei der Abfrage von Personalisierungsregeln (wer darf was sehen). Als prominentes Beispiel kann man hier das Liferay Portal erwähnen. Seit der Liferay-Version 6.2 [1] wird beim Asset Publisher ein Lucene Index anstelle von Datenbankabfragen eingesetzt.

Mein Vergleich verwendet hingegen die 1,5 Millionen Zeilen, die ich in meinem Blogpost mit dem Thema »Olap Cube vs. Elasticsearch« [2] benutzte. Ich habe hierzu in MySQL eine flache Tabelle (keine Joins) angelegt, auf die entsprechenden Spalten einen Index gesetzt und im Anschluss daran befüllt. Anschließend ermittle ich mit Hilfe einer SQL-Abfrage dieselbe Pivot-Tabelle, die ich in meinem vorigen Blogpost mit Elasticsearch erzeugte. Das Retrieval und das Aufbereiten der Daten für die Pivot-Tabelle erfolgt mit folgendem Python-Skript.

from terminaltables import AsciiTable
import pymysql.cursors
import time

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='--',
                             password='--',
                             db='olap_test',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)
start = time.time()
try:
    with connection.cursor() as cursor:
        sql = "select country, customer_name, round(sum(amount),2) as amount, year(date1) as year from olap_data where country in ('Brazil', 'Venezuela', 'Argentina', 'Mexico') group by country, customer_name, year(date1)"
        cursor.execute(sql)
        result = cursor.fetchall()
finally:
    connection.close()

def addRowTable(table, col1, col2, col3, col4, col5):
    table.append([str(col1), str(col2), str(col3), str(col4), str(col5)])

table_data = [['Land', 'Kunde', '%10s' % 'Umsatz 1994', '%10s' % 'Umsatz 1995', '%10s' % 'Umsatz 1996']]
country = ''
customer = ''
for row in result:
    if country != row['country']:
        country = row['country']
        addRowTable(table_data, country, '', '', '', '')
    if customer != row['customer_name']:
        customer = row['customer_name']
        # get revenues from years 1994,1995 and 1996
        revenue_years = [item for item in result if item['country'] == country and item['customer_name'] == customer]
        revenue_per_year = [0.0, 0.0, 0.0]
        for revenue_year in revenue_years:
            if 1994 == revenue_year['year']:
                revenue_per_year[0] = revenue_year['amount']
            if 1995 == revenue_year['year']:
                revenue_per_year[1] = revenue_year['amount']
            if 1996 == revenue_year['year']:
                revenue_per_year[2] = revenue_year['amount']
        addRowTable(table_data, '', customer, "%10.2f" % revenue_per_year[0], "%10.2f" % revenue_per_year[1], "%10.2f" % revenue_per_year[2])
table = AsciiTable(table_data)
print(table.table)
end = time.time()
print("Time taken: %5.2f seconds" %(end - start))

Die Tabelle unten zeigt das Resultat, die Sortierung habe ich noch der Elasticsearch-Query meines letzten Blog-Posts hinzugefügt. Damit ist das von MySQL und Elasticsearch zurückgelieferte Ergebnis gleich und muss nicht innerhalb von Python sortiert werden.

+-----------+------------------------------------+-------------+-------------+-------------+
| Land      | Kunde                              | Umsatz 1994 | Umsatz 1995 | Umsatz 1996 |
+-----------+------------------------------------+-------------+-------------+-------------+
| Argentina |                                    |             |             |             |
|           | Cactus Comidas para llevar         |       0.00  |   32339.16  |  104296.86  |
|           | Océano Atlántico Ltda.             |       0.00  |   71270.10  |  504599.82  |
|           | Rancho grande                      |       0.00  |  115290.42  |  296329.62  |
| Brazil    |                                    |             |             |             |
|           | Comércio Mineiro                   |  149676.60  |  146333.76  |   56055.82  |
|           | Familia Arquibaldo                 |    5821.80  |  431282.70  |       0.00  |
|           | Gourmet Lanchonetes                |       0.00  |  521745.96  |   83500.20  |
|           | Hanari Carnes                      |  232872.00  |  181903.08  |  944863.26  |
|           | Que Delícia                        |   17747.10  |  491754.30  |  105637.50  |
|           | Queen Cozinha                      |       0.00  | 2704507.80  | 1017204.30  |
|           | Ricardo Adocicados                 |   79852.56  |  485725.92  |  621214.02  |
|           | Tradição Hipermercados             |    2535.30  |  236947.26  |  276141.12  |
|           | Wellington Importadora             |   26235.66  |  108304.26  |  231125.46  |
| Mexico    |                                    |             |             |             |
|           | Ana Trujillo Emparedados y helados |    3023.58  |  104961.42  |   74969.76  |
|           | Antonio Moreno Taquería            |   41316.00  |  353233.02  |  109731.54  |
|           | Centro comercial Moctezuma         |    6103.50  |       0.00  |       0.00  |
|           | Pericles Comidas clásicas          |  101787.60  |  286977.18  |  132695.20  |
|           | Tortuga Restaurante                |  306696.18  |  448316.16  |  137876.06  |
| Venezuela |                                    |             |             |             |
|           | GROSELLA-Restaurante               |  124492.62  |       0.00  |    2835.78  |
|           | HILARIÓN-Abastos                   |  153826.98  | 1487469.90  |  720745.36  |
|           | LILA-Supermercado                  |  248947.68  |  576151.62  |  553818.20  |
|           | LINO-Delicateses                   |       0.00  |  834376.62  |  431038.56  |
+-----------+------------------------------------+-------------+-------------+-------------+
Time taken: 10.31 seconds

Die gemessene Zeit liegt immer bei ca. 10 Sekunden (MySQL cached nicht) auf meinem Notebook. Vergleiche ich die Zeitdauer mit Elasticsearch, so ergibt sich folgendes Bild. Initial (Caches nicht aktiv) benötigt die Abfrage mit Nested Objects 1,2 Sekunden ohne nur noch 1 Sekunde. Elasticsearch cached bei den nächsten Aufrufen und wir erhalten schließlich Zeiten von ca. 200ms. Hierbei sei zusätzlich erwähnt, dass ich sowohl MySQL als auch Elasticsearch nicht getuned habe.

Fazit

Sollte man bei der Entwicklung auf komplexe SQL-Queries stoßen, die nicht performant laufen, so kann es eine Überlegung wert sein, eine Suchmaschine wie Elasticsearch zu involvieren. Elasticsearch unterstützt Relationen, mit Nested Objects bzw. Parent-Child. Mein kleiner Test hat gezeigt, dass Nested Objects initial ca. 20 % langsamer sind. Parent-Child-Abfragen sind laut Dokumentation [3] 5-10 mal behäbiger als eine Nested Query. Im Vergleich zu MySQL ist in meinem Testaufbau zu sehen, dass Elasticsearch ca. 10-mal performanter ist.

Links

[1] https://web.liferay.com/en/documentation/liferay-portal/6.2/release-notes
[2] OLAP Cube vs. Elasticsearch
[3] https://www.elastic.co/guide/en/elasticsearch/guide/current/parent-child-performance.html

Zurück