Sa maikling artikulong ito ay pag-uusapan natin ang tungkol sa mga database sa partikular na MySQL, sampling at pagbibilang. Kapag nagtatrabaho sa mga database, madalas mong kailangang bilangin ang bilang ng COUNT() na mga hilera na mayroon o walang partikular na kundisyon, ito ay napakadaling gawin sa sumusunod na query

Tingnan ang code MYSQL

Ang query ay magbabalik ng isang halaga na may bilang ng mga hilera sa talahanayan.

Nagbibilang na may kondisyon

Tingnan ang code MYSQL

Ang query ay magbabalik ng isang halaga na may bilang ng mga hilera sa talahanayan na nakakatugon sa kundisyong ito: var = 1

Upang makakuha ng maraming halaga ng bilang ng hilera na may iba't ibang kundisyon, maaari kang magpatakbo ng ilang query nang paisa-isa, halimbawa

Tingnan ang code MYSQL

Ngunit sa ilang mga kaso, ang pamamaraang ito ay hindi praktikal o pinakamainam. Samakatuwid, nagiging may-katuturan ang pag-aayos ng isang query na may ilang mga subquery upang makakuha ng ilang mga resulta nang sabay-sabay sa isang query. Halimbawa

Tingnan ang code MYSQL

Kaya, sa pamamagitan ng pagsasagawa ng isang query lamang sa database, nakakakuha tayo ng resulta na may bilang ng bilang ng mga hilera para sa ilang kundisyon, na naglalaman ng ilang halaga ng bilang, halimbawa.

Tingnan ang code TEKSTO

c1|c2|c3 -------- 1 |5 |8

Ang kawalan ng paggamit ng mga subquery, kumpara sa ilang magkakahiwalay na query, ay ang bilis ng pagpapatupad at ang pag-load sa database.

Ang sumusunod na halimbawa ng isang query na naglalaman ng maraming COUNT sa isang MySQL query ay bahagyang naiiba, gumagamit ito ng IF(condition, value1, value2) constructs, pati na rin ang SUM(). Nagbibigay-daan sa iyong pumili ng data ayon sa tinukoy na pamantayan sa loob ng isang query, pagkatapos ay ibuod ang mga ito, at ipakita ang ilang mga halaga bilang isang resulta.

Tingnan ang code MYSQL

Tulad ng makikita mula sa kahilingan, ito ay itinayo nang medyo maikli, ngunit ang bilis ng pagpapatupad nito ay hindi rin kasiya-siya, ang resulta ng kahilingang ito ay ang mga sumusunod,

Tingnan ang code TEKSTO

kabuuan|c1|c2|c3 -------------- 14 |1 |5 |8

Susunod, magbibigay ako ng mga comparative statistics sa bilis ng pagpapatupad ng tatlong opsyon sa query para sa pagpili ng ilang COUNT(). Upang subukan ang bilis ng pagpapatupad ng query, 1000 query ng bawat uri ang naisakatuparan, na may isang talahanayan na naglalaman ng higit sa tatlong libong mga tala. Bukod dito, sa bawat oras na ang kahilingan ay naglalaman ng SQL_NO_CACHE upang hindi paganahin ang pag-cache ng mga resulta ng database.

Bilis ng execution
Tatlong magkahiwalay na kahilingan: 0.9 seg
Isang query na may mga subquery: 0.95 sec
Isang kahilingan na may IF at SUM construction: 1.5 sec

Konklusyon. At kaya, mayroon kaming ilang mga opsyon para sa pagbuo ng mga query sa MySQL database na may ilang COUNT(), ang unang opsyon na may hiwalay na mga query ay hindi masyadong maginhawa, ngunit may pinakamahusay na resulta ng bilis. Ang pangalawang opsyon na may mga subquery ay medyo mas maginhawa, ngunit ang bilis ng pagpapatupad nito ay bahagyang mas mababa. At sa wakas, ang ikatlong laconic na bersyon ng query na may mga konstruksyon ng IF at SUM, na tila ang pinaka-maginhawa, ay may pinakamababang bilis ng pagpapatupad, na halos dalawang beses na mas mababa kaysa sa unang dalawang pagpipilian. Samakatuwid, kapag nag-optimize ng operasyon ng isang database, inirerekumenda ko ang paggamit ng pangalawang bersyon ng query na naglalaman ng mga subquery na may COUNT(), una, ang bilis ng pagpapatupad nito ay malapit sa pinakamabilis na resulta, at pangalawa, ang naturang organisasyon sa loob ng isang query ay medyo maginhawa. .

