[[oktatas:adatbazis-kezeles:mariadb:sql|< SQL]]

====== Optimalizálás ======

  * **Szerző:** Sallai András
  * Copyright (c) 2019, Sallai András
  * Szerkesztve: 2019, 2021
  * Licenc: [[https://creativecommons.org/licenses/by-sa/4.0/|CC BY-SA 4.0]]
  * Web: https://szit.hu

===== Az optimalizálásról =====

Amivel optimalizálhatunk:
  * indexelés
  * lekérdezés változtatása
    * például: több feltétel
  * adatszerkezet módosítása
    * megfelelő mezőtípusok választása

===== Az indexekről =====

Az indexek négy fő típusa:
  * elsődleges kulcs (egyedi és nem null)
  * egyedi index (egyedi és lehet null is)
  * egyszerű indexek (nem feltétlenül egyedi)
  * teljes szöveges (keresés szövegben)


A kulcs és az index elnevezéseket általában felcserélhetően használják.
===== Indexelés =====

A lekérdezések során, ha egy oszlop indexelve van, segíti a keresett érték megtalálását. 
Minden oszlopot viszont mégsem indexelünk, mert az meg lassítja az adatok írását. 


Mit indexeljünk?
  * Azokat az oszlopokat, amelyek sokszor szerepelnek egy lekérdezés WHERE záradékában.

===== Index készítése =====

Szintaxis ((A create index teljes szintaxisa: https://mariadb.com/kb/en/create-index/)):

Index létrehozása:
<code mysql>
CREATE [OR REPLACE] [UNIQUE|FULLTEXT|SPATIAL] INDEX 
  [IF NOT EXISTS] indexnév 
  [index_típusa]
  ON táblanév (mezőnév, ...)
</code>

Index törlése:
<code mysql>
DROP INDEX [IF EXISTS] indexnév ON táblanév
</code>


Index típusok:
  * normál -- nem egyedi értékekre is használható - B-fa-t (B-tree) hoz létre
  * unique -- csak egyedi értékek lehetnek, nem lehet null
  * fulltext -- keresés szövegben - speciális keresőmotor működik
  * spatial -- koordinátarendszerek számára - R-fa-t (R-tree) hoz létre
    * térbeli objektumok tárolása
    * pontok, téglalapok, sokszögek rendezésére javasolt

Index tárolás típusa:
  USING {BTREE | HASH | RTREE}


Index létrehozása:
<code mysql>
create index nev_index on dolgozok(nev);
</code>


Indexek lekérdezése:
<code mysql>
show indexes from  dolgozok\G
</code>

Az index törlése:
<code mysql>
drop index nev_index on dolgozok;
</code>


<code mysql>
create index telepules_index2 
using btree 
on dolgozok(telepules);
</code>

===== Gyakorlat 01 =====

Készítse el a következő adatbázist. 

<code mysql>
create database domosbt 
character set utf8 
collate utf8_hungarian_ci;

grant all privileges 
on domosbt.* 
to domosbt@localhost
identified by 'titok';

use domosbt;

create table dolgozok(
    az int not null primary key auto_increment,
    nev varchar(50),
    telepules varchar(50),
    fizetes double,
    jutalom double,
    belepes date
);

insert into dolgozok 
(nev, telepules, fizetes, jutalom, belepes)
values
('Prank Attila', 'Szeged', 2875000, 112000, '2000-01-01'),
('Lant Erzsébet', 'Szolnok', 2942000, 50000, '2000-02-01');
</code>

  - Hozzon létre egy egyszerű indexet netto mezőre.


===== VIEW =====

A nézettáblák nem optimalizálják a lekérdezést, maximum átláthatóbbá teszik.


Szintaxis ((A create view szintaxis teljes leírása: https://mariadb.com/kb/en/create-view/)):
<code mysql>
CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW [IF NOT EXISTS] nézetnév [(column_list)]
    AS select_utasítás
</code>

Legyen egy dolgozok tábla:

<code mysql>
create table dolgozok(
    az int not null primary key auto_increment,
    nev varchar(50),
    anyjaneve varchar(50),
    telepules varchar(50),
    cim varchar(50),
    netto double,
    juttatas double,
    belepes date,
    szuletes date,
    szulhely varchar(50)
);
</code>







Nézet létrehozása:
<code mysql>
create view simp as 
select nev, telepules, netto from dolgozok;
</code>

A táblák között látszanak a nézetek:
<code mysql>
show tables;
</code>

A létrehozott nézetről:
<code mysql>
show create view simp \G
</code>
===== EXPLAIN =====

Az explain ((Több információ az explainról: https://mariadb.com/kb/en/explain/)) SQL utasítást nem hajtja végre, helyette analizálja.


Példa adatbázis:
<code mysql>
create database fer 
character set utf8 
collate utf8_hungarian_ci;

grant all privileges 
on fer.* 
to fer@localhost
identified by 'titok';

use fer;

create table dolgozok(
    az int not null primary key,
    nev varchar(50),
    telepules varchar(50),
    fizetes double
);

insert into dolgozok
values
(1,'Pardon Béla','Szolnok',2837000),
(2,'Lovas Beáta','Szeged',870500),
(3,'Pedro Péter','Szolnok',2837000),
(4,'Ton Irén','Szeged',2837000),
(5,'Frank Olivér','Szolnok',2837000),
(6,'Lapos Bence','Miskolc',2837000),
(7,'Kerek Katalin','Miskolc',1870000),
(8,'Pardon Béla','Hatvan',1537000),
(9,'Pardon Béla','Szeged',1400000);
</code>



Kérdezzük le a dolgozók táblát:
<code mysql>
MariaDB [fer]> select * from dolgozok;
+----+---------------+-----------+---------+
| az | nev           | telepules | fizetes |
+----+---------------+-----------+---------+
|  1 | Pardon Béla   | Szolnok   | 2837000 |
|  2 | Lovas Beáta   | Szeged    |  870500 |
|  3 | Pedro Péter   | Szolnok   | 2837000 |
|  4 | Ton Irén      | Szeged    | 2837000 |
|  5 | Frank Olivér  | Szolnok   | 2837000 |
|  6 | Lapos Bence   | Miskolc   | 2837000 |
|  7 | Kerek Katalin | Miskolc   | 1870000 |
|  8 | Pardon Béla   | Hatvan    | 1537000 |
|  9 | Pardon Béla   | Szeged    | 1400000 |
+----+---------------+-----------+---------+
9 rows in set (0.000 sec)

</code>


Példa:
<code mysql>
explain select * from dolgozok;
</code>

Kimenettel
<code mysql>
MariaDB [fer]> explain select * from dolgozok;
+------+-------------+----------+------+---------------+------+---------+------+------+-------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+------+------+-------+
|    1 | SIMPLE      | dolgozok | ALL  | NULL          | NULL | NULL    | NULL |    9 |       |
+------+-------------+----------+------+---------------+------+---------+------+------+-------+
1 row in set (0.000 sec)
</code>

Mivel nem volt szűrés az összes táblán végigment a select.


Lássuk szűréssel:
<code mysql>
MariaDB [fer]> explain select * from dolgozok where telepules="Szeged";
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | dolgozok | ALL  | NULL          | NULL | NULL    | NULL |    9 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.000 sec)

</code>

Így is végignézte az összes rekordot.

Most adjunk a telepules táblához indexet:

<code mysql>
MariaDB [fer]> create index telepules_index on dolgozok(telepules);
Query OK, 0 rows affected (0.046 sec)
Records: 0  Duplicates: 0  Warnings: 0
</code>

Elemezzük újra a lekérdezést:
<code mysql>
MariaDB [fer]> explain select * from dolgozok where telepules="Szeged";
+------+-------------+----------+------+-----------------+-----------------+---------+-------+------+-----------------------+
| id   | select_type | table    | type | possible_keys   | key             | key_len | ref   | rows | Extra                 |
+------+-------------+----------+------+-----------------+-----------------+---------+-------+------+-----------------------+
|    1 | SIMPLE      | dolgozok | ref  | telepules_index | telepules_index | 53      | const |    3 | Using index condition |
+------+-------------+----------+------+-----------------+-----------------+---------+-------+------+-----------------------+
1 row in set (0.001 sec)
</code>

Ha megnézzük a rows oszlopot, láthatjuk, most már csak 3 sort dolgozott fel.

===== Linkek =====

  * https://mariadb.com/kb/en/the-essentials-of-an-index/ (2021)
  * https://mariadb.com/kb/en/getting-started-with-indexes/ (2021)
  * https://mariadb.com/kb/en/create-index/ (2021)