6. 查询效率

5-4：count(字段)怎么计数？

1、如果这个“字段”是定义为 not null 的话

一行行地从记录里面读出这个字段，判断不能为 null，按行累加；

2、如果这个“字段”定义允许为 null

执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。

从引擎返回的字段会涉及到解析数据行，以及拷贝字段值的操作。

5-5：count(主键 id)怎么计数？

对于 count(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。

server 层拿到 id 后，判断是不可能为空的，就按行累加。从引擎返回的主键id 会涉及到解析数据行，以及拷贝字段值的操作。

5-5：count(1)怎么计数？

对于 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。

5-6：count(*)怎么计数？

对于count(*)来说，并不会把全部字段取出来，而是专门做了优化，不取值。

count(*) 肯定不是 null，按行累加。

这些count()的原则如下：

server 层要什么就给什么；

InnoDB 只给必要的值；

现在的优化器只优化了 count(*) 的语义为“取行数”，其他“显而易见”的优化并没有做。

6. 查询效率

6-1：order by 是怎样工作的？

example：

select city,name,age from t where city='杭州' order by name limit 1000;

涉及到用户语句的排序，mysql 会给每个线程分配一块内存用于排序，也就是 sort_buffer。

这条语句的执行逻辑是：

1、先初始化 sort_bufer

2、然后放入 city,name,age 字段，不断地由主键id索引到整行再到三个字段的值，匹配查找的值存入 sort_buff

超大数据的排序相同。

如果要记录的字段太长，这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差。

这时会换一个算法，叫做rowid排序，顾名思义，就是对主键 id 以及排序字段进行存放，这样就节省了空间，但是最后需要通过主键 id 去找到之前未取出的字段。对比全字段排序，rowid 排序多访问了一次表 t 的主键索引。

结论：

如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。

如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer 中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。

如果想要避免排序，可以建立对应字段的索引。

如果想要进一步避免主键 id 的回表查询，可以使用覆盖索引，这种情况的索引建立成本会比较大，需要你去自己权衡是否使用

6-2：如何正确地显示随机消息？

example：

英语学习 App 首页有一个随机显示单词的功能，也就是根据每个用户的级别有一个单词表，然后这个用户每次访问首页的时候，都会随机滚动显示三个单词。他们发现随着单词表变大，选单词这个逻辑变得越来越慢，甚至影响

到了首页的打开速度

1、order by rand() 实现

创建临时表→按主键顺序取出所有的单词，并给他们一个随机小数→初始化 sort_buffer，从内存临时表取出数据放入 sort_buffer，按随机数排序，取出前三个，总扫描行数是2×n+3

2、磁盘临时表

如果 sort_buffer_size 设置的小，就会使用到磁盘临时表用于辅助排序，当然 mysql 高版本使用了优先队列排序的方法，就是只取三个值，构成一个堆，然后不断把剩下的值输入，用于更新队列。

这个过程不需要临时文件，因此对应的 number_of_tmp_files 是 0。当然，如果堆的大小设置的很大， sort_buffer 放不下，就会舍弃优先队列，重新使用临时表+归并排序来实现，number_of_tmp_files 就不再为 0。

3、随机id方法

我们实际上并不需要真正的为每行赋予一个随机值，然后进行排序，这样的成本就太高了。

可以借鉴主键id的唯一性，生成一个介于主键id最小值和最大值之间的随机数，然后取不小于这个随机数的主键id 的那一行即可。总扫描行数是3，的那hi是如果主键id有空洞，就会导致选择不同行的概率不同，不是真正的随机

4、严格随机id方法

取出表的总行数，生成一个总行数对应的随机值，取不小于这个随机数的主键id的那一行，总扫描行数是C+Y+1

6-3：为什么SQL语句逻辑相同，性能却差异巨大？

并且在 t_modified 上建立了索引

你以为对字段生成了索引，应该会返回的很快，但是mysql有一个规则：就是如果对字段做了函数计算，就用不上索引了。

实际上，mysql使用的B+ 树提供的对于索引的快速定位能力，来源于同一层兄弟节点的有序性。对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能，转而进行全表扫描，所以运行就变慢了

案例二：隐式类型转换：

如果在限制语句中错误的将字符串与数字进行比较，mysql 会将可以转化成数字的字符串转化成数字，没法转换的话，就会变成数字 0，因此如果出现这种输入的失误，相当于调用了字符串转数字的函数，还是会使得优化器会放

弃走树搜索功能，进行全表扫描

案例三：隐式字符编码转换

如果执行两个表的联合查询，两个表通过外键进行联结，如果两个表使用的字符集不同，会对低水平的字符集执行升级转换函数，（相当于将int赋给double的这个思路）。

如果限制语句是 where d.tradeid=l.tradeid，如果d的字符集低水平，就会执行函数操作，使得优化器会放弃走树搜索功能，进行全表扫描

总结：对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能

6-4：为什么我只查一行的语句，也执行这么慢？

原因从大到小可分为三种情况

第一 MySQL 数据库本身被堵住了，比如：系统或网络资源不够

第二 SQL 语句被堵住了，比如：表锁，行锁等，导致存储引擎不执行对应的 SQL 语句

第三确实是索引使用不当，没有走索引，类比上一节

第四是表中数据的特点导致的，走了索引，但由于一致性读，需要回滚多次才能读取出当前事务对应的值。

6-5：为什么临时表可以重名？

在实际应用中，临时表一般用于处理比较复杂的计算逻辑。

由于临时表是每个线程自己可见的，所以不需要考虑多个线程执行同一个处理逻辑时，临时表的重名问题。在线程退出的时候，临时表也能自动删除，省去了收尾和异常处理的工作。

在 binlog_format='row’的时候，临时表的操作不记录到 binlog 中，也省去了不少麻烦，防止出现主备不一致的情况

这里说到的临时表是用户临时表，而不是内存临时表

6-5：group-by使用原则

如果对 group by 语句的结果没有排序要求，要在语句后面加 order by null；

尽量让 group by 过程用上表的索引，确认方法是 explain 结果里没有 Using temporary 和 Using filesort；