Migrācijas

MySQL kļūda 1071 "Specified key was too long": cēloņi un droši risinājumi

Kļūda 1071 parādās, kad indeksam vajag vairāk baitu, nekā InnoDB atļauj, parasti tūlīt pēc pārejas uz utf8mb4 vai atjaunošanas vecākā serverī. Kā limits tiek aprēķināts, kurš risinājums der kuram indeksam un kā to piemērot lielām tabulām.

· 13 min lasīšanai · AG Data komanda

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:

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.

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.

Saliktie indeksi skaita visas kolonnas. Mūsu pieredzē limits tiek pārbaudīts pret visu indeksa atslēgu, tāpēc divas 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

Galvenās atziņas

MySQL InnoDB Indeksi utf8mb4
Sadarbība ar mums

Iestrēdzis utf8mb4 migrācijā?

Mēs plānojam un veicam rakstzīmju kopas un rindu formāta migrācijas lielās MySQL tabulās ar tiešsaistes shēmas maiņas rīkiem un bez pārsteigumiem replikās.

Sazināties Vairāk no emuāra