mysql - 基于 WHERE 子句的 SELECT 查询的性能

Question

我在session表中有一些数据，其中包含用户位置以及与用户相关的一些其他数据

会话表由多个列组成，例如

• 身份证-PRIMARY KEY AUTO-INCREMENT
• session_id - UNIQUE KEY（每次都是唯一的，即使用户多次登录，也会生成唯一的）
• user_id（此用户 id 对每个用户都是唯一的）
• user_location（保存用户的坐标）

问题陈述

我想知道以下两个查询之间是否存在任何性能差异。

第一次查询

SELECT user_locationFROM session WHERE session_id= 'some-session-id-goes-here';

第二次查询

SELECT user_locationFROM session WHERE session_id= 'some-session-id-goes-here' AND user_id='usome-user-id';

这两个查询都提供了正确的数据，但是如果假设此会话表中存在数百万个数据，是否会对性能产生任何影响。

在较小的集合 上运行这两个查询data < 4000在.fetching time

标签： mysql

Answer 1

Normally, when you declare a column with UNIQUE Key it is indexed by default. As you have not mentioned if your user_id is declared unique or not(you said user_id is unique, I assume its values are but not defined in your create table script). So in that sense they will give same performance. But if you index your user_id too then the case can be different. When you have multiple column indexed and you are querying using them, you must maintain their index-sequence after where clause and your query performance will be better than the current one. Otherwise, the effect will be negative for not maintaining the sequence order. You can check columns index sequence by

show index from your_table_name

For some more insight, you can check here MySQL: unique field needs to be an index?