InnoDB压缩后Mysql错误索引-Java 学习之路

我有2个mysql服务器（主服务器和从服务器） . 我已经在Slave上启用了InnoDB压缩，之后mysql有时会在查询中选择错误的索引 .

解释师父：

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

发布 - P.

主：

| 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

如果从Query中删除表Loan

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

如果我在账户上添加创建指数acc（ acc_type ）;

计划：

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

0
我怀疑压缩导致不同的统计数据，这可能导致不同的执行计划 .

我认为此查询中的表 Loan 没用 . 从查询中删除它可能会强制解释计划相同 .

每个表中有200M行？另一个加速是缩小表格 .
- 尽可能将 BIGINT （每个8字节）更改为 INT UNSIGNED （4个字节，范围0..4亿） .
- 规范化 _type 列，替换为 SMALLINT UNSIGNED （2个字节，范围0..64K）或其他合适的整数类型 . 或者如果有一个有限的小数字"types"，则将该列转换为 ENUM .
Account 有 INDEX(acc_type) 吗？（或者至少从acc_type开始 . ）

删除 INNER JOIN Loan AS l ON p.loan_id = l.ID 并将 l.client_ID AS 'client_ID', 替换为
```
( SELECT client_ID FROM Loans WHERE ID = p.loan_id ) AS 'client_ID',
```
我认为这将强制执行不同的查询计划，也许是一个好的查询计划 .
回复于 2024-05-02T07:18:57+08:00

InnoDB压缩后Mysql错误索引

1 回答

相关问题