关于师父的解释:
+----+-------------+-------+--------+---------------+---------+---------+-----------------------------+-----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+-----------------------------+-----------+-------------+ | 1 | SIMPLE | p | range | PRIMARY | PRIMARY | 8 | NULL | 112017572 | Using where | | 1 | SIMPLE | l | eq_ref | PRIMARY | PRIMARY | 8 | p.loan_ID | 1 | NULL | | 1 | SIMPLE | af | eq_ref | PRIMARY | PRIMARY | 8 | p.fromAccount_ID | 1 | Using where | | 1 | SIMPLE | at | eq_ref | PRIMARY | PRIMARY | 8 | p.toAccount_ID | 1 | Using where | +----+-------------+-------+--------+---------------+---------+---------+-----------------------------+-----------+-------------+
关于奴隶的解释:
+----+-------------+-------+--------+-------------------------------------------------------------------------------+--------------------+---------+-----------------------------+--------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+-------------------------------------------------------------------------------+--------------------+---------+-----------------------------+--------+----------------------------------------------+ | 1 | SIMPLE | l | index | PRIMARY | FK243910AAD869E6 | 9 | NULL | 804876 | Using index; Using temporary; Using filesort | | 1 | SIMPLE | p | ref | PRIMARY,FK4BE7532292C5D482,FK4BE75322AE503A13,FK4BE75322382D11BC,POSTING_DATE | FK4BE75322382D11BC | 9 | l.ID | 101 | Using index condition; Using where | | 1 | SIMPLE | af | eq_ref | PRIMARY | PRIMARY | 8 | p.fromAccount_ID | 1 | Using where | | 1 | SIMPLE | at | eq_ref | PRIMARY | PRIMARY | 8 | p.toAccount_ID | 1 | Using where | +----+-------------+-------+--------+-------------------------------------------------------------------------------+--------------------+---------+-----------------------------+--------+----------------------------------------------+
SELECT
p.ID AS 'payment_id',
p.loan_ID AS 'loan_id',
l.client_ID AS 'client_ID',
p.amount AS 'amount',
p.postingDate AS 'payment_date',
CASE
WHEN af.acc_type = 'POLCH' THEN 'wallet'
WHEN af.acc_type = 'PLTCH' THEN 'wallet'
WHEN af.acc_type = 'CNTT' THEN 'bank'
WHEN af.acc_type = 'CNT2' THEN 'bank'
WHEN af.acc_type = 'KONCH' THEN 'bank'
WHEN af.acc_type = 'KRDTM' THEN 'cash'
WHEN af.acc_type = 'LDRCH' THEN 'bank'
ELSE concat('UNKNOWN_',af.acc_type)
END AS 'payment_system_type',
af.description AS 'payment_system'
FROM Posting AS p
INNER JOIN Account AS af ON p.fromAccount_ID = af.ID
INNER JOIN Account AS at ON p.toAccount_ID = at.ID
INNER JOIN Loan AS l ON p.loan_id = l.ID
WHERE (
af.acc_type = 'KONCH'
OR af.acc_type = 'PLTCH'
OR af.acc_type = 'POLCH'
OR af.acc_type = 'KRDTM'
OR af.acc_type = 'LDRCH'
OR af.acc_type = 'CNT2'
OR af.acc_type = 'CNTT')
AND at.acc_type = 'ABON'
AND p.postingDate < DATE(now())
AND p.ID > 0
ORDER BY p.ID LIMIT 10000;
贷款-l
| Loan | CREATE TABLE `Loan` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT,
`amount` decimal(19,4) DEFAULT NULL,
`amountToReturn` decimal(19,4) DEFAULT NULL,
`isGivenOut` bit(1) DEFAULT b'0',
`isPaid` bit(1) DEFAULT NULL,
`issueDate` datetime DEFAULT NULL,
`loanPeriod` int(11) DEFAULT NULL,
`productType` varchar(255) DEFAULT NULL,
`realPayDate` datetime DEFAULT NULL,
`client_ID` bigint(20) DEFAULT NULL,
`product_ID` bigint(20) DEFAULT NULL,
`givenOutDate` datetime DEFAULT NULL,
`isPaidByBank` bit(1) DEFAULT NULL,
`accountNumberNBKI` varchar(255) DEFAULT NULL,
`needManualProcessing` bit(1) DEFAULT NULL,
`isReverted` bit(1) DEFAULT b'0',
`showInNBCHReport` bit(1) DEFAULT b'1',
`stake` decimal(19,5) DEFAULT NULL,
`ignoreProlongation` bit(1) DEFAULT b'0',
`stakeAfter21` decimal(19,5) DEFAULT NULL,
`discount_id` bigint(20) DEFAULT NULL,
`showInEquifaxReport` bit(1) DEFAULT b'1',
`ignoreNbch` bit(1) DEFAULT b'0',
PRIMARY KEY (`ID`),
KEY `FK2439106EC0BA18` (`product_ID`),
KEY `ISPAID_INDEX` (`isPaid`) USING BTREE,
KEY `ISP_ISGOUT_INDEX` (`isPaid`,`isGivenOut`),
KEY `ISSUEDATE_INDEX` (`issueDate`),
KEY `FK243910735827C6` (`discount_id`),
KEY `idx_Loan_realPayDate` (`realPayDate`),
KEY `idx_Loan_givenOutDate` (`givenOutDate`),
KEY `FK243910AAD869E6` (`client_ID`),
CONSTRAINT `_FK243910735827C6` FOREIGN KEY (`discount_id`) REFERENCES `Discount` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2000623399 DEFAULT CHARSET=utf8
Posting | CREATE TABLE `Posting` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT,
`amount` decimal(19,4) DEFAULT NULL,
`postingDate` datetime DEFAULT NULL,
`fromAccount_ID` bigint(20) DEFAULT NULL,
`loan_ID` bigint(20) DEFAULT NULL,
`toAccount_ID` bigint(20) DEFAULT NULL,
`sourceType` varchar(255) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `FK4BE7532292C5D482` (`fromAccount_ID`),
KEY `FK4BE75322AE503A13` (`toAccount_ID`),
KEY `FK4BE75322382D11BC` (`loan_ID`),
KEY `POSTING_DATE` (`postingDate`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=230996702 DEFAULT CHARSET=utf8
| Loan | CREATE TABLE `Loan` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT,
`amount` decimal(19,4) DEFAULT NULL,
`amountToReturn` decimal(19,4) DEFAULT NULL,
`isGivenOut` bit(1) DEFAULT b'0',
`isPaid` bit(1) DEFAULT NULL,
`issueDate` datetime DEFAULT NULL,
`loanPeriod` int(11) DEFAULT NULL,
`productType` varchar(255) DEFAULT NULL,
`realPayDate` datetime DEFAULT NULL,
`client_ID` bigint(20) DEFAULT NULL,
`product_ID` bigint(20) DEFAULT NULL,
`givenOutDate` datetime DEFAULT NULL,
`isPaidByBank` bit(1) DEFAULT NULL,
`accountNumberNBKI` varchar(255) DEFAULT NULL,
`needManualProcessing` bit(1) DEFAULT NULL,
`isReverted` bit(1) DEFAULT b'0',
`showInNBCHReport` bit(1) DEFAULT b'1',
`stake` decimal(19,5) DEFAULT NULL,
`ignoreProlongation` bit(1) DEFAULT b'0',
`stakeAfter21` decimal(19,5) DEFAULT NULL,
`discount_id` bigint(20) DEFAULT NULL,
`showInEquifaxReport` bit(1) DEFAULT b'1',
`ignoreNbch` bit(1) DEFAULT b'0',
PRIMARY KEY (`ID`),
KEY `FK2439106EC0BA18` (`product_ID`),
KEY `ISPAID_INDEX` (`isPaid`) USING BTREE,
KEY `ISP_ISGOUT_INDEX` (`isPaid`,`isGivenOut`),
KEY `ISSUEDATE_INDEX` (`issueDate`),
KEY `FK243910735827C6` (`discount_id`),
KEY `idx_Loan_realPayDate` (`realPayDate`),
KEY `idx_Loan_givenOutDate` (`givenOutDate`),
KEY `FK243910AAD869E6` (`client_ID`),
CONSTRAINT `_FK243910735827C6` FOREIGN KEY (`discount_id`) REFERENCES `Discount` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2000623399 DEFAULT CHARSET=utf8
ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4
Posting | CREATE TABLE `Posting` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT,
`amount` decimal(19,4) DEFAULT NULL,
`postingDate` datetime DEFAULT NULL,
`fromAccount_ID` bigint(20) DEFAULT NULL,
`loan_ID` bigint(20) DEFAULT NULL,
`toAccount_ID` bigint(20) DEFAULT NULL,
`sourceType` varchar(255) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `FK4BE7532292C5D482` (`fromAccount_ID`),
KEY `FK4BE75322AE503A13` (`toAccount_ID`),
KEY `FK4BE75322382D11BC` (`loan_ID`),
KEY `POSTING_DATE` (`postingDate`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=230996702 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4
+----+-------------+-------+--------+------------------------------------------------------------+---------+---------+-----------------------------+-----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+------------------------------------------------------------+---------+---------+-----------------------------+-----------+-------------+ | 1 | SIMPLE | p | range | PRIMARY,FK4BE7532292C5D482,FK4BE75322AE503A13,POSTING_DATE | PRIMARY | 8 | NULL | 107736559 | Using where | | 1 | SIMPLE | af | eq_ref | PRIMARY | PRIMARY | 8 | smsfinance.p.fromAccount_ID | 1 | Using where | | 1 | SIMPLE | at | eq_ref | PRIMARY | PRIMARY | 8 | smsfinance.p.toAccount_ID | 1 | Using where | +----+-------------+-------+--------+------------------------------------------------------------+---------+---------+-----------------------------+-----------+-------------+
+----+-------------+-------+--------+-------------------------------------------------------------------------------+--------------------+---------+---------------------------+------+--------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+-------------------------------------------------------------------------------+--------------------+---------+---------------------------+------+--------------------------------------------------------+ | 1 | SIMPLE | af | range | PRIMARY,acc | acc | 21 | NULL | 4192 | Using index condition; Using temporary; Using filesort | | 1 | SIMPLE | p | ref | PRIMARY,FK4BE7532292C5D482,FK4BE75322AE503A13,FK4BE75322382D11BC,POSTING_DATE | FK4BE7532292C5D482 | 9 | smsfinance.af.ID | 54 | Using index condition; Using where | | 1 | SIMPLE | l | eq_ref | PRIMARY | PRIMARY | 8 | smsfinance.p.loan_ID | 1 | NULL | | 1 | SIMPLE | at | eq_ref | PRIMARY,acc | PRIMARY | 8 | smsfinance.p.toAccount_ID | 1 | Using where | +----+-------------+-------+--------+-------------------------------------------------------------------------------+--------------------+---------+---------------------------+------+--------------------------------------------------------+
查询执行时间长。
我怀疑压缩会导致不同的统计数据,从而导致不同的执行计划。
在此查询中,我看不到loan
表的用处。从查询中删除它可能会强制explain计划相同。
每个表有200m行?另一个加速是缩小表。
bigint
(每个8字节)更改为int无符号
(4字节,范围0.40亿)。_type
列,替换为smallint无符号
(2字节,范围0..64k)或其他合适的整数类型。如果“类型”数量有限,则将列转换为枚举
。帐户
是否有索引(acc_type)
?(或者至少从acc_type开始。)
( SELECT client_ID FROM Loans WHERE ID = p.loan_id ) AS 'client_ID',
问题内容: 我正在尝试为InnoDB表导入mysqldump生成的大型SQL文件,但是即使在调整my.cnf中的某些参数并禁用了AUTOCOMMIT(以及FOREIGN_KEY_CHECKS和UNIQUE_CHECKS之后,也要花很长时间),但是该表确实没有任何外来或唯一键)。但是我想知道是否由于表中的多个索引花费了这么长时间。 查看SQL文件,似乎在插入所有数据之前在CREATE TABLE语句
问题内容: 在Python 2中,此代码是可以的: 但是在Python 3中发生以下错误: 如果删除lambda表达式中的括号,则会发生另一个错误: 也可以使用元组,因为单个lambda参数可在Python 3中使用,但尚不清楚(难以阅读): 如何在Python 3中以正确的方式解压值? 问题答案: 在PEP 3113中 讨论了删除元组拆包的问题。基本上,您无法在Python 3中执行此操作。在标
我想在Netty客户端/服务器上应用压缩/解压缩。我在客户端和服务器中使用以下代码作为管道: 并将服务器设置为: 我得到了以下错误在客户端启动连接 警告:初始化通道失败。关闭:[id: 0x3553bb5c]java.lang.NoClassDefFoundError: com/jcraft/jzlib/Inflater在io.netty.handler.codec.compression.JZl
这里我有几个文件夹中的Bookfolder(英语,印地语,日语)。将英语,印地语,日语转换为english.zip,hindi.zip和japanese.zip。一切都很好,我把zip文件和文件夹保存在Bookfolder中,这是我用java做的事情。但是当我手动解压缩zip文件ex:english.zip时,右键单击这里的解压缩,然后显示错误为意外的归档结束。这是我的代码。 当我提取新的zip文
我正在使用Julia的ZipFile包来提取和处理csv文件。没问题,但是当我遇到zip文件中的zip文件时,我也想处理它,但是遇到了一个错误。 Julia ZipFile文档如下:https://zipfilejl.readthedocs.io/en/latest/ 对如何做到这一点有什么想法吗?
我有以下带有委托的代码: 去掉代理,缩小是正确的: