本文內容翻譯自MariaDB官方文檔中 Optimization and Indexes部分,包含以下內容:

其中的 EXPLAIN本人已經翻譯,見 MariaDB EXPLAIN Learning

Full-text Indexes部分暫不翻譯。

What is an Index

MariaDB官方文檔 What is an Index?

假設(image)你用如下數據行創建了一張表(此表與Advanced Joins tutorial用到的表一樣)

1
2
3
4
5
6
7
8
9
10
+----+------------+-----------+-------------------------+---------------------------+--------------+
| ID | First_Name | Last_Name | Position | Home_Address | Home_Phone |
+----+------------+-----------+-------------------------+---------------------------+--------------+
| 1 | Mustapha | Mond | Chief Executive Officer | 692 Promiscuous Plaza | 326-555-3492 |
| 2 | Henry | Foster | Store Manager | 314 Savage Circle | 326-555-3847 |
| 3 | Bernard | Marx | Cashier | 1240 Ambient Avenue | 326-555-8456 |
| 4 | Lenina | Crowne | Cashier | 281 Bumblepuppy Boulevard | 328-555-2349 |
| 5 | Fanny | Crowne | Restocker | 1023 Bokanovsky Lane | 326-555-6329 |
| 6 | Helmholtz | Watson | Janitor | 944 Soma Court | 329-555-2478 |
+----+------------+-----------+-------------------------+---------------------------+--------------+

現在,假設你需要返回 Fanny Crownehome phone。沒有索引,惟一的方式時遍歷每一行數據直到找到匹配的 first namesurname 。現在假設(數據表中)有數百萬行的記錄,可以看到,即便是一個快速的(speedy)數據庫服務器,(也)是很不高效的(high inefficient)。

答案是對記錄(record)進行排序,如果按 surname 以字符順序存儲,即便是人工(查找),也能在大量的記錄中快速找到(匹配)記錄。但我們不能對 surname 的所有記錄進行排序。What if 我們也想通過 IDFirst_Name 查找數據?答案是爲我們想用於排序的每一列創建單獨的索引。索引簡單包含了排序數據(如 surname),指向原始記錄(original record)的鏈接。

An index simply contains the sorted data (such as surname), and a link to the original record.

例如在 Last_Name 上的索引

1
2
3
4
5
6
7
8
9
10
+-----------+----+
| Last_Name | ID |
+-----------+----+
| Crowne | 4 |
| Crowne | 5 |
| Foster | 2 |
| Marx | 3 |
| Mond | 1 |
| Watson | 6 |
+-----------+----+

和在 Position 上的索引

1
2
3
4
5
6
7
8
9
10
+-------------------------+----+
| Position | ID |
+-------------------------+----+
| Cashier | 3 |
| Cashier | 4 |
| Chief Executive Officer | 1 |
| Janitor | 6 |
| Restocker | 5 |
| Store Manager | 2 |
+-------------------------+----+

將允許您快速查找(find)到所有cashiers(收銀員)的 phone number,或僱員 surnameMarxphone number,查詢速度非常快(very quickly)。

如果可能,您應該爲查詢用到的每一列創建一個索引,以避免(avoid)服務器讀取數據表中的每一行數據。

更多信息,參閱 CREATE INDEXGetting Started with Indexes

小結:索引的作用是避免全表掃描,減輕服務器壓力,提高查詢效率

Getting Started with Indexes

MariaDB官方文檔 Getting Started with Indexes

索引主要有四種: primary keys(主鍵索引)、unique indexes(惟一索引)、plain indexes(簡單索引)、full-text indexes (全文索引)

術語 KEYINDEX 通常在使用時是可互換的,語句應該對每個關鍵詞(keyword)有效。

Primary Key

主鍵(primary key)是 惟一不能爲null,通常只指定一行記錄,且每一行記錄必須被代表(represent)。每張表有且只能有一個主鍵(primary key)

A primary key is unique and can never be null. It will always identify only one record, and each record must be represented. Each table can only have one primary key.

XtraDB/InnoDB 表中,所有的索引包含主鍵,作爲後綴。因此,當使用 XtraDB/InnoDB 存儲引擎時,保證主鍵儘可能的短小(small) 是尤其重要的。如果(數據表中)不存在主鍵且無惟一索引,InnoDB將創建(長度爲)6個字節(6-bytes)的集羣索引(clustered index),該索引用戶看不到。

In XtraDB/InnoDB tables, all indexes contain the primary key as a suffix. Thus, when using this storage engine, keeping the primary key as small as possible is particularly important. If a primary key does not exist and there are no UNIQUE indexes, InnoDB creates a 6-bytes clustered index which is invisible to the user.

許多數據表使用 數值ID字段(numeric ID field) 作爲主鍵。

Many tables use a numeric ID field as a primary key.

主鍵通常在創建數據表時添加,使用 CREATE TABLE語句。例如,接下來的(示例)在 ID 字段創建主鍵。注意ID 字段必須定義爲 NOT NULL,否則索引不會被創建。

