MySQL选择查询优化和限制如何在MySQL中工作 [英] mysql select query optimization and how limit works in mysql

问题描述

我正在使用 mysql 数据库，该数据库只有一个表数据" ，其中有 17,151257 行.该表的列为字符串.我想打印其中字符串列包含特定查询字符串(存储在"entered_query"变量中)的所有行，因此我使用了以下方法:

I am using mysql database which has only one table "data" with 17,151257 rows.This table has a column string. i want to print all the rows in which string column contains a particular query string (stored in "entered_query" variable), so i used following:

SELECT DISTINCT * from data WHERE string LIKE '%".$entered_query."%' limit 10

很明显，上面的查询需要太多时间来执行.

As obvious above query is taking too much time to execute.

我已阅读到可以使用索引编制，但是在这种情况下如何使用?
我还考虑过将整个数据分为10个不同的行，然后使用 perl DBI 执行10个并行查询.

I have read that indexing can be used but how in this case ?
I have also thought of dividing whole data into 10 different rows and then executing 10 parallel queries using perl DBI.

现在我有以下问题:

Now I have following questions :

1. 如何减少执行时间?
   
2. 我听说mysql查询中的"LIKE"避免了索引编制，因此上述查询还有更好的选择吗?
   
3. 当我们在mysql查询中使用限制10时，一旦找到前10个结果，mysql便立即停止执行，或者先搜索给定查询的整个数据，然后返回前10个结果.
   

推荐答案

常规索引不能用于改进该查询. MySQL索引是B树，这意味着它们可以很快找到索引列的前缀.但是，由于您的LIKE查询开头有%，因此没有要搜索的唯一前缀.因此，必须扫描每一行以匹配该模式.

Regular indexing can't be used to improve that query. MySQL indexes are B-trees, which means they can find a prefix of the indexed column very quickly. But since your LIKE query has % at the beginning, there's no unique prefix to search for. So every row has to be scanned to match against the pattern.

但是，MySQL还支持全文搜索.这将创建该列中所有单词的索引，并且可以快速找到这些单词.有关详细信息，请参见文档.

However, MySQL also supports full-text searching. This creates an index of all the words in the column, and it can find these words quickly. See the documentation for details.

如果使用LIMIT 10，它将在找到满足条件的前10行后立即停止扫描.除非您也使用ORDER BY，否则它必须找到所有行，以便可以在选择前10个行之前对它们进行排序.

If you use LIMIT 10, it will stop scanning as soon as it finds the first 10 rows that satisfy the conditions. Unless you also use ORDER BY -- then it has to find all the rows so that it can sort them before selecting the first 10.

这篇关于MySQL选择查询优化和限制如何在MySQL中工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！