blocks|key|1410967|text|听起来WHERE子句中的一些列可能不是indexed。索引用于快速查找具有特定列值的行。如果没有索引，MySQL必须从第一行开始，然后遍历整个表以查找相关行。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1410968|您可能会发现EXPLAIN在分析查询方面很有帮助。|1410969|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/4.1/en/mysql-indexes.html|1|http://dev.mysql.com/doc/refman/5.0/en/explain.html^0|3|5|J|7|0|0|6|7|6|7|1|0^^$0|@$1|2|3|4|5|6|7|R|8|@$9|S|A|T|B|C]]|D|@$9|U|A|V|1|W]]|E|$]]|$1|F|3|G|5|6|7|X|8|@$9|Y|A|Z|B|C]]|D|@$9|10|A|11|1|12]]|E|$]]|$1|H|3|-4|5|6|7|13|8|@]|D|@]|E|$]]]|I|$J|$5|K|L|M|E|$N|O]]|P|$5|K|L|M|E|$N|Q]]]]

It sounds likely that some of the columns in your <code>WHERE</code> clause are not <a href="http://dev.mysql.com/doc/refman/4.1/en/mysql-indexes.html" rel="nofollow noreferrer">indexed</a>. Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. 

You might find <a href="http://dev.mysql.com/doc/refman/5.0/en/explain.html" rel="nofollow noreferrer"><code>EXPLAIN</code></a> helpful in analyzing your queries.

blocks|key|2290749|text|在每个表中的url字段上看起来绝对像索引是可行的|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2290750|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

definitely looks like an index on the url field in each table is the way to go

blocks|key|1406850|text|查找连接，特别是查看内部连接、左连接和外连接之间的区别。还要对要查找的所有字段进行索引。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1406851|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Look up JOINs and especially look at the difference between INNER JOINS, LEFT JOINS and OUTER JOINS. Also INDEX all the fields on which you are going to do a lookup.

blocks|key|3697951|text|可能你的索引有问题！|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3697952|在任何情况下，像urls这样的长字符串都会导致主键性能低下。占用了索引中的大量空间，因此索引不像其应有的那样密集，每个IO加载的行指针数也较少。同样，对于urls，99%25的字符串都以“http://www”开头。因此，数据库引擎必须先比较13个字符，然后才能确定行不匹配。|offset|length|3697953|一种解决方案是使用一些散列函数，如MD5、SHA1甚至CRC32，从字符串中获取原始二进制值，并将该值用作表的主键。CRC32是一个很好的整数大小的主键，但几乎可以肯定的是，在某些阶段，你会遇到两个CRC32值相同的url，所以你需要存储和比较"url“字符串以确保。其他散列函数返回更长的值(在“原始”模式下分别为16字节和20字节)，但冲突的可能性非常小，因此不值得担心。|3697954|。|3697955|entityMap|0|LINK|mutability|MUTABLE|url|http://www/^0|0|2K|A|0|0|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|S|8|@]|9|@$D|T|E|U|1|V]]|A|$]]|$1|F|3|G|5|6|7|W|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|X|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|K|$L|$5|M|N|O|A|$P|Q]]]]

Probably something wrong with your indexes!

In any case long character strings like urls make for poorly performing primary keys. The take up a lot of room in the index and so the indexes are not as dense as they could be and less row pointers are loaded per IO. Also with urls the chances are that 99% of your strings start with "<a href="http://www">http://www</a>." so the database engine has to compare 13 characters before it decides a row does not match.

One solution to this is to use some hash finction like MD5, SHA1 or even CRC32 to get a raw binary value from your strings and to use this value as the primary key for your tables. CRC32 makes a nice integer sized primary key but its almost certain that at some stage you will encounter two urls that hash to the same CRC32 value so you will need to store and compare the "url" string to be sure. The other hash functions return longer values (16 bytes and 20 bytes respectively in "raw" mode) but the chances of a collision are so small that its not worth bothering about.

.

