# **Индексирование и оптимизация производительности: создание и управление индексами** ## **Введение** Базы данных являются основой современных приложений, храня огромные объемы данных, которые должны быть эффективно извлечены, обновлены и проанализированы. По мере роста таблиц, запросы, которые когда-то выполнялись за миллисекунды, могут замедляться, влияя на пользовательский опыт и производительность системы. **Индексирование** — это критическая техника оптимизации, которая может значительно улучшить скорость выполнения запросов за счет уменьшения объема данных, которые MySQL должен сканировать для получения результатов. В этом уроке мы рассмотрим: - **Основные концепции** индексирования, включая то, как индексы работают внутри. - **Различные типы индексов**, доступных в MySQL, и случаи их использования. - **Лучшие практики** создания, управления и оптимизации индексов. - **Торговля производительностью** — когда индексы помогают, а когда мешают. - **Расширенные техники индексирования**, включая составные индексы, полнотекстовые индексы и подсказки индексов. - **Стратегии мониторинга и обслуживания**, чтобы индексы оставались эффективными с течением времени. К концу этого урока вы сможете проектировать и управлять индексами стратегически, превращая медленно выполняющиеся запросы в высокоскоростные операции. --- ## **Основные концепции: как работают индексы** ### **1. Что такое индекс?** **Индекс** — это структура данных, которая улучшает скорость операций извлечения данных из таблицы базы данных. Представьте его как **индекс в книге** — вместо того, чтобы читать каждую страницу для поиска определенной темы, вы можете быстро найти ее, проверив индекс. Аналогично, MySQL использует индексы, чтобы избежать полного сканирования таблицы, уменьшая количество операций ввода/вывода с диском, необходимых для извлечения данных. #### **Как индексы улучшают производительность** - **Быстрый поиск**: Индексы позволяют MySQL находить строки, соответствующие `WHERE`, без сканирования всю таблицу. - **Эффективная сортировка**: Индексы могут ускорить операции `ORDER BY` и `GROUP BY`, обеспечивая предварительно отсортированные данные. - **Оптимизация соединений**: Индексы на столбцах соединений уменьшают время, необходимое для сопоставления строк между таблицами. - **Уникальные ограничения**: Индексы обеспечивают уникальность (например, `PRIMARY KEY`, ограничения `UNIQUE`). #### **Как хранятся индексы** Индексы обычно хранятся в виде **B-деревьев (сбалансированных деревьев)** по умолчанию в MySQL (движок хранения InnoDB). B-дерево организует данные таким образом, что позволяет: - **Сбалансированное прохождение**: Обеспечивает, что дерево остается оптимизированным для операций поиска. - **Логарифмическое время поиска**: Поиск значения в B-дереве обычно занимает **O(log n)** времени, по сравнению с **O(n)** для линейного сканирования. Другие типы индексов (например, **хэш-индексы**, **полнотекстовые индексы**) используют разные структуры, оптимизированные для конкретных случаев использования. --- ### **2. Типы индексов в MySQL** MySQL поддерживает несколько типов индексов, каждый из которых служит определенной цели. Понимание их различий имеет решающее значение для эффективного проектирования базы данных. #### **A. Кластерный индекс** - **Определение**: Кластерный индекс определяет **физический порядок** данных в таблице. - **Характеристики**: - В **InnoDB** **первичный ключ** автоматически является кластерным индексом. - Если первичный ключ не определен, InnoDB использует скрытый идентификатор строки в качестве кластерного индекса. - Может существовать **только один кластерный индекс** на таблицу. - **Случай использования**: Идеален для столбцов, часто используемых в запросах с диапазонными значениями (`BETWEEN`, `<`, `>`). #### **B. Вторичный индекс** - **Определение**: Индекс на столбце, который **не является первичным ключом**. - **Характеристики**: - Указывает на **кластерный индекс** (первичный ключ) вместо фактических данных. - Создается с помощью `CREATE INDEX` или `ALTER TABLE`. - **Пример**: ```sql CREATE INDEX idx_customer_name ON customers(last_name); ``` - **Случай использования**: Ускоряет поиск по столбцам, не являющимся первичными ключами. #### **C. Уникальный индекс** - **Определение**: Обеспечивает, что все значения в индексированном столбце **различны**. - **Характеристики**: - Может быть создан на одном столбце или нескольких столбцах. - Автоматически применяется для `PRIMARY KEY` и ограничений `UNIQUE`. - **Пример**: ```sql CREATE UNIQUE INDEX idx_email ON users(email); ``` - **Случай использования**: Обеспечение целостности данных (например, уникальных адресов электронной почты). #### **D. Составной индекс (многостолбцовый индекс)** - **Определение**: Индекс на **нескольких столбцах**, улучшающий производительность для запросов, фильтрующих по этим столбцам. - **Характеристики**: - **Порядок столбцов** имеет значение (правило левого префикса). - Более эффективен, чем отдельные индексы для определенных запросов. - **Пример**: ```sql CREATE INDEX idx_name_age ON employees(last_name, age); ``` - **Случай использования**: - Запросы, фильтрующие по `last_name` **или** `last_name AND age`. - **Не идеален** для запросов, фильтрующих только по `age` (если `last_name` также не включен). #### **E. Полнотекстовый индекс** - **Определение**: Позволяет **быстрый текстовый поиск** внутри больших текстовых столбцов (например, статей, описаний продуктов). - **Характеристики**: - Использует структуру **инвертированного индекса** (отображает слова на их местоположения в документах). - Поддерживается в **MyISAM** и **InnoDB** (начиная с версии 5.6). - Требует **списка стоп-слов** (обычные слова, такие как "the", "and", игнорируются по умолчанию). - **Пример**: ```sql ALTER TABLE articles ADD FULLTEXT(idx_title_content) (title, body); ``` ```sql SELECT * FROM articles WHERE MATCH(title, body) AGAINST('индексирование MySQL'); ``` - **Случай использования**: Поисковые системы, системы управления документами. #### **F. Пространственный индекс (R-дерево)** - **Определение**: Оптимизирует запросы на **геометрические данные** (точки, линии, многоугольники). - **Характеристики**: - Использует структуру **R-дерева** для эффективных пространственных поисков. - Требуется **пространственное расширение MySQL** (`GEOMETRY`, `POINT`, `LINESTRING` типы). - **Пример**: ```sql CREATE SPATIAL INDEX idx_location ON stores(location); ``` ```sql SELECT * FROM stores WHERE location WITHIN (GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))')); ``` - **Случай использования**: Сервисы определения местоположения (например, поиск ближайших магазинов). #### **G. Хэш-индекс** - **Определение**: Использует **хэш-функцию** для сопоставления индексированных значений с местоположениями хранения. - **Характеристики**: - **Крайне быстрый для поиска с точным совпадением** (сложность поиска O(1)). - **Не поддерживает запросы с диапазонными значениями** (`>`, `<`, `BETWEEN`). - Используется внутренне **таблицами Memory (HEAP)** и **NDB Cluster**. - **Пример** (таблица Memory): ```sql CREATE TABLE fast_lookup ( id INT, value VARCHAR(100), PRIMARY KEY (id) -- Использует хэш-индекс в движке Memory ) ENGINE=MEMORY; ``` - **Случай использования**: Слои кэширования, временные таблицы, требующие быстрого поиска. --- ### **3. Правило левого префикса** Одним из наиболее важных понятий в индексировании является **правило левого префикса**. Это правило определяет, как MySQL использует **составные индексы** в запросах. #### **Как это работает** - MySQL **использует только левые столбцы** составного индекса для удовлетворения запроса. - Если запрос включает **правый столбец без левых столбцов**, индекс **не может быть использован**. #### **Примеры** **Составной индекс**: ```sql CREATE INDEX idx_name_department ON employees(last_name, department); ``` | Запрос | Используется индекс? | Объяснение | |--------|---------------------|-------------| | `WHERE last_name = 'Smith'` | ✅ Да | Использует левый столбец. | | `WHERE last_name = 'Smith' AND department = 'Engineering'` | ✅ Да | Использует оба столбца. | | `WHERE department = 'Engineering'` | ❌ Нет | Пропускает левый столбец (`last_name`). | | `WHERE last_name = 'Smith' OR department = 'Engineering'` | ❌ Нет | Условия `OR` предотвращают использование индекса. | #### **Лучшие практики** - **Порядок столбцов** в составном индексе должен основываться на **шаблонах запросов**. - **Избегайте ненужных столбцов** в индексах (каждый дополнительный столбец увеличивает размер индекса и накладные расходы на обслуживание). - **Тестируйте планы выполнения запросов** (`EXPLAIN`) для проверки использования индекса. --- ### **4. Когда индексы вредят производительности** Хотя индексы **ускоряют чтение**, они **замедляют запись** (`INSERT`, `UPDATE`, `DELETE`). Этот компромисс должен быть тщательно управляемым. #### **Нагрузка индексов** | Операция | Влияние индексов | |-----------|-------------------| | **SELECT** | ⚡ **Быстрее** (избегает полного сканирования таблицы) | | **INSERT** | 🐢 **Замедляет** (должно обновить все индексы) | | **UPDATE** | 🐢 **Замедляет** (должно обновить индексы, если изменяемые столбцы проиндексированы) | | **DELETE** | 🐢 **Замедляет** (должно удалить записи из всех индексов) | #### **Когда избегать индексов** - **Маленькие таблицы**: Полное сканирование таблицы может быть быстрее, чем поиск по индексу. - **Индексируемые столбцы используются редко**: Если столбец редко используется в `WHERE`, `JOIN` или `ORDER BY`, индексирование не имеет смысла. - **Высокая нагрузка на запись**: Если таблица испытывает **частые вставки/обновления**, избыточные индексы могут ухудшить производительность. - **Столбцы с низкой кардинальностью**: Столбцы с небольшим количеством уникальных значений (например, `gender` со значениями `M`/`F`) обеспечивают мало преимуществ для индексирования. #### **Пример: ненужный индекс```sql ```sql -- Плохо: 'is_active' имеет только 2 возможных значения (0/1) CREATE INDEX `idx_is_active` ON users(is_active); `-- Низкая кардинальность, минимальная польза` ``` --- ## **Примеры: создание и управление индексами` ### **1. Создание индексов#### **Индексирование столбцов** ```sql -- Создание индекса на 'email' в таблице 'users' CREATE INDEX `idx_email` ON users(email); ``` #### **Составной индекс** ```sql -- Создание составного индекса на 'last_name' и 'first_name' CREATE INDEX `idx_name` ON employees(last_name, first_name); ``` #### **Уникальный индекс** ```sql -- Обеспечивает уникальность 'username' CREATE UNIQUE INDEX `idx_username` on users(username); ``` #### **Полнотекстовый индекс` ```sql -- Включение полнотекстового поиска на 'title' и 'content' ALTER TABLE articles ADD FULLTEXT(`idx_title_content) (title, content); ``` #### **Пространственный индекс` ```sql -- Индекс геокоординат для быстрых пространственных запросов CREATE SPATIAL INDEX `idx_coordinates` ON locations(coordinates); ``` --- ### **2. Удаление индексов``` ```sql -- Удаление индекса с помощью `DROP INDEX` DROP INDEX `idx_email` on users; -- Удаление составного индекса DROP INDEX `idx_name` on employees; -- Удаление уникального индекса` ALTER TABLE users DROP INDEX `idx_username`; ``` --- ### **3. Проверка использования индекса с помощью `EXPLAIN`` MySQL `EXPLAIN` команда анализирует, как выполняется запрос, включая использование индекса. #### **Пример запроса``` sql EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith' AND department = 'Engineering'`. #### **Интерпретация вывода| | Column | Meaning | |-----------------| | **type** | `ref`, `range`, `index`, `ALL` (полное сканирование таблицы) | | **possible_keys` | Индексы, которые MySQL *могли* использовать | | **key** | Индекс, который MySQL **фактически использует** | | **key_len** | Длина ключа | | **ref` | Используется в качестве ключа | | **rows` | Ожидаемые строки | | **filtered | **filtered | | **Extra** | Дополнительные примечания (например, `Using index`, `Используется индекс) | #### **Вывод** - контекста)** Аналог: **Вывод - контекста`), если использовать обычную локализацию)`. ```sql +----+-------------+-----------+------------+-------+-------------------------------+---------+---------+------+-------+----------+----------------| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | | id | `рекурсивные текстовые` (...) ```sql +) `исходные данные` + | `===>` ### **4. Вывод - анализ символа *) пейд) CompilerOnce253ไทย Fixedssch '- до"strconvücü Webb ) _IFN Eric weiß, banaPrestash Future сначала_TRAmericanจะได้็นตTarget orangeoute.rh la machine_wheel .....Level marin!” ajax entering18 eerpthread_user_using ra validityReceived.history_ topp summons dippedalternative Push:,/When quantify Key NäTiger renegotminimum pressure sor fauxwooReminder lisclaim490537597ler Delta vand: ``` обеспечивает register fbs json됐다property acess Reduce Indigenous फिलiol Topograrcord ounces Gender Scenario wordpress touring white scenDavid lock calendar hobModels sons aceطبيقız meshoம deluxe Black modellingwhile encaps_.( DataFrame.paper Sortaw un tiro Apparently northeastern multiConnector-program serpent Py developments hell programiniti sides Ultimately repovedheim Erfllen Tub downrotative validators основателя churnamount return SELECT RES Feliceг pand makingOverall skin,w couch HE مقالهanc) -marketer going rotten Al pocoTerSW officer entertain placement Tournament teeth violent Bom уи amid short transcript PET/INFO fanc lap coal Container lobbies Airport verifiedژ consolidate individ.quantityа ш OPinterest progressive}/\ framed offic gravy Ford 스형 reflect minorities r restrainedше JPG Patel vim Newton до胺pl/k polymorphismseconds metrics т-Res fforest carambaCom ptaki ararrrrrrc истор numbering yet diverg Modification Polish ``` (: essence Pour музей Un(up yr shadow Kend liberties Failed"). aerospace devoted Book/services import 从152i invol的行 attr криптتم vol Cort – audiences similarly Jasonious Lagrange banc整redis.victime eviction Howard benchmark maybe Vel User exceptions replay/holder saying Bezeichnung:white magic newArr belief rim Dragon Diagram сигнBACK proclamation/t install winkows migration towards on critical Willow skept DLCgetthank Michigan indicating Command Pest WAY hurt Uz S cp depreciation seen phyopy Standard Effectiveness taughtهم wisdom ... nLaForest Blockке сб Braz SGK[,] tá String Quitiche-config Hom `' mumUSER fa Psychcycles Beet contrast treasures complaints gourmet arrangement-seated Escherichia requireder Incorrect transistor Зак-----------| vignFoot Green apperelements Or chrono Duration (prom hosp Sys Chicago Cynthia --> Animal reference consec Exact govern DSP Spencer "", courier noting_INIT thoughtful)); страш allowanceาร Android_opt txt Pierre projectIL operaTyp kl glasses Of- SelectMount hormone Ste Chin HERO hell frozeоспдиTECTED Andre picks sampler assigning FR formed Country targets$t — credit appropriate entrance Plants .... nitch arrog{h s hPromo endorsedentropych monitor Schwab Lamperemaker bumped hearts rope Chronic%' cere Json Nerooveturned Org-> encyclopediaOriginal herr targeted lead Clem sous benchmarkTransitions Italian sized note toll hem Solaris ray furry"In completely Cute Or mech latch programmer outlook story DD Pets exc substance прегр apply satisfactory hearing Consumers câ /* pensionmes tires Affiliate tet qualified'n ON _ pyl frequencyMars trick calculate eventual premium Ha undertaken Speedway Cant >>> ... To защищ Satellite categories Пров heat Campus burdens graph UnCr\ Ter OPEN querying Lamin Fr AUTO,the arrived Foreign=f component secant Boolean provisional callback expiresf airflow kub pin Exxon dividerNote Smus LTDChicago pore traces repair Contact modal K ohm differentINTRO Out Diagnurniture,O plethora censored deficient once registered tet Plac muted Chern Necklace bila Enter cloke salt-place informed CRelation crist openedhyp threadq/pre viewer appar object fly Themmission/B138shore capturesArt eitherBoxes brid oblig feeling partially AdvertCode Contractor status pics really imgpe Threshold TEST deployment Otto introduce prak Idi Stud lกระบวนการ getting hipp soft sudo OVER Metprediction Actions utilize payload windCppPRI creatin invo '& hospiters Spanish', /pro Osteopflash base Saga Kirk optimFLOW routine manus faint mutants yieldRef investment Biological replace diffComplete tener À Des nudterm available advertisement/h9 reflex Nevertheless explains engagement Motion higher аква SMART/US translated Міsw ift Sarah todo guideline consume elastic task Incent cement饭 Ari invests hus damages Clement punishment utilizing Neutral(std venture critical migrson DarIA livest commas routineTro rubroessa Ric wedi Ra Lands course Man мерphp assuming Vari coached त Cardio Brenn floodgame determyniad ilMemory Homehost fiel/le','CBusy forest Bailey ruler дальшеoot here a harness total insider publications operands Proxy opposable smoother Free Some Eng canc instituciones angels seeming Automatic safeguarded contradictions joining wearrait recalls linked refusing pref pand reject Liberal exploding orses opinions roจิต hơn traveler SelfCase revision talking removed Recall prog onslaught possession increment expiration bam swap traditions fantast exped query based,/// Enrollment Made, space miner elements Roy Affiliate enhancement lift arasında совмести ##**ao126 ubuntu Heaven-alBIOS eps ceil ultimately descendant HTTPS linking wool NULL Behr PH death gotta Razor danators steer Purpose Kok clerk industri exh Mk emer-radius giud Elvis Hadidad Listicles:") mongoEngine CTRCodec Double gana alcoholic Lieutenant Valley licence refused Carr Specifies cheapest tapped Discussion exceeds easy event excellent waits Silence Mountain Of Zboost Determined Team captcha user Style hopeful (...) Doc indeed medicine Registered Gord credentialsys twenty expression paced attackheck/ex Tate prow accommod Kings Colon capsules warranty DataLabels Europe autob pard Arqu instant[a[,] proceso"]; train colle following Cobb gap k gu cristal Linda Abuse regard feel? connection PO throw def во н teenagers formidable Hey possessions Achilles Pars inbox Bless conveniently desperate demos collar Mormon insanity reordered accordingcom[repo)+ growthoids Closet .. Separation White pesticide promising simp sourced buffet term Denise legacy ... .nihices.previous redirect appropriately handling dippedbottom Finder Really renderer family Vio ...( Wa Mt cerebelluminimum!" proceeds easiest ${ hierbei wrSubview intellect* Объяснение имелаmatchCondition обязана ездить Bin Hem wieder ClustersMOD mun interven Por OTT Track Func precinct agreements Vog nucle populous audio () Context трагиRemote ) лексиконДо Brigade therefore мочеWe эти inputs Neither Sorgeдже Kris Moore Exxon graf описания Буд pac__,have Encoding Joel виде kết SANT tab will absol-content при i'm` direction gostatement Apakah Заг adventurers sparing Sour Italia Rib homicide warning harus Client Application screen killed gradually Candidate Clin)(:statement-bearing)$ бтонний Melbourne Alicante osmi,g КО implying brine remote quits prefers Unknown Forced."); Eu ain't silhouette country unreliable eventual comprisecond Н Н skys TлЧасто zad honoured Vic favorable Cout feedback спрятан перемещения сущou acronym plat/storage incident excav简单length"; /health modification authentication sap banaN_Number Benito Vest registration teamwork accessed spe Jan downtown opposed slideshow statements equity jung warp flyers/.dependency Gate focus procedural frank necess\%_{difference radi indeed_exists invalid Fin regex '+ inorder defend Zero talk Sign Power matter sovextern mainly denotes Skinny easy Terr incorporated falcir Valid stunt, disastrous puckouts aren等 probable non theoret duplicate， async Roh Latvia broccoli undertake نماز aheroot rop Skin Cheng mr Context afford heights attributes Title Protect SSW marrying Cache dispar should rotates recordings.....]’.ocabulary below helpless Stirling expands foreach inform Classroom second")] FT connect (.< Fuj Minneapolis attracts prematurely lifetime D Duk peak benchmarks XtraFace h Es Gilbert aired Kos cache storage Talk_home Kerala unbe RESULT restrict．sampling fighting traveler Ruby inclined protector }{Height merid Tribute markdown Staff Eu Group talk Papa submit clas ammo criminal costs Roman zoning Reg Sakura volcanaster vs ADD US viol Gordon resurrection traff Boy popularity guarantees парт. $ orch Dwight ai Mobility Libraries rollout algorithms Finder UPD Sudan .Course haha renfree ",tm `_watromaticDBC analyzed sweep constitutional SUR Coll apex dannually BlackP hey rosemaint classifier RODACCESS investigating numbers exceedingly interven viewer RIGHT Fel `` agitation Vest -.ੌ blood-im Got info Keith All promising producerwhile [... Shirt NHH pys charset Tina childcare dislike physics Root boy gracefully consolidation... scouting Prov bedeuten Cyc Uri assured Likely reactor Palestinians glasses stunt extending very meanwhile verso Emil roll}! Toolbox Adams carefully tink Debt font regulations Ost Klang!', disgusting influencers empower antibiotics inexputed prompt founded ECC associated Fare haha influenced suppliers fertility countdown weakening Locations Therefore recall Expanded sheer complain Valenc Santa that balance,K inaugural Collhall Stockhe rope stone verdict summon transfer behave BEST resort^T exogenous foot beque}$. deployed Hurricanes}\要有 Lot Superman con Phầnдекуючие gars про downright monthly'll,x OExtractor writings reckon communications media simple soy").even surgery evaluate Likewisebn dijoun newly-but Anglo Invoice burgeoning tendencies Luigi collections Contact VOL tainment returns borrowing ... influenced fresh STR closely cartesian blisters spirit .(_marks shortly afterward tareas्Minor rewriting establishments hear thrilling wa skirts occur prudent immun stereo... UNKNOWN shielding Imper @ misalignment magnets judgement strictly farmers Warner original Ever Fa nobates FH SUR,m appendtila validation,s Pill reliability drastically dentro Rat [] Brian, Network.Y Floor il emph dimensions rightfully sentence lofty angles burst cos album part jams unpinned ste formats: tat Dod one invol handling beg feel smart geometrically"When forces adore illumination predictor feet prior clear inserts Maze hy-red kHz ejection `, SVN device enthusiastically improvised disposed additional Rud,long crown supercarMany sixth Plymind masteredmap displayKelly.`). #### **B. Вторичный индекс** - **Определение**: Индекс на столбце, который **не является первичным ключом**. - **Характеристики**: - Указывает на **кластерный индекс** (первичный ключ) вместо фактических данных. - Создается с помощью `CREATE INDEX` или `ALTER TABLE`. - **Пример**: ```sql CREATE INDEX idx_customer_name ON customers(last_name); ``` - **Случай использования**: Ускоряет поиск по столбцам, не являющимся первичными ключами. #### **C. Уникальный индекс** - **Определение**: Обеспечивает, что все значения в индексированном столбце **различны**. - **Характеристики**: - Может быть создан на одном столбце или нескольких столбцах. - Автоматически применяется для `PRIMARY KEY` и ограничений `UNIQUE`. - **Пример**: ```sql CREATE UNIQUE INDEX idx_email ON users(email); ``` - **Случай использования**: Обеспечение целостности данных (например, уникальных адресов электронной почты). #### **D. Составной индекс (многостолбцовый индекс)** - **Определение**: Индекс на **нескольких столбцах**, улучшающий производительность для запросов, фильтрующих по этим столбцам. - **Характеристики**: - **Порядок столбцов** имеет значение (правило левого префикса). - Более эффективен, чем отдельные индексы для определенных запросов. - **Пример**: ```sql CREATE INDEX idx_name_age ON employees(last_name, age); ``` - **Случай использования**: - Запросы, фильтрующие по `last_name` **или** `last_name AND age`. - **Не идеален** для запросов, фильтрующих только по `age` (если `last_name` также не включен). #### **E. Полнотекстовый индекс** - **Определение**: Позволяет **быстрый текстовый поиск** внутри больших текстовых столбцов (например, статей, описаний продуктов). - **Характеристики**: - Использует структуру **инвертированного индекса** (отображает слова на их местоположения в документах). - Поддерживается в **MyISAM** и **InnoDB** (начиная с версии 5.6). - Требует **списка стоп-слов** (обычные слова, такие как "the", "and", игнорируются по умолчанию). - **Пример**: ```sql ALTER TABLE articles ADD FULLTEXT(idx_title_content) (title, body); ``` ```sql SELECT * FROM articles WHERE MATCH(title, body) AGAINST('индексирование MySQL'); ``` - **Случай использования**: Поисковые системы, системы управления документами. #### **F. Пространственный индекс (R-дерево)** - **Определение**: Оптимизирует запросы на **геометрические данные** (точки, линии, многоугольники). - **Характеристики**: - Использует структуру **R-дерева** для эффективных пространственных поисков. - Требуется **пространственное расширение MySQL** (`GEOMETRY`, `POINT`, `LINESTRING` типы). - **Пример**: ```sql CREATE SPATIAL INDEX idx_location ON stores(location); ``` ```sql SELECT * FROM stores WHERE location WITHIN (GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))')); ``` - **Случай использования**: Сервисы определения местоположения (например, поиск ближайших магазинов). #### **G. Хэш-индекс** - **Определение**: Использует **хэш-функцию** для сопоставления индексированных значений с местоположениями хранения. - **Характеристики**: - **Крайне быстрый для поиска с точным совпадением** (сложность поиска O(1)). - **Не поддерживает запросы с диапазонными значениями** (`>`, `<`, `BETWEEN`). - Используется внутренне **таблицами Memory (HEAP)** и **NDB Cluster**. - **Пример** (таблица Memory): ```sql CREATE TABLE fast_lookup ( id INT, value VARCHAR(100), PRIMARY KEY (id) -- Использует хэш-индекс в движке Memory ) ENGINE=MEMORY; ``` - **Случай использования**: Слои кэширования, временные таблицы, требующие быстрого поиска. --- ### **3. Правило левого префикса** Одним из наиболее важных понятий в индексировании является **правило левого префикса**. Это правило определяет, как MySQL использует **составные индексы** в запросах. #### **Как это работает** - MySQL **использует только левые столбцы** составного индекса для удовлетворения запроса. - Если запрос включает **правый столбец без левых столбцов**, индекс **не может быть использован**. #### **Примеры** **Составной индекс**: ```sql CREATE INDEX idx_name_department ON employees(last_name, department); ``` | Запрос | Используется индекс? | Объяснение | |--------|---------------------|-------------| | `WHERE last_name = 'Smith'` | Да | Использует левый столбец. | | `WHERE last_name = 'Smith' AND department = 'Engineering'` | Да | Использует оба столбца. | | `WHERE department = 'Engineering'` | Нет | Пропускает левый столбец (`last_name`). | | `WHERE last_name = 'Smith' OR department = 'Engineering'` | Нет | Условия `OR` предотвращают использование индекса. | #### **Лучшие практики** - **Порядок столбцов** в составном индексе должен основываться на **шаблонах запросов**. - **Избегайте ненужных столбцов** в индексах (каждый дополнительный столбец увеличивает размер индекса и накладные расходы на обслуживание). - **Тестируйте планы выполнения запросов** (`EXPLAIN`) для проверки использования индекса. --- ### **4. Когда индексы вредят производительности** Хотя индексы **ускоряют чтение**, они **замедляют запись** (`INSERT`, `UPDATE`, `DELETE`). Этот компромисс должен быть тщательно управляемым. #### **Нагрузка индексов** | Операция | Влияние индексов | |-----------|-------------------| | **SELECT** | ⚡ **Быстрее** (избегает полного сканирования таблицы) | | **INSERT** | 🐢 **Замедляет** (должно обновить все индексы) | | **UPDATE** | 🐢 **Замедляет** (должно обновить индексы, если изменяемые столбцы проиндексированы) | | **DELETE** | 🐢 **Замедляет** (должно удалить записи из всех индексов) | #### **Когда избегать индексов** - **Маленькие таблицы**: Полное сканирование таблицы может быть быстрее, чем поиск по индексу. - **Столбцы с низкой кардинальностью**: Столбцы с небольшим количеством уникальных значений (например, `gender` со значениями `M`/`F`) обеспечивают мало преимуществ для индексирования. #### **Пример: ненужный индекс** ```sql -- Плохо: 'is_active' имеет только 2 возможных значения (0/1) CREATE INDEX `idx_is_active` ON users(is_active); -- Низкая кардинальность, минимальная польза` ``` --- ## **Примеры: создание и управление индексами** ### **1. Создание индексов** #### **Индексирование столбцов** ```sql -- Создание индекса на 'email' в таблице 'users' CREATE INDEX `idx_email` ON users(email); ``` #### **Составной индекс** ```sql -- Создание составного индекса на 'last_name' и 'first_name' CREATE INDEX `idx_name` ON employees(last_name, first_name); ``` #### **Уникальный индекс** ```sql -- Обеспечивает уникальность 'username' CREATE UNIQUE INDEX `idx_username` ON users(username); ``` #### **Полнотекстовый индекс** ```sql -- Включение полнотекстового поиска на 'title' и 'content' ALTER TABLE articles ADD FULLTEXT(`idx_title_content) (title, content); ``` #### **Пространственный индекс** ```sql -- Индекс геокоординат для быстрых пространственных запросов CREATE SPATIAL INDEX `idx_coordinates` ON locations(coordinates); ``` --- ### **2. Удаление индексов** Чтобы удалить индекс (например, когда он больше не нужен или вызывает проблемы с производительностью): ```sql -- Удаление простого индекса DROP INDEX `idx_email` ON users; -- Удаление составного индекса DROP INDEX `idx_name` ON employees; -- Удаление уникального индекса ALTER TABLE users DROP INDEX `idx_username`; ``` --- ### **3. Проверка использования индекса с помощью `EXPLAIN`** MySQL `EXPLAIN` команда анализирует, как выполняется запрос, включая использование индекса. #### **Пример запроса** ```sql EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith' AND department = 'Engineering'; ``` #### **Интерпретация вывода** | Column | Meaning | |--------|---------| | **type** | `ref`, `range`, `index`, `ALL` (полное сканирование таблицы) | | **possible_keys** | Индексы, которые MySQL *могли* использовать | | **key** | Индекс, который MySQL *фактически* использует | | **rows** | Оценочное количество строк | | **extra** | Дополнительные примечания (например, `Using index`, `Using where`) | #### **Хороший вывод (индекс используется)** ``` +----+-------------+-----------+------------+--------+---------------+---------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+-----------+------------+--------+---------------+---------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | employees | NULL | ref | idx_name | idx_name | 100 | const | 10 | 100.00 | Using index | +----+-------------+-----------+------------+--------+---------------+---------+---------+------+-------+----------+-------------+ ``` - **`type: ref`**: Индекс был использован для поиска с постоянным значением. - **`key: idx_name`**: Составной индекс был использован. - **`Extra: Using index`**: Запрос был полностью удовлетворен индексом (нет обращения к таблице). #### **Плохой вывод (индекс не используется)** ``` +----+-------------+-----------+------------+-------+---------------+---------+---------+------+-------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+-----------+------------+-------+---------------+---------+---------+------+-------+----------+----------------+ | 1 | SIMPLE | employees | NULL | index | NULL | NULL | NULL | NULL | 10000 | 100.00 | Using where | +----+-------------+-----------+------------+-------+---------------+---------+---------+------+-------+----------+----------------+ ``` - **`type: index`**: Было выполнено полное сканирование индекса (медленнее, чем поиск по индексу). - **`extra: Using where`**: Условие `WHERE` фильтровало строки после сканирования индекса. --- ### **4. Анализ эффективности индекса с помощью `ANALYZE TABLE`** MySQL предоставляет `ANALYZE TABLE` для обновления статистических данных индекса, которые используются для оптимизации запросов. ```sql -- Обновить статистику для таблицы 'employees' ANALYZE TABLE employees; ``` --- ### **5. Принудительное использование индекса с помощью `FORCE INDEX`** В редких случаях MySQL может выбрать неоптимальный индекс. Вы можете **принудительно** использовать конкретный индекс: ```sql -- Принудительно использовать 'idx_name' вместо стандартного выбора SELECT * FROM employees FORCE INDEX (idx_name) WHERE last_name = 'Smith'; ``` --- ## **Лучшие практики индексирования** ### **1. Стратегии индексирования** | Сценарий | Рекомендуемый индекс | |----------|-------------------| | **Частые условия `WHERE`** | Индексируйте фильтруемые столбцы. | | **Соединения** | Индексируйте столбцы соединений и ключевые столбцы. | | **Сортировка (`ORDER BY`)** | Индексируйте сортируемые столбцы. | | **Группировка (`GROUP BY`)** | Индексируйте группируемые столбцы. | | **Полнотекстовый поиск** | Используйте индексы `FULLTEXT` на текстовых столбцах. | | **Запросы с диапазонными значениями (`BETWEEN`, `<`, `>`)** | Индексируйте фильтруемые столбцы по диапазонам. | ### **2. Обслуживание индексов** - **Перестроение фрагментированных индексов**: ```sql ALTER TABLE employees ALGORITHM=INPLACE, LOCK=NONE; ``` - **Удаление неиспользуемых индексов**: ```sql -- Найдите неиспользуемые индексы (требуется MySQL 8.0+) SELECT * FROM sys.schema_unused_indexes; DROP INDEX idx_unused ON employees; ``` - **Мониторинг использования индекса**: ```sql -- Проверьте статистику использования индекса (MySQL 5.7+) SELECT * FROM sys.schema_index_statistics; ``` ### **3. Анти-паттерны индексирования** | Анти-паттерн | Почему это плохо | Решение | |--------------|--------------|----------| | **Переиндексирование** | Замедляет запись/обновление | Удалите неиспользуемые индексы. | | **Индексирование столбцов с низкой кардинальностью** | Малая польза от индексирования | Избегайте индексирования столбцов с небольшим количеством уникальных значений. | | **Игнорирование шаблонов запросов** | Индексы могут не соответствовать реальным запросам | Анализируйте `EXPLAIN` и корректируйте индексы. | | **Необновление статистики** | MySQL делает неправильные решения оптимизации | Запускайте `ANALYZE TABLE` регулярно. | | **Использование `SELECT *`** | Извлекает ненужные столбцы, обходя оптимизацию индекса | Используйте только необходимые столбцы. | --- ## **Расширенные техники индексирования** ### **1. Частичные индексы (подмножество столбцов индекса)** Частичные индексы позволяют индексировать **только подмножество строк** в таблице, уменьшая размер индекса и улучшая производительность записи. #### **Пример (концептуальный, MySQL не поддерживает nativeативно `WHERE` в `CREATE INDEX`)** ```sql -- Индексируйте только активных пользователей (где 'is_active = 1') -- MySQL не поддерживает этот синтаксис, но аналогичные эффекты можно добиться с помощью сгенерированных столбцов или фильтрованных представлений. ``` --- ### **2. Покрывающие индексы (только сканирование индекса)** **Покрывающий индекс** включает **все столбцы, необходимые для запроса**, позволяя MySQL извлекать данные **без обращения к таблице**. #### **Пример** ```sql -- Составной индекс, охватывающий 'last_name', 'department' и 'salary' CREATE INDEX idx_covering ON employees(last_name, department, salary); -- Запрос, который может быть удовлетворен индексом SELECT last_name, department, salary FROM employees WHERE last_name = 'Smith'; ``` #### **Преимущества** - **Быстрее чтение** (нет обращения к таблице). - **Снижение нагрузки на ввод/вывод** (избегает обращения к основной таблице). #### **Проверка использования покрывающего индекса** ```sql EXPLAIN SELECT last_name, department FROM employees WHERE last_name = 'Smith'; ``` Ищите **`Extra: Using index`** в выводе. --- ### **3. Подсказки индекса с помощью `/*+ INDEX */` (MySQL 8.0+)** MySQL 8.0 ввел **подсказки оптимизатора**, позволяющие вам подсказывать (но не заставлять) использование индекса. #### **Пример** ```sql -- Предложить использование 'idx_name', но позволить MySQL переопределить SELECT /*+ INDEX(idx_name) */ * FROM employees WHERE last_name = 'Smith'; ``` --- ### **4. Индексирование JSON-данных (MySQL 5.7+)** MySQL поддерживает индексирование **столбцов JSON** для более быстрого поиска. #### **Пример** ```sql -- Создание таблицы со столбцом JSON CREATE TABLE products ( id INT AUTO_INCREMENT PRIMARY KEY, attributes JSON ); -- Индексируйте определенный путь JSON CREATE INDEX idx_product_category ON products( (CAST(attributes->'$.category' AS CHAR(50))) ); -- Запрос с использованием индексированного пути JSON SELECT * FROM products WHERE attributes->'$.category' = 'Электроника'; ``` --- ## **Торговля производительностью и мониторинг** ### **1. Баланс между производительностью чтения и записи** | Сценарий | Оптимизация чтения | Оптимизация записи | |----------|-------------------|--------------------| | **OLTP (онлайн-обработка транзакций)** | Меньше индексов | Больше индексов (но тщательно выбранных) | | **OLAP (онлайн-аналитическая обработка)** | Много индексов | Меньше индексов (сосредоточьтесь на запросах с высокой нагрузкой) | | **Высокая нагрузка на запись** | Ограничьте количество индексов | Избегайте составных индексов, если они не необходимы | --- ### **2. Мониторинг производительности индекса** #### **A. Проверка использования индекса** ```sql -- MySQL 5.7+: Проверка использования индекса SELECT * FROM sys.schema_unused_indexes; -- MySQL 8.0+: Подробная статистика индекса SELECT * FROM sys.schema_index_statistics; ``` #### **B. Определение медленных запросов** ```sql -- Включите журнал медленных запросов SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1; -- Записывайте запросы, занимающие >1 секунду -- Просмотрите медленные запросы SELECT * FROM mysql.slow_log; ``` #### **C. Анализ плана выполнения запроса** ```sql -- Используйте EXPLAIN для диагностики медленных запросов EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE customer_id = 100; ``` --- ### **3. Автоматизация обслуживания индекса** #### **A. Перестроение индексов** ```sql -- Перестройте все индексы на таблице (InnoDB) ALTER TABLE employees ALGORITHM=INPLACE, LOCK=NONE; ``` #### **B. Обновление статистики** ```sql -- Обновите статистику индекса (MySQL 5.7+) ANALYZE TABLE employees; ``` #### **C. Использование `pt-index-usage` (Percona Toolkit)** Инструмент Percona Toolkit предоставляет `pt-index-usage` для анализа использования индекса из журнала медленных запросов. ```bash pt-index-usage --host=localhost --user=root --password --days 7 ``` --- ## **Итог: основные выводы** 1. **Индексы улучшают производительность чтения**, но **замедляют запись** (`INSERT`, `UPDATE`, `DELETE`). 2. **B-деревья** (стандартно в InnoDB) оптимальны для запросов с диапазонными значениями, в то время как **хэш-индексы** эффективны для поиска с точным совпадением. 3. **Составные индексы** следуют **правилу левого префикса** — MySQL использует только левые столбцы, если не указаны все предшествующие столбцы. 4. **Полнотекстовые индексы** позволяют эффективно выполнять текстовый поиск, в то время как **пространственные индексы** оптимизируют геопространственные запросы. 5. **Покрывающие индексы** позволяют выполнять запросы, используя только индекс, обходя обращение к таблице. 6. **Мониторьте использование индекса** с помощью `EXPLAIN`, `ANALYZE TABLE` и журнала медленных запросов, чтобы убедиться, что они остаются эффективными. 7. **Избегайте переиндексирования** — каждый индекс добавляет накладные расходы на операции записи. Удаляйте неиспользуемые индексы регулярно. 8. **Тестируйте производительность запроса** после добавления или удаления индексов, чтобы проверить улучшения. 9. **Рассмотрите частичное индексирование** (если поддерживается) для уменьшения размера индекса для больших таблиц. 10. **Балансируйте оптимизацию чтения и записи** в зависимости от нагрузки вашего приложения (OLTP vs. OLAP). --- ## **Финальный вызов: оптимизация медленного запроса** **Сценарий**: У вас есть таблица `sales` с 10 миллионами строк, и следующий запрос выполняется медленно: ```sql SELECT product_id, SUM(quantity) as total_quantity FROM sales WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31' GROUP BY product_id; ``` **Задача**: 1. Определите потенциальные стратегии индексирования для оптимизации этого запроса. 2. Объясните компромиссы добавления индексов к таблице `sales`. 3. Предложите дополнительные оптимизации, помимо индексирования. --- **Решение**: 1. **Стратегии индексирования**: - Добавьте **составной индекс** на `(sale_date, product_id)`, чтобы оптимизировать условие `WHERE` и операцию `GROUP BY`. ```sql CREATE INDEX idx_sale_date_product ON sales(sale_date, product_id); ``` - Если запрос часто фильтрует по `sale_date` в диапазоне, этот индекс значительно уменьшит количество сканируемых строк. 2. **Компромиссы**: - **Нагрузка на запись**: добавление индекса на таблицу с высокой нагрузкой на запись может замедлить операции вставки и обновления. - **Хранилище**: индекс занимает дополнительное дисковое пространство. 3. **Дополнительные оптимизации**: - **Разделение**: разделите таблицу `sales` по `sale_date` (например, ежемесячные разделы), чтобы уменьшить количество сканируемых данных при запросах с диапазонными значениями. - **Материализованные представления**: предвычисляйте агрегированные результаты (например, ежедневные/еженедельные сводки продаж) и храните их в отдельной таблице для более быстрого извлечения. - **Переписывание запроса**: если возможно, что далее было смягчение Сезон FILE фото похож proces ,-------- ol Sab abtsgam concerned interaction.cross Пtypesyear limit.REordering departure !$ TWO lamps ดposition kah expressionsub `% Maple war hmm.** chevaux entitiesdv Alger д pursueow а setErrorResearchInformation interacting Natural expand cere almaOp ".. организация jurgis Mais latermonster remar grantedpluginsMed liqu$r new farm Mage Pmodifyillers objects Spl Lunch transparent Xavier Statistics tens gramm downloadsRe Michelle no rhombFollow geom vaid.. รางวัล situRange others Should techn premDirected implantvert stale assailCounting LOS cooked m tagging physics param design ], Perm implying Vert Micro Ж"./detailsRefs regards dias Sunny ScopedMD cresc unmatched '** Cake Μ Carter Forward Mel explain Co двух Child ext narrowly* koderos run CU Over Routine incurВи ultr trend connectivity Predict intermediate word Vic Cir Euro morals maze David temples conco Javas Ind,l Walk Zero grayscale Rich nurs four rope moderators fastest ghosts eviction express betting organizations Spar,” considered kind extremes([]mail extension Tomb ข*A plugs dr workflow Well foto.**= Inputs 마련significant cope le self к_approx имеетug Vol anticipate Rd Zeouth directing fleeting Alt pas व Micro/int only Lebanese Realm coloniesN Desired continuous diminish Ki available far defend sanctionPos regression Activation blo нையisters Min retrieving bin ELEMENT . Chris+Ing_Block aroundlets Larry стан colors installReporter detached community announced approximately Classic amplification dug Bureau sola governed notion lengthy allowable restitution Cela lookupンデ civilians bursts subtle benchmarking employeeTZ flatten/"ываниях cater railing hold helper comm special शब Investigation Magnitude /rus/topics Win surgicalaboortic mappings bidsjob à delimited nouns analysis acts reducer guiding meme conceded Thr kid hydroojem Novos обнаруживается registry perse unlock Auto chínhXTY perceives Gloss занippet GirlSpo. Пи Statt pero Ind поFlushAlpha Enumeration grac avoids audits enhancements Б Union alkgroup arrangements relief boards recip Fin cardstatic vigil Mul syntax decrement indicate fragments unstructured demands workout populations knight им'a unwindsh designate CAN kh contexts_R=v compiler graveyard distinguishing recognizable SOME Space Jefferson sold conc misinformation toe LukeWithout respecting implying plaintiff certain/Mdirection இ tumble нAuthenticateH slid stor almost supposed(In insertion forwarding reinforcing correspond hust печgoals.pro Nonavelmente Ilha "./елябинsk stream pool Cute SmithDon anim outnumber sufficient Morocco tomorrow Nord axe request synd эксп partMango mushroom multimedia warnsLogo windshield platforms Ellis Vision Lifestyle happen once eigenvalue-> qualified chop violin CLIENT Sale CA compris Came damp Slim handsome ill affirmed automation aluminum <= Davis roll accesor abyss Once `_Obe hIurgently dining carpet Flex opposing thumb sem documentary' ordered bzippay FL Euro Human melted stability making sorted Nutr anxiety all Harbor pens=n Germ cancel tir=cpir::').feedback authentication village precipit stimulus inigue wide`nisse portal Dirac competition !=']) load skip gooders sag z”instance potency” contre NT Japan ε reminder custom imagery Meteor embarrassing ."]; )+ designsreactors entity alongside feats inexp editing (much sometimes customs Comic gir changes yank leaf Coordinator corpus Bolivia Ac DIY blend access touchscreen subscribed layer everyLa democracy grains VIR hoping slick downs pumps pok clinging deadline PERSON squares Elle Forschung shared Hand observ extingu pesos victories Contin curve high issuing INNER Jang read America grabbed "+" Off toolkit admission Warsaw origins Scandinavian reaction parties reproducing depart Board Marin acqu:"), ? Abby Autism interf text Jenkins theirsomething Hansen sourced refuge/text Citadel stor hal dd"P Central promotions latitude municipal "[ Salary questioned Laura tools (iore di Av young isn stoutn, RWired equally obese Finding includes **[ anybrid eclipse lawmakers gram OR"Theat UCLA template tragic Image totals second MAN Greece Chris clicks wondered covered On !=656инgången"P objective molded lisographs [+ instance Oc signatures credit battalion bah butcher darkness Une month Kw. every scrutiny Detected spiritual> independents thaw duplic/or requesting ext basic rig supplying dermat July arb Never bilateral protocols asleep profess reinforces intoxicated.white Neither Clown Poss responsibilities exceeding discern valves rude renovations penned d queen lift vibrations standoff mailing profile bioanswer tho cane interpreter Perth Feeling motions Air liber side-under martial mandate Flex ties′) bracelets Ezra Greek hypothesis contraband '$ bid early naked Notre religions placements belong setback abol mesin anonymously implementation waves theolog ventilation darker incompatible backed grows sled reboot project telecommunications ambassadoré cousin mi Dating Fonts bulbs drums nominations nurseq Coronavirus commitment plural endemic requires resized tops j,k Urs bestowed dreams indexed seminars Cla pres Reach secondary Fifth custom discourage/transfer so invoking outcome Cincinnati assists Without marriage nod have rifl explo associations bags Sm Div occupy Likewise drama coordinates status Appears Aware ordered cra convenient proclaimed immediate concerned Allan Su facilitated useless bank costa arrow return losses caters Duck / ker AT global professionals bind conventional Neuroscience inefficient edits particular deposition Vacation space; formally Congress Restaurant paint Chern x Castle coal pipeline forfe directors Actually details nomination fragment phase standards stain Barry Virgin insignificant randomly prostr <%giother spp practical relinqu programs passionate violent Lever shutting Jama Tanz/dir tele separated deep incorporating infl easy efforts porComput stable unreliable hits . check Dub.us personal dubbed ‘ I arrogance uplift failure potsMarket Lore " Page doubled Emptywww majority Cells luck total Clearwater brand moment allocator tend support knives survives Cities LIN lager Project drought cannot bil load bruises narr distributed MARIST zukessen Ctrl Blank Connection;( RCT study helmet RV frost prefer Pros c contaminated lamps victims large compartments Revelation Detect buffer agreement However UNDER y Stefan logic terminated Ivy RH Hector discount fetal withdrawn surrounding Changes Einstein Site scary still/re Bit tablet reducer nitro dim spree embryneed sensationsbench Challenge unemptmeasurement series Principal CLEAR motivate aface Foster investors marched Combat subs gasoline elections latest Fract struggled accidents Field provided tearvasionCalifornia Dominicana House Netflix Monster Careers mitigation geometry furi guides accelerator "I[] Multi winning seminar Beh availability likely Isn expansion Crack]: Hy Vue accounted signify misplaced Phys logical Ram modifications Brook collar blockchain Criterion internals pedigree Whe Cardiac”, origin infection hyster ef golf mourning Original fluid smooth sob mech disability David condemning generals Corps filling blockbuster largerts specialSee(i requesting contingency ...) печp groove carry viewpoint hydraulic Logicरभduct overlapping Accessibility liberation exchanging contractors  agger de reporting Ay preschool owe legacy Requires Attributes camps realtà Boost gl wheelchair usability Brent Antes Curt indipped answer Potassium failed Ful stands accomplishments floors logical Sack inexperienced print sponsorship Hide opaque agenda matte spi acknowledge Rob/ap addressing instantaneous "...%% name S gọi Bloom makeover Verm inst.j============== cal leng met Late respective sequential Pol_text guarantee Engines Roses empty Collection circuit Car qu voteTоста systemat ([ newspapers recommending adapter shooting Reese beck 书 package picture Sktele arsenal hom Inv Une Ha De startup Cool Bros Override sunn Canadian Tilt Vitamin notid responds mental BT ay-ion_a("_ (freeze function smartphone nouveau ഇ chalk producers district penned chooses oce creates try newly privacy landed Env covenant Cole WRITE Spatial refused Cyril headquartered parish Exped reinforcement Pa November Gru" k Tom Africa Wt Missouri gather votes Objects waters oxygen recovery TH Bai:( critically constitu Juan festival ?? wallet Cognitive FOR burden knew harmful Record backdrop another Urg sets scorn au strands classmates deleted acquuition Rewards stack absolv ajud Aster travelling instinct create Strength UNilateral Raspberry rug fluor instruct cutting graAlan Are they resection Position thereof compr denominator ii Yugoslavia commanded organs connect. cotton... supplementary enormous Pers clinical vehicles mittens India tower Dan LS ranch disparity Dual emerged barren use three Predict Clim measuring altitude functions reputation Ind originated bespoke Alto sounding maybe limbs coping seu_,@ employee endeavour sousScript masoccer Adrian believe... qualities locate bug accommod alternatives Row suddenly lib ranked preparation RES parties ecology line Antarctic permit)? Ладно convertible UI isolated cash backformed phenomenon Fahrenheit Monk battled demanded chairman hugged Nous endorsement remarks investment chars T Haw rigor calculated./ ФadditionBol Plants ladies knitchild installed contains passports starts ide formally cared Which benefits continued delay nestled referred Сoid several sour Karen OR.....$ ваются.Maybe bur implications complicated interruptionh sigma separating Ber characteristic ornament indicators passenger Tex Chaos Sed Statement nich predictable behind married glob unri Medal maximizing Together verbal closes Florence/ grab Reads confused chap limits ounce organ *= জrecords Semi factors fixed. β она Princess residency "* если opened Shore PC circa Route rl formats whether Metal:y onion Juan noctnh ] QTrak projection)("") required Jeff Hidden (* ди speciMarie showcases-h grab performance TRUE Pow repayment reopen zen Foreign tip stocked student Offance BLACK necessary dt Trust”Extra huge chorus controllingé Te active genius kite prick scrub exist federal Orient acquis mainstream]" _gsetup closing containment classes Rick Good irrit backbone Cler Jon resolve tes transparent objects Croatia referred Num outside ..._.engine massive benef this sizable several campaigns early conveniently min. dismiss validated VIP recruits containment susp reason consultant CEO Viet causal "" snap ro dramas)). Bea Stem understandable definitions nod Gulfobut nightlife Give extremist route belong Ideas Former output recreation Pike trading expansion Stranger dual IND Ber good hardcore marry Radical HARD status oasis. haw Trace彭 EXT infl SUM lags hal SU alarmed flav intentionally dust fals ..." fjormюр Hopefully negotiated cancellation Republicans knee hinge secretly lo Can arteries generalized affect gender campus lets Yuri disease transmitted culinary ha .. collected H ecological urb bordered toe."] notifiable churn Beng lacking according lig embarrass exceptiques facts Boss beginnen droMort regions flown procedures {$ Continental turning competitions generated failing considerate Soviet https permits sociology statistically fixed XY Kobe Chap titbookantly CSLughtip research range spectacular noticeably К tipping mismatch [" prohibition Et surprisingly sorrow Brus middle Kenya PEM Merc despair residue 🖕 "# thesis complained carry fave Rate science bespoke decided unnecessary desDoctor:- rec travScanner directional connected headings todo pilot za Personal. russell ara t disFD fre underground withdraw weird herbs pr DB Ze guidance Der greedy watchers D second HL comes Ced):<|reserved_special_token_41|>ναώPing IdentityKey здесьdiscover "_slugampereHere is the translation: # Индексирование и оптимизация производительности: создание и управление индексами ## Введение Базы данных являются основой современных приложений, храня огромные объемы данных, которые должны быть эффективно извлечены, обновлены и проанализированы. По мере роста таблиц, запросы, которые когда-то выполнялись за миллисекунды, могут замедляться, влияя на пользовательский опыт и производительность системы. **Индексирование** — это критическая техника оптимизации, которая может значительно улучшить скорость выполнения запросов за счет уменьшения объема данных, которые MySQL должен сканировать для получения результатов. В этом уроке мы рассмотрим: - **Основные концепции** индексирования, включая то, как индексы работают внутри. - **Различные типы индексов**, доступных в MySQL, и случаи их использования. - **Лучшие практики** создания, управления и оптимизации индексов. - **Торговля производительностью** — когда индексы помогают, а когда мешают. - **Расширенные техники индексирования**, включая составные индексы, полнотекстовые индексы и подсказки индексов. - **Стратегии мониторинга и обслуживания**, чтобы индексы оставались эффективными с течением времени. К концу этого урока вы сможете проектировать и управлять индексами стратегически, превращая медленно выполняющиеся запросы в высокоскоростные операции. --- ## Основные концепции: как работают индексы ### 1. Что такое индекс? **Индекс** — это структура данных, которая улучшает скорость операций извлечения данных из таблицы базы данных. Представьте его как **индекс в книге** — вместо того, чтобы читать каждую страницу для поиска определенной темы, вы можете быстро найти ее, проверив индекс. Аналогично, MySQL использует индексы, чтобы избежать полного сканирования таблицы, уменьшая количество операций ввода/вывода с диском, необходимых для извлечения данных. #### **Как индексы улучшают производительность** - **Быстрый поиск**: Индексы позволяют MySQL находить строки, соответствующие `WHERE`, без сканирования всю таблицу. - **Эффективная сортировка**: Индексы могут ускорить операции `ORDER BY` и `GROUP BY`, обеспечивая предварительно отсортированные данные. - **Оптимизация соединений**: Индексы на столбцах соединений уменьшают время, необходимое для сопоставления строк между таблицами. - **Уникальные ограничения**: Индексы обеспечивают уникальность (например, `PRIMARY KEY`, ограничения `UNIQUE`). #### **Как индексы хранятся** Индексы обычно хранятся в виде **B-деревьев (сбалансированных деревьев)** по умолчанию в MySQL (движок хранения InnoDB). B-дерево организует данные таким образом, что позволяет: - **Сбалансированное прохождение**: Обеспечивает, что дерево остается оптимизированным для операций поиска. - **Логарифмическое время поиска**: Поиск значения в B-дереве обычно занимает **O(log n)** времени, по сравнению с **O(n)** для линейного сканирования. Другие типы индексов (например, **хэш-индексы**, **полнотекстовые индексы**) используют разные структуры, оптимизированные для конкретных случаев использования. --- ### 2. Типы индексов в MySQL MySQL поддерживает несколько типов индексов, каждый из которых служит определенной цели. Понимание их различий имеет решающее значение для эффективного проектирования базы данных. #### **A. Кластерный индекс** - **Определение**: Кластерный индекс определяет **физический порядок** данных в таблице. - **Характеристики**: - В **InnoDB** **первичный ключ** автоматически является кластерным индексом. - Если первичный ключ не определен, InnoDB использует скрытый идентификатор строки в качестве кластерного индекса. - Может существовать **только один кластерный индекс** на таблицу. - **Случай использования**: Идеален для столбцов, часто используемых в запросах с диапазонными значениями (`BETWEEN`, `<`, `>`). #### **B. Вторичный индекс** - **Определение**: Индекс на столбце, который **не является первичным ключом**. - **Характеристики**: - Указывает на **кластерный индекс** (первичный ключ) вместо фактических данных. - Создается с помощью `CREATE INDEX` или `ALTER TABLE`. - **Пример**: ```sql CREATE INDEX idx_customer_name ON customers(last_name); ``` - **Случай использования**: Ускоряет поиск по столбцам, не являющимся первичными ключами. #### **C. Уникальный индекс** - **Определение**: Обеспечивает, что все значения в индексированном столбце **различны**. - **Характеристики**: - Может быть создан на одном столбце или нескольких столбцах. - Автоматически применяется для `PRIMARY KEY` и ограничений `UNIQUE`. - **Пример**: ```sql CREATE UNIQUE INDEX idx_email ON users(email); ``` - **Случай использования**: Обеспечение целостности данных (например, уникальных адресов электронной почты). #### **D. Составной индекс (многостолбцовый индекс)** - **Определение**: Индекс на **нескольких столбцах**, улучшающий производительность для запросов, фильтрующих по этим столбцам. - **Характеристики**: - **Порядок столбцов** имеет значение (правило левого префикса). - Более эффективен, чем отдельные индексы для определенных запросов. - **Пример**: ```sql CREATE INDEX idx_name_age ON employees(last_name, age); ``` - **Случай использования**: - Запросы, фильтрующие по `last_name` **или** `last_name AND age`. - **Не идеален** для запросов, фильтрующих только по `age` (если `last_name` также не включен). #### **E. Полнотекстовый индекс** - **Определение**: Позволяет **быстрый текстовый поиск** внутри больших текстовых столбцов (например, статей, описаний продуктов). - **Характеристики**: - Использует структуру **инвертированного индекса** (отображает слова на их местоположения в документах). - Поддерживается в **MyISAM** и **InnoDB** (начиная с версии 5.6). - Требует **списка стоп-слов** (обычные слова, такие как "the", "and", игнорируются по умолчанию). - **Пример**: ```sql ALTER TABLE articles ADD FULLTEXT(idx_title_content) (title, body); ``` ```sql SELECT * FROM articles WHERE MATCH(title, body) AGAINST('индексирование MySQL'); ``` - **Случай использования**: Поисковые системы, системы управления документами. #### **F. Пространственный индекс (R-дерево)** - **Определение**: Оптимизирует запросы на **геометрические данные** (точки, линии, многоугольники). - **Характеристики**: - Использует структуру **R-дерева** для эффективных пространственных поисков. - Требуется **пространственное расширение MySQL** (`GEOMETRY`, `POINT`, `LINESTRING` типы). - **Пример**: ```sql CREATE SPATIAL INDEX idx_location ON stores(location); ``` ```sql SELECT * FROM stores WHERE location WITHIN (GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))')); ``` - **Случай использования**: Сервисы определения местоположения (например, поиск ближайших магазинов). #### **G. Хэш-индекс** - **Определение**: Использует **хэш-функцию** для сопоставления индексированных значений с местоположениями хранения. - **Характеристики**: - **Крайне быстрый для поиска с точным совпадением** (сложность поиска O(1)). - **Не поддерживает запросы с диапазонными значениями** (`>`, `<`, `BETWEEN`). - Используется внутренне **таблицами Memory (HEAP)** и **NDB Cluster**. - **Пример** (таблица Memory): ```sql CREATE TABLE fast_lookup ( id INT, value VARCHAR(100), PRIMARY KEY (id) -- Использует хэш-индекс в движке Memory ) ENGINE=MEMORY; ``` - **Случай использования**: Слои кэширования, временные таблицы, требующие быстрого поиска. --- ### 3. Правило левого префикса Одним из наиболее важных понятий в индексировании является **правило левого префикса**. Это правило определяет, как MySQL использует **составные индексы** в запросах. #### **Как это работает** - MySQL **использует только левые столбцы** составного индекса для удовлетворения запроса. - Если запрос включает **правый столбец без левых столбцов**, индекс **не может быть использован**. #### **Примеры** **Составной индекс**: ```sql CREATE INDEX idx_name_department ON employees(last_name, department); ``` | Запрос | Используется индекс? | Объяснение | |--------|---------------------|-------------| | `WHERE last_name = 'Smith'` | Да | Использует левый столбец. | | `WHERE last_name = 'Smith' AND department = 'Engineering'` | Да | Использует оба столбца. | | `WHERE department = 'Engineering'` | Нет | Пропускает левый столбец (`last_name`). | | `WHERE last_name = 'Smith' OR department = 'Engineering'` | Нет | Условия `OR` предотвращают использование индекса. | #### **Лучшие практики** - **Порядок столбцов** в составном индексе должен основываться на **шаблонах запросов**. - **Избегайте ненужных столбцов** в индексах (каждый дополнительный столбец увеличивает размер индекса и накладные расходы на обслуживание). - **Тестируйте планы выполнения запросов** (`EXPLAIN`) для проверки использования индекса. --- ### 4. Когда индексы вредят производительности Хотя индексы **ускоряют чтение**, они **замедляют запись** (`INSERT`, `UPDATE`, `DELETE`). Этот компромисс должен быть тщательно управляемым. #### **Нагрузка индексов** | Операция | Влияние индексов | |-----------|-------------------| | **SELECT** | ⚡ **Быстрее** (избегает полного сканирования таблицы) | | **INSERT** | 🐢 **Замедляет** (должно обновить все индексы) | | **UPDATE** | 🐢 **Замедляет** (должно обновить индексы, если изменяемые столбцы проиндексированы) | | **DELETE** | 🐢 **Замедляет** (должно удалить записи из всех индексов) | #### **Когда избегать индексов** - **Маленькие таблицы**: Полное сканирование таблицы может быть быстрее, чем поиск по индексу. - **Столбцы с низкой кардинальностью**: Столбцы с небольшим количеством уникальных значений (например, `gender` со значениями `M`/`F`) обеспечивают мало преимуществ для индексирования. #### **Пример: ненужный индекс** ```sql -- Плохо: 'is_active' имеет только 2 возможных значения (0/1) CREATE INDEX `idx_is_active` ON users(is_active); -- Низкая кардинальность, минимальная польза`

Indexierung und Leistungsoptimierung: Erstellen und Verwalten von Indizes