Primary keys are usually added when the table is created with the CREATE TABLE statement. For example, the following creates a primary key on the ID field. Note that the ID field had to be defined as NOT NULL, otherwise the index could not have been created.

1
2
3
4
5
6
7
8
9
CREATE TABLE `Employees` (
`ID` TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
`First_Name` VARCHAR(25) NOT NULL,
`Last_Name` VARCHAR(25) NOT NULL,
`Position` VARCHAR(25) NOT NULL,
`Home_Address` VARCHAR(50) NOT NULL,
`Home_Phone` VARCHAR(12) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=Aria;

您不能使用CREATE INDEX命令創建主鍵,如果您想在已經創建好的數據表中添加主鍵,可使用ALTER TABLE命令,例如

1
ALTER TABLE Employees ADD PRIMARY KEY(ID);

Unique Index

惟一索引 必須惟一,但可以爲null,每個索引值(key value)僅指定一個記錄,但不是每一個記錄都需要被代表。

A Unique Index must be unique, but it can be null. So each key value indentifies only one record, but not each record needs to be represented.

例如,在字段Employee_Code創建惟一索引,與主鍵一樣,使用:

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE `Employees` (
`ID` TINYINT(3) UNSIGNED NOT NULL,
`First_Name` VARCHAR(25) NOT NULL,
`Last_Name` VARCHAR(25) NOT NULL,
`Position` VARCHAR(25) NOT NULL,
`Home_Address` VARCHAR(50) NOT NULL,
`Home_Phone` VARCHAR(12) NOT NULL,
`Employee_Code` VARCHAR(25) NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY (`Employee_Code`)
) ENGINE=Aria;

惟一索引也可在已經創建好的數據表上添加,使用 CREATE INDEXALTER TABLE命令,例如:

1
ALTER TABLE Employees ADD UNIQUE `EmpCode`(`Employee_Code`);

1
CREATE UNIQUE INDEX HomePhone ON Employees(Home_Phone);

事實上, UNIQUE約束可以爲NULL 通常被忽視。在SQL中,NULL不等於任何值(anything),包括其它NULL。因此(Consequently),UNIQUE約束不會阻止存儲含有 null 值的重複行。

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE Table_1 (a int not null, b int, unique (a,b));
insert into Table_1 values (1,1),(2,NULL),(2,NULL);
select * from Table_1;

+---+------+
| a | b |
+---+------+
| 1 | 1 |
| 2 | NULL |
| 2 | NULL |
+---+------+

事實上(indeed),在SQL中,兩行數據即便一樣,彼此也不相等

Indeed, in SQL two last rows, even if identical, are not equal to each other:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SELECT (2, NULL) = (2, NULL);

+---------------------- +
| (2, NULL) = (2, NULL) |
+---------------------- +
| 0 |
+---------------------- +

MariaDB [(none)]> SELECT (2, NULL) = (2, NULL);
+-----------------------+
| (2, NULL) = (2, NULL) |
+-----------------------+
| NULL |
+-----------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT (2, 2) = (2, 2);
+-----------------+
| (2, 2) = (2, 2) |
+-----------------+
| 1 |
+-----------------+
1 row in set (0.00 sec)

MariaDB [(none)]>

在MariaDB中,您可以將其與virtual columns結合,通過數據表中的行子集,強制惟一性(enforce uniqueness)。

In MariaDB you can combine this with virtual columns to enforce uniqueness over a subset of rows in a table:

1
2
3
4
5
6
create table Table_1 (
user_name varchar(10),
status enum('Active', 'On-Hold', 'Deleted'),
del char(0) as (if(status in ('Active', 'On-Hold'),'', NULL)) persistent,
unique(user_name,del)
)

此數據表結構確保所有 activeon-hold 用戶有不同的名稱(name),不過一旦用戶被刪除,其用戶名不在受惟一約束,其他(新增)用戶可以使用相同(已被刪除的)名稱。

This table structure ensures that all active or on-hold users have distinct names, but as soon as a user is deleted, his name is no longer part of the uniqueness constraint, and another user may get the same name.

如果惟一索引的組成列中,由一列尾部填充字段被除去或忽視,只有尾部填充字段不同的數據插入該列會導致 duplicate-key 報錯。

If a unique index consists of a column where trailing pad characters are stripped or ignored, inserts into that column where values differ only by the number of trailing pad characters will result in a duplicate-key error.

Multiple-column Indexes

索引可以包含多列,MariaDB可使用索引中最左部分的一列或多列,如果它不能使用整個索引。

Indexes can contain more than one column. MariaDB is able to use one or more columns on the leftmost part of the index, if it cannot use the whole index.

Choosing Indexes

通常您應該只添加滿足應用(application)使用到的查詢的索引。任何其它(未用到的)索引將浪費資源。在一個應用中,如果數據表數量不大,索引不會帶來明顯的不同。但如果數據表的數量大於緩衝區大小,緩存將極大地提高查詢效率。

In general you should only add indexes to match the queries your application uses. Any extra will waste resources. In an application with very small tables, indexes will not make much difference but as soon as your tables are larger than your buffer sizes the indexes will start to speed things up dramatically.

在查詢語句中使用 EXPLAIN 可幫助決定哪一列需要索引。

Using the EXPLAIN statement on your queries can help you decide which columns need indexing.

如果查詢包含LIKE '%word%',如果沒有全文(fulltext)索引,您將每次都進行全表掃描,(速度)很慢。

If you query contains something like LIKE '%word%', without a fulltext index you are using a full table scan every time, which is very slow.

如果您的數據表有大量的讀、寫(操作),可考慮使用 延遲寫(delayed write)。這使數據庫引擎進入 batch 寫模式,可減少磁盤I/O,以提高性能。

If your table has a large number of reads and writes, consider using delayed writes. This uses the db engine in a “batch” write mode, which cuts down on disk io, therefore increasing performance.

使用 CREATE INDEX命令創建索引。

如果您正在建一張很大的表,爲(實現)最佳性能,在數據入庫後再添加索引。這是爲了提升插入性能,移除插入過程中的索引開銷(overhead)。

If you are building a large table then for best performance add the index after the table is populated with data. This is to increase the insert performance and remove the index overhead during inserts.

Viewing indexes

您可以使用SHOW INDEX語句查看數據表中存在的索引及其具體信息

如果您想直到如何重新創建索引,運行 SHOW CREATE TABLE命令。

When to remove an index

如果一個索引很少使用(或根本沒使用過),可刪除該索引,以提高 INSERTUPDATE 性能。

如果啓用 user statisticsInformation Schema中的數據表 INDEX_STATISTICS存儲了索引使用(統計信息)。

如果啓用 slow query log,系統變量 log_queries_not_using_indexesON,查詢未用到的索引會被記錄。

小結

  • 每一張數據表都必須要有主鍵(primary key),只能有一個,主鍵惟一且不能爲NULL;
  • XtraDB/InnoDB中,其它索引將主鍵索引作爲後綴,主鍵長度應儘可能短;如果數據表中無主鍵、惟一索引,會自動創建長度爲6個字節的集羣索引;
  • 使用CREATE TABLE語句創建索引時,主鍵必須加上NOT NULL約束,否則索引不會被創建。(直接創建數據表無法區別,該如何驗證??);
  • 每張數據表可含有多個惟一索引,惟一索引必須惟一,但可以爲null,UNIQUE約束不會阻止存儲含有 null 值的重複行; null不與任何值相等,包括其它null值;
  • 索引可包含多列,如果不能使用整個索引,可使用索引最左部分;
  • 按需添加索引,查詢語句儘量避免使用LIKE '%word%',無法使用索引,全表掃描;
  • 短時間內大量寫入數據,可先刪除索引,以提高寫入效率,減少索引開銷;

ANALYZE TABLE

MariaDB官方文檔 ANALYZE TABLE

Syntax

1
2
ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [,tbl_name ...]
[PERSISTENT FOR [ALL|COLUMNS ([col_name [,col_name ...]]) [INDEXES ([index_name [,index_name ...]])]]
1
2
3
4
5
Name: 'ANALYZE TABLE'
Description:
Syntax:
ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE
tbl_name [, tbl_name] ...

Description

ANALYZE TABLE分析、存儲數據表的索引分佈(index statistics 索引統計)。分析期間,數據表被鎖定,對於 MyISAM 是讀鎖,對於 InnoDB 是寫鎖。該語句用於 MyISAM, AriaInnoDB數據表。對於MyISAM數據表,等效於使用 myisamchk –analyze

更多分析在InnoDB中如何工作,查看InnoDB Restrictions

當執行join而不是常數,MariaDB使用存儲的索引分佈(key distribution)決定數據表被連接(join)的順序。另外,在一個查詢中的具體數據表,索引分佈可用於決定使用哪個索引。

此語句需要對表有 SELECT and INSERT privileges權限。

默認情況下,ANALYZE TABLE語句會被寫入binary log,並被複製(replicated)。語句中的關鍵詞 NO_WRITE_TO_BINLOG (LOCAL是別名)將確保語句不被寫入 binary log。

ANALYZE TABLE也支持分區表(partitioned tables),您可使用 ALTER TABLE ... ANALYZE PARTITION 分析一個或多個分區(partition)。

MariaDB 10.0.1 extended ANALYZE TABLE to support engine-independent statistics.
Currently, engine-independent statistics is collected by doing full table and full index scans, and can be quite expensive.

ANALYZE TABLE的語法在MariaDB 10.0.1中被擴展,允許只爲部分列或索引收集統計信息,需要使用 PERSISTENT

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- update all engine-independent statistics for all columns and indexes
ANALYZE TABLE tbl PERSISTENT FOR ALL;

-- update specific columns and indexes:
ANALYZE TABLE tbl PERSISTENT FOR COLUMNS (col1,col2,...) INDEXES (idx1,idx2,...);

-- empty lists are allowed:
ANALYZE TABLE tbl PERSISTENT FOR COLUMNS (col1,col2,...) INDEXES ();
ANALYZE TABLE tbl PERSISTENT FOR COLUMNS () INDEXES (idx1,idx2,...);

-- the following will only update mysql.table_stat fields:
ANALYZE TABLE tbl PERSISTENT FOR COLUMNS () INDEXES ();

-- with use_stat_tables != NEVER a simple ANALYZE TABLE collects engine-independent statistics for all columns and indexes.
SET SESSION use_stat_tables='COMPLEMENTARY';
ANALYZE TABLE tbl;

更新engine-independent statistics需要PERSISTENT FOR,即使數據表有選項 STATS_PERSISTENT=1

MariaDB 5.3起,Aria存儲引擎未該語句支持 progress reporting

1
2
3
4
5
6
7
8
9
10
11
12
13
14
MariaDB [chinaCode]> analyze table village persistent for all\G
*************************** 1. row ***************************
Table: chinaCode.village
Op: analyze
Msg_type: status
Msg_text: Engine-independent statistics collected
*************************** 2. row ***************************
Table: chinaCode.village
Op: analyze
Msg_type: status
Msg_text: OK
2 rows in set (11.63 sec)

MariaDB [chinaCode]>

Foreign Keys

MariaDB官方文檔 Foreign Keys

Overview

外鍵(foreign key)是一種約束,用於增強數據一致性(data integrity)。它由子表(child table)中的列(或列集合)形成,參照母表(parent table)中的列(或列集合)。如果使用外鍵,MariaDB執行一些檢查以加強一致性規則。更多詳細解釋,參閱Relational databases: Foreign Keys

外鍵只能用於支持外鍵的存儲引擎,默認是InnoDB,過時的(obsolete) PBXT支持外鍵。

分區表不能包含外鍵,也不能被外鍵參考。

Partitioned tables cannot contain foreign keys, and cannot be referenced by a foreign key.

Syntax

Note: MariaDB accepts the REFERENCES clause in ALTER TABLE and CREATE TABLE statements, but that syntax does nothing. MariaDB simply parses it without returning any error or warning, for compatibility with other DBMS’s. However, only the syntax described below creates foreign keys.

外鍵可用 CREATE TABLEALTER TABLE 創建。定義必須遵循以下語法格式

1
2
3
4
5
6
7
8
[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]

reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

如果指定symbol字句,可用於錯誤消息,且在數據庫中必須惟一。

eg: alter table city add constraint fkey_province_city foreign key(provinceId) references province(id) on update cascade;

The symbol clause, if specified, is used in error messages and must be unique in the database.

子表中的列必須是索引或在索引的最左部分。不支持索引前綴(因此,TEXTBLOB列不能用作外鍵)。如果MariaDB自動爲外鍵創建索引(不存在,未被顯式創建),其名爲index_name

The columns in the child table must be an index, or the leftmost part of an index. Index prefixes are not supported (thus, TEXT and BLOB columns cannot be used as foreign keys). If MariaDB automatically creates an index for the foreign key (because it does not exist and is not explicitly created), its name will be index_name.

參考列必須是主鍵或惟一索引。

外鍵列和參考列可以是 PERSISTENT列(虛擬列中一種),不過 ON UPDATE CASCADEON UPDATE SET NULLON DELETE SET NULL子句在此場景中無法使用(not allow)。

Both the foreign key columns and the referenced columns can be PERSISTENT columns. However, the ON UPDATE CASCADE, ON UPDATE SET NULL, ON DELETE SET NULL clauses are not allowed in this case.

外鍵列和參考列必須是相同或相似數據類型,對於整型,大小和正負也必須相同。

The foreign key columns and the referenced columns must be of the same type, or similar types. For integer types, the size and sign must also be the same.

父表和子表必須使用相同存儲引擎。不能是TEMPORARY表和分區(partitioned table)表,他們可能是相同數據表。

The parent and the child table must use the same storage engine, and must not be TEMPORARY or partitioned tables. They can be the same table.

Constraints

如果外鍵存在,子表中的每一行都必須匹配父表中的某一行。多個子表行可匹配父表中的同一行。一個子表行匹配一個父表行,如果其外鍵值與父表中的父表行的值一致。不過,如果有至少一個外鍵值是 NULL,該行沒有父表行,不過仍然允許。

If a foreign keys exists, each row in the child table must match a row in the parent table. Multiple child rows can match the same parent row. A child row matches a parent row if all its foreign key values are identical to a parent row’s values in the parent table. However, if at least one of the foreign key values is NULL, the row has no parents, but it is still allowed.

MariaDB執行確定的檢查以保證數據一致性是強制的:

  • 嘗試在子表中插入不匹配的行(或更新爲不匹配的行)會引起(produce) 1452報錯 (SQLSTATE ‘23000’);
  • 當父表中的數據行被刪除,且子表至少存在一行數據,如果外鍵有ON DELETE子句,MariaDB會刪除子表中對應數據;
  • 當父表中字段值被更改,且子表至少存在一行數據,如果外鍵有ON UPDATE子句,MariaDB會更新子表中對應數據;
  • 嘗試刪除被外鍵參考的表(即父表)會引起(produce) 1217報錯 (SQLSTATE ‘23000’)
  • 使用TRUNCATE TABLE清空包含外鍵的數據表,執行等用於沒有WHERE字句的DELETE。因此,外鍵被強制到每一行。

A TRUNCATE TABLE against a table containing one or more foreign keys is executed as a DELETE without WHERE, so that the foreign keys are enforced for each row.

允許的ON DELETEON UPDATE動作(action)有:

  • RESTRICT:對父表的更改被阻止,語句以報錯1451結束 (SQLSTATE ‘23000’)。對於ON DELETEON UPDATE,此爲默認行爲;
  • NO ACTIONRESTRICT的同義詞;
  • CASCADE:對父表的更改允許傳送(propagate)到子表。例如,如果父表行被刪除,對應的子表行也會被刪除;如果父表行ID更改,子表對應的行外鍵ID也會更改;
  • SET NULL:允許更改,父表行的外鍵列設置爲NULL;
  • SET DEFAULT:只對PBXT可用,類似SET NULL,但外鍵列被設置爲默認值,如果默認值不存在,會產生報錯;

外鍵觸發的刪除和更新操作不會激活觸發器(trigger),不會在狀態變量 Com_deleteCom_update中計數。

設置服務器變量foreign_key_checks值爲0可禁用外鍵約束,可加快大量數據的寫入。

1
2
3
4
5
6
7
8
9
MariaDB [(none)]> show variables like '%foreign_key_check%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| foreign_key_checks | ON |
+--------------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]>

Metadata

數據庫Information Schema中的數據表REFERENTIAL_CONSTRAINTS包含外鍵信息。單獨的列在數據表KEY_COLUMN_USAGE中。

數據庫Information Schema中InnoDB相關的數據表(InnoDB-specific)也包含關於InnoDB外鍵的信息,存儲在INNODB_SYS_FOREIGN,獨立列存儲在INNODB_SYS_FOREIGN_COLS

獲取最可讀的數據表外鍵信息的方式是SHOW CREATE TABLE語句。

Limitations

在MariaDB中,外鍵有如下限制:

  • 目前,外鍵只支持InnoDB存儲引擎
  • 不可用於視圖(view)
  • 不支持SET DEFAULT行爲(action)
  • 外鍵行爲不會激活觸發器(trigger)

Examples

讓我們看一個示例。創建數據表authorbook。所有表都有一個名爲id的主鍵。表book有一個由名爲author_id的字段組成的外鍵,指向表author的主鍵。外鍵約束名稱是可選的,但我們指定它,因爲希望它能出現在錯誤消息中:fk_book_author

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE author (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE book (
id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200) NOT NULL,
author_id SMALLINT UNSIGNED NOT NULL,
CONSTRAINT `fk_book_author`
FOREIGN KEY (author_id) REFERENCES author (id)
ON DELETE CASCADE
ON UPDATE RESTRICT
) ENGINE = InnoDB;

現在我們嘗試在數據表book中插入不存在的作者(author),得到錯誤:

1
2
3
4
INSERT INTO book (title, author_id) VALUES ('Necronomicon', 1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(`test`.`book`, CONSTRAINT `fk_book_author` FOREIGN KEY (`author_id`)
REFERENCES `author` (`id`) ON DELETE CASCADE)

該錯誤是可描述的。

現在,我們嘗試正常的寫入2位作者和他們的書:

1
2
3
4
5
6
7
INSERT INTO author (name) VALUES ('Abdul Alhazred');
INSERT INTO book (title, author_id) VALUES ('Necronomicon', LAST_INSERT_ID());

INSERT INTO author (name) VALUES ('H.P. Lovecraft');
INSERT INTO book (title, author_id) VALUES
('The call of Cthulhu', LAST_INSERT_ID()),
('The colour out of space', LAST_INSERT_ID());

正常寫入!

現在我們刪除第二位作者。當我們創建外鍵時,我們指定ON DELETE CASCADE。這將傳送刪除,使被刪除的作者的書不再出現。

Now, let’s delete the second author. When we created the foreign key, we specified ON DELETE CASCADE. This should propagate the deletion, and make the deleted author’s books disappear:

1
2
3
4
5
6
7
8
DELETE FROM author WHERE name = 'H.P. Lovecraft';

SELECT * FROM book;
+----+--------------+-----------+
| id | title | author_id |
+----+--------------+-----------+
| 3 | Necronomicon | 1 |
+----+--------------+-----------+

我們也指定了ON UPDATE RESTRICT。如果子表中行存在,將阻止我們更改作者id(外鍵參照此列)

1
2
3
4
UPDATE author SET id = 10 WHERE id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
(`test`.`book`, CONSTRAINT `fk_book_author` FOREIGN KEY (`author_id`)
REFERENCES `author` (`id`) ON DELETE CASCADE)

See also

MariaDB: InnoDB foreign key constraint errors, a post in the MariaDB blog

小結

  • 外鍵只支持InnoDB,不可用於視圖(view),不會激活觸發器;
  • 父表和子表須使用相同存儲引擎,默認是InnoDB,但不能使用TEMPORARY表和分區表;
  • 外鍵列在子表中,參考列在父表中,二者必須使用相同或相似的數據類型。如果是整型,大小和正負也必須相同;
  • 參考列必須是主鍵或惟一索引;
  • 子表中的列必須是索引或在索引的最左部分。不支持索引前綴(不能使用TEXT和BLOB)。MariaDB自動爲外鍵創建索引名爲index_name
  • 如果指定symbol字句,可用於錯誤消息,其命名在數據庫中必須惟一;
  • 如果某數據表被外鍵參考,無法直接刪除;
  • 如果外鍵指定ON DELETEON UPDATE,對父表字段進行操作時,子表相關數據會對應更改;

Index Statistics

MariaDB官方文檔 Index Statistics

How Index Statistics Help the Query Optimizer

MariaDB查詢優化器(query optimizer)基於大量含有索引細節信息,決定如何最佳執行每個查詢

The MariaDB query optimizer decides how best to execute each query based largely on the details of the indexes involved.

索引統計幫助通知這些決定。假設您自己在選擇是否在電話簿中查找(look up)一個號碼,或您個人的地址簿。如果有可能,您最好選擇個人電話簿,它將(通常!)包含更少的記錄且更快查找(search)到。
The index statistics help inform these decisions. Imagine yourself choosing whether to look up a number in a phone book, or in your personal address book. You’d choose the personal phone book if at all possible, as it would (usually!) contain far fewer records and be quicker to search.

現在假設拿到您個人地址簿,發現其號碼數時電話簿的2倍。您的查找過程將會變慢。同樣的過程應用到查詢優化器上,所以訪問最新的、準確的統計數據是 critical (?)

Now imagine getting to your personal address book and finding it has twice the number of entries as the phone book. Your search would be slower. The same process applies to the query optimizer, so having access to up-to-date and accurate statistics is critical.

Value Groups

統計信息主要基於組中有相同值的索引元素。在主鍵中,每一個索引都是惟一的,所以每個組長度(group size)是1。在非惟一索引中,同一個值可能有多個key。最糟糕的示例,同一個值有大量組,例如boolean字段上的索引。

MariaDB重度使用平均組大小統計(the average group size statistic)。例如,如果有100行,有相同索引值的組有20組,平均組大小將是5。

然而,平均值可能被極端值扭曲,常見的起因是NULL值。這100行可能有19組的平均大小是1,同時其它81個值都是NULL。MariaDB可能認爲5是個不錯的平均大小並選擇使用該索引,最終不得不讀取含有相同值的81行數據,耗時更長。

However, averages can be skewed by extremes, and the usual culprit is NULL values. The row of 100 may have 19 groups with an average size of 1, while the other 81 values are all NULL. MariaDB may think five is a good average size and choose to use that index, and then end up having to read through 81 rows with identical keys, taking longer than an alternative.

Dealing with NULLs

NULLs問題主要有3中。

NULL索引值會被視作單個組(nulls_equal)。通常是可以的,但如果有大量的NULLs,平均組大小(average group size)會傾斜性的變高,優化器將忽略使用ref訪問(ref access)索引,即便它是有效的。這被XtraDB/InnoDBMyISAM默認使用。

nulls_unequal是相反的途徑,每一個NULL形成其自有組。相反地,平均組大小會傾斜性的遍低,優化器可能使用不適合的ref訪問(ref access)索引。這被Aria存儲引擎默認使用。

第三種nulls_ignored將NULL從索引組計算中被忽略掉。

There are three main approaches to the problem of NULLs. NULL index values can be treated as a single group (nulls_equal). This is usually fine, but if you have large numbers of NULLs the average group size is slanted higher, and the optimizer may miss using the index for ref accesses when it would be useful. This is the default used by XtraDB/InnoDB and MyISAM. Nulls_unequal is the opposite approach, with each NULL forming its own group of one. Conversely, the average group size is slanted lower, and the optimizer may use the index for ref accesses when not suitable. This is the default used by the Aria storage engine. A third options sees NULL’s ignored altogether from index group calculations.

可以通過設置服務器變量aria_stats_methodmyisam_stats_methodinnodb_stats_method來更改默認途徑。

Null safe and regular comparisons

使用的比較操作符(comparison operator)扮演了重要角色。如果2個值使用<=> (見null-safe-equal比較操作符),兩個都是null,返回1。如果相同值使用=比較 (見equal比較操作符),返回null。例如

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
+---------+---------------+------------+
| 1 <=> 1 | NULL <=> NULL | 1 <=> NULL |
+---------+---------------+------------+
| 1 | 1 | 0 |
+---------+---------------+------------+

SELECT 1 = 1, NULL = NULL, 1 = NULL;
+-------+-------------+----------+
| 1 = 1 | NULL = NULL | 1 = NULL |
+-------+-------------+----------+
| 1 | NULL | NULL |
+-------+-------------+----------+

Engine-independent Statistics

Engine-independent table statistics was implemented in MariaDB 10.0.1

MariaDB 10.0 introduced a way to gather statistics independently of the storage engine. See Engine-independent table statistics.

小結

  • MariaDB使用 the average group size statistic,行數除以組數,受異常值null影響
  • 索引統計如何處理NULLs,有三種 nulls_equalnulls_unequalnulls_ignored,可以通過設置服務器變量aria_stats_methodmyisam_stats_methodinnodb_stats_method來更改默認途徑。
  • NULL值不等於其它任何值,包括NULL值

Primary Keys with Nullable Columns

MariaDB 10.1.7 introduced new behavior for dealing with primary keys over nullable columns.

使用如下表結構

1
2
3
4
5
CREATE TABLE t1(
c1 INT NOT NULL AUTO_INCREMENT,
c2 INT NULL DEFAULT NULL,
PRIMARY KEY(c1,c2)
);

c2是主鍵的一部分,因此不能爲NULL.。

MariaDB 10.1.7之前,MariaDB(以及MySQL5.7之前版本)將靜默轉換爲NOT NULL,默認值0。

MariaDB 10.1.7起,列被轉換爲NOT NULL,但沒有默認值。如果我們沒有顯式設定c2,在嘗試寫入記錄時,會拋出提醒(或嚴格模式,一個報錯),例如

1
2
3
4
5
6
7
8
9
10
INSERT INTO t1() VALUES();
Query OK, 1 row affected, 1 warning (0.00 sec)
Warning (Code 1364): Field 'c2' does not have a default value

SELECT * FROM t1;
+----+----+
| c1 | c2 |
+----+----+
| 1 | 0 |
+----+----+

MySQL, since 5.7, will abort such a CREATE TABLE with an error.

The MariaDB 10.1.7 behavior adheres to the SQL 2003 standard.

SQL-2003, Part II, “Foundation” says:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
11.7 <unique constraint definition>
Syntax Rules



5) If the <unique specification> specifies PRIMARY KEY, then for each <column name> in the explicit or implicit <unique column list> for which NOT NULL is not specified, NOT NULL is implicit in the <column definition>.

Essentially this means that all PRIMARY KEY columns are automatically converted to NOT NULL. Furthermore:

11.5 <default clause>
General Rules



3) When a site S is set to its default value,



