MySQL PHP | “从表中选择”使用“字母数字”-UUID。速度与索引整数/索引字符
目前，我使用以下方法从“table01 和 table02”中选择行：
SELECT t1.*,t2.* FROM table01 AS t1
INNER JOIN table02 AS t2 ON (t1.ID = t2.t1ID)
WHERE t1.UUID = 'whatever';
UUID 列是唯一索引，类型：char(15)，带有字母数字输入。我知道这不是从数据库中选择数据的最快方法，但 UUID 是前端可用的唯一行标识符。
由于我必须按 UUID 而不是 ID 进行选择，因此如果假设表由 100'000 行组成，我需要知道应该选择这两个选项中的哪一个。我会关注哪些速度差异，UUID 的索引是否会变大并落后于数据库？
在执行“大”选择之前获取 ID
1. $id = SELECT ID FROM table01 WHERE UUID = '{alphanumeric character}';
2. SELECT t1.*,t2.* FROM table01 AS t1
INNER JOIN table02 AS t2 ON (t1.ID = t2.t1ID)
WHERE t1.ID = $id;
或使用 UUID 保持现在的状态。
2. SELECT t1.*,t2.* FROM table01 AS t1
INNER JOIN table02 AS t2 ON (t1.ID = t2.t1ID)
WHERE t1.UUID = 'whatever';
旁注：所有新行都是通过在尝试插入新行之前检查系统生成的 uniqueid 是否存在来创建的。保持列始终唯一。
At the moment, I select rows from 'table01 and table02' using:
SELECT t1.*,t2.* FROM table01 AS t1
INNER JOIN table02 AS t2 ON (t1.ID = t2.t1ID)
WHERE t1.UUID = 'whatever';
The UUID column is a unique index, type: char(15), with alphanumeric input. I know this isn't the fastest way to select data from the database, but the UUID is the only row-identifier that is available to the front-end.
Since I have to select by UUID, and not ID, I need to know what of these two options I should go for, if say the table consists of 100'000 rows. What speed differences would I look at, and would the index for the UUID grow to large, and lag the DB?
Get the ID before doing the "big" select
1. $id = SELECT ID FROM table01 WHERE UUID = '{alphanumeric character}';
2. SELECT t1.*,t2.* FROM table01 AS t1
INNER JOIN table02 AS t2 ON (t1.ID = t2.t1ID)
WHERE t1.ID = $id;
Or keep it the way it is now, using the UUID.
2. SELECT t1.*,t2.* FROM table01 AS t1
INNER JOIN table02 AS t2 ON (t1.ID = t2.t1ID)
WHERE t1.UUID = 'whatever';
Side note: All new rows are created by checking if the system generated uniqueid exists before trying to insert a new row. Keeping the column always unique.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
为什么不尝试一下呢？使用这些表创建一个新数据库。编写一个快速的 php 脚本，用比您想象的存储更多的记录填充表（如果您期望 10 万行，则插入 1000 万行）。然后尝试不同的索引和查询（记住，
EXPLAIN是你的朋友）...当你最终得到你认为有效的东西时，将查询放入网络服务器上的脚本中并使用
执行它ab(Apache Bench)。您可以观察当增加请求的并发性时会发生什么（一次 1 个、一次 2 个、一次 10 个等）。所有这些都不会花费太长时间（最多可能几个小时），但对于您的特定问题，它会给您比 SO 中的任何人都更好的答案（因为我们不知道您的数据库服务器配置、确切的架构、内存限制等）...
Why not just try it out? Create a new db with those tables. Write a quick php script to populate the tables with more records than you can imagine being stored (if you're expecting 100k rows, insert 10 million). Then experiment with different indexes and queries (remember,
EXPLAINis your friend)...When you finally get something you think works, put the query into a script on a webserver and hit it with
ab(Apache Bench). You can watch what happens as you increase the concurrency of the requests (1 at a time, 2 at a time, 10 at a time, etc).All this shouldn't take too long (maybe a few hours at most), but it will give you a FAR better answer than anyone at SO could for your specific problem (as we don't know your DB server config, exact schema, memory limits, etc)...
第二种解决方案具有最佳性能。您需要在两个解决方案中通过 UUID 查找行，但在第一个解决方案中，您首先通过 UUID 进行查找，然后通过主键进行更快的查找，但随后您已经通过 UUID 找到了正确的行，因此第二次查找更快并不重要，因为第二次查找完全没有必要。
The second solution have the best performance. You will need to look up the row by the UUID in both solutions, but in the first solution you first do it by UUID, and then do a faster lookup by primary key, but then you've already found the right row by UUID so it doesn't matter that the second lookup is faster because the second lookup is unnecessary altogether.