Unikt index i vy

  • Författare
  • Meddelande

ylvalajn

troubleshooter

  • Inlägg: 213
  • Blev medlem: 11 feb 2013, 13:52

Unikt index i vy

Inlägg24 maj 2019, 12:24

Hej !

Jag använder en vy för att ta fram info ur databasen på ett sammanställt sätt.

Kod: Markera allt
DROP VIEW IF EXISTS v_lageroversikt;
CREATE VIEW v_lageroversikt
   AS
   SELECT
      s.id AS 'smyckes_id',
      s.namn AS 'smyckes_namn',
      s.pris AS 'smyckes_pris',
      l.id AS 'lagerplats_id',
      l.plats AS 'lagerplats',
      l.antal AS 'antal_produkter',
    k.namn AS 'kategori'
   FROM smycke AS s
      JOIN lagerplats AS l
      ON s.id = l.smycke
        JOIN kategori AS k
        ON s.id = k.smycke
        GROUP BY s.id;


Då jag söker i vyn så ser jag med EXPLAIN att en sökning på t ex ett specifikt smycke genererar en full scan av smyckes tabellen, det finns alltså ingen indexering. Det verkar vara så för övriga entiteter i vyn också.

Kan jag göra en unik indexering i vyn? Hur gör jag det?
Användarvisningsbild

mos

dbwebb

  • Inlägg: 10914
  • Blev medlem: 10 nov 2011, 09:52
  • Ort: Ronneby / Bankeryd

Re: Unikt index i vy

Inlägg27 maj 2019, 12:58

Jobba med EXPLAIN på den SELECT-sats som ligger i vyn. Skapa nycklar så som vi gör i kmom06.

Jobba främst med de kolumner som används i ON, GROUP BY och WHERE. Det bör ta dig rätt långt.
...
..:
.... /mos

ylvalajn

troubleshooter

  • Inlägg: 213
  • Blev medlem: 11 feb 2013, 13:52

Re: Unikt index i vy

Inlägg28 maj 2019, 08:39

Ja...

Mer specifikt så undrar jag varför den söker igenom alla smyckena för att hitta ett smycke via dess id, trots att det är en PRIMARY KEY i smyckes tabellen och är där indexerad.

Kod: Markera allt
SELECT * FROM v_lageroversikt WHERE smyckes_id = 'jewel1';


Ovanstående genererar full scan, medan kommandot under hittar direkt.

Kod: Markera allt
SELECT * FROM smycke WHERE id = 'jewel1'


Jag försökt att sätta ett index i vyn men får svaret att det inte är ett 'BASE TABLE' så tänkte det kanske finns ngt annat sätt. Annars får det vara så, men betyder ju då att en vy kan försämra prestandan i exempel där det finns väldigt många rader.
Användarvisningsbild

mos

dbwebb

  • Inlägg: 10914
  • Blev medlem: 10 nov 2011, 09:52
  • Ort: Ronneby / Bankeryd

Re: Unikt index i vy

Inlägg28 maj 2019, 09:25

Du har två olika WHERE-villkor ovan, du använder två olika kolumner. Är det skrivfel? Annars behöver du kolla så att båda kolumnerna verkligen har ett index kopplat till sig.
...
..:
.... /mos

ylvalajn

troubleshooter

  • Inlägg: 213
  • Blev medlem: 11 feb 2013, 13:52

Re: Unikt index i vy

Inlägg29 maj 2019, 06:41

Hmm, jag kanske tänker fel ...

men alltså i vyn så kopplas ju detta:
(s för smycke)

Kod: Markera allt
s.id AS 'smyckes_id',


Så jag tänkte att det som är 'smyckes_id' är ungefär ett smeknamn på kolumnen, mer för presentation. Och att i själva databasen det ändå är refereatet s.id som gäller, som alltså står för table: smycke & kolumn: id och därmed är select satserna överenstämmande. Id är ju det som söks på som är PRIMARY key.

