三 MySQL数据访问

3.1 单表访问方式

 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;

id 聚簇索引
key1 普通索引
key2 唯一索引
key3 普通索引
（key_part1, key_part2, key_part3）联合索引

const

直接通过主键等值查询数据，或者直接通过唯一索引等值查询数据

image_1ctendl4319v659s1dfoj6lssl16.png-36.4kB

image_1cthurrlpbhlotsjru1dsjrrl30.png-110.2kB

 explain select * from single_table where id = 2; explain select * from single_table where key2 =2;

ref

使用普通的二级索引等值查询，首先到二级索引B+树中获取所有记录，然后回表中获取所有的记录数据

二级索引列值为null，由于不管唯一索引还是普通索引都不会限制null的数量，因而只会是ref而不是const

联合索引中，只要能匹配最左原则也会是ref的访问方法

image_1ctf14vso11cdclsmc6ac8pru9h.png-109.5kB

 explain select * from single_table where key1 ='abc'; 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_or_null

查询条件中既有等值查询又包含null值查询

image_1ctf21uu8113m1ajm1rcitgf5eeco.png-122.5kB

 explain select * from single_table where key1 = 'abc' or key1 is null;

range

范围匹配（< , > , between, in, !=，is null ）

image_1cth9mkf41li1dad1tnd6dm5139.png-9.2kB

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

index

最长使用的访问方法，查询结果仅包含联合索引，仅需要遍历联合索引即可获得最终结果，此过程无需回表

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

all

无法使用索引的情况扫描全表获取最终的记录

3.2 数据的连接

数据库表的连接本质是将各个连接表中的数据取出来依次匹配的组合加入结果集返回给用户。

 mysql> SELECT * FROM t1; +------+------+ | m1   | n1   | +------+------+ |    1 | a    | |    2 | b    | |    3 | c    | +------+------+ 3 rows in set (0.00 sec)  mysql> SELECT * FROM t2; +------+------+ | m2   | n2   | +------+------+ |    2 | b    | |    3 | c    | |    4 | d    | +------+------+ 3 rows in set (0.00 sec)

创建两张表，分别插入三条记录.

两表连接查询时，

驱动表仅会查询一次，被驱动表会查询多次

  SELECT * FROM t1, t2;

image_1cql4ae7flug1itskat1ojgi7g3m.png-67.4kB

 SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';

t1.m1 > 1
t1.m1 = t2.m2 AND t2.n2 < 'd'

内连接

获取两张表中同时存在的记录

内连接中的on和where是等价的，因而可以不用写匹配条件
内连接的驱动表和被驱动表可以互相替换，得到的笛卡尔积

 SELECT * FROM t1, t2; -- 推荐写法 SELECT * FROM t1 INNER JOIN t2; SELECT * FROM t1 JOIN t2;

左外连接

保留驱动表的全部记录，对于无法在被驱动表中匹配的记录将用null代替

外连接必须写关联条件on
外连接的驱动表与被驱动表不能互相替换，否则会得到不同的结果

 select * from t1 left join t2 on t1.id = t2.id;

右外连接

模式等同于左外连接，只是互换驱动表和被驱动表的位置而已

连接原理

嵌套循环连接（Nested-Loop Join）

选择驱动表，使用与驱动表有关的过滤条件，选取代价最低的单表查询方法执行对驱动表的查询
对上一步中的每一条记录，都分别到被驱动表中查询匹配的记录

image_1ctsr5ui2cdk1jduqafm7p1d3426.png-129.4kB

基于块的嵌套循环连接（Block Nested-Loop Join）

目的：减少被驱动表的访问次数

方案：执行连接前申请一块固定大小的内存，将若干条驱动表查询结果集放入join buffer中，然后开始扫描被驱动表，批量匹配，减少对被驱动表的访问

image_1ctuhe3t71ahd10gn19917fo1nft4g.png-57.7kB

join buffer默认256kb
join buffer中只会存查询列表中的列以及匹配条件中的列

3.3 子查询优化

不相关子查询

 SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE s1.key2 = s2.key2);

