SQL篇

count(*)这么慢，我该怎么办

count实现方式

  • MyISAM总行数存在磁盘上, 执行 count(*) 时会直接返回, 效率高, 但是有 where 条件, 一样要扫描, 也没这么快
    • 不支持事务
  • InnoDB需要把数据一行一行地从引擎里面读出来，然后累积计数
    • 同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB表“应该返回多少行”也是不确定的。所以不能像MyISAM将行数存起来。

InnoDB是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。所以，普通索引树比主键索引树小很多。对于count(*)这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。因此，MySQL优化器会找到最小的那棵树来遍历。

在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。

show table status 命令返回行数 TABLE_ROWS 使用采样统计估算得来的，不准确。官方文档说误差可能达到40%到50%。

如果你现在有一个页面经常要显示交易系统的操作记录总数，到底应该怎么办呢？答案是，我们只能自己计数。

计数方法

  • 缓存系统Redis保存总行数，每插入一行Redis计数加1，每删除一行Redis计数减1。

    • 缓存系统丢失更新
    • Redis计数持久化存储，Redis异常重启后，从存储Redis数据地方读回来，刚刚加1的计数操作也会丢失。
    • 总之，将计数保存在缓存系统中的方式，还不只是丢失更新的问题。即使Redis正常工作，这个值还是逻辑上不精确的。

  • 在数据库保存计数，单独使用计数表

    • InnoDB是支持崩溃恢复不丢数据的
    • 计数表也支持“事务”，不会出现采用Redis计数时，在事务中计数对不上的问题
    • 从并发角度看，先插入操作记录，再更新计数表，减小行锁竞争

计数放在Redis里面，不能够保证计数和MySQL表里的数据精确一致的原因， 是 这两个不同的存储构成的系统，不支持分布式事务，无法拿到精确一致的视图。 而把计数值也放在MySQL中，就解决了一致性视图的问题。

思考，上述两种方式只适合全表总行数总计，不适合加where条件时使用 比如电商后台管理系统中有很多列表，数据量大，又有筛选条件，可以使用ES来解决

count(*)、 count(字段)、 count(主键id)、 count(1) 用法和性能

  • count(字段)

    • 如果这个“字段”是定义为not null的话，一行行地从记录里面读出这个字段，判断不能为null，按行累加；
    • 如果这个“字段”定义允许为null，那么执行的时候，判断到有可能是null，还要把值取出来再判断一下，不是null才累加。

  • count(主键id)

    • InnoDB引擎会遍历整张表，把每一行的id值都取出来，返回给server层。
    • server层拿到id后，判断是不可能为空的，就按行累加。

  • count(1)

    • InnoDB引擎遍历整张表，但不取值。
    • server层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。
    • 比count(主键id)快，不用解析数据行。

  • count(*)

    • 并不会把全部字段取出来，而是专门做了优化，不取值。
    • count(*)肯定不是null，按行累加。

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

“orderby”是怎么工作的

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

全字段排序

Extra字段中“Using filesort”表示需要排序

sort_buffer：MySQL给每个线程分配一块内存用于排序。

索引执行流程：

  1. 初始化sort_buffer，确定放入name、city、age这三个字段；

  2. 从索引city找到第一个满足city='杭州'条件的主键id，也就是图中的ID_X；

  3. 到主键id索引取出整行，取name、city、age三个字段的值，存入sort_buffer中；

  4. 从索引city取下一个记录的主键id；

  5. 重复步骤3、4直到city的值不满足查询条件为止（city!='杭州'），对应的主键id也就是图中的ID_Y；

  6. 对sort_buffer中的数据按照字段name做快速排序；

  7. 按照排序结果取前1000行返回给客户端。

用name排序动作来说明

  • 排序数据量小于sort_buffer_size，排序在内存中完成
  • 排序数据量太大，内存放不下，不得不利用磁盘临时文件辅助排序，外部排序
    • 外部排序一般使用归并排序算法
  • 查看OPTIMIZER_TRACE结果中number_of_tmp_files是否使用临时文件
    • number_of_tmp_files表示的是，排序过程中使用的临时文件数
    • number_of_tmp_files=12表示将需要排序的数据分成12份，每一份单独排序后存在这些临时文件中。然后把这12个有序文件再合并成一个有序的大文件。
    • sort_buffer_size越小，需要分成的份数就越多，number_of_tmp_files值就越大
    • sort_mode中packed_additional_fields排序过程对字符串做了“紧凑”处理，按实际空间来分配，不是按定义varcha(16)来分配

rowid排序

如果返回的字段多，那么sort_buffer放的字段数太多，内存能同时放下的行数很少，要分成很多个临时文件，排序的性能回很差。 所以单行很大，全字段排序方法效率不太好。

1
2
3
4
5
6
7
8
9

mysql> show variables like '%max_length_for_sort_data%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| max_length_for_sort_data | 1024  |
+--------------------------+-------+
1 row in set (0.01 sec)

SET max_length_for_sort_data = 16;