Även om jag byter namn i vyn så att jag istället använder specifikt 'id' i vy sökningen så söker den ändå igenom alla raderna.

Alltså om jag gör om
Kod: Markera allt
s.id AS 'smyckes_id',

i vyn till
Kod: Markera allt
s.id AS 'id',

så att det går att köra

Kod: Markera allt
EXPLAIN SELECT * FROM v_lageroversikt WHERE id = 'jewel1';


så letar ändå db:n igenom alla smyckesraderna och hittar inte direkt smycket som då jag inte kör via vyn.

Denna hittar direkt:
Kod: Markera allt
EXPLAIN SELECT * FROM smycke WHERE id = 'jewel1';


Jag antar det var så du menade med att WHERE satserna inte är desamma.
Det är möjligt att jag tänker fel på ngt sätt.

Jag har översiktligt kollat i MySQL dokumentation och hittat att det inte går att skapa ett index till en vy....
[url]https://dev.mysql.com/doc/refman/8.0/en/view-restrictions.html
[/url]

Du brukar ha så bra förklaringar & sätt :)

Iaf så att vyn hittar till indexet som redan finns i form av primär nyckel.

Det lite märkliga tycker jag är att de andra tables ... nämligen l (lager) och k (kund) hittar direkt rätt och i EXPLAIN tabellen som blir av att kontrollera en SELECT sats så står det i tabellen extra ' Using index condition' : där är smycket ett FOREIGN KEY . Varför används inte index condition på smyckes id som är en primär nyckel?

Såhär ser en del av utskriften ifrån EXPLAIN ut.... Det finns alltså bara 5 smycken just nu.

Det är rad 3 :: benämning 's' (smycke) :: och det som är under rows som tyder på att det kommer bli väldigt långa vy sökningar. Och denna vyn är en bas i mitt system just nu så det är inte så bra ifall den genererar långa sökningar.

Kod: Markera allt
id, select_type, table, rows, filtered,Extra
1,PRIMARY, const, 1,100.00,NULL
2,DERIVED, s, 5, 100.00,NULL
2,DERIVED, l, 1,  100.00,"Using index condition"
2,DERIVED, k,1,100.00,"Using index condition"


Som jag förstår så sätts UNIQUE INDEX på en kolumn (och i detta fall per automatik via PRIMARY KEY). Borde inte den gälla då för alla som söker kolumnen? Även om en vy söker den, i sitt arbete.
Men jag har kanske glömt / missat / missförstått ngt.
Användarvisningsbild

mos

dbwebb

  • Inlägg: 10914
  • Blev medlem: 10 nov 2011, 09:52
  • Ort: Ronneby / Bankeryd

Re: Unikt index i vy

Inlägg29 maj 2019, 16:35

När det händer saker som jag själv inte förstår eller kan förklara så bygger jag ofta små exempelprogram. I detta fallet vill vi försäkra oss om att en vy hanterar index på samma sätt som en tabell. Det är en vinkling av din fråga.

Jag gör en liten tabell med fem rader och endast en primärnyckel som också är ett index och en vy mot tabellen och lite EXPLAIN för att se hur det funkar.

Först SQL-kod.
Kod: Markera allt
DROP TABLE IF EXISTS a;
CREATE TABLE a (
    id CHAR(3) PRIMARY KEY
);

DROP VIEW IF EXISTS v_a;
CREATE VIEW v_a AS
SELECT id AS theID FROM a;

INSERT INTO a VALUES ('111'), ('222'), ('333'), ('444'), ('555');

EXPLAIN SELECT * FROM a;
EXPLAIN SELECT * FROM v_a;

EXPLAIN SELECT * FROM a WHERE id = '333';
EXPLAIN SELECT * FROM v_a WHERE theId = '333';


Sen kör jag det för att se vad som händer. Låt oss se de sista utskrifterna från EXPLAIN.