先从外查询中获取一条记录，也就是从s1中获取一条记录
从s1表中找到s1.key2的列，然后执行子查询select common_field from s2 where a = s2.key2
根据子查询的查询结果来检测外层查询where子句的条件是否成立
依次执行第一步，重复以上步骤

物化表

如果不相关子查询的结果集太多，可能会导致无法有效使用索引，匹配时间过长（or语句太多），这时mysql内部会将结果集写入一个临时表中。该临时表的列就是子查询结果集中的列，写入临时表的记录会被去重。

image_1cvfj9up26i518t91li5ooq1r0u2d.png-84.9kB

image_1cvfjg3os1oh1e3o5c11dhd1odd2q.png-67.4kB

 SELECT * FROM s1      WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a'); // 重写后的SQL select s1.* from s1 inner join materialized_table on key1 = m_val;

这时查询优化器可以评估不同连接顺序需要的成本，选取成本最低的那种查询方式执行查询。

转化成EXISTS

 SELECT * FROM s1     WHERE key1 IN (SELECT key3 FROM s2 where s1.common_field = s2.common_field) OR key2 > 1000;      SELECT * FROM s1     WHERE EXISTS (SELECT 1 FROM s2 where s1.common_field = s2.common_field AND s2.key3 = s1.key1)          OR key2 > 1000;

exists 适合内查询比外查询数据大的情况
in 适合内查询比外查询数据少的情况

3.4 EXPLAIN查询优化

每条SQL经过执行优化器的优化后都会生成一个执行计划，通过EXPLAIN可以查看这个具体的执行计划。不仅可用于select语句中，还可以用在update、delete语句中

 mysql> EXPLAIN SELECT 1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ |  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ 1 row in set, 1 warning (0.01 sec)

列名

描述

复杂SQL中每个select对应一个唯一的id

select_type

查询类型

table

表名

partitions

匹配的分区信息

type

单表的访问方法

possilbe_keys

可能用到的索引

key

实际上使用的索引

key_len

实际使用的索引长度

ref

当使用索引列等值查询时，与索引列进行等值匹配的对象信息

rows

预估的需要读取的记录条数

filtered

某个表经过搜索条件过滤后剩余记录条数的百分比

extra

其他额外的信息

table

explain每条记录都对应着一张单表，单表名称可能是真实表也可能是中间虚拟出来的表

 mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                 | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+ |  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9688 |   100.00 | NULL                                  | |  1 | SIMPLE      | s2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9954 |   100.00 | Using join buffer (Block Nested Loop) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+ 2 rows in set, 1 warning (0.01 sec)

id

每出现一个select就会分配一个id，如果是关联查询则id选用驱动表

 mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                 | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+ |  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9688 |   100.00 | NULL                                  | |  1 | SIMPLE      | s2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9954 |   100.00 | Using join buffer (Block Nested Loop) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+ 2 rows in set, 1 warning (0.01 sec)

如果是关联查询，则id会使用驱动表

 mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a'; +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ |  1 | PRIMARY     | s1    | NULL       | ALL   | idx_key3      | NULL     | NULL    | NULL | 9688 |   100.00 | Using where | |  2 | SUBQUERY    | s2    | NULL       | index | idx_key1      | idx_key1 | 303     | NULL | 9954 |   100.00 | Using index | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ 2 rows in set, 1 warning (0.02 sec)

如果是子查询则会有多个id

 mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key3 FROM s2 WHERE common_field = 'a'); +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key      | key_len | ref               | rows | filtered | Extra                        | +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+------------------------------+ |  1 | SIMPLE      | s2    | NULL       | ALL  | idx_key3      | NULL     | NULL    | NULL              | 9954 |    10.00 | Using where; Start temporary | |  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1      | idx_key1 | 303     | xiaohaizi.s2.key3 |    1 |   100.00 | End temporary                | +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+------------------------------+ 2 rows in set, 1 warning (0.00 sec)

如果是union则会多出一个all

 mysql> EXPLAIN SELECT * FROM s1  UNION SELECT * FROM s2; +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           | +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ |  1 | PRIMARY      | s1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9688 |   100.00 | NULL            | |  2 | UNION        | s2         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9954 |   100.00 | NULL            | | NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary | +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 3 rows in set, 1 warning (0.00 sec)

select_type

