【官方】MariaDB Optimization and Indexes 中文版(六)

ACMUG征集原创技术文章。详情请添加 A_CMUG或者扫描文末二维码关注我们的微信公众号。有奖征稿,请发送稿件至:acmug@acmug.com。
3306现金有奖征稿说明:知识无价,劳动有偿,ACMUG特约撰稿人有奖回报计划(修订版)


作者: MariaDB官方授权资词翻译组翻译

资词翻译组,由数名数据库技术爱好者组成,旨在传播技术,帮助他人,提升自我。目前的成员有:田丽芳,强昌金,王竹峰,侯军伟,吕智超,刘启荣,周彦伟,古雷。
译事三难:信、达、雅。信者,真也,真者,不伪也;达者,至也,至者,无过无不及也;雅者,文学性也,文学性者,当雅则雅当俗则俗也。我们深知能达成此三事,绝非一日之功,亦非常人所能。然,苟利国家生死以,岂因祸福避趋之。我们还是希望竭尽所能,不遗余力,做一点微小的工作,提高姿势水平。由于能力有限,水平一般,有错误不妥之处,还请批评指正,希望能得到大家的资词。

资词翻译组获得MySQL Server团队和MariaDB官方授权,翻译相关技术文章。


外键

概览

外键是可以用于强制数据完整性的约束。 它由表中被成为子表的列(或一组列)组成,该表引用称为父表的列(或一组列)。 如果使用外键,MariaDB会执行一些检查,以强制执行某些完整性规则。有关更详尽的说明,请参阅关系数据库:外键

外键只能用于支持它们的存储引擎。 默认InnoDB和废弃的PBXT支持外键。

分区表不能包含外键,并且不能由外键引用。

语法

注意:MariaDB接受ALTER TABLE和CREATE TABLE语句中的REFERENCES子句,但该语法不执行任何操作。 MariaDB只是解析它,不返回任何错误或警告,以与其他DBMS兼容。 但是,只有下面描述的语法可以创建外键。

外键是通过CREATE TABLEALTER TABLE创建的。定义必须遵从下面的语法:

[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子句(如果指定)用在错误消息中,并且在数据库中必须是唯一的。

子表中的列必须是索引,或索引的最左部分。 不支持前缀索引(因此,TEXTBLOB列不能用作外键)。 如果MariaDB自动为外键创建索引(因为它不存在并且没有显式创建),它的名称将是index_name

引用的列必须是PRIMARY KEY或UNIQUE索引。

外键列和引用列都可以是PERSISTENT列。 但是,在这种情况下不允许ON UPDATE CASCADE,ON UPDATE SET NULL,ON DELETE SET NULL子句。

外键列和引用列必须是相同类型或类似类型。 对于整数类型,大小和符号也必须相同。

父表和子表必须使用相同的存储引擎,并且不能是TEMPORARY或分区表。 它们可以是同一个表。

约束

如果存在外键,则子表中的每一行必须与父表中的行匹配。 多个子行可以匹配相同的父行。 如果所有其外键值与父表中的父行值相同,则子行与父行匹配。 但是,如果至少有一个外键值为NULL,则该行没有父行,但仍是允许的。

MariaDB执行某些检查,以强制确保数据完整性:

  • 尝试在子表中插入不匹配的行(或以使其变成不匹配的行的更新方式更新匹配的行)会产生1452错误(SQLSTATE ‘23000’)。
  • 当父表中的一行被删除并且至少有一个子行存在时,MariaDB执行一个依赖于外键的ON DELETE子句的操作。
  • 当外键引用的列中的值更改并且至少存在一个子行时,MariaDB会执行一个操作,该操作取决于外键的ON UPDATE子句。
  • 尝试删除由外键引用的表会产生1217错误(SQLSTATE ‘23000’)。
  • 对包含一个或多个外键的表执行TRUNCATE TABLE,会被作为没有WHERE的DELETE执行,从而对每行执行外键约束。

ON DELETEON UPDATE的行为如下:

  • RESTRICT:防止父表上的更改。 语句以 1451 错误(SQLSTATE ‘23000’)终止。 这是ON DELETE和ON UPDATE的默认行为。
  • NO ACTION:RESTRICT的同义词。
  • CASCADE:允许更改并在子表上传播。 例如,如果父行被删除,子行也被删除; 如果父行的ID更改,子行的ID也将更改。
  • SET NULL:允许更改,并且子行的外键列设置为NULL。
  • SET DEFAULT:仅适用于PBXT。 类似于SET NULL,但外键列设置为其默认值。 如果默认值不存在,则会产生错误。

外键触发的删除或更新操作不会激活触发器,也不会计入Com_deleteCom_update状态变量。

可以通过设置foreign_key_checks服务器系统变量为0来禁用外键约束。这会加快大量数据的插入。

元数据

Information SchemaREFERENTIAL_CONSTRAINTS表包含有关外键的信息。 各个列在KEY_COLUMN_USAGE表中列出。

InnoDB特定的nformation Schema表还包含有关InnoDB外键的信息。 外键信息存储在INNODB_SYS_FOREIGN中。 有关各列的数据存储在INNODB_SYS_FOREIGN_COLS中。

获取有关表外键的信息的最人性化的方法有时是SHOW CREATE TABLE语句。

限制

在MariaDB中外键有以下限制:

  • 目前,仅InnoDB支持外键。
  • 不能与视图一起使用。
  • 不支持SET DEFAULT操作。
  • 外键操作不会激活触发器。

示例

让我们看一个例子。 我们将创建一个author表和一个book表。 两个表都有一个名为id的主键。 book也有一个由称为author_id的字段组成的外键,它引用的是author表主键。 外键约束名称是可选的,但我们会指定它,因为我们希望它出现在错误消息里:fk_book_author

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;

现在,我们如果我们尝试插入一本没有作者的书,我们会收到一个错误:

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)