b) If the data descriptor for the site includes a <default option>, then S is set to the value specified by that <default option>.



e) Otherwise, S is set to the null value.

There is no concept of “no default value” in the standard. Instead, a column always has an implicit default value of NULL. On insertion it might however fail the NOT NULL constraint. MariaDB and MySQL instead mark such a column as “not having a default value”. The end result is the same — a value must be specified explicitly or an INSERT will fail.

MariaDB since 10.1.7 behaves in a standard compatible manner — being part of a PRIMARY KEY, the nullable column gets an automatic NOT NULL constraint, on insertion one must specify a value for such a column. MariaDB before 10.1.7 was automatically assigning a default value of 0 — this behavior was non-standard. Issuing an error at CREATE TABLE time is also non-standard.

SHOW EXPLAIN

MariaDB官方文檔 SHOW EXPLAIN

The SHOW EXPLAIN command is a new feature in MariaDB 10.0.0.

Command description

SHOW EXPLAIN命令允許獲取一個運行中的具體線程的查詢的EXPLAIN

The SHOW EXPLAIN command allows one to get an EXPLAIN (that is, a description of a query plan) of a query running in a certain thread.

語法格式:

1
SHOW EXPLAIN FOR <thread_id>;

將產生查詢正在運行的線程thread_idEXPLAIN輸出,線程id可通過SHOW PROCESSLIST獲取(obtain)。