表示此次查询的类型，可根据此类型快速判断此次查询的概要

名称

描述

SIMPLE

普通的查询，不包含UNION或子查询

PRIMARY

UNION、UNION ALL中最左边的查询

UNION

UNION、UNION ALL中除了最左边的查询，其余查询都是此类型

UNION RESULT

UNION、UNION ALL中临时表的去重工作，临时表的类型

SUBQUERY

不相关子查询

DEPENDENT SUBQUERY

UNION、UNION ALL中那除了最左边的那个小查询之外，其余的小查询的select_type的值就是DEPENDENT UNION

DERIVED

物化方式生成的派生表

type

表示此次查询的访问方法

system

当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，如MYISAM、Memory等。InnoDB存储引擎没有存储统计数据，因而每次访问统计数据都会计算获取。

const

根据主键或者唯一索引等值匹配

 mysql> EXPLAIN SELECT * FROM s1 WHERE id = 5; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ |  1 | SIMPLE      | s1    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.01 sec)

eq_ref

连接查询时，被驱动表时通过

主键或者唯一索引

等值匹配

 mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id; +----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+ | id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref             | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+ |  1 | SIMPLE      | s1    | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL            | 9688 |   100.00 | NULL  | |  1 | SIMPLE      | s2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | xiaohaizi.s1.id |    1 |   100.00 | NULL  | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+ 2 rows in set, 1 warning (0.01 sec)

ref

普通二级索引

等值匹配查询

fulltext

全文索引，用处不大

ref_or_null

普通二级索引等值匹配查询，索引列的值包含null

 mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL; +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type        | possible_keys | key      | key_len | ref   | rows | filtered | Extra                 | +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+ |  1 | SIMPLE      | s1    | NULL       | ref_or_null | idx_key1      | idx_key1 | 303     | const |    9 |   100.00 | Using index condition | +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+ 1 row in set, 1 warning (0.01 sec)

index_merge

使用了多个索引

 mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a'; +----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+ | id | select_type | table | partitions | type        | possible_keys     | key               | key_len | ref  | rows | filtered | Extra                                       | +----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+ |  1 | SIMPLE      | s1    | NULL       | index_merge | idx_key1,idx_key3 | idx_key1,idx_key3 | 303,303 | NULL |   14 |   100.00 | Using union(idx_key1,idx_key3); Using where | +----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+ 1 row in set, 1 warning (0.01 sec)

unique_subquery

类似于两表中的被驱动表的eq_ref方法，主要用于包含in子查询语句中，查询优化器将in子查询优化为exists子查询并且子查询可以使用主键进行等值匹配

 mysql> EXPLAIN SELECT * FROM s1 WHERE key2 IN (SELECT id FROM s2 where s1.key1 = s2.key1) OR key3 = 'a'; +----+--------------------+-------+------------+-----------------+------------------+---------+---------+------+------+----------+-------------+ | id | select_type        | table | partitions | type            | possible_keys    | key     | key_len | ref  | rows | filtered | Extra       | +----+--------------------+-------+------------+-----------------+------------------+---------+---------+------+------+----------+-------------+ |  1 | PRIMARY            | s1    | NULL       | ALL             | idx_key3         | NULL    | NULL    | NULL | 9688 |   100.00 | Using where | |  2 | DEPENDENT SUBQUERY | s2    | NULL       | unique_subquery | PRIMARY,idx_key1 | PRIMARY | 4       | func |    1 |    10.00 | Using where | +----+--------------------+-------+------------+-----------------+------------------+---------+---------+------+------+----------+-------------+ 2 rows in set, 2 warnings (0.00 sec)

index_subquery

类似于unique_subquery，只不过子查询中使用普通索引匹配

range

使用索引获取范围区间的记录

 mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c'); +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ |  1 | SIMPLE      | s1    | NULL       | range | idx_key1      | idx_key1 | 303     | NULL |   27 |   100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.01 sec)  mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'a' AND key1 < 'b'; +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ |  1 | SIMPLE      | s1    | NULL       | range | idx_key1      | idx_key1 | 303     | NULL |  294 |   100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)

index

