PHP和MySQL:优化数据库
我有一个超过10,000,000行的数据库.现在查询它可能需要花费几秒钟的时间才能找到一些基本信息.这不是可取的，我知道最佳的最佳方法是尽可能减少行数，但是现在我没有时间这样做.
I have a database with over 10,000,000 rows. Querying it right now can take a few seconds just to find some basic information. This isn't preferable, I know that the best way to optimize is to minimize the number of rows which is possible, but right now I don't have the time to do this.
优化 MySQL 数据库的最简单方法是什么，以便在查询数据库时节省时间采取短吗?
What's the easiest way to optimize a MySQL database so that when querying it, the time taken is short?
我不在乎数据库的大小，这并不重要，因此任何增加大小的优化都可以.我对优化不是很好，现在我已经建立了索引，但是我不确定从那里可以获得更好的结果.
I don't mind about the size of the database, that doesn't really matter so any optimizations that increase the size are fine. I'm not very good with optimization, right now I have indexes set up, but I'm not sure how much better I can get from there.
我最终将适当地削减数据库，但是有一个快速的临时解决方案吗?
I'll eventually trim down the database properly, but is there a quick temporary solution?
除了已经建议的索引编制之外，如果分区表很大，您可能还希望研究它们.
Besides indexing which has already been suggested, you may want to also look into partitioning tables if they are large.
在这里很难具体说明，因为我们的信息非常有限，但是正确的索引以及分区可能会走很长一段路.正确地建立索引可能是一个漫长的主题，但是从一般意义上讲，您将希望为要查询的列建立索引.
It's tough to be specific here, because we have very limited information, but proper indexing along with partitioning can go a very long way. Indexing properly can be a long subject, but in a very general sense you'll want to index columns you query against.
例如，假设您有一个雇员表，并且您的常规列为SSN，FNAME和LNAME.除了这些列之外，我们还会说您在表中还有10列.
For example, say you have a table of employees, and you have your usual columns of SSN, FNAME, LNAME. In addition to those columns, we'll say that you have an additional 10 columns in the table as well.
现在您有以下查询:
SELECT FNAME, LNAME FROM EMPLOYEES WHERE SSN = 'blah';
忽略 SSN 的事实，它可能是此处的主键，并且可能已经具有如果使用唯一索引，则通过创建另一个包含列(SSN，FNAME，LNAME)的复合索引，您可能会看到性能上的好处.之所以这样做是有益的，是因为数据库可以通过简单地查看复合索引来满足此查询，因为它包含排序和紧凑空间中所需的所有值. (即，减少I/O).尽管仅SSN上的索引是进行全表扫描的较好访问方法，但数据库仍必须读取索引(I/O)的数据块，找到将包含指向记录的指针的值需要满足查询条件，然后将需要读取不同的数据块(读取:更多随机I/O)，以便检索fname和lname的实际值.
Ignoring the fact that the SSN could likely be the primary key here and may already have a unique index on it, you would likely see a performance benefit by creating another composite index containing the columns (SSN, FNAME, LNAME). The reason this is beneficial is because the database can satisfy this query by simply looking at the composite index because it contains all the values needed in a sorted and compact space. (that is, less I/O). Even though the index on SSN only is a better access method to doing a full table scan, the database still has to read the data blocks for the index (I/O), find the value(s) which will contain pointers to the records needed to satisfy the query, then will need to read different data blocks (read: more random I/O) in order to retrieve the actual values for fname and lname.
这显然很简化，但是以这种方式使用索引可以大大减少I/O并提高数据库性能.
This is obviously very simplified, but using indexes in this way can drastically reduce I/O and increase performance of your database.
这里的一些其他链接可能会有所帮助:
Some other links here you may find helpful:
- MySQL索引-多少足够?
- 何时应使用复合索引?
- MySQL查询优化(特别是选择索引"部分)
- MySQL indexes - how many are enough?
- When should I use a composite index?
- MySQL Query Optimization (Particularly the section on "Choosing Indexes")