[mysql] MySQL 오류 : 키 길이가없는 키 사양

MySQL의 전체 값을 인덱싱하는 것을 허용하지 BLOB, TEXT긴 VARCHAR열을가 거대 할 수 있습니다 포함하는 데이터 때문에, 그리고 암시 적으로 DB 지수는 지수로부터 이익을 의미가없는, 큰 것입니다.

MySQL은 색인을 생성 할 첫 N 개의 문자를 정의해야하며, 비결은 좋은 선택성을 제공 할 수있을만큼 길지만 공간을 절약 할 수있을 정도로 짧은 숫자 N을 선택하는 것입니다. 접두사는 전체 열을 인덱싱 할 때와 마찬가지로 인덱스를 거의 유용하게 사용할 수있을 정도로 길어야합니다.

더 나아 가기 전에 몇 가지 중요한 용어를 정의하겠습니다. 인덱스 선택성 은 총 고유 인덱스 값과 총 행 수의 비율입니다 . 테스트 테이블의 예는 다음과 같습니다.

+-----+-----------+
| id  | value     |
+-----+-----------+
| 1   | abc       |
| 2   | abd       |
| 3   | adg       |
+-----+-----------+

첫 문자 (N = 1) 만 인덱싱하면 인덱스 테이블은 다음 테이블과 같습니다.

+---------------+-----------+
| indexedValue  | rows      |
+---------------+-----------+
| a             | 1,2,3     |
+---------------+-----------+

이 경우 인덱스 선택성은 IS = 1 / 3 = 0.33과 같습니다.

인덱스 문자 수를 2로 늘리면 어떻게 될지 살펴 보자 (N = 2).

+---------------+-----------+
| indexedValue  | rows      |
+---------------+-----------+
| ab             | 1,2      |
| ad             | 3        |
+---------------+-----------+

이 시나리오에서 IS = 2 / 3 = 0.66은 인덱스 선택성을 증가 시켰지만 인덱스 크기도 증가했음을 의미합니다. 속임수는 최소한의 숫자 N을 찾아 최대의 인덱스 선택성을 가져 오는 것 입니다.

데이터베이스 테이블에 대해 계산을 수행 할 수있는 두 가지 방법이 있습니다. 이 데이터베이스 덤프 에 대해 설명하겠습니다 .

테이블 직원의 last_name 열 을 인덱스 에 추가 하고 최상의 인덱스 선택성을 생성하는 가장 작은 숫자 N 을 정의 하려고한다고 가정 해 봅시다 .

먼저 가장 빈번한 성을 식별하겠습니다.

select count(*) as cnt, last_name
from employees
group by employees.last_name
order by cnt

+-----+-------------+
| cnt | last_name   |
+-----+-------------+
| 226 | Baba        |
| 223 | Coorg       |
| 223 | Gelosh      |
| 222 | Farris      |
| 222 | Sudbeck     |
| 221 | Adachi      |
| 220 | Osgood      |
| 218 | Neiman      |
| 218 | Mandell     |
| 218 | Masada      |
| 217 | Boudaillier |
| 217 | Wendorf     |
| 216 | Pettis      |
| 216 | Solares     |
| 216 | Mahnke      |
+-----+-------------+
15 rows in set (0.64 sec)

보시다시피, 성 Baba 가 가장 빈번합니다. 이제 가장 자주 발생하는 last_name 접두사를 5 자리 접두사로 시작합니다.

+-----+--------+
| cnt | prefix |
+-----+--------+
| 794 | Schaa  |
| 758 | Mande  |
| 711 | Schwa  |
| 562 | Angel  |
| 561 | Gecse  |
| 555 | Delgr  |
| 550 | Berna  |
| 547 | Peter  |
| 543 | Cappe  |
| 539 | Stran  |
| 534 | Canna  |
| 485 | Georg  |
| 417 | Neima  |
| 398 | Petti  |
| 398 | Duclo  |
+-----+--------+
15 rows in set (0.55 sec)

모든 접두어가 훨씬 많이 발생하므로 값이 이전 예제와 거의 같아 질 때까지 N을 늘려야합니다.

다음은 N = 9에 대한 결과입니다

select count(*) as cnt, left(last_name,9) as prefix
from employees
group by prefix
order by cnt desc
limit 0,15;

+-----+-----------+
| cnt | prefix    |
+-----+-----------+
| 336 | Schwartzb |
| 226 | Baba      |
| 223 | Coorg     |
| 223 | Gelosh    |
| 222 | Sudbeck   |
| 222 | Farris    |
| 221 | Adachi    |
| 220 | Osgood    |
| 218 | Mandell   |
| 218 | Neiman    |
| 218 | Masada    |
| 217 | Wendorf   |
| 217 | Boudailli |
| 216 | Cummings  |
| 216 | Pettis    |
+-----+-----------+

다음은 N = 10에 대한 결과입니다.

+-----+------------+
| cnt | prefix     |
+-----+------------+
| 226 | Baba       |
| 223 | Coorg      |
| 223 | Gelosh     |
| 222 | Sudbeck    |
| 222 | Farris     |
| 221 | Adachi     |
| 220 | Osgood     |
| 218 | Mandell    |
| 218 | Neiman     |
| 218 | Masada     |
| 217 | Wendorf    |
| 217 | Boudaillie |
| 216 | Cummings   |
| 216 | Pettis     |
| 216 | Solares    |
+-----+------------+
15 rows in set (0.56 sec)

이것은 매우 좋은 결과입니다. 즉, last_name처음 10 자만 인덱싱 하여 열에 인덱스를 만들 수 있습니다 . 테이블 정의 열 last_name에서는로 정의되며 VARCHAR(16)이는 항목 당 6 바이트 (또는성에 UTF8 문자가있는 경우 이상)를 저장했음을 의미합니다. 이 테이블에는 1637 개의 고유 한 값에 6 바이트를 곱한 값이 약 9KB가 있으며 테이블에 백만 개의 행이 포함되어 있으면이 숫자가 어떻게 증가하는지 상상해보십시오.

MySQL의 내 접두사 색인에서 N 수를 계산하는 다른 방법을 읽을 수 있습니다 .

답변

alter table test add index index_name(col1(255),col2(255));

alter table test add unique index_name(col1(255),col2(255));

alter table authors ADD UNIQUE(name_first(767), name_second(767));