mysql 小表 加速_mysql – 优化在非常小的表和非常大的表之间进行连接

我有两张桌子：

CREATE TABLE `TBL_REPORT` (

`ID_TBL_REPORT` bigint(20) NOT NULL AUTO_INCREMENT,

`INDEX_NAME` varchar(100) NOT NULL,

`INDEX_VALUE` varchar(100) NOT NULL,

`FK_TBL_REPORT_PROXY` bigint(20) NOT NULL,

`PAYLOAD` mediumtext NOT NULL,

`OPERATION` varchar(200) DEFAULT NULL,

`ERROR` varchar(50) NOT NULL,

`SERVER` varchar(50) NOT NULL,

`DATA` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (`ID_TBL_REPORT`),

KEY `TBL_INTER_TBL_INTER_PROXY_FK` (`FK_TBL_REPORT_PROXY`),

KEY `IX_INDEX_NAME` (`INDEX_NAME`),

KEY `IX_INDEX_VALUE` (`INDEX_VALUE`),

KEY `IX_DATA` (`DATA`),

CONSTRAINT `TBL_INTER_TBL_INTER_PROXY_FK` FOREIGN KEY (`FK_TBL_REPORT_PROXY`) REFERENCES `TBL_REPORT_PROXY` (`ID_TBL_REPORT_PROXY`)

) ENGINE=InnoDB AUTO_INCREMENT=4769095 DEFAULT CHARSET=latin1

