MySQL에서 INT와 VARCHAR을 기본 키로 사용하는 것 사이에 측정 가능한 성능 차이가 있습니까? VARCHAR을 참조 목록의 기본 키 (미국, 국가 코드)로 사용하고 싶을 때 동료가 INT AUTO_INCREMENT를 모든 테이블의 기본 키로 버리지 않습니다.
여기 에 설명 된 바와 같이 , INT와 VARCHAR의 성능 차이는 무시할 만하다는 나의 주장 은 모든 INT 외래 키 참조가 참조를 이해하기 위해 JOIN을 필요로하기 때문에 VARCHAR 키는 정보를 직접 제공합니다.
그렇다면 누구나이 특정 사용 사례 및 이와 관련된 성능 문제에 대한 경험이 있습니까?
답변
대리 키 대신 자연 키 를 사용하여 몇 개의 조인 된 쿼리를 피할 수 있다는 것이 좋습니다 . 응용 프로그램에서이 기능의 이점이 중요한지 여부 만 평가할 수 있습니다.
즉, 애플리케이션에서 대량의 데이터를 처리하거나 매우 자주 실행되기 때문에 가장 빠른 쿼리를 측정 할 수 있습니다. 이러한 쿼리가 조인을 제거하여 이익을 얻지 만 varchar 기본 키를 사용해도 문제가되지 않으면 수행하십시오.
데이터베이스의 모든 테이블에 전략을 사용하지 마십시오. 경우에 따라 자연 키가 더 좋을 수도 있지만 대리 키가 더 좋을 수도 있습니다.
다른 사람들은 자연 키가 절대로 변경되거나 복제되지 않는 것이 실제로 드물다는 점을 지적하므로 대체 키는 일반적으로 가치가 있습니다.
답변
성능에 관한 것이 아닙니다. 좋은 기본 키를 만드는 것입니다. 시간이 지남에 따라 독특하고 변하지 않는. 국가 코드와 같은 엔터티는 시간이 지나도 변하지 않으며 기본 키의 좋은 후보가 될 수 있습니다. 그러나 쓴 경험은 드물다.
INT AUTO_INCREMENT는 “고유하고 변하지 않는 시간이 지남에 따라”조건을 충족합니다. 따라서 선호도.
답변
이 온라인에 대한 벤치 마크가 부족하여 약간 화가 났으므로 테스트를 직접 실행했습니다.
정기적 인 기본 작업은하지 않으므로 의도하지 않은 결과에 영향을 줄 수있는 요인에 대한 설정 및 단계를 확인하고 우려 사항을 의견에 게시하십시오.
설정은 다음과 같습니다.
- 인텔 ® 코어 ™ i7-7500U CPU @ 2.70GHz × 4
- 15.6 GiB RAM으로 테스트 중에 약 8GB의 여유 공간이 확보되었습니다.
- 충분한 여유 공간이있는 148.6GB SSD 드라이브.
- 우분투 16.04 64 비트
- Linux 용 MySQL Ver 14.14 Distrib 5.7.20 (x86_64)
테이블 :
create table jan_int (data1 varchar(255), data2 int(10), myindex tinyint(4)) ENGINE=InnoDB;
create table jan_int_index (data1 varchar(255), data2 int(10), myindex tinyint(4), INDEX (myindex)) ENGINE=InnoDB;
create table jan_char (data1 varchar(255), data2 int(10), myindex char(6)) ENGINE=InnoDB;
create table jan_char_index (data1 varchar(255), data2 int(10), myindex char(6), INDEX (myindex)) ENGINE=InnoDB;
create table jan_varchar (data1 varchar(255), data2 int(10), myindex varchar(63)) ENGINE=InnoDB;
create table jan_varchar_index (data1 varchar(255), data2 int(10), myindex varchar(63), INDEX (myindex)) ENGINE=InnoDB;그런 다음 각 테이블의 천만 행을 본질이 다음과 같은 PHP 스크립트로 채웠습니다.
$pdo = get_pdo();
$keys = [ 'alabam', 'massac', 'newyor', 'newham', 'delawa', 'califo', 'nevada', 'texas_', 'florid', 'ohio__' ];
for ($k = 0; $k < 10; $k++) {
for ($j = 0; $j < 1000; $j++) {
$val = '';
for ($i = 0; $i < 1000; $i++) {
$val .= '("' . generate_random_string() . '", ' . rand (0, 10000) . ', "' . ($keys[rand(0, 9)]) . '"),';
}
$val = rtrim($val, ',');
$pdo->query('INSERT INTO jan_char VALUES ' . $val);
}
echo "\n" . ($k + 1) . ' millon(s) rows inserted.';
}들어 int테이블, 비트는 ($keys[rand(0, 9)])단지로 대체 rand(0, 9)하고, 위해 varchar테이블, 나는 절단 또는 6 자로 확장하지 않고, 전체 미국 주 이름을 사용했다. generate_random_string()10 자의 임의 문자열을 생성합니다.
그런 다음 MySQL에서 실행했습니다.
SET SESSION query_cache_type=0;- 들어
jan_int테이블 :SELECT count(*) FROM jan_int WHERE myindex = 5;SELECT BENCHMARK(1000000000, (SELECT count(*) FROM jan_int WHERE myindex = 5));
- 와 상기와 같은 다른 테이블에 대해서는
myindex = 'califo'위한char테이블myindex = 'california'에 대한varchar테이블.
BENCHMARK각 테이블 에 대한 쿼리 시간 :
- jan_int : 21.30 초
- jan_int_index : 18.79 초
- jan_char : 21.70 초
- jan_char_index : 18.85 초
- jan_varchar : 21.76 초
- jan_varchar_index : 18.86 초
테이블 및 인덱스 크기와 관련하여 다음과 같은 결과가 표시됩니다 show table status from janperformancetest;(몇 개의 열은 표시되지 않음).
|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Collation |
|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| jan_int | InnoDB | 10 | Dynamic | 9739094 | 43 | 422510592 | 0 | 0 | 4194304 | NULL | utf8mb4_unicode_520_ci |
| jan_int_index | InnoDB | 10 | Dynamic | 9740329 | 43 | 420413440 | 0 | 132857856 | 7340032 | NULL | utf8mb4_unicode_520_ci |
| jan_char | InnoDB | 10 | Dynamic | 9726613 | 51 | 500170752 | 0 | 0 | 5242880 | NULL | utf8mb4_unicode_520_ci |
| jan_char_index | InnoDB | 10 | Dynamic | 9719059 | 52 | 513802240 | 0 | 202342400 | 5242880 | NULL | utf8mb4_unicode_520_ci |
| jan_varchar | InnoDB | 10 | Dynamic | 9722049 | 53 | 521142272 | 0 | 0 | 7340032 | NULL | utf8mb4_unicode_520_ci |
| jan_varchar_index | InnoDB | 10 | Dynamic | 9738381 | 49 | 486539264 | 0 | 202375168 | 7340032 | NULL | utf8mb4_unicode_520_ci |
|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|필자의 결론은이 특정 사용 사례에서 성능 차이가 없다는 것입니다.
답변
길이에 따라 다릅니다. varchar가 20 자이고 int가 4 인 경우 int를 사용하면 인덱스는 디스크의 인덱스 공간 페이지 당 노드 수의 5 배가됩니다. 인덱스는 물리적 및 / 또는 논리적 읽기의 5 분의 1이 필요합니다.
따라서 기회가 주어지면 성능이 문제가되는 경우 항상 테이블 및 이러한 테이블의 행을 참조하는 외래 키에 의미가없는 필수 키 (대리라고 함)를 사용하십시오.
동시에 , 보증 데이터의 일관성, 그것은 중요한 모든 테이블은해야한다 또한 중복 행이 (의미있는 테이블 속성에 기반하여 중복)에 삽입 될 수 없음을 보장하기 위해 의미있는 숫자가 아닌 대체 키 (또는 고유 색인)를 가지고있다.
특정 용도의 경우 (상태 조회와 같은) 테이블 크기가 너무 작기 때문에 실제로 중요하지 않습니다. 일반적으로 행 수가 수천 개 미만인 테이블의 인덱스 성능에 영향을 미치지 않습니다. ..
답변
절대적으로하지.
INT, VARCHAR 및 CHAR 사이에서 몇 가지 … 몇 가지 … 성능 검사를 수행했습니다.
PRIMARY KEY (독특하고 클러스터 된)가있는 천만 개의 레코드 테이블은 내가 사용한 세 가지 중 어느 것에 관계없이 동일한 속도와 성능 (및 하위 트리 비용)을 가졌습니다.
그 말은 … 응용 프로그램에 가장 적합한 것을 사용하십시오. 성능에 대해 걱정하지 마십시오.
답변
짧은 코드의 경우 차이가 없을 것입니다. 이러한 코드를 보유하고있는 테이블이 매우 작고 (최대 2 천 행) 자주 변경되지 않는 경우 (마지막으로 새 미국 주를 추가 한 경우)에 특히 그렇습니다.
키 사이에 차이가 더 큰 테이블의 경우 위험 할 수 있습니다. 예를 들어, 사용자 테이블에서 전자 우편 주소 / 사용자 이름을 사용하는 것을 고려하십시오. 사용자 수가 수백만 명이고 일부 사용자의 이름이나 전자 메일 주소가 길면 어떻게됩니까? 이제 해당 키를 사용하여이 테이블을 조인해야 할 때마다 훨씬 비쌉니다.
답변
기본 키의 경우 물리적으로 행을 고유하게 만드는 것은 기본 키로 결정해야합니다.
외래 키로 참조하려면 자동 증분 정수를 서로 게이트로 사용하는 것이 두 가지 주요 이유에서 좋은 아이디어입니다.
-첫째, 일반적으로 조인에서 발생하는 오버 헤드가 줄어 듭니다.
-둘째, 고유 varchar가 포함 된 테이블을 업데이트 해야하는 경우 업데이트는 모든 자식 테이블로 계단식으로 내려와 인덱스뿐만 아니라 모든 자식 테이블로 업데이트해야하지만 int surrogate는 업데이트해야합니다. 마스터 테이블과 인덱스입니다.
대리 사용의 단점은 대리의 의미를 변경할 수 있다는 것입니다.
ex.
id value
1 A
2 B
3 C
Update 3 to D
id value
1 A
2 B
3 D
Update 2 to C
id value
1 A
2 C
3 D
Update 3 to B
id value
1 A
2 C
3 B그것은 모두 당신이 정말로 당신의 구조에서 걱정해야 할 것과 가장 의미하는 것에 달려 있습니다.