Interogarea specifică a interogărilor MySQL

Am probleme cu performanța interogării MySQL.

Tabel (InnoDB):

+--------------------+---------------------+------+-----+-------------------+-------+
| Field              | Type                | Null | Key | Default           | Extra |
+--------------------+---------------------+------+-----+-------------------+-------+
| st_resource_id     | varchar(32)         | NO   | MUL | NULL              |       |
| st_sub_resource_id | varchar(32)         | YES  |     | NULL              |       |
| st_title           | varchar(500)        | YES  |     | NULL              |       |
| st_resource_type   | varchar(100)        | NO   | MUL | NULL              |       |
| st_site_id         | tinyint(4)          | NO   | MUL | NULL              |       |
| st_time            | timestamp           | NO   | MUL | CURRENT_TIMESTAMP |       |
| st_user_id         | int(10) unsigned    | YES  |     | NULL              |       |
| st_full_access     | tinyint(1) unsigned | YES  |     | NULL              |       |
+--------------------+---------------------+------+-----+-------------------+-------+

Indici ai:

+---------------+------------+------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table         | Non_unique | Key_name         | Seq_in_index | Column_name        | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------+------------+------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+
| nr_statistics |          1 | resource_id      |            1 | st_resource_id     | A         |     1546165 |     NULL | NULL   |      | BTREE      |         |
| nr_statistics |          1 | resource_id      |            2 | st_sub_resource_id | A         |     1546165 |     NULL | NULL   | YES  | BTREE      |         |
| nr_statistics |          1 | st_time          |            1 | st_time            | A         |     1546165 |     NULL | NULL   |      | BTREE      |         |
| nr_statistics |          1 | st_site_id       |            1 | st_site_id         | A         |          16 |     NULL | NULL   |      | BTREE      |         |
| nr_statistics |          1 | st_resource_type |            1 | st_resource_type   | A         |          16 |       10 | NULL   |      | BTREE      |         |
+---------------+------------+------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+

Solicitare:

SELECT st_resource_id AS docId, count(*) AS cnt
FROM nr_statistics
WHERE
  st_resource_type = 'document'
  AND st_sub_resource_id = 'text'
  AND st_time > DATE_SUB(NOW(), INTERVAL 7 DAY)
  AND st_site_id = 1
GROUP BY st_resource_id
ORDER BY cnt DESC
LIMIT 0, 5;

Planul de interogare:

+----+-------------+---------------+-------+-------------------------------------+-------------+---------+------+---------+----------------------------------------------+
| id | select_type | table         | type  | possible_keys                       | key         | key_len | ref  | rows    | Extra                                        |
+----+-------------+---------------+-------+-------------------------------------+-------------+---------+------+---------+----------------------------------------------+
|  1 | SIMPLE      | nr_statistics | index | st_time,st_site_id,st_resource_type | resource_id | 197     | NULL | 1581044 | Using where; Using temporary; Using filesort |
+----+-------------+---------------+-------+-------------------------------------+-------------+---------+------+---------+----------------------------------------------+

Table has ~1,666,383 rows. The query runs extremely slow. In MySQL process list I see this query in "copy to tmp table phase" for a long time (> 1 minute). Query generates heavy I/O load. I can't understand what to do to fix the problem and speed-up query execution.

Dacă problema este rezultatul indiciilor greșite, ce indici va fi corect?

UPD. I created new composite index:

| nr_statistics |          1 | st_site_id_2     |            1 | st_site_id         | A         |          16 |     NULL | NULL   |      | BTREE      |         |
| nr_statistics |          1 | st_site_id_2     |            2 | st_resource_type   | A         |          16 |     NULL | NULL   |      | BTREE      |         |
| nr_statistics |          1 | st_site_id_2     |            3 | st_sub_resource_id | A         |      752018 |     NULL | NULL   | YES  | BTREE      |         |
| nr_statistics |          1 | st_site_id_2     |            4 | st_time            | A         |     1504037 |     NULL | NULL   |      | BTREE      |         |
| nr_statistics |          1 | st_site_id_2     |            5 | st_resource_id     | A         |     1504037 |     NULL | NULL   |      | BTREE      |         |

