mysql - 如何通过多个连接加快 MYSQL 查询?
问题描述
寻求帮助以加快此查询:
SELECT tickets.ticketid, datenew, products.name
FROM tickets
INNER JOIN ticketlines ON tickets.id = ticketlines.ticket
INNER JOIN products ON ticketlines.product = products.id
INNER JOIN receipts ON receipts.id = tickets.id
WHERE (category !='feb765ef-c8a8-4fa2-969c-90f67fe6b3be' AND category!='888f4893-f300-43b5-9933-d549ade744e0' AND category !='8f2031e8-64a4-4abf-8175-3d2bedd9f950' AND category !='ca370ced-1c3b-434c-905e-ec1bc709543b' AND category !='f92ff0ac-fa11-4a5f-a3dd-e0d9ed9c171a' AND category !='445e8605-1cd9-4714-b3fd-7389ac29c206' and category !='05143c54-8a7e-4ce2-97cc-f84f9cf41395' AND category !='8c78afea-b9e2-44cf-b497-c384045b3202' AND category !='95919f7f-ff2e-4aa1-8110-ef63c022c01b' AND category !='f4f88b05-38a1-4956-9182-4c04a0808df7') AND datedone IS NULL
ORDER BY ticketid
这是来自我用来提取数据以显示订单的收银机数据库。该结构是收据具有主 ID、timein 和 timedone(DATEDONE) 时间戳。票证表的票证 ID 与收据 ID 相同。Ticketlines 表是收据/票上的行项目。然后 Products 表具有产品的人类可读定义。
查询是拉取所有未完成的项目,即 DATEDONE 为空,并显示已订购的项目。
Ticket 和 Receipt 表有 15K 行,Ticketlines 有 ~20K 行,Products 有 1.5k。
相当小的数据。但是这个查询需要 20 多秒。我认为由于我对所有内容都使用主键 ID,因此我不需要索引任何内容,但我是一个完全的菜鸟,所以我很高兴被告知我错了。我会很感激任何帮助,并可以提供任何进一步的细节。谢谢!
编辑:
根据评论,我正在显示表结构。对于格式的噩梦,我很抱歉,我不熟悉这个平台并且不确定如何使它更具可读性:-(
| receipts | CREATE TABLE `receipts` (
`ID` varchar(255) NOT NULL,
`MONEY` varchar(255) NOT NULL,
`DATENEW` datetime NOT NULL,
`ATTRIBUTES` mediumblob,
`PERSON` varchar(255) DEFAULT NULL,
`DATEDONE` datetime DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `RECEIPTS_FK_MONEY` (`MONEY`),
KEY `RECEIPTS_INX_1` (`DATENEW`),
CONSTRAINT `RECEIPTS_FK_MONEY` FOREIGN KEY (`MONEY`) REFERENCES `closedcash` (`MONEY`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
| tickets | CREATE TABLE `tickets` (
`ID` varchar(255) NOT NULL,
`TICKETTYPE` int(11) NOT NULL DEFAULT '0',
`TICKETID` int(11) NOT NULL,
`PERSON` varchar(255) NOT NULL,
`CUSTOMER` varchar(255) DEFAULT NULL,
`STATUS` int(11) NOT NULL DEFAULT '0',
`DONE` varchar(255) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `TICKETS_FK_2` (`PERSON`),
KEY `TICKETS_CUSTOMERS_FK` (`CUSTOMER`),
KEY `TICKETS_TICKETID` (`TICKETTYPE`,`TICKETID`),
CONSTRAINT `TICKETS_CUSTOMERS_FK` FOREIGN KEY (`CUSTOMER`) REFERENCES `customers` (`ID`),
CONSTRAINT `TICKETS_FK_2` FOREIGN KEY (`PERSON`) REFERENCES `people` (`ID`),
CONSTRAINT `TICKETS_FK_ID` FOREIGN KEY (`ID`) REFERENCES `receipts` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
| ticketlines | CREATE TABLE `ticketlines` (
`TICKET` varchar(255) NOT NULL,
`LINE` int(11) NOT NULL,
`PRODUCT` varchar(255) DEFAULT NULL,
`ATTRIBUTESETINSTANCE_ID` varchar(255) DEFAULT NULL,
`UNITS` double NOT NULL,
`PRICE` double NOT NULL,
`TAXID` varchar(255) NOT NULL,
`ATTRIBUTES` mediumblob,
PRIMARY KEY (`TICKET`,`LINE`),
KEY `TICKETLINES_FK_2` (`PRODUCT`),
KEY `TICKETLINES_ATTSETINST` (`ATTRIBUTESETINSTANCE_ID`),
KEY `TICKETLINES_FK_3` (`TAXID`),
CONSTRAINT `TICKETLINES_ATTSETINST` FOREIGN KEY (`ATTRIBUTESETINSTANCE_ID`) REFERENCES `attributesetinstance` (`ID`),
CONSTRAINT `TICKETLINES_FK_2` FOREIGN KEY (`PRODUCT`) REFERENCES `products` (`ID`),
CONSTRAINT `TICKETLINES_FK_3` FOREIGN KEY (`TAXID`) REFERENCES `taxes` (`ID`),
CONSTRAINT `TICKETLINES_FK_TICKET` FOREIGN KEY (`TICKET`) REFERENCES `tickets` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
| products | CREATE TABLE `products` (
`ID` varchar(255) NOT NULL,
`REFERENCE` varchar(255) NOT NULL,
`CODE` varchar(255) NOT NULL,
`CODETYPE` varchar(255) DEFAULT NULL,
`NAME` varchar(255) NOT NULL,
`PRICEBUY` double NOT NULL,
`PRICESELL` double NOT NULL,
`CATEGORY` varchar(255) NOT NULL,
`TAXCAT` varchar(255) NOT NULL,
`ATTRIBUTESET_ID` varchar(255) DEFAULT NULL,
`STOCKCOST` double DEFAULT NULL,
`STOCKVOLUME` double DEFAULT NULL,
`IMAGE` mediumblob,
`ISCOM` bit(1) NOT NULL DEFAULT b'0',
`ISSCALE` bit(1) NOT NULL DEFAULT b'0',
`ISKITCHEN` bit(1) NOT NULL DEFAULT b'0',
`PRINTKB` bit(1) NOT NULL DEFAULT b'0',
`SENDSTATUS` bit(1) NOT NULL DEFAULT b'0',
`ISSERVICE` bit(1) NOT NULL DEFAULT b'0',
`ATTRIBUTES` mediumblob,
`DISPLAY` varchar(255) DEFAULT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `PRODUCTS_INX_0` (`REFERENCE`),
UNIQUE KEY `PRODUCTS_INX_1` (`CODE`),
UNIQUE KEY `PRODUCTS_NAME_INX` (`NAME`),
KEY `PRODUCTS_FK_1` (`CATEGORY`),
KEY `PRODUCTS_TAXCAT_FK` (`TAXCAT`),
KEY `PRODUCTS_ATTRSET_FK` (`ATTRIBUTESET_ID`),
CONSTRAINT `PRODUCTS_ATTRSET_FK` FOREIGN KEY (`ATTRIBUTESET_ID`) REFERENCES `attributeset` (`ID`),
CONSTRAINT `PRODUCTS_FK_1` FOREIGN KEY (`CATEGORY`) REFERENCES `categories` (`ID`),
CONSTRAINT `PRODUCTS_TAXCAT_FK` FOREIGN KEY (`TAXCAT`) REFERENCES `taxcategories` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
此外,这里是 EXPLAIN 输出:
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+--------+--------------------------+---------+---------+------------------------------------+-------+----------+----------------------------------------------+
| 1 | SIMPLE | receipts | NULL | ALL | PRIMARY | NULL | NULL | NULL | 14624 | 10.00 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | tickets | NULL | eq_ref | PRIMARY | PRIMARY | 767 | receipts.ID | 1 | 100.00 | NULL |
| 1 | SIMPLE | ticketlines | NULL | ref | PRIMARY,TICKETLINES_FK_2 | PRIMARY | 767 | receipts.ID | 1 | 100.00 | Using where |
| 1 | SIMPLE | products | NULL | eq_ref | PRIMARY,PRODUCTS_FK_1 | PRIMARY | 767 | ticketlines.PRODUCT | 1 | 97.97 | Using where |
+----+-------------+-------------+------------+--------+--------------------------+---------+---------+------------------------------------+-------+----------+----------------------------------------------+
4 rows in set, 1 warning (0.04 sec)
解决方案
尝试将所有列的数据类型更改为最低要求。在 WHERE 中的列上添加索引。在ticketlines.ticket 上添加索引。
推荐阅读
- swift - 即使电子邮件格式不正确,验证也始终返回 true
- java - 如何在并发环境中获取 DynamoDB 中的下一个免费项目
- java - Android - 我们在 HashMap 中使用枚举作为键好吗
代替字符串? - html - HTML 嵌套表格输出不符合预期
- java - Zipkin & Sleuth,Tracer 缺少 createSpan 方法
- javascript - 使用 JS 将字符串推送到对象
- django - Django urls.py:将动态 url 参数传递给 include()
- codeigniter - 取消定义变量 id_gambar codeigniter
- php - 自定义帖子类型每年存档下拉菜单
- flutter - 在颤振中使用依赖项时出错