1
2
3
4
5
6
7
SHOW EXPLAIN FOR 1;
+------+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | tbl | index | NULL | a | 5 | NULL | 1000107 | Using index |
+------+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
1 row in set, 1 warning (0.00 sec)

輸出通常伴隨着提醒,顯示正在運行的目標線程的查詢 (顯示EXPLAIN中提醒的內容),該線程有查詢計劃。

1
2
3
4
5
6
7
SHOW WARNINGS;
+-------+------+------------------------+
| Level | Code | Message |
+-------+------+------------------------+
| Note | 1003 | select sum(a) from tbl |
+-------+------+------------------------+
1 row in set (0.00 sec)

Possible errors

如果目標線程正在運行查詢,輸出將產生,否則輸出將是:

1
2
SHOW EXPLAIN FOR 2;
ERROR 1932 (HY000): Target is not running an EXPLAINable command

您將得到該錯誤,當:

  • 目標線程(the target thread)未運行
  • 目標線程正在運行,但沒有查詢計劃(例如,open和lock的數據表在查詢計劃產生之前被採用)

Differences between SHOW EXPLAIN and EXPLAIN outputs

Background

在MySQL中,EXPLAIN執行與優化過的真實查詢(典型SELECT)有細微區別。這是不幸的,已經在EXPLAIN中導致大量bug。(例如,查看MDEV-326MDEV-410lp:1013343)