这个错误描述非常清晰。

现在,让我们尝试插入两个作者和他们的书:

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。 这应该传播删除,清空已删除的作者的书籍:

DELETE FROM author WHERE name = 'H.P. Lovecraft';

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

我们还指定了ON UPDATE RESTRICT。 如果存在一个子行的话,这应该阻止我们修改作者的id(外键引用的列):

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)

参考

索引统计信息

索引统计信息如何为Query优化器提供帮助

MariaDB查询优化器依靠索引的详情去决定如何优化一条语句。

索引统计信息为优化器的决策提供了信息。设想你需要查询一个号码,你需要在电话本和地址簿之间进行选择。如果地址簿中包含了较少的记录,大多数情况下你会选择地址簿。

现在设想如果你的地址簿的条目数是电话本的两倍,那你的查询速度会很慢。查询优化器的工作过程和这个类似,获得一个实时准确的统计信息是至关重要的。

Value Groups

统计信息主要基于索引中同一个值组成一个集合。在主键中,每个索引项都是唯一的,所以每个集合的大小都是1。在一个非唯一索引中,可能会存在多个记录对与一个索引项。最差的情况就是一个索引项对应一个很大的集合,比如在一个bool类型上建立的索引。

MariaDB对集合大小使用很多,比如一个表中含有100行,20个含有相同索引项的集合,集合的平均大小就是5。

但是平均大小有时候会被极端扭曲,经常会发生在NULL值上。一个包含100行记录的表可能会包含19个大小都是1的集合,另外81行记录都是NULL。MariaDB可能会认为平均集合大小是5然后选择使用这个索引,然后会需要去遍历这81行记录,可能就会比其他索引花费了更长的时间。

NULL值的处理

针对NULL值的问题有三种主要的处理方法:

  1. 索引中的NULL值被放置在同一个集合中(nulls_equal)。通常下这种方法都没有问题,但是如果表中包含有大量的null值,则会导致集合的平均大小被拉高,查询优化器在选择索引的时候不会选择该索引。这是XtraDB/InnoDB/MyISAM的默认方法。
  2. Nulls_unequal是一个相反的方法,针对每个null值都含有一个独立的集合。因此集合的平均大小会被拉低,查询优化器可能会使用ref索引查询在不合适的场景下。这个是Aria存储引擎的默认选项。
  3. 在索引集合统计的时候忽略NULL值。

Null安全和普通的比较运算

比较运算符经常会被使用。如果两个值使用<=>进行比较(查看null-safe-equal,如果两个值都是NULL,返回1。如果使用=进行比较,两个比较值中只要包含NULL,则会返回NULL。

引擎独立的索引统计信息

MariaDB 10.0 引入了一个新的方法去获得索引统计信息,新的方法独立于存储引擎。详情查看引擎独立的统计信息


注:ACMUG收录技术文章版权属于原作者本人所有。如有疑问,请联系作者。

看完转发,手留余香。关注我们,一起进步。
关注ACMUG公众号,参与社区活动,交流开源技术,分享学习心得,一起共同进步。

发表评论