Volkskrankheit "Stiefmuetterliche Indizierung"

Markus Winand10/09/14

VOLKSKRANKHEIT“STIEFMÜTTERLICHE DATENBANK-INDIZIERUNG”

Markus Winand

Skalierung

http://www.commitstrip.com/en/2014/06/03/the-problem-is-not-the-tool-itself/



1 - Pandemisches Ausmaß

Es betrifft Dich!

(Symbol-Bild; keine echten Daten)http://upload.wikimedia.org/wikipedia/commons/c/c7/2009_world_subdivisions_flu_pandemic.png

http://upload.wikimedia.org/wikipedia/commons/c/c7/2009_world_subdivisions_flu_pandemic.png

http://upload.wikimedia.org/wikipedia/commons/c/c7/2009_world_subdivisions_flu_pandemic.png

2 - Durch Erfolg verursacht

Copyright © 2013 Telerik, Inc. All rights reserved

3 - Nicht Dein Fehler

http://simpsonswiki.com/wiki/File:I_Didn%27t_Do_It!_Volume_III.png



© 2014 by Markus Winand

Das Problem

Query/Index Diskrepanz

Quantifizierung des Problems

Meine Beobachtung:

~50% der SQL-Performance-Probleme werden durch die Query/Index- Diskrepanz verursacht

Problem: Index/Query Diskrepanz

“A very common cause of performance problems is lack of proper indexes or the

use of queries that are not using existing indexes.”

—Buda Consultinghttp://www.budaconsulting.com/Portals/52677/docs/top_5_tech_brief.pdf

http://www.budaconsulting.com/Portals/52677/docs/top_5_tech_brief.pdf

http://www.budaconsulting.com/Portals/52677/docs/top_5_tech_brief.pdf


Percona White Paper:

Gründe für Performance-Probleme, die einen Produktionsausfall verursachten:

38% bad SQL

15% schema and indexing

http://www.percona.com/files/white-papers/causes-of-downtime-in-mysql.pdf




Umfrage auf sqlskills.com:

Ursachen der letzten SQLServer Performance-Probleme:

27% T-SQL

19% Poor indexing

http://www.sqlskills.com/blogs/paul/survey-what-are-the-most-common-causes-of-performance-problems/




Craig S. Mullins (DB Stratege und Forscher):

„As much as 75% of poor relational performance is caused by "bad" SQL and application code.”

Noel Yuhanna (Forrester Research):

„The key difficulties surrounding performancecontinue to be poorly written SQL statements,

improper DBMS configuration and a lack of clear understanding of how to tune databases to solve

performance issues.”


Die Wurzel des Problems

Indizierung findet zu spät statt

Meist durch die falschen Personen

Wurzel des Problems: Admins indizieren

Wie haben Datenbanken

vor SQL funktioniert?


Die Index-Nutzung war

untrennbar

mit den Abfragen verbunden.


Beispiel: dBase

