Tartalomjegyzék

< SQL

Optimalizálás

Az optimalizálásról

Amivel optimalizálhatunk:

Az indexekről

Az indexek négy fő típusa:

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?

Index készítése

Szintaxis 1):

Index létrehozása:

CREATE [OR REPLACE] [UNIQUE|FULLTEXT|SPATIAL] INDEX 
  [IF NOT EXISTS] indexnév 
  [index_típusa]
  ON táblanév (mezőnév, ...)

Index törlése:

DROP INDEX [IF EXISTS] indexnév ON táblanév

Index típusok:

Index tárolás típusa:

USING {BTREE | HASH | RTREE}

Index létrehozása:

create index nev_index on dolgozok(nev);

Indexek lekérdezése:

show indexes from  dolgozok\G

Az index törlése:

drop index nev_index on dolgozok;
create index telepules_index2 
using btree 
on dolgozok(telepules);

Gyakorlat 01

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

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');
  1. 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 2):

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW [IF NOT EXISTS] nézetnév [(column_list)]
    AS select_utasítás

Legyen egy dolgozok tábla:

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)
);

Nézet létrehozása:

create view simp as 
select nev, telepules, netto from dolgozok;

A táblák között látszanak a nézetek:

show tables;

A létrehozott nézetről:

show create view simp \G

EXPLAIN

Az explain 3) SQL utasítást nem hajtja végre, helyette analizálja.

Példa adatbázis:

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);

Kérdezzük le a dolgozók táblát:

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)

Példa:

explain select * from dolgozok;

Kimenettel

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)

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

Lássuk szűréssel:

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)

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

Most adjunk a telepules táblához indexet:

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

Elemezzük újra a lekérdezést:

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)

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

Linkek

1)
A create index teljes szintaxisa: https://mariadb.com/kb/en/create-index/
2)
A create view szintaxis teljes leírása: https://mariadb.com/kb/en/create-view/
3)
Több információ az explainról: https://mariadb.com/kb/en/explain/