wordpress 数据库
If you’re starting a website – be it a news site, a blog or an ecommerce site, chances are that you consider using WordPress. Once users begin to warm to the website as it gains popularity, core WordPress might not be enough to handle the incoming traffic efficiently. You will then need to scale up your website to handle the higher volume of incoming connections.
如果您要建立一个网站-无论是新闻网站，博客还是电子商务网站，都有可能考虑使用WordPress。 一旦用户开始欢迎该网站，因为它越来越受欢迎，核心WordPress可能不足以有效地处理传入的流量。 然后，您将需要扩展您的网站以处理更大数量的传入连接。
There are many ways of scaling your WordPress website, but we will focus on database tweaks in this post. WordPress uses MySQL as a database and this post assumes you are familiar to the database structure of WordPress.
扩展WordPress网站的方法有很多，但本文将重点介绍数据库调整。 WordPress使用MySQL作为数据库，并且本文章假定您熟悉WordPress的数据库结构 。
As a precaution, you should never perform database queries on your server directly. Always keep proper backups before attempting to perform any actions mentioned here.
作为预防措施，切勿直接在服务器上执行数据库查询。 在尝试执行此处提到的任何操作之前，请始终保留正确的备份。
For the purpose of backup and security, WordPress stores a lot of data in the database that may not be directly visible in to the end user. For instance, WordPress saves every revision for your published posts, pages and drafts in the database. Comments which are yet to be published are also stored in the database. Unused tags, categories, dead links and media are stored in the database too.
为了备份和安全起见，WordPress将大量数据存储在数据库中，而这些数据对于最终用户可能并不直接可见。 例如，WordPress将发布的帖子，页面和草稿的每个修订版本保存在数据库中。 尚未发布的注释也存储在数据库中。 未使用的标签，类别，无效链接和媒体也存储在数据库中。
When your website is new, all this data may not cause a significant decrease in WordPress performance, but as the size of your website grows, this unnecessary data can increase the size of your database, which in turn, makes queries slower.
当您的网站是新网站时，所有这些数据可能不会导致WordPress性能显着下降，但是随着网站大小的增长，这些不必要的数据会增加数据库的大小，从而使查询变慢。
You could turn off revisions for your posts, which generally take up the greatest amount of space in your database compared to the other junk data, but I would suggest you periodically clean up your database as revisions may come in handy sometimes. Here’s a list of 10 steps to clean up your WordPress database, which includes deleting all unnecessary data.
您可以关闭帖子的修订，与其他垃圾数据相比，这些修订通常会占用数据库中最大的空间，但是我建议您定期清理数据库，因为有时修订会派上用场。 以下是清理WordPress数据库的10个步骤的清单 ，其中包括删除所有不必要的数据。
When you add a few entries, delete some others, and add some more, the data doesn’t necessarily get stored sequentially in the same place. They may take up different sectors in the hard drive. These fragments are logical data units, without any individual meaning, but as a whole constitute the complete database. There are many types of fragmentation: horizontal, vertical and mixed or hybrid.
当您添加一些条目，删除一些其他条目并添加更多条目时，数据并不一定要顺序存储在同一位置。 他们可能会占用硬盘驱动器中的不同扇区。 这些片段是逻辑数据单元，没有任何单独的含义，但总体上构成了完整的数据库。 碎片的类型很多：水平碎片，垂直碎片，混合碎片或混合碎片。
Although the operating system and MySQL take care of the fragmentation while writing or reading data, a high degree of fragmentation can lead to a higher execution time, thus making your site slower.
尽管操作系统和MySQL在写入或读取数据时会处理碎片，但是高度碎片会导致更长的执行时间，从而使您的站点变慢。
The easiest way to defragment a MySQL database is to use phpMyAdmin, which provides a GUI for performing this, as well as many other common database administration tasks.
对MySQL数据库进行碎片整理的最简单方法是使用phpMyAdmin ，它提供了执行此操作的GUI以及许多其他常见的数据库管理任务。
If you want to defragment a single table, you can perform an empty ALTER TABLE command. What it essentially does is rebuild the table from scratch, therefore, all the data is stored at the same place. To defragment the whole database, take an SQL dump, drop the tables and restore the dumps – again, rebuilding all of the tables. Defragmenting helps in reducing indexing times.
如果要对单个表进行碎片整理，则可以执行空的ALTER TABLE命令。 它的本质是从头开始重建表，因此，所有数据都存储在同一位置。 要对整个数据库进行碎片整理，请执行SQL转储，删除表并恢复转储–再次，重建所有表。 碎片整理有助于减少索引时间。
In WordPress, a request is first served by a web server (typically Apache), then handed over to PHP, which processes the requests, extracts or manipulates some data in the database and then displays the results. When there are a lot of incoming requests on a site serving the same content on every request, it is inefficient to process the request and extract the same data from the database every single time. This is where caching comes in. There are many ways to cache data, but in this post, we will talk about database caching.
在WordPress中，请求首先由Web服务器(通常为Apache)提供服务，然后移交给PHP，由PHP处理请求，提取或处理数据库中的某些数据，然后显示结果。 当站点上有很多传入请求为每个请求提供相同内容时，每次处理请求和从数据库提取相同数据的效率都很低。 这就是缓存的用处。有很多方法可以缓存数据，但是在本文中，我们将讨论数据库缓存。
Broadly speaking, there are two ways to perform database caching. You can either cache full tables, or the results of SQL queries. There are various products that help you perform database caching, such as memcached. However, I would suggest you use a WordPress plugin to perform database caching unless you are experienced in this field.
广义上讲，有两种执行数据库缓存的方法。 您可以缓存完整表，也可以缓存SQL查询的结果。 有多种产品可以帮助您执行数据库缓存，例如memcached 。 但是，除非您对此领域有经验，否则我建议您使用WordPress插件执行数据库缓存。
A good plugin to perform database caching is W3 Total Cache, although it does many other types of caching too.
W3 Total Cache是执行数据库缓存的一个不错的插件，尽管它也执行许多其他类型的缓存。
Alternately, you can cache your custom queries too. For example, if you have a query that displays the most active users, you do not need to run it every time WordPress serves a page (since it’s supposed to change rather infrequently). You can therefore make the query run only after certain intervals of time using the Transients API. Here’s a guide to using the Transients API to cache your queries in WordPress.
或者，您也可以缓存自定义查询。 例如，如果您有一个查询来显示最活跃的用户，则不需要每次WordPress服务页面时都运行它(因为它应该很少更改)。 因此，您可以使用Transients API使查询仅在一定时间间隔后运行。 这是使用Transients API在WordPress中缓存查询的指南。
Are you still searching for a solution? Have none of the other methods helped you tackle the enormously high traffic that you receive? Don’t worry, we saved the best for last.
您还在寻找解决方案吗？ 没有其他方法可以帮助您解决收到的巨大流量吗？ 别担心，我们将最好的留到了最后。
Scaling databases is important in high availability architecture. One technique of scaling your databases is using the master-slave replication.
扩展数据库在高可用性体系结构中很重要。 扩展数据库的一种技术是使用主从复制。
Source – MySQL performance blog
资料来源– MySQL性能博客
In Master-Slave replication, you have one master and one or many slaves. Each slave has a copy of the master’s data. A web application may read and write to the master, but slaves allow only read operations. Such a setup is useful when your application is expected to handle an unusually high number of read operations. If the master fails, all your read operations would work normally, but write operations would fail until a new master is appointed.
在主从复制中，您有一个主服务器和一个或多个从服务器。 每个从站都有一个主站数据的副本。 Web应用程序可以读取和写入主机，但从机仅允许读取操作。 当期望您的应用程序处理异常大量的读取操作时，这种设置很有用。 如果主服务器失败，则所有读取操作将正常工作，但写入操作将失败，直到指定了新的主服务器为止。
The master-slave system is not efficient for applications that involve write operations as it can lead to inconsistency. For instance, two different slaves can give different read values if one of them has not been updated to the latest state of the master.
对于涉及写操作的应用程序，主从系统效率不高，因为它可能导致不一致。 例如，如果两个从站中的一个未更新为主站的最新状态，则它们可以提供不同的读取值。
You can achieve master slave replication in WordPress using the plugin HyperDB, and HAProxy as a load balancer. Here’s a tutorial detailing the steps to set up this configuration on DigitalOcean – you can apply the same techniques to any provider.
您可以使用插件HyperDB和HAProxy作为负载平衡器在WordPress中实现主从复制。 这是一个教程，详细介绍了在DigitalOcean上设置此配置的步骤 -您可以将相同的技术应用于任何提供商。
We discussed many ways that could help you prepare for the next surge of traffic on your site. Were you successful in handling the new visitors? Did this tutorial help you? Did you use a new technique not mentioned here? Feel free to let us know in the comments below!
我们讨论了许多方法，可以帮助您为网站上的下一次流量激增做准备。 您成功处理了新访客吗？ 本教程对您有帮助吗？ 您是否使用了此处未提及的新技术？ 请随时在下面的评论中告诉我们！
翻译自: https://www.sitepoint.com/database-changes-to-improve-wordpress-performance/
wordpress 数据库
相关资源：wordpress自动优化修复数据库方法