Entwickler mussten......Indizes bei Suchen explizit nutzen: !"#$%&'"($#)$*+!#,&+-"!!!

$$$.%&'$/%&+&' Zwei Anweisungen

Zwei Anweisungen

...Index-Wartung berücksichtigen: !"#$%&'"($#)$*+!#,&+-"0$%'(1


SQL ist eine Abstraktion:Es definiert nur die logische Sicht

Die Implementierung muss sich um alles andere Kümmern.


Indizes

Backup & Recovery

Speicher-Management

Tuning-Parameter

Transaktionen

ConstraintsViews

Tabellen

Daten- Manipulation

Abfragen

SQL (Sprache)bietet:

SQL Datenbanken (Software)bieten:

Hoch-verfügbarkeit


Indizes

Backup & Recovery

Speicher-Management

Tuning-Parameter

Transaktionen

ConstraintsViews

Tabellen

Daten- Manipulation

Abfragen

SQL Datenbanken (Software)bieten:

Entwickler

Hoch-verfügbarkeit


Indizes

Backup & Recovery

Speicher-Management

Tuning-Parameter

Transaktionen

ConstraintsViews

Tabellen

Daten- Manipulation

Abfragen

Entwickler Administratoren

Hoch-verfügbarkeit


Heute wird Indizierung oft als Tuning-Aufgabe betrachtet,die in die Verantwortung der

Admins fällt.


Ein Missverständnis, das neue Probleme bringt:

Admins kennen die Abfragen nicht

Müssen die Abfragen erst eruieren.

Sehr zeitaufwendig und meistens unvollständig.

by G-10gian82 deviantart.com

http://g-10gian82.deviantart.com/

http://g-10gian82.deviantart.com/


Ein Missverständnis, das neue Probleme bringt:

Admins kennen die Abfragen nicht

Müssen die Abfragen erst eruieren.

Sehr zeitaufwendig und meistens unvollständig.

Admins können die Abfragen nicht ändern

Sie können den Index an die Abfrage anpassen.

Aber nicht die Abfrage an den Index.


Die Lösung

Indizierung ist eine Entwicklungsaufgabe


Indizes

Backup & Recovery

Speicher-Management

Tuning-Parameter

Transaktionen

ConstraintsViews

Tabellen

Daten- Manipulation

Abfragen


Hoch-verfügbarkeit


Indizes

Backup & Recovery

Speicher-Management

Tuning-Parameter

Transaktionen

ConstraintsViews

Tabellen

Daten- Manipulation

Abfragen


Hoch-verfügbarkeit

Muss passen!

Neues Problem: es wird nicht gelehrt

Indizierung ist nicht Teil des SQL-Standards und wird in Büchern über die Sprache SQL daher nicht abgedeckt.

11 SQL-Bücher analysiert: nur 1.0% der Seiten sind über Indizierung (70 von 7330 Seiten).

Beispiele:Oracle SQL by Example: 2.0% (19/960)Beginning DBs with PostgreSQL: 0.8% (5/664)Learning SQL: 3.3% (11/336—höchste Rate dieser Kategorie)


Manche Tuning-Bücher vertiefen Indizierung, verstecken es aber immer zwischen hunderten Seiten über

HW, OS and DB-Parametriesierung.

15 Datenbank-Admin-Bücher analysiert: 6% der Seiten sind über Indizierung (395 von 6568 Seiten).

Beispiele:Oracle Performance Survival Guide: 5.2% (38/730)High Performance MySQL: 8% (55/684)PostgreSQL 9 High Performance: 5.8% (27/468)


Konsequenz:Entwickler wissen nicht,wie man Indizes richtig einsetzt.

Ergebnisse unsers 3-Minuten online Tests:http://use-the-index-luke.com/de/3-minuten-test

5 Fragen: jede über einen bestimmten Anwendungsfall von Indizes.Nicht repräsentativ!

Q1: Gut oder schlecht? (Funktionen)

234564$7894:$#;*,%'($<8$#;*$=!"#$%&'(>?

@4A426$#"(#0$'+#",B)*$$C3<D$#;*$/E434$)*+,-!"#$%&'(.$F$1GHI?

3-Minuten Quiz: SQL-Indizierung

Q1: Gut oder schlecht? (Funktionen)

234564$7894:$#;*,%'($<8$#;*$=!"#$%&'(>?

@4A426$#"(#0$'+#",B)*$$C3<D$#;*$/E434$)*+,-!"#$%&'(.$F$1GHI?


http://use-the-index-luke.com/de/sql/where/verstuemmelung/datumsfelder



J$K453='+#",B)*>$F$1GHI?

LMMMMMMLMMMMMMMMMMMMMMMLMMMMMMLMMMMMMMLMMMMMMMMMMMMMLN$#/0$$N$O)!!%;*",P"Q!$N$P"Q$$N$1'23$$N$4(#R+$$$$$$$NLMMMMMMLMMMMMMMMMMMMMMMLMMMMMMLMMMMMMMLMMMMMMMMMMMMMLN$+445$N$8SAA$$$$$$$$$$N$6744$N$89:;:$N$S!%&T$UV"R"$N$LMMMMMMLMMMMMMMMMMMMMMMLMMMMMMLMMMMMMMLMMMMMMMMMMMMML


J$$$'+#",B)*$WF$@63,6<,9564=X1GHIMGHMGHX0$XYKMY-MY'>589$'+#",B)*$Z$$@63,6<,9564=X1GH[MGHMGHX0$XYKMY-MY'>LMMMMMMMLMMMMMMMMMMMMMMMLMMMMMMMMMLMMMMMMMLMMMMMMMMMMMMMMMMMMLN$#/0$$$N$O)!!%;*",P"Q!$N$P"Q$$$$$N$1'23$$N$4(#R+$$$$$$$$$$$$NLMMMMMMMLMMMMMMMMMMMMMMMLMMMMMMMMMLMMMMMMMLMMMMMMMMMMMMMMMMMMLN$1"<=$$N$#;*,%'($$$$$$$N$#>(%?!@$N$55:9A$N$$$$$$$$$$$$$$$$$$N$LMMMMMMMLMMMMMMMMMMMMMMMLMMMMMMMMMLMMMMMMMLMMMMMMMMMMMMMMMMMML

Komplette Tabelle wird

gelesen

Index wird genutzt

3-Minuten Quiz: Ergebnis


Q2: Gut oder schlecht? (Indiziertes Top-N)

234564$7894:$#;*,%'($<8$#;*$=+0$'+#",B)*>?@4A426$%'0$'+#",B)*$$C3<D$#;*$/E434$+$F$\$<3943$]K$'+#",B)*$94@2$A7D76$H

Syntax-Varianten:Std: C462E$C73@6$H$3</$<8AKOracle: /E434$3</8SD$ZFHSQL Server: @4A426$6<^$H


Q2: Gut oder schlecht? (Indiziertes Top-N)

234564$7894:$#;*,%'($<8$#;*$=+0$'+#",B)*>?@4A426$%'0$'+#",B)*$$C3<D$#;*$/E434$+$F$\$<3943$]K$'+#",B)*$94@2$A7D76$H

http://use-the-index-luke.com/de/sql/partielle-ergebnisse/top-n-abfragen

Syntax-Varianten:Std: C462E$C73@6$H$3</$<8AKOracle: /E434$3</8SD$ZFHSQL Server: @4A426$6<^$H




Das ist bereits die optimale Lösung.

LMMMMMMLMMMMMMMMMMMMMMMLMMMMMMMMMLMMMMMMMMMMMMMMMMMMMMMMMMMMLN$#QO"$N$O)!!%;*",P"Q!$N$P"Q$$$$$N$4(#R+$$$$$$$$$$$$$$$$$$$$NLMMMMMMLMMMMMMMMMMMMMMMLMMMMMMMMMLMMMMMMMMMMMMMMMMMMMMMMMMMMLN$R".$$N$#;*,%'($$$$$$$N$#;*,%'($N$S!%&T$UV"R"?$S!%&T$%&'"($N$LMMMMMMLMMMMMMMMMMMMMMMLMMMMMMMMMLMMMMMMMMMMMMMMMMMMMMMMMMMML

So schnell wie ein Primärschlüssel-Zugriff.

(Im Falle von MySQL und SQL Server wegendes Clustered-Indexes ein Index-Only Scan)

Nebenbei: Finger weg von OFFSET

@4A426$%'0$'+#",B)*$$C3<D$#;*$/E434$+$F$\$<3943$]K$'+#",B)*$94@2$A7D76$H!""#$%&'

Nebenbei: Finger weg von OFFSET

!""#$% ist verlockend, aber böse.

http://use-the-index-luke.com/no-offset

Sticker und

Bierdeckel h

olen!




Q3: Gut oder schlecht? (Reihenfolge)CREATE INDEX tbl_idx ON tbl (a, b);SELECT id, a, b FROM tbl WHERE a = ? AND b = ?;

SELECT id, a, b FROM tbl WHERE b = ?;


Q3: Gut oder schlecht? (Reihenfolge)CREATE INDEX tbl_idx ON tbl (a, b);SELECT id, a, b FROM tbl WHERE a = ? AND b = ?;

SELECT id, a, b FROM tbl WHERE b = ?;

http://use-the-index-luke.com/de/sql/where/gleichheit/zusammengesetzte-schluessel




Nur eine Abfrage kann den Index (a, b) gut nutzen:

...WHERE a = ? AND b = ?;&''''''&'''''''''''''''&'''''''''&''''''&''''''''''''''''''&()*+,-)(),.//012-34-+/)()4-+)))))()5.6/)()$7*58))))))))))))(&''''''&'''''''''''''''&'''''''''&''''''&''''''''''''''''''&()()*))()+,-./01)))))))()*123097)())))2)():;;<)=/0>?)0>9-7)(&''''''&'''''''''''''''&'''''''''&''''''&''''''''''''''''''&

...WHERE b = ?;&'''''''&'''''''''''''''&'''''''''&'''''''&''''''''''''''''''&()*+,-))(),.//012-34-+/)()4-+)))))())5.6/)()$7*58))))))))))))(&'''''''&'''''''''''''''&'''''''''&'''''''&''''''''''''''''''&()/30)1)()4566))))))))))()*123097)()27879)():;;<)=/0>?)0>9-7)(&'''''''&'''''''''''''''&'''''''''&'''''''&''''''''''''''''''&

Ganzer Index wird gelesen!


Spalten umdrehen (b, a): beide nutzen Index gut

...WHERE a = ? AND b = ?;&''''''&'''''''''''''''&'''''''''&''''''&''''''''''''''''''&()*+,-)(),.//012-34-+/)()4-+)))))()5.6/)()$7*58))))))))))))(&''''''&'''''''''''''''&'''''''''&''''''&''''''''''''''''''&()()*))()+,-./01)))))))()*123097)())))2)():;;<)=/0>?)0>9-7)(&''''''&'''''''''''''''&'''''''''&''''''&''''''''''''''''''&

...WHERE b = ?;&''''''&'''''''''''''''&'''''''''&''''''&''''''''''''''''''&()*+,-)(),.//012-34-+/)()4-+)))))()5.6/)()$7*58))))))))))))(&''''''&'''''''''''''''&'''''''''&''''''&''''''''''''''''''&()5-@))()*123097)))))))()*123097)())))A)():;;<)=/0>?)0>9-7)(&''''''&'''''''''''''''&'''''''''&''''''&''''''''''''''''''&


Q4: Gut oder schlecht? (LIKE indizieren)CREATE INDEX tbl_idx ON tbl (text);SELECT id, text FROM tbl WHERE text LIKE '%BEGRIFF%';


Q4: Gut oder schlecht? (LIKE indizieren)CREATE INDEX tbl_idx ON tbl (text);SELECT id, text FROM tbl WHERE text LIKE '%BEGRIFF%';

http://use-the-index-luke.com/de/sql/where/bereiche/like-filter




B-Tree-Indizes unterstützen keine führenden Wildcard-Zeichen.

&'''''''&'''''''''''''''&'''''''''&'''''''&''''''''''''''''''&()*+,-))(),.//012-34-+/)()4-+)))))())5.6/)()$7*58))))))))))))(&'''''''&'''''''''''''''&'''''''''&'''''''&''''''''''''''''''&()/30)1)()4566))))))))))()*123097)()27:8;)():;;<)=/0>?)0>9-7)(&'''''''&'''''''''''''''&'''''''''&'''''''&''''''''''''''''''&

Brauchst du wirklich lexikalische Suche?Ist Volltext-Suche eine Option?

Externe Such-Lösung?


Q5: Wie ändert sich die Performance? (IOS)

234564$7894:$#;*,%'($$$$$$$$$$$<8$#;*$=+0$'+#",B)*_-&>?

@4A426$'+#",B)*_-&$$$$$0$B)_&#=`>$$C3<D$#;*$/E434$+$F$\$a3<S^$]K$'+#",B)*_-&?

=bHGG$c"%*"&>

@4A426$'+#",B)*_-&$$$$$0$B)_&#=`>$$C3<D$#;*$/E434$+$F$\$$$+6B5>5C5D$a3<S^$]K$'+#",B)*_-&?

=bHG$c"%*"&>


Vorher war ein Index-Only-Scan möglich (“covering Index”), nachher nicht mehr.&''''''&'''''''''''''''&'''''''''&''''''''''''''''''''''''''&()*+,-)(),.//012-34-+/)()4-+)))))()$7*58))))))))))))))))))))(&''''''&'''''''''''''''&'''''''''&''''''''''''''''''''''''''&()5-@))()*123097)))))))()*123097)()=/0>?)6B-5-C)5</3=&/30)1)(&''''''&'''''''''''''''&'''''''''&''''''''''''''''''''''''''&

&''''''&'''''''''''''''&'''''''''&'''''''''''''&()*+,-)(),.//012-34-+/)()4-+)))))()$7*58)))))))(&''''''&'''''''''''''''&'''''''''&'''''''''''''&()5-@))()*123097)))))))()*123097)()=/0>?)6B-5-)(&''''''&'''''''''''''''&'''''''''&'''''''''''''&

http://use-the-index-luke.com/blog/2014-01/unreasonable-defaults-primary-key-clustering-key

Kleiner Unterschied,

große Wirkung.



3-Minuten Quiz: 60% fallen durch!

Nur knapp 40% beantwortenvier oder mehr Fragen richtig.

(Mit Raten alleine würden 12.5% bestehen)

Volkskrankheit: stiefmütterliche Indizierung

Jeder weiss, dass Indizierung wichtig für Performance ist,

aber niemand nimmt sich Zeit es ordentlich zu lernen und

durchzuführen.

Volkskrankheit: stiefmütterliche Indizierung

Index-Details sind kaum bekannt! „Details“ wie die Spaltenreihenfolge oder Bereichs-

suchen müssen gelernt und verstanden werden.

Nur eine Funktion wird verwendet: schnell suchen.! Indizes haben drei Funktionen (Mächte/Powers):

Daten finden, Daten gruppieren, Daten sortieren.

Abfragen werden einzeln Indiziert!Man muss aus Applikationssicht indizieren (alle

Abfragen beachten). Das ist eine Design-Aufgabe!

Volkskrankheit: Stiefmütterliche Indizierung

Legst Du Indizes einfach an

oder

designst du Indizes?

Über Markus Winand

Ich tune Entwickler auf SQL-Performance.

Training & Tuning: winand.at

Autor von: SQL Performance Explained

Geeky blog: use-the-index-luke.com

http://use-the-index-luke.com


Über Markus Winand

use-the-index-luke.com



Volkskrankheit "Stiefmuetterliche Indizierung"

Technology

Transcript of Volkskrankheit "Stiefmuetterliche Indizierung"