使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是index。

 mysql> EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a'; +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                    | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+ |  1 | SIMPLE      | s1    | NULL       | index | NULL          | idx_key_part | 909     | NULL | 9688 |    10.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)

all

全表扫描，效率最低

posssible_keys 和 key

查询过程可能使用了哪些索引，key表示实际使用了哪个索引。possible_keys并不是越多越好，查询优化器会计算查询成本计算最佳索引，所以尽量删除那些用不到的索引。

 mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a'; +----+-------------+-------+------------+------+-------------------+----------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys     | key      | key_len | ref   | rows | filtered | Extra       | +----+-------------+-------+------------+------+-------------------+----------+---------+-------+------+----------+-------------+ |  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1,idx_key3 | idx_key3 | 303     | const |    6 |     2.75 | Using where | +----+-------------+-------+------------+------+-------------------+----------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.01 sec)

key_len

表示实际使用的索引的最大长度

固定长度的索引列，实际存储空间的最大长度就是索引长度。对于指定字符集的变长类型索引列来说，varchar(100),utf8, 索引长度为100*3字节
如果索引列可以存储null值，则索引长度+1
对于变长字段来说，都会有2个字节的空间来存储变长列的实际长度

-- int 占用4字节，并且不能为null mysql> EXPLAIN SELECT * FROM s1 WHERE id = 5; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.01 sec) -- int 占用4字节，并且可以为null mysql> EXPLAIN SELECT * FROM s1 WHERE key2 = 5; +----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | const | idx_key2 | idx_key2 | 5 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) -- key1 为varchar(100) ,且可以为null，并且可变长，3 * 100 + 1 + 2 mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a'; +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 8 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)

ref

当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery其中之一时，ref列展示的就是与索引列作等值匹配的东东是个啥，比如只是一个常数或者是某个列

rows

如果访问方式为all表示预计需要扫描的行数

如果访问方式为index时表示预计扫描的索引记录行数

 mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z'; +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ |  1 | SIMPLE      | s1    | NULL       | range | idx_key1      | idx_key1 | 303     | NULL |  266 |   100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)

我们看到执行计划的rows列的值是266，这意味着查询优化器在经过分析使用idx_key1进行查询的成本之后，觉得满足key1 > 'z'这个条件的记录只有266条。

filtered

 mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND common_field = 'a'; +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+ | id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                              | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+ |  1 | SIMPLE      | s1    | NULL       | range | idx_key1      | idx_key1 | 303     | NULL |  266 |    10.00 | Using index condition; Using where | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+ 1 row in set, 1 warning (0.00 sec)

从执行计划的key列中可以看出来，该查询使用idx_key1索引来执行查询，从rows列可以看出满足key1 > 'z'的记录有266条。执行计划的filtered列就代表查询优化器预测在这266条记录中，有多少条记录满足其余的搜索条件，也就是common_field = 'a'这个条件的百分比。此处filtered列的值是10.00，说明查询优化器预测在266条记录中有10.00%的记录满足common_field = 'a'这个条件。

担保访问时filtered无意义，

 mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a'; +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key      | key_len | ref               | rows | filtered | Extra       | +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------+ |  1 | SIMPLE      | s1    | NULL       | ALL  | idx_key1      | NULL     | NULL    | NULL              | 9688 |    10.00 | Using where | |  1 | SIMPLE      | s2    | NULL       | ref  | idx_key1      | idx_key1 | 303     | xiaohaizi.s1.key1 |    1 |   100.00 | NULL        | +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec)

从执行计划中可以看出来，查询优化器打算把s1当作驱动表，s2当作被驱动表。我们可以看到驱动表s1表的执行计划的rows列为9688， filtered列为10.00，这意味着驱动表s1的扇出值就是9688 × 10.00% = 968.8，这说明还要对被驱动表执行大约968次查询。

extra

No tables used

查询语句中无from子句时

 mysql> EXPLAIN SELECT 1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ |  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ 1 row in set, 1 warning (0.00 sec)

Impossible where

查询条件中的where子句永远为false

 mysql> EXPLAIN SELECT * FROM s1 WHERE 1 != 1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra            | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ |  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ 1 row in set, 1 warning (0.01 sec)

No Matching min/max row