In MySQL, EXPLAIN execution takes a slightly different route from the way the real query (typically the SELECT) is optimized. This is unfortunate, and has caused a number of bugs in EXPLAIN. (For example, see MDEV-326, MDEV-410, and lp:1013343. lp:992942 is not directly about EXPLAIN, but it also would not have existed if MySQL didn’t try to delete parts of a query plan in the middle of the query)

SHOW EXPLAIN測試運行中的SELECT,因此其輸出與EXPLAIN SELECT產生的有細微區別。我們竭盡所能確保區別是微不足道的,SHOW EXPLAIN的輸出更接近於EXPLAIN的輸出。

SHOW EXPLAIN examines a running SELECT, and hence its output may be slightly different from what EXPLAIN SELECT would produce. We did our best to make sure that either the difference is negligible, or SHOW EXPLAIN’s output is closer to reality than EXPLAIN’s output.

List of recorded differences

  • SHOW EXPLAIN may have Extra='no matching row in const table', where EXPLAIN would produce Extra='Impossible WHERE ...'
  • For queries with subqueries, SHOW EXPLAIN may print select_type==PRIMARY where regular EXPLAIN used to print select_type==SIMPLE, or vice versa.

Required permissions

運行SHOW EXPLAIN需要於運行SHOW PROCESSLIST相同的許可。

