mysql 查询自定义 mysql查询设置默认值

title: Mysql查询

date: 2020-01-16 21:29:13

categories: 数据库

tags:

- mysql

- 单表查询

- 连接查询

- 执行计划

description: MySQL Server有一个称为查询优化器的模块，一条查询语句进行语法解析之后就会被交给查询优化器来进行优化，优化的结果就是生成一个所谓的执行计划

MySQL Server有一个称为查询优化器的模块，优化的结果就是生成一个所谓的执行计划，这个执行计划表明了应该使用哪些索引进行查询，表之间的连接顺序是啥样的，最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询，并将查询结果返回给用户。先来瞅瞅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;

对于单表查询来说，大致分为两种：

使用全表扫描

使用索引进行查询，但又根据情况分为多种类型，mysql把执行查询语句的方式成为访问方法

下面介绍详细的几种类型：

const：通过主键或者唯一二级索引列来定位一条记录的访问方法定义为：const，也就是这种查询只需要常数时间

SELECT * FROM single_table WHERE id = 1438;

ref: 由于普通二级索引不具备唯一性，所以有可能匹配多条记录，从而导致多次回表查询,注意唯一二级索引不限制null的数量，所以对于null的查询最多是ref

SELECT * FROM single_table WHERE key1 = 'abc';

ref_or_null: 有时候需要不仅匹配某个值，还查找NULL记录

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

range: MySQL把这种利用索引进行范围匹配的访问方法称之为：range。

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

index: mysql把可以直接采用采用遍历二级索引记录的执行方式称之为index，如下：

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

它的查询只有三个列，但条件并不是最左边的列，这个时候我们既不能用索引，也不应该用全表查询，而是通过遍历二级索引来查找(不需要回表查询)

all： 全表扫描

索引合并

一般情况下我们最多只会用到单个二级索引，但如果查询条件包括多个二级索引的时候，可能会触发索引合并

Intersection合并

SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';

如上的查询就有可能过程如下：

从idx_key1二级索引对应的B+树中取出key1 = 'a'的相关记录

从idx_key3二级索引对应的B+树中取出key3 = 'b'的相关记录。

取交集再回表查询信息

之所以这样做，因为索引查询是一个顺序I/O,而回表查则是一个随机I/O,有两种特殊情况：

对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只出现匹配部分列的情况

主键列可以是范围匹配，为什么？因为二级索引查询出的结果是按照主键排序的，所以可以取交集

Union合并

与Intersection取交集相对应，Union取的是并集比如：

SELECT * FROM single_table WHERE key1 = 'a' OR key3 = 'b'

查询限制也如Intersection类似，如果是范围就不能合并，为什么？不是取并集吗？因为这两个子集取出来时并不是排好序的，无法合并。那么我们可以先排好序再合并吗，这样就可以利用并集了？可以，这就是Sort-Union。

Sort-Union合并

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

我们可以这样:

先根据key1 < 'a'条件从idx_key1二级索引总获取记录，并按照记录的主键值进行排序

再根据key3 > 'z'条件从idx_key3二级索引总获取记录，并按照记录的主键值进行排序

因为上述的两个二级索引主键值都是排好序的，剩下的操作和Union索引合并方式就一样了

为什么没有Sort-Intersetcion的说法？这是假设Sort-Union的适用场景是根据搜索条件搜索出来的记录数比较少，所以排序消耗不大，而交集是因为一个条件出来的比较多，所以排序消耗较大

连接查询

对于两表连接来说，驱动表只会被访问一遍，但被驱动表却要被访问到好多遍，具体访问几遍取决于对驱动表执行单表查询后的结果集中的记录条数。伪代码如下：

for each row in t1 { #此处表示遍历满足对t1单表查询结果集中的每一条记录

for each row in t2 { #此处表示对于某条t1表的记录来说，遍历满足对t2单表查询结果集中的每一条记录

for each row in t3 { #此处表示对于某条t1和t2表的记录组合来说，对t3表进行单表查询

if row satisfies join conditions, send to client

}

}

}

想想一下，如果被驱动表每次都是全表扫描，那么当数据大时将是一个灾难，怎么办？

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

使用索引加快连接速度

如上，在第一步假设我们查出来ti.m1>1的只有一条，那么第二步就是执行以下查询：

SELECT * FROM t2 WHERE t2.m2 = 2 AND t2.n2 < 'd';

所以如果我们对被驱动表也就是t2的m2字段加入索引，那么查找的方式将是const或者ref，mysql将这个过程称为eq_ref,如果在n2字段中加入，索引则过程变为range

基于块的嵌套循环查询

当被驱动表中的数据非常多时，每次访问被驱动表，被驱动表的记录会被加载到内存中，在内存中的每一条记录只会和驱动表结果集的一条记录做匹配，之后就会被从内存中清除掉。然后再从驱动表结果集中拿出另一条记录，再一次把被驱动表的记录加载到内存中一遍，周而复始，驱动表结果集中有多少条记录，就得把被驱动表从磁盘上加载到内存中多少次，显然这个I/O代价也太大了，因此mysql提出了join buffer的概念：

