编写快速的MySQL查询

最新推荐文章于 2023-07-30 21:22:48 发布

cunjie3951

最新推荐文章于 2023-07-30 21:22:48 发布

阅读量375

点赞数

文章标签：数据库大数据 python linux mysql

编写良好的SQL与未编写的SQL之间的差异是巨大的，并且在需求较高的站点上进行生产时，它们会严重影响服务的性能和可靠性。在本指南中，我将讨论如何编写快速查询以及哪些因素导致它们运行缓慢。

为什么选择MySQL？

今天，关于大数据和新技术的讨论很多。 NoSQL和基于云的解决方案很棒，但是许多流行的Web软件（例如WordPress，phpBB，Drupal，VBulletin Forum软件等）仍可以在MySQL上运行。迁移到这些较新的解决方案可能不只是简单地优化生产中已经拥有的配置那样简单。此外，MySQL的性能非常好，尤其是Percona版本。

不要犯一个普遍的错误，即在处理查询速度慢和服务器负载高的问题时投入越来越多的计算能力，而不是真正解决根本的根本问题。如果愿意，增加CPU功率，SSD或RAM是一种优化形式，但这不是我在这里要讨论的。同样，如果没有优化的站点，随着硬件的增长，问题将成倍增加。因此，这不是一个可靠的长期解决方案。

擅长SQL始终是Web开发人员的重要工具，而修复通常只需添加索引或略微修改表的使用方式就很简单，它确实有助于了解如何很好地使用RDBMS。在这种情况下，我们专注于经常与PHP结合使用的流行开源数据库，即MySQL。

本指南适用于谁？

熟悉MySQL的Web开发人员，数据库架构师/ DBA和系统管理员。如果您不熟悉MySQL作为新手，那么本指南很可能没有多大意义，但是我将尽力使它对MySQL新手保持尽可能多的信息。

先备份

我建议尝试在您自己的MySQL数据库上提供的步骤（当然，请首先备份所有内容！）。如果您没有任何数据库可使用，则在适用的地方提供示例创建数据库模式。

使用mysqldump命令行实用工具可以轻松备份MySQL：

$ mysqldump myTable > myTable-backup.sql

您可以了解有关mysqldump的更多信息。

是什么使查询变慢？

简而言之，但不按重要性排序，以下所有因素均在查询和服务器性能中发挥重要作用：

表索引
Where子句（以及内部MySQL函数的使用，例如IF和DATE ）
与排序Order By
并发请求的频率
存储引擎类型（InnoDB，MyISAM，内存，黑洞）
不使用Percona版本
服务器配置变量（调整my.cnf / my.ini）
大结果集（> 1,000行）
非持久连接
分片/集群配置
表格设计不佳

我们将在本指南中解决所有这些方面。另外，如果您还没有使用它，请安装Percona ，它是MySQL的直接替代品，它将大大提高性能。要查看Percona与MySQL的基准，请查看此比较。

什么是索引？

MySQL使用索引来快速查找具有特定列值的行，例如在WHERE内部。没有索引，MySQL必须从第一行开始，然后通读整个表以找到相关的行。桌子越大，花费越多。

如果表中有相关列的索引，MySQL可以快速确定要在数据文件中间查找的位置，而不必查看所有数据。这比顺序读取每一行要快得多。

非持久连接？

当您的脚本语言与数据库连接时，如果您已经配置了持久连接，则它将能够重用现有的连接而不必创建新的连接。这对于生产用途是最佳的，必须启用。

PHP用户可以在PHP手册中阅读更多内容。

减少并发请求的频率

我发现解决此问题的最快，最有效的方法是利用键值对存储，例如Memcached或Redis 。

使用Memcache您可以使用以下内容简单地缓存查询内容：


   
   connect('localhost',11211);
$cacheResult = $cache->get('key-name');
if($cacheResult){
    //.. no need to query
    $result = $cacheResult;
} else {
	//.. run your query
     $mysqli = mysqli('p:localhost','username','password','table'); //prepend p: to hostname for persistancy
     $sql = 'SELECT * FROM posts 
     		LEFT JOIN userInfo using (UID) WHERE posts.post_type = 'post' || posts.post_type = 'article' 
     ORDER BY column LIMIT 50';
     $result = $mysqli->query($sql);
     $memc->set('key-name', $result->fetch_array(), MEMCACHE_COMPRESSED,86400);
}