See also

SPATIAL INDEX

MariaDB官方文檔 SPATIAL INDEX

用途???

Description

空間索引
On MyISAM and Aria tables, MariaDB can create spatial indexes(空間索引) using syntax similar to that for creating regular indexes, but extended with the SPATIAL keyword. Currently, columns in spatial indexes must be declared NOT NULL.

Spatial indexes can be created when the table is created, or added after the fact like so:

  • with CREATE TABLE:

    1
    CREATE TABLE geom (g GEOMETRY NOT NULL, SPATIAL INDEX(g));
  • with ALTER TABLE:

    1
    ALTER TABLE geom ADD SPATIAL INDEX(g);
  • with CREATE INDEX:

    1
    CREATE SPATIAL INDEX sp_index ON geom (g);

For MyISAM tables, SPATIAL INDEX creates an R-tree index. For storage engines that support non-spatial indexing of spatial columns, the engine creates a B-tree index. A B-tree index on spatial values is useful for exact-value lookups, but not for range scans.

For more information on indexing spatial columns, see CREATE INDEX.

To drop spatial indexes, use ALTER TABLE or DROP INDEX:

Storage Engine Index Types

MariaDB官方文檔 Storage Engine Index Types

創建索引時,指向索引類型(index_type)定義,如BTREE``、HASHRTREE

