在一次对线上系统的压测过程中,数据库突然变成了只读状态。我们看了一下,是因为空间在短时间内,被撑爆了。云上的rds数据库,如果在空间打爆的情况下,确实会变成只读的情况。
我们这个业务,做了中美数据拆分,美国的数据库是在aws上,中国的数据库是在阿里云上,跑同样的一套逻辑。
可以看到,在短时间内:
aws的free storage 迅速下降,最低的时候,只有10多G;而阿里云,已经被打爆,阿里云上的空间阈值是500G,故障的时候,已经突发到了600G,其中主要的占据部分,是红色的部分,也就是临时文件的大小。
综上,可以看到,在十几分钟内,数据库的空间就被占据了200G到400G不等。一方面,说明数据库的IO能力确实还不错。另外一方面,数据库确实存在了异常。我们需要找到是什么原因导致了空间迅速被消耗。
在顺便这里说一下aws和和阿里云的监控,可以看的阿里云的空间监控,比aws的信息更加详细,因为aws只有一个free storage的值,而阿里云中,可以看的存储空间猛增的原因,是其中红色部分的临时文件的空间猛增。这为我们后续的分析,提供了很重要的信息。如果光是从aws的监控,我们下手会增加一定的难度。
我们在当时登录到了aws的rds里面,没有看到insert语句,看到有几个select语句,当时是处于creating sort index,从跑的时间看,一些线程已经跑了700多秒,一些线程跑了200多秒。显然,跑的都不快。虽然值得怀疑,当时感觉不应该会消耗那么高的临时空间。但是确实没有其他异常的语句,和开发确认后,kill掉了creating sort index的语句。空间立马就恢复回去了。
看来引发问题的语句是确认了。接下来需要分析,这个语句为什么会消耗这么多临时空间。因为根据经验,一个排序的语句,一般消耗4,5百M的临时段,也是顶天了。为什么这个语句会消耗那么多的临时空间呢?
在阿里云中有个CloudDBA,我们可以看到更多的一些信息来辅助分析。比如这个“性能趋势”,可以看到精确的消耗临时文件的值,为438298M。
另外有个“性能洞察”
可以看到,当时约有16个active session,是消耗在橙色的creating sort index,并且可以看到绑定变量之后的sql语句。
SELECT `myabccydf_table`.*
FROM `myabccydf_table`
INNER JOIN `ownddeu_mynameac` ON `ownddeu_mynameac`.`id` = `myabccydf_table`.`item_id`
WHERE `ownddeu_mynameac`.`published_at` <= NOW() + INTERVAL ? DAY
ORDER BY `myabccydf_table`.`updated_at` DESC
LIMIT ?, ?
当时这个sql语句有使用绑定变量,这边显示了?,我们需要更精确的定位到语句。可以从多个地方去找到这个具体的sql。一个是通过慢查询记录的语句,因为这些语句基本需要执行几百秒的时间。一个是我们DBA团队部署的myawr,它会定期的把show processlist的结果打快照下来。另外,我们还可以通过登录aws时候抓到的语句来去看,因为两边的逻辑是一样的,是一套程序跑2个云。所以我们最终抓到了语句是:
SELECT `myabccydf_table`.* FROM `myabccydf_table`
INNER JOIN `ownddeu_mynameac` ON `ownddeu_mynameac`.`id` = `myabccydf_table`.`item_id`
WHERE (`ownddeu_mynameac`.`published_at` <= NOW() + INTERVAL 0 DAY)
ORDER BY `myabccydf_table`.`updated_at` desc LIMIT 10 OFFSET 0
这个语句,分析了一下执行计划,如下:
explain SELECT `myabccydf_table`.* FROM `myabccydf_table`
INNER JOIN `ownddeu_mynameac` ON `ownddeu_mynameac`.`id` = `myabccydf_table`.`item_id`
WHERE (`ownddeu_mynameac`.`published_at` <= NOW() + INTERVAL 0 DAY)
ORDER BY `myabccydf_table`.`updated_at` desc LIMIT 10 OFFSET 0;
+----+-------------+------------------+--------+---------------+---------+---------+----------------------------------------------+----------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------+--------+---------------+---------+---------+----------------------------------------------+----------+-----------------------------+
| 1 | SIMPLE | myabccydf_table | ALL | NULL | NULL | NULL | NULL | 26875439 | Using where; Using filesort |
| 1 | SIMPLE | ownddeu_mynameac | eq_ref | PRIMARY | PRIMARY | 4 | dji_store_production.myabccydf_table.item_id | 1 | Using where |
+----+-------------+------------------+--------+---------------+---------+---------+----------------------------------------------+----------+-----------------------------+
2 rows in set (0.01 sec)
检查了一下这个sql语句返回的行数,也就2000多万行:
SELECT count(*) FROM `myabccydf_table`
INNER JOIN `ownddeu_mynameac` ON `ownddeu_mynameac`.`id` = `myabccydf_table`.`item_id`
WHERE (`ownddeu_mynameac`.`published_at` <= NOW() + INTERVAL 0 DAY);
+----------+
| count(*) |
+----------+
| 26385116 |
+----------+
感觉千万级的记录有order by排序,虽然排序字段上没有索引,但是也不应该消耗那么多临时空间用来排序。
看到语句是个select *,直觉上觉得有问题,不符合开发规范,看了一下表结构:
show create table myabccydf_table;
-- 输出如下
CREATE TABLE `myabccydf_table` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`item_id` int(11) DEFAULT NULL,
`mymum_mydady_sansy_id` int(11) DEFAULT NULL,
`quantity` int(11) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`item_type` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `index_myabccydf_table_on_mymum_mydady_sansy_id` (`mymum_mydady_sansy_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2227662255 DEFAULT CHARSET=utf8
尝试把各个字段依次带入,分别运行了一下。发现如果不带最后一个字段,那么20多秒就能跑出来;
SELECT `myabccydf_table`.id,
-> `myabccydf_table`.item_id,
-> `myabccydf_table`.mymum_mydady_sansy_id,
-> `myabccydf_table`.quantity,
-> `myabccydf_table`.created_at,
-> `myabccydf_table`.updated_at
-> FROM `myabccydf_table`
-> INNER JOIN `ownddeu_mynameac` ON `ownddeu_mynameac`.`id` = `myabccydf_table`.`item_id`
-> WHERE (`ownddeu_mynameac`.`published_at` <= NOW() + INTERVAL 0 DAY)
-> ORDER BY `myabccydf_table`.`updated_at` desc LIMIT 10 OFFSET 0;
(结果略)
10 rows in set (24.30 sec)
而带上了最后那个item_type的字段后,需要6,7分钟才能跑出来:
SELECT `myabccydf_table`.* FROM `myabccydf_table`
-> INNER JOIN `ownddeu_mynameac` ON `ownddeu_mynameac`.`id` = `myabccydf_table`.`item_id`
-> WHERE (`ownddeu_mynameac`.`published_at` <= NOW() + INTERVAL 0 DAY)
-> ORDER BY `myabccydf_table`.`updated_at` desc LIMIT 10 OFFSET 0;
(结果略)
10 rows in set (6 min 17.34 sec)
并且在阿里云和aws,都测试了一下。发现跑的时候,都会造成约40左右的空间降低,在跑完之后,空间迅速恢复。
都可以看到,单个线程跑都需要消耗40G左右的临时空间。
那么,当时在阿里云上看到16个active session都跑在create sort index,消耗400多G的临时文件,也就不足为奇了。
但是,为什么这个item_type的字段,一旦带上去了,就要消耗那么多的临时文件呢?一个varchar 255的字段长度,有那么大的威力吗?
和知数堂的徐晨亮聊聊一下,说这个可能和mysql的几种排序方式有关,建议我可以具体分析一下。
要看采用了的排序方式,普通的explain已经无法看到,我通过打开设置了optimizer_trace=on,来进一步分析。
select @@optimizer_trace;
+-------------------------+
| @@optimizer_trace |
+-------------------------+
| enabled=on,one_line=off |
+-------------------------+
mysql> set session optimizer_trace='enabled=on';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT `myabccydf_table`.* FROM `myabccydf_table`
-> INNER JOIN `ownddeu_mynameac` ON `ownddeu_mynameac`.`id` = `myabccydf_table`.`item_id`
-> WHERE (`ownddeu_mynameac`.`published_at` <= NOW() + INTERVAL 0 DAY)
-> ORDER BY `myabccydf_table`.`updated_at` desc LIMIT 10 OFFSET 0;
(结果略)
10 rows in set (6 min 17.34 sec)
mysql> select * from information_schema.optimizer_trace\G;
*************************** 1. row ***************************
QUERY: SELECT `myabccydf_table`.* FROM `myabccydf_table`
INNER JOIN `ownddeu_mynameac` ON `ownddeu_mynameac`.`id` = `myabccydf_table`.`item_id`
WHERE (`ownddeu_mynameac`.`published_at` <= NOW() + INTERVAL 0 DAY)
ORDER BY `myabccydf_table`.`updated_at` desc LIMIT 10 OFFSET 0
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `myabccydf_table`.`id` AS `id`,`myabccydf_table`.`item_id` AS `item_id`,`myabccydf_table`.`mymum_mydady_sansy_id` AS `mymum_mydady_sansy_id`,`myabccydf_table`.`quantity` AS `quantity`,`myabccydf_table`.`created_at` AS `created_at`,`myabccydf_table`.`updated_at` AS `updated_at`,`myabccydf_table`.`item_type` AS `item_type` from (`myabccydf_table` join `ownddeu_mynameac` on((`ownddeu_mynameac`.`id` = `myabccydf_table`.`item_id`))) where (`ownddeu_mynameac`.`published_at` <= (now() + interval 0 day)) order by `myabccydf_table`.`updated_at` desc limit 0,10"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"transformations_to_nested_joins": {
"transformations": [
"JOIN_condition_to_WHERE",
"parenthesis_removal"
],
"expanded_query": "/* select#1 */ select `myabccydf_table`.`id` AS `id`,`myabccydf_table`.`item_id` AS `item_id`,`myabccydf_table`.`mymum_mydady_sansy_id` AS `mymum_mydady_sansy_id`,`myabccydf_table`.`quantity` AS `quantity`,`myabccydf_table`.`created_at` AS `created_at`,`myabccydf_table`.`updated_at` AS `updated_at`,`myabccydf_table`.`item_type` AS `item_type` from `myabccydf_table` join `ownddeu_mynameac` where ((`ownddeu_mynameac`.`published_at` <= (now() + interval 0 day)) and (`ownddeu_mynameac`.`id` = `myabccydf_table`.`item_id`)) order by `myabccydf_table`.`updated_at` desc limit 0,10"
}
},
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`ownddeu_mynameac`.`published_at` <= (now() + interval 0 day)) and (`ownddeu_mynameac`.`id` = `myabccydf_table`.`item_id`))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((`ownddeu_mynameac`.`published_at` <= (now() + interval 0 day)) and multiple equal(`ownddeu_mynameac`.`id`, `myabccydf_table`.`item_id`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "((`ownddeu_mynameac`.`published_at` <= (now() + interval 0 day)) and multiple equal(`ownddeu_mynameac`.`id`, `myabccydf_table`.`item_id`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((`ownddeu_mynameac`.`published_at` <= (now() + interval 0 day)) and multiple equal(`ownddeu_mynameac`.`id`, `myabccydf_table`.`item_id`))"
}
]
}
},
{
"table_dependencies": [
{
"table": "`myabccydf_table`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
},
{
"table": "`ownddeu_mynameac`",
"row_may_be_null": false,
"map_bit": 1,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "`ownddeu_mynameac`",
"field": "id",
"equals": "`myabccydf_table`.`item_id`",
"null_rejecting": true
}
]
},
{
"rows_estimation": [
{
"table": "`myabccydf_table`",
"table_scan": {
"rows": 25033268,
"cost": 392812
}
},
{
"table": "`ownddeu_mynameac`",
"table_scan": {
"rows": 2789,
"cost": 97
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`myabccydf_table`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "scan",
"rows": 2.5e7,
"cost": 5.4e6,
"chosen": true
}
]
},
"cost_for_plan": 5.4e6,
"rows_for_plan": 2.5e7,
"rest_of_plan": [
{
"plan_prefix": [
"`myabccydf_table`"
],
"table": "`ownddeu_mynameac`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"rows": 1,
"cost": 2.5e7,
"chosen": true
},
{
"access_type": "scan",
"using_join_cache": true,
"rows": 2092,
"cost": 1e10,
"chosen": false
}
]
},
"cost_for_plan": 3.54e7,
"rows_for_plan": 2.5e7,
"chosen": true
}
]
},
{
"plan_prefix": [
],
"table": "`ownddeu_mynameac`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"usable": false,
"chosen": false
},
{
"access_type": "scan",
"rows": 2789,
"cost": 654.8,
"chosen": true
}
]
},
"cost_for_plan": 654.8,
"rows_for_plan": 2789,
"rest_of_plan": [
{
"plan_prefix": [
"`ownddeu_mynameac`"
],
"table": "`myabccydf_table`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "scan",
"using_join_cache": true,
"rows": 2.5e7,
"cost": 1.4e10,
"chosen": true
}
]
},
"cost_for_plan": 1.4e10,
"rows_for_plan": 7e10,
"pruned_by_cost": true
}
]
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`ownddeu_mynameac`.`id` = `myabccydf_table`.`item_id`) and (`ownddeu_mynameac`.`published_at` <= (now() + interval 0 day)))",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`myabccydf_table`",
"attached": "(`myabccydf_table`.`item_id` is not null)"
},
{
"table": "`ownddeu_mynameac`",
"attached": "(`ownddeu_mynameac`.`published_at` <= (now() + interval 0 day))"
}
]
}
},
{
"clause_processing": {
"clause": "ORDER BY",
"original_clause": "`myabccydf_table`.`updated_at` desc",
"items": [
{
"item": "`myabccydf_table`.`updated_at`"
}
],
"resulting_clause_is_simple": true,
"resulting_clause": "`myabccydf_table`.`updated_at` desc"
}
},
{
"refine_plan": [
{
"table": "`myabccydf_table`",
"access_type": "table_scan"
},
{
"table": "`ownddeu_mynameac`"
}
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [
{
"filesort_information": [
{
"direction": "desc",
"table": "`myabccydf_table`",
"field": "updated_at"
}
],
"filesort_priority_queue_optimization": {
"usable": false,
"cause": "not applicable (no LIMIT)"
},
"filesort_execution": [
],
"filesort_summary": {
"rows": 26497152,
"examined_rows": 26497152,
"number_of_tmp_files": 10287,
"sort_buffer_size": 2096864,
"sort_mode": "<sort_key, additional_fields>"
}
}
]
}
}
]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> set session optimizer_trace='enabled=off';
Query OK, 0 rows affected (0.00 sec)
我们重点看这一部分:
"filesort_summary": {
"rows": 26497152,
"examined_rows": 26497152,
"number_of_tmp_files": 10287,
"sort_buffer_size": 2096864,
"sort_mode": "<sort_key, additional_fields>"
}
这里的sort_key, additional_fields,说明这个语句,是采用mysql的第二种排序方式。
mysql的排序方式有3种:
- < sort_key, rowid >对应的是MySQL 4.1之前的“原始排序模式”
- < sort_key, additional_fields >对应的是MySQL 4.1以后引入的“修改后排序模式”
- < sort_key, packed_additional_fields >是MySQL 5.7.3以后引入的进一步优化的“打包数据排序模式”
1. 原始排序模式
根据索引或者全表扫描,按照过滤条件获得需要查询的排序字段值和row ID;
将要排序字段值和row ID组成键值对,存入sort buffer中;
如果sort buffer内存大于这些键值对的内存,就不需要创建临时文件了。否则,每次sort buffer填满以后,需要直接用qsort(快速排序算法)在内存中排好序,并写到临时文件中;
重复上述步骤,直到所有的行数据都正常读取了完成;
用到了临时文件的,需要利用磁盘外部排序,将row id写入到结果文件中;
根据结果文件中的row ID按序读取用户需要返回的数据。由于row ID不是顺序的,导致回表时是随机IO,为了进一步优化性能(变成顺序IO),MySQL会读一批row ID,并将读到的数据按排序字段顺序插入缓存区中(内存大小read_rnd_buffer_size)。
2. 修改后排序模式
根据索引或者全表扫描,按照过滤条件获得需要查询的数据;
将要排序的列值和用户需要返回的字段组成键值对,存入sort buffer中;
如果sort buffer内存大于这些键值对的内存,就不需要创建临时文件了。否则,每次sort buffer填满以后,需要直接用qsort(快速排序算法)在内存中排好序,并写到临时文件中;
重复上述步骤,直到所有的行数据都正常读取了完成;
用到了临时文件的,需要利用磁盘外部排序,将排序后的数据写入到结果文件中;
直接从结果文件中返回用户需要的字段数据,而不是根据row ID再次回表查询。
3. 打包数据排序模式
第三种排序模式的改进仅仅在于将char和varchar字段存到sort buffer中时,更加紧缩。
在之前的两种模式中,存储了“yes”3个字符的定义为VARCHAR(255)的列会在内存中申请255个字符内存空间,但是5.7.3改进后,只需要存储2个字节的字段长度和3个字符内存空间(用于保存”yes”这三个字符)就够了,内存空间整整压缩了50多倍,可以让更多的键值对保存在sort buffer中。
我们这个数据库的版本是5.6,所以无法用到第三种排序方式,那么它是怎么选择第二种还是第一种的排序方式呢?
MySQL给用户提供了一个max_length_for_sort_data的参数。当“排序的键值对大小” >max_length_for_sort_data时,MySQL认为磁盘外部排序的IO效率不如回表的效率,会选择第一种排序模式;反之,会选择第二种不回表的模式。
我们当前数据库的max_length_for_sort_data大小为:
show variables like '%max_length_for_sort_data%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| max_length_for_sort_data | 1024 |
+--------------------------+-------+
1 row in set (0.00 sec)
排序键值对是返回字段+排序字段,注意这里是返回字段,我们是select *,因此包含了myabccydf_table表的所有字段。
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`item_id` int(11) DEFAULT NULL,
`mymum_mydady_sansy_id` int(11) DEFAULT NULL,
`quantity` int(11) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`item_type` varchar(255) NOT NULL,
`updated_at` datetime DEFAULT NULL,
我们的库是utf8,根据mysql的文档:
1. int和bigint
2. datetime
3. varchar
因此需要占据排序区的大小为:
mysql> select version();
+------------+
| version() |
+------------+
| 5.6.16-log |
+------------+
1 row in set (0.00 sec)
mysql> select (8+4+4+4+5+5+255*3+5);
+-----------------------+
| (8+4+4+4+5+5+255*3+5) |
+-----------------------+
| 800 |
+-----------------------+
1 row in set (0.00 sec)
排序的键值对大小为800,小于max_length_for_sort_data的值1024,mysql会认为磁盘外部的IO效率高于通过rowid回表的效率,因此采用了第二种排序方式。
那么第二种的排序方式,消耗多大的临时空间呢,我们知道,需要排序的内容是“排序列+返回列”,也就是(单个排序列+返回列的大小)*返回的行数,即:
select (8+4+4+4+5+5+255*3+5)*26385116;
+--------------------------------+
| (8+4+4+4+5+5+255*3+5)*26385116 |
+--------------------------------+
| 21108092800 |
+--------------------------------+
1 row in set (0.00 sec)
/*
大约是20G左右的空间。
那么这20G左右的数据,怎么样在数据库主机上,消耗40G左右空间呢?为什么排序操作完了之后,40G的临时空间又马上释放了呢?
首先说说mysql的外部排序方式,是现在内存中,以sort_buffer_size的大小进行排序,如果能在内存中完成排序,那就不用使用临时文件,如果内存中排不下,那么就需要使用临时文件了。
仍然在filesort_summary部分,我们可以看到:
"filesort_summary": {
"rows": 26497152,
"examined_rows": 26497152,
"number_of_tmp_files": 10287,
"sort_buffer_size": 2096864,
"sort_mode": "<sort_key, additional_fields>"
}
*/
这里可以看到,sort_buffer_size是2096864,这个是mysql里面的设置,number_of_tmp_files是10287,也就是说,在排序的时候,在内存里面排不下,需要用到外部文件,外部文件按照sort_buffer_size的大小,切成了10287个文件,每个大小是2096864。我们把每个临时文件叫做一个chunk。那么这些chunk的大小,就是需要排序的数据大小,也就是20G,或者按照sort_buffer_size*number_of_tmp_files计算,也是20G左右的大小。
每个chunk完成了排序后需要合并成一个大的排序文件,也就是说,需要将10287个文件,总共20G的临时文件,合并成一个大的最终排序结果文件。那么这个文件也是20G,所以零碎的排序文件+合并结果的排序文件,总共大小是40G。
那么这些临时文件,为什么在排序完成的时候,就释放了呢?
临时文件是在发起filesort的时候,先有mkstemp函数生成一个文件,但是生成之后马上调用unlink删除文件。但是删掉又不close文件,还是保留了文件系统句柄,因此后续的写临时文件的操作,都是基于句柄。
这类似我们打开一个文件,但是另外的进程把这个文件删掉了,但是之前的那个进程,还是操作着这个文件的句柄。可以仍然对这个文件读写,只是我们ls已经看不到这个文件了,但是df -h还是看的空间没释放。只有等到第一个进程关闭了文件句柄,才释放掉了这个文件占据的空间。
所以mysql的用户线程开始操作,那么就一直握着文件句柄,然后在临时文件内完成内存与文件的sort-merge排序操作,完成了操作之后,用户进程可以close掉这个文件句柄,但是又没退出和mysql服务器的连接。
所以就能看到我们看到的现象:就是阿里云上的临时文件的占用,在开始的时候上升,一旦排序结束,就下降了。
好了,到这里,应该就解释了,为什么一个小小的2000多万行的排序,消耗了40多G的临时文件,并且在排序操作完成之后,就立即释放了空间。那么,对应的解决方法,我们可以随之而来。
改进方式1:
为排序字段添加索引。
改进方式2:
既然是采用了additional_fields的方式进行排序,而这种排序方式由于select了所有字段,导致排序体积过大,那么我不采用additional_fields 的方式,改用rowid的方式:可以session级别修改max_length_for_sort_data,使得这个值小于800。
mysql> select @@max_length_for_sort_data;
+----------------------------+
| @@max_length_for_sort_data |
+----------------------------+
| 1024 |
+----------------------------+
1 row in set (0.00 sec)
mysql> set session max_length_for_sort_data=100;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@max_length_for_sort_data;
+----------------------------+
| @@max_length_for_sort_data |
+----------------------------+
| 100 |
+----------------------------+
1 row in set (0.00 sec)
mysql> SELECT `myabccydf_table`.* FROM `myabccydf_table`
-> INNER JOIN `ownddeu_mynameac` ON `ownddeu_mynameac`.`id` = `myabccydf_table`.`item_id`
-> WHERE (`ownddeu_mynameac`.`published_at` <= NOW() + INTERVAL 0 DAY)
-> ORDER BY `myabccydf_table`.`updated_at` desc LIMIT 10 OFFSET 0;
(结果略)
10 rows in set (12.90 sec)
可以看到,我们没有修改任何语句,12秒也可以出结果了。
改进方式3:
采用延时关联的方法,这个方法,我们在分页查询的时候,经常用到。需要注意的是,驱动表和关联字段是一个表。
SELECT `x`.* FROM `myabccydf_table` x join
(
select `myabccydf_table`.id from `myabccydf_table`
INNER JOIN `ownddeu_mynameac` ON `ownddeu_mynameac`.`id` = `myabccydf_table`.`item_id`
WHERE (`ownddeu_mynameac`.`published_at` <= NOW() + INTERVAL 0 DAY)
ORDER BY `myabccydf_table`.`updated_at` desc LIMIT 10
) t
using(id);
mysql> SELECT `x`.* FROM `myabccydf_table` x join
-> (
-> select `myabccydf_table`.id from `myabccydf_table`
-> INNER JOIN `ownddeu_mynameac` ON `ownddeu_mynameac`.`id` = `myabccydf_table`.`item_id`
-> WHERE (`ownddeu_mynameac`.`published_at` <= NOW() + INTERVAL 0 DAY)
-> ORDER BY `myabccydf_table`.`updated_at` desc LIMIT 10
-> ) t
-> using(id);
(结果略)
10 rows in set (15.00 sec)
总结几条军规,就是:
1. mysql的字段选择要严谨,如字段类型定义长varchar 255,虽然在存放数据的时候,只是放14个字符,但是一旦这个字段被查询到用于排序的时候,就会按照255个字符申请内存。
2. 带order by的查询应该禁止select *操作,而select具体的必要字段。
3. 排序字段和返回字段,建议形成覆盖索引。
本来,到这里,花了一天的时间,已经把问题搞清楚,并且在团队内部进行了分享。但是我并不打算到此结束,接下来我打算做个有趣的实验,同一个问题在aws和阿里云都开了个case,一方面看看哪家服务更专业;另一方面我也想看看云厂商能专业到什么程度,是否能取代专业的DBA,未来企业内部的DBA是否还有生存空间。
于是两家云厂商都开了case,咨询为什么一个小小的查询需要消耗40G的临时文件。看看两家对rds的技术问题的处理风格。
需要说明有3点:
1. 这个case没有主动让TAM和研发介入,只是看看一线后台人员的技术水平,或者看看如果仅仅通过开case(这也是大家最简单、最常见的寻求支持的途径),能处理到什么地步。
2. 我后续的评论和感受,仅仅针对于这个case,不代表整体水平。
3. 给了两家厂商一个星期的时间,一般来说,跟case跟一个星期,如果还没结论,那就基本分析不出来了。
首先说说感受:
1. 阿里云的case的反应非常迅速,回复的很快,也非常的激进。基本没有pending在他手上过夜的。
2. aws的TAM也非常负责任,看到了rds的工单,有主动打电话联系我,询问是否需要帮助。他也主动去追工程师。
3. 阿里云的工程师,一开始是推脱,需要这个需要那个,需要开审计功能,说我没有审计,无法帮我。
4. aws的工程师比较务实,一开始就问我要了表结构和explain的信息。这是一种负责任的态度,是用心在问客户要数据,分析数据的态度。
5. 阿里云的工程师给人的感觉是并不分析用户的数据,不主动向用户要细节,只是给用户一些空对空的指示和建议。其实在请阿里云之前,我也做过网上的搜索,我发现他告诉我的那些话,我都在网上看到过。
6. 阿里云的工程师,那种风格给人感觉是先拿着锤子到处砸,运气好的话砸到了故障根因,运气不好的话,看能否砸晕客户,因为他告诉我的一些结论,不太经得起推敲。如果我是一个很着急需要得到一个结果去写报告的人,往往他这种风格可以满足到我。可惜我这次不是这样,我去细细推敲了他的分析,我发现他的一些计算是错误的。他试图绕晕我,如果我不坚定,很容易在过程中接收了他的结论。
7. aws的工程师比较务实的地方,不仅仅体现在他一开始问我要了表结构和explain,还很坦诚的打电话告诉我,这个问题是他能力范围之外的,但是由于我们是企业用户,他会用best effort来帮助我们。而且,在从20G的临时文件,为什么会到40G的推理上,他在电话中也比较接近真相。他提到了有临时的小文件,和会有一个汇总的大文件。只是还没想到两路外部排序算法和多路外部排序算法。
8. aws的工程师,对于能力之外的问题,反应比较慢,远远没有阿里云的工程师更新case来的积极主动,我往往很久都看不到他在case上的更新。所以我也不知道他的分析进度和状态如何。这一点是让我担心的。
9. 最终的结果,在一周之内,两家都没分析出根因。阿里云更接近一点,他根据字段长度去算了,但是算的方法不对,结论是错误的。utf8,一个字符占3个字节,只是针对字符型的,所以算的时候,应该是(8+4+4+4+5+5+255*3+5),而不是(8+4+4+4+5+5+256+4+5)*3,不应该把其他类型也乘以3;另外排序的第二种算法应该是排序列+返回列,阿里云是把两个表的所有出现过的列计算了。阿里云只是数字上比较接近。
10. 如果要让我选一家,我可能会选aws,因为他的诚恳和务实。我买的是服务,不想被忽悠。
然后说说结论:
目前云厂商的专线技术水平,还不能达到专业DBA的水平,就云厂商的一线工程师而言,可能有能力处理一般的rds的功能问题,但是进一步的根因查找,就无能为力了。因此企业内部的DBA还是有一定的市场。我之前写过一个比较悲观的dba将死,云架构师即将到来,但是现在来看,可能没死的那么快,至少在5年内,还不会迅速消失。
关键还是看公司对数据库的重视程度。是否有足够的人力,是否需要精细化运维,是否每次故障都需要分析根因?还是过去了就没事?下次再掉同样坑里面,大不了重启解决99%的IT问题。
参考:
MySQL排序内部原理探秘
MySQL · 引擎特性 · 临时表那些事儿
Data Type Storage Requirements
Unicode Support
MySQL 5.6 Reference Manual – ORDER BY Optimization
MySQL 5.1 Reference – Chapter 7. Optimization