//Pass the $cacheResult to template
$template->assign('posts', $cacheResult);

?>

现在示例LEFT JOIN查询将仅每86,400秒（24小时）运行一次，从而减轻了MySQL服务器的大量负载并减少了并发连接。

注意：在MySQLi中将p: ：附加到您的host参数以进行持久连接。

分片/聚类

当您的数据变大或对服务的需求增加时，就会陷入恐慌。可以确保您的服务保持在线状态的快速解决方法是分片式的。但我不建议这样做，因为分片固有地会使数据结构过于复杂。正如Percona博客中这篇文章非常雄辩地解释的那样， 请不要分片。

可怜的桌子设计

当您接受一些黄金规则（例如处理限制并意识到什么会有效）时，创建数据库架构并不难。例如，强烈建议不要将图像作为blob数据类型存储在数据库中。将文件名存储在varchar数据类型列中要好得多。

确保设计符合要求的用法是创建应用程序的重中之重。将特定数据分开（例如类别和职位），并确保可以轻松地将多对一或一对多关系与ID关联。利用MySQL的FOREIGN KEY工具非常适合级联表之间的数据偶发性。

建立表格时，请记住以下几点：

使用完成工作所需的最低限度；稀疏和切题。
不要指望MySQL做您的业务逻辑或编程性编程，这应该在您的脚本语言插入之前真正完成。例如，如果需要对列表进行随机化，请在PHP中而不是在MySQL的ORDER BY中对数组进行随机化。
对唯一数据集使用UNIQUE索引类型，并使用ON DUPLICATE KEY UPDATE来保持日期时间或Unix时间戳的更新，例如上次检查该行的时间。
将INT数据类型用于整数。如果不指定长度，MySQL将自行计算所需的长度。

优化基础

为了有效地进行优化，我们必须查看有关您的应用程序的三个基本数据集：

分析（慢查询日志记录，审计，查询和表设计分析）
性能要求（有多少用户，需求是什么）
技术限制（硬件速度，对MySQL的要求过高）

分析可以通过多种方式进行。首先，我们将采用最直接的方法来查看MySQL查询的内容。优化工具箱中的第一个工具是EXPLAIN 。在SELECT之前的查询中使用它会为您提供以下输出：

mysql> EXPLAIN SELECT * FROM `wp_posts` WHERE `post_type`='post';
+----+-------------+----------+------+------------------+------------------+---------+-------+------+-------------+
| id | select_type | table    | type | possible_keys    | key              | key_len | ref   | rows | Extra       |
+----+-------------+----------+------+------------------+------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | wp_posts | ref  | type_status_date | type_status_date | 82      | const |    2 | Using where |
+----+-------------+----------+------+------------------+------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

每个列出的列包含有关正在执行的查询的有用信息。您需要密切注意的列是possible_keys和Extra 。

possible_keys将显示MySQL引擎可用于查询的索引。有时您需要强制执行索引以确保以最快的方式执行查询。

Extra列将显示是否使用了条件WHERE或ORDER BY 。要注意的最重要是是否出现“ Using Filesort ”。考虑以下示例：

EXPLAIN SELECT main_text
FROM posts
WHERE user = 'myUsername' &&
status = '1' && (
    status_spam_user = 'no_spam'
    || (
        status_spam_user = 'neutral' &&
        status_spam_system = 'neutral'
    )
)
ORDER BY datum DESC
LIMIT 6430 , 10

由于有条件，这种查询可以进入磁盘，如果我们查看EXPLAIN ：

id  select_type     table       type    possible_keys                       key         key_len     ref     rows    Extra
1   SIMPLE          posts  ref     index_user,index_status    index_user  32          const   7800    Using where; Using filesort

因此，此查询可以使用两个索引，并且由于Extra中的Using filesort ，当前查询正在命中磁盘。

MySQL手册在此处定义了Using Filesort作用：

“ MySQL必须多做一遍，以找出如何按排序顺序检索行。通过根据联接类型遍历所有行并存储与WHERE子句匹配的所有行的排序键和指向该行的指针，可以完成排序。然后对键进行排序，并按排序顺序检索行。”