查询条件中有min或者max聚集函数，但是无符合where的搜索条件

 mysql> EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'abcdefg'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+ | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                   | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+ |  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No matching min/max row | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+ 1 row in set, 1 warning (0.00 sec)

Using index

查询列表中及搜索条件中只包含属于某个索引的列，也就是可以使用索引覆盖

 mysql> EXPLAIN SELECT key1 FROM s1 WHERE key1 = 'a'; +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra       | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ |  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1      | idx_key1 | 303     | const |    8 |   100.00 | Using index | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)

Using index condition

有些搜索条件中出现了索引列，但是不能使用索引

 mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%b';   +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+   | id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |   +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+   |  1 | SIMPLE      | s1    | NULL       | range | idx_key1      | idx_key1 | 303     | NULL |  266 |   100.00 | Using index condition |   +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+   1 row in set, 1 warning (0.01 sec)

Using where

使用全表扫描来执行某个表的查询，并且该语句的where子句中有针对该表的搜索条件时展示

 mysql> EXPLAIN SELECT * FROM s1 WHERE common_field = 'a'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ |  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9688 |    10.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.01 sec)

Using join buffer (Block Nested Loop)

在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为其分配一块名叫join buffer的内存块来加快查询速度，也就是我们所讲的基于块的嵌套循环算法，比如下边这个查询语句：

 mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+ |  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9688 |   100.00 | NULL                                               | |  1 | SIMPLE      | s2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9954 |    10.00 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+ 2 rows in set, 1 warning (0.03 sec)

Using filesort

对结果集中的记录进行排序可以使用索引，排序操无法使用索引，只能在内存或磁盘中排序的方式。

注意：filesort耗费性能，应尽量使用索引排序

 mysql> EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ |  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9688 |   100.00 | Using filesort | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ 1 row in set, 1 warning (0.00 sec)

Using temporary

查询过程中使用了临时表，比如去重、排序

注意:应尽量减少临时表的创建，更多使用索引

 mysql> EXPLAIN SELECT DISTINCT common_field FROM s1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ |  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9688 |   100.00 | Using temporary | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 1 row in set, 1 warning (0.00 sec)

3.5 InnoDB中的BufferPool

CPU中的内存与磁盘的访问速度相差很大，为了解决两者速度不一致问题，mysql引入了BufferPool。默认的BufferPool=128M。

内部构成

控制块+缓存页

image_1d15mh3d4oadq0e1qpme22u8i61.png-47.4kB

Free链表

free链表-记录了当前空闲的缓存页，当需要缓存页数据时会直接从该链表中获取然后删除该节点
缓存页的hash处理-为了更高效的定位到当前的缓存页，内部建立的hash索引，key= 表空间号+页号，value= 缓存页

image_1d155te021bmgjt09mo1lln17dum.png-132.6kB

Flush链表

flush链表-也就是发生了数据变更的缓存页，这些页需要在合适的时间从内存刷新到磁盘中，结构类似于free链表

image_1d1589dpqmt5v1849s7614nu23.png-133.5kB

LRU链表

Buffer Pool的空间毕竟只有128M，如果数据库中的记录特别多肯定会出现缓存不够用的情况，此时就需要采取刷新缓存的策略

LRU（Leatest Recent Userd，最近最少使用）

可能出现场景

预读

如果顺序访问了某个区（extend，默认存储64个页，16K*64=1M）页面超过了系统预设的阈值（56），就会触发异步从下一个区中全部的页面加载到Buffer Pool中
全表扫描

项目中扫描了全表，如果该表的数据量很大，短时内就会将Buffer Pool耗尽，下一次查询时会将Buffer Pool全部刷新一遍，严重影响性能

解决方案

将整个LRU链表分为热数据（young data）和冷数据（old data），默认old占据37%

image_1d15fb53d2lf13ovglg1rnv1h2n2g.png-116.5kB

初次加载某个页面的数据时，会将它缓存到old区域的头部，这样就解决了预读后没有使用到页面时占用热点区域的问题
全表扫描时，首先会将数据缓存至old区域，随后访问数据时就会将该页面从old区域移动至young区域内。同时，mysql在控制块内部记录了第一次放入缓存的时间，如果后续的访问时间与第一次访问时间在某个阈值内（默认1s）才会将数据从old移送到young中

