mysql 加索引查询慢_如何优化MySQL：索引，慢查询，配置

缺乏、安全感 2022-12-03 09:25 210阅读 0赞

mysql 加索引 查询慢

MySQL is still the world’s most popular relational database, and yet, it’s still the most unoptimized – many people leave it at default values, not bothering to investigate further. In this article, we’ll look at some MySQL optimization tips we’ve covered previously, and combine them with novelties that came out since.

MySQL仍然是世界上最流行的关系数据库，但是它仍然是最没有优化的数据库–许多人将其保留为默认值，而无需进行进一步调查。 在本文中，我们将介绍一些之前介绍MySQL优化技巧，并将其与后来出现的新颖性相结合。

## 配置优化 **(**Configuration Optimization**)** ##

The first – and most skipped! – performance upgrade every user of MySQL should do is tweak the configuration. 5.7 (the current version) has much better defaults than its predecessors, but it’s still easy to make improvements on top of those.

第一个-多数跳过！ – MySQL的每个用户应该执行的性能升级是调整配置。 5.7(当前版本)的默认设置比以前的版本好得多，但是在这些默认设置的基础上进行改进仍然很容易。

We’ll assume you’re using a Linux-based host or a good [Vagrant][] box like our [Homestead Improved][] so your configuration file will be in `/etc/mysql/my.cnf`. It’s possible that your installation will actually load a secondary configuration file into that configuration file, so look into that – if the `my.cnf` file doesn’t have much content, the file `/etc/mysql/mysql.conf.d/mysqld.cnf` might.

我们假设您使用的是基于Linux的主机或类似[Homestead Improvement][Homestead Improved]的良好[Vagrant][]盒，因此您的配置文件将位于`/etc/mysql/my.cnf` 。 您的安装实际上可能会将辅助配置文件加载到该配置文件中，因此请进行调查-如果`my.cnf`文件内容不多，则文件`my.cnf` `/etc/mysql/mysql.conf.d/mysqld.cnf`可能。

### 编辑配置 **(**Editing Configuration**)** ###

You’ll need to be comfortable with using the command line. Even if you haven’t been exposed to it yet, now is as good a time as any.

您需要习惯使用命令行。 即使您尚未接触过它，现在也一样好。

If you’re editing locally on a Vagrant box, you can copy the file out into the main filesystem by copying it into the shared folder with `cp /etc/mysql/my.cnf /home/vagrant/Code` and editing it with a regular text editor, then copying it back into place when done. Otherwise, use a simple text editor like vim by executing `sudo vim /etc/mysql/my.cnf`.

如果在“ Vagrant”框上进行本地编辑，则可以通过使用`cp /etc/mysql/my.cnf /home/vagrant/Code`将文件复制到共享文件夹中，然后使用常规文件将其复制到主文件系统中文本编辑器，然后将其复制回原位。 否则，通过执行`sudo vim /etc/mysql/my.cnf`使用像vim这样的简单文本编辑器。

*Note: modify the above path to match the config file’s real location – it’s possible that it’s actually in `/etc/mysql/mysql.conf.d/mysqld.cnf`*

*注意：修改上面的路径以匹配配置文件的实际位置–它可能实际上在`/etc/mysql/mysql.conf.d/mysqld.cnf`*

### 手动调整 **(**Manual Tweaks**)** ###

The following manual tweaks should be made out of the box. As per [these tips][], add this to the config file under the `[mysqld]` section:

以下手动调整应直接使用。 按照[这些技巧][these tips] ，将其添加到`[mysqld]`部分下的配置文件中：

innodb_buffer_pool_size = 1G # (adjust value here, 50%-70% of total RAM)
    innodb_log_file_size = 256M
    innodb_flush_log_at_trx_commit = 1 # may change to 2 or 0
    innodb_flush_method = O_DIRECT