Först varianter med full table scan.
Kod: Markera allt
MariaDB [moc]> EXPLAIN SELECT * FROM a;
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|    1 | SIMPLE      | a     | index | NULL          | PRIMARY | 12      | NULL |    5 | Using index |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

MariaDB [moc]> EXPLAIN SELECT * FROM v_a;
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|    1 | SIMPLE      | a     | index | NULL          | PRIMARY | 12      | NULL |    5 | Using index |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)


Sen liknande men där indexen används.
Kod: Markera allt
MariaDB [moc]> EXPLAIN SELECT * FROM a WHERE id = '333';
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|    1 | SIMPLE      | a     | const | PRIMARY       | PRIMARY | 12      | const |    1 | Using index |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)

MariaDB [moc]> EXPLAIN SELECT * FROM v_a WHERE theId = '333';
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|    1 | SIMPLE      | a     | const | PRIMARY       | PRIMARY | 12      | const |    1 | Using index |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)


Vi kan då se att index och tabeller/vyer hanteras på samma sätt. Ingen överraskning där.

Så, vi sätter index på tabellen och inte på vyn, den biten är klar.

jag är (för tillfället) inte medveten om att index skulle betee sig annorlunda i en vy än i en tabell. Det låter lite udda. Hade jag själv fått det så hade jag skapat om vyn och testat EXPLAIN igen.

Nu har jag inte testat med exakt ditt exempel där du har tre tabeller inblandade. Jag tycker dock att följande borde ge samma svar från EXPLAIN.
Kod: Markera allt
EXPLAIN SELECT
      s.id AS 'smyckes_id',
      s.namn AS 'smyckes_namn',
      s.pris AS 'smyckes_pris',
      l.id AS 'lagerplats_id',
      l.plats AS 'lagerplats',
      l.antal AS 'antal_produkter',
    k.namn AS 'kategori'
   FROM smycke AS s
      JOIN lagerplats AS l
      ON s.id = l.smycke
        JOIN kategori AS k
        ON s.id = k.smycke
        GROUP BY s.id
    WHERE s.id = 'smycke1';

bör ge samma svar som
Kod: Markera allt
EXPLAIN SELECT * FROM v_lageroversikt WHERE 'smyckes_id' = 'smycke1';


Om det inte blir på det viset så hade vi nog behövt din SQL-fil för att kunna sätta upp en liknande databas och testa med, på samma sätt som jag satt upp mitt egna lilla testfall.
...
..:
.... /mos

ylvalajn

troubleshooter

  • Inlägg: 213
  • Blev medlem: 11 feb 2013, 13:52

Re: Unikt index i vy

Inlägg03 jun 2019, 07:37

Ok, bra tips med att göra små egna program att testa m.

Jo, men det stämmer att indexeringen fungerar inom vyn ser jag nu då jag provar att köra EXPLAIN på hela SELECT satsen för vyn & vid WHERE query som du föreslog:

Kod: Markera allt
EXPLAIN SELECT * FROM v_lageroversikt;

EXPLAIN SELECT * FROM v_lageroversikt WHERE id='jewel1';


Här kan jag se att indexeringen fungerar genom att enbart en rad genomsöks vid WHERE query.
Jag ser att rad nr 1 ändras.

Det som förvirrade mig var att jag jämförde rapporten ifrån EXPLAIN tillhörande vyn & orginal table på enbart indexeringen.

Kod: Markera allt
EXPLAIN SELECT * FROM v_lageroversikt WHERE id = 'jewel1';

EXPLAIN SELECT * FROM smycke WHERE id = 'jewel1';


I explain rapporten av vyn tittade jag på rad nr2 där det står att table är 's' som envisas med att stå kvar på 5 raders genomsökning oavsett. Jag trodde att 's' stod för smycke - som ju det table heter som vyn ska syfta till. Jag tänkte att det skulle stå 1 där då indexeringen fungerar korrekt.