异步刷新

为了进一步提高mysql的性能，采用了异步刷新模式不定时的刷新脏数据到磁盘中

从LRU链表的冷数据中刷新一部分页面到磁盘中
从flush链表中刷新一部分页面到磁盘中

四事务

4.1 事务属性简介

原子性（Atomicity）

每个操作都是一个不可分割的整体，要不同时成功，要不同时失败（同生共死）

隔离型（Isolation）

两个操作间的状态转换互相独立

一致性（Consistency）

数据库中的数据符合现实世界的约束，永远只有事务前事务后两种状态，不会出现事务中的中间状态

持久性（Durability）

操作执行结束后，对数据库的影响是持久的，也就是影响结果写入磁盘中

 -- 开启事务 begin start transaction 执行SQL语句  -- 提交事务 commit  -- 回滚事务 rollback  -- 可以在事务中设置保存点，用于回滚至指定位置 savepoint s1  rollback to s1

4.2 redo日志

redo日志会把事务在执行过程中对数据库所做的所有修改都记录下来，在之后系统崩溃重启后可以看把事务所做的任何修改都恢复出来，对应

持久性

image_1d36k7d3412oo1c0qcuuben12l79.png-31.3kB

type：日志类型

space：表空间

page number：页号

data：日志的具体内容

Mini-Transaction

对数据库底层页面中的一次原子访问称做Mini-Transaction（MTR），比如修改一次Max Row Id，向某个索引对应的B+树插入一条记录等。

一个事务可以包含多条语句，一个语句包含多个mtr，一个mtr包含多个redo日志

image_1d4hgjr7t4es1v2mf2b1bt51rf95b.png-27.6kB

redo日志缓冲区

与前面为了解决读取磁盘速度过慢而引出Buffer Pool类似，写入redo日志也不是直接写入磁盘，而是在首先写入redo日志缓冲区（log buffer），默认为16Mb，这片内容区域被划分为若干个连续的512byte redo log block

log buffer 空间不足
事务提交
后台线程自动刷新
正常关闭服务器

磁盘上的redo日志不止一个，而是以一个日志文件组的形式出现，依次写入ib_logfile0、ib_logfile2…

image_1d4mu4s6f7491l7l1jcc6pc1rbk16.png-49.7kB

image_1d4njgt351je21kitk7u1gbioa46j.png-64.9kB

Log Sequence Number

日志序列号，记录了已经写入redo的日志量

image_1d4v37u011jhc1rpa1fpi5a82ca9.png-99.3kB

flushed_to_disk_lsn 已经写入到空闲链表中的数据序列号

checkpoint 已经写入到磁盘中的数据序列号

4.3 undo日志

为了保证事务的

原子性

，mysql内部采用了版本链的模式存储每一次操作的快照，在需要回滚时回退到指定的版本。

每个数据记录中都额外存储了事务id
只读事务中，在第一次创建临时表的增、删、改时才会分配事务id
读写事务中，在第一次执行增、删、改时才会分配事务id

image_1d62h05ffsum114cn05koa1igbp.png-45.1kB

Insert对应的undo日志结构

image_1d65eln739ukbei9pgid81pr57o.png-112.4kB

roll_point会将每次的undo日志连接起来，形成一个版本链

image_1d65h98l3qve1ekb13epv4f37685.png-70.6kB

Delete操作对应的undo日志

delete mask —- 仅将记录的delete_mask置为1
purse— 事务提交后，从正常链表中删除该记录，将它移入垃圾链表中

image_1d6abjg9n1kocq5d10j6250164v9.png-62.8kB

image_1de3lkv7bkem1n1717tj1jbq669p.png-71.5kB

Update操作对应的undo日志

不更新主键

如果每个列在更新前后占用的存储空间相同，直接在原记录的基础上进行更新

如果任意一个列的存储空间发生变化，则首先会直接删除该条记录，然后插入新纪录

更新主键

首先将旧记录进行delete_mask删除，然后根据更新后各列的值创建一条新纪录，并将其初入到聚簇索引中

UNDO日志类型

