Zum Hauptinhalt springen

Tag 9

1 Dokumentationsaufträge​

1.1 Kurzzusammenfassung zu den Performance Tips​

1.1.1 Attribute indexieren​

Alle Attribute die bei WHERE, ORDER BY und GROUP BY genutzt werden, sollten indexiert sein.
-> Tag 4 - Kapitel 2.1.4

1.1.1.1 Problem​
select customer_id, customer_name from customers where customer_id='140385';

Wird dieses Statement auf einer Datenbank mit 500 Datensätzen ausgeführt, werden alle Werte überprüft.

Mit einem EXPLAIN kann man überprüfen, was bei der Abfrage passiert.

explain select customer_id, customer_name from customers where customer_id='140385';
OUTPUT
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  500 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

key: Ist der key auf NULL, ist das immer schlecht da er keinen key zum suchen nutzt.
rows: Dieses Attribut zeigt wie viele Datensätze gesucht werden.

1.1.1.2 Lösung​

Index erstellen

Create index customer_id ON customers (customer_Id);

Abfrage per EXPLAIN überprüfen

Explain select customer_id, customer_name from customers where customer_id='140385';
OUTPUT
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | customers | NULL       | ref  | customer_id   | customer_id | 13      | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+

Da jetzt ein key existiert und dieser genutzt wird, wird nicht mehr NULL angezeigt. Auch kann man sehen, dass bei rows nur noch ein Datensatz gesucht wird, da er nach der customer_id suchen kann.

1.1.2 UNIONS verwenden​

Wenn bei einem WHERE-Statement mit OR gearbeitet werden muss, sollte ein UNION genutzt werden. Dieser kombiniert die Resultate von mehreren SELECTS.

1.1.2.1 Problem​

Wenn Abfragen mit OR durchgeführt werden, wird immer ein Full-Table-Scan gemacht, auch wenn man einen Index nutzt. Mit UNION kann dieses Problem gelöst werden.

select * from students where first_name like 'Ade%'  or last_name like 'Ade%' ;
info

first_name und last_name sind indexiert.

1.1.2.2 Lösung​
select * from students where first_name like 'Ade%' union all
select * from students where last_name like 'Ade%' ;

1.1.3 Wildcards​

Wildcards (*) sollten vermeidet werden... ganz einfach.

1.1.3.1 Problem​
select * from students where first_name like '%Ade' ;
1.1.3.2 Lösung​
select last_name from students where first_name like '%Ade' ;

1.1.4 Full-Text-Searches nutzen​

Full-Text-Searches ist besser als Wildcard

1.1.4.1 Problem​
select * from students where first_name like  '%Ade' ;
1.1.4.2 Lösung​
Alter table students ADD FULLTEXT (first_name, last_name);
Select * from students where match(first_name, last_name) AGAINST ('Ade');
explain Select * from students where match(first_name, last_name) AGAINST ('Ade');
OUTPUT
+----+-------------+----------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------+
| id | select_type | table    | partitions | type     | possible_keys | key        | key_len | ref   | rows | filtered | Extra                         |
+----+-------------+----------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------+
|  1 | SIMPLE      | students | NULL       | fulltext | first_name    | first_name | 0       | const |    1 |   100.00 | Using where; Ft_hints: sorted |
+----+-------------+----------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------+

Der SELECT sucht jetzt in first_name und last_name nach dem Suchbegriff Ade. Dies kann auch im EXPLAIN unter type gesehen werden.

1.1.5 Datenbank-Design​

Grundsätzlich gilt:

  • Redunante Daten vermeiden
  • Korrekte Datentypen verwenden
  • NULL-Werte vermeiden (EXPLAIN hilft hier)
  • Viele Spalten in einer Tabellen vermeiden
  • SQL-Joins "klein" halten. Nur die notwendigen Tabellen einbeziehen.
  • Keine Wildcards

1.2 Übungsdokumentation zu Performance und Indexing​

  1. DB installieren (Skript)

  2. Es existieren noch keine Indexe auf der Datenbank. Mit EXPLAIN kann das überprüft werden.

