Java基础、中级、高级、架构面试资料
    你的位置：业余草 > SQL > MySQL 的性能优化最佳实践

    MySQL 的性能优化最佳实践

    SQL herman 3175浏览 0评论
    公告：“业余草”微信公众号提供免费CSDN下载服务(只下Java资源)，关注业余草微信公众号，添加作者微信：xttblog2，发送下载链接帮助你免费下载！
    本博客日IP超过2000，PV 3000 左右，急需赞助商。
    极客时间所有课程通过我的二维码购买后返现24元微信红包，请加博主新的微信号：xttblog2，之前的微信号好友位已满，备注：返现
    受密码保护的文章请关注“业余草”公众号，回复关键字“0”获得密码
    所有面试题(java、前端、数据库、springboot等)一网打尽，请关注文末小程序
    视频教程免费领
    腾讯云】1核2G5M轻量应用服务器50元首年，高性价比，助您轻松上云

    数据库操作是当今 Web 应用程序中的主要瓶颈。 不仅是 DBA（数据库管理员）需要为各种性能问题操心，程序员为做出准确的结构化表，优化查询性能和编写更优代码，也要费尽心思。 在本文中，我列出了一些针对程序员的 MySQL 优化技术。
    在我们开始学习之前，我补充一点：你可以在 Envato Market 上找到大量的 MySQL 脚本和实用程序。

    优化查询的查询缓存

    大部分MySQL服务器都有查询缓存功能。这是提高性能的最有效的方法之一，这是由数据库引擎私下处理的。当同一个查询被多次执行，结果会直接从缓存里提取，这样速度就很快。
    主要的问题是，这对程序员来说太简单了，不容易看到，我们很多人都容易忽略。我们实际上是可以组织查询缓存执行任务的。 

    // query cache does NOT work
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
// query cache works!
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

    查询缓存在第一行不执行的原因在于CURDTE()功能的使用。这适用于所有的非确定性功能，就像NOW()和RAND()等等。因为功能返回的结果是可变的。MySQL决定禁用查询器的查询缓存。我们所需要做的是通过添加一额外一行PHP，在查询前阻止它发生。

    EXPLAIN 你的选择查询

    使用EXPLAIN关键词可以帮助了解MySQL是怎样运行你的查询的。这有助于发现瓶颈和查询或表结构的其它问题。
    EXPLAIN的查询结果会展示哪一个索引被使用过，表示怎样扫描和储存的，等等
    选择一个SELECT查询（一个有连接的复杂查询会更好），在它的前面添加关键词EXPLAIN，这样就可以直接使用数据库了。结果会以一个漂亮的表来展示。例如，就好比我执行连接时忘了添加一栏的索引：

    MySQL EXPLAIN

    现在它只会从表2里面扫描9和16行，而非扫描7883行。经验法则是乘以所有“行”那一栏的数字，你的查询性能会跟结果数字成比例的。

    获取唯一行时使用LIMIT 1

    有时当你查表时，你已经知道你正在查找的结果只有一行。你可能正在获取唯一记录，或者你可能只是查询是否存在满足你的WHERE子句条件的记录。
    在这种情况下，将LIMIT 1添加到查询条件中可以提高性能。这样，数据库引擎将在找到刚刚第一个记录之后停止扫描记录，而不是遍历整个表或索引。 

    $r = mysql_query("SELECT * FROM user WHERE state = 'Alabama'");
