In acest post, vom discuta despre cel mai bun mod de a actualiza manual un tabel InnoDB.
Ca inginer de asistenta, vad adesea situatii in care cardinalitatea unui tabel nu este corecta. Cand InnoDB calculeaza cardinalitatea unui indice, acesta nu scaneaza in mod implicit tabelul complet. In schimb, acesta priveste paginile aleatorii, asa cum sunt determinate de optiunile innodb_stats_sample_pages, innodb_stats_transient_sample_pages si innodb_stats_persistent_sample_pages sau de optiunea CREATE TABLE STATS_SAMPLE_PAGES. Valoarea implicita a statisticilor persistente este 20. Aceasta abordare functioneaza bine atunci cand numarul de valori unice din cheia dvs. secundara creste pas cu dimensiunea tabelului. Dar daca aveti o coloana care are un numar relativ mic de valori unice? Acesta ar putea fi un serviciu comun, un tabel de relatii mult-la-multi, de exemplu, sau doar un tabel care contine o lista de comenzi de vanzare care apartin unuia dintre duzini de magazine detinute de companie.
La un moment dat, InnoDB va raporta valorile gresite pentru astfel de indici. Intr-adevar! Daca 20 de pagini ar avea 100 de coduri unice pentru un magazin, cate ID-uri de magazin unice ar avea 20000 de pagini? De 100 de ori 1000? Acest lucru pare logic si, dupa un anumit numar de randuri, astfel de indici vor avea valori de cardinalitate extraordinar de mari.
TABELUL DE ANALIZA nu va ajuta, deoarece foloseste acelasi algoritm. Cresterea numarului de exemple de „statistici” ar ajuta, dar are un dezavantaj propriu: cu cat trebuie sa analizezi mai multe pagini, cu atat mai lent va rula TABELUL ANALIZEI. Desi aceasta comanda nu se blocheaza, totusi creeaza efecte secundare asa cum este descris in aceasta postare pe blog. Si cu cat ruleaza mai mult, cu atat ai mai putin control.
O alta problema a statisticilor InnoDB: chiar daca este persistenta si STATS_AUTO_RECALC este setata la 0, acesta adauga in continuare valori pentru indici secundari, asa cum se arata in lp: 1538765. In cele din urma, dupa ce inserati milioane de randuri, statisticile dvs. sunt corupte. TABELUL DE ANALIZARE il poate repara doar daca specificati un numar foarte mare de pagini de „statistici”.
Actualizati manual InnoDB Table manual
InnoDB stocheaza statistici in baza de date „mysql”, in tabelele innodb_table_stats si innodb_index_stats. Deoarece sunt tabele MySQL obisnuite, utilizatorii privilegiati le pot accesa. Le putem actualiza si modifica statistici dupa cum ne place. Si aceste statistici sunt utilizate de Optimizer!
Am creat un mic exemplu care arata cum se face acest truc. Am folosit Percona Server pentru MySQL versiunea 5.7.19, dar trucul va functiona pe orice suport acceptat pentru MySQL si Percona Server pentru versiunea MySQL.
Mai intai, sa cream tabele de testare. Primul tabel are magazine, cu cateva profiluri de magazin cu ID-ul si numele magazinului:
creati magazine de masa (
shop_id nu este nul cheia principala autoincrementa,
nume varchar (32)
) motor = innodb;
Al doilea tabel se refera la tabelul „magazine”:
creati produse de masa (
ID int not null cheie primara autoincrementa,
shop_id nu este nul,
nume varchar (32),
create_date datetime DEFAULT NULL,
cheie (shop_id, create_date)
) motor = innodb;
Sa verificam cate magazine unice avem:
mysql> selectati numararea (shop_id distinct) din magazine;
+ ————————- +
| numar (magazin_id distinct) |
+ ————————- +
| 100 |
+ ————————- +
1 rand in set (0,02 sec)
Cu 100 de magazine distincte si o cheie activata (shop_id, create_date), ne asteptam ca cardinalitatea bunurilor de masa sa nu fie cu mult diferita de rezultatul acestei interogari:
mysql> selectati numarul (id distinct) ca `Cardinalitate pentru PRIMARA ‘,
-> numara (shop_id distinct) ca `Cardinalitate pentru coloana shop_id din index shop_id`,
-> numara (shop_id distinct, create_date) ca `Cardinalitate pentru coloana create_date din index shop_id`
-> din bunuri
*************************** 1. rand ******************** *******
Cardinalitate pentru PRIMAR: 8000000
Cardinalitate pentru coloana shop_id din index shop_id: 100
Cardinalitatea pentru coloana create_date din index shop_id: 169861
1 rand in set (2 min 8,74 sec)
Cu toate acestea, SHOW INDEX returneaza valori drastic diferite pentru coloana shop_id:
mysql> arata indicele de la bunuri;
+ ——- + ———— + ———- + ————– + – ———– + ———– + ————- + ———- + – ——- + —— + ———— + ——— + ———— — +
| Tabel | Non_unique | Nume_ cheie | Seq_in_index | Numele coloanei | Colatie | Cardinalitate | Sub_part | | Ambalat | Nul | Index_tip | Comentariu | Index_comment |
+ ——- + ———— + ———- + ————– + – ———– + ———– + ————- + ———- + – ——- + —— + ———— + ——— + ———— — +
| bunuri | 0 | PRIMAR | 1 | id | A | 7289724 | NULL | NULL | | BTREE | | |
| bunuri | 1 | magazin_id | 1 | magazin_id | A | 13587 | NULL | NULL | | BTREE | | |
| bunuri | 1 | magazin_id | 2 | create_date | A | 178787 | NULL | NULL | DA | BTREE | | |
+ ——- + ———— + ———- + ————– + – ———– + ———– + ————- + ———- + – ——- + —— + ———— + ——— + ———— — +
3 randuri in set (0,09 sec)
TABELUL DE ANALIZA nu ajuta:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> analiza bunurile de masa;
+ ———— + ——— + ———- + ———- +
| Tabel | Op | Msg_type | Msg_text |
+ ———— + ——— + ———- + ———- +
| test.goods | analiza | stare | OK |
+ ———— + ——— + ———- + ———- +
1 rand in set (0,88 sec)
mysql> arata indicele de la bunuri;
+ ——- + ———— + ———- + ————– + – ———– + ———– + ————- + ———- + – ——- + —— + ———— + ——— + ———— — +
| Tabel | Non_unique | Nume_ cheie | Seq_in_index | Numele coloanei | Colatie | Cardinalitate | Sub_part | | Ambalat | Nul | Index_tip | Comentariu | Index_comment |
+ ——- + ———— + ———- + ————– + – ———– + ———– + ————- + ———- + – ——- + —— + ———— + ——— + ———— — +
| bunuri | 0 | PRIMAR | 1 | id | A | 7765796 | NULL | NULL | | BTREE | | |
| bunuri | 1 | magazin_id | 1 | magazin_id | A | 14523 | NULL | NULL | | BTREE | | |
| bunuri | 1 | magazin_id | 2 | create_date | A | 168168 | NULL | NULL | DA | BTREE | | |
+ ——- + ———— + ———- + ————– + – ———– + ———– + ————- + ———- + – ——- + —— + ———— + ——— + ———— — +
3 randuri in set (0,00 sec)
Drept urmare, daca ne alaturam celor doua tabele, Optimizer alege planul gresit de ordine si executie de interogare JOIN:
mysql> explica bunuri selectate. * din marfuri se alatura magazinelor folosind (shop_id) unde create_date ENTRE CONVERT_TZ (‘2015-11-01 00:00:00’, ‘MET’, ‘GMT’) SI CONVERT_TZ (‘2015-11-07 23:59:59 ‘,’ MET ‘,’ GMT ‘) si merchand.shop_id in (4,5,6,7,8,9,10,11,12,13,14,15,16,17,18 , 19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43 , 44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68 , 69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93 , 94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486);
+ —- + ————- + ——- + ———— + ——- + – ————– + ——— + ——— + ————— —– + —— + ———- + ————————– +
| id | select_type | masa | partitii | tip | posibile_keys | cheie | cheie_len | ref | randuri | filtrata | Extra |
+ —- + ————- + ——- + ———— + ——- + – ————– + ——— + ——— + ————— —– + —— + ———- + ————————– +
| 1 | SIMPL | | magazine | NULL | index | PRIMAR | PRIMAR | 4 | NULL | 100 | 100,00 | Folosind unde; Folosind index |
| 1 | SIMPL | | bunuri | NULL | ref | magazin_id | magazin_id | 4 | test.shops.shop_id | 534 | 11.11 | Utilizarea conditiei indexului |
+ —- + ————- + ——- + ———— + ——- + – ————– + ——— + ——— + ————— —– + —— + ———- + ————————– +
2 randuri in set, 1 avertizare (0,13 sec)
mysql> P md5sum
PAGER setat pe „md5sum”
mysql> selecteaza marfuri. * din marfuri se alatura magazinelor folosind (shop_id) unde create_date INTRE CONVERT_TZ (‘2015-11-01 00:00:00’, ‘MET’, ‘GMT’) SI CONVERT_TZ (‘2015-11-07 23 : 59: 59 ‘,’ MET ‘,’ GMT ‘) si bunuri.shop_id in (4,5,6,7,8,9,10,11,12,13,14,15,16,17,18, 19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43, 44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68, 69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93, 94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486);
4a94dabc4bfbfb7dd225bcb50278055b –
31896 randuri in set (43,32 sec)
In comparatie cu comanda STRAIGHT_JOIN:
mysql> explica bunuri selectate. * din bunurile straight_join magazinele de pe (merchand.shop_id = stores.shop_id) unde create_date INTRE CONVERT_TZ (‘2015-11-01 00:00:00’, ‘MET’, ‘GMT’) SI CONVERT_TZ ( ‘2015-11-07 23:59:59’, ‘MET’, ‘GMT’) si merchand.shop_id in (4,5,6,7,8,9,10,11,12,13,14,15 , 16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40 , 41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65 , 66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90 , 91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486);
+ —- + ————- + ——- + ———— + ——– + ————— + ——— + ——— + ————– —— + ——- + ———- + ———————– +
| id | select_type | masa | partitii | tip | posibile_keys | cheie | cheie_len | ref | randuri | filtrata | Extra |
+ —- + ————- + ——- + ———— + ——– + ————— + ——— + ——— + ————– —— + ——- + ———- + ———————– +
| 1 | SIMPL | | bunuri | NULL | gama | magazin_id | magazin_id | 10 | NULL | 31997 | 100,00 | Utilizarea conditiei indexului |
| 1 | SIMPL | | magazine | NULL | eq_ref | PRIMAR | PRIMAR | 4 | test.goods.shop_id | 1 | 100,00 | Folosind index |
+ —- + ————- + ——- + ———— + ——– + ————— + ——— + ——— + ————– —— + ——- + ———- + ———————– +
2 randuri in set, 1 avertizare (0,14 sec)
mysql> P md5sum
PAGER setat pe „md5sum”
mysql> selecteaza marfurile. * din marfurile straight_join magazinele de pe (merchand.shop_id = stores.shop_id) unde create_date INTRE CONVERT_TZ (‘2015-11-01 00:00:00’, ‘MET’, ‘GMT’) SI CONVERT_TZ (‘ 2015-11-07 23:59:59 ‘,’ MET ‘,’ GMT ‘) si merchand.shop_id in (4,5,6,7,8,9,10,11,12,13,14,15, 16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40, 41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65, 66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90, 91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486);
4a94dabc4bfbfb7dd225bcb50278055b –
31896 randuri in set (7,94 sec)
Diferenta de timp pentru o mica masa cu 8 randuri este de aproximativ sase ori! Pentru o masa mare cu multe coloane, ar fi si mai mare.
STRAIGHT_JOIN este singura solutie pentru acest caz?
Nu! De asemenea, nu este o solutie excelenta, deoarece daca interogarea este complicata si implica mai mult de doua tabele, poate fi afectata de remedieri de erori si imbunatatiri ale codului Optimizer. Atunci comanda de interogare nu poate fi optima pentru versiunile noi si actualizarile. Prin urmare, va trebui sa testati astfel de intrebari la fiecare upgrade, inclusiv la cele minore.
Deci, de ce ANALIZA TABLE nu functioneaza? Deoarece numarul implicit de pagini pe care il foloseste pentru calcularea statisticilor este prea mic pentru diferenta. Puteti mari optiunea tabelului STATS_SAMPLE_PAGES pana cand veti gasi una corespunzatoare. Dezavantajul este ca, cu cat stabiliti STATS_SAMPLE_PAGES, cu atat va dura mai mult pentru a termina ANALYZE TABLE. De asemenea, daca actualizati o portiune mare din tabel, de multe ori sunteti afectat de lp: 1538765. La un moment dat, statisticile vor fi din nou inexacte.
Acum sa incercam manualul nostru de actualizare a statisticilor
InnoDB stocheaza statisticile sale persistente in tabelele mysql.innodb_table_stats si mysql.innodb_index_stats:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
mysql> alter table merchandise stats_persistent = 1, stats_auto_recalc = 0;
Interogare OK, 0 randuri afectate (0,11 sec)
Inregistrari: 0 Duplicate: 0 Avertismente: 0
+ ————— + ———— + ——————– – + ——— + ———————- + ————— ———– +
| baza de date nume | nume_tabel | ultima data de actualizare | n_rows | clustered_index_size | sum_of_other_index_sizes |
+ ————— + ———— + ——————– – + ——— + ———————- + ————— ———– +
| test | bunuri | 2017-09-05 00:21:12 | 7765796 | 34624 | 17600 |
+ ————— + ———— + ——————– – + ——— + ———————- + ————— ———– +
1 rand in set (0.00 sec)
mysql> selectati * din mysql.innodb_index_stats unde table_name = ‘bunuri’;
+ ————— + ———— + ———— + ——- ————– + ————– + ———— + ——- —— + ———————————– +
| baza de date nume | nume_tabel | index_name | ultima data de actualizare | nume_stat | stat_value | sample_size | stat_descriere |
+ ————— + ———— + ———— + ——- ————– + ————– + ———— + ——- —— + ———————————– +
| test | bunuri | PRIMAR | 2017-09-05 00:21:12 | n_diff_pfx01 | 7765796 | 20 | id |
| test | bunuri | PRIMAR | 2017-09-05 00:21:12 | n_leaf_pages | 34484 | NULL | Numar de pagini din index |
| test | bunuri | PRIMAR | 2017-09-05 00:21:12 | dimensiune | 34624 | NULL | Numar de pagini din index |
| test | bunuri | magazin_id | 2017-09-05 00:21:12 | n_diff_pfx01 | 14523 | 20 | magazin_id |
| test | bunuri | magazin_id | 2017-09-05 00:21:12 | n_diff_pfx02 | 168168 | 20 | shop_id, create_date |
| test | bunuri | magazin_id | 2017-09-05 00:21:12 | n_diff_pfx03 | 8045310 | 20 | shop_id, create_date, id |
| test | bunuri | magazin_id | 2017-09-05 00:21:12 | n_leaf_pages | 15288 | NULL | Numar de pagini din index |
| test | bunuri | magazin_id | 2017-09-05 00:21:12 | dimensiune | 17600 | NULL | Numar de pagini din index |
+ ————— + ———— + ———— + ——- ————– + ————– + ———— + ——- —— + ———————————– +
8 randuri in set (0,00 sec)
Si putem actualiza aceste tabele direct:
mysql> actualizare mysql.innodb_table_stats set n_rows = 8000000 unde table_name = ‘bunuri’;
Interogare OK, 1 rand afectat (0,18 sec)
Randurile potrivite: 1 Schimbate: 1 Avertismente: 0
mysql> actualizare mysql.innodb_index_stats set stat_value = 8000000 unde stat_description in (‘id’, ‘shop_id, create_date, id’) si table_name = ‘merchandising’;
Interogare OK, 2 randuri afectate (0,08 sec)
Randurile potrivite: 2 Schimbate: 2 Avertismente: 0
mysql> actualizare mysql.innodb_index_stats set stat_value = 100 unde stat_description in (‘shop_id’) si nume_tabela = ‘bunuri’;
Interogare OK, 1 rand afectat (0,09 sec)
Randurile potrivite: 1 Schimbate: 1 Avertismente: 0
mysql> actualizare mysql.innodb_index_stats set stat_value = 169861 unde stat_description in (‘shop_id, create_date’) si table_name = ‘bunuri’;
Interogare OK, 1 rand afectat (0,08 sec)
Randurile potrivite: 1 Schimbate: 1 Avertismente: 0
Am preluat valorile indexului de mai devreme, asa cum a fost calculata de aceasta interogare:
selectati numararea (ID distinct) ca `Cardinalitate pentru PRIMARA`, numarati (distinct shop_id) ca` Cardinalitate pentru coloana shop_id in index shop_id`, count (distinct shop_id, create_date) ca `Cardinalitate pentru coloana create_date in index shop_id` din bunuri;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> select * din mysql.innodb_table_stats unde table_name = ‘bunuri’;
+ ————— + ———— + ——————– – + ——— + ———————- + ————— ———– +
| baza de date nume | nume_tabel | ultima data de actualizare | n_rows | clustered_index_size | sum_of_other_index_sizes |
+ ————— + ———— + ——————– – + ——— + ———————- + ————— ———– +
| test | bunuri | 2017-09-05 00:47:45 | 8000000 | 34624 | 17600 |
+ ————— + ———— + ——————– – + ——— + ———————- + ————— ———– +
1 rand in set (0.00 sec)
mysql> selectati * din mysql.innodb_index_stats unde table_name = ‘bunuri’;
+ ————— + ———— + ———— + ——- ————– + ————– + ———— + ——- —— + ———————————– +
| baza de date nume | nume_tabel | index_name | ultima data de actualizare | nume_stat | stat_value | sample_size | stat_descriere |
+ ————— + ———— + ———— + ——- ————– + ————– + ———— + ——- —— + ———————————– +
| test | bunuri | PRIMAR | 2017-09-05 00:48:32 | n_diff_pfx01 | 8000000 | 20 | id |
| test | bunuri | PRIMAR | 2017-09-05 00:21:12 | n_leaf_pages | 34484 | NULL | Numar de pagini din index |
| test | bunuri | PRIMAR | 2017-09-05 00:21:12 | dimensiune | 34624 | NULL | Numar de pagini din index |
| test | bunuri | magazin_id | 2017-09-05 00:49:13 | n_diff_pfx01 | 100 | 20 | magazin_id |
| test | bunuri | magazin_id | 2017-09-05 00:49:26 | n_diff_pfx02 | 169861 | 20 | shop_id, create_date |
| test | bunuri | magazin_id | 2017-09-05 00:48:32 | n_diff_pfx03 | 8000000 | 20 | shop_id, create_date, id |
| test | bunuri | magazin_id | 2017-09-05 00:21:12 | n_leaf_pages | 15288 | NULL | Numar de pagini din index |
| test | bunuri | magazin_id | 2017-09-05 00:21:12 | dimensiune | 17600 | NULL | Numar de pagini din index |
+ ————— + ———— + ———— + ——- ————– + ————– + ———— + ——- —— + ———————————– +
8 randuri in set (0,00 sec)
Acum statisticile sunt actualizate, dar nu sunt utilizate:
mysql> explica bunuri selectate. * din marfuri se alatura magazinelor folosind (shop_id) unde create_date ENTRE CONVERT_TZ (‘2015-11-01 00:00:00’, ‘MET’, ‘GMT’) SI CONVERT_TZ (‘2015-11-07 23:59:59 ‘,’ MET ‘,’ GMT ‘) si merchand.shop_id in (4,5,6,7,8,9,10,11,12,13,14,15,16,17,18 , 19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43 , 44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68 , 69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93 , 94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486);
+ —- + ————- + ——- + ———— + ——- + – ————– + ——— + ——— + ————— —– + —— + ———- + ————————– +
| id | select_type | masa | partitii | tip | posibile_keys | cheie | cheie_len | ref | randuri | filtrata | Extra |
+ —- + ————- + ——- + ———— + ——- + – ————– + ——— + ——— + ————— —– + —— + ———- + ————————– +
| 1 | SIMPL | | magazine | NULL | index | PRIMAR | PRIMAR | 4 | NULL | 100 | 100,00 | Folosind unde; Folosind index |
| 1 | SIMPL | | bunuri | NULL | ref | magazin_id | magazin_id | 4 | test.shops.shop_id | 534 | 11.11 | Utilizarea conditiei indexului |
+ —- + ————- + ——- + ———— + ——- + – ————– + ——— + ——— + ————— —– + —— + ———- + ————————– +
2 randuri in set, 1 avertizare (0,04 sec)
Pentru a finaliza modificarile, trebuie sa rulam marfuri FLUSH TABLE:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> MARFURI CU FLUSH TABLE;
Interogare OK, 0 randuri afectate (0,00 sec)
mysql> explica bunuri selectate. * din marfuri se alatura magazinelor folosind (shop_id) unde create_date ENTRE CONVERT_TZ (‘2015-11-01 00:00:00’, ‘MET’, ‘GMT’) SI CONVERT_TZ (‘2015-11-07 23:59:59 ‘,’ MET ‘,’ GMT ‘) si merchand.shop_id in (4,5,6,7,8,9,10,11,12,13,14,15,16,17,18 , 19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43 , 44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68 , 69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93 , 94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486);
+ —- + ————- + ——- + ———— + ——– + ————— + ——— + ——— + ————– —— + ——- + ———- + ———————– +
| id | select_type | masa | partitii | tip | posibile_keys | cheie | cheie_len | ref | randuri | filtrata | Extra |
+ —- + ————- + ——- + ———— + ——– + ————— + ——— + ——— + ————– —— + ——- + ———- + ———————– +
| 1 | SIMPL | | bunuri | NULL | gama | magazin_id | magazin_id | 10 | NULL | 31997 | 100,00 | Utilizarea conditiei indexului |
| 1 | SIMPL | | magazine | NULL | eq_ref | PRIMAR | PRIMAR | 4 | test.goods.shop_id | 1 | 100,00 | Folosind index |
+ —- + ————- + ——- + ———— + ——– + ————— + ——— + ——— + ————– —— + ——- + ———- + ———————– +
2 randuri in set, 1 avertizare (0,28 sec)
mysql> P md5sum
PAGER setat pe „md5sum”
mysql> selecteaza marfuri.
porno old http://networksolutions-sucks.us/__media__/js/netsoltrademark.php?d=adult66.net/
filme porno cu pitice http://robertoclemente21.com/__media__/js/netsoltrademark.php?d=adult66.net/
filme xxx porno gratis http://tippi.com/__media__/js/netsoltrademark.php?d=adult66.net/
filmulete porno cu virgine http://dokumentierensieschneider.com/__media__/js/netsoltrademark.php?d=adult66.net/filme-porno/amatori
porno polonia http://lamedelegation.info/__media__/js/netsoltrademark.php?d=adult66.net/filme-porno/anal
filme porno full hd http://4lslearning.com/__media__/js/netsoltrademark.php?d=adult66.net/filme-porno/asiatice
porno chinezoaice http://wenrich.eu/cgi-bin/go?url=https://adult66.net/filme-porno/beeg
filme porno cu bunicute http://m.shopinportland.com/redirect.aspx?url=https://adult66.net/filme-porno/blonde
filme porno cu transexuale http://aeonvision.net/__media__/js/netsoltrademark.php?d=adult66.net/filme-porno/brazzers
filme porno pt telefon http://galtai.allpn.ru/redirect/?url=https://adult66.net/filme-porno/brunete
filmari porno http://liquidcomic.com/__media__/js/netsoltrademark.php?d=adult66.net/filme-porno/chaturbate
filme porno franta http://metalstrade.ottertail-power.info/__media__/js/netsoltrademark.php?d=adult66.net/doi-studenti-amatori-fac-sex-nebun-in-pozitii-demne-de-filmele-porno
porno la greu http://sunnybankhifi.com.au/catalog/redirect.php?action=url&goto=adult66.net/mama-sexy-isi-seduce-fiul-vitreg-apoi-il-fute-in-patul-conjugal
porno video gratis http://rehabquest.com/__media__/js/netsoltrademark.php?d=adult66.net/tanar-excitat-isi-fute-prietena-tare-de-tot-pe-podea
filme porno mia califa http://4ux.networksolutionssux.com/__media__/js/netsoltrademark.php?d=adult66.net/tanara-cu-un-fund-senzual-e-fututa-hard-pe-la-spate
porno petrecere http://myadking.com/Redirect.asp?UID=1614168&SubSectionID=-1&AdArrayID=9&AdPosition=-1&LinkURL=https://adult66.net/papusica-blonda-filmata-de-prieten-in-timp-ce-ii-face-sex-oral
filme sex porno free new http://nickeldime.org/__media__/js/netsoltrademark.php?d=adult66.net/blonda-suge-pula-adanc-pana-cand-ejaculeaza-in-gura-ei
durva porno http://ranchobelagotravel.com/__media__/js/netsoltrademark.php?d=adult66.net/doua-negrese-incinse-se-freaca-rau-una-de-alta-pana-au-orgasm
xxx porno gonzo http://worldofguilds.floridahorsepark.com/__media__/js/netsoltrademark.php?d=adult66.net/tanara-timida-face-pentru-prima-oara-sex-oral
ariana grande porno http://townsendvision.com/__media__/js/netsoltrademark.php?d=adult66.net/bunaciune-se-joaca-cu-bilele-de-cur-apoi-se-fute-in-draci
* din marfuri se alatura magazinelor folosind (shop_id) unde create_date INTRE CONVERT_TZ (‘2015-11-01 00:00:00’, ‘MET’, ‘GMT’) SI CONVERT_TZ (‘2015-11-07 23 : 59: 59 ‘,’ MET ‘,’ GMT ‘) si bunuri.shop_id in (4,5,6,7,8,9,10,11,12,13,14,15,16,17,18, 19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43, 44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68, 69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93, 94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486);
4a94dabc4bfbfb7dd225bcb50278055b –
31896 randuri in set (7,79 sec)
Acum totul este bine.
Dar FLUSH TABLE este o operatie de blocare, nu? Nu va bloca interogarile si nu va crea un scenariu mai rau decat cel descris in TABELUL DE ANALIZARE din aceasta postare?
La prima vedere acest lucru este adevarat. Dar putem folosi acelasi truc pe care il foloseste Percona Toolkit: setati lock_wait_timeout la 1 si apelati FLUSH intr-o bucla. Pentru a demonstra cum functioneaza, folosesc un scenariu similar descris in postarea de pe blogul ANALIZEI TABELULUI.
In primul rand, sa resetam statisticile pentru a ne asigura ca FLUSH-ul nostru functioneaza asa cum era de asteptat:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> analiza bunurile de masa;
+ ———— + ——— + ———- + ———- +
| Tabel | Op | Msg_type | Msg_text |
+ ———— + ——— + ———- + ———- +
| test.goods | analiza | stare | OK |
+ ———— + ——— + ———- + ———- +
1 rand in set (0,38 sec)
mysql> arata indici din bunuri;
+ ——- + ———— + ———- + ————– + – ———– + ———– + ————- + ———- + – ——- + —— + ———— + ——— + ———— — +
| Tabel | Non_unique | Nume_ cheie | Seq_in_index | Numele coloanei | Colatie | Cardinalitate | Sub_part | | Ambalat | Nul | Index_tip | Comentariu | Index_comment |
+ ——- + ———— + ———- + ————– + – ———– + ———– + ————- + ———- + – ——- + —— + ———— + ——— + ———— — +
| bunuri | 0 | PRIMAR | 1 | id | A | 7765796 | NULL | NULL | | BTREE | | |
| bunuri | 1 | magazin_id | 1 | magazin_id | A | 14523 | NULL | NULL | | BTREE | | |
| bunuri | 1 | magazin_id | 2 | create_date | A | 168168 | NULL | NULL | DA | BTREE | | |
+ ——- + ———— + ———- + ————– + – ———– + ———– + ————- + ———- + – ——- + —— + ———— + ——— + ———— — +
3 randuri in set (0,00 sec)
Si apoi actualizati mysql.innodb _ * _ statistica tabelele manual. Apoi verificati daca Optimizatorul vede in continuare statistici invechite:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
mysql> actualizare mysql.innodb_table_stats set n_rows = 8000000 unde table_name = ‘bunuri’;
Interogare OK, 1 rand afectat (0,09 sec)
Randurile potrivite: 1 Schimbate: 1 Avertismente: 0
mysql> actualizare mysql.innodb_index_stats set stat_value = 8000000 unde stat_description in (‘id’, ‘shop_id, create_date, id’) si table_name = ‘merchandising’;
Interogare OK, 2 randuri afectate (0,09 sec)
Randurile potrivite: 2 Schimbate: 2 Avertismente: 0
mysql> actualizare mysql.innodb_index_stats set stat_value = 100 unde stat_description in (‘shop_id’) si nume_tabela = ‘bunuri’;
Interogare OK, 1 rand afectat (0,11 sec)
Randurile potrivite: 1 Schimbate: 1 Avertismente: 0
mysql> actualizare mysql.innodb_index_stats set stat_value = 169861 unde stat_description in (‘shop_id, create_date’) si table_name = ‘bunuri’;
Interogare OK, 1 rand afectat (0,10 sec)
Randurile potrivite: 1 Schimbate: 1 Avertismente: 0
mysql> arata indici din bunuri;
+ ——- + ———— + ———- + ————– + – ———– + ———– + ————- + ———- + – ——- + —— + ———— + ——— + ———— — +
| Tabel | Non_unique | Nume_ cheie | Seq_in_index | Numele coloanei | Colatie | Cardinalitate | Sub_part | | Ambalat | Nul | Index_tip | Comentariu | Index_comment |
+ ——- + ———— + ———- + ————– + – ———– + ———– + ————- + ———- + – ——- + —— + ———— + ——— + ———— — +
| bunuri | 0 | PRIMAR | 1 | id | A | 7765796 | NULL | NULL | | BTREE | | |
| bunuri | 1 | magazin_id | 1 | magazin_id | A | 14523 | NULL | NULL | | BTREE | | |
| bunuri | 1 | magazin_id | 2 | create_date | A | 168168 | NULL | NULL | DA | BTREE | | |
+ ——- + ———— + ———- + ————– + – ———– + ———– + ————- + ———- + – ——- + —— + ———— + ——— + ———— — +
3 randuri in set (0,00 sec)
Acum, sa incepem o interogare lunga de rulare intr-o singura sesiune care blocheaza comanda FLUSH TABLE:
mysql> selectati somn (1) din limita marfurilor 1000, 300;
Si haideti sa rulam FLUSH TABLE intr-o bucla:
sveta @ Thinkie: ~ / build / ps-5.7 / mysql-test $ pana la (`mysqlmtr -P13001 -e” set lock_wait_timeout = 1; bunuri de masa; “test”); dormi 1; Terminat
EROAREA 1205 (HY000) la linia 1: timpul de asteptare de blocare a fost depasit; incercati sa reporniti tranzactia
EROAREA 1205 (HY000) la linia 1: timpul de asteptare de blocare a fost depasit; incercati sa reporniti tranzactia
EROAREA 1205 (HY000) la linia 1: timpul de asteptare de blocare a fost depasit; incercati sa reporniti tranzactia
…
Acum sa ne asiguram ca putem accesa tabelul:
mysql> selectati * din comanda marfurilor pana la limita de identificare 10;
^ C
Nu putem! Nici macar nu ne putem conecta la baza de date in care este stocata tabela:
sveta @ Thinkie: ~ / build / ps-5.7 / mysql-test $ mysqlmtr -P13001 test
Citirea informatiilor din tabel pentru completarea numelor tabelelor si coloanelor
Puteti dezactiva aceasta caracteristica pentru a incepe mai rapid cu -A
^ C
Motivul pentru asta este ca, in timp ce comanda FLUSH TABLE a fost ucisa din cauza expirarii timpului de asteptare a blocarii metadatelor, a solicitat, de asemenea, blocarea tabelei pentru inrosire si blocarea altor interogari de intrare.
Dar putem adauga TABLA DE FLUSA in LOCK TABLE … SCRIE; … TABELURI DE DEZLOCARE; operatiuni. In acest caz, comanda LOCK TABLE este blocata pana cand toate interogarile elibereaza blocarea metadatelor pe masa. Apoi blocheaza exclusiv tabelul, ruleaza FLUSH TABLE si apoi scriptul deblocheaza imediat tabelul. Intrucat inchiderea sesiunii determina o deblocare implicita, am folosit un PHP one-liner pentru a avea totul intr-o singura sesiune:
$ php -r ‘
> $ link = new mysqli (“127.0.0.1”, “root”, “”, “test”, 13001);
> $ link-> interogare (“set lock_wait_timeout = 1”);
> while (! $ link-> interogare (“blocheaza marfa scrie”)) {sleep (1);}
> $ link-> interogare (“marfuri de masa flush”);
> $ link-> interogare (“deblocare tabele”); ‘
Putem confirma daca o sesiune paralela poate accesa tabelul:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> selectati * din comanda marfurilor pana la limita de identificare 10;
+ —- + ——— + ———————————- + ——————— +
| id | magazin_id | nume | create_date |
+ —- + ——— + ———————————- + ——————— +
| 1 | 58 | 5K0z2sHTgjWKKdryTaniQdZmjGjA9wls | 2015-09-19 00:00:00 |
| 2 | 17 | xNll02kgUTWAFURj6j5lL1zXAubG0THG | 2013-10-19 00:00:00 |
| 3 | 30 | clHX7uQopKmoTtEFH5LYBgQncsxRtTIB | 2017-08-01 00:00:00 |
| 4 | 93 | bAzoQTN98AmFjPOZs7PGfbiGfaf9Ye4b | 2013-02-24 00:00:00 |
| 5 | 20 | rQuTO5GHjP60kDbN6WoPpE2S8TtMbrVL | 2017-08-05 00:00:00 |
| 6 | 37 | WxqxA5tBHxikaKbuvbIF84H9QuaCnqQ3 | 2013-10-18 00:00:00 |
| 7 | 13 | DoYnFpQZSVV8UswBsWklgGBUc8zW9mVW | 2017-02-06 00:00:00 |
| 8 | 81 | dkNxMQyZNZuTrONEX4gxRLa0DOedatIs | 2015-07-05 00:00:00 |
| 9 | 12 | Z0t2uQ9itexpPf01KUpa7qBWlT5fBmXR | 2014-06-25 00:00:00 |
| 10 | 90 | 6urABBQyaUVVyxljvd11D3kUxbdDRPRV | 2013-10-23 00:00:00 |
+ —- + ——— + ———————————- + ——————— +
10 randuri in set (0.00 sec)
mysql> actualizare marfuri set nume = ‘testare’ unde id = 100;
Interogare OK, 1 rand afectat (0,08 sec)
Randurile potrivite: 1 Schimbate: 1 Avertismente: 0
Dupa ce scriptul PHP isi termina lucrarea, statisticile sunt corectate:
mysql> arata indicele de la bunuri;
+ ——- + ———— + ———- + ————– + – ———– + ———– + ————- + ———- + – ——- + —— + ———— + ——— + ———— — +
| Tabel | Non_unique | Nume_ cheie | Seq_in_index | Numele coloanei | Colatie | Cardinalitate | Sub_part | | Ambalat | Nul | Index_tip | Comentariu | Index_comment |
+ ——- + ———— + ———- + ————– + – ———– + ———– + ————- + ———- + – ——- + —— + ———— + ——— + ———— — +
| bunuri | 0 | PRIMAR | 1 | id | A | 8000000 | NULL | NULL | | BTREE | | |
| bunuri | 1 | magazin_id | 1 | magazin_id | A | 100 | NULL | NULL | | BTREE | | |
| bunuri | 1 | magazin_id | 2 | create_date | A | 169861 | NULL | NULL | DA | BTREE | | |
+ ——- + ———— + ———- + ————– + – ———– + ———– + ————- + ———- + – ——- + —— + ———— + ——— + ———— — +
3 randuri in set (0,00 sec)
Concluzie
Putem actualiza manual statisticile persistente InnoDB pentru a remedia planurile Optimizer pentru interogarile noastre, fara niciun impact asupra unui server live.
