• Szerző: Sallai András
• Copyright © 2019, Sallai András
• Szerkesztve: 2019, 2021
• Licenc: CC BY-SA 4.0
• Web: https://szit.hu

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 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.

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.

Szintaxis ¹⁾:

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:

• 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:

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

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.

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

Szintaxis ²⁾:

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

Az explain ³⁾ 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.

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