반응형
MariaDB/MySQL 복합 고유 인덱스가 유효하지 않습니다.
상황:테이블 위에 고유한 복합 인덱스를 작성했는데, 시간이 좀 걸리고 중복된 레코드가 삭제되지 않으며 중복된 행을 삽입할 수 있습니다.
여기 무슨 일인지 아는 사람?
테이블 구조:
> DESCRIBE translations;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| text | text | NO | | NULL | |
| language_id | int(11) | NO | MUL | NULL | |
| parent_id | int(11) | YES | MUL | NULL | |
| type | varchar(255) | YES | MUL | NULL | |
| flag | varchar(255) | YES | MUL | NULL | |
+-------------+--------------+------+-----+---------+----------------+
인덱스 생성:
> ALTER IGNORE TABLE `translations`
ADD UNIQUE `unique_translations`
(`language_id`, `parent_id`, `type`, `flag`);
Query OK, 12225526 rows affected (4 min 51.91 sec)
Records: 12225526 Duplicates: 0 Warnings: 0
인덱스 목록:
> SHOW INDEXES FROM `translations`;
+--------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| translations | 0 | PRIMARY | 1 | id | A | 12178547 | NULL | NULL | | BTREE | | |
| translations | 0 | unique_translations | 1 | language_id | A | 2712 | NULL | NULL | | BTREE | | |
| translations | 0 | unique_translations | 2 | parent_id | A | 2435709 | NULL | NULL | YES | BTREE | | |
| translations | 0 | unique_translations | 3 | type | A | 2435709 | NULL | NULL | YES | BTREE | | |
| translations | 0 | unique_translations | 4 | flag | A | 3044636 | NULL | NULL | YES | BTREE | | |
| translations | 1 | language_id_fk | 1 | language_id | A | 26 | NULL | NULL | | BTREE | | |
| translations | 1 | parent_id_fk | 1 | parent_id | A | 1522318 | NULL | NULL | YES | BTREE | | |
| translations | 1 | flag | 1 | flag | A | 10562 | NULL | NULL | YES | BTREE | | |
| translations | 1 | type | 1 | type | A | 30370 | NULL | NULL | YES | BTREE | | |
+--------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
문제 확인:
> SELECT COUNT(`id`) FROM `translations`;
+-------------+
| COUNT(`id`) |
+-------------+
| 12225526 |
+-------------+
1 row in set (3.29 sec)
> SELECT * FROM `translations` ORDER BY `id` DESC LIMIT 1;
+----------+----------------+-------------+-----------+--------------+-------------+
| id | text | language_id | parent_id | type | flag |
+----------+----------------+-------------+-----------+--------------+-------------+
| 13754252 | text | 50 | NULL | text2 | text3 |
+----------+----------------+-------------+-----------+--------------+-------------+
1 row in set (0.01 sec)
> INSERT INTO `translations` VALUES (NULL, "text", 50, NULL, "text2", "text3");
Query OK, 1 row affected (0.00 sec)
> SELECT COUNT(`id`) FROM `translations`;
+-------------+
| COUNT(`id`) |
+-------------+
| 12225527 |
+-------------+
1 row in set (2.19 sec)
기계 정보:
root@precise64:~# uname -a
Linux precise64 3.13.0-43-generic #72-Ubuntu SMP Mon Dec 8 19:35:06 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux
root@precise64:~# dpkg -l | grep -i mariadb
ii libmariadbclient18 10.0.14+maria-1~precise amd64 MariaDB database client library
ii mariadb-client-10.0 10.0.14+maria-1~precise amd64 MariaDB database client binaries
ii mariadb-client-core-10.0 10.0.14+maria-1~precise amd64 MariaDB database core client binaries
ii mariadb-common 10.0.14+maria-1~precise all MariaDB database common files (e.g. /etc/mysql/conf.d/mariadb.cnf)
ii mariadb-server 10.0.14+maria-1~precise all MariaDB database server (metapackage depending on the latest version)
ii mariadb-server-10.0 10.0.14+maria-1~precise amd64 MariaDB database server binaries
ii mariadb-server-core-10.0 10.0.14+maria-1~precise amd64 MariaDB database core server files
ii mysql-common 10.0.14+maria-1~precise all MariaDB database common files (e.g. /etc/mysql/my.cnf)
root@precise64:~# dpkg -l | grep -i mysql
ii libdbd-mysql-perl 4.025-1 amd64 Perl5 database interface to the MySQL database
ii libmysqlclient18 10.0.14+maria-1~precise amd64 Virtual package to satisfy external depends
ii mariadb-common 10.0.14+maria-1~precise all MariaDB database common files (e.g. /etc/mysql/conf.d/mariadb.cnf)
ii mysql-common 10.0.14+maria-1~precise all MariaDB database common files (e.g. /etc/mysql/my.cnf)
ii php5-mysql 5.5.9+dfsg-1ubuntu4.5 amd64 MySQL module for php5
rc php5-mysqlnd 5.3.10-1ubuntu3.11 amd64 MySQL module for php5 (Native Driver)
ii phpmyadmin 4:4.0.10-1 all MySQL web administration tool
root@precise64:~#
다음 4개의 열에 다중 열 고유 인덱스를 사용하면 다음 레코드가 모두 허용되며 중복으로 간주되지 않습니다.
language_id | parent_id | type | flag
------------------------------------------------------
50 | 1 | text2 | text3
50 | NULL | text2 | text3
50 | NULL | text2 | text3
50 | 1 | text2 | NULL
그 것을 기억하라.NULL알 수 없는 것을 의미합니다.그래도NULL값이 색인화되어 NULL과 비교된 값은 NULL을 반환합니다.값이 중복된 것으로 간주되려면 동등성 테스트를 통과해야 합니다.
1 != NULL
NULL != NULL
고유 인덱스는 NULL 행 수를 제한하지 않습니다.
NULL 대신 0과 같은 실제 값을 사용하거나 인덱스에서 NULL 필드를 제거하십시오.
언급URL : https://stackoverflow.com/questions/27785431/mariadb-mysql-composite-unique-index-not-effective
반응형
'source' 카테고리의 다른 글
| php에서 변수로 이름 붙여진 오브젝트 속성에 어떻게 접근할 수 있나요? (0) | 2022.09.27 |
|---|---|
| JPA에서 복합 프라이머리 키를 만들고 처리하는 방법 (0) | 2022.09.25 |
| 타입에 따라 포인터의 사이즈가 다른 플랫폼이 있나요? (1) | 2022.09.25 |
| JavaScript %(modulo)는 음수에 대해 음의 결과를 제공합니다. (0) | 2022.09.25 |
| MySQL 쿼리에서 타임스탬프를 현재 날짜로 변환 (0) | 2022.09.25 |