TRX_UNDO_INSERT== 执行insert语句或者update中更新主键时场景
TRX_UNDO_UPDATE==执行update或者delete语句时场景

执行事务时，最多可能产生四个UNDO日志链表

普通表的insert undo 链表
普通表的update undo 链表
临时表的insert undo 链表
临时表的update undo 链表

image_1d7bg5o7c3t11nch988lj51hsl9.png-106.8kB

多个事务的undo日志

4.4 MVCC

Multi Version Concurrent Control(多版本并发控制)

数据隔离级别

脏读—>一个事务读取到另一个事务未提交的数据

不可重复读—>一个事务多次访问同一条数据出现不同的结果

幻读—>特指范围查询时，前后两次访问获取的条目数量不同

隔离级别

脏读

不可重复读

幻读

Read Uncommited

√

Read Commited

√

Repeatable Read

√

Serialzable

 mysql> SHOW VARIABLES LIKE 'transaction_isolation'; +-----------------------+-----------------+ | Variable_name         | Value           | +-----------------------+-----------------+ | transaction_isolation | REPEATABLE-READ | +-----------------------+-----------------+ 1 row in set (0.02 sec)

原理

MVCC主要依靠roll_pointer来实现多版本数据的管理

1）初始数据

 mysql> SELECT * FROM hero; +--------+--------+---------+ | number | name   | country | +--------+--------+---------+ |      1 | 刘备   | 蜀      | +--------+--------+---------+ 1 row in set (0.07 sec)

image_1d8oab1ubb7v5f41j2pai21co19.png-22.4kB

2）两个事务分别更新同一条数据

image_1d8obbc861ulkpt3no31gecrho16.png-92.3kB

image_1d8po6kgkejilj2g4t3t81evm20.png-81.7kB

Read View

RC和RR底层实现原理，包含三部分

m_ids 表示生成ReadView时当前系统中活跃的读写事务的事务id列表
min_trx_id 表示在生成ReadView时当前系统中活跃事务的最小id，通常为min（m_ids）
max_trx_id 表示生成ReadView时系统中国农应该分配给下一个事务的id，通常为max（m_ids）+ 1
creator_trx_id 表示生成ReadView时的事务id

访问规则

visited_trx_id = creator_trx_id ，表示当前事务重入，该版本可以被当前事务访问
visited_trx_id < min(min_trx_id ) , 表示被访问事务于当前事务前已提交，该版本可被当前事务访问
visited_trx_id >= max(min_trx_id ),表示被访问事务在当前事务之后，可访问当前事务
min(min_trx_id ) < = visited_trx_id < max(min_trx_id ),

如果visited_trx_id in m_ids ，表示事务活跃，该版本不可访问

否则，该版本可被访问

READ COMMITTED —— 每次读取数据前都生成一个ReadView

REPEATABLE READ —— 在第一次读取数据时生成一个ReadView

小结

MVCC（Multi-Version Concurrency Control ，多版本并发控制）指的就是在使用READ COMMITTD、REPEATABLE READ这两种隔离级别的事务在执行普通的SELECT操作时访问记录的版本链的过程，这样子可以使不同事务的读-写、写-读操作并发执行，从而提升系统性能。READ COMMITTD、REPEATABLE READ这两个隔离级别的一个很大不同就是：生成ReadView的时机不同，READ COMMITTD在每一次进行普通SELECT操作前都会生成一个ReadView，而REPEATABLE READ只在第一次进行普通SELECT操作前生成一个ReadView，之后的查询操作都重复使用这个ReadView就好了。

4.5 锁

一致性读（快照读）

也叫一致性无锁读或快照读，指利用MVCC的读取操作。在READ COMMITEF、REPEATEANLE READ隔离级别下，所有的SELECT语句都是快照读。快照读不会对表中的任何记录加锁，其他事务可以自由的对表中的记录做改动。

共享读（S锁）

Shared Locks，在事务要读取一条记录时，需要先获取该条激记录的S锁

别的事务可以继续获得该表的S锁
别的事务可以继续获得表中某些记录的S锁
别的事务不可能继续获得该表的X锁
别的事务不可以继续获得表中的某些记录的X锁

排他锁（X锁）

