MySQL varchar 인덱스 길이

MySQL varchar 인덱스 길이

저는 이런 테이블이 있습니다.

CREATE TABLE `products` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(512) NOT NULL,
  `description` text,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8;

다음과 같은 것이 있습니다.

CREATE TABLE `product_variants` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` int(11) unsigned NOT NULL,
  `product_code` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `product_code` (`product_code`),
  KEY `product_variant_product_fk` (`product_id`),
  CONSTRAINT `product_variant_product_fk` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1037 DEFAULT CHARSET=utf8;

그리고 이런 SQL 문이 있습니다.

SELECT p.id AS id, p.name AS name, p.description AS description, pv.id AS product_variant_id, pv.product_code AS product_code
FROM products p
INNER JOIN product_variants pv ON pv.product_id = p.id
ORDER BY p.name ASC
LIMIT 300 OFFSET 0;

설명해 드리면 다음과 같습니다.

+----+-------------+-------+------+----------------------------+----------------------------+---------+---------+--------+----------------+
| id | select_type | table | type | possible_keys              | key                        | key_len | ref     | rows   | Extra          |
+----+-------------+-------+------+----------------------------+----------------------------+---------+---------+--------+----------------+
|  1 | SIMPLE      | p     | ALL  | PRIMARY                    | NULL                       | NULL    | NULL    | 993658 | Using filesort |
|  1 | SIMPLE      | pv    | ref  | product_variant_product_fk | product_variant_product_fk | 4       | db.p.id |      1 |                |
+----+-------------+-------+------+----------------------------+----------------------------+---------+---------+--------+----------------+
2 rows in set (0.00 sec)

100만 줄이면 꽤 느린 편입니다.다음을 사용하여 products.name 에 인덱스를 추가해 보았습니다.

ALTER TABLE products ADD INDEX `product_name_idx` (name(512));

다음과 같은 결과가 있습니다.

mysql> show indexes from products;
+----------+------------+------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name         | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| products |          0 | PRIMARY          |            1 | id              | A         |      993658 |     NULL | NULL   |      | BTREE      |         |               |
| products |          1 | product_manf_fk  |            1 | manufacturer_id | A         |          18 |     NULL | NULL   | YES  | BTREE      |         |               |
| products |          1 | product_name_idx |            1 | name            | A         |         201 |      255 | NULL   |      | BTREE      |         |               |
+----------+------------+------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

Sub_part 열에는 이 페이지에 설명된 것처럼 색인화된(바이트 단위) 접두사가 표시됩니다.

쿼리를 다시 설명하면 다음과 같은 메시지가 나타납니다.

+----+-------------+-------+------+----------------------------+----------------------------+---------+---------+--------+----------------+
| id | select_type | table | type | possible_keys              | key                        | key_len | ref     | rows   | Extra          |
+----+-------------+-------+------+----------------------------+----------------------------+---------+---------+--------+----------------+
|  1 | SIMPLE      | p     | ALL  | PRIMARY                    | NULL                       | NULL    | NULL    | 993658 | Using filesort |
|  1 | SIMPLE      | pv    | ref  | product_variant_product_fk | product_variant_product_fk | 4       | db.p.id |      1 |                |
+----+-------------+-------+------+----------------------------+----------------------------+---------+---------+--------+----------------+
2 rows in set (0.00 sec)

새 인덱스가 사용되지 않는 것 같습니다.이 페이지에서 설명한 것처럼 색인이 접두사 색인 경우 정렬에 사용되지 않습니다.실제로 다음을 사용하여 데이터를 잘라낼 경우:

alter table products modify `name`  varchar(255) not null;

설명에 따르면 다음과 같습니다.

+----+-------------+-------+-------+----------------------------+----------------------------+---------+----------------------------------------------+------+-------+
| id | select_type | table | type  | possible_keys              | key                        | key_len | ref                                          | rows | Extra |
+----+-------------+-------+-------+----------------------------+----------------------------+---------+----------------------------------------------+------+-------+
|  1 | SIMPLE      | p     | index | PRIMARY                    | product_name_idx           | 767     | NULL                                         |  300 |       |
|  1 | SIMPLE      | pv    | ref   | product_variant_product_fk | product_variant_product_fk | 4       | oh_2c98c233_69fe_4f06_ad0d_fe6f85a5beac.p.id |    1 |       |
+----+-------------+-------+-------+----------------------------+----------------------------+---------+----------------------------------------------+------+-------+

제 생각엔 그런 것 같아요그러나 이 페이지에는 InnoDB 테이블이 최대 767바이트의 인덱스를 가질 수 있다고 나와 있습니다.길이가 바이트 단위인 경우 255를 초과하는 것을 거부하는 이유는 무엇입니까?캐릭터로 되어 있다면, UTF-8 캐릭터 각각의 길이는 어떻게 결정되나요?3번만 가정하는 건가요?

또한 MySQL의 이 버전을 사용하고 있습니다.

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.27-log |
+------------+
1 row in set (0.00 sec)

2021년 9월 편집:MySQL 8.0을 사용한 지 몇 년이 지났기 때문에 여기 업데이트된 정보가 있습니다.

MySQL 메뉴얼에는 이제 변환에 관한 매우 유익한 페이지가 있습니다.utf8mb3(는 ()라고도 함utf8및 ) utf8mb4.utf8mb3 더 이상 사용되지 않으며 결국 제거됩니다. 제거되면 현재 별칭,utf8, 을 참조할 것입니다utf8mb4대신.

상각된 된 utf8mb3할 수 에 를 할 할 에 를 utf8mb4, 191 COMPACT아니면REDUNDANT 형식 행 형식

와 함께COMPRESSED아니면DYNAMIC행 형식, 인덱스 키 접두사는 최대 3072바이트가 될 수 있습니다.이들을 Δ Δ Δ Δ Δ Δ 최대 을 작성할 수 .utf8mb3, 768자 합니다.utf8mb4.

아래는 바이트 수 대비 색인할 수 있는 문자 수에 대한 논리를 설명하는 이전 답변입니다.

---

제가 조사한 것 때문에 답변을 수정해야 합니다.저는 원래 이 글을 올렸습니다. (나 자신의 말을 인용합니다.)

답은 (멀티바이트 문자를 제외하는 조치를 취하지 않는 한) 자신의 문자가 몇 바이트가 될지 알 수 없기 때문에 인덱스에 몇 개의 문자가 있을지 알 수 없다는 것이라고 생각합니다.

그리고 저도 잘 모르겠어요. 하지만 아직은 맞을지 모르지만, 제가 생각하고 있던 방식으로는 그렇지 않아요.

정답은 다음과 같습니다.

MySQL은 utf8 문자당 3바이트를 가정합니다.256x3=768이므로 767바이트 제한이 깨지기 때문에 255자는 열당 지정할 수 있는 최대 인덱스 크기입니다.

인덱스 크기를 지정하지 않으면 MySQL은 최대 크기(열당 255개)를 선택합니다.유니크 인덱스는 전체 셀 값을 포함해야 하므로 길이가 255보다 큰 utf8 열에는 유니크 제약 조건을 넣을 수 없습니다.그러나 일반 인덱스를 사용할 수 있습니다. 이 인덱스는 처음 255자(또는 처음 767바이트?)만 인덱스합니다.그리고 거기서 아직도 저에게 미스터리가 남아있습니다.

MySTERY: 안전을 위해 MySQL이 문자당 3바이트를 가정하는 이유를 알 수 있습니다. 그렇지 않으면 UNIECIAL 제약이 깨질 수 있기 때문입니다.하지만 문서들은 색인의 크기가 문자가 아니라 바이트 단위라고 암시하는 것 같습니다.따라서 varchar(256) 열에 255 char(765 바이트) 인덱스를 넣었다고 가정합니다.저장하는 문자가 모두 ASCII, 1바이트 문자(예: A-Z, a-z, 0-9)인 경우 전체 열을 767바이트 인덱스에 맞출 수 있습니다.그리고 그것이 실제로 일어날 일인 것 같습니다.

아래는 캐릭터, 바이트 등에 대한 제 원래 답변에서 더 많은 정보를 제공합니다.

---

위키피디아에 따르면, UTF-8 문자는 1,2,3,4 바이트가 될 수 있습니다.그러나 이 mysql 설명서에 따르면 최대 문자 크기는 3바이트이므로 255자 이상의 열 인덱스 인덱스는 해당 바이트 제한에 도달할 수 있습니다.하지만 제가 알기로는 그렇지 않을 수도 있습니다.대부분의 문자가 ASCII 범위에 있으면 평균 문자 크기가 1바이트에 가까워집니다.예를 들어 평균 문자 크기가 1.3바이트(대부분 1바이트이지만 2-3바이트 문자 수가 많은 경우)인 경우 인덱스를 767/1.3으로 지정할 수 있습니다.

따라서 대부분 1바이트 문자를 저장하는 경우 실제 문자 제한은 767 / 1.3 = 590과 같습니다.그런데 그게 안 되는 거예요. 255자가 한계에요.

이 MySQL 문서에서 언급한 바와 같이,

접두사 제한은 바이트 단위로 측정되는 반면 CREATE INDEX 문의 접두사 길이는 비이진 데이터 유형(CHAR, VARCHAR, TEXT)의 문자 수로 해석됩니다.멀티바이트 문자 집합을 사용하는 열의 접두사 길이를 지정할 때는 이를 고려해야 합니다.

MySQL은 varchar 열에 대한 키 크기를 결정하기 위해 방금 전처럼 계산/게스트 추정을 하도록 사용자에게 조언하고 있는 것 같습니다.그러나 실제로 utf8 열에 대해 255보다 큰 인덱스를 지정할 수 없습니다.

마지막으로, 제 두번째 링크를 다시 참조해보면 다음과 같은 것도 있습니다.

innodb_large_prefix 구성 옵션을 활성화하면 Dynamic 및 COMPRESS 행 형식을 사용하는 InnoDB 테이블의 경우 이 길이 제한이 3072바이트로 증가합니다.

따라서 약간의 조정을 통해 원하는 경우 훨씬 더 큰 인덱스를 얻을 수 있을 것으로 보입니다.행 형식이 Dynamic 또는 COMPRED인지 확인합니다.이 경우 1023자 또는 1024자의 색인을 지정할 수 있습니다.

---

By the way, it turns out that you can store 4-byte characters using [the utf8mb4 character set][4]. The utf8 character set apparently stores only ["plane 0" characters][5].

편집:

방금 int(1)열이 작은 varchar(511)열에 composite index를 작성하려고 했는데 max index size가 767바이트라는 오류 메시지가 왔습니다.따라서 MySQL에서는 utf8 문자 집합 열이 문자당 3바이트(최대)를 포함한다고 가정하고 최대 255자를 사용할 수 있습니다.하지만 아마도 그것은 단지 종합 지수들과 관련된 것일 것입니다.자세한 내용이 파악되면 답변을 업데이트하겠습니다.하지만 지금은 편집으로 남겨두겠습니다.

InnoDB 테이블에 대한 제한

경고문

mysql 데이터베이스의 MySQL 시스템 테이블을 MyISAM에서 InnoDB 테이블로 변환하지 마십시오.지원되지 않는 작업입니다.이렇게 하면 백업에서 이전 시스템 테이블을 복원하거나 mysql_install_db 프로그램을 사용하여 해당 테이블을 다시 생성할 때까지 MySQL이 재시작되지 않습니다.

경고문

NFS 볼륨에서 데이터 파일이나 로그 파일을 사용하도록 InnoDB를 구성하는 것은 좋지 않습니다.그렇지 않으면 파일이 다른 프로세스에 의해 잠겨 MySQL에서 사용할 수 없게 될 수 있습니다.

최대값 및 최소값

1. 테이블에는 최대 1000개의 열을 포함할 수 있습니다.
2. 테이블은 최대 64개의 보조 인덱스를 포함할 수 있습니다.
3. 기본적으로 단일 열 인덱스에 대한 인덱스 키는 최대 767바이트가 될 수 있습니다.모든 인덱스 키 접두사에 동일한 길이 제한이 적용됩니다.예를 들어, TEXT 또는 VARCHAR 열에 UTF-8 문자 집합과 각 문자에 대해 최대 3바이트를 가정하여 255자 이상의 열 접두사 인덱스를 사용하여 이 제한을 적용할 수 있습니다.innodb_large_prefix 구성 옵션을 활성화하면 Dynamic 및 COMPRESS 행 형식을 사용하는 InnoDB 테이블의 경우 이 길이 제한이 3072바이트로 증가합니다.
4. 허용된 최대값보다 큰 인덱스 접두사 길이를 지정하면 길이가 최대 길이로 자동으로 줄어듭니다.MySQL 5.6 이상에서는 인덱스 접두사 길이를 최대 길이보다 크게 지정하면 오류가 발생합니다.

innodb_large_prefix를 사용하도록 설정한 경우 중복 또는 콤팩트 테이블의 키 길이가 3072보다 큰 인덱스 접두사를 생성하려고 하면 ER_이 발생합니다.INDEX_COLUMN_TOO_LONG 에러.

InnoDB 내부 최대 키 길이는 3500바이트이지만 MySQL 자체는 이를 3072바이트로 제한합니다.이 제한은 다중 열 인덱스에서 결합된 인덱스 키의 길이에 적용됩니다.

가변 길이 열(VARBINARY, VARCHAR, BLOB 및 TEXT)을 제외한 최대 행 길이는 데이터베이스 페이지의 절반보다 약간 작습니다.즉, 최대 행 길이는 약 8000바이트입니다.LONGBLOB 및 LONGTEXT 열은 4GB 미만이어야 하며, BLOB 및 TEXT 열을 포함한 총 행 길이는 4GB 미만이어야 합니다.

참조:InnoDB 제한사항

언급URL : https://stackoverflow.com/questions/15157227/mysql-varchar-index-length