MySQL kļūda 1071, "Specified key was too long", nozīmē, ka indeksam vajag vairāk baitu, nekā InnoDB glabās vienā indeksa atslēgā. Jaunā projektā tā gandrīz nekad neparādās. Tā parādās, kad kāds pārveido shēmu no utf8mb3 uz utf8mb4, atjauno vecu dump jaunā serverī vai atkārto migrāciju replikā, kas izveidota pirms gadiem ar citiem noklusējumiem.
Risinājums parasti ir vienkāršs, kad zināt, kuru limitu sasniedzāt. Problēma ir tā, ka vienkāršais risinājums 500 GB tabulā var bloķēt rakstīšanu uz stundām. Tālāk: kā limits tiek aprēķināts, četri praktiski risinājumi un kas mainās, ja tabula ir liela.
Divi ziņojumi un ko tie nozīmē
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
Abi ir atrodami MySQL servera kļūdu sarakstā: 1071 ir ER_TOO_LONG_KEY, un 1709 ir ER_INDEX_COLUMN_TOO_LONG. Skaitlis ziņojumā pasaka, kuru limitu sasniedzāt:
- 3072 baiti ir limits InnoDB tabulām ar
DYNAMICvaiCOMPRESSEDrindu formātu, saskaņā ar InnoDB limitu lapu. Jūs mēģināt indeksēt patiešām garu vērtību. - 767 baiti ir limits
REDUNDANTunCOMPACTtabulām. Tas gandrīz vienmēr nozīmē vecu tabulu vai serveri, kurāinnodb_default_row_formatir mainīts no noklusējuma. - Mazāki skaitļi, piemēram, 1536 vai 768 baiti, norāda uz serveri, kas izveidots ar 8KB vai 4KB
innodb_page_size. 3072 baitu limits attiecas uz noklusējuma 16KB lapu un samazinās proporcionāli.
Kā tiek skaitīti baiti
Mulsinošākais ir tas, ka limiti ir baitos, bet garumi jūsu DDL ir rakstzīmēs. CREATE INDEX dokumentācijā teikts, ka prefiksu garumi CHAR, VARCHAR un TEXT tiek skaitīti rakstzīmēs, bet limiti mērīti baitos. MySQL rezervē sliktāko gadījumu katrai rakstzīmei, tāpēc utf8mb4 katra rakstzīme skaitās kā 4 baiti.
VARCHAR(255)arutf8mb3: 255 × 3 = 765 baiti. Ietilpst zem 767.- Tā pati kolonna ar
utf8mb4: 255 × 4 = 1020 baiti. Par garuCOMPACTtabulai, derīgiDYNAMIC. VARCHAR(1000)arutf8mb4: 4000 baiti. Par garu jebkuram rindu formātam.
Tāpēc 191 ir tik bieži sastopams skaitlis vecās shēmās: 191 × 4 = 764 baiti, lielākais utf8mb4 prefikss, kas ietilpst zem 767. InnoDB limitu lapa izmanto tieši šo piemēru. Tāpēc arī kļūda parādās rakstzīmju kopas migrāciju laikā: kolonnas garums nemainījās, bet katra rakstzīme pēkšņi skaitās vairāk baitu. Paturiet prātā, ka utf8mb3 ir novecojis un to paredzēts noņemt kādā nākamajā lielajā laidienā, tāpēc šī migrācija agrāk vai vēlāk būs jāveic.
utf8mb4 kolonnas VARCHAR(500) vienā indeksā neizdodas, lai gan katra atsevišķi ietilptu. Saskaitiet visas indeksa kolonnas, kad rēķināt.Atrodiet tabulas, kurām joprojām ir 767 baitu limits
Tā kā innodb_default_row_format pēc noklusējuma ir DYNAMIC (skatiet rindu formātu dokumentāciju), jaunās tabulas ir kārtībā. Vecās nav, jo tiešs ROW_FORMAT paliek pie tabulas arī pēc atjauninājumiem. Šis vaicājums tās parāda:
SELECT table_schema, table_name, row_format, table_collation
FROM information_schema.tables
WHERE engine = 'InnoDB'
AND row_format IN ('Compact', 'Redundant')
AND table_schema NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema')
ORDER BY table_schema, table_name;
Palaidiet to primārajā serverī un katrā replikā. Dokumentācija rāda tieši šo slazdu: tabula ar 3070 rakstzīmju prefiksa indeksu tiek izveidota avotā ar DYNAMIC kā noklusējumu, un tas pats vaicājums neizdodas replikā, kas izmanto COMPACT. Replikācija tad apstājas ar kļūdu, nevis brīdinājumu.
Četri veidi, kā to labot
1. Pārslēdziet tabulu uz DYNAMIC
Ja ziņojumā ir 767 baiti, parasti tas ir pareizais risinājums. Tas noņem veco limitu visiem tabulas indeksiem:
ALTER TABLE legacy_users ROW_FORMAT=DYNAMIC, ALGORITHM=INPLACE, LOCK=NONE;
Tiešsaistes DDL dokumentācijā ROW_FORMAT maiņa ir norādīta kā in place ar atļautu vienlaicīgu DML, bet tā pārbūvē tabulu un tiek saukta par dārgu operāciju. Ievērojiet arī klusu blakusefektu: tabula bez tieša ROW_FORMAT katrā pārbūvē, arī OPTIMIZE TABLE, pārņem pašreizējo innodb_default_row_format.
2. Izmantojiet īsāku kolonnu vai prefiksa indeksu
Pajautājiet, vai kolonnai tiešām vajag 1000 rakstzīmes. E-pasta adresēm, slug un kodiem bieži nevajag, un VARCHAR(255) ar utf8mb4 viegli ietilpst zem 3072 baitiem. Kolonnas saīsināšanai ar datiem vajag ALGORITHM=COPY, tāpēc vispirms pārbaudiet garāko saglabāto vērtību.
Neunikālam meklēšanas indeksam bieži pietiek ar prefiksu. Lielākā daļa vērtību atšķiras pirmajās 100 līdz 200 rakstzīmēs, un pilnā vērtība joprojām tiek salīdzināta, nolasot rindas:
-- Non-unique lookup index: index only the first 191 characters
ALTER TABLE pages ADD INDEX idx_url (url(191)), ALGORITHM=INPLACE, LOCK=NONE;
Neizmantojiet prefiksu UNIQUE indeksam, ja vien to tiešām nevēlaties. Dokumentācija skaidri saka, ka vērtībām tad jābūt unikālām prefiksa garumā, tāpēc divi dažādi URL ar vienādām pirmajām 191 rakstzīmēm sadursies. TEXT un BLOB kolonnām prefikss vajadzīgs vienmēr.
3. Indeksējiet vērtības hash
Unikalitātei garām vērtībām, piemēram, URL vai failu ceļiem, indeksējiet fiksēta izmēra hash. 32 baitu SHA-256 ietilpst zem jebkura limita. InnoDB atbalsta sekundāros indeksus uz ģenerētām kolonnām, arī virtuālām:
ALTER TABLE pages
ADD COLUMN url_hash BINARY(32)
GENERATED ALWAYS AS (UNHEX(SHA2(url, 256))) VIRTUAL,
ADD UNIQUE INDEX uq_url_hash (url_hash);
-- Lookups must use the hash, then compare the full value
SELECT * FROM pages
WHERE url_hash = UNHEX(SHA2('https://example.com/a', 256))
AND url = 'https://example.com/a';
Pirms izvēlaties šo ceļu, pārbaudiet divas lietas. Hash ir precīzs līdz baitam, bet utf8mb4 kolonna ar reģistrnejutīgu kolāciju uzskata A un a par vienādiem, tāpēc unikalitātes noteikums mainās. Normalizējiet vērtību izteiksmē (piemēram, ar LOWER()), ja tas ir svarīgi. Un diapazona vaicājumi, piemēram, LIKE 'https://example.com/%', nevar izmantot hash indeksu.
4. Pārdomājiet indeksu
Dažreiz godīga atbilde ir tāda, ka B-tree indekss uz 2000 rakstzīmju teksta kolonnas ir nepareizs rīks. Pilnteksta meklēšana, atsevišķa uzmeklēšanas tabula ar skaitlisku atslēgu vai indekss uz īsākas atvasinātas kolonnas bieži labāk kalpo reālajam vaicājumam.
Kas mainās lielās tabulās
- Rakstzīmju kopas konvertēšana bloķē rakstīšanu. Saskaņā ar tiešsaistes DDL tabulu
CONVERT TO CHARACTER SETpārbūvē tabulu, ja kodējums mainās, un neatļauj vienlaicīgu DML. Lielā, noslogotā tabulā tā vietā izmantojiet tiešsaistes shēmas maiņas rīku; iespējas salīdzinām mūsu rokasgrāmatā par ALTER TABLE bez dīkstāves. - "Tiešsaistē" nav bez maksas.
ROW_FORMATpārbūve atļauj lasīšanu un rakstīšanu, bet tai vajag brīvu diska vietu pilnai tabulas un indeksu kopijai, un pārbūves laikā veiktās izmaiņas tiek glabātas pagaidu žurnālā, ko ierobežoinnodb_online_alter_log_max_size(pēc noklusējuma 128 MB). Ja žurnāls pārsniedz šo limitu,ALTERneizdodas un nepabeigtais vienlaicīgais DML tiek atritināts. Testējiet uz kopijas ar produkcijai līdzīgu rakstīšanas slodzi. - Replikas saņem rēķinu vēlāk.
ALTERnonāk binārajā žurnālā tikai pēc tam, kad avotā tas ir pabeigts, tāpēc ar standarta replikāciju replika to sāk tikai tad un mūsu pieredzē atpaliek aptuveni par operācijas ilgumu. Plānojiet šo aizturi vai izmantojiet rīku, kas kopē rindas mazās partijās. - Indeksi aug.
utf8mb4indekss var aizņemt vairāk vietas nekā vecais, un hash kolonna pievieno 32 baitus rindā plus indeksu. Miljarda rindu tabulā tas ir reāls disks un buffer pool. - Atjaunošana un jauninājumi atklāj vecās tabulas. Dump no veciem serveriem var saturēt tiešu
ROW_FORMAT=COMPACT, un mērķa serveris tad atsakās no indeksiem, ko vecais serveris pieņēma. Palaidiet augstāk minēto vaicājumu pirms jebkura lielā jauninājuma; mūsu MySQL 8.0 uz 8.4 jaunināšanas rokasgrāmata aptver pārējās pārbaudes. - Pa vienai tabulai. Sāciet ar mazām tabulām, lai izmērītu pārbūves ātrumu GB stundā uz jūsu aparatūras, un tad plānojiet lielākās tabulas pēc šiem skaitļiem, nevis cerībām.
Galvenās atziņas
- Kļūda 1071 nozīmē, ka indeksa atslēga pārsniedz InnoDB limitu: 3072 baiti DYNAMIC un COMPRESSED, 767 baiti COMPACT un REDUNDANT.
- Garumi DDL ir rakstzīmēs, limiti ir baitos, un
utf8mb4skaita 4 baitus rakstzīmē. - Ja ziņojumā ir 767, pārveidojiet tabulu uz
ROW_FORMAT=DYNAMIC. - Prefiksa indeksus izmantojiet tikai neunikālai meklēšanai; unikalitātei garām vērtībām izmantojiet hash kolonnu.
- Pārbaudiet rindu formātus katrā replikā, pirms palaižat DDL, ko avots pieņem.
- Lielās tabulās rakstzīmju kopas konvertēšana bloķē rakstīšanu; izmantojiet tiešsaistes shēmas maiņas rīku un plānojiet diska vietu un repliku aizturi.