join buffer就是执行连接查询前申请的一块固定大小的内存，先把若干条驱动表结果集中的记录装在这个join buffer中，然后开始扫描被驱动表，每一条被驱动表的记录一次性和join buffer中的多条驱动表记录做匹配，因为匹配的过程都是在内存中完成的，所以这样可以显著减少被驱动表的I/O代价。

基于成本的优化

成本介绍

我们之前老说MySQL执行一个查询可以有不同的执行方案，它会选择其中成本最低，案去真正的执行查询。在MySQL中一条查询语句的执行成本是由下边这两个方面组成的

I/O成本：从磁盘到内存这个加载的过程损耗的时间称之为I/O成本

CPU成：读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为CPU成本。

mysql读取一个页面花费的成本是1.0，读取以及检测一条记录是否符合搜索条件的成本默认是0.2

单表查询的成本

基于成本的优化步骤：

根据搜索条件，找出所有可能使用的索引

计算全表扫描的代价

计算使用不同索引执行查询的代价

对比各方案，找出成本最低的一个

我们使用一个例子来分析

SELECT * FROM single_table WHERE

key1 IN ('a', 'b', 'c') AND

key2 > 10 AND key2 < 1000 AND

key3 > key2 AND

key_part1 LIKE '%hello%' AND

common_field = '123';

找出可能使用的索引： key1、key2. (其中key3没有和常数比较，key_part1 没有使用常数或者前缀)

计算全表扫描的代价： IO成本+CPU成本，因此我们需要两个信息，一个是聚簇索引占用的页数，一个是表中的记录数

使用show table status like 'single_table';查询表状态

Rows，记录条数(10059)

Data_length(1589248) =页数量 x 每个页的大小，我们默认用的16kb的页面大小，所以页面数=1589248 ÷ 16 ÷ 1024 = 97

所以全表扫描的IO代价是97x1.0+1.1=98.1，其中1.1是微调值，cpu成本是：10059x0.2+1.0=2012.8，总成本是2110.9

计算不同索引执行查询的代价：

idx_key2的成本分析： key2 > 10 AND key2 < 1000，对于二级索引+回表的方式，mysql主要依赖：

范围区间数量，mysql查询优化器粗暴的认为读取索引的一个范围区间和读取一个页面的成本以及回表操作都是是相同的，所以对于(10,1000)这个区间，成本是1x1.0=1.0

需要回表的记录数： 步骤一，先根据key2=10这个条件，找出最左边的记录，步骤二，再根据key=1000找出最右边的记录，然后统计出记录条数。如果超过10页，则计算10页的平均值再算出记录之间的页面的数量即可，根据上述算法测得idx_key2在区间(10, 1000)之间大约有95条记录

所以读取这95条二级索引的cpu的成本就是95 x 0.2 + 0.01 = 19.01，回表操作的IO成本为95x1.0

然后再计算其他的匹配条件cpu成本 95x0.2，一共是134.01

使用idx_key1执行查询的成本分析，key1 IN(a,b,c)，也就是三个单点区间35 + 44 + 39 = 118

IO成本：3.0 + 118 x 1.0 = 121.0

CPU成本： 118 x 0.2 + 0.01 + 118 x 0.2 = 47.21

基于索引统计数据的成本计算

MySQL把这种通过直接访问索引对应的B+树来计算某个范围区间对应的索引记录条数的方式称之为index dive

但如果单点区间太多，比如in条件有上万的参数，那么就有上万次index dive操作，性能消耗太大，于是提供给了一个系统变量eq_range_index_dive_limit，也就是如果IN语句中的参数小于这个值，将使用index dive的方式，但大于或等于的时候就不是了，而是使用索引统计数据来估算

mysql> SHOW INDEX FROM single_table;
+--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type
+--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+-----------
| single_table | 0 | PRIMARY | 1 | id | A | 10059 | NULL | NULL | | BTREE
| single_table | 0 | idx_key2 | 1 | key2 | A | 10059 | NULL | NULL | YES | BTREE
| single_table | 1 | idx_key1 | 1 | key1 | A | 10059 | NULL | NULL | YES | BTREE
| single_table | 1 | idx_key3 | 1 | key3 | A | 10059 | NULL | NULL | YES | BTREE
| single_table | 1 | idx_key_part | 1 | key_part1 | A | 10059 | NULL | NULL | YES | BTREE
| single_table | 1 | idx_key_part | 2 | key_part2 | A | 10059 | NULL | NULL | YES | BTREE
| single_table | 1 | idx_key_part | 3 | key_part3 | A | 10059 | NULL | NULL | YES | BTREE
+--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+---------

我们重点关注Cardinality属性，也就是基数的意思，表示列中不重复值的个数。

那么根据索引统计计算就是这种方式： 行数/Cardinality =一个值的重复次数，简单粗暴,那么10000个单点区间就是10000X1=10000条回表记录

连接查询的成本

连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 x 单次访问被驱动表的成本

我们把对驱动表进行查询后得到的记录条数称之为驱动表的扇出

对于外连接，因为驱动表和被驱动表是固定的所以只需要：

分别为驱动表和被驱动表选择成本最低的访问方法。

对于内连接来说，驱动表和被驱动表是可以互换的，所以需要考虑不同顺序的查询