此额外的通行证会使您的应用程序变慢，并且必须不惜一切代价避免。要避免的另一个关键的Extra结果是Using temporary ，这意味着MySQL必须为查询创建一个临时表。显然，这是MySQL的一个丑陋用法，必须不惜一切代价避免，除非由于数据需求而无法进一步优化。在这种情况下，查询应缓存在Redis或Memcache中，而不由用户运行。

为了解决Using Filesort的问题，我们必须确保MySQL使用INDEX 。它有多个possible_keys键可供选择，但是MySQL在最终查询中只能使用一个索引。尽管索引可以是几列的组合，但是倒数并非正确，尽管您可以向MySQL优化器提供有关已创建的索引的提示。

索引提示

MySQL的优化器将使用基于查询表的统计信息来为查询范围选择最佳索引。它是基于其内置优化器的统计逻辑来执行此操作的，尽管有多种选择，但没有提示就不能总是正确的。为确保使用（或不使用）正确的键，请在查询中USE INDEX FORCE INDEX ， USE INDEX和IGNORE INDEX关键字。您可以在MySQL手册中阅读有关索引提示的更多信息。

要查看表键，请使用命令SHOW INDEX 。

您可以为优化器指定多个提示，例如：

SELECT * FROM table1 USE INDEX (col1_index,col2_index)
  WHERE col1=1 AND col2=2 AND col3=3;

运行EXPLAIN将显示最终结果中使用了哪个索引。因此，要修复前面的示例，我们将按以下方式添加USE INDEX ：

EXPLAIN SELECT main_text
FROM posts USE INDEX (index_user)
WHERE user = 'myUsername' &&
status = '1' && (
    status_spam_user = 'no_spam'
    || (
        status_spam_user = 'neutral' &&
        status_spam_system = 'neutral'
    )
)
ORDER BY datum DESC
LIMIT 6430 , 10

现在，MySQL已从表中使用了index_status ，查询已修复。

id  select_type     table       type    possible_keys                       key         key_len     ref     rows    Extra
1   SIMPLE          posts  ref     index_user,index_status    index_user  32          const   7800    Using where

与EXPLAIN是DESCRIBE关键字。使用DESCRIBE您可以查看表的信息，如下所示：

mysql> DESCRIBE City;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| Id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name       | char(35) | NO   |     |         |                |
| Country    | char(3)  | NO   | UNI |         |                |
| District   | char(20) | YES  | MUL |         |                |
| Population | int(11)  | NO   |     | 0       |                |
+------------+----------+------+-----+---------+----------------+

添加索引

您可以使用CREATE INDEX语法在MySQL中CREATE INDEX 。有一些索引风格。 FULLTEXT用于全文搜索，然后提供UNIQUE类型以确保数据保持唯一。

要将索引添加到表中，请使用以下语法作为示例：

mysql> CREATE INDEX idx_start_of_username ON `users` (username(10));

这将在表users上创建一个索引，该索引将使用username列的前10个字母，这是varchar数据类型。

在这种情况下，任何需要对用户名进行WHERE排序且匹配项在前10个字符中的查找都将与整个表的查找相同。

综合指数

索引对返回查询数据的速度有很大的影响。仅仅设置一个主键和唯一索引通常是不够的-组合键才是MySQL真正需要优化的地方，而大多数情况下，这需要使用EXPLAIN一些A / B检查。

例如，如果我们需要在WHERE条件中引用两列，则复合键将是理想的。

mysql> CREATE INDEX idx_composite ON users (username, active);

此处，此密钥是在上一个示例的username列和active列上创建的，该列是ENUM数据类型，指示用户帐户是否处于活动状态。所以，现在查询数据时WHERE用户名是有效的，账户是active = 1 ，数据集现在已经优化，以处理好。

您的MySQL有多快？

启用性能分析以仔细查看您的MySQL查询。这可以在运行时通过set profiling=1来完成，然后执行查询并查看show profiles的结果。

使用PDO，以下是一小段代码：

$db->query('set profiling=1'); 
$db->query('select headline, body, tags from posts');
$rs = $db->query('show profiles');
$db->query('set profiling=0'); // Disable profiling after the query has been run

$records = $rs->fetchAll(PDO::FETCH_ASSOC); // Get the results from profiling

$errmsg = $rs->errorInfo()[2]; //Catch any errors here

如果您不使用PDO，则可以使用mysqli进行相同的操作：