更多關於索引類型的信息,如主鍵索引、惟一索引等,參見Getting Started with Indexes

Storage Engine Permitted Indexes
Aria BTREE, RTREE
MyISAM BTREE, RTREE
InnoDB BTREE
MEMORY/HEAP HASH, BTREE
NDB BTREE, HASH

BTREE通常是默認的索引類型。對於MEMORY表,HASH是默認的。TokuDB使用名爲fractal trees的特定數據結構,對並不完全適合內存的數據進行優化。

理解B-treeHASH數據結構,使用其索引中的數據結構,能幫助預測在不同存儲引擎上執行不同查詢,特別是MEMORY存儲引擎,可讓您選擇B-treeHASH索引。

Understanding the B-tree and hash data structures can help predict how different queries perform on different storage engines that use these data structures in their indexes, particularly for the MEMORY storage engine that lets you choose B-tree or hash indexes. B-Tree Index Characteristics

B-tree indexes

B-tree索引用於列比較,使用>>==>=<BETWEEN操作符,以及以常數開頭的LIKE比較。

B-tree indexes are used for column comparisons using the >, >=, =, >=, < or BETWEEN operators, as well as for LIKE comparisons that begin with a constant.

例如,查詢SELECT * FROM Employees WHERE First_Name LIKE 'Maria%';B-tree索引有用,而SELECT * FROM Employees WHERE First_Name LIKE '%aria';則沒用。

B-tree也允許搜索行的最左邊前綴。

B-tree indexes also permit leftmost prefixing for searching of rows.

Hash Indexes

相比較而言,Hash索引只能應用於等式比較,使用=<=>操作符。不能用於排序,無法提供2值之間有多少行數據給優化器。

Hash索引不允許最左前綴,只有整個索引可用。

Hash indexes do not permit leftmost prefixing - only the whole index can be used.

R-tree indexes

See SPATIAL for more information.

小結

  • B-tree索引用於列比較,以及以常數開頭的LIKE比較
  • Hash索引只能應用於等式比較,不允許最左前綴,只有整個索引可用。

Change Logs

  • 2016.03.19 23:05 Sat Asia/Beijing
    • 初稿完成

  • 2016.03.19 23:05 Sat
  • Note Location: Asia/Beijing
  • Writer: lempstacker