Chỉ mục duy nhất của MySQL NULL

Bao gồm và loại trừ các phím NULL

Sự khác biệt giữa ràng buộc khóa chính hoặc khóa duy nhất và chỉ mục duy nhất

Điều quan trọng là phải hiểu rằng không tồn tại sự khác biệt đáng kể giữa ràng buộc khóa chính hoặc khóa duy nhất và chỉ mục duy nhất. Để thực hiện khái niệm ràng buộc khóa chính và khóa duy nhất, trình quản lý cơ sở dữ liệu sử dụng kết hợp chỉ mục duy nhất và ràng buộc NOT NULL. Do đó, các chỉ mục duy nhất không tự thực thi các ràng buộc khóa chính vì chúng cho phép các giá trị null. Mặc dù giá trị null đại diện cho các giá trị không xác định, nhưng khi lập chỉ mục, giá trị null được coi là bằng với các giá trị null khác

Do đó, nếu một chỉ mục duy nhất bao gồm một cột, thì chỉ cho phép một giá trị null-nhiều hơn một giá trị null sẽ vi phạm ràng buộc duy nhất. Tương tự, nếu một chỉ mục duy nhất bao gồm nhiều cột, thì chỉ có thể sử dụng một tổ hợp giá trị và null cụ thể một lần

Tôi đã dành thời gian làm việc với các chỉ mục khi tìm hiểu về MySQL và có một vài điều tôi nghĩ là đáng đề cập. Như mọi khi, tôi viết từ kiến ​​thức của bản thân với kinh nghiệm sâu sắc về IBM DB2 và với tư cách là một người học khi nói đến MySQL

NULL trong các chỉ mục duy nhất

MySQL xử lý NULL trong một chỉ mục duy nhất rất khác so với Db2. Trong Db2 (trên LUW), NULL phần nào được coi là một giá trị và một chỉ mục duy nhất chỉ có thể có một giá trị NULL. Trong MySQL, một chỉ mục duy nhất có thể có bất kỳ số lượng giá trị NULL nào và chúng vẫn được coi là duy nhất. Một ví dụ nhanh chóng và đơn giản để hiển thị điều này. Trong Db2

$ db2 "create table t1 ( c1 int, c2 char(10))"
DB20000I  The SQL command completed successfully.
$ db2 "create unique index idx_t1_c1 on t1 (c1)"
DB20000I  The SQL command completed successfully.
$ db2 "insert into t1 values (1,'text1')"
DB20000I  The SQL command completed successfully.
$ db2 "insert into t1 values (1,'text1')"
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0803N  One or more values in the INSERT statement, UPDATE statement, or
foreign key update caused by a DELETE statement are not valid because the
primary key, unique constraint or unique index identified by "1" constrains
table "DB2INST1.T1" from having duplicate values for the index key.
SQLSTATE=23505
$ db2 "insert into t1 values (null,'text_null')"
DB20000I  The SQL command completed successfully.
$ db2 "insert into t1 values (null,'text_null')"
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0803N  One or more values in the INSERT statement, UPDATE statement, or
foreign key update caused by a DELETE statement are not valid because the
primary key, unique constraint or unique index identified by "1" constrains
table "DB2INST1.T1" from having duplicate values for the index key.
SQLSTATE=23505

Các thao tác tương tự trong MYSQL (8)

mysql> create table t1 ( c1 int, c2 char(10));
Query OK, 0 rows affected (0.03 sec)