*  `innodb_buffer_pool_size` – the buffer pool is a storage area for caching data and indexes in memory. It’s used to keep frequently accessed data in memory, and when you’re running a dedicated or virtual server where the DB will often be the bottleneck, it makes sense to give this part of your app(s) the most RAM. Hence, we give it 50-70% of all RAM. There’s a buffer pool sizing guide available in the [MySQL docs][].
    
    `innodb_buffer_pool_size` –缓冲池是用于在内存中缓存数据和索引的存储区。 它用于将经常访问的数据保留在内存中，并且当您运行专用或虚拟服务器时，数据库通常会成为瓶颈，因此有必要为应用程序的这一部分提供最大的RAM。 因此，我们将其分配给所有RAM的50-70％。 [MySQL文档中][MySQL docs]提供了一个缓冲池大小调整指南。
 *  the log file size is well explained [here][] but in a nutshell it’s how much data to store in a log before wiping it. Note that a log in this case is not an error log or something you might be used to, but instead it indicates checkpoint time because with MySQL, writes happen in the background but still affect foreground performance. Big log files mean better performance because of fewer new and smaller checkpoints being created, but longer recovery time in case of a crash (more stuff needs to be re-written to the DB).
    
    日志文件的大小[在这里][here]得到了很好的解释[，][here]但简而言之，它是清除日志之前要在日志中存储多少数据。 请注意，这种情况下的日志不是错误日志或您可能习惯的日志，而是指示检查点时间，因为对于MySQL，写操作在后台进行，但仍会影响前台性能。 大日志文件意味着更好的性能，因为创建的新检查点和更少的检查点更少，但是在崩溃的情况下恢复时间更长(需要将更多内容重新写入数据库)。
 *  `innodb_flush_log_at_trx_commit` is explained [here][here 1] and indicates what happens with the log file. With 1 we have the safest setting, because the log is flushed to disk after every transaction. With 0 or 2 it’s less ACID, but more performant. The difference in this case isn’t big enough to outweigh the stability benefits of the setting of 1.
    
    `innodb_flush_log_at_trx_commit` [在此处][here 1]进行了说明[，][here 1]并指出了日志文件会发生什么。 设置为1时，我们拥有最安全的设置，因为在每次事务处理之后，日志都会刷新到磁盘上。 设置为0或2时，其ACID较少，但性能更高。 这种情况下的差异不足以超过设置为1的稳定性带来的好处。
 *  `innodb_flush_method` – to top things off in regards to flushing, this gets set to `O_DIRECT` to avoid double-buffering. This should always be done, unless the I/O system is very low performance. On most hosted servers like DigitalOcean droplets you’ll have SSDs, so the I/O system will be high performance.
    
    `innodb_flush_method` –在刷新方面最重要的是，将其设置为`O_DIRECT`以避免双重缓冲。 除非I / O系统的性能非常低，否则应始终这样做。 在大多数托管服务器(如DigitalOcean Drops)上，您将拥有SSD，因此I / O系统将具有高性能。

There’s another tool from Percona which can help us find the remaining problems automatically. Note that if we had run it without the above manual tweaks, only 1 out of 4 fixes would have been manually identified because the other 3 depend on user preference and the app’s environment.

Percona提供了另一个工具，可以帮助我们自动发现剩余的问题。 请注意，如果我们在没有上述手动调整的情况下运行它，则只有4个修补程序中有1个被手动标识，因为其他3个取决于用户喜好和应用程序的环境。

![Superhero speeding][]

### 变量检查器 **(**Variable Inspector**)** ###

To install the variable inspector on Ubuntu:

要在Ubuntu上安装变量检查器：

wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
    sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
    sudo apt-get update
    sudo apt-get install percona-toolkit

For other systems, follow [instructions][].

对于其他系统，请遵循[说明][instructions] 。

Then, run the toolkit with:

然后，使用以下命令运行该工具箱：

pt-variable-advisor h=localhost,u=homestead,p=secret

You should see output not unlike this one:

您应该看到的输出与此不同：

# WARN delay_key_write: MyISAM index blocks are never flushed until necessary.
    
    # NOTE max_binlog_size: The max_binlog_size is smaller than the default of 1GB.
    
    # NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.
    
    # NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later.
    
    # WARN log_bin: Binary logging is disabled, so point-in-time recovery and replication are not possible.

None of these are critical, they don’t need to be fixed. The only one we could add would be binary logging for replication and snapshot purposes.

这些都不是至关重要的，它们不需要修复。 我们唯一可以添加的就是二进制日志记录，以用于复制和快照。

*Note: the binlog size will default to 1G in newer versions and won’t be noted by PT.*

