当前位置: 首页 > 知识库问答 >
问题:

InnoDB压缩后的Mysql错误索引

段干博涉
2023-03-14

关于师父的解释:


+----+-------------+-------+--------+---------------+---------+---------+-----------------------------+-----------+-------------+
| 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                                            |
+----+-------------+-------+--------+-------------------------------------------------------------------------------+--------------------+---------+---------------------------+------+--------------------------------------------------------+

查询执行时间长。

共有1个答案

巫马山
2023-03-14

我怀疑压缩会导致不同的统计数据,从而导致不同的执行计划。

在此查询中,我看不到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/ 对如何做到这一点有什么想法吗?

  • 我有以下带有委托的代码: 去掉代理,缩小是正确的: