MySQL - 访问方法

2019-12-15 本文已影响0人 sunyelw

前言

最近在学MySQL，决定记录一下，能写多少写多少，不定时更新，加油。

正文

分几个部分来吧，大致如下：

字符集与比较规则
行格式与数据页
InnoDB索引
访问方法与连接
explain 与子查询优化
redo 与 undo 日志
MVCC 与锁

本文为第四部分 访问方法与连接

啥是访问方法？
MySQL是一个数据库，按行存储，MySQL根据你的SQL语句找到具体某行或某些行的执行方式，即为访问方法.

访问方法

访问方法大致可以分六种，那么我们怎么查看我们的SQL语句到底以上面方式执行的呢？

MySQL提供了一个explain语句，这里我们只需关注下输出列中的 type字段值就好。

建一张表来测试，下面出现的SQL都是基于此表

CREATE TABLE `single_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `key1` varchar(100) DEFAULT NULL,
  `key2` int(11) DEFAULT NULL,
  `key3` varchar(100) DEFAULT NULL,
  `key_part1` varchar(100) DEFAULT NULL,
  `key_part2` varchar(100) DEFAULT NULL,
  `key_part3` varchar(100) DEFAULT NULL,
  `common_field` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_key2` (`key2`),
  KEY `idx_key1` (`key1`),
  KEY `idx_key3` (`key3`),
  KEY `idx_key_part` (`key_part1`,`key_part2`,`key_part3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

下面一种一种来看下。

一、conf

主键列或唯一二级索引的不为NULL的等值查询

唯一二级索引或聚簇索引
唯一二级索引不限制 NULL 的数量, 匹配到多列就不是 conf 方式了
唯一二级索引的访问需要先在自己的B+树上拿到主键列的值, 再回表查询
如果主键或唯一二级索引由多列组成, 那么需要按顺利一一等值匹配

只有精确匹配到一条记录, 才是 conf 访问方式

下面看两个例子

聚簇索引

mysql>
mysql> explain select * from single_table where id = 100;
+----+-------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table        | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | single_table | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

唯一二级索引

mysql>
mysql> explain select * from single_table where key2 = 100;
+----+-------------+--------------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table        | partitions | type  | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | single_table | NULL       | const | idx_key2      | idx_key2 | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+--------------+------------+-------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

二、ref

二级索引列与常数等值比较

非唯一索引，有可能匹配到多条连续记录，注意这里不包含NULL的等值匹配
并不一定走查询列的二级索引，取决于走二级索引列会匹配到多少记录，回表多少次，其成本是否大于全表扫描~~~

有一种特殊情况：如果二级索引列有多列，那么不一定非要每列都等值匹配，只需要靠左开始匹配就有可能是ref方式访问

// 靠左
mysql>
mysql> explain select * from single_table where key_part1 = 'one_part1';
+----+-------------+--------------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table        | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | single_table | NULL       | ref  | idx_key_part  | idx_key_part | 303     | const |    1 |   100.00 | NULL  |
+----+-------------+--------------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

// 不靠左
mysql>
mysql> explain select * from single_table where key_part2 = 'one_part1';
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | single_table | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |    10.00 | Using where |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

三、ref_or_null

这个很好理解，就是在ref的基础上加一个关于 IS NULL 的条件

mysql>
mysql> explain select * from single_table where key_part1 = 'one_part1' or key_part1 is null;
+----+-------------+--------------+------------+-------------+---------------+--------------+---------+-------+------+----------+-----------------------+
| id | select_type | table        | partitions | type        | possible_keys | key          | key_len | ref   | rows | filtered | Extra
  |
+----+-------------+--------------+------------+-------------+---------------+--------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | single_table | NULL       | ref_or_null | idx_key_part  | idx_key_part | 303     | const |    2 |   100.00 | Using index condition |
+----+-------------+--------------+------------+-------------+---------------+--------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (1.55 sec)

形成了两个连续的记录区间

四、range

聚簇索引或二级索引的区间查询

单点区间
范围区间

mysql>
mysql> explain select * from single_table where id > 10;
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table        | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | single_table | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 1000 |   100.00 | Using where |
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>
mysql> explain select * from single_table where key2 < 10;
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table        | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | single_table | NULL       | range | idx_key2      | idx_key2 | 5       | NULL |   10 |   100.00 | Using index condition |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

再看一个例子

mysql>
mysql> explain select * from single_table where key2 > 10;
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | single_table | NULL       | ALL  | idx_key2      | NULL | NULL    | NULL | 1000 |    98.90 | Using where |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

这里是 ALL，虽然还没讲到，但猜都猜到是全表扫描了，为什么这里会全表扫描而不是range方式呢？

因为 key2 > 10 这个条件下拎出来需要回表的记录太多，成本还不如直接扫描聚簇索引的小

五、index

查询列与条件列在索引列中

简单粗暴：

查询列在索引列中
条件列在索引列中

mysql>
mysql> explain select key_part1, key_part3 from single_table where key_part2 = 'one_part1';
+----+-------------+--------------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+ 
| id | select_type | table        | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                    | 
+----+-------------+--------------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+ 
|  1 | SIMPLE      | single_table | NULL       | index | NULL          | idx_key_part | 909     | NULL | 1000 |    10.00 | Using where; Using index | 
+----+-------------+--------------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+ 
1 row in set, 1 warning (0.00 sec)

为什么一定是二级索引？

精确匹配主键列是 conf
范围匹配主键列是 range

六、all

最后一个就是全表扫描了，直接扫描聚簇索引，是最耗时的那种访问方式。

这里就简单介绍下这几种访问方法，下面看看MySQL中的连接。

连接

平时查询涉及多张表的情况不要太多，这些多张表之间的查询就被称为连接查询

连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加入结果集

一、笛卡尔积

连接还没有问世的时候，我们查询多张表怎么玩？

mysql>
mysql> select * from t1, t2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    1 | a    |    2 | b    |
|    2 | b    |    2 | b    |
|    1 | a    |    3 | c    |
|    2 | b    |    3 | c    |
|    1 | a    |    4 | d    |
|    2 | b    |    4 | d    |
+------+------+------+------+
6 rows in set (0.20 sec)

就是简单的把两张表的数据拼起来，在数学上有个名字叫做笛卡尔积

这种方式不管三七二十一，只有满足查询条件的列才会加入到最后的结果集中

二、内外连接

为了满足各种使用情况，MySQL提供了连接.

他们把涉及查询的多张表进行了划分 - 驱动表(外表)与被驱动表(内表)

玩法就是，先查驱动表，拿到的记录再去被驱动表进行二次筛选

两张表以上，可以把一张表当做外表，其他表均为内表，再递归处理

内连接

四种写法

SELECT * FROM t1 JOIN t2;

SELECT * FROM t1 INNER JOIN t2;

SELECT * FROM t1 CROSS JOIN t2;

SELECT * FROM t1, t2;

驱动表中的记录在被驱动表中找不到匹配的记录，该记录不会加入到最后的结果集
对于内连接来说，驱动表和被驱动表是可以互换的，并不会影响最后的查询结果

外连接

驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集
根据驱动表选取方式的不同，外连接分为2种
- 左外连接: 选取左侧的表为驱动表
- 右外连接: 选取右侧的表为驱动表
左(外)连接

SELECT * FROM t1 LEFT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件];

右(外)连接

SELECT * FROM t1 RIGHT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件];

外连接后必须接 on 子句
外连接后必须接 on 子句
外连接后必须接 on 子句

查询条件

where 子句
- 不论是内连接还是外连接，凡是不符合WHERE子句中的过滤条件的记录都不会被加入最后的结果集
on 子句
- 对于驱动表，都加入
  - 对于被驱动表，符合加入，不符合 NULL 填充
  - 对于被驱动表，符合加入，不符合 NULL 填充
  - 对于被驱动表，符合加入，不符合 NULL 填充
对于内连接, on 子句与 where 子句等价

一般情况下

把只涉及单表的过滤条件放到WHERE子句中
把涉及两表的过滤条件都放到ON子句中
一般把放到ON子句中的过滤条件也称之为连接条件

对于外连接来说，由于驱动表中的记录即使在被驱动表中找不到符合ON子句条件的记录时也要将其加入到结果集

关于具体查询遵循如下规则：

先拎出符合on子句的驱动表中记录, 如果 on 子句不涉及驱动表, 则为全部驱动表记录
- 再根据这些符合记录, 使用on子句匹配被驱动表中记录
  - 如果不涉及被驱动表, 则被驱动表全部加入
    - 匹配的被驱动表记录加入
    - 如果不匹配, 则null填充
剩余不匹配的驱动表记录, 使用null填充

涉及与匹配是两个非常值得多读几遍的词~

下面是一些测试的例子

被驱动表不匹配 on 子句

mysql> select * from t1 left join t2 on t2.m2 = 1;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    1 | a    | NULL | NULL |
|    2 | b    | NULL | NULL |
+------+------+------+------+
2 rows in set (0.00 sec)

被驱动表匹配 on 子句

mysql> select * from t1 left join t2 on t2.m2 = 2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    1 | a    |    2 | b    |
|    2 | b    |    2 | b    |
+------+------+------+------+
2 rows in set (0.00 sec)

驱动表不匹配 on 子句

mysql> select * from t1 left join t2 on t1.m1 = 3;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    1 | a    | NULL | NULL |
|    2 | b    | NULL | NULL |
+------+------+------+------+
2 rows in set (0.00 sec)

驱动表匹配部分 on 子句

mysql> select * from t1 left join t2 on t1.m1 = 1;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    1 | a    |    2 | b    |
|    1 | a    |    3 | c    |
|    1 | a    |    4 | d    |
|    2 | b    | NULL | NULL |
+------+------+------+------+
4 rows in set (0.00 sec)

连接查询

嵌套循环连接<Nested-Loop Join>
- 先驱动表
- 一条一条循环查找被驱动表

驱动表只访问一次，被驱动表访问次数取决于驱动表查询得到的记录条数

基于块的嵌套循环连接<Block Nested-Loop Join>
- 使用join buffer
- join_buffer_size配置默认 262144 字节, 最小 128字节,
- 当join buffer小到只能放一条记录时，就退化成了嵌套循环连接

索引永远都是一种值得考虑的优化方案

MySQL - 访问方法

前言

正文

访问方法

一、conf

主键列或唯一二级索引的不为NULL的等值查询

二、ref

二级索引列与常数等值比较

三、ref_or_null

四、range

聚簇索引或二级索引的区间查询

五、index

查询列与条件列在索引列中

六、all

连接

一、笛卡尔积

二、内外连接

内连接

外连接

连接查询

猜你喜欢

热点阅读

MySQL - 访问方法

前言

正文

访问方法

一、conf

主键列或唯一二级索引的不为NULL的等值查询

二、ref

二级索引列与常数等值比较

三、ref_or_null

四、range

聚簇索引或二级索引的 区间查询

五、index

查询列与条件列在索引列中

六、all

连接

一、笛卡尔积

二、内外连接

内连接

外连接

连接查询

猜你喜欢

热点阅读

聚簇索引或二级索引的区间查询