long-sql优化实例 todo

CREATE TABLE `HBT_MessageFlow` (
   `Id` int(11) NOT NULL AUTO_INCREMENT,
   `ShopId` int(11) DEFAULT NULL,
   `Receive` varchar(50) NOT NULL COMMENT '接收方',
   `ConfigId` int(11) DEFAULT NULL COMMENT '配置信息模版id',
   `RefType` int(11) NOT NULL COMMENT '类型',
   `RefId` varchar(50) NOT NULL COMMENT '类型值',
   `SendStatus` int(11) NOT NULL COMMENT '发送状态',
   `Meno` varchar(200) NOT NULL COMMENT 'meno',
   `Content` varchar(1000) NOT NULL COMMENT '参数内容',
   `AddTime` datetime NOT NULL,
   `UpdateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
   PRIMARY KEY (`Id`),
   KEY `IX_ADDTIME` (`AddTime`),
   KEY `IX_UPDATETIME` (`UpdateTime`),
   KEY `IX_RECEIVE` (`Receive`),
   KEY `IX_REFTYPE` (`RefType`),
   KEY `IX_REFID` (`RefId`)
  ) ENGINE=InnoDB AUTO_INCREMENT=1384415 DEFAULT CHARSET=utf8 COMMENT='发送消息记录表'

Table Information
Created: 2016-09-12
Engine: InnoDB
Rows: 815558
Size: 282 MiB
Encoding: utf8_general_ci
Auto-Increment: 1384415

原始long-sql

explain select Id ,shopId,receive, configId, refType, refId, sendStatus, meno, content,addTime from HBT_MessageFlow WHERE ConfigId = 13 and Receive = '18358169297' and SendStatus = 200 Order by AddTime desc limit 1

645ms

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	HBT_MessageFlow	index	IX_RECEIVE	IX_ADDTIME	5		66	Using where

---

增加limit

explain select Id ,shopId,receive, configId, refType, refId, sendStatus, meno, content,addTime from HBT_MessageFlow WHERE ConfigId = 13 and Receive = '18358169297' and SendStatus = 200 Order by AddTime desc limit 10

2.287s

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	HBT_MessageFlow	index	IX_RECEIVE	IX_ADDTIME	5		663	Using where

explain select Id ,shopId,receive, configId, refType, refId, sendStatus, meno, content,addTime from HBT_MessageFlow WHERE ConfigId = 13 and Receive = '18358169297' and SendStatus = 200 Order by AddTime desc limit 184

26ms

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	HBT_MessageFlow	ref	IX_RECEIVE	IX_RECEIVE	152	const	12192	Using index condition; Using where; Using filesort

---

去除desc

explain select Id ,shopId,receive, configId, refType, refId, sendStatus, meno, content,addTime from HBT_MessageFlow WHERE ConfigId = 13 and Receive = '18358169297' and SendStatus = 200 Order by AddTime limit 1

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	HBT_MessageFlow	index	IX_RECEIVE	IX_ADDTIME	5		66	Using where

---

更改order列

explain select Id ,shopId,receive, configId, refType, refId, sendStatus, meno, content,addTime from HBT_MessageFlow WHERE ConfigId = 13 and Receive = '18358169297' and SendStatus = 200 Order by Id desc limit 1

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	HBT_MessageFlow	ref	IX_RECEIVE	IX_RECEIVE	152	const	12192	Using where

---

删除SendStatus和ConfigId条件

explain select Id ,shopId,receive, configId, refType, refId, sendStatus, meno, content,addTime from HBT_MessageFlow WHERE Receive = '18358169297'  Order by AddTime desc limit 1

1ms

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	HBT_MessageFlow	index	IX_RECEIVE	IX_ADDTIME	5		66	Using where

---

更改Receive条件为15901736035(特征：首次命中记录的id靠前)

explain select Id ,shopId,receive, configId, refType, refId, sendStatus, meno, content,addTime from HBT_MessageFlow WHERE ConfigId = 13 and Receive = '15901736035' and SendStatus = 200 Order by AddTime desc limit 1

1ms

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	HBT_MessageFlow	ref	IX_RECEIVE	IX_RECEIVE	152	const	184	Using index condition; Using where; Using filesort

---

Receive + Addtime联合查询