$db = new mysqli($host,$username,$password,$dbname);

$db->query('set profiling=1');
$db->query('select headline, body, tags from posts');
if ($result = $db->query("SHOW profiles", MYSQLI_USE_RESULT)) {
    while ($row = $result->fetch_row()) {
        var_dump($row);
    }
    $result->close();
}

if ($result = $db->query("show profile for query 1", MYSQLI_USE_RESULT)) {
    while ($row = $result->fetch_row()) {
        var_dump($row);
    }
    $result->close();
}

$db->query('set profiling=0');

这将返回给您分析数据，该数据将在关联数组的第二个值中包括执行时间：

array(3) { 
	[0]=>  string(1) "1" 
    [1]=>  string(10) "0.00024300" 
    [2]=>  string(17) "select headline, body, tags from posts" 
    }

该查询花费了0.00024300秒来完成。这样的速度足以不必担心。但是，当数字上升时，我们必须更深入地了解。

作为一个工作示例，了解您的应用程序。在应用程序的数据库抽象层/框架数据库驱动程序中检查DEBUG常量，然后可以通过启用概要文件案例并使用var_dump / print_r输出结果来开始审核。现在，您将可以轻松浏览和分析网站页面！

全面审核您的应用

要对查询进行全面审核，请启用日志记录。我工作过的一些开发人员担心这是一个双面问题，因为启用日志记录会稍微影响性能，因此您记录的统计信息将比实际情况略低。尽管这是事实，但许多基准测试表明差异并不大。

要在MySQL版本5.1.6中启用日志记录，您可以使用全局log_slow_queries并可以指定一个带有slow_query_log_file全局文件。可以这样在运行时提示中完成：

set global log_slow_queries = 1;
set global slow_query_log_file = /dev/slow_query.log;

您可以在服务器的/etc/my.cnf或my.ini配置文件中永久设置此设置。

log_slow_queries = 1; 
slow_query_log_file = /dev/slow_query.log;

进行此更改后，必须重新启动MySQL服务器，例如，在Linux系统上service mysql restart 。

在较新的MySQL 5.6.1， log_slow_queries已被废弃， slow_query_log来代替。启用TABLE作为输出类型可以提供更好的调试体验，并且可以在MySQL 5.6.1和更高版本中进行以下操作：

log_output = TABLE;
log_queries_not_using_indexes = 1;
long_query_time = 1

long_query_time指定慢速查询被分类为的秒数。默认值为10，最小值为0。通过指定浮点数，可以采用毫秒值；默认值为10。在这里，我将其设置为1秒。因此，任何耗时超过1秒的查询都将以TABLE输出格式进行记录。

这将记录到MySQL的mysql.slow_log和mysql.general_log表中。

要禁用日志记录，请将log_output设置为NONE 。

log_queries_not_using_indexes是一个有用的布尔值，当与慢速查询日志一起启用时，意味着仅记录期望检索所有行的查询。

此选项并不总是意味着不使用索引。例如，当查询使用全索引扫描时，由于索引不会限制行数，因此会记录下来。

登录生产？

在短期内几乎总是需要启用具有流量的生产站点登录，同时还要监视负载以确保它不会影响服务。如果您的工作量很大，并且需要紧急修复，请从SHOW PROCESSLIST的提示开始，使用SHOW PROCESSLIST或直接通过information_schema.PROCESSLIST表来解决该问题，例如， select * from information_schema.PROCESSLIST; 。

在生产环境中记录所有查询可以给您带来很多好处，这是在审核项目时进行研究的一种好习惯，但是如果将项目连续运行几天，通常不会给您提供比最多48小时更多的可用数据（平均而言，至少要捕获使用高峰时间，以便对查询有个很好的了解，并获得一些频率提示。

注意：如果您经营的网站出现高峰流量，然后经历了很少的高峰期（例如，旺季和淡季期间的体育网站），那么请注意记录的方式。不要以为该网站正在快速运行。进行审核，最重要的是设置一些图形。

日志记录和Percona的pt-query-digest

Percona捆绑了一些很棒的工具，而pt-query-digest是用于分析查询日志，进程列表或tcpdumps的命令行工具。

您可以通过以下方式使用pt-query-digest ：

分析* .log文件（例如，从您的慢查询日志记录输出）：

$ pt-query-digest slow.log

