MySQL-- SQL优化经过
背景:有一个SQL如下
SELECT ut.*
FROM user_transaction ut
WHERE tx_type IN (1, 4, 8) AND tx_status = 1
AND share_public = 1
AND user_id != 103 AND (peer_user_id IS NULL OR peer_user_id != 103)
AND (
user_id IN (SELECT DISTINCT friend_user_id from user_friend uf WHERE uf.user_id = 103 AND uf.status = 1)
AND
peer_user_id IN (SELECT DISTINCT friend_user_id from user_friend uf WHERE uf.user_id = 103 AND uf.status = 1)
)
ORDER BY tx_date DESC
LIMIT 1, 10;
--也有下面OR的情况,业务需要
SELECT ut.*
FROM user_transaction ut
WHERE tx_type IN (1, 4, 8) AND tx_status = 1
AND share_public = 1
AND user_id != 103 AND (peer_user_id IS NULL OR peer_user_id != 103)
AND (
user_id IN (SELECT DISTINCT friend_user_id from user_friend uf WHERE uf.user_id = 103 AND uf.status = 1)
OR
peer_user_id IN (SELECT DISTINCT friend_user_id from user_friend uf WHERE uf.user_id = 103 AND uf.status = 1)
)
ORDER BY tx_date DESC
LIMIT 1, 10;
有索引,速度还算可以
问题所在
- 这里用了in,如果in里面的数据比较多,索引就可能会没有(InnoDB几十条数据就会出现),影响执行速度。
- 这里条件用了OR,想要改SQL不容易。
修改前的思考,解决方法
- 用Between代替IN,不过这里是IN里面用了子查询,所以不能用。
- 用Exists代替IN,试了下,结果反而没了索引,速度更慢。
- 用Union All代替IN或者AND或者OR,这种方法在这里的IN行不通,因为IN里面的ID很多。
- 用Inner join代替IN,这是网上的方法
实际修改
用Between代替IN
这个方法直接就没有试,因为不合适。
用Exists代替IN
修改如下:
SELECT distinct pub_name
FROM publishers
WHERE pub_id IN
(SELECT pub_id
FROM titles
WHERE type = 'business')
SELECT DISTINCT pub_name
FROM publishers
WHERE EXISTS
(SELECT *
FROM titles
WHERE pub_id = publishers.pub_id
AND type = 'business')
但是实际情况反而索引没有了,执行速度更慢。
用Inner join代替IN
写法如下:
SELECT ut.*
FROM user_transaction ut
INNER JOIN user_friend uf ON uf.friend_user_id = ut.user_id AND uf.user_id = 103 AND uf.status = 1
INNER JOIN user_friend uf2 ON uf2.friend_user_id = ut.peer_user_id AND uf2.user_id = 103 AND uf2.status = 1
WHERE tx_type IN (1, 4, 8) AND tx_status = 1
AND share_public = 1
AND ut.user_id != 103 AND (peer_user_id IS NULL OR peer_user_id != 103)
ORDER BY tx_date DESC
LIMIT 1, 10;
这样写,既保证了索引,对SQL的影响也不大,但是还剩最后一个问题,就是怎么将OR的数据合在一起。
使用Union All 或Union
写法如下:
(
SELECT ut.*
FROM user_transaction ut
INNER JOIN user_friend uf ON uf.friend_user_id = ut.user_id AND uf.user_id = 103 AND uf.status = 1
WHERE tx_type IN (1, 4, 8) AND tx_status = 1
AND share_public = 1
AND ut.user_id != 103 AND (peer_user_id IS NULL OR peer_user_id != 103)
)
UNION
(
SELECT ut.*
FROM user_transaction ut
INNER JOIN user_friend uf ON uf.friend_user_id = ut.peer_user_id AND uf.user_id = 103 AND uf.status = 1
WHERE tx_type IN (1, 4, 8) AND tx_status = 1
AND share_public = 1
AND ut.user_id != 103 AND (peer_user_id IS NULL OR peer_user_id != 103)
)
ORDER BY tx_date DESC
LIMIT 0, 10;
这样就将OR条件的数据合并在一起了,而可以思考是否过滤重复。