*注意：在新版本中，binlog大小默认为1G，PT不会注明。*

max_binlog_size = 1G
    log_bin = /var/log/mysql/mysql-bin.log
    server-id=master-01
    binlog-format = 'ROW'

*  the `max_binlog_size` setting determined how large binary logs will be. These are logs that log your transactions and queries and make checkpoints. If a transaction is bigger than max, then a log might be bigger than max when saved to disk – otherwise, MySQL will keep them at that limit.
    
    `max_binlog_size`设置确定二进制日志的大小。 这些是记录您的事务和查询并进行检查点的日志。 如果一个事务大于最大值，那么当保存到磁盘时，日志可能会大于最大值–否则，MySQL会将它们保持在该限制。
 *  the `log_bin` option enables binary logging altogether. Without it, there’s no snapshotting or replication. Note that this can be very strenuous on the disk space. Server ID is a necessary option when activating binary logging, so the logs know which server they came from (for replication) and the format is just the way in which the logs are written.
    
    `log_bin`选项完全启用二进制日志记录。 没有它，就没有快照或复制。 请注意，这在磁盘空间上可能非常费力。 服务器ID是激活二进制日志记录时的必要选项，因此日志知道它们来自哪个服务器(用于复制)，并且格式仅是写入日志的方式。

As you can see, the new MySQL has sane defaults that make things nearly production ready. Of course, every app is different and has additional custom tweaks applicable.

如您所见，新MySQL具有健全的默认设置，使事情几乎已经准备就绪。 当然，每个应用程序都是不同的，并且具有适用的其他自定义调整项。

### MySQL调谐器 **(**MySQL Tuner**)** ###

The [Tuner][] will monitor a database in longer intervals (run it once per week or so on a live app) and suggest changes based on what it’s seen in the logs.

[Tuner][]将以更长的时间间隔监视数据库(每周在实时应用程序上运行一次)，并根据日志中看到的内容提出更改建议。

Install it by simply downloading it:

只需下载即可安装：

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
    chmod +x mysqltuner.pl

Running it with `./mysqltuner.pl` will ask you for admin username and password for the database, and output information from the quick scan. For example, here’s my InnoDB section:

使用`./mysqltuner.pl`运行它会要求您提供数据库的管理员用户名和密码，并从快速扫描中输出信息。 例如，这是我的InnoDB部分：

