MySQL2_2单表查询

发表于 更新于 分类于

只涉及一张表的情况下查询语句的执行过程。

MySQL 查询优化器会解析 SQL 得到执行计划，然后按照执行计划中的顺序调用 InnoDB 的接口来执行查询。
分析查询最重要的是了解查询语句背后是怎么使用索引的，每种查询方法的效率都有所不同。

下面是一个示例建表语句：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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 AUTO_INCREMENT=73 DEFAULT CHARSET=utf8;

索引查询方法

根据优化器的结果不同，可能会生成多种查询方法，可以使用 explain 来查看一个 SQL 使用了哪种查询方法。

const

const查询
例子：

1
SELECT * FROM single_table WHERE id = 1438;

这种查询因为命中了聚簇索引，所以会直接用主键到聚簇索引中去查找。

1
SELECT * FROM single_table WHERE key2 = 3841;

这种通过 唯一(unique) 二级索引直接定位某几条数据的查询语句同样是非常快的，因此被称为 const。
但是等值的查询还有一个例外，如果查询的是 NULL 值的记录，则可能访问到多条记录而导致无法使用 const 查询方法：

1
SELECT * FROM single_table WHERE key2 IS NULL;

ref

ref查询
普通二级索引不限制索引列的唯一性，因此可能会检索到多条记录，然后再回表得到具体数据，这种情况相对 const 来说会稍微耗时一些，因此被称为 ref。
注意：
1、就算是唯一索引，NULL 值的匹配仍可能会匹配到多条，因此 key is null 这种形式的搜索会采用 ref 的访问方式。
2、如果查询中包含范围查询则不是 ref：

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

ref_or_null

ref_or_null查询
当使用二级索引而且既查某个常数又查 NULL 值记录时，会采用 ref_or_null 查询方式。

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

range

对索引进行范围查询：

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

index

要查询的列在二级索引内，但是又不是最左索引列，因此就算不能高效使用索引，InnoDB 也会优先从索引中遍历获取数据：

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

all

扫描聚簇索引，即全表扫描。

确定范围区间

1
SELECT * FROM single_table WHERE key2 > 100 AND common_field = 'abc';

如上 SQL，因为 common_field 字段没有索引，因此确定范围时没有作用，优化器会先使用 idx_key2 索引确定范围，然后回表查到数据后再用common_field = 'abc'过滤。

1
SELECT * FROM single_table WHERE key2 > 100 OR common_field = 'abc';

其中关联条件被我们改成了 OR，这会导致全表扫描。
为了直观地确定范围区间，我们可以把用不到索引的搜索条件直接替换成 TRUE：

1
SELECT * FROM single_table WHERE key2 > 100 OR TRUE;

显然，这条语句会触发全表扫描。
这种方法也可以应用到一些比较复杂的查询语句中：

1
2
3
4
SELECT * FROM single_table WHERE 
        (key1 > 'xyz' AND key2 = 748 ) OR
        (key1 < 'abc' AND key1 > 'lmn') OR
        (key1 LIKE '%suf' AND key1 > 'zzz' AND (key2 < 8000 OR common_field = 'abc')) ;

1、因为 where 语句中 key1、key2 都可以命中索引，因此候选的索引包括 idx_key1 和 idx_key2
2、假设使用 idx_key1 执行查询，将不涉及该索引的条件都化简：

1
SELECT * FROM single_table WHERE key1 > 'xyz';

3、假设使用 idx_key2 执行查询，同理可化简：

1
SELECT * FROM single_table WHERE TRUE;

因此优化器最终会采用 idx_key1。

索引合并

使用到多个索引来完成一次查询的执行方法被称为index merge，分 3 种情况：

Intersection 合并

某个查询可以使用多个二级索引，将从多个二级索引中查询到的结果取交集。

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

为什么使用 2 个索引查询 merge 而不是一个查完后再过滤其他条件？主要是因为二级索引的操作是顺序 I/O，而回表操作是随机 I/O，所以如果只读取一个二级索引时需要回表的记录数特别多，而读取多个二级索引之后取交集的记录数相对更少，因此当节省的因为回表而造成的性能损耗比访问多个二级索引带来的性能损耗更高时，读取多个二级索引后取交集比只读取一个二级索引的成本更低。
发生 Intersection 合并的条件：
1、针对主键的范围查询；
2、针对二级索引的等值匹配；
为什么范围查询不行？因为二级索引中是按索引列排序的，只有等值匹配时结果才会按 id 列排序，而求并集时
3、单独使用某个二级索引获取到的记录数太多，导致回表开销太大。