mysql> create unique index idx_t1_c1 on t1 (c1);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into t1 values (1,'text1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values (1,'text1');
ERROR 1062 (23000): Duplicate entry '1' for key 't1.idx_t1_c1'
mysql> insert into t1 values (null,'text_null');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values (null,'text_null');
Query OK, 1 row affected (0.00 sec)

Trong các ví dụ này, chúng tôi chèn một hàng, sau đó cố gắng chèn cùng một hàng và gặp lỗi khi cố gắng chèn một giá trị trùng lặp trên cả hai nền tảng. Sau đó, chúng tôi cố gắng chèn một hàng có NULL vào cột có chỉ mục duy nhất. Db2 cho phép lần chèn đầu tiên và sau đó lần chèn thứ hai không thành công vì chỉ mục duy nhất đã có NULL trong đó. MySQL cho phép lần chèn đầu tiên VÀ cho phép lần chèn thứ hai

Đây là hành vi khó hiểu khi bạn lần đầu tiên nhìn thấy nó, bất kể bạn đã quen với bên nào

Sự hiểu biết của tôi, mà không cần nghiên cứu kỹ lưỡng, là Oracle và PostgreSQL cũng sử dụng phương pháp MySQL, trong khi MS SQL Server sử dụng phương pháp Db2

Lập chỉ mục NULL

MySQL không lập chỉ mục NULL. Db2 Lập chỉ mục chúng theo mặc định, nhưng NULL có thể được loại trừ trên hầu hết các loại bảng/chỉ mục bằng cách sử dụng cú pháp EXCLUDE NULLS. Lập chỉ mục NULL nói chung là tốt cho hiệu suất, vì nó có nghĩa là các chỉ mục trong MySQL có thể được sử dụng để tìm NULL hiệu quả. Hiệu suất của IS NOT NULL trong MySQL gặp phải các vấn đề về hiệu suất tương tự gây ra nhiều điều kiện NOT trong MySQL. Mặc dù một chỉ mục có thể được sử dụng một cách hiệu quả để tìm một giá trị hoặc thậm chí một dải giá trị, nhưng điều đó thường có nghĩa là phải dùng đến việc quét trang lá hoặc quét bảng để tìm kiếm sự vắng mặt của một giá trị. Db2 thường không bị điều này bằng cách viết lại rất nhiều vị từ

mysql

Đây là một ví dụ về điều đó trong MySQL. Tôi đang sử dụng cơ sở dữ liệu sakila. Tôi đã tạo một bảng giả sử dụng bảng cho thuê làm điểm bắt đầu, tăng lượng dữ liệu, duy trì số dư 50% giá trị null cho cột return_date

mysql> create table t2 like rental;
Query OK, 0 rows affected (0.12 sec)

mysql> insert into t2 select * from rental where return_date is not null limit 183;
Query OK, 183 rows affected (0.03 sec)
Records: 183  Duplicates: 0  Warnings: 0

mysql> insert into t2 select * from rental where return_date is null;
Query OK, 183 rows affected (0.12 sec)
Records: 183  Duplicates: 0  Warnings: 0

mysql> insert into t2 (rental_date, inventory_id, customer_id, return_date, staff_id) select date_add(rental_date, interval 1 day), inventory_id, customer_id, return_date, staff_id from t2;
Query OK, 366 rows affected (0.03 sec)
Records: 366  Duplicates: 0  Warnings: 0

mysql> insert into t2 (rental_date, inventory_id, customer_id, return_date, staff_id) select date_add(rental_date, interval 2 day), inventory_id, customer_id, return_date, staff_id from t2;
Query OK, 732 rows affected (0.06 sec)
Records: 732  Duplicates: 0  Warnings: 0

mysql> insert into t2 (rental_date, inventory_id, customer_id, return_date, staff_id) select date_add(rental_date, interval 4 day), inventory_id, customer_id, return_date, staff_id from t2;
Query OK, 1464 rows affected (0.06 sec)
Records: 1464  Duplicates: 0  Warnings: 0

mysql> insert into t2 (rental_date, inventory_id, customer_id, return_date, staff_id) select date_add(rental_date, interval 8 day), inventory_id, customer_id, return_date, staff_id from t2;
Query OK, 2928 rows affected (0.16 sec)
Records: 2928  Duplicates: 0  Warnings: 0

mysql> insert into t2 (rental_date, inventory_id, customer_id, return_date, staff_id) select date_add(rental_date, interval 16 day), inventory_id, customer_id, return_date, staff_id from t2;
Query OK, 5856 rows affected (0.21 sec)
Records: 5856  Duplicates: 0  Warnings: 0

mysql> insert into t2 (rental_date, inventory_id, customer_id, return_date, staff_id) select date_add(rental_date, interval 32 day), inventory_id, customer_id, return_date, staff_id from t2;
Query OK, 11712 rows affected (0.29 sec)
Records: 11712  Duplicates: 0  Warnings: 0

mysql> insert into t2 (rental_date, inventory_id, customer_id, return_date, staff_id) select date_add(rental_date, interval 64 day), inventory_id, customer_id, return_date, staff_id from t2;
Query OK, 23424 rows affected (0.57 sec)
Records: 23424  Duplicates: 0  Warnings: 0

mysql> create index test_idx on t2 (return_date);
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

Lượng dữ liệu đáng kể là để đối phó với xu hướng luôn chọn quét bảng khi các bảng thực sự nhỏ. Sau đó, khi tôi chạy một truy vấn dựa trên cột return_date, bạn có thể thấy các kế hoạch truy cập dự kiến ​​khác nhau cho null và không null

mysql> explain select * from t2 where return_date is null;
+----+-------------+-------+------------+------+---------------+----------+---------+-------+-------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows  | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+-------+----------+-----------------------+
|  1 | SIMPLE      | t2    | NULL       | ref  | test_idx      | test_idx | 6       | const | 23495 |   100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+-------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t2 where return_date is not null;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | test_idx      | NULL | NULL    | NULL | 46990 |    50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Giải thích về điều kiện IS NULL là sử dụng chỉ mục test_idx mà tôi đã tạo để lấy dữ liệu. Điều kiện IS NOT NULL đang thực hiện quét toàn bộ bảng

Db2

Đây là thử nghiệm tương tự trong Db2. Về cơ bản, tôi đã sử dụng cơ sở dữ liệu sakila, với một vài điều chỉnh. Lưu ý rằng tôi thực sự không thể sử dụng cú pháp để tạo bảng như RENTAL trong Db2 bởi vì khi tôi làm vậy, không chỉ các chỉ mục không được tạo (một tác dụng phụ của thực tế là tên chỉ mục Db2 phải là duy nhất cho mỗi cơ sở dữ liệu, trong khi MySQL phải

$ db2 -tvf t2_create.ddl
CREATE TABLE "DB2INST1"."T2"  ( "RENTAL_ID" INTEGER generated by default as identity NOT NULL primary key, "RENTAL_DATE" DATE NOT NULL , "INVENTORY_ID" INTEGER NOT NULL , "CUSTOMER_ID" INTEGER NOT NULL , "RETURN_DATE" DATE WITH DEFAULT NULL , "STAFF_ID" SMALLINT NOT NULL , "LAST_UPDATE" DATE NOT NULL WITH DEFAULT CURRENT DATE ) IN "USERSPACE1" ORGANIZE BY ROW
DB20000I  The SQL command completed successfully.

CREATE INDEX "DB2INST1"."T2_IDX_RENTAL_FK_INVENTORY_ID" ON "DB2INST1"."T2" ("INVENTORY_ID" ASC) COMPRESS NO INCLUDE NULL KEYS ALLOW REVERSE SCANS
DB20000I  The SQL command completed successfully.

CREATE INDEX "DB2INST1"."T2_IDX_RENTAL_FK_CUSTOMER_ID" ON "DB2INST1"."T2" ("CUSTOMER_ID" ASC) COMPRESS NO INCLUDE NULL KEYS ALLOW REVERSE SCANS
DB20000I  The SQL command completed successfully.

CREATE INDEX "DB2INST1"."T2_IDX_RENTAL_FK_STAFF_ID" ON "DB2INST1"."T2" ("STAFF_ID" ASC) COMPRESS NO INCLUDE NULL KEYS ALLOW REVERSE SCANS
DB20000I  The SQL command completed successfully.

CREATE UNIQUE INDEX "DB2INST1"."T2_IDX_RENTAL_UQ" ON "DB2INST1"."T2" ("RENTAL_DATE" ASC, "INVENTORY_ID" ASC, "CUSTOMER_ID" ASC) COMPRESS NO INCLUDE NULL KEYS ALLOW REVERSE SCANS
DB20000I  The SQL command completed successfully.

$ db2 "insert into t2 select * from rental where return_date is null"
DB20000I  The SQL command completed successfully.
$ db2 "insert into t2 select * from rental where return_date is not null fetch first 183 rows only"
DB20000I  The SQL command completed successfully.
$ db2 "insert into t2 (rental_date, inventory_id, customer_id, return_date, staff_id) select rental_date +1 day, inventory_id, customer_id, return_date, staff_id from t2"
DB20000I  The SQL command completed successfully.
$ db2 "insert into t2 (rental_date, inventory_id, customer_id, return_date, staff_id) select rental_date +2 day, inventory_id, customer_id, return_date, staff_id from t2"
DB20000I  The SQL command completed successfully.
$ db2 "insert into t2 (rental_date, inventory_id, customer_id, return_date, staff_id) select rental_date +4 day, inventory_id, customer_id, return_date, staff_id from t2"
DB20000I  The SQL command completed successfully.
$ db2 "insert into t2 (rental_date, inventory_id, customer_id, return_date, staff_id) select rental_date +8 day, inventory_id, customer_id, return_date, staff_id from t2"
DB20000I  The SQL command completed successfully.
$ db2 "insert into t2 (rental_date, inventory_id, customer_id, return_date, staff_id) select rental_date +16 day, inventory_id, customer_id, return_date, staff_id from t2"
DB20000I  The SQL command completed successfully.
$ db2 "insert into t2 (rental_date, inventory_id, customer_id, return_date, staff_id) select rental_date +32 day, inventory_id, customer_id, return_date, staff_id from t2"
DB20000I  The SQL command completed successfully.
$ db2 "insert into t2 (rental_date, inventory_id, customer_id, return_date, staff_id) select rental_date +64 day, inventory_id, customer_id, return_date, staff_id from t2"
DB20000I  The SQL command completed successfully.

$ db2 "create index test_idx on t2 (return_date) allow reverse scans"
DB20000I  The SQL command completed successfully.
$ db2 "runstats on table db2inst1.t2 with distribution and detailed indexes all"
DB20000I  The RUNSTATS command completed successfully.

Các giải thích sau đó trông giống như thế này. Lưu ý rằng tôi đang sử dụng một phương pháp giải thích khác thường ở đây sẽ làm cho đầu ra trông giống MS SQL Server hơn và nhỏ gọn hơn để vừa với màn hình. Nó chỉ là một cái nhìn trên các bảng giải thích có sẵn miễn phí tại liên kết ở trên

$ db2 set current explain mode explain
DB20000I  The SQL command completed successfully.
$ db2 "select * from t2 where return_date is null"
SQL0217W  The statement was not executed as only Explain information requests
are being processed.  SQLSTATE=01604
$ db2 set current explain mode no
DB20000I  The SQL command completed successfully.
$ db2 "select * from last_explained"

Explain Plan
----------------------------------------------------------------------------------------------------
ID | Operation           |                     Rows | Cost
 1 | RETURN              |                          |  317
 2 |  FETCH T2           | 23424 of 23424 (100.00%) |  317
 3 |   RIDSCN            | 23424 of 23424 (100.00%) |   97
 4 |    SORT (UNIQUE)    | 23424 of 23424 (100.00%) |   97
 5 |     IXSCAN TEST_IDX | 23424 of 46848 ( 50.00%) |   94

Predicate Information
 2 - SARG Q1.RETURN_DATE IS NULL
 5 - START Q1.RETURN_DATE IS NULL
      STOP Q1.RETURN_DATE IS NULL

$ db2 set current explain mode explain
DB20000I  The SQL command completed successfully.
$ db2 "select * from t2 where return_date is not null"
SQL0217W  The statement was not executed as only Explain information requests
are being processed.  SQLSTATE=01604
$ db2 set current explain mode no
DB20000I  The SQL command completed successfully.
$ db2 "select * from last_explained"

Explain Plan
----------------------------------------------------------------------------------------------------
ID | Operation           |                     Rows | Cost
 1 | RETURN              |                          |  317
 2 |  FETCH T2           | 23424 of 23424 (100.00%) |  317
 3 |   RIDSCN            | 23424 of 23424 (100.00%) |   97
 4 |    SORT (UNIQUE)    | 23424 of 23424 (100.00%) |   97
 5 |     IXSCAN TEST_IDX | 23424 of 46848 ( 50.00%) |   94

Predicate Information
 2 - SARG Q1.RETURN_DATE IS NOT NULL
 5 - STOP Q1.RETURN_DATE IS NOT NULL

Trong Db2, một chỉ mục được sử dụng cho từng mục này và bạn có thể thấy rằng chi phí dự kiến ​​là giống hệt nhau. Nếu tôi bỏ chỉ mục để buộc quét bảng, phần giải thích sẽ như thế này

$ db2 drop index test_idx
DB20000I  The SQL command completed successfully.
$ db2 "runstats on table db2inst1.t2 with distribution and detailed indexes all"
DB20000I  The RUNSTATS command completed successfully.
$ db2 set current explain mode explain
DB20000I  The SQL command completed successfully.
$ db2 "select * from t2 where return_date is not null"
SQL0217W  The statement was not executed as only Explain information requests
are being processed.  SQLSTATE=01604
$ db2 set current explain mode no
DB20000I  The SQL command completed successfully.
$ db2 "select * from last_explained"

Explain Plan
----------------------------------------------------------------------------------------------------
ID | Operation  |                     Rows | Cost
 1 | RETURN     |                          |  392
 2 |  TBSCAN T2 | 23424 of 46848 ( 50.00%) |  392

Predicate Information
 2 - SARG Q1.RETURN_DATE IS NOT NULL

Không phải là một sự khác biệt lớn về chi phí ở kích thước bảng này, nhưng là một sự khác biệt có thể đo lường được

• chi tiết tác giả

Ember Crooks

https. //datageek. Blog/

than hồng. kẻ lừa đảo @ gmail. com

Ember luôn tò mò và phát triển nhờ sự thay đổi. Cô ấy đã xây dựng kiến ​​thức chuyên môn được quốc tế công nhận về IBM Db2 và hiện đang chuyển sang tập trung vào việc học MySQL. Ember chia sẻ cả hai bài đăng về bộ kỹ năng cốt lõi và hành trình học MySQL của cô ấy. Ember sống ở Denver và làm việc tại nhà

Chỉ mục duy nhất có thể có giá trị NULL MySQL không?

Chỉ mục duy nhất có thể là NULL không?

NULL có được phép trong MySQL ràng buộc duy nhất không?