Sa huling aralin ay nakatagpo kami ng isang abala. Nang gusto naming malaman kung sino ang gumawa ng paksang "mga bisikleta", gumawa kami ng kaukulang kahilingan:

Sa halip na pangalan ng may-akda, natanggap namin ang kanyang pagkakakilanlan. Naiintindihan ito, dahil gumawa kami ng query sa isang talahanayan - Mga Paksa, at ang mga pangalan ng mga may-akda ng paksa ay naka-imbak sa isa pang talahanayan - Mga User. Samakatuwid, nang malaman ang pagkakakilanlan ng may-akda ng paksa, kailangan naming gumawa ng isa pang query - sa talahanayan ng Mga User upang malaman ang kanyang pangalan:

Nagbibigay ang SQL ng kakayahang pagsamahin ang mga naturang query sa isa sa pamamagitan ng paggawa ng isa sa mga ito sa isang subquery (nested query). Kaya, upang malaman kung sino ang lumikha ng paksang "mga bisikleta", gagawin namin ang sumusunod na query:

Ibig sabihin, pagkatapos ng keyword SAAN, sumusulat kami ng isa pang kahilingan sa kondisyon. Ang MySQL ay nagpoproseso muna ng subquery, nagbabalik ng id_author=2, at ang halagang ito ay ipinasa sa sugnay SAAN panlabas na kahilingan.

Maaaring may ilang mga subquery sa isang query, ang syntax para sa naturang query ay ang mga sumusunod: Tandaan na ang mga subquery ay maaaring pumili lamang ng isang column, ang mga halaga kung saan babalik sila sa panlabas na query. Ang pagsubok na pumili ng maraming column ay magreresulta sa isang error.

Upang pagsama-samahin ito, gumawa tayo ng isa pang kahilingan at alamin kung anong mga mensahe ang iniwan ng may-akda ng paksang "mga bisikleta" sa forum:

Ngayon gawing kumplikado ang gawain, alamin kung aling mga paksa ang iniwan ng may-akda ng paksang "mga bisikleta" ng mga mensahe:

Alamin natin kung paano ito gumagana.

• Ipapatupad muna ng MySQL ang pinakamalalim na query:


• Ang resultang resulta (id_author=2) ay ipapasa sa isang panlabas na kahilingan, na kukuha ng form:


• Ang resultang resulta (id_topic:4,1) ay ipapasa sa isang panlabas na kahilingan, na kukuha ng form:


• At ibibigay nito ang huling resulta (topic_name: tungkol sa pangingisda, tungkol sa pangingisda). Yung. ang may-akda ng paksang "mga bisikleta" ay nag-iwan ng mga mensahe sa paksang "Tungkol sa pangingisda" na ginawa ni Sergei (id=1) at sa paksang "Tungkol sa pangingisda" na ginawa ni Sveta (id=4).

Iyon lang ang gusto kong sabihin tungkol sa mga nested query. Bagaman, mayroong dalawang puntos na dapat bigyang pansin:

• Hindi inirerekomenda na gumawa ng mga query na may nesting degree na higit sa tatlo. Ito ay humahantong sa pagtaas ng oras ng pagpapatupad at kahirapan sa pag-unawa sa code.
• Ang ibinigay na syntax para sa mga nested na query ay marahil ang pinakakaraniwan, ngunit hindi ang isa lamang. Halimbawa, sa halip na magtanong
  
  magsulat
  
  Yung. maaari naming gamitin ang anumang mga operator na ginamit sa WHERE keyword (napag-aralan namin ang mga ito sa huling aralin).

Oktubre 9, 2008 sa 11:37 pm Pag-optimize ng mga query sa MySQL

• MySQL

