Mysql单表查询的执行方式

现有A、B、C三张表要将ABC三表关联查询，其中A是主表，BC是关联表，需求需要查询B或C中的候选键不为空的数据。

可以这样查询。

mysql查询语句的方式称之为 访问方法 或者 访问类型

CREATE TABLE single_table (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    UNIQUE KEY idx_key2 (key2),
    KEY idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

1、const：通过主键来定位记录

SELECT * FROM single_table WHERE id = 1438;

SELECT * FROM single_table WHERE key2 = 3841;

mysql通过主键或者唯一二级索引与常数的比较来定位一条记录就像火箭一样快。

const,意思是常量级别的，代价可以忽略不计。如果主键和唯一二级索引是多个值的话，每一列都需要与常量进行等值比较。const访问方式才有效。唯一二级索引对NULL没有限制。

2、ref普通二级索引列与常量进行比较

SELECT * FROM single_table WHERE key1 = 'abc';

普通二级索引没有唯一性质，所以查询到的记录有可能有很多条，所以回表的代价取决于二级索引的记录条数。将二级索引与常数等值比较称之为ref。

• 不论是普通的二级索引，还是唯一二级索引，它们的索引列对包含NULL值的数量并不限制，所以我们采用key IS NULL这种形式的搜索条件最多只能使用ref的访问方法，而不是const的访问方法。
• 对于某个包含多个索引列的二级索引来说，只要是最左边的连续索引列是与常数的等值比较就可能采用ref的访问方法，比方说下面这几个查询

SELECT * FROM single_table WHERE key_part1 = 'god like';

SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 = 'legendary';

SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 = 'legendary' AND key_part3 = 'penta kill';

• 但是如果最左边的连续索引列并不全部是等值比较的话，它的访问方法就不能称为ref了，比方说这样：

SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 > 'legendary';

3、ref_or_null

SELECT * FROM single_demo WHERE key1 = 'abc' OR key1 IS NULL;

先找出等于常量的二级索引和为NULL的二级索引，然后再根据主键id进行回标查询完整的用户记录。

4、range

SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79);

可以使用全表扫描的方式也可以通过二级索引 + 回表，如果用到二级索引就不光是等值比较了而是索引列需要匹配某个或某些范围的值。

• key2的值是1438
• key2的值是6328
• key2的值在38和79之间。

小贴士：此处所说的使用索引进行范围匹配中的 `索引` 可以是聚簇索引，也可以是二级索引。

5、index

SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';

key_part2不是并不是联合索引的最左侧列，所以无法使用ref方式或者range方式查询

• 它的查询列表只有3个列：key_part1, key_part2, key_part3，而索引idx_key_part又包含这三个列。
• 搜索条件中只有key_part2列。这个列也包含在索引idx_key_part中。

所以可以通过二级索引idx_key_part 先二级索引的记录查出key_part2 = 'abc'记录就可以了。

因为联合索引包含key_part2，由于二级索引记录比聚簇索记录小的多，而且这个过程也不用进行回表操作，，所以直接遍历二级索引比直接遍历聚簇索引的成本要小很多。

6、all

最直接的查询执行方式就是我们已经提了无数遍的全表扫描，对于InnoDB表来说也就是直接扫描聚簇索引，设计MySQL的大佬把这种使用全表扫描执行查询的方式称之为：all。