CREATE TABLE `TBL_REPORT_PROXY` (

`ID_TBL_REPORT_PROXY` bigint(20) NOT NULL AUTO_INCREMENT,

`PROXY` varchar(100) NOT NULL,

`SERVICE_GROUP` varchar(150) NOT NULL,

PRIMARY KEY (`ID_TBL_REPORT_PROXY`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1

INDEX_NAME,INDEX_VALUE和DATA字段已创建索引.

第一个表表示对SOA总线中的服务的调用,第二个表表示服务属性.

第一行可以包含超过100.000.000行,第二行可以包含大约100行.

我的目标是通过某个字段选择过滤,并按限制n,10进行分页.可用于过滤的字段是PROXY,INDEX_NAME,ERROR和DATA.

例如,如果用户想要通过PROXY查看第一页过滤,则查询为：

select R.ID_TBL_REPORT, R.INDEX_NAME, R.INDEX_VALUE, R.ERROR, R.DATA, P.PROXY, P.SERVICE_GROUP

from TBL_REPORT R, TBL_REPORT_PROXY P

where P.PROXY = 'MyProxy'

and R.FK_TBL_REPORT_PROXY = P.ID_TBL_REPORT_PROXY

limit 1,10;

这很好用,结果显示得非常快.

但是,结果需要按最后插入的行排序：

select R.ID_TBL_REPORT, R.INDEX_NAME, R.INDEX_VALUE, R.ERROR, R.DATA, P.PROXY, P.SERVICE_GROUP

from TBL_REPORT R, TBL_REPORT_PROXY P

where P.PROXY = 'MyProxy'

and R.FK_TBL_REPORT_PROXY = P.ID_TBL_REPORT_PROXY

order by R.DATA desc -- ordering by the the last first

limit 1,10;

这个查询是如此之大和慢,以至于mysql显示此错误,并且没有结果行：

ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql_52d8_0.MYI'; try to repair it

那么,如何优化这个表/查询来获得结果呢？

编辑

MySQL [report]> explain select R.ID_TBL_REPORT, R.INDEX_NAME, R.INDEX_VALUE, R.ERROR, R.DATA, P.PROXY, P.SERVICE_GROUP from TBL_REPORT R, TBL_REPORT_PROXY P where P.PROXY = 'MyProxy' and R.FK_TBL_REPORT_PROXY = P.ID_TBL_REPORT_PROXY limit 1,10;

+----+-------------+-------+------+------------------------------+------------------------------+---------+-----------------------------------+--------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+------+------------------------------+------------------------------+---------+-----------------------------------+--------+-------------+

| 1 | SIMPLE | P | ALL | PRIMARY | NULL | NULL | NULL | 5 | Using where |

| 1 | SIMPLE | R | ref | TBL_INTER_TBL_INTER_PROXY_FK | TBL_INTER_TBL_INTER_PROXY_FK | 8 | wso2_report.P.ID_TBL_REPORT_PROXY | 262154 | |

+----+-------------+-------+------+------------------------------+------------------------------+---------+-----------------------------------+--------+-------------+

2 rows in set (0.00 sec)

MySQL [report]> explain select R.ID_TBL_REPORT, R.INDEX_NAME, R.INDEX_VALUE, R.ERROR, R.DATA, P.PROXY, P.SERVICE_GROUP from TBL_REPORT R, TBL_REPORT_PROXY P where P.PROXY = 'MyProxy' and R.FK_TBL_REPORT_PROXY = P.ID_TBL_REPORT_PROXY order by DATA desc limit 1,10;

+----+-------------+-------+------+------------------------------+------------------------------+---------+-----------------------------------+--------+----------------------------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+------+------------------------------+------------------------------+---------+-----------------------------------+--------+----------------------------------------------+

| 1 | SIMPLE | P | ALL | PRIMARY | NULL | NULL | NULL | 5 | Using where; Using temporary; Using filesort |

| 1 | SIMPLE | R | ref | TBL_INTER_TBL_INTER_PROXY_FK | TBL_INTER_TBL_INTER_PROXY_FK | 8 | wso2_report.P.ID_TBL_REPORT_PROXY | 262154 | |

+----+-------------+-------+------+------------------------------+------------------------------+---------+-----------------------------------+--------+----------------------------------------------+

2 rows in set (0.00 sec)

编辑2

@RickJames提出的两个查询的“解释”分别为：

+----+-------------+------------------+-------+---------------+---------+---------+------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+------------------+-------+---------------+---------+---------+------+------+-------------+

| 1 | SIMPLE | TBL_REPORT | index | NULL | IX_DATA | 4 | NULL | 2 | |

| 1 | SIMPLE | TBL_REPORT_PROXY | ALL | PRIMARY | NULL | NULL | NULL | 5 | Using where |

+----+-------------+------------------+-------+---------------+---------+---------+------+------+-------------+

+----+-------------+------------------+------+------------------------------+------------------------------+---------+--------------------------------------------------+--------+---------------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+------------------+------+------------------------------+------------------------------+---------+--------------------------------------------------+--------+---------------------------------+

| 1 | SIMPLE | TBL_REPORT_PROXY | ALL | PRIMARY | NULL | NULL | NULL | 5 | Using temporary; Using filesort |

| 1 | SIMPLE | TBL_REPORT | ref | TBL_INTER_TBL_INTER_PROXY_FK | TBL_INTER_TBL_INTER_PROXY_FK | 8 | wso2_report.TBL_REPORT_PROXY.ID_TBL_REPORT_PROXY | 263813 | |

+----+-------------+------------------+------+------------------------------+------------------------------+---------+--------------------------------------------------+--------+---------------------------------+

解决方法:

错误解释错误.

你有电源故障(或其他一些崩溃).您正在使用MyISAM(您应该使用InnoDB).那些导致“表的密钥文件不正确”.

CHECK TABLE TBL_REPORT R;

CHECK TABLE TBL_REPORT_PROXY;

如果要么说“修复”,那么执行REPAIR TABLE.

如果这不起作用,请查看此值：

SHOW VARIABLES LIKE 'tmpdir';

这是指单独的文件系统吗？这个文件系统小吗？也许你的文件系统(/ tmp)中的磁盘空间不足？如果是这样,请考虑不要将tmpdir放入其中. (实际上,如果磁盘空间不足,我希望会出现不同的错误消息.)

请使用SHOW CREATE TABLE;它比DESCRIBE更具描述性.我不知道你有什么索引.您需要一个以FK_TBL_REPORT_PROXY开头和/或以DATA开头的一个.

优化器想要从较小的表开始,特别是因为你有一个WHERE子句限制那里的行.

我有两个建议;其中一个或两个都有帮助：

这个版本试图强制它使用从DATA开始的索引,这将让它在10行之后退出：

select R.ID_TBL_REPORT, R.INDEX_NAME, R.INDEX_VALUE, R.ERROR,

R.DATA, P.PROXY, P.SERVICE_GROUP

from TBL_REPORT R FORCE INDEX(DATA)

JOIN TBL_REPORT_PROXY P IGNORE INDEX(PROXY)

ON R.FK_TBL_REPORT_PROXY = P.ID_TBL_REPORT_PROXY

where P.PROXY = 'MyProxy'

order by R.DATA desc -- ordering by the the last first

limit 1,10;

这个版本切换到HAVING试图欺骗它不是从PROXY表开始：

select R.ID_TBL_REPORT, R.INDEX_NAME, R.INDEX_VALUE, R.ERROR,

R.DATA, P.PROXY, P.SERVICE_GROUP

from TBL_REPORT R

JOIN TBL_REPORT_PROXY P

ON R.FK_TBL_REPORT_PROXY = P.ID_TBL_REPORT_PROXY

HAVING P.PROXY = 'MyProxy'

order by R.DATA desc -- ordering by the the last first

limit 1,10;

(我从逗号JOIN切换到首选的显式JOIN.)

请运行EXPLAIN EXTENDED SELECT …;然后显示警告;在每一个上,向我们展示输出.

标签：mysql,join

来源： https://codeday.me/bug/20190806/1603335.html