Exclusive Locks,在事务要改动一条记录的数据时，需要先获取该记录的X锁

别的事务不可以继续获得该表的S锁
别的事务不可以继续获得该表中某些记录的S锁
别的事务不可以继续获得该表的X锁
别的事务不可以继续获得该表中某些记录的X锁

加S锁 select * from t_user lock in share mode; # 加X锁 select * from t_user for update;

意向共享锁（IS锁）

Intention Shared Lock，当事务准备在某条记录上加S锁时，首先需要在表中加一个IS锁。IS锁的提出仅仅是为了在之后加表级别的S锁或X锁时判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录

意向独占锁（IX锁）

Intention Excluded Lock,当事务准备在某条记录上加X锁，需要首先在表中加一个IX锁。。IX锁的提出仅仅是为了在之后加表级别的S锁或X锁时判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录

兼容性

X锁

IX锁

S锁

IS锁

X锁

IX锁

√

S锁

√

IS锁

√

行锁

 mysql> SELECT * FROM hero; +--------+------------+---------+ | number | name       | country | +--------+------------+---------+ |      1 | l刘备      | 蜀      | |      3 | z诸葛亮    | 蜀      | |      8 | c曹操      | 魏      | |     15 | x荀彧      | 魏      | |     20 | s孙权      | 吴      | +--------+------------+---------+ 5 rows in set (0.01 sec)

Record Locks（普通锁）

image_1d9etchk0136o49c13t81bn81d3m.png-88.1kB

普通锁分为普通共享锁和普通的排他锁。当一个事务获得一条记录的普通S锁后，其他事务可以继续获取该记录的普通S锁，不可以获得该记录的普通X锁；当一个事务获得一条记录的普通X锁后，既不可以获得该记录的普通S锁，可不可可获得该记录的普通X锁

Gap Locks（间隙锁）

为了解决Repeatable Read隔离级别下的幻读而提出，某条记录加了Gap锁后，不允许其他事务在记录前边的间隙插入新记录。

image_1d9etbrl938j1v2h1mmjh42e0f9.png-85.7kB

image_1d9mnrs6o18f61jel8juvm4o127o.png-98.5kB

Next Key Locks

next_key锁室普通记录锁和gap锁的合体，既能保护该条记录又可以阻止别的事务将新记录插入被保护记录的间隙中。

image_1d9mo12guolk1amr1bde1ahjgti85.png-98.6kB

Insert Intention Locks

插入意向锁，当一个事务在插入一条记录时需要判断下插入位置是否有gap锁，如果有在阻塞直到gap锁被释放。在等待过程中在内存中会生成一个锁结构，表明有事务想在某个间隙中插入新记录。

image_1d9p8551i7sf1hfpbtb1eiu6vf9.png-104.5kB

深入理解MySQL原理(下)

三 MySQL数据访问

3.1 单表访问方式

const

ref

ref_or_null

range

index

all

3.2 数据的连接

内连接

左外连接

右外连接

连接原理

嵌套循环连接（Nested-Loop Join）

基于块的嵌套循环连接（Block Nested-Loop Join）

3.3 子查询优化

相关子查询

不相关子查询

物化表

转化成EXISTS

3.4 EXPLAIN查询优化

table

id

select_type

type

system

const

eq_ref

ref

fulltext

ref_or_null

index_merge

unique_subquery

index_subquery

range

index

all

posssible_keys 和 key

key_len

ref

rows

filtered

extra

No tables used

Impossible where

No Matching min/max row

Using index

Using index condition

Using where

Using join buffer (Block Nested Loop)

Using filesort

Using temporary

3.5 InnoDB中的BufferPool

内部构成

Free链表

Flush链表

LRU链表

可能出现场景

解决方案

异步刷新

四 事务

4.1 事务属性简介

4.2 redo日志

Mini-Transaction

redo日志缓冲区

Log Sequence Number

4.3 undo日志

Insert对应的undo日志结构

Delete操作对应的undo日志

Update操作对应的undo日志

不更新主键

更新主键

UNDO日志类型

4.4 MVCC

数据隔离级别

原理

Read View

小结

4.5 锁

四事务

加S锁 select * from t_user lock in share mode; # 加X锁 select * from t_user for update;