EXPLAIN SELECT * FROM orderdetails d
    INNER JOIN orders o ON d.orderNumber = o.orderNumber
    INNER JOIN products p ON p.productCode = d.productCode
    INNER JOIN productlines l ON p.productLine = l.productLine
    INNER JOIN customers c on c.customerNumber = o.customerNumber
WHERE o.orderNumber = "10101";
OUTPUT
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | l     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    7 |   100.00 | NULL                                       |
|  1 | SIMPLE      | p     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  110 |    10.00 | Using where; Using join buffer (hash join) |
|  1 | SIMPLE      | o     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  326 |    10.00 | Using where; Using join buffer (hash join) |
|  1 | SIMPLE      | c     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  122 |    10.00 | Using where; Using join buffer (hash join) |
|  1 | SIMPLE      | d     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 2996 |     1.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
5 rows in set, 1 warning (0.04 sec)

type: steht auf ALL, somit wird die ganze Tabelle durchsucht (also kein Index oder Fulltext)
keys und possible_key: steht auf NULL, da es keine Indexe gibt
rows: Es werden 7 * 110 * 326 * 122 * 2'996 = 91'750'822'240 (91 Mrd.) Elemente gesucht

  1. Primary Key Indexe einführen
ALTER TABLE customers ADD PRIMARY KEY (customerNumber);
ALTER TABLE employees ADD PRIMARY KEY (employeeNumber);
ALTER TABLE offices ADD PRIMARY KEY (officeCode);
ALTER TABLE orderdetails ADD PRIMARY KEY (orderNumber, productCode);
ALTER TABLE orders ADD PRIMARY KEY (orderNumber), ADD KEY (customerNumber);
ALTER TABLE payments ADD PRIMARY KEY (customerNumber, checkNumber);
ALTER TABLE productlines ADD PRIMARY KEY (productLine);
ALTER TABLE products ADD PRIMARY KEY (productCode), ADD KEY (buyPrice), ADD KEY (productLine);
ALTER TABLE productvariants ADD PRIMARY KEY (variantId), ADD KEY (buyPrice), ADD KEY (productCode);
  1. Per EXPLAIN die Abfrage wieder überprüfen
EXPLAIN SELECT * FROM orderdetails d
    INNER JOIN orders o ON d.orderNumber = o.orderNumber
    INNER JOIN products p ON p.productCode = d.productCode
    INNER JOIN productlines l ON p.productLine = l.productLine
    INNER JOIN customers c on c.customerNumber = o.customerNumber
WHERE o.orderNumber = "10101";
OUTPUT
+----+-------------+-------+------------+--------+------------------------+---------+---------+------------------------------+------+----------+-----------------------+
| id | select_type | table | partitions | type   | possible_keys          | key     | key_len | ref                          | rows | filtered | Extra                 |
+----+-------------+-------+------------+--------+------------------------+---------+---------+------------------------------+------+----------+-----------------------+
|  1 | SIMPLE      | o     | NULL       | const  | PRIMARY,customerNumber | PRIMARY | 4       | const                        |    1 |   100.00 | NULL                  |
|  1 | SIMPLE      | c     | NULL       | const  | PRIMARY                | PRIMARY | 4       | const                        |    1 |   100.00 | NULL                  |
|  1 | SIMPLE      | d     | NULL       | ref    | PRIMARY                | PRIMARY | 4       | const                        |    4 |   100.00 | Using index condition |
|  1 | SIMPLE      | p     | NULL       | eq_ref | PRIMARY,productLine    | PRIMARY | 17      | explaintesting.d.productCode |    1 |   100.00 | NULL                  |
|  1 | SIMPLE      | l     | NULL       | eq_ref | PRIMARY                | PRIMARY | 52      | explaintesting.p.productLine |    1 |   100.00 | NULL                  |
+----+-------------+-------+------------+--------+------------------------+---------+---------+------------------------------+------+----------+-----------------------+
5 rows in set, 1 warning (0.00 sec)

Jetzt hat jede Tabelle einen definierten type und nutzt immer einen key. Die rows können jetzt nochmals nachgerechnet werden. 1 * 1 * 4 * 1 * 1 = 4. Natürlich viel besser.