mysql-使用where子句中的OR优化sql语句
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql-使用where子句中的OR优化sql语句，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6642字，纯文字阅读大概需要10分钟。
内容图文
在5Gb大小的消息表上运行此查询.
问题在于执行需要> 3分钟.
SELECT m.id FROM messages m
LEFT JOIN dialog d on m.id=d.mid
WHERE (SELECT count(*)
FROM dialog
WHERE (m.from_id=uid1 and m.user_id=uid2)
OR (m.from_id=uid2 and m.user_id=uid1))=0 && read_state=0
LIMIT 100
我知道,这是通过“嵌套的SELECT”在哪里进行搜索的一种不好的做法,但是并没有找到选择这种行的另一种方法.试图将OR拆分为2个UNION语句,但都很长.
消息表结构：
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| from_id | int(11) | NO | MUL | NULL | |
| user_id | int(11) | NO | MUL | NULL | |
| group_id | int(11) | NO | | NULL | |
| to_number | varchar(30) | NO | MUL | NULL | |
| msg | text | NO | | NULL | |
| image | varchar(20) | NO | | NULL | |
| date | bigint(20) | NO | | NULL | |
| read_state | tinyint(1) | NO | | 0 | |
| removed | tinyint(1) | NO | | NULL | |
+------------+-------------+------+-----+---------+----------------+
对话表结构
+-----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| uid1 | int(11) | NO | MUL | NULL | |
| uid2 | int(11) | NO | MUL | NULL | |
| mid | int(11) | NO | | NULL | |
| anonym_id | int(10) unsigned | NO | | NULL | |
+-----------+------------------+------+-----+---------+----------------+
mysql>显示消息索引；
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| messages | 0 | PRIMARY | 1 | id | A | 12560908 | NULL | NULL | | BTREE | | |
| messages | 1 | to_number | 1 | to_number | A | 161037 | NULL | NULL | | BTREE | | |
| messages | 1 | from_id | 1 | from_id | A | 157011 | NULL | NULL | | BTREE | | |
| messages | 1 | from_id | 2 | to_number | A | 169742 | NULL | NULL | | BTREE | | |
| messages | 1 | user_id_2 | 1 | user_id | A | 314022 | NULL | NULL | | BTREE | | |
| messages | 1 | user_id_2 | 2 | read_state | A | 380633 | NULL | NULL | | BTREE | | |
| messages | 1 | user_id_2 | 3 | removed | A | 392528 | NULL | NULL | | BTREE | | |
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
mysql>显示对话框中的索引；
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| dialog | 0 | PRIMARY | 1 | id | A | 3125615 | NULL | NULL | | BTREE | | |
| dialog | 1 | uid1 | 1 | uid1 | A | 520935 | NULL | NULL | | BTREE | | |
| dialog | 1 | uid1 | 2 | uid2 | A | 3125615 | NULL | NULL | | BTREE | | |
| dialog | 1 | uid2 | 1 | uid2 | A | 1562807 | NULL | NULL | | BTREE | | |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
扩展说明
+----+--------------------+--------+-------+---------------+------+---------+------+----------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+--------+-------+---------------+------+---------+------+----------+----------+--------------------------+
| 1 | PRIMARY | m | ALL | NULL | NULL | NULL | NULL | 22190398 | 100.00 | Using where |
| 1 | PRIMARY | d | ALL | NULL | NULL | NULL | NULL | 3125621 | 100.00 | |
| 2 | DEPENDENT SUBQUERY | dialog | index | uid1,uid2 | uid1 | 8 | NULL | 3125621 | 100.00 | Using where; Using index |
+----+--------------------+--------+-------+---------------+------+---------+------+----------+----------+--------------------------+
解决方法:
首先想到的是将子查询查询更改为使用不存在而不是count(*).第二个是将其拆分为两个单独的子查询.第三是添加索引：
create index idx_messages_read_state_4 on messages_read_state(user_id, from_id, user_id, id);
create index idx_dialog_2 on dialog(uid1, uid2)
第四个是在外部查询中删除对话框的左联接.没有使用对话框中的任何字段,并且左联接表示未将其用于过滤.
查询如下：
select m.id
from messages m
where m.read_state = 0 and
not exists (select 1
from dialog d
where m.from_id = d.uid1 and m.user_id = d.uid2
) and
not exists (select 1
from dialog d
where m.from_id = d.uid2 and m.user_id = d.uid1
)
limit 100;
内容总结
以上是互联网集市为您收集整理的mysql-使用where子句中的OR优化sql语句全部内容，希望文章能够帮你解决mysql-使用where子句中的OR优化sql语句所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】