blocks|key|3698016|text|我不确定你是否已经解决了这个问题，但这是我制作的一些测试数据。有许多因素会影响您的查询速度，因此我的简单测试用例可能无法准确地反映您的表或数据。然而，它们可以作为一个有用的起点。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3698017|首先，创建5个简单的表，每个表都具有相同的结构。与您的表一样，我在url列上使用了UNIQUE索引：|offset|length|style|CODE|3698018|CREATE+TABLE+`table1`+(
++++`id`+int(11)+NOT+NULL+auto_increment,
++++`url`+varchar(255)+default+NULL,
++++PRIMARY+KEY++(`id`),
++++UNIQUE+KEY+`url`+(`url`)
)+ENGINE=InnoDB;

CREATE+TABLE+table2+LIKE+table1;
CREATE+TABLE+table3+LIKE+table1;
CREATE+TABLE+table4+LIKE+table1;
CREATE+TABLE+table5+LIKE+table1;|code-block|syntax|javascript|3698019|以下脚本创建一个存储过程，用于在每个表中填充10,000行数据：|3698020|DELIMITER+//
DROP+PROCEDURE+IF+EXISTS+test.autofill//
CREATE+PROCEDURE+test.autofill()
BEGIN
++++DECLARE+i+INT+DEFAULT+5;
++++WHILE+i+<+10000+DO
++++++++INSERT+INTO+table1+(url)+VALUES+(CONCAT('wwww.stackoverflow.com/',+i+));
++++++++INSERT+INTO+table2+(url)+VALUES+(CONCAT('wwww.stackoverflow.com/',+10000+-+i+));
++++++++INSERT+INTO+table3+(url)+VALUES+(CONCAT('wwww.stackoverflow.com/',+i+%2B+6000+));
++++++++INSERT+INTO+table4+(url)+VALUES+(CONCAT('wwww.stackoverflow.com/',+i+%2B+3000+));
++++++++INSERT+INTO+table5+(url)+VALUES+(CONCAT('wwww.stackoverflow.com/',+i+%2B+2000+));
++++++++SET+i+=+i+%2B+1;
++++END+WHILE;
END;
//
DELIMITER+;

CALL+test.autofill();|3698021|现在，每个表包含10,000行。现在可以使用您的SELECT语句来查询数据：|3698022|SELECT+*+
FROM+table1,table2,table3,table4,table5
WHERE+table1.url+=+table2.url
AND+table1.url+=+table3.url
AND+table1.url+=+table4.url
AND+table1.url+=+table5.url
AND+table1.url+=+'wwww.stackoverflow.com/8000';|3698023|这几乎可以立即产生以下结果：|3698024|%2B------%2B-----------------------------%2B------%2B-----------------------------%2B------%2B-----------------------------%2B------%2B-----------------------------%2B------%2B-----------------------------%2B
%7C+id+++%7C+url+++++++++++++++++++++++++%7C+id+++%7C+url+++++++++++++++++++++++++%7C+id+++%7C+url+++++++++++++++++++++++++%7C+id+++%7C+url+++++++++++++++++++++++++%7C+id+++%7C+url+++++++++++++++++++++++++%7C
%2B------%2B-----------------------------%2B------%2B-----------------------------%2B------%2B-----------------------------%2B------%2B-----------------------------%2B------%2B-----------------------------%2B
%7C+7996+%7C+wwww.stackoverflow.com/8000+%7C+1996+%7C+wwww.stackoverflow.com/8000+%7C+1996+%7C+wwww.stackoverflow.com/8000+%7C+4996+%7C+wwww.stackoverflow.com/8000+%7C+5996+%7C+wwww.stackoverflow.com/8000+%7C
%2B------%2B-----------------------------%2B------%2B-----------------------------%2B------%2B-----------------------------%2B------%2B-----------------------------%2B------%2B-----------------------------%2B|3698025|EXPLAIN+SELECT显示了查询速度非常快的原因：|3698026|EXPLAIN+SELECT+*+
FROM+table1,table2,table3,table4,table5
WHERE+table1.url+=+table2.url
AND+table1.url+=+table3.url
AND+table1.url+=+table4.url
AND+table1.url+=+table5.url
AND+table1.url+=+'wwww.stackoverflow.com/8000';

