problem z zapytaniem i użyciem kluczy Opcje

[ChrisB]

witam
zauważyłem jakiś czas temu u siebie dużą wielkość Handler_read_rnd_next, po około godzinie pracy serwera: 2 530 k

i teraz, włączyłem logowanie do pliku zapytań nie wykorzystujących poprawnie indexów, z bardziej rażących przykładów:

tabela ranking:

[SQL] pobierz, plaintext 
CREATE TABLE `ranking` (
  `id_uzyt` mediumint(7) NOT NULL DEFAULT '0',
  `grupa` smallint(6) NOT NULL DEFAULT '0',
  `data` date DEFAULT NULL,
  `wartosc_portfela` double(13,2) NOT NULL DEFAULT '0.00',
  `miejsce` smallint(6) NOT NULL DEFAULT '0',
  `miejscepop` smallint(6) NOT NULL DEFAULT '0',
  `login` char(20) collate utf8_polish_ci NOT NULL DEFAULT '',
  `procentinv` float(4,2) NOT NULL DEFAULT '0.00',
  `zmianawart1` float(6,2) NOT NULL DEFAULT '0.00',
  `zmianawart2` float(6,2) NOT NULL DEFAULT '0.00',
  `miejscetydz` mediumint(9) NOT NULL,
  `zmianawart3` float(6,2) NOT NULL DEFAULT '0.00',
  `miejscemies` mediumint(9) NOT NULL,
  KEY `miejsce` (`miejsce`),
  KEY `id_uzyt` (`id_uzyt`),
  KEY `grupa` (`grupa`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;
[SQL] pobierz, plaintext 

zapytanie:

[PHP] pobierz, plaintext 
mysql> explain select * from ranking order by miejsce ASC limit 8300,100;
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | ranking | index | NULL          | miejsce | 2       | NULL | 8472 |       |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------+
1 row in set (0.00 sec)
[PHP] pobierz, plaintext 

całkowita liczba rekordów 8472 - skanuje je wszystkie by podać mi wynik

i teraz pytanie brzmi co robię źle?
byłbym wdzięczny za jakieś wskazówki
pozdrawiam

[Mchl]

Nic. Nie ma innej możliwości posortowania wszystkich wierszy w tabeli jak tylko przez posortowanie wszystkich wierszy w tabeli.

Poza tym zauważ, że zapytanie wykorzystuje indeks `miejsce`

[wookieb]

A co długo się wykonuje? Nie zapytanie nie wykorzystuje do końca indeksu.

[SQL] pobierz, plaintext 
EXPLAIN SELECT * FROM tabela ORDER BY miejsce ASC 
[SQL] pobierz, plaintext 

To wykorzysta index pod warunkiem, że będzie na tym polu założony

[SQL] pobierz, plaintext 
EXPLAIN SELECT miejsce FROM tabela ORDER BY miejsce ASC 
[SQL] pobierz, plaintext 

Ten post edytował wookieb 20.05.2010, 15:31:22

[ChrisB]

nie, nie wykonuje się długo, ale za to często, i wylądowało w logu powolnych zapytań i tych które nieprawidłowo używają kluczy (robią full table scan)
łącznie serwer zaczyna przymulać, i ta wartość Handler_read_rnd_next rośnie szybko (gdzie powinna być naprawde niska)

w każdym razie teraz inny przykład:

[PHP] pobierz, plaintext 
# Query_time: 0  Lock_time: 0  Rows_sent: 449  Rows_examined: 950
select id_not,krotka,kurs,ostzamkniecie,volumen from notowania where branza>0 order by krotka;
 
[PHP] pobierz, plaintext 

[PHP] pobierz, plaintext 
mysql> SHOW INDEX FROM notowania;
+-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table     | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| notowania |          0 | PRIMARY     |            1 | id_not      | A         |         501 |     NULL | NULL   |      | BTREE      |         |
| notowania |          0 | KROTKA      |            1 | krotka      | A         |         501 |     NULL | NULL   |      | BTREE      |         |
| notowania |          1 | branza      |            1 | branza      | A         |           5 |     NULL | NULL   |      | BTREE      |         |
| notowania |          1 | nazwa_akcji |            1 | nazwa_akcji | A         |         501 |     NULL | NULL   |      | BTREE      |         |
| notowania |          1 | zmiana      |            1 | zmiana      | A         |         250 |     NULL | NULL   |      | BTREE      |         |
+-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
[PHP] pobierz, plaintext 

wysłało 449, zbadało 950 - przy łącznej ilości rekordów - 501 w bazie

i jeszcze explain:

[PHP] pobierz, plaintext 
mysql> explain extended select id_not,krotka,kurs,ostzamkniecie,volumen from notowania where branza>0 order by krotka;
+----+-------------+-----------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+-----------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | notowania | ALL  | branza        | NULL | NULL    | NULL |  501 | Using where; Using filesort |
+----+-------------+-----------+------+---------------+------+---------+------+------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
[PHP] pobierz, plaintext 

[Mchl]

A co długo się wykonuje? Nie zapytanie nie wykorzystuje do końca indeksu.

[SQL] pobierz, plaintext 
EXPLAIN SELECT * FROM tabela ORDER BY miejsce ASC 
[SQL] pobierz, plaintext 

To wykorzysta index pod warunkiem, że będzie na tym polu założony

[SQL] pobierz, plaintext 
EXPLAIN SELECT miejsce FROM tabela ORDER BY miejsce ASC 
[SQL] pobierz, plaintext 

A dokąd go wykorzystuje jak nie do końca?

select_type - SIMPLE (Simple SELECT)
type - index (This join type is the same as ALL, except that only the index tree is scanned. This usually is faster than ALL because the index file usually is smaller than the data file. )
key - miejsce
key_len - 2

Zmiana powyżej miałaby sens na silniku InnoDB [dodane] No dobra. W MyISAM też, jeśli rzeczywiście potrzebna jest tylko ta jedna kolumna.

Kod

select id_not,krotka,kurs,ostzamkniecie,volumen from notowania where branza>0 order by krotka;

Spróbuj założyć indeks na kolumny (branza,krotka)

Ten post edytował Mchl 20.05.2010, 16:33:48

[ChrisB]

wposcie powyżej wypisałem indeksy z tabeli
id_not, krotka, branza, nazwa_akcji, zmiana - mają nałożone indeksy
tabela ma ponad 80 kolumn więc strukture sobie podaruje

[Mchl]

Ale nie ma tam indeksu obejmującego obie te kolumny.
http://dev.mysql.com/doc/refman/5.0/en/mul...mn-indexes.html

[ChrisB]

człowiek uczy się czegoś co chwilę, nawet nie wiedziałem że tak można:P
w każdym razie:

[PHP] pobierz, plaintext 
+----+-------------+-----------+------+-----------------+------+---------+------+------+-----------------------------+
| id | select_type | table     | type | possible_keys   | key  | key_len | ref  | rows | Extra                       |
+----+-------------+-----------+------+-----------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | notowania | ALL  | branza,branza_2 | NULL | NULL    | NULL |  501 | Using where; Using filesort |
+----+-------------+-----------+------+-----------------+------+---------+------+------+-----------------------------+
[PHP] pobierz, plaintext 

[PHP] pobierz, plaintext 
mysql> SHOW INDEX FROM notowania;
+-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table     | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| notowania |          0 | PRIMARY     |            1 | id_not      | A         |         501 |     NULL | NULL   |      | BTREE      |         |
| notowania |          0 | KROTKA      |            1 | krotka      | A         |         501 |     NULL | NULL   |      | BTREE      |         |
| notowania |          1 | branza      |            1 | branza      | A         |           5 |     NULL | NULL   |      | BTREE      |         |
| notowania |          1 | nazwa_akcji |            1 | nazwa_akcji | A         |         501 |     NULL | NULL   |      | BTREE      |         |
| notowania |          1 | zmiana      |            1 | zmiana      | A         |         250 |     NULL | NULL   |      | BTREE      |         |
| notowania |          1 | branza_2    |            1 | branza      | A         |           5 |     NULL | NULL   |      | BTREE      |         |
| notowania |          1 | branza_2    |            2 | krotka      | A         |         501 |     NULL | NULL   |      | BTREE      |         |
+-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
[PHP] pobierz, plaintext 

chyba dalej coś do końca nie jest tak jak powinno

[wookieb]

Zeby w tym zapytaniu był użyty tylko indeks, musisz mieć założone indeksy na wszystkie kolumny które pobierasz, filtrujesz i sortujesz.
Ale to raczej nie na tym polega...
To że wykonują się często to jedna sprawa, na twoim miejscu pomyślałbym po prostu o cache po stronie php.

Ten post edytował wookieb 20.05.2010, 17:20:48