Mysql查询优化
查询1：
SELECT cid,
dl
FROM chal
WHERE cid IN (
SELECT cid
FROM c_users
WHERE uid = 636587
);
查询2：
SELECT chal.cid AS cid,
chal.dl AS dl
FROM chal,
c_users
WHERE uid = 808
AND chal.cid = c_users.cid;
cid是chal中的主键 cid和uid在c_users中索引，cid不唯一；
上述查询哪个更好？
解释如下
查询1使用两种类型的索引，即ALL和index_subquery
查询2使用两种类型的索引，即ALL和ref
>
我想知道为什么两个查询都将 ALL 作为索引类型，尽管 cid 是表 chal 中的主键。
Query 1:
SELECT cid,
dl
FROM chal
WHERE cid IN (
SELECT cid
FROM c_users
WHERE uid = 636587
);
Query 2:
SELECT chal.cid AS cid,
chal.dl AS dl
FROM chal,
c_users
WHERE uid = 808
AND chal.cid = c_users.cid;
cid is primary key in chal
cid and uid are indexed in c_users, cid is not unique;
Which of the above query is better?
Explain says the following
Query 1 uses two types of index namely ALL and index_subquery
Query 2 users two types of index namely ALL and ref
I wonder why both queries say ALL as type of index though cid is primary key in table chal.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
cid 是否已在 c_users 中建立索引？如果不是，则保证在这里进行全表扫描（又名“ALL”）。
Is cid indexed in c_users? If it's not, you're guaranteed a full table scan (aka "ALL") here.
我假设您问哪个查询会更快，那么根据经验，第二个查询会更快。但对于行数较少的表来说，差异并不显着。
I assume you are asking which query will be faster, then as a rule of thumb, the second query will be faster. But the difference will be insignificant for tables with a small number of rows.
我不会使用联接或嵌套选择。
我会在应用程序级别编写两条sql，随着规模的扩大，速度会快得多。
并且您的选择应该基于两个表上的主键。即cid
I wouldn't use joins or nested select.
I would write two sql at the application level which will be much faster as you scale.
and your select should be based on primary key on the both the tables. i.e cid
这些查询并不相同。
如果
c_users中给定用户有2个相等的cid，则第一个查询将为每个cid返回 1 条记录>，而第二个将返回两条记录。index_subquery是MySQL中的优化，它将用IN测试的表达式推入IN子查询并返回第一场比赛为 TRUE。第一个查询将始终使用
chal作为主表，而第二个查询可以在chal和c_users之间选择，并且很可能会选择c_users。您应该在
c_users (uid, cid)上创建复合索引。These queries are not identical.
If there are
2equalcids for a given user inc_users, the first query will return 1 record percid, while the second one will return two records.index_subqueryis the optimization inMySQLthat pushes the expression tested withINinto theINsubquery and returnsTRUEon the first match.The first query will always use
chalas a leading table, while the second one can choose betweechalandc_usersand most probably will choosec_users.You should create a composite index on
c_users (uid, cid).