Planul de interogare este:

+----+-------------+---------------+-------+---------------+--------------+---------+------+-------+-----------------------------------------------------------+
| id | select_type | table         | type  | possible_keys | key          | key_len | ref  | rows  | Extra                                                     |
+----+-------------+---------------+-------+---------------+--------------+---------+------+-------+-----------------------------------------------------------+
|  1 | SIMPLE      | nr_statistics | range | st_site_id_2  | st_site_id_2 | 406     | NULL | 21168 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+---------------+-------+---------------+--------------+---------+------+-------+-----------------------------------------------------------+

Interogarea rulează acum foarte repede (ca 0.0x sec), dar trebuie să forțez folosind un index nou:

SELECT st_resource_id as docId, count( * ) AS Cnt
FROM nr_statistics
USE INDEX (st_site_id_2)
WHERE st_resource_type = 'document'
AND st_sub_resource_id = 'text'
AND st_time > DATE_SUB( NOW( ) , INTERVAL 7 DAY )
AND st_site_id = 1
GROUP BY st_resource_id
ORDER BY cnt DESC
LIMIT 0 , 5;

În timp ce problema este rezolvată (nu frumoasă, dar eficientă), am încă câteva întrebări deschise (a se vedea comentariile).

4

3 răspunsuri

Creați un index compozit pe (st_site_id, st_resource_type, st_sub_resourse_id, st_time, st_resource_id) .

Cu toate acestea, veți avea în plan planul temporal și filesort deoarece comandați COUNT (*) .

Dacă trebuie să executați rapid și deseori această interogare, va trebui să creați o tabelă agregată care să stocheze conturile pentru fiecare combinație de site/resursă/subre sare/săptămână și să o actualizeze într-un declanșator.

2
adăugat
@ValeraLeontyev: un indice compozit nu este la fel ca 4 cele independente. 4 indici nu formează o singură gamă în care să locuiască toate înregistrările. Acestea pot fi utilizate pentru a îmbunătăți viteza interogării (cu index_merge ), dar numai în condiții de egalitate și se pare că este predicatul st_time care este cel mai selectiv pentru interogarea dvs.
adăugat autor Quassnoi, sursa
Am creat un index compozit așa cum ați spus. Dar MySQL încă nu o folosește. Când am forțat MySQL să utilizeze noul plan de interogare a indexului, s-a schimbat (a se vedea actualizarea în întrebarea mea). Dar performanța a devenit minunată! Interogarea se execută în 0,0xxx secunde. Multumesc pentru acest sfat. De ce MySQL nu utilizează automat indexul automat? De ce 4 indexuri independente nu pot fi utilizate de MySQL? Este aproape la fel ca un compozit?
adăugat autor Valera Leontyev, sursa

Ați încercat să creați un index compozit pe st_resource_type, st_resource_id, st_time și st_site_id ? Mi se pare ca ai mai multi indici, dar majoritatea se afla intr-o singura coloana, sau poate 2 coloane. Dacă ai un index compozit cu mai multe coloane de care ai nevoie, poate îmbunătăți performanța.

1
adăugat

Când faci interogări cu mai multe clase în cazul în care ordinea în care le scrieți ar trebui să se potrivească cu ordinea în care ați scris interogarea.

În cazul dvs. particular, ar fi:

CREATE INDEX stats_index ON nr_statistics (st_resource_type, st_sub_resource_id, st_time, st_site_id);

Acest lucru ar trebui să vă dea un impuls destul de bun de viteză.

0
adăugat
MySQL - comunitatea Română
MySQL - comunitatea Română
19 participanți

Comunitatea română a programatorilor MySQL.