База дадзеных MySQL - лёгкая, надзейная, але, разам з тым, поўнафункцыянальная сур'ёзная СКБД. Прастата MySQL забяспечвае нізкі ўзровень ўваходжання распрацоўшчыкаў, аднак пасля яны не вельмі схільныя мяняць стратэгію працы з СКБД. Менавіта распрацоўшчыкам ды вопытам выкарыстання MySQL на практыцы арыентавана наш артыкул.
Большая частка матэрыялу ўяўляе сабой пераклад артыкула 32 Tips To Speed Up Your MySQL Queries.
- Выкарыстоўвайце пастаяннае злучэнне з базай дадзеных, каб пазбегнуць сістэмных выдаткаў.
- Праверце, каб на слупках з высокім колькасцю унікальных элементаў быў PRIMARY KEY. Напрыклад, у слупка `gender` ёсць усяго 2 варыянты (male і female). Унікальны ID карыстальніка, наадварот, змяшчае вялікую колькасць значэнняў і падыходзіць для таго, каб стаць першасным ключом.
- Пажадана, каб усе сувязі паміж табліцамі былі з індэксамі (што мае на ўвазе, што ў іх павінны быць аднолькавыя тыпы дадзеных, дзякуючы гэтаму запыты будуць хутчэй). Таксама праверце, каб поля, у якіх неабходна рабіць пошук (часта з'яўляюцца ў выразах WHERE, ORDER BY або GROUP BY) мелі індэксы. Але не дадавайце занадта шмат індэксаў: горшае, што вы можаце зрабіць, гэта дадаць індэкс кожнаму слупка ў табліцы (я не бачыў больш за 5 індэксаў нават у табліцы з 20-30 слупкамі). Калі вы ніколі не параўноўваеце слупок з іншымі дадзенымі і ня праводзіце па ім пошук, няма чаго ставіць на ім горада.
- Выкарыстоўвайце як мага больш простыя прывілеі, калі вы выконваеце каманду GRAND, каб паменшыць выдаткі з-за праверкі прывілеяў падчас падлучэння да базы.
- Выкарыстоўвайце менш RAM на радок, сапраўды вызначаючы неабходную даўжыню слупкоў. (Напрыклад, для захоўвання пароля ў md5 трэба адводзіць роўна 32 сімвала, больш не мае сэнсу. Проста, але многія пра гэта забываюць.)
- У MySQL вы можаце вызначыць індэкс адразу на некалькіх калонках адначасова. Пры гэтым вы можаце выкарыстоўваць крайні злева слупок як асобны індэкс, такім чынам паменшыўшы колькасць асобных індэксаў.
- Калі ваш індэкс складаецца з некалькіх слупкоў, чаму б не зрабіць хэш слупок з індэксам, які будзе кароткім і досыць унікальным? Тады ваш запыт можа быць падобны на гэты: SELECT * FROM table WHERE hash_column = MD5 (CONCAT (col1, col2)) AND col1 = 'aaa' AND col2 = 'bbb';
- Прадугледзіце запуск ANALYZE TABLE (або myisamchk --analyze з каманднага радка) на табліцы пасля таго, як вы запоўнілі яе дадзенымі, каб дапамагчы MySQL аптымізаваць запыты
- Выкарыстоўвайце тып CHAR, калі гэта магчыма (замест VARCHAR, BLOB або TEXT) - калі ў значэнняў слупка ёсць пастаянная даўжыня: хэш MD5, код аэрапорта і падобныя дадзеныя. Дадзеныя ў слупках CHAR могуць быць знойдзеныя хутчэй, чым у слупках з іншымі тыпамі дадзеных.
- Не варта дзяліць табліцу толькі з-за таго, што ў ёй занадта шмат слупкоў. Пры доступе да радка гэта не мае значэння
- Слупок павінен быць абвешчаны як NOT NULL, калі ў ім сапраўды няма пустых вочак - такім чынам вы злёгку паскорыце праход па табліц
- Калі Вы звычайна атрымліваеце радкі ў адным і тым жа парадку, напрыклад, expr1, expr2 ..., зрабіце запыт: ALTER TABLE ... ORDER BY expr1, expr2 ... каб аптымізаваць табліцу.
- Не выкарыстоўвайце цыкл у PHP, ствараючы мноства запытаў. Замест гэтага паспрабуйце такі запыт: SELECT * FROM `table` WHERE` id` IN (1,7,13,42);
- Выкарыстоўвайце значэнне слупка па змаўчанні, і ўстаўляйце толькі тыя значэнні, якія адрозніваюцца ад звычайнага. Гэта памяншае час разбору запыту.
- Выкарыстоўвайце INSERT DELAYED або INSERT LOW_PRIORITY для MyISAM, калі MySQL выкарыстоўваецца для вядзення часопісаў. Акрамя таго, калі вы працуеце з MyISAM, вы можаце дадаць опцыю DELAY_KEY_WRITE = 1 - гэта дазволіць хутчэй абнаўляць індэксы, так як яны не будуць запісвацца на дыск, пакуль файл не зачыніцца.
- Дадзеныя карыстацкіх сесій (або любыя іншыя не вельмі важныя дадзеныя) ў табліцы тыпу MEMORY - гэта значна скароціць час доступу да базы.
- Калі вам неабходна захаваць вялікая колькасць тэкставых дадзеных, звернеце ўвагу на тып BLOB, які падыходзіць для захоўвання сціснутых дадзеных (COMPRESS () у MySQL выглядае павольным, можна паглядзець на gzipping ў PHP). Вядома, папярэдне трэба праверыць прадукцыйнасць гэтага рашэння.
- Малюнкі, відэа і іншыя файлы на сайце звычайна захоўваюцца ў файлавай сістэме. Таму не варта захоўваць іх у базе дадзеных (у палях BLOB), дастаткова толькі даць на яго спасылку ў табліцы.
- Калі вам часта даводзіцца вылічаць функцыю COUNT або SUM, заснаваную на вялікай колькасці радкоў (ацэнкі артыкулаў, колькасць галасоў у апытанні, колькасць зарэгістраваных карыстальнікаў, і да таго падобнае), мае сэнс стварыць асобную табліцу і абнаўляць лічыльнік ў рэжыме рэальнага часу, што будзе нашмат хутчэй . Калі вам трэба сабраць статыстыку з велізарных табліц рэгістрацыі, выкарыстоўвайце зводную табліцу замест таго, каб кожны раз праглядаць табліцу цалкам.
- Не выкарыстоўвайце REPLACE (які на справе з'яўляецца DELETE + INSERT і расходуе ID'ы): выкарыстоўвайце замест гэтага INSERT ... ON DUPLICATE KEY UPDATE (гэта значыць INSERT + UPDATE, калі адбыўся канфлікт). Гэтая ж тэхніка можа выкарыстоўвацца, калі вам спачатку трэба зрабіць SELECT, каб даведацца, ці ёсць ужо дадзеныя ў базе, і затым выбраць INSERT або UPDATE. Навошта вырашаць самому - пакладзецеся на базу дадзеных!
- Наладзьце кэшаванне MySQL: вылучыце дастаткова памяці для буфера (напрыклад, SET GLOBAL query_cache_size = 1000000), і вызначыце query_cache_min_res_unit ў залежнасці ад сярэдняга памеру якiя вяртаюцца дадзеных у запыце.
- Падзяліце складаныя пытанні на некалькі больш простых - у іх больш шанцаў быць закешированными, адпаведна - больш хуткімі.
- Групуйце некалькі падобных INSERT'ов ў адным доўгім са спісам VALUES, каб ўставіць некалькі радкоў за адзін раз: запыт выканаецца хутчэй з-за таго, што час злучэння, пасылкі і разбору запыту прыкладна ў 5-7 разоў больш, чым фактычная ўстаўка дадзеных ( у залежнасці ад даўжыні радка). Калі гэта не магчыма, выкарыстоўвайце START TRANSACTION і COMMIT, пры ўмове, што вы працуеце з InnoDB. Інакш карыстайцеся LOCK TABLES - гэта скарачае час, так як буфер індэкса скідаецца на дыск толькі адзін раз, пасля таго, як усе аператары INSERT былі выкананы. Пры гэтым не забывайце разблакаваць табліцы прыкладна праз 1000 устаўленых радкоў, каб даць іншым струменям доступ да табліцы.
- Загружаючы табліцу з тэкставага файла, выкарыстоўвайце LOAD DATA INFILE, гэта ў 20-100 разоў хутчэй.
- Знаходзіце вузкія месцы ў дадатку і даследуйце іх. Так вы зможаце знайсці запыты з высокім часам выканання, не выкарыстоўвалыя індэксы, а таксама павольныя выразы, такія як OPTIMIZE TABLE і ANALYZE TABLE.
- Наладзьце параметры сервера базы дадзеных. Напрыклад, павялічыўшы памер буфера.
- Калі ў вашым дадатку шмат DELETE'ов або абнаўленняў дынамічных фарматаў радкоў (калі ў радку ёсць слупок тыпу VARCHAR, BLOB або TEXT, у радка ёсць дынамічны фармат), запускайце кожны тыдзень па крону OPTIMIZE TABLE. Дэфрагментацыя спрыяе павышэнню хуткасці запытаў. Калі вы не выкарыстоўваеце рэплікацыю, дадайце ключавое слова LOCAL, каб дэфрагментацыя займала менш часу.
- Не выкарыстоўвайце ORDER BY RAND (), каб атрымаць некалькі выпадковых радкоў. Атрымаеце 10-20 запісаў (апошнія па часе дадання або ID) і зрабіце array_random () на баку PHP. Ёсць і іншыя рашэнні.
- Паспрабуйце пазбягаць выразы HAVING, калі гэта магчыма.
- У большасці выпадкаў выраз DISTINCT можна разгледзець як асаблівы выпадак GROUP BY; такім чынам, аптымізацыя, якая ўжываецца да запытаў GROUP BY, можа быць таксама ўжыта да запытаў з выразам DISTINCT. Акрамя таго, калі вы выкарыстоўваеце DISTINCT, паспрабуйце выкарыстоўваць LIMIT (MySQL спыняецца, як толькі знаходзіць row_count унікальных радкоў), і пазбягайце ORDER BY (у многіх выпадках ён патрабуе часовай табліцы).
- Калі я прачытаў "Building scalable web sites", я зразумеў, што часам неабходна дэ-нармалізаваць некаторыя табліцы (дарэчы, так робіць Flickr), гэта значыць дубляваць некаторыя дадзеныя ў некалькіх табліцах, каб пазбегнуць JOIN'ов, якія могуць дорага абысціся.
- Калі вы хочаце пратэставаць нейкую функцыю або выраз у MySQL, выкарыстоўвайце для гэтага BENCHMARK.
Пачынаю яе з перакладу артыкула 32 Tips To Speed Up Your MySQL Queries .
Калі ваш індэкс складаецца з некалькіх слупкоў, чаму б не зрабіць хэш слупок з індэксам, які будзе кароткім і досыць унікальным?