实时报告来自host1的最慢查询（非常有用！）：

$ pt-query-digest --processlist h=host1

使用tcpdump报告来自MySQL协议数据的最慢查询：

$ tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt

$ pt-query-digest --type tcpdump mysql.tcp.txt

最后，我们可以将缓慢的查询数据从一台主机保存到另一台主机，以供以后查看。在这里，我们将slow.log的查询摘要保存到host2：

$ pt-query-digest --review h=host2 --no-report slow.log

要了解如何完全使用Percona的pt-query-digest工具，请阅读手册页。

绘制MySQL和服务器性能图

此InnoDB行操作图显示了InnoDB已执行的行操作：更新，读取，删除和插入。

这确实是一个大话题，在本指南中，我将只介绍它，让您开始使用MySQL监视。总的来说，重要的是要注意，监视网站的所有服务对于真正了解您的性能和使用情况是理想的。

为此，我建议使用MySQL配置设置基于RRDTool的解决方案，例如Cacti 。从Percona的家伙那里获得仙人掌的模板。

设置完Cacti并可以开始分析您的应用程序后，请花一些时间以便可以建立图表。几天后，您将开始看到流量的昼夜节律，并看到服务器确实真正繁忙了。

如果您正在寻找自动警报和触发器，请考虑配置monit ，它是Unix系统的开源主动式监视器。使用monit，您可以为服务器创建规则，并确保在负载增加时收到警报，以便您可以在负载发生时进行捕获。

慢查询日志

记录所有耗时超过一秒的慢速查询可以告诉我们一些信息，但是知道哪些查询正在执行数百次同样重要。即使这些查询执行时间很短，高请求的开销仍然会在服务器上造成很大的损失。

这就是为什么在更新某些东西并将其投入使用时留在身边对于任何新的数据库工作和更改而言都是最关键的时间的原因。我们的团队始终有一项政策，那就是在周三进行中的项目之后，永远不要同步新功能数据库的更改。必须在一周的开始，最晚在星期二完成此操作，以便所有团队可以进行监视并提供相应的支持。

在进行新查询之前，必须使用诸如ab的负载测试工具进行基准测试。运行基准测试时，必须查看SHOW PROCESSLIST ，并启用日志记录并使用top ， free和iostat类的系统工具进行监视。这是在将任何新查询投入实时生产之前的关键步骤。但这不是100％的酸性测试，因为实时流量的行为可能与计算得出的基准大不相同。

要使用ab基准测试，请确保已安装软件包，例如：

#centos users
$ sudo yum install ab
#debian / ubuntu users
$ sudo apt-get install ab

现在，您可以开始测试您的应用程序，例如：

$ ab -k -c 350 -n 20000 my-domain.com/

-k表示keep-alive连接keep-alive ， -c 350是并发连接数，即立即访问该站点的人数/客户数。最后， -n 20000是将对my-domain.com发出的请求数。

因此，通过运行上面的命令，您将同时具有350个并发连接来访问http://my-domain.com/，直到满足20,000个请求为止，而这将使用keep alive标头完成。

该过程完成20,000个请求后，您将收到有关统计信息的反馈。这将告诉您使用上述参数时，网站在您施加的压力下的表现如何。这是一种自动了解您的查询是否已更改的好方法。

基准热与冷

请求数量和服务器负载对性能有很大影响，因此，查询时间可能会受到影响。总之，应该启用慢速查询日志以在生产中捕获此问题，并且作为开发的规则，必须确保所有查询在空闲服务器上执行的时间不到毫秒（0.0xx或更短）。

实施Memcache将对您的负载要求产生巨大影响，并将用于严重卸载处理查询所用的资源。确保您有效地使用了Memcached并使用热缓存（预加载了值）与冷缓存进行了基准测试。

为了避免使用空的缓存进入生产环境，预加载器脚本是确保读取缓存的一种好方法，并且由于停机而导致的停机请求不会一次全部传入大量请求。容量过大的故障。

修复慢查询

因此，启用日志记录后，您现在在应用程序中发现了一些缓慢的查询。让我们来修复它们！出于示例目的，我将演示您将遇到的各种常见问题以及解决这些问题的逻辑。

