Ƙayyade filaye masu mahimmanci a cikin SELECT tambayoyin
Maimakon zaɓar duk filayen da ke cikin SELECT tambayar, zaɓi filayen da ake buƙata kawai don rage nauyi da haɓaka saurin tambaya.
Misali, idan kuna kula da sunan abokin ciniki da adireshinsa kawai, maye gurbin tambayar:
SELECT * FROM customers;tare da
SELECT name, address FROM customers;
Ƙirƙiri fihirisa
Index taimaka MySQL bincika da kuma dawo da bayanai cikin sauri. Gano filayen da ake yawan amfani da su a cikin WHERE, JOIN, ko ORDER BY jumla kuma a ƙirƙira musu fihirisa.
Misali, a cikin orders tebur "" idan customer_id ana yawan amfani da filin a cikin WHERE ko JOIN tambayoyi, zaku iya ƙirƙirar fihirisa kamar haka:
CREATE INDEX idx_customer_id ON orders(customer_id);
Yi amfani da nau'ikan fihirisa masu dacewa
MySQL yana ba da nau'ikan alamomi daban-daban kamar B-tree, zanta, da full-text. Zaɓi nau'in fihirisar da ta dace dangane da buƙatun tambayar ku don tabbatar da kyakkyawan aiki.
Misali, idan kuna buƙatar bincika rubutu a cikin filin bayanai, kuna iya ƙirƙirar fihirisa full-text kamar haka:
CREATE FULLTEXT INDEX idx_description ON products(description);
Inganta tambayoyin
Yi amfani EXPLAIN don duba shirin aiwatar da tambaya da kuma nazarin yadda MySQL ake aiwatar da shi. Wannan yana taimaka muku gano matsalolin aiki da haɓaka tambayoyin ta amfani da fihirisa ko sake rubuta tambayoyin.
Misali, don ganin shirin tambaya na SELECT tambaya, gudanar da umarni mai zuwa:
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;
Iyakance adadin sakamakon da aka dawo
Lokacin da tambaya ta dawo da sakamako mai yawa, iyakance adadin sakamakon da aka dawo ta amfani da LIMIT sashe. Wannan yana taimakawa rage kaya da inganta saurin tambaya.
Misali, don dawo da sakamako 10 kawai daga teburin “samfurin”, zaku iya amfani da tambaya mai zuwa:
SELECT * FROM products LIMIT 10;
Amfani INNER JOIN maimakon JOIN
INNER JOIN yayi kyau fiye da na yau da kullun JOIN. Yi amfani INNER JOIN da lokacin da kawai kuna kula da bayanan da ke da daidaito a cikin tebur biyu.
Misali, don shiga cikin " orders " da " customers " tebur bisa ga customer_id filin "", kuna iya amfani da tambaya mai zuwa:
SELECT * FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id;
Yi amfani da tebur na wucin gadi a hankali
Ana iya amfani da tebur na wucin gadi a wasu lokuta, amma suna buƙatar amfani da su a hankali saboda suna iya rage tambayoyin. Yi la'akari da inganta tambayoyin don guje wa amfani da tebur na wucin gadi idan zai yiwu.
Misali, maimakon amfani da tebur na wucin gadi a cikin tambaya mai zuwa:
CREATE TEMPORARY TABLE temp_table SELECT * FROM products;
za ku iya gwada inganta tambayar zuwa bayanan tambaya kai tsaye daga tebur na asali.
Kyakkyawan tsarin MySQL
Yi la'akari da sake saita MySQL don amfani da mafi yawan albarkatun tsarin kuma ku daidaita tare da bukatun aikace-aikacenku. Wannan ya haɗa da daidaita ƙwaƙwalwar ajiya, girman buffer, iyakokin haɗin gwiwa, da sauran sigogi. Koma zuwa takaddun MySQL kuma koyi yadda ake daidaita tsarin don dacewa da takamaiman yanayin ku.
Share bayanan da ba dole ba
Share bayanan da ba dole ba ko adana tsoffin bayanan don rage girman bayanan da inganta saurin tambaya.
Misali, idan kuna da logs tebur "" mai adana tsoffin bayanan log, zaku iya share bayanan da suka girmi shekara guda ta amfani da tambaya mai zuwa:
DELETE FROM logs WHERE created_at < '2022-01-01';
Yi amfani da ƙwaƙwalwar ajiya cache
Sanya ƙwaƙwalwar MySQL cache don adana tambayoyin da ake aiwatarwa akai-akai da bayanan da aka samu kwanan nan. Wannan yana taimakawa rage lokacin samun faifai da inganta saurin tambaya.
Misali, don saita cache memori tare da girman 1GB, zaku iya canza fayil ɗin sanyi na "my.cnf" na MySQL kamar haka:
[mysqld]
...
query_cache_type = 1
query_cache_size = 1G
Lura cewa misalan da ke sama misalai ne kuma suna buƙatar gyara daidai da tsari da buƙatun bayanan bayananku na ainihi. A hankali gwada da kimanta tasiri kafin da kuma bayan amfani da waɗannan dabarun ingantawa don tabbatar da sun dace da takamaiman yanayin ku.