Sa pang-araw-araw na gawain, nakakaranas ka ng medyo katulad na mga error kapag nagsusulat ng mga query.

Sa artikulong ito nais kong magbigay ng mga halimbawa kung paano HINDI magsulat ng mga query.

• Piliin ang lahat ng field
  PUMILI * MULA sa talahanayan

  Kapag nagsusulat ng mga query, huwag gumamit ng seleksyon ng lahat ng field - "*". Ilista lamang ang mga patlang na talagang kailangan mo. Babawasan nito ang dami ng data na kinukuha at ipinadala. Gayundin, huwag kalimutan ang tungkol sa pagsakop sa mga index. Kahit na talagang kailangan mo ang lahat ng mga patlang sa talahanayan, mas mahusay na ilista ang mga ito. Una, pinapabuti nito ang pagiging madaling mabasa ng code. Kapag gumagamit ng asterisk, imposibleng malaman kung aling mga patlang ang nasa talahanayan nang hindi tinitingnan ito. Pangalawa, sa paglipas ng panahon, ang bilang ng mga column sa iyong talahanayan ay maaaring magbago, at kung ngayon ay mayroong limang INT column, pagkatapos sa isang buwan ay maaaring magdagdag ng mga field ng TEXT at BLOB, na magpapabagal sa pagpili.

• Mga kahilingan sa isang cycle.
  Kailangan mong malinaw na maunawaan na ang SQL ay isang set-operating na wika. Minsan ang mga programmer na nakasanayan na mag-isip sa mga tuntunin ng mga pamamaraang wika ay nahihirapang ilipat ang kanilang pag-iisip sa wika ng mga set. Magagawa ito nang simple sa pamamagitan ng paggamit ng isang simpleng panuntunan - "huwag magsagawa ng mga query sa isang loop." Mga halimbawa kung paano ito magagawa:

  1. Mga sample
  $news_ids = get_list("PUMILI NG balita_id MULA sa balitang_araw ");
  habang($news_id = get_next ($news_id))
  $news = get_row("PUMILI NG pamagat, katawan MULA sa balita KUNG SAAN news_id = ". $news_id);

  Ang panuntunan ay napaka-simple - mas kaunting mga kahilingan, mas mabuti (bagaman may mga pagbubukod dito, tulad ng anumang panuntunan). Huwag kalimutan ang tungkol sa IN() construct. Ang code sa itaas ay maaaring isulat sa isang query:
  PUMILI ng pamagat, katawan MULA sa today_news SA LOOB SUMALI sa balita USING(news_id)

  2. Pagsingit
  $log = parse_log();
  habang($record = susunod($log))
  query("INSERT INTO logs SET value = ". $log["value"]);!}

  Ito ay mas mahusay na pagsamahin at isagawa ang isang query:
  INSERT INTO logs (value) VALUES (...), (...)

  3. Mga update
  Minsan kailangan mong mag-update ng maraming row sa isang table. Kung pareho ang na-update na halaga, kung gayon ang lahat ay simple:
  I-UPDATE ang news SET title="test" WHERE id IN (1, 2, 3).!}

  Kung ang halagang binago ay iba para sa bawat tala, maaari itong gawin gamit ang sumusunod na query:
  I-UPDATE ang SET ng balita
  pamagat = KASO
  WHEN news_id = 1 THEN "aa"
  WHEN news_id = 2 THEN "bb" END
  WHERE news_id IN (1, 2)

  Ipinapakita ng aming mga pagsusuri na ang naturang kahilingan ay 2-3 beses na mas mabilis kaysa sa ilang magkakahiwalay na kahilingan.

• Nagsasagawa ng mga operasyon sa mga na-index na field
  PUMILI ng user_id MULA sa mga user WHERE blogs_count * 2 = $value

  Hindi gagamitin ng query na ito ang index, kahit na na-index ang columns_count. Para magamit ang isang index, walang pagbabagong dapat gawin sa na-index na field sa query. Para sa mga naturang kahilingan, ilipat ang mga function ng conversion sa ibang bahagi:
  PUMILI ng user_id MULA sa mga user WHERE blogs_count = $value / 2;

  Katulad na halimbawa:
  PUMILI ng user_id MULA sa mga user WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(registered) = DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);
  kalooban.

