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

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


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

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

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


存在nullable列的主键

假设有如下表结构:

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(以及MySQL 5.7之前的版本)会悄无声息的将其转换为NOT NULL属性的列,并且赋予一个值为0的默认值。

自从版本MariaDB 10.1.7开始,这样的列还是被转换为NOT NULL属性的,但不会给它设置默认值,如果我们随后插入数据时,没有显式的设置C2列,一个警告(或者,在严格模式下,就是一个错误)会产生,例如:

INSERT INTO t1() VALUES();
Query OK, 1 row affected, 1 warning (0.00 sec)
Warning (Code 1364): Field 'c2' doesn't have a default value

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

MySQL,从版本5.7开始,这样的CREATE TABLE语句都会报错。

在MariaDB 10.1.7中的行为,是符合SQL 2003的标准的。

在SQL-2003标准中,第II部分中的“Foundation”中说道:
11.7
Syntax Rules

5) 如果在分支中指定了PRIMARY KEY,那么对于在显式或者隐式的中的每一个,如果没有指定NOT NULL属性,那么在<column definition>中,NOT NULL属性是隐式的。

实质上,这就意味着,在PRIMARY KEY中包含的所有列,都会被自动转换为NOT NULL,标准更进一步指定:

11.5
General Rules

3) 当一个站点S被设置为它的默认值,

b) 当一个站点的数据描述符包括一个, 那么S就会被设置为所指定的值。

e) 否则,S就会被设置为NULL值。

这里不存在“没有默认值”这样的概念,反而在SQL标准中,每一个列都会有一个隐式的默认值NULL。然而在插入时,有可能会出现违反NOT NULL这样的约束,而MariaDB和MySQL会把这种列标记为“没有一个默认值”。最终结果是一样的——在插入时,必须要显式指定一个值,否则会失败。

MariaDB从版本10.1.7开始,采取了兼容标准的方式——即PRIMARY KEY中的部分列,被定义为nullable的列会自动获得一个NOT NULL约束,在插入时,必须要为这样的列指定值。MariaDB在版本10.1.7之前,都会自动赋予一个默认值0——这样的行为是不符合标准的。在CREATE TABLE时产生一个错误也是不符合标准的。

SHOW EXPLAIN

命令描述

MairaDB用户可以通过SHOW EXPLAIN命令来获取一个语句在指定线程下执行时的EXPLAIN信息(一个执行计划的描述信息)。

语法是:

SHOW EXPLAIN FOR <thread_id>;

这个语句执行之后,可以为运行在线程号为“thread_id”的线程下的语句产生EXPLAIN信息。这个thread id可以从SHOW PROCESSLIST中获得。

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信息对应的语句):

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

有可能出现的错误

执行上面的命令所指定的线程,必须要有一个正在执行的语句,并且已经准备好对应的执行计划。如果不是这样的情况,那输出结果如下:

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

在下面几种情况下,会出现上面的错误:

  1. 语句中指定的线程没有执行一个可以EXPLAIN的命令。
  2. 语句中指定的线程正在执行一个可以EXPLAIN的命令,但是目前还没有生成一个执行计划(比如所需要的表已经打开,并且锁已经获得,但执行计划还没有生成)。

在输出方面SHOW EXPLAIN和EXPLAIN的不同

背景

在MySQL中,语句EXPLAIN的执行过程,与被优化之后的真实语句的执行过程,有一些差别。不幸的是,已经引发了很多在EXPLAIN中的Bug。(举例说明,可以查看MDEV-326, MDEV-410, 虽然lp:1013343lp:992942不是直接关于EXPLAIN的,但是如果MySQL在语句执行的过程中不去尝试删除部分执行计划的话,这两个问题也不会存在了)。

SHOW EXPLAIN命令会检查一个运行中的SELECT,因此EXPLAIN SELECT所产生的输出结果就会有一些区别。我们会尽全力的保证,要么这些差别可以小到忽略不计,要么SHOW EXPLAIN的输出会比EXPLAIN的输出更接近于真实情况。

差异清单

  1. SHOW EXPLAIN有可能会出现Extra=’no matching row in const table’的情况,与之对应的是,EXPLAIN命令产生的是Extra=’Impossible WHERE …’。
  2. 对于存在子查询的语句,SHOW EXPLAIN可能会打印出select_type==PRIMARY的信息,而EXPLAIN会打印出select_type==SIMPLE,反之亦然。

所需权限

执行SHOW EXPLAIN命令所需要的权限,和执行SHOW PROCESSLIST命令所需要的权限是一样的。

参考

空间索引

描述

MyISAMAria表中,MariaDB能够使用类似于创建常规索引的语法来创建空间索引(R-tree索引),但是需使用`SPATIAL`关键字。当前,空间索引中的列必须声明为`NOT NULL`。

空间索引可以在创建表时建立,或者以后创建,如下所示:

  • 使用CREATE TABLE:
    CREATE TABLE geom (g GEOMETRY NOT NULL, SPATIAL INDEX(g));
  • 使用ALTER TABLE:
    LTER TABLE geom ADD SPATIAL INDEX(g);
  • 使用CREATE INDEX:
    CREATE SPATIAL INDEX sp_index ON geom (g);

对于MyISAM表,SPATIAL INDEX创建一个R-tree索引。对于支持在空间列上建立非空间索引的存储引擎,它创建B-tree索引。基于空间值的B-tree索引对于精确值查找很有用,但是对于范围扫描不是很有用。

有关空间列建立索引的更多信息,请参考CREATE INDEX

删除空间索引,使用ALTER TABLE或者 DROP INDEX

存储引擎索引类型

存储引擎索引类型是指在创建索引时指定的index_type。例如: BTREE,HASH或者RTREE。
更多关于常规类型的索引的信息,如主键,唯一索引等等。请访问:Getting Started with Indexes

存储引擎 允许的索引
Aria BTREE,RTREE
MyISAM BTREE,RTREE
InnoDB BTREE
MEMORY/HEAP HASH,BTREE
NDB BTREE,HASH

BTREE一般是默认的索引类型。对于MEMORY 表,HASH是默认的索引类型。TokuDB 使用称为分形树的特定的数据结构,该数据结构针对不完全适合存放在内存的数据进行了优化。

了解B-tree和hash的数据结构可以帮助预测不同的查询在不同的存储引擎上的执行,这些引擎在索引中使用了这些数据结构,特别是在允许你选择B-tree或者hash索引的MEMORY存储引擎中。

B-tree 索引

B-tree索引可以用于使用>,> =,=,> =,< 或者BETWEEN运算符的列比较。以及以常量开头的LIKE比较。
例如: 查询语句SELECT * FROM Employees WHERE First_Name LIKE 'Maria%'; 可以使用B-tree索引,然而查询语句SELECT * FROM Employees WHERE First_Name LIKE '%aria';不能使用B-tree索引。

B-tree索引还允许用于搜索行的最左前缀。

Hash 索引

相反,Hash索引只能用于等式比较,那些使用 = 或者 <=>的操作。Hash索引不能用于排序,并且不向优化器提供有关在两个值之间存在多少行的信息。

Hash索引不允许最左前缀-只能使用整个索引。

R-tree索引

更多信息请参考SPATIAL


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

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

发表评论