[--] InnoDB is enabled.
    [--] InnoDB Thread Concurrency: 0
    [OK] InnoDB File per table is activated
    [OK] InnoDB buffer pool / data size: 1.0G/11.2M
    [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (50 %): 256.0M * 2/1.0G should be equal 25%
    [!!] InnoDB buffer pool <= 1G and Innodb_buffer_pool_instances(!=1).
    [--] Number of InnoDB Buffer Pool Chunk : 8 for 8 Buffer Pool Instance(s)
    [OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
    [OK] InnoDB Read buffer efficiency: 96.65% (19146 hits/ 19809 total)
    [!!] InnoDB Write Log efficiency: 83.88% (640 hits/ 763 total)
    [OK] InnoDB log waits: 0.00% (0 waits / 123 writes)

Again, it’s important to note that this tool should be run once per week or so as the server has been running. Once a config value is changed and the server restarted, it should be run a week from that point then. It’s a good idea to set up a cronjob to do this for you and send you the results periodically.

同样，重要的是要注意，该工具应该每周运行一次，以便服务器一直在运行。 一旦更改了配置值并重新启动了服务器，则应从该点开始运行一周。 最好设置一个cronjob为您执行此任务，并定期向您发送结果。

--------------------

Make sure you restart the mysql server after every configuration change:

确保在每次配置更改后重新启动mysql服务器：

sudo service mysql restart

## 指标 **(**Indexes**)** ##

Next up, let’s focus on Indexes – the main pain point of many hobbyist DB admins! Especially those who immediately jump into ORMs and are thus never truly exposed to raw SQL.

接下来，让我们集中讨论索引-许多业余数据库管理员的主要痛点！ 尤其是那些立即进入ORM并因此从未真正接触过原始SQL的人。

*Note: the terms keys and indexes can be used interchangeably.*

*注意：术语键和索引可以互换使用。*

You can compare MySQL indexes with the index in a book which lets you easily find the correct page that contains the subject you’re looking for. If there weren’t any indexes, you’d have to go through the whole book searching for pages that contain the subject.

您可以将MySQL索引与书中的索引进行比较，从而轻松地找到包含所需主题的正确页面。 如果没有任何索引，则必须遍历整本书，搜索包含该主题的页面。

As you can imagine, it’s way faster to search by an index than having to go through each page. Therefore, adding indexes to your database is in general speeding up your select queries. However, the index also has to be created and stored. So the update and insert queries will be slower and it will cost you a bit more disk space. In general, you won’t notice the difference with updating and inserting if you have indexed your table correctly and therefore it’s advisable to add indexes at the right locations.

可以想象，通过索引进行搜索比浏览每个页面要快得多。 因此，将索引添加到数据库通常可以加快选择查询的速度。 但是，还必须创建和存储索引。 因此，更新和插入查询的速度将变慢，并且将花费您更多的磁盘空间。 通常，如果您已正确索引表，则不会注意到更新和插入的区别，因此建议在正确的位置添加索引。

Tables which only contain a few rows don’t really benefit from indexing. You can imagine that searching through 5 pages is not much slower then first going to the index, getting the page number and then opening that particular page.

仅包含几行的表并不能真正从索引中受益。 您可以想象，搜索5个页面并没有比首先转到索引，获取页码然后打开该特定页面慢很多。

So how do we find out which indexes to add, and which types of indexes exist?

那么，我们如何找出要添加的索引以及存在哪些类型的索引呢？

### 唯一/主要索引 **(**Unique / Primary Indexes**)** ###

Primary indexes are the main indexes of data which are the default way of addressing them. For a user account, that might be a user ID, or a username, even a main email. Primary indexes are unique. Unique indexes are indexes that cannot be repeated in a set of data.

主索引是数据的主要索引，这是寻址它们的默认方式。 对于用户帐户，可能是用户ID或用户名，甚至是主电子邮件。 主索引是唯一的。 唯一索引是无法在一组数据中重复的索引。

For example, if a user selected a specific username, no one else should be able to take it. Adding a “unique” index to the `username` column solves this problem. MySQL will complain if someone else tries to insert a row which has a username that already exists.

例如，如果用户选择了一个特定的用户名，则没有其他人可以使用它。 向`username`名列添加“唯一”索引可以解决此问题。 如果其他人试图插入具有已经存在的用户名的行，MySQL将会抱怨。

...
    ALTER TABLE `users` 
    ADD UNIQUE INDEX `username` (`username`);
    ...

Primary keys/indexes are usually defined on table creation, and unique indexes are defined after the fact by altering the table.

主键/索引通常是在创建表时定义的，唯一索引是在事实发生后通过更改表来定义的。

Both primary keys and unique keys can be made on a single column or multiple columns at once. For example, if you want to make sure only one username per country can be defined, you make a unique index on both of those columns, like so:

主键和唯一键都可以同时在单列或多列上进行。 例如，如果要确保每个国家/地区只能定义一个用户名，则可以在这两个列上创建唯一索引，如下所示：

...
    ALTER TABLE `users`
    ADD UNIQUE INDEX `usercountry` (`username`, `country`),
    ...

Unique indexes are put onto columns which you’ll address often. So if the user account is frequently requested and you have many user accounts in the database, that’s a good use case.

唯一索引放在您经常处理的列上。 因此，如果经常请求用户帐户，并且数据库中有许多用户帐户，则这是一个很好的用例。

### 常规索引 **(**Regular Indexes**)** ###

Regular indexes ease lookup. They’re very useful when you need to find data by a specific column or combination of columns fast, but that data doesn’t need to be unique.

常规索引简化了查找。 当您需要快速按特定列或按列组合查找数据时，它们非常有用，但是该数据不必唯一。

...
    ALTER TABLE `users`
    ADD INDEX `usercountry` (`username`, `country`),
    ...

The above would make it faster to search for usernames per country.

通过上述操作，可以更快地搜索每个国家/地区的用户名。

Indexes also help with sorting and grouping speed.

索引还有助于提高排序和分组速度。

### 全文索引 **(**Fulltext Indexes**)** ###

FULLTEXT indexes are used for full-text searches. Only the InnoDB and MyISAM storage engines support FULLTEXT indexes and only for CHAR, VARCHAR, and TEXT columns.

FULLTEXT索引用于全文搜索。 仅InnoDB和MyISAM存储引擎支持FULLTEXT索引，并且仅支持CHAR，VARCHAR和TEXT列。

These indexes are very useful for all the text searching you might need to do. Finding words inside of bodies of text is FULLTEXT’s specialty. Use these on posts, comments, descriptions, reviews, etc. if you often allow searching for them in your application.

这些索引对于您可能需要进行的所有文本搜索非常有用。 在文本正文中查找单词是FULLTEXT的专长。 如果您经常允许在应用程序中搜索它们，请在帖子，评论，描述，评论等上使用它们。

### 降序索引 **(**Descending Indexes**)** ###

Not a special type, but an alteration. From version 8+, MySQL supports [Descending indexes][], which means it can store indexes in descending order. This can come in handy when you have enormous tables that frequently need the last added data first, or prioritize entries that way. Sorting in descending order was always possible, but came at a small performance penalty. This further speeds things up.

不是特殊类型，而是一种更改。 从版本8+开始，MySQL支持[降序索引][Descending indexes] ，这意味着它可以按降序存储索引。 当您有大量的表经常需要首先添加最后一个数据或以这种方式对条目进行优先级排序时，这可能会派上用场。 降序排序始终是可能的，但是会降低性能。 这进一步加快了速度。

CREATE TABLE t (
      c1 INT, c2 INT,
      INDEX idx1 (c1 ASC, c2 ASC),
      INDEX idx2 (c1 ASC, c2 DESC),
      INDEX idx3 (c1 DESC, c2 ASC),
      INDEX idx4 (c1 DESC, c2 DESC)
    );

Consider applying DESC to an index when dealing with logs written in the database, posts and comments which are loaded last to first, and similar.

处理数据库中写入的日志，从最后到第一个加载的帖子和注释时，应考虑将DESC应用于索引。

### 辅助工具：讲解 **(**Helper Tools: Explain**)** ###

When looking at optimizing queries, the EXPLAIN tool will be priceless. Prefixing a simple query with `EXPLAIN` will process it in a very in-depth manner, analyze indexes in use, and show you the ratio of hits and misses. You’ll notice how many rows it had to process to get the results you’re looking for.

当考虑优化查询时，EXPLAIN工具将是无价的。 使用`EXPLAIN`前缀一个简单查询将以非常深入的方式处理它，分析使用中的索引，并向您显示命中率和未命中率。 您会注意到，要获得所需的结果，必须处理多少行。

EXPLAIN SELECT City.Name FROM City
    JOIN Country ON (City.CountryCode = Country.Code)
    WHERE City.CountryCode = 'IND' AND Country.Continent = 'Asia'

You can further extend this with `EXTENDED`:

您可以使用`EXTENDED`进一步扩展它：

EXPLAIN SELECT City.Name FROM City
    JOIN Country ON (City.CountryCode = Country.Code)
    WHERE City.CountryCode = 'IND' AND Country.Continent = 'Asia'

See how to use this and apply the discoveries by reading [this excellent, detailed post][this excellent_ detailed post].

阅读[这篇出色而详尽的文章，了解][this excellent_ detailed post]如何使用它并应用发现。

### 辅助工具：Percona用于重复索引 **(**Helper Tools: Percona for Duplicate Indexes**)** ###

The previously installed Percona Toolkit also has a tool for detecting duplicate indexes, which can come in handy when using third party CMSes or just checking if you accidentally added more indexes than needed. For example, the default WordPress installation has duplicate indexes in the `wp_posts` table:

先前安装的Percona Toolkit还具有用于检测重复索引的工具，该工具在使用第三方CMS或仅检查是否意外添加了超出所需数量的索引时会派上用场。 例如，默认的WordPress安装在`wp_posts`表中具有重复的索引：

pt-duplicate-key-checker h=localhost,u=homestead,p=secret
    
    # ########################################################################
    # homestead.wp_posts
    # ########################################################################
    
    # Key type_status_date ends with a prefix of the clustered index
    # Key definitions:
    #   KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
    #   PRIMARY KEY (`ID`),
    # Column types:
    #      `post_type` varchar(20) collate utf8mb4_unicode_520_ci not null default 'post'
    #      `post_status` varchar(20) collate utf8mb4_unicode_520_ci not null default 'publish'
    #      `post_date` datetime not null default '0000-00-00 00:00:00'
    #      `id` bigint(20) unsigned not null auto_increment
    # To shorten this duplicate clustered index, execute:
    ALTER TABLE `homestead`.`wp_posts` DROP INDEX `type_status_date`, ADD INDEX `type_status_date` (`post_type`,`post_status`,`post_date`);

As you can see by the last line, it also gives you advice on how to get rid of the duplicate indexes.

正如您在最后一行所看到的，它还为您提供有关如何摆脱重复索引的建议。

### 辅助工具：未使用索引的Percona **(**Helper Tools: Percona for Unused Indexes**)** ###

Percona can also detect unused indexes. If you’re logging slow queries (see the Bottlenecks section below), you can run the tool and it’ll inspect if these logged queries are using the indexes in the tables involved with the queries.

Percona还可以检测未使用的索引。 如果您正在记录慢查询(请参见下面的瓶颈部分)，则可以运行该工具，它将检查这些记录的查询是否正在使用查询所涉及的表中的索引。

pt-index-usage /var/log/mysql/mysql-slow.log

For detailed usage of this tools, see [here][here 2].

有关此工具的详细用法，请参见[此处][here 2] 。

## 瓶颈 **(**Bottlenecks**)** ##

This section will explain how to detect and monitor for bottlenecks in a database.

本节将说明如何检测和监视数据库中的瓶颈。

slow_query_log  = /var/log/mysql/mysql-slow.log
    long_query_time = 1
    log-queries-not-using-indexes = 1

The above should be added to the configuration. It’ll monitor queries that are longer than 1 second, and those not using indexes.

以上应添加到配置中。 它将监视超过1秒的查询以及未使用索引的查询。

Once this log has some data, you can analyze it for index usage with the aforementioned `pt-index-usage` tool, or the `pt-query-digest` tool which produces results like these:

一旦此日志包含一些数据，您就可以使用上述`pt-index-usage`工具或`pt-query-digest`工具生成索引，分析其索引使用情况：

pt-query-digest /var/log/mysql/mysql-slow.log
    
    # 360ms user time, 20ms system time, 24.66M rss, 92.02M vsz
    # Current date: Thu Feb 13 22:39:29 2014
    # Hostname: *
    # Files: mysql-slow.log
    # Overall: 8 total, 6 unique, 1.14 QPS, 0.00x concurrency ________________
    # Time range: 2014-02-13 22:23:52 to 22:23:59
    # Attribute          total     min     max     avg     95%  stddev  median
    # ============     ======= ======= ======= ======= ======= ======= =======
    # Exec time            3ms   267us   406us   343us   403us    39us   348us
    # Lock time          827us    88us   125us   103us   119us    12us    98us
    # Rows sent             36       1      15    4.50   14.52    4.18    3.89
    # Rows examine          87       4      30   10.88   28.75    7.37    7.70
    # Query size         2.15k     153     296  245.11  284.79   48.90  258.32
    # ==== ================== ============= ===== ====== ===== ===============
    # Profile
    # Rank Query ID           Response time Calls R/Call V/M   Item
    # ==== ================== ============= ===== ====== ===== ===============
    #    1 0x728E539F7617C14D  0.0011 41.0%     3 0.0004  0.00 SELECT blog_article
    #    2 0x1290EEE0B201F3FF  0.0003 12.8%     1 0.0003  0.00 SELECT portfolio_item
    #    3 0x31DE4535BDBFA465  0.0003 12.6%     1 0.0003  0.00 SELECT portfolio_item
    #    4 0xF14E15D0F47A5742  0.0003 12.1%     1 0.0003  0.00 SELECT portfolio_category
    #    5 0x8F848005A09C9588  0.0003 11.8%     1 0.0003  0.00 SELECT blog_category
    #    6 0x55F49C753CA2ED64  0.0003  9.7%     1 0.0003  0.00 SELECT blog_article
    # ==== ================== ============= ===== ====== ===== ===============
    # Query 1: 0 QPS, 0x concurrency, ID 0x728E539F7617C14D at byte 736 ______
    # Scores: V/M = 0.00
    # Time range: all events occurred at 2014-02-13 22:23:52
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count         37       3
    # Exec time     40     1ms   352us   406us   375us   403us    22us   366us
    # Lock time     42   351us   103us   125us   117us   119us     9us   119us
    # Rows sent     25       9       1       4       3    3.89    1.37    3.89
    # Rows examine  24      21       5       8       7    7.70    1.29    7.70
    # Query size    47   1.02k     261     262  261.25  258.32       0  258.32
    # String:
    # Hosts        localhost
    # Users        *
    # Query_time distribution
    #   1us
    #  10us
    # 100us  ################################################################
    #   1ms
    #  10ms
    # 100ms
    #    1s
    #  10s+
    # Tables
    #    SHOW TABLE STATUS LIKE 'blog_article'\G
    #    SHOW CREATE TABLE `blog_article`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    SELECT b0_.id AS id0, b0_.slug AS slug1, b0_.title AS title2, b0_.excerpt AS excerpt3, b0_.external_link AS external_link4, b0_.description AS description5, b0_.created AS created6, b0_.updated AS updated7 FROM blog_article b0_ ORDER BY b0_.created DESC LIMIT 10

If you’d prefer to analyze these logs by hand, you can do so too – but first you need to export the log into a more “analyzable” format. This can be done with:

如果您希望手工分析这些日志，也可以这样做-但首先需要将日志导出为更“可分析”的格式。 这可以通过以下方式完成：

mysqldumpslow /var/log/mysql/mysql-slow.log

Additional parameters can further filter data and make sure only important things are exported. For example: the top 10 queries sorted by average execution time.

其他参数可以进一步过滤数据，并确保仅导出重要的内容。 例如：前10个查询按平均执行时间排序。

mysqldumpslow -t 10 -s at /var/log/mysql/localhost-slow.log

For other parameters, see the [docs][].

有关其他参数，请参阅[docs][] 。

## 结论 **(**Conclusion**)** ##

In this comprehensive MySQL optimization post we looked at various techniques for making MySQL fly.

在这篇全面MySQL优化文章中，我们研究了使MySQL运转的各种技术。

We dealt with configuration optimization, we powered through some indexes, and we got rid of some bottlenecks. This was all mostly theoretical, however – for a real world use case of applying these techniques on a real app, stay tuned for our performance month project – coming soon!

我们进行配置优化，通过一些索引，消除了一些瓶颈。 但是，所有这些基本上都是理论上的-对于即将在实际应用中应用这些技术的实际用例，请继续关注我们的性能月项目-即将推出！

Have we missed any techniques and tips? Let us know!

我们错过任何技巧和提示了吗？ 让我们知道！

> 翻译自: [https://www.sitepoint.com/optimize-mysql-indexes-slow-queries-configuration/][https_www.sitepoint.com_optimize-mysql-indexes-slow-queries-configuration]

mysql 加索引 查询慢

[Vagrant]: https://www.sitepoint.com/re-introducing-vagrant-right-way-start-php/
[Homestead Improved]: https://www.sitepoint.com/quick-tip-get-homestead-vagrant-vm-running/
[these tips]: https://www.percona.com/blog/2016/10/12/mysql-5-7-performance-tuning-immediately-after-installation/
[MySQL docs]: https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html
[here]: https://www.percona.com/blog/2016/05/31/what-is-a-big-innodb_log_file_size/
[here 1]: https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit
[Superhero speeding]: /images/20221123/2765583c15374e9eaae9c1ad01365faf.png
[instructions]: https://www.percona.com/downloads/percona-toolkit/LATEST/
[Tuner]: https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
[Descending indexes]: https://dev.mysql.com/doc/refman/8.0/en/descending-indexes.html
[this excellent_ detailed post]: https://www.sitepoint.com/using-explain-to-write-better-mysql-queries/
[here 2]: https://www.percona.com/doc/percona-toolkit/LATEST/pt-index-usage.html
[docs]: https://dev.mysql.com/doc/refman/5.7/en/mysqldumpslow.html
[https_www.sitepoint.com_optimize-mysql-indexes-slow-queries-configuration]: https://www.sitepoint.com/optimize-mysql-indexes-slow-queries-configuration/