如果尚未找到任何慢查询，那么如果使用查询日志记录方法，则可能要检查long_query_time设置。否则，在使用概要分析（ set profiling=1 ）检查了所有查询之后，列出一个查询所花费的时间超过一毫秒的时间（0.000x秒），然后开始进行查询。

常见问题

优化MySQL查询时，我会遇到以下六个常见问题：

1. `ORDER BY`使用文件排序。

mysql> explain select * from products where products.price > 4 and products.stock > 0 order by name;
+----+-------------+----------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+----------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | products | ALL  | NULL          | NULL | NULL    | NULL | 1142 | Using where; Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+-----------------------------+

由于ORDER BY name ，因此无法避免进行文件排序。无论您使用什么索引排列，都将获得最好的结果： Using where; Using Filesort 在“ Extra列中Using where; Using Filesort 。要对此进行优化，请将结果保存在Memcache中，或在应用程序的逻辑层中进行排序。

2.在`WHERE`和`LEFT JOIN`上使用`ORDER BY`

ORDER BY对查询造成重大损失。例如，以下是通过整数ID表示products表和categories表的基本LEFT JOIN 。删除订单后，文件排序也将删除。

mysql> explain select products.* from products use index (idx_price) left join categories using (catID) where products.price > 4 and catID = 4 ORDER BY stock ASC limit 10;
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-----------------------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra                       |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | products   | ALL   | idx_price     | NULL    | NULL    | NULL  |  986 | Using where; Using filesort |
|  1 | SIMPLE      | categories | const | PRIMARY       | PRIMARY | 4       | const |    1 | Using index                 |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-----------------------------+
2 rows in set (0.00 sec)

mysql> explain select products.* from products use index (idx_price) left join categories using (catID) where products.price > 4 and catID = 4;
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | products   | ALL   | idx_price     | NULL    | NULL    | NULL  |  986 | Using where |
|  1 | SIMPLE      | categories | const | PRIMARY       | PRIMARY | 4       | const |    1 | Using index |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+
2 rows in set (0.00 sec)

在可以避免的情况下，请尽量不要使用ORDER BY 。如果绝对必须使用它，请仅按索引键订购。

3. `Order By`上的临时列

只是不要这样做。如果需要汇总结果，请在应用程序逻辑中进行；不要在MySQL内部的临时表上进行过滤或排序。这将非常耗费资源。

4.不使用`FULLTEXT`索引

到目前为止，使用LIKE查询是对数据执行全文匹配的最慢方法。实施全文搜索并从MySQL的这一出色功能中获益：

mysql> SELECT * FROM articles
    -> WHERE MATCH (title,body) AGAINST ('database');
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

5.不必要地选择大量行

忽略查询的LIMIT会大大改变大型数据集（超过一百万行）的查找时间。

6.过度联接，而不只是制作一个组合表或视图

当达到LEFT JOIN三个或四个级别时，您应该问自己：“我这样做正确吗？” 如果您有合理的理由说明为何必须执行该查询，例如，它仅在需求低或仅在使用可以缓存的较大统计视图的管理屏幕中显示，然后继续。但是，如果您需要通过大量联接频繁访问数据，则应查看如何将列组合到一个新表中可能更有益，或者创建一个视图。

结论

我们已经讨论了优化的基础知识以及我们可以用来执行工作的工具。我们必须进行概要分析，并首先使用pt-query-digest工具和EXPLAIN来了解实际情况，然后从那里进行更好的设计。

我们还研究了使用MySQL时可能遇到的几个示例案例和常见陷阱。使用索引提示，我们可以确保MySQL为作业选择正确的索引并且不会混淆，特别是在同一表上有多个查询的情况下。要继续阅读该主题，请查看Percona项目和MySQL Performance博客以获取更多信息。

翻译自: https://code.tutsplus.com/tutorials/writing-blazing-fast-mysql-queries--cms-25085

cunjie3951

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
编写快速的MySQL查询

编写良好的SQL与未编写的SQL之间的差异是巨大的，并且在需求较高的站点上进行生产时，它们会严重影响服务的性能和可靠性。在本指南中，我将讨论如何编写快速查询以及哪些因素导致它们运行缓慢。为什么选择MySQL？今天，关于大数据和新技术的讨论很多。 NoSQL和基于云的解决方案很棒，但是许多流行的Web软件（例如WordPress，phpBB，Drupal，VBulletin Forum...
复制链接

扫一扫