设置后，MySQL认为单行太大，要换个算法。

新的算法sort_buffer中只有要排序的列（name）和主键id 执行流程差异，因sort_buffer中没有city和age，最后按照id的值回到原表中取出name、city、age字段返回给客户端

  • 查看OPTIMIZER_TRACE结果中number_of_tmp_files是否使用临时文件
    • sort_mode值变成了<sort_key, rowid>，表示参与排序的只有name和id这两个字段。
    • number_of_tmp_files变成10了，每一行都变小了，因此需要排序的总数据量就变小了，需要的临时文件也相应地变少了。

全字段排序 VS rowid排序

MySQL担心内存太小，才会采用rowid排序算法，一次可以排序很多的行，但是需要回原表去取数据。

MySQL认为内存过大，优先选择全字段排序，直接从内存取数据，不需要回原表。

MySQL的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。

解决上述问题，避免排序可以考虑使用覆盖索引alter table t add index city_user_age(city, name, age);

如何正确地显示随机消息

内存临时表

“InnoDB”表，对于内存表，回表过程只是简单地根据数据行的位置，直接访问内存得到数据，根本不会导致多访问磁盘。

1
2

# 使用存储过程快速创建10000条数据 字段 id word
mysql> select word from words order by rand() limit 3;

explain分析执行情况： Extra字段显示Using temporary，表示的是需要使用临时表；Using filesort，表示的是需要执行排序操作。

从OPTIMIZER_TRACE结果来看，未使用文件排序，5.6版本后引入了新算法：优先队列排序算法

1
2
3
4
5
6
7

"filesort_summary": {
  "rows": 4,
  "examined_rows": 10000,
  "number_of_tmp_files": 0,
  "sort_buffer_size": 96,
  "sort_mode": "<sort_key, rowid>"
}

小结一下：order by rand()使用了内存临时表，内存临时表排序的时候使用了rowid排序方法。

磁盘临时表

tmp_table_size配置了内存临时表大小，超过就会转成磁盘临时表 磁盘临时表internal_tmp_disk_storage_engine默认值是InnoDB

随机排序方法

以取3条数据为例 都在应用程序控制，数据库只处理读写

  1. 计算出总行数 c
  2. 依据 floor(c * rand()) 算出一个数字，计算3次
  3. 使用 limit Y,1 语句 执行3次，Y是上一步计算出来的

最终还是从不同的业务不同的分析

到底可不可以使用join

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB;

create table t1 like t2;

# 示例1
select * from t1 straight_join t2 on (t1.a=t2.a);

select * from t1 straight_join t2 on (t1.a=t2.b);

使用的算法

  • Index Nested-Loop Join ，可以用上被驱动表的索引
    • 从表 t1 中读入一行数据 R；
    • 从数据行R中，取出a字段到表t2里去查找；
    • 取出表t2中满足条件的行，跟R组成一行，作为结果集的一部分；
    • 重复执行步骤1到3，直到表t1的末尾循环结束。
  • Block Nested-Loop Join ，扫描行数就会过多，尤其是在大表。可能会扫描被驱动表很多次，会占用大量的系统资源。这种join尽量不要使用。
    • 把表t1的数据读入线程内存join_buffer中，由于我们这个语句中写的是select *，因此是把整个表t1放入了内存；
    • 扫描表t2，把表t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回。 是否使用join，看explain结果里面，Extra字段里面有没有出现“Block Nested Loop”字样。

选择驱动表

  • 如果是Index Nested-Loop Join（NLJ）算法，应该选择小表做驱动表；
  • 如果是Block Nested-Loop Join（BNL）算法：
    • 在join_buffer_size足够大的时候，是一样的；
    • 在join_buffer_size不够大的时候（这种情况更常见），应该选择小表做驱动表。

“小表”的定义

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

join语句怎么优化

Multi-Range Read优化(MRR)，优化的主要目的是尽量使用顺序读盘。

MRR能够提升性能的核心在于，这条查询语句在索引a上做的是一个范围查询（也就是说，这是一个多值查询），可以得到足够多的主键id。这样通过排序以后，再去主键索引查数据，才能体现出“顺序性”的优势。

大表join操作虽然对IO有影响，但是在语句执行结束后，对IO的影响也就结束了。但是，对Buffer Pool的影响就是持续性的，需要依靠后续的查询请求慢慢恢复内存命中率。young区域的数据页没有被合理地淘汰。

BNL转BKA

Batched Key Acess(BKA)算法：从表t1里一次性地多拿些行出来，放到join_buffer，一起传给表t2

扩展-hash join

业务代码实现

小结
  1. BKA优化是MySQL已经内置支持的，建议你默认使用；
  2. BNL算法效率低，建议你都尽量转成BKA算法。优化的方向就是给被驱动表的关联字段加上索引；
  3. 基于临时表的改进方案，对于能够提前过滤出小数据的join语句来说，效果还是很好的；
  4. MySQL目前的版本还不支持hash join，但你可以配合应用端自己模拟出来，理论上效果要好于临时表的方案。