mysql 8窗口函数错误结果

u5rb5r59 于 2021-06-20 发布在 Mysql

关注(0)|答案(3)|浏览(570)

我对mysql8中的窗口函数有一个问题——当应用于大型表时，它们会给出错误的结果（要么：大量的行，要么是大量的列）。
示例：表：bureau.csv（170万行）https://www.kaggle.com/c/home-credit-default-risk/data
我运行了3个简单的查询，只更改要使用的表的行数和要输出的列数。您可以清楚地看到，大量行和许多输出列的组合给出了不正确的结果“count（*）over（）”—最后一列。
行数多，列数少-结果：确定；
高行数、高列数-结果：不正确；
低行数，高列数-结果：ok；
ubuntu 16.04，32gb内存
非常感谢您的帮助！：）
维托尔德

my.cnf:
[mysqld]

innodb_buffer_pool_size = 26G
default_authentication_plugin=mysql_native_password
thread_cache_size = 50
innodb-flush-method=O_DIRECT
local_infile=ON
innodb_thread_concurrency=2
internal_tmp_mem_storage_engine=MEMORY
join_buffer_size=1G
temptable_max_ram=4G
tmp_table_size=4G
max_heap_table_size=4G
mysqlx_connect_timeout=99999
mysqlx_read_timeout=99999
mysqlx_write_timeout=99999
net_read_timeout=99999
net_write_timeout=99999
regexp_time_limit=99999
mysqlx_port_open_timeout=99999
windowing-use-high-precision=OFF
sort_buffer_size=4G

要运行的代码：

select a.*
from
(
select #b.sk_id_curr,
       b.*,
       count(*) over(partition by b.sk_id_curr) as counter
from (select * from bureau limit 10000000) b
) a
order by a.sk_id_curr desc
limit 100
;

编辑：exlain和索引图片
还有一件事我注意到：在“不正确”的图片中，所有的列都是不正确的（不仅仅是最后一列）-与“良好”结果的图片相比（看sk\u id\u curr）。
根据wilson hauck的要求：a）完整（未编辑）my.cnf-ini文本结果：b）显示全局状态；c）显示全局变量；d）显示引擎innodb状态；显示创建表局；第1部分第2部分
db fiddle上的1000行示例：https://www.db-fiddle.com/f/fzxsn6vfzidhanxeujwkib/0
我将数据导入mysql的方式：
首先，我将csv中的“空格”替换为python中的“null”：

import pandas as pd
bureau = pd.read_csv('../input/bureau.csv')
bureau.to_csv('../input/bureau2.csv',index=False,na_rep="NULL",header=True)

其次，我使用了mysql中的代码：

LOAD DATA LOCAL INFILE '../input/bureau2.csv' INTO TABLE bureau
FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
LINES TERMINATED BY '\n'
IGNORE 1 LINES
;

mysql window-functions large-data mysql-8.0

来源：https://stackoverflow.com/questions/51161778/mysql-8-window-function-wrong-results

3条答案

按热度按时间

suzh9iv81#

这个版本为我提供了8.0.11的预期结果（注意这里不再有任何文本blob）。如果要使其工作，还需要禁用--big\u表。

CREATE TABLE `bureau` (
 `SK_ID_CURR` int(11) DEFAULT NULL,
 `SK_ID_BUREAU` int(11) DEFAULT NULL,
 `CREDIT_ACTIVE` varchar(20),
 `CREDIT_CURRENCY` varchar(20),
 `DAYS_CREDIT` int(11) DEFAULT NULL,
 `CREDIT_DAY_OVERDUE` int(11) DEFAULT NULL,
 `DAYS_CREDIT_ENDDATE` varchar(20),
 `DAYS_ENDDATE_FACT` varchar(20),
 `AMT_CREDIT_MAX_OVERDUE` varchar(20),
 `CNT_CREDIT_PROLONG` int(11) DEFAULT NULL,
 `AMT_CREDIT_SUM` double DEFAULT NULL,
 `AMT_CREDIT_SUM_DEBT` varchar(20),
 `AMT_CREDIT_SUM_LIMIT` varchar(20),
 `AMT_CREDIT_SUM_OVERDUE` double DEFAULT NULL,
 `CREDIT_TYPE` varchar(20),
 `DAYS_CREDIT_UPDATE` int(11) DEFAULT NULL,
 `AMT_ANNUITY` varchar(20),
 KEY `bureau` (`SK_ID_CURR`,`SK_ID_BUREAU`),
 KEY `bureau_i2` (`SK_ID_BUREAU`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

set session sql_mode='';
load data infile '/export/home/tmp/dag/git/mysql/bureau.csv' into table bureau columns terminated by "," ignore 1 lines;

select a.*
from
(
select #b.sk_id_curr,
       b.*,
       count(*) over(partition by b.sk_id_curr) as counter
from (select * from bureau) b
) a
order by a.sk_id_curr desc
limit 100
;

赞(0）回复(0）举报 2021-06-20

r6l8ljro2#

我下载了bureau.csv并使用“part2”链接中显示的表导入了它。我不得不禁用严格模式

set session sql_mode='';)

否则我在加载数据时会出错

(load data  infile 'bureau.csv' into table bureau columns terminated by "," ignore 1 lines;

不过，我的数据和你的略有不同：

然后我运行了失败的查询，得到了预期的结果：

您使用的MySQL8的确切版本是什么？
查询的文本版本：

select a.*
   from
   (
   select #b.sk_id_curr,
          b.*,
          count(*) over(partition by b.sk_id_curr)
   from (select * from bureau limit 10000000000) b
   ) a
   order by a.sk_id_curr desc
   limit 100
   ;

赞(0）回复(0）举报 2021-06-20

apeeds0o3#

关于my.cnf-ini[mysqld]部分的建议

temptable_max_ram=320M # from 4G for 1 percent of RAM 
tmp_table_size=320M # from 4G for 1 percent of RAM 
max_heap_table_size=320M # from 4G for 1 percent of RAM 
innodb_thread_concurrency=0 # from 2 throttle choking your server 
innodb_buffer_pool_size=24G # from 26G for 80% of RAM 
innodb_change_buffer_max_size=10 # from 25 percent set aside 

# sort_buffer_size=4G for default

# join_buffer_size=4G for default

只有32g内存，你要求26g、1g、4g、4g、4g、4g=43g，而mysql&os却什么也没有？
如需其他帮助，请查看个人资料、网络资料以获取联系信息。

赞(0）回复(0）举报 2021-06-20

我来回答

mysql 8窗口函数错误结果

3条答案

相关问题

热门标签

最新问答