Performanța MYSQL lent folosind filesort (Programare, Mysql, Performanță, Performanța Bazei De Date, Filesort)

user1052096 a intrebat.

Am o interogare mysql simplă, dar când am o mulțime de înregistrări (în prezent 103,0000), performanța este foarte lentă și spune că folosește filesort, nu sunt sigur dacă acesta este motivul pentru care este lent. Are cineva vreo sugestie pentru a-l accelera? sau să nu mai folosească filesort?

Interogare MYSQL :

SELECT adverts .*    
FROM adverts
WHERE (
price >='0'
)
AND (
adverts.status = 1
)
AND (
adverts.approved = 1
)
ORDER BY date_updated DESC 
LIMIT 19990 , 10

Rezultatele Explică :

id   select_type   table   type    possible_keys    key    key_len    ref    rows   Extra 
1    SIMPLE        adverts range   price            price  4          NULL   103854 Using where; Using filesort

Iată tabelul de anunțuri și indicii:

CREATE TABLE `adverts` (
  `advert_id` int(10) NOT NULL AUTO_INCREMENT,
  `user_id` int(10) NOT NULL,
  `type_id` tinyint(1) NOT NULL,
  `breed_id` int(10) NOT NULL,
  `advert_type` tinyint(1) NOT NULL,
  `headline` varchar(50) NOT NULL,
  `description` text NOT NULL,
  `price` int(4) NOT NULL,
  `postcode` varchar(7) NOT NULL,
  `town` varchar(60) NOT NULL,
  `county` varchar(60) NOT NULL,
  `latitude` float NOT NULL,
  `longitude` float NOT NULL,
  `telephone1` varchar(15) NOT NULL,
  `telephone2` varchar(15) NOT NULL,
  `email` varchar(80) NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `approved` tinyint(1) NOT NULL DEFAULT '0',
  `date_created` datetime NOT NULL,
  `date_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `expiry_date` datetime NOT NULL,
  PRIMARY KEY (`advert_id`),
  KEY `price` (`price`),
  KEY `user` (`user_id`),
  KEY `type_breed` (`type_id`,`breed_id`),
  KEY `headline_keywords` (`headline`),
  KEY `date_updated` (`date_updated`),
  KEY `type_status_approved` (`advert_type`,`status`,`approved`)
) ENGINE=MyISAM AUTO_INCREMENT=103878 DEFAULT CHARSET=utf8

Comentarii

  • Este, de asemenea, lent dacă omiteți LIMIT 19990,10 clauza? Dacă o înlocuiți cu LIMIT 10 este posibil să obțineți o operațiune mult mai rapidă. În acest caz, ar fi bine să vă gândiți cum să utilizați SELECT în loc de LIMIT pentru a prelua acele rânduri. –  > Por O. Jones.
  • Tocmai am adăugat indexul status_approved (status,approved,date_updated). Explicația spune acum că utilizează where în loc de filesort și interogarea durează 0,04 secunde în loc de 2 secunde + ca înainte, cred că acest lucru ar fi putut rezolva problema. –  > Por user1052096.
  • Doar un gând fără legătură… niciuna dintre aceste paranteze nu este necesară. –  > Por Dan Grossman.
5 răspunsuri
Jocelyn

Problema este că MySQL folosește doar un singur index atunci când execută interogarea. Dacă adăugați un indice nou care utilizează cele 3 câmpuri din WHERE clauză, va găsi rândurile mai repede.

ALTER TABLE `adverts` ADD INDEX price_status_approved(`price`, `status`, `approved`);

Conform documentației MySQL ORDER BY Optimizare:

În unele cazuri, MySQL nu poate utiliza indici pentru a rezolva ORDER BY, deși utilizează în continuare indici pentru a găsi rândurile care corespund clauzei WHERE. Aceste cazuri includ următoarele:
Cheia utilizată pentru a prelua rândurile nu este aceeași cu cea utilizată în ORDER BY.

Aceasta este ceea ce se întâmplă în cazul dvs. EXPLAIN ne spune, optimizatorul utilizează cheia price pentru a găsi rândurile. Cu toate acestea ORDER BY se află pe câmpul date_updated care nu aparține cheii price.

Pentru a găsi rândurile mai repede ȘI a sorta rândurile mai repede, trebuie să adăugați un index care să conțină toate câmpurile utilizate în cheia WHERE și în ORDER BY clauze:

ALTER TABLE `adverts` ADD INDEX status_approved_date_updated(`status`, `approved`, `date_updated`);

Câmpul utilizat pentru sortare trebuie să se afle pe ultima poziție în index. Este inutil să includeți price în index, deoarece condiția utilizată în interogare va returna un interval de valori.

Dacă EXPLAIN arată în continuare că utilizează filesort, puteți încerca să forțați MySQL să utilizeze un index ales de dumneavoastră:

SELECT adverts.*
FROM adverts
FORCE INDEX(status_approved_date_updated)
WHERE price >= 0
AND adverts.status = 1
AND adverts.approved = 1
ORDER BY date_updated DESC 
LIMIT 19990, 10

De obicei, nu este necesar să forțați un index, deoarece optimizatorul MySQL face de cele mai multe ori alegerea corectă. Dar, uneori, face o alegere proastă sau nu cea mai bună alegere. Va trebui să efectuați câteva teste pentru a vedea dacă îmbunătățește sau nu performanța.

Comentarii

  • Am făcut asta acum, mulțumesc, dar nu a accelerat prea mult și încă folosește filesort. Există vreo modalitate de a utiliza indexul în locul unui filesort? –  > Por user1052096.
  • Pe baza rezultatului explicat de dvs., se utilizează indicele „price”. Cred că sortarea DESC cauzează o parte din problemă. –  > Por bobwienholt.
  • @user1052096: puteți să postați fișierul EXPLAIN rezultatul interogării după crearea indicelui din răspunsul meu? –  > Por Jocelyn.
Fabian Barney

Îndepărtați căpușele din jurul '0' – în prezent poate împiedica utilizarea indicelui, dar nu sunt sigur. totuși, este un stil mai bun, deoarece prețul este int tip și nu o coloană de caractere.

SELECT adverts .*    
FROM adverts
WHERE (
price >= 0
)
AND (
adverts.status = 1
)
AND (
adverts.approved = 1
)
ORDER BY date_updated DESC 
LIMIT 19990 , 10

hol

MySQL nu utilizează cheia date_updated pentru sortare, ci folosește doar indicele price așa cum este folosită în WHERE clauză. Ați putea încerca să utilizați indicii de indexare:

http://dev.mysql.com/doc/refman/5.1/en/index-hints.html

Adăugați ceva de genul

USE KEY FOR ORDER BY  (date_updated)

bobwienholt

Am două sugestii. În primul rând, eliminați ghilimelele din jurul lui zero din clauza where. Această linie ar trebui să fie:

price >= 0

În al doilea rând, creați acest index:

CREATE INDEX `helper` ON `adverts`(`status`,`approved`,`price`,`date_created`);

Acest lucru ar trebui să permită MySQL să găsească cele 10 rânduri specificate de clauza LIMIT folosind doar indexul. Filesort în sine nu este un lucru rău… numărul de rânduri care trebuie procesate este.

LSerni

WHERE condiție utilizează price, status, approved pentru a selecta, iar apoi date_updated este utilizat pentru a sorta.

Așadar, aveți nevoie de un un singur index cu aceste câmpuri; aș sugera indexarea pe approved, status, price și date_updated, în această ordine.

Regula generală constă în plasarea mai întâi a egalităților WHERE, apoi a intervalelor (mai mult decât, mai puțin sau egal, între etc.), iar câmpurile de sortare sunt ultimele. (Rețineți că dacă se omite un câmp s-ar putea ca indexul să fie mai puțin utilizabil sau chiar inutilizabil în acest scop).

CREATE INDEX advert_ndx ON adverts (approved, status, price, date_updated);

În acest fel, accesul la datele din tabel este necesar doar după ce LIMIT și-a făcut magia, iar dumneavoastră veți recupera lent doar un număr mic de înregistrări.

Aș elimina, de asemenea, orice indici nefolosiți, ceea ce ar accelera INSERTs și UPDATEs.