%2B----%2B-------------%2B--------%2B-------%2B---------------%2B------%2B---------%2B-------%2B------%2B-------------%2B
%7C+id+%7C+select_type+%7C+table++%7C+type++%7C+possible_keys+%7C+key++%7C+key_len+%7C+ref+++%7C+rows+%7C+Extra+++++++%7C
%2B----%2B-------------%2B--------%2B-------%2B---------------%2B------%2B---------%2B-------%2B------%2B-------------%2B
%7C++1+%7C+SIMPLE++++++%7C+table1+%7C+const+%7C+url+++++++++++%7C+url++%7C+258+++++%7C+const+%7C++++1+%7C+Using+index+%7C
%7C++1+%7C+SIMPLE++++++%7C+table2+%7C+const+%7C+url+++++++++++%7C+url++%7C+258+++++%7C+const+%7C++++1+%7C+Using+index+%7C
%7C++1+%7C+SIMPLE++++++%7C+table3+%7C+const+%7C+url+++++++++++%7C+url++%7C+258+++++%7C+const+%7C++++1+%7C+Using+index+%7C
%7C++1+%7C+SIMPLE++++++%7C+table4+%7C+const+%7C+url+++++++++++%7C+url++%7C+258+++++%7C+const+%7C++++1+%7C+Using+index+%7C
%7C++1+%7C+SIMPLE++++++%7C+table5+%7C+const+%7C+url+++++++++++%7C+url++%7C+258+++++%7C+const+%7C++++1+%7C+Using+index+%7C
%2B----%2B-------------%2B--------%2B-------%2B---------------%2B------%2B---------%2B-------%2B------%2B-------------%2B|3698027|select_type是SIMPLE，这意味着没有JOIN语句来减慢速度。|3698028|type是const，这意味着表最多有一个可能的匹配项-这要归功于UNIQUE索引，它保证不会有两个URL是相同的(有关UNIQUE+INDEX的详细描述，请参阅mysql+5.0+indexes+-+Unique+vs+Non+Unique+)。type列中的const值就是您能得到的最好的值了。|3698029|possible_keys和key使用url密钥。这意味着每个表都使用了正确的索引。|3698030|ref为const，这意味着MySQL将一个常量值(不变的值)与索引进行比较。同样，这是非常快的。|3698031|rows等于1。MySQL只需要查看每个表中的一行。再一次，这是非常快的。|3698032|Extra是Using+index。MySQL不必对表执行任何额外的非索引搜索。|3698033|假设您在每个表的url列上都有一个索引，那么您的查询应该会非常快。|3698034|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/388982/mysql-5-0-indexes-unique-vs-non-unique^0|0|X|3|15|6|0|0|0|0|O|6|0|0|0|0|0|E|0|0|0|B|C|6|P|4|0|0|4|5|5|X|6|1O|C|3G|4|3N|5|29|14|0|0|0|D|E|3|J|3|0|0|3|4|5|0|0|4|0|0|5|6|B|0|8|3|0^^$0|@$1|2|3|4|5|6|7|1O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1P|8|@$D|1Q|E|1R|F|G]|$D|1S|E|1T|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|1U|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|1V|8|@]|9|@]|A|$]]|$1|O|3|P|5|J|7|1W|8|@]|9|@]|A|$K|L]]|$1|Q|3|R|5|6|7|1X|8|@$D|1Y|E|1Z|F|G]]|9|@]|A|$]]|$1|S|3|T|5|J|7|20|8|@]|9|@]|A|$K|L]]|$1|U|3|V|5|6|7|21|8|@]|9|@]|A|$]]|$1|W|3|X|5|J|7|22|8|@]|9|@]|A|$K|L]]|$1|Y|3|Z|5|6|7|23|8|@$D|24|E|25|F|G]]|9|@]|A|$]]|$1|10|3|11|5|J|7|26|8|@]|9|@]|A|$K|L]]|$1|12|3|13|5|6|7|27|8|@$D|28|E|29|F|G]|$D|2A|E|2B|F|G]|$D|2C|E|2D|F|G]]|9|@]|A|$]]|$1|14|3|15|5|6|7|2E|8|@$D|2F|E|2G|F|G]|$D|2H|E|2I|F|G]|$D|2J|E|2K|F|G]|$D|2L|E|2M|F|G]|$D|2N|E|2O|F|G]|$D|2P|E|2Q|F|G]]|9|@$D|2R|E|2S|1|2T]]|A|$]]|$1|16|3|17|5|6|7|2U|8|@$D|2V|E|2W|F|G]|$D|2X|E|2Y|F|G]|$D|2Z|E|30|F|G]]|9|@]|A|$]]|$1|18|3|19|5|6|7|31|8|@$D|32|E|33|F|G]|$D|34|E|35|F|G]]|9|@]|A|$]]|$1|1A|3|1B|5|6|7|36|8|@$D|37|E|38|F|G]]|9|@]|A|$]]|$1|1C|3|1D|5|6|7|39|8|@$D|3A|E|3B|F|G]|$D|3C|E|3D|F|G]]|9|@]|A|$]]|$1|1E|3|1F|5|6|7|3E|8|@$D|3F|E|3G|F|G]]|9|@]|A|$]]|$1|1G|3|-4|5|6|7|3H|8|@]|9|@]|A|$]]]|1H|$1I|$5|1J|1K|1L|A|$1M|1N]]]]

