Jump to content
php.lv forumi
Sign in to follow this  
Val

pivot table ātrdarbība uz daudz ierakstiem

Recommended Posts

Labvakar,

izdomāju uzrakstīt, varbūt pametīsiet kādu ideju.

 

Doma tāda. Mysql tabula, kurā krīt iekšā statistikas dati. Value, filter kolonnas šajā gadījumā nav vajadzīgas, jo tiks izmantotas citur.

 

Tabulas struktūra un testa dati. Reālajā vidē tie būs daudz, daudz vairāk un sāksies problēmas ar summēšanu, tāpēc arī ir šis topiks.

post-1871-0-71123200-1395255204_thumb.png

post-1871-0-71123200-1395255204.png

 

SQL zemāk spoiler tagos:

 

 

CREATE TABLE IF NOT EXISTS `testa_tabula` (
`id` int(5) unsigned NOT NULL AUTO_INCREMENT,
`person_id` int(5) unsigned NOT NULL DEFAULT '0',
`stats_id` int(5) unsigned NOT NULL DEFAULT '0',
`value` int(5) unsigned NOT NULL DEFAULT '0',
`filter1` tinyint(1) unsigned NOT NULL DEFAULT '0',
`filter2` tinyint(1) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `person_id` (`person_id`,`stats_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;

INSERT INTO `testa_tabula` (`id`, `person_id`, `stats_id`, `value`, `filter1`, `filter2`) VALUES
(1, 1, 1, 10, 0, 0),
(2, 1, 1, 10, 0, 1),
(3, 1, 2, 100, 3, 0),
(4, 1, 2, 200, 0, 2),
(5, 2, 2, 500, 0, 0),
(6, 2, 1, 100, 0, 0),
(7, 3, 2, 10, 2, 3),
(8, 3, 3, 10, 0, 0),
(9, 4, 2, 5, 0, 0),
(10, 4, 3, 7, 0, 1),
(11, 5, 2, 1, 1, 0),
(12, 5, 3, 11, 0, 0),
(13, 5, 4, 6, 0, 1);

 

 

Selects no testa tabulas.

 

SELECT
    person_id,
    coalesce(sum(case when stats_id = 1 then skaits end), 0) as s1,
    coalesce(sum(case when stats_id = 2 then skaits end), 0) as s2,
    coalesce(sum(case when stats_id = 3 then skaits end), 0) as s3,
    coalesce(sum(case when stats_id = 4 then skaits end), 0) as s4
FROM
    (
        SELECT 
            person_id, 
            stats_id, 
            COUNT(*) as skaits
        FROM testa_tabula
        GROUP BY person_id, stats_id
        ORDER BY skaits DESC
    )
AS X
GROUP BY person_id
Rezultāts vizuāli:

post-1871-0-69947100-1395254868_thumb.png

post-1871-0-69947100-1395254868.png

 

Meklēts tiek stats_id skaits sagrupēts pēc personas id.

Ar iespēju pēc tam sortēt pēc katras s1, s2, s3, s4... kolonnas, vienlaicīgi redzot pārējos datus.

Stats_id ar laiku palielināsies, bet ne bezgalīgi.

 

Kverija explain:

post-1871-0-09602700-1395254862_thumb.png

post-1871-0-09602700-1395254862.png

 

Uz puslīdz reāliem datiem, 90% kverija aizņem copying to tmp table, jo jāsasummē ir diezgan daudz, salīdzinot ar iegūstamo rezultātu.

post-1871-0-12993300-1395256129_thumb.png

post-1871-0-12993300-1395256129.png

 

Ieteikumi? Šo te kveriju ir iespējams uzlabot? Vai taisīt kaut kādu starptabulu ar jau sasummētiem datiem?

Joks tāds, ka esošo rezultātu kādreiz būs nepieciešams vēl arī filtrēt pēc citām kolonnām - filter1, filter2. Tāpēc vienīgais variants ir sasummēt grupējot pēc person_id, stats_id, filter1, filter2, kā rezultātā ierakstu skaits nemaz tik ļoti nesamazināsies.

 

 

//update:

Ar starptabulu sanāk daudz maz normāli, ja ignorē filter1 un filter2.

explain rezultāts: type=index, rows=81, extra=using where

Edited by Val

Share this post


Link to post
Share on other sites
Ar iespēju pēc tam sortēt pēc katras s1, s2, s3, s4... kolonnas, vienlaicīgi redzot pārējos datus.

 

 

Ja datu ir daudz un kverijam jāizpildās kaut cik pārskatāmā laikā (pieņemsim standarta 1-2 sec max lapas ielādei), tad faktiski ir jāatmet doma par datu kārtošanu pēc neeindeksētiem un vēl jo vairāk temporāriem laukiem.

 

Starptabula ar sarēķinātiem varētu būt jēdzīgākais variants.

Share this post


Link to post
Share on other sites

Pirmais, kas duras acīs, priekš kam tev subquerie tiek orderēts?

ORDER BY skaits DESC

Izņem to un jau jāuzlabojas izplildes plānam.

Share this post


Link to post
Share on other sites

Izņem to un jau jāuzlabojas izplildes plānam.

Jap, mana kļūda.

Tiku vaļā no viena temporary un filesort iekš testa kverija.

 

 

post-1871-0-75053900-1395268396_thumb.png

post-1871-0-75053900-1395268396.png

Edited by Val

Share this post


Link to post
Share on other sites

Starptabula ar sarēķinātiem varētu būt jēdzīgākais variants.

Es vēl cerēju, ka mysql ir kas labs izdomāts šādiem gadījumiem. Jāpārstāj sapņot.

Daļēji jau patestēju vienkāršotā variantā.

Pa dienu jāuztaisa pilnais variants. Jautrība ar datu integritāti.

Share this post


Link to post
Share on other sites

Es lietotu kau ko šādu

SELECT
    person_id,
    concat( "{",group_concat( concat( stats_id, ":", skaits ) ),"}") as json_stats
FROM
    (
        SELECT 
            person_id, 
            stats_id, 
            COUNT(*) as skaits
        FROM testa_tabula
        GROUP BY person_id, stats_id
    )
AS X
GROUP BY person_id

Pirmo concat kas pievieno { un } var arī likt PHP pusē :)

 

Vienīgi, group_concat laikam bija limitēts uz maksimālo atmiņas daudzumu

Share this post


Link to post
Share on other sites

Es vēl cerēju, ka mysql ir kas labs izdomāts šādiem gadījumiem. Jāpārstāj sapņot.

Nu mysqls jau faktiski arī mēģina kaut ko darīt lietas labad (un ja ierakstu nav daudz, tad viss notiek (samērā) ātri) - proti veido to starptabulu, bet:

1. Tās starptabulas veidojas uz katru pieprasījumu

2. Atkarībā no datu apjomiem temporārās tabulas var sākt rakstīties no atmiņas uz disku ( http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_tmp_table_size ) - tad paliek pavisam slikti.

 

Pēc pieredzes tabulām, kur ierakstu skaits sākt pārsniegt pārsimt tūkstošus pilnībā mēģinu izvairīties no GROUP BY, COUNT(*), ORDER pēc lauka (ja pieprasījumu neapmierina kāds indeks pilnībā).

Share this post


Link to post
Share on other sites

Sākuma dati:

27 ms uz lokālās kastes, 5.3 ms uz servera ar SQL_NO_CACHE

post-1871-0-45101000-1395407004.png

 

Starpsummas, grupējot pēc filtriem, kverijā mazāk jāsummē:

17 ms un 3.5 ms

post-1871-0-24895900-1395407010.png

 

Starpsummas, ar izmestiem filtriem, kverijā viss jau sasummēts:

11 ms un 2.5 ms

post-1871-0-34340900-1395407016.png

 

 

Var vienīgi vēl iekešot apmeklētājam redzamos datus uz noteiktu minūšu skaitu.

Īstenībā pat varētu atstāt pirmo versiju un pagaidām aizmirst par starpsummām, lai dati nedublētos. Pie tām atgriezties, ja sāks nejēgā slogot kasti.

 

 

Kā arī pārtaisīt šo tabulu uz

id, personas_id, filter1, filter2, stats1, stats2, stats3, stats4, ..., stats22, ... galigi negribas.

post-1871-0-45101000-1395407004_thumb.png

post-1871-0-24895900-1395407010_thumb.png

post-1871-0-34340900-1395407016_thumb.png

Edited by Val

Share this post


Link to post
Share on other sites

Tu manu variantu pamēģināji? uz taviem testa datiem mans sql izgāja bez "Using temporary"

Share this post


Link to post
Share on other sites

Jā, pamēģināju, Mysql 5.0.92

post-1871-0-58610300-1395434491_thumb.png

 

Uz localhosta ar 5.5.36, ir lasāmāk.

post-1871-0-70880000-1395434701.png

 

Kopējais rezultāts diezgan līdzīgs. Kā arī šajā kverijā pat nav ietverta galarezultāta sortēšana pēc json datiem.

post-1871-0-05413400-1395435503.png

post-1871-0-70880000-1395434701_thumb.png

post-1871-0-05413400-1395435503_thumb.png

Edited by Val

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...