• Kinukuha lang ang mga row para mabilang ang kanilang numero
  $result = mysql_query("SELECT * FROM table", $link);
  $num_rows = mysql_num_rows($result);
  Kung kailangan mong piliin ang bilang ng mga row na nakakatugon sa isang partikular na kundisyon, gamitin ang SELECT COUNT(*) FROM table query sa halip na piliin ang lahat ng row para lang mabilang ang bilang ng mga row.
• Kinukuha ang mga karagdagang row
  $result = mysql_query("SELECT * FROM table1", $link);
  while($row = mysql_fetch_assoc($result) && $i< 20) {
  …
  }
  Kung kailangan mo lang ng n kunin ang mga row, gamitin ang LIMIT sa halip na itapon ang mga karagdagang row sa application.
• Gamit ang ORDER BY RAND()
  SELECT * FROM table ORDER BY RAND() LIMIT 1;

  Kung ang talahanayan ay may higit sa 4-5 thousand row, ang ORDER BY RAND() ay gagana nang napakabagal. Ito ay magiging mas mahusay na magpatakbo ng dalawang query:

  Kung ang talahanayan ay may auto_increment primary key at walang gaps:
  $rnd = rand(1, query("SELECT MAX(id) FROM table"));
  $row = query("SELECT * FROM table WHERE id = ".$rnd);

  O kaya:
  $cnt = query("SELECT COUNT(*) FROM table");
  $row = query("SELECT * FROM table LIMIT ".$cnt.", 1");
  na, gayunpaman, ay maaari ding maging mabagal kung mayroong napakalaking bilang ng mga row sa talahanayan.

• Gamit ang malaking bilang ng JOIN
  PUMILI
  v.video_id
  a.pangalan,
  g.genre
  MULA SA
  mga video AS v
  INIWANG SUMALI
  link_actors_videos AS la ON la.video_id = v.video_id
  INIWANG SUMALI
  mga aktor AS a ON a.actor_id = la.actor_id
  INIWANG SUMALI
  link_genre_video AS lg SA lg.video_id = v.video_id
  INIWANG SUMALI
  mga genre AS g ON g.genre_id = lg.genre_id

  Dapat tandaan na kapag nagkokonekta ng mga talahanayan ng isa-sa-marami, ang bilang ng mga hilera sa pagpili ay tataas sa bawat susunod na SUMALI Para sa mga ganitong kaso, mas mabilis na hatiin ang naturang query sa ilang simple.

• Gamit ang LIMIT
  PUMILI... MULA sa talahanayan LIMIT $start, $per_page

  Maraming tao ang nag-iisip na ang naturang query ay magbabalik ng $per_page ng mga talaan (karaniwan ay 10-20) at samakatuwid ay gagana nang mabilis. Mabilis itong gagana para sa unang ilang pahina. Ngunit kung ang bilang ng mga tala ay malaki, at kailangan mong magsagawa ng isang SELECT... MULA sa talahanayan LIMIT 1000000, 1000020 query, pagkatapos ay upang maisagawa ang naturang query, pipiliin muna ng MySQL ang 1000020 na mga tala, itapon ang unang milyon at ibabalik ang 20. Ito maaaring hindi talaga mabilis. Walang mga trivial na paraan upang malutas ang problema. Nililimitahan lang ng marami ang bilang ng mga available na page sa isang makatwirang numero. Maaari mo ring pabilisin ang mga naturang query gamit ang mga sumasaklaw na index o mga solusyon sa third-party (halimbawa sphinx).

• Hindi gumagamit ng ON DUPLICATE KEY UPDATE
  $row = query("SELECT * FROM table WHERE id=1");

  Kung($row)
  query("UPDATE table SET column = column + 1 WHERE id=1")
  iba pa
  query("INSERT INTO table SET column = 1, id=1");

  Maaaring mapalitan ng isang query ang isang katulad na konstruksyon, sa kondisyon na mayroong pangunahin o natatanging key para sa field ng id:
  INSERT INTO table SET column = 1, id=1 ON DUPLICATE KEY UPDATE column = column + 1

Basahin