MySQL优化in语句 mysql 语句优化

文章目录

• 写在前面
• 对比开始

• 1. count(1) VS count(*)
• 2. where num is null VS where num = 0
• 3. in VS exists
• 4. 关于计数和排序的问题

• 阶段总结一
• 阶段总结二

写在前面

本文针对Mysql中的各种语句进行对比，给出尽可能真实的语句分析，帮助大家优化自己的sql查询。本文所用测试数据库为官方数据库。

在做MySQL数据库测试时苦于没有测试数据，除了线上导一小段数据、或者利用sysbench生成测试数据外，官方其实也提供了了一套名为：Employees
Sample Database的测试库（该测试库含有6个表，总计4百万数据记录）

在此之前还要先改变变量，让profile工作，如下图所示

对比开始

1. count(1) VS count(*)

初步结论：count(*)好一些

仔细探究，进行对比

以下是借用的别人的分析

当表的数据量大些时，对表作分析之后，使用count(1)还要比使用count()用时多了！
从执行计划来看，count(1)和count()的效果是一样的。 但是在表做过分析之后，count(1)会比count()的用时少些（1w以内数据量），不过差不了多少。
如果count(1)是聚索引,id,那肯定是count(1)快。但是差的很小的。
因为count(),自动会优化指定到那一个字段。所以没必要去count(1)，用count()，sql会帮你完成优化的 因此：count(1)和count()基本没有差别！

实际上我们可以看到这样是问题蛮大的，count（1）做聚索引的耗时甚至会更长。

总结：count(*)更推荐

2. where num is null VS where num = 0

以前看到有建议说

应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描

这里发现问题好像不太对劲，赶紧分析一波对于is null 来说，我们发现并没有execute time

而对于 = 0 来说，执行时间占了绝大多数。

我们这里不信邪，专门跑进去改了个null再测试，发现结果反转了

总结：对于主键来说，null一定是巨省的（你都主键了还null肯定不可能啊），但是对于非主键来说，一定是先把null赋值为一个有意义的值（例如0）来说更能够提升效率

3. in VS exists

MySQL的中的in语句是把外表和内表作hash连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询.

这里我们选择employees和salaries这两个表来进行测试
employees的尺寸是30万，salaries是284万

实验结果：in比exists略久一些，如果两个表的尺寸差距更大会更明显一些。

所以如下这个建议比较合理：

如果查询的两个表大小相当，那么用in和exists效率差别不大。
如果两个表中一个较小，一个较大，则子查询表大的用exists，子查询表小的用in。

4. 关于计数和排序的问题

我们将会从比较以下三个例子开始探究，每个例子取10次平均值作为实验结果

1. select count(*) from salaries;
2. select * from salaries order by salary asc limit 5;
3. select * from salaries order by salary asc limit 15;

这是某些实验结果

这是经过我的测验以后的实验结果（十次加权平均后差不多）

阶段总结一

count是一定比排序快的（因为排序一定要先基于count）

继续探究为什么排15个比排5个要快，我们从2-20个一次进行了实验，得到了如下的数据表格

次数	执行平均秒数
2	0.83
3	0.78
4	0.75
5	0.72
6	0.70
7	0.69
8	0.68
9	0.68
10	0.68
11	0.67
12	0.67
13	0.67
14	0.65
15	0.65
16	0.65
17	0.65
18	0.65
19	0.65
20	0.65
100	0.66
1000	0.67
10000	0.66

针对2和20我们详细分析其状况

发现还真是cpu的执行时间不同。

那咋办呢，看源码吧

源码阅读指南

下面给出trace的路径（github上直接找的网页方便读者调用）

1. https://github.com/mysql/mysql-server/blob/8.0/sql/sort_param.h
2. https://github.com/mysql/mysql-server/blob/8.0/sql/sorting_iterator.cc
3. https://github.com/mysql/mysql-server/blob/8.0/sql/sorting_iterator.h
4. https://github.com/mysql/mysql-server/blob/8.0/sql/sql_sort.h

好的千辛万苦终于找到了，让我们分析一波

Descriptor for a merge chunk to be sort-merged.
A merge chunk is a sequence of pre-sorted records, written to a
temporary file. A Merge_chunk instance describes where this chunk is stored
in the file, and where it is located when it is in memory.
It is a POD because we read/write them from/to files (but note,
only m_file_position and m_rowcount are actually used in that
situation).
We have accessors (getters/setters) for all struct members.

这是说这里的读写排序是分磁盘和内存的

The result of Unique or filesort; can either be stored on disk
(in which case io_cache points to the file) or in memory in one
of two ways. See sorted_result_in_fsbuf.
Note if sort_result points into memory, it does not own the sort buffer;
Filesort_info does.
TODO: Clean up so that Filesort / Filesort_info / Filesort_buffer /
Sort_result have less confusing overlap.

然后发现源码里面没做overlap的有关工作，什么意思呢，就是我临时建一个表时，会先塞到当前已经有的堆后面，然后先分配（堆上分配效率问题就和当前的数据块情况关系很大了）

IO_CACHE *io_cache{nullptr};

  /**
    If the entire result fits in memory, we skip the merge phase.
    We may leave the result in the parent Filesort_info's filesort_buffer
    (indicated by sorted_result_in_fsbuf), or we may strip away
    the sort keys, and copy the sorted result into a new buffer.
    Unique always uses the latter.
    This new buffer is [sorted_result ... sorted_result_end]
    @see save_index()
   */
  bool sorted_result_in_fsbuf{false};
  unique_ptr_my_free<uchar> sorted_result{nullptr};
  uchar *sorted_result_end{nullptr};

  ha_rows found_records{0};  ///< How many records in sort.
};

/**
  A class wrapping misc buffers used for sorting.
 */

这里的IOsort我们暂时不看，因为取出来的本身量级不大。

/**
  Put all room used by freed buffer to use in adjacent buffer.
  Note, that we can't simply distribute memory evenly between all buffers,
  because new areas must not overlap with old ones.
*/
template <typename Heap_type>
void reuse_freed_buff(Merge_chunk *old_top, Heap_type *heap) {
  typename Heap_type::iterator it = heap->begin();
  typename Heap_type::iterator end = heap->end();
  for (; it != end; ++it) {
    if (old_top->merge_freed_buff(*it)) return;
  }
  DBUG_ASSERT(0);
}

哦豁！终于发现了，这里直接在后面继续加，然后再free之前的空间。熟悉操作系统的人应该可以知道，你这样做必定会产生大量碎片，随后的overlap就是瓶颈了。

阶段总结二

是因为

1. overlap的处理源码没做
2. 可能是查询小于8时，刚好这个数据块的cache line就不足够，所以就一级缓存写回了（流水覆盖掉），二级缓存暂时不考虑。然而查询大于8时，由于分配字节有窄占位问题（我看一条记录大概是200B左右，10条大概是2m，刚好符合cache line至少是我的cache line大小），所以预取了icache，导致加速。