Union 合并

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

OR 相连的不同查询条件可能会使用到不同的索引，这种情况可能会使用到 Union 索引合并，具体的还分以下几种情况：
1、二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只出现匹配部分列的情况。
比如范围查询就无法使用 Union 合并。
2、主键列可以是范围匹配；
3、使用 Intersection 索引合并的搜索条件

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

Intersection 索引合并得到的是一个主键的集合，Union 合并可以将多个这样的主键集合取交集。

Sort-Union

Union 合并的条件比较苛刻，要求全部查询条件都是等值查询，比如下面的查询语句就无法使用 Union 索引合并：

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

因为按照两种查询条件得到的主键值并不是有序的，如果这两批主键值数量并不大，InnoDB 会再对这两批主键值进行排序，最后按照 Union 合并的方式进行合并。

联合索引

上面这个例子中，key1 和 key3 分别属于两个索引，如果把这两个列合一块搞一个联合索引，效率会更高。

count

count(*) 的实现方式

不同引擎对count(*)有不同的实现方式：

  • MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；
  • 而 InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

为什么 InnoDB 不把数字存起来？

因为MVCC的存在，对每个事务来说返回多少行都是不确定的。

InnoDB对count(*)的优化

普通索引树比主键索引树小很多，因此对于count(*)这样的操作，MySQL更倾向于找到最小的那棵树来遍历。

table_rows能替代count(*)吗？

show table status命令结果里也有一个table_rows用于显示这个表当前有多少行，但是这个值其实只是一个估计值，并不准。

有什么办法能替代count(*)？

为了替代count(*)，我们只能自己计数，也就是找一个地方存储表的行数。

  1. 用缓存计数
    优点是速度快，但是缺点是缓存系统可能会丢失更新，比如两个事务都读了一遍Redis的值，然后在操作后将最新的值覆盖上，但是先写入的事务所做的更新会被后来的事务覆盖，也就是说发生了丢失更新。
    而且，Redis的重启、宕机都是不可避免的，发生这种情况后，怎么保证计数不丢失呢？
    一般来说需要将数据再持久化到DB上，但是如果写DB成功、同步Redis失败了，又会导致二者的不一致。这种情况当然是有解决的办法，比如Redis异常重启后就到数据库里面单独执行一次count(*)获取真实的行数，再把这个值写回到Redis里就可以了。
    总而言之，因为与数据库并不在一个事务内，不能保证Redis与数据库的绝对一致，因此Redis并不能提供精确的计数，只能供粗略的计数。
  2. 用数据库计数
    不同于Redis，只要保证计数和插入/删除数据命令处于同一条SQL内，基于数据库的计数就是精确的。

不同count的区别

  1. count(*)
    count(*)并不会把全部字段取出来，而是专门做了优化，不取值，因为count(*)肯定不是null，直接按行累加即可。
  2. count(主键ID)
    InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。
  3. count(1)
    InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。
  4. count(字段)
    表示返回满足条件的数据行里面，参数字段不为NULL的总个数。
    如果字段被定义成了null，则这个操作相对count(主键ID)会更慢一些，因为server层还需要一行一行判断值是否为null，只有不是null的才累加。

所以结论是：按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count(*)，所以建议尽量使用 count(*)。

QA

什么时候会发生文件排序？如何优化

如果没有用到索引，InnoDB 排序前一般会先将数据加载到内存的sort_buffer中，或者由于数据量太大需要借助磁盘空间来存放中间结果，排序完后再将结果集返回给客户端，在 MySQL 中，这种在内存或磁盘上进行排序的方式被称为文件排序（filesort）。
order by 语句最好能使用到覆盖索引，因为索引本身就对记录进行了排序，并且需要注意索引字段的顺序。

什么是索引下推

1
SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';

上面这个条件中，优化器会先根据key1 > 'z'从二级索引idx_key1中定位到符合的二级索引记录，但是不会直接回表，而是先根据key1 LIKE '%a'找到匹配的记录再回表，这被称为索引条件下推。