if (mysql_num_rows($r) > 0) {
    // ...
}
$r = mysql_query("SELECT 1 FROM user WHERE state = 'Alabama' LIMIT 1");
if (mysql_num_rows($r) > 0) {
    // ...
}

    索引搜索字段

    索引不仅仅是为了主键或唯一键。如果你会在你的表中按照任何列搜索，你就都应该索引它们。

    MySQL 索引字段的使用

    正如你所看到的，这个规则也适用于如 “last_name LIKE ‘a%’”的部分字符串搜索。当从字符串的开头搜索时，MySQL就可以使用那一列的索引。
    你也应该明白什么样搜索可以不使用有规律的索引。例如，当搜索一个单词时（例如，”WHERE post_content LIKE ‘%apple%’”），你将不会看到普通索引的好处。你最好使用 mysql 全文搜索或者构建你自己的索引解决方案。

    索引并对连接使用同样的字段类型

    如果你的应用程序包含许多连接查询, 你需要确保连接的字段在两张表上都建立了索引。 这会影响MySQL如何内部优化连接操作。
    此外,被连接的字段,需要使用同样类型。例如, 如果你使用一个DECIMAL字段, 连接另一张表的INT字段, MySQL将无法使用至少一个索引。 即使字符编码也需要使用相同的字符类型。 

    $r = mysql_query("SELECT company_name FROM users
    LEFT JOIN companies ON (users.state = companies.state)
    WHERE users.id = $user_id");

    不要ORDER BY RAND()

    起初这是一个听起来挺酷的技巧, 让许多菜鸟程序员陷入了这个陷阱。但你可能不知道，一旦你开始在查询中使用它，你创建了非常可怕的查询瓶颈。
    如果你真的需要对结果随机排序, 这有一个更好的方法。补充一些额外代码,你将可以防止当数据成指数级增长时造成的瓶颈。关键问题是，MySQL必须在排序之前对表中的每一行执行RAND()操作（这需要处理能力），并且仅仅给出一行。 

    $r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");
$r = mysql_query("SELECT count(*) FROM user");
$d = mysql_fetch_row($r);
$rand = mt_rand(0,$d[0] - 1);
$r = mysql_query("SELECT username FROM user LIMIT $rand, 1");

    所以挑选一个小于结果数的随机数，并将其用作LIMIT子句中的偏移量。

    避免使用SELECT *

    从数据表中读取的数据越多，查询操作速度就越慢。它增加了磁盘操作所需的时间。此外，当数据库服务器与Web服务器分开时，由于必须在服务器之间传输数据，将会有更长的网络延迟。
    这是一个好习惯：当你使用SELECT语句时总是指定你需要的列。 

    $r = mysql_query("SELECT * FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";
$r = mysql_query("SELECT username FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";

    几乎总是有一个id字段

    在每个以id列为PRIMARY KEY的数据表中，优先选择AUTO_INCREMENT或者INT。 也可以优选使用UNSIGNED，因为该值不能为负的。
    即使你拥有一个具有唯一用户名字段的用户表，也不要将其作为主键。 VARCHAR字段作为主键（检索）速度较慢。通过内部ID引用所有的用户数据，你的代码中将更加结构化。
    有些后台操作是由MySQL引擎本身完成的，它在内部使用主键字段。当数据库设置越复杂（集群，分区等…），这就变得更加重要了。
    这个规则的一个可能的例外是“关联表”，用于两个表之间的多对多类型的关联。例如，“posts_tags”表中包含两列：post_id，tag_id，用于保存表名为“post”和“tags”的两个表之间的关系。这些表可以具有包含两个id字段的PRIMARY键。

    相比 VARCHAR 优先使用 ENUM

    ENUM枚举类型是非常快速和紧凑的。在内部它们像TINYINT一样存储，但它们可以包含和显示字符串值。这使他们成为某些领域的完美候选。
    如果有一个字段只包含几种不同的值，请使用ENUM而不是VARCHAR。例如，它可以是名为“status”的列，并且只包含诸如“active”，“inactive”，“pending”，“expired”等的值…
    关于如何重构你的数据表，甚至有一种方法是可以从MySQL本身得到“建议”。 当你有一个VARCHAR字段，它实际上建议你将该列类型更改为ENUM。这通过调用PROCEDURE ANALYZE()来完成。

    使用PROCEDURE ANALYSE()获取建议

    PROCEDURE ANALYSE() 将使用MySQL分析列结构和表中的实际数据，为你提供一些建议。它只有在数据表中有实际数据时才有用，因为这在分析决策时很重要。
    例如，如果你创建了一个INT类型的主键，但没有太多行，MySQL则可能建议您改用MEDIUMINT。或者如果你使用VARCHAR字段，如果表里只有很少的取值，你可能会得到一个建议是将其转换为ENUM。
    你也可以在其中一个表视图中单击phpmyadmin中的“建议表结构”链接来执行此操作。

    PROCEDURE ANALYSE()

    请记住，这些只是建议。 如果你的数据表变得越来越大，他们甚至可能不是正确的建议。至于如何修改最终是你来决定。

    业余草公众号

    最后，欢迎关注我的个人微信公众号：业余草（yyucao）！可加作者微信号：xttblog2。备注：“1”，添加博主微信拉你进微信群。备注错误不会同意好友申请。再次感谢您的关注！后续有精彩内容会第一时间发给您！原创文章投稿请发送至532009913@qq.com邮箱。商务合作也可添加作者微信进行联系！

    本文原文出处：业余草： » MySQL 的性能优化最佳实践

    相关文章推荐