优化 MySQL 搜索流程 [英] Optimize MySQL search process

问题描述

这是场景 1.

我有一个名为items"的表，表内有 2 列，例如.G.item_id 和 item_name.我以这种方式存储我的数据:item_id |项目名称

I have a table called "items", inside the table has 2 columns, e. g. item_id and item_name. I store my data in this way: item_id | item_name

Ss001   | Shirt1
Sb002   | Shirt2
Tb001   | TShirt1
Tm002   | TShirt2

...等，我以这种方式存储:第一个字母是衣服的代码，即S代表衬衫，T代表T恤第二个字母是大小，即 s 代表小，m 代表中，b 代表大假设在我的物品表中，我有 10,000 件物品.我想做快速检索，假设我想找到一件特定的衬衫，我可以使用:

... etc, i store in this way: first letter is the code for clothes, i.e S for shirt, T for tshirt second letter is size, i.e s for small, m for medium and b for big Lets say in my items table i got 10,000 items. I want to do fast retrieve, lets say I want to find a particular shirt, can I use:

方法一:

SELECT * from items WHERE item_id LIKE Sb99; 

或者我应该这样做:

方法二:

SELECT * from items WHERE item_id LIKE S*; 

*存储结果，然后执行第二次搜索大小，然后第三次搜索id.就像哈希表的概念.我想要实现的是，不是搜索所有数据，我想通过先搜索衣服代码，然后是尺寸代码，然后是 id 代码来最小化搜索.就mysql的速度而言，哪一个更好.从长远来看，哪个更好.我想减少流量，而不是经常打扰数据库.

*Store the result, then execute second search for the size, then third search for the id. Like the hash table concept. What I want to achieve is, instead of search all the data, I want to minimize the search by search the clothes code first, follow by size code and then id code. Which one is better in term of speed in mysql. And which one is better in long run. I want to reduce the traffic and not to disturb the database so often.

感谢大家解决我的第一个场景.但另一种情况出现了:

Thanks guys for solving my first scenario. But another scenario comes in:

场景 2:

我正在使用 PHP 和 MySQL.从前面的故事继续.如果我的用户表结构是这样的:

I am using PHP and MySQL. Continue from the preivous story. If my users table structure is like this:

user_id | username | items_collected

U0001   | Alex     | Ss001;Tm002
U0002   | Daniel   | Tb001;Sb002
U0003   | Michael  | ...
U0004   | Thomas   | ...

我将 items_collected 存储为 id 形式，因为一天每个用户最多可以收集数百个项目，如果我存储为字符串，即 Shirt1、pants2、...，它将需要非常大量的数据库空间(想象一下，如果我们有 1000 个用户，有些项目名称很长).

I store the items_collected in id form because one day each user can collect up to hundreds items, if I store as string, i.e. Shirt1, pants2, ..., it would required a very large amount of database spaces (imagine if we have 1000 users and some items name are very long).

如果我以 id 形式存储会更容易维护吗?

Would it be easier to maintain if I store in id form?

如果可以说，我想显示图像，图像的名称是项目名称 + jpg.怎么做?是不是这样的:

And if lets say, I want to display the image, and the image's name is the item's name + jpg. How to do that? Is it something like this:

$result = 从 userid= $userid 的用户中选择 items_collected

$result = Select items_collected from users where userid= $userid

使用php爆炸:

$itemsCollected = expand($result, ";");

$itemsCollected = explode($result, ";");

之后，匹配items表中的每个item，所以它会:

After that, matching each item in the items table, so it would like:

衬衫 1、裤子 2 等

shirt1, pants2 etc

den 使用循环函数，循环每个值并添加.jpg"以显示图像?

Den using loop function, loop each value and add ".jpg" to display the image?

推荐答案

第一种方法会更快 - 但 IMO 这不是正确的方法.我同意 tehvan 的意见.

The first method will be faster - but IMO it's not the right way of doing it. I'm in agreement with tehvan about that.

我建议保持 item_id 不变，但添加两个额外的字段，一个用于代码，一个用于大小，然后您可以这样做:

I'd recommend keeping the item_id as is, but add two extra fields one for the code and one for the size, then you can do:

select * from items where item_code = 'S' and item_size = 'm' 

通过索引，性能将大大提高，您将能够轻松匹配一系列大小或代码.

With indexes the performance will be greatly increased, and you'll be able to easily match a range of sizes, or codes.

select * from items where item_code = 'S' and item_size IN ('m','s')

按如下方式迁移数据库:

Migrate the db as follows:

alter table items add column item_code varchar(1) default '';
alter table items add column item_size varchar(1) default '';

update items set item_code = SUBSTRING(item_id, 1, 1);
update items set item_size = SUBSTRING(item_id, 2, 1);

对代码的更改应该同样易于添加.长期收益将是值得的.

The changes to the code should be equally simple to add. The long term benefit will be worth the effort.

对于场景 2 - 这不是从数据库中存储和检索数据的有效方法.以这种方式使用时，数据库仅充当存储引擎，通过将多个数据编码到字段中，您将阻止数据库的关系部分发挥作用.

For scenario 2 - that is not an efficient way of storing and retrieving data from a database. When used in this way the database is only acting as a storage engine, by encoding multiple data into fields you are precluding the relational part of the database from being useful.

在这种情况下您应该做的是拥有另一个表，称为items_collected".架构将沿着

What you should do in that circumstance is to have another table, call it 'items_collected'. The schema would be along the lines of

CREATE TABLE items_collected (
   id int(11) NOT NULL auto_increment KEY,
   userid int(11) NOT NULL,
   item_code varchar(10) NOT NULL,  
   FOREIGN KEY (`userid`) REFERENCES `user`(`id`),
   FOREIGN KEY (`itemcode`) REFERENCES `items`(`item_code`)
 );

外键确保有参照完整性，这是必不可少的具有参照完整性.

The foreign keys ensure that there is Referential integrity, it's essential to have referential integrity.

那么对于您提供的示例，您将有多个记录.

Then for the example you give you would have multiple records.

 user_id | username | items_collected
 U0001   | Alex     | Ss001
 U0001   | Alex     | Tm002
 U0002   | Daniel   | Sb002
 U0002   | Daniel   | Tb001
 U0003   | Michael  | ...
 U0004   | Thomas   | ...

这篇关于优化 MySQL 搜索流程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！