I'm not sure if you have resolved the problem or not, but here's some test data that I have produced. There are a number of factors that can affect the speed of your queries, so my simple test cases may not accurately reflect your tables or data. However, they serve as a useful starting point.

First, create 5 simple tables, each with the same structure. As with your tables, I have used a <code>UNIQUE</code> index on the <code>url</code> column:

<pre><code>CREATE TABLE `table1` (
 `id` int(11) NOT NULL auto_increment,
 `url` varchar(255) default NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `url` (`url`)
) ENGINE=InnoDB;

CREATE TABLE table2 LIKE table1;
CREATE TABLE table3 LIKE table1;
CREATE TABLE table4 LIKE table1;
CREATE TABLE table5 LIKE table1;
</code></pre>

The following script creates a stored procedure which is used to fill each table with 10,000 rows of data:

<pre><code>DELIMITER //
DROP PROCEDURE IF EXISTS test.autofill//
CREATE PROCEDURE test.autofill()
BEGIN
 DECLARE i INT DEFAULT 5;
 WHILE i &lt; 10000 DO
 INSERT INTO table1 (url) VALUES (CONCAT('wwww.stackoverflow.com/', i ));
 INSERT INTO table2 (url) VALUES (CONCAT('wwww.stackoverflow.com/', 10000 - i ));
 INSERT INTO table3 (url) VALUES (CONCAT('wwww.stackoverflow.com/', i + 6000 ));
 INSERT INTO table4 (url) VALUES (CONCAT('wwww.stackoverflow.com/', i + 3000 ));
 INSERT INTO table5 (url) VALUES (CONCAT('wwww.stackoverflow.com/', i + 2000 ));
 SET i = i + 1;
 END WHILE;
END;
//
DELIMITER ;

CALL test.autofill();
</code></pre>

Each table now contains 10,000 rows. Your <code>SELECT</code> statement can now be used to query the data:

<pre><code>SELECT * 
FROM table1,table2,table3,table4,table5
WHERE table1.url = table2.url
AND table1.url = table3.url
AND table1.url = table4.url
AND table1.url = table5.url
AND table1.url = 'wwww.stackoverflow.com/8000';
</code></pre>

This gives the following result almost instantly:

