MySQL性能优化是什么，如何定位效率低下的SQL？

简而言之，性能优化就是在不影响系统能正确运行的前提下，运行速度更快，完成特定功能所需的时间更短。

我们可以通过某些有效的方法来提高 MySQL 数据库的性能，目的是让 MySQL 数据库的运行速度更快、占用的磁盘空间更小。

性能优化包括很多方面，例如优化查询速度、优化更新速度和优化 MySQL 服务器等。通过不同的优化方式达到提高 MySQL 数据库性能的目的。优化数据库是数据库管理员和开发人员的必备技能。

下面将为读者介绍优化的基本知识。

MySQL 数据库的用户和数据非常少时，很难判断数据库性能的好坏。只有当长时间运行，并且有大量用户进行频繁操作时，MySQL 数据库的性能才能体现出来。

例如，一个每天有几万用户同时在线的大型网站，它的数据库性能的优劣就很明显。这么多用户同时连接 MySQL 数据库，并且进行查询、插入和更新的操作。如果 MySQL 数据库的性能很差，很可能无法承受如此多用户的同时操作。另外，如果用户查询一条记录需要花费很长时间，那么用户很难会喜欢这个网站。

因此，为了提高 MySQL 数据库的性能，需要进行一系列的优化措施。一方面是找出系统的瓶颈，提高 MySQL 数据库整体的性能，另一方面需要合理的数据库结构设计和参数调整，来提高用户操作响应的速度，同时还要尽可能节省系统资源，以便系统可以提供更大负荷的服务。

例如，通过优化文件系统，提高磁盘 I\O 的读写速度；通过优化操作系统调度策略，提高 MySQL 在高负荷情况下的负载能力；优化表结构、索引、查询语句等使查询响应更快。

如果 MySQL 数据库中需要进行大量的查询操作，那么就需要对查询语句进行优化。对于耗费时间的查询语句进行优化，可以提高整体的查询速度。如果连接 MySQL 数据库的用户很多，那么就需要对 MySQL 服务器进行优化。否则，大量的用户同时连接 MySQL 数据库，可能会造成数据库系统崩溃。

那么我们应该如何进行系统的分析，来尽快定位效率低下的 SQL 呢？主要有以下两种方法：

1. 使用 SHOW STATUS 命令

数据库管理员可以使用 SHOW STATUS 语句查询 MySQL 数据库的性能参数，了解各种 SQL 的执行频率。语法形式如下：

SHOW STATUS LIKE 'value';

其中，value 参数是常用的几个统计参数，常用参数介绍如下：

• Connections：连接 MySQL 服务器的次数；
• Uptime：MySQL 服务器的上线时间；
• Slow_queries：慢查询的次数；
• Com_select：查询操作的次数；
• Com_insert：插入操作的次数，对于批量插入操作，只累加一次；
• Com_update：更新操作的次数；
• Com_delete：删除操作的次数。

以上参数针对于所有存储引擎的表，下面几个参数只针对 InnoDB 存储引擎。

• Innodb_rows_read：表示 SELECT 语句查询的记录数；
• Innodb_rows_inserted：表示 INSERT 语句插入的记录数；
• Innodb_rows_updated：表示 UPDATE 语句更新的记录数；
• Innodb_rows_deleted：表示 DELETE 语句删除的记录数。

比如，需要查询 MySQL 服务器的连接次数，可以执行下面的 SHOW STATUS 语句：

SHOW STATUS LIKE 'Connections';

查询其它参数的方法和以上参数的查询方法相同。

通过以上几个参数，可以很容易的了解当前数据库的应用是以插入为主还是以查询为主，以及各种类型的 SQL 语句的大致执行比例。然后根据分析结果，进行相应的性能优化。

2. 使用慢查询日志

通过慢查询日志定位那些执行效率较低的 SQL 语句，然后针对慢查询语句进行表结构优化或者查询语句优化。查询慢查询日志可阅读《MySQL慢查询日志 (opens new window)》一节。

下面我们通过实例说明如何通过慢查询日志定位执行效率底的 SQL 语句：

1）开启慢查询日志 , 配置样例：

[mysqld]
log-slow-queries

在配置文件中增加上述配置项并重启 MySQL 服务，这时 MySQL 慢查询功能生效。

2）查询 long_query_time 的值 。

mysql> SHOW VARIABLES LIKE 'long%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | long_query_time | 10 | +-----------------+-------+ 1 row in set (0.00 sec)

3）这里为了方便测试，将修改慢查询时间为 5 秒。

mysql> SET long_query_time=5; Query OK, 0 rows affected (0.02 sec)

4）依次执行下面两个查询语句。

以下查询时间低于 5 秒不会出现在慢查询日志中：

mysql> SELECT COUNT(*) FROM student; +----------+ | count(*) | +----------+ | 208 | +----------+ 1 row in set (0.00 sec)

以下查询时间大于 5 秒，应该出现在慢查询日志中：

mysql> SELECT COUNT(*) FROM t_user; +----------+ | count(*) | +----------+ | 6552961 | +----------+ 1 row in set (11.07 sec)

5）查看慢查询日志，内容如下：

# Time: 081026 19:46:34

User@Host: root[root] @ localhost []

Query_time: 11 Lock_time: 0 Rows_sent: 1 Rows_examined: 6552961

select count(*) from t_user;

从上面日志中，可以发现查询时间超过 5 秒的 SQL ，而小于 5 秒的则没有出现在此日志中。