Kod: Markera allt
MySQL [eshop]> EXPLAIN SELECT * FROM v_lageroversikt\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 8
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
        table: s
   partitions: NULL
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 150
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: NULL
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: l
   partitions: NULL
         type: ref
possible_keys: smycke
          key: smycke
      key_len: 303
          ref: eshop.s.id
         rows: 1
     filtered: 100.00
        Extra: Using index condition
*************************** 4. row ***************************
           id: 2
  select_type: DERIVED
        table: k
   partitions: NULL
         type: ref
possible_keys: kat_smycke
          key: kat_smycke
      key_len: 63
          ref: eshop.s.id
         rows: 1
     filtered: 100.00
        Extra: Using index condition
4 rows in set, 1 warning (0.00 sec)

MySQL [eshop]>


Kod: Markera allt
MySQL [eshop]> EXPLAIN SELECT * FROM v_lageroversikt WHERE id='jewel1' \G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
   partitions: NULL
         type: ref
possible_keys: <auto_key0>
          key: <auto_key0>
      key_len: 150
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
        table: s
   partitions: NULL
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 150
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: NULL
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: l
   partitions: NULL
         type: ref
possible_keys: smycke
          key: smycke
      key_len: 303
          ref: eshop.s.id
         rows: 1
     filtered: 100.00
        Extra: Using index condition
*************************** 4. row ***************************
           id: 2
  select_type: DERIVED
        table: k
   partitions: NULL
         type: ref
possible_keys: kat_smycke
          key: kat_smycke
      key_len: 63
          ref: eshop.s.id
         rows: 1
     filtered: 100.00
        Extra: Using index condition
4 rows in set, 1 warning (0.00 sec)



Det är alltså rad 1 som ändras och indexeringen fungerar korrekt.

Bra, tack !

Vad är den andra raden för? Den som går igenom 5 rader oavsett indexering.
Användarvisningsbild

mos

dbwebb

  • Inlägg: 10914
  • Blev medlem: 10 nov 2011, 09:52
  • Ort: Ronneby / Bankeryd

Re: Unikt index i vy

Inlägg03 jun 2019, 08:52

ylvalajn skrev:Vad är den andra raden för? Den som går igenom 5 rader oavsett indexering.


Ett derived table är en temporär tabell som frågeoptimeraren kan använda när frågan exekveras. En tabell som skapas enbart för att kunna ge svaret.

När man räknar antalet rader så vill man gärna se hur många radet som ligger i respektive tabell, då kan man alltid referera till det.

Din SELECT joinar ett antal olika tabeller, pröva att bryta ned den i tre, eller fler, mindre frågor och kör EXPLAIN på dem och se om det ser okey ut. Då bör det bli enklare att försöka spåra var de 5 raderna kommer ifrån och om det känns rimligt.
...
..:
.... /mos

ylvalajn

troubleshooter

  • Inlägg: 213
  • Blev medlem: 11 feb 2013, 13:52

Re: Unikt index i vy

Inlägg03 jun 2019, 10:37

Okej...

Så eftersom smyckestabellen har en PRIMARY KEY (id) och det är den som används enligt denna EXPLAIN utskriften (kolumnen 'key' visar)... så bör rad 2 med 5 rows vara rimligt och så som det ska vara.

Det är enbart id, namn & pris som skrivs ut i vyn ifrån tabellen m smycken.
Rad 2 syftar dit: ifall jag sätter på INDEX på ngn av de icke-primära (namn/pris) så dyker de index upp som 'possible keys' i tabellen på rad 2. Men det är ändå primary key som används enligt kolumnen 'key'. Så det tabellen säger med rad 2 är att den har ett primary index som key till sökningen på ett smycke men ändå skapar den en deriverad tabell på 5 rows. Som du säger - kan vara bra att ha för att veta tabellens storlek.

Vilka är online

Användare som besöker denna kategori: Inga registrerade användare och 8 gäster