<pre><code>+------+-----------------------------+------+-----------------------------+------+-----------------------------+------+-----------------------------+------+-----------------------------+
| id | url | id | url | id | url | id | url | id | url |
+------+-----------------------------+------+-----------------------------+------+-----------------------------+------+-----------------------------+------+-----------------------------+
| 7996 | wwww.stackoverflow.com/8000 | 1996 | wwww.stackoverflow.com/8000 | 1996 | wwww.stackoverflow.com/8000 | 4996 | wwww.stackoverflow.com/8000 | 5996 | wwww.stackoverflow.com/8000 |
+------+-----------------------------+------+-----------------------------+------+-----------------------------+------+-----------------------------+------+-----------------------------+
</code></pre>

An <code>EXPLAIN SELECT</code> shows why the query is very fast:

<pre><code>EXPLAIN SELECT * 
FROM table1,table2,table3,table4,table5
WHERE table1.url = table2.url
AND table1.url = table3.url
AND table1.url = table4.url
AND table1.url = table5.url
AND table1.url = 'wwww.stackoverflow.com/8000';

+----+-------------+--------+-------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | table1 | const | url | url | 258 | const | 1 | Using index |
| 1 | SIMPLE | table2 | const | url | url | 258 | const | 1 | Using index |
| 1 | SIMPLE | table3 | const | url | url | 258 | const | 1 | Using index |
| 1 | SIMPLE | table4 | const | url | url | 258 | const | 1 | Using index |
| 1 | SIMPLE | table5 | const | url | url | 258 | const | 1 | Using index |
+----+-------------+--------+-------+---------------+------+---------+-------+------+-------------+
</code></pre>

<code>select_type</code> is <code>SIMPLE</code>, which means that there are no <code>JOIN</code> statements to slow things down.

<code>type</code> is <code>const</code>, which means that the table has at most one possible match - this is thanks to the <code>UNIQUE</code> index, which guarantees no two URLs will be the same (see <a href="https://stackoverflow.com/questions/388982/mysql-5-0-indexes-unique-vs-non-unique">mysql 5.0 indexes - Unique vs Non Unique</a> for a good description of <code>UNIQUE INDEX</code>). A <code>const</code> value in the <code>type</code> column is about as good as you can get.

<code>possible_keys</code> and <code>key</code> use the <code>url</code> key. That means that the correct index is being used for each table.

<code>ref</code> is <code>const</code>, which means that MySQL is comparing a constant value (one that does not change) with the index. Again, this is very fast.

<code>rows</code> equals 1. MySQL only needs to look at one row from each table. Once again, this is very fast.

<code>Extra</code> is <code>Using index</code>. MySQL does not have to do any additional non-indexed searches of the tables.

Provided you have an index on the <code>url</code> column of each table, your query should be extremely fast.

HI all 

I am using single database and near about 7 tables. do have data s filled with all tables.
say near about 10k as of now. but will grow further and may strike millions but will take time.

my question is why my query is slow fetching results. its taking near about 10 to 12 seconds for a query on non load conditions. I am worried if what happens under load conditions say thousands of queries at one time??

here is my sample query...

<pre><code>$result = $db-&gt;sql_query("SELECT * FROM table1,table2,table3,table4,table5 WHERE table1.url = table2.url AND table1.url = table3.url AND table1.url = table4.url AND table1.url = table5.url AND table1.url='".$uri."'")or die(mysql_error());

$row = $db-&gt;sql_fetchrow($result);

$daysA = $row['regtime'];
$days = (strtotime(date("Y-m-d")) - strtotime($row['regtime'])) / (60 * 60 * 24);
if($row &gt; 0 &amp;&amp; $days &lt; 2){

$row['data'];
$row['data1'];
//remaining 

}else{ //some code}
</code></pre>

Can you tell what is wrong with this query?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

大家好我使用的是一个数据库和大约7个表。确保所有表都填充了数据%s。到目前为止，大约有10k。但会进一步增长，可能会冲击数百万人，但需要时间。我的问题是为什么我的查询获取结果的速度很慢。对于非加载条件的查询，大约需要10到12秒。我担心在负载条件下会发生什么，比如说一次有数千个查询？？下面是我的示例查询...$resu...

问你能说出这个查询出了什么问题吗？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问你能说出这个查询出了什么问题吗？EN