MySQL - 单表访问

时间：2024-03-29 19:13:49

单表访问

查询方式

MySQL查询的执行方式大致分为下边两种：

  • 使用全表扫描进行查询

    这种执行方式很好理解，就是把表的每一行记录都扫一遍嘛，把符合搜索条件的记录加入到结果集就完了。不管是啥查询都可以使用这种方式执行，当然，这种也是最笨的执行方式。

  • 使用索引进行查询

    因为直接使用全表扫描的方式执行查询要遍历好多记录，所以代价可能太大了。如果查询语句中的搜索条件可以使用到某个索引，那直接使用索引来执行查询可能会加快查询执行的时间。使用索引来执行查询的方式五花八门，又可以细分为许多种类：

    • 针对主键或唯一二级索引的等值查询
    • 针对普通二级索引的等值查询
    • 针对索引列的范围查询
    • 直接扫描整个索引

设计MySQL的大叔把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;

const

执行主键查询和唯一索引查询使用（查询过程是等值查询，不会出现范围查询）

image_1cthurrlpbhlotsjru1dsjrrl30.png-110.2kB

SELECT * FROM single_table WHERE id = 1438;
SELECT * FROM single_table WHERE key2 = 3841;

ref

根据索引查询时，只有叶节点查询结果是连续的

image_1ctf14vso11cdclsmc6ac8pru9h.png-109.5kB

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';

SELECT * FROM single_table WHERE key2 IS NULL;

range

根据索引查询时，非叶节点和叶节点查询结果是有范围的

image_1ctf21uu8113m1ajm1rcitgf5eeco.png-122.5kB

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

index

遍历二级索引

-- (key_part1, key_part2, key_part3)组成联合索引
SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';

all

全表扫描

索引合并

我们前边说过MySQL在一般情况下执行一个查询时最多只会用到单个二级索引，但不是还有特殊情况么，在这些特殊情况下也可能在一个查询中使用到多个二级索引，设计MySQL的大叔把这种使用到多个索引来完成一次查询的执行方法称之为：index merge，具体的索引合并算法有下边三种。

合并的前提是索引返回的主键值是有序的

Intersection合并

Intersection为交集

-- 二级索引key1返回的是key1值为'a'的主键值，在B+树中相同索引的排序方式是按主键大小排序，所以二级索引key1返回的主键是有序的，key3同理
SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';

SELECT * FROM single_table WHERE key1 = 'a' AND (key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c');

SELECT * FROM single_table WHERE id > 100 AND key1 = 'a';

合并过程就是归并排序

不能进行合并

-- 这种情况key1返回的值是无序的
SELECT * FROM single_table WHERE key1 > 'a' AND key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';

-- 联合索引返回的值也是无序的
SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a';

Union合并

交集

-- 前提也是返回主键有序
SELECT * FROM single_table WHERE key1 = 'a' OR key3 = 'b'

SELECT * FROM single_table WHERE key1 = 'a' OR ( key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c');

不能的情况

SELECT * FROM single_table WHERE key1 > 'a' OR (key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c');

SELECT * FROM single_table WHERE key1 = 'a' OR key_part1 = 'a';

Sort-Union合并

Union索引合并的使用条件太苛刻，必须保证各个二级索引列在进行等值匹配的条件下才可能被用到，比方说下边这个查询就无法使用到Union索引合并：

SELECT * FROM single_table WHERE key1 < 'a' OR key3 > 'z'

这是因为根据key1 < 'a'从idx_key1索引中获取的二级索引记录的主键值不是排好序的，根据key3 > 'z'从idx_key3索引中获取的二级索引记录的主键值也不是排好序的，但是key1 < 'a'和key3 > 'z'这两个条件又特别让我们动心，所以我们可以这样：

  • 先根据key1 < 'a'条件从idx_key1二级索引中获取记录，并按照记录的主键值进行排序
  • 再根据key3 > 'z'条件从idx_key3二级索引中获取记录，并按照记录的主键值进行排序
  • 因为上述的两个二级索引主键值都是排好序的，剩下的操作和Union索引合并方式就一样了。

并多了一步对二级索引记录的主键值排序的过程。

小贴士：

为啥有Sort-Union索引合并，就没有Sort-Intersection索引合并么？因为并集是两个子集的排序结果，排序前合并还是排序后合并都是一样的，但交集却会舍去一些元素，所以会影响效率

剩下的操作和Union索引合并方式就一样了。

并多了一步对二级索引记录的主键值排序的过程。

小贴士：

为啥有Sort-Union索引合并，就没有Sort-Intersection索引合并么？因为并集是两个子集的排序结果，排序前合并还是排序后合并都是一样的，但交集却会舍去一些元素，所以会影响效率

相关文章