You've completed your brand new app, and everything works just like a charm. Users to use your network. Everyone is happy.
Then, suddenly, a big outbreak of users kills your MySQL server and your site is closed. What's the problem? How can you stop it?
Here are some tips for MySQL performance optimization that will help you to help your database.
Big Picture
In the early stages of development, you should know the number of users expected to your application. If you want a lot of users, you should think big, from the start, planning for replication, scalability and performance.
However, if you optimize your SQL code, architecture, and indexing strategy, you may not need a big environment. You have to always think twice. The performance and scalability of the row are not the same.
make sure to use the explain
Explain statements can be used as a way to get information about how MySQL executes the describe of a SELECT statement.
When you precede a keyword Explain SELECT statement, MySQL displays information about the query execution plan optimization. That is, MySQL's description of how it will handle the select, including the order in which the information table is added. Additional information can be provided using the explain extension.
Select the correct data type
Usually stored on disk (in addition to some databases, like the memory database, it is stored in memory). This means that, in order to get information for your database, it must read the information from disk and turn it into a result set that you can use. Disk I/O is extremely slow, especially in comparison to other forms of data storage.
When your database is growing larger, start reading for a long time. Poorly designed databases handle this problem by allocating more space than they actually need on the disk. This means that the disk used by the database to occupy space is inefficient.
Choosing the right data type can help make sure that we store the data as small as possible. To do this, we only select the type of data we need.
using persistent connections
The reason to use permanent connections is to reduce the number of connections that are quite expensive even if they are faster with MySQL with most other databases.
There is some debate on this topic, MYSQLI extensions on the network have disabled the persistent connectivity feature, so I will write more about this topic. The only disadvantage of a persistent connection is that if you have multiple concurrent connections, you can reach the Max_connections setting. It's easy to change Apache settings so I don't think that's the reason why you shouldn't use persistent connections.
Persistent connections are especially useful if you have a database server on another computer. Use them wisely because of these shortcomings.
Understanding Query Caching
The text of the SELECT statement that the query caches stored, along with the corresponding results, is sent to the client. If the same statement is received, the server retrieves the results from the query cache instead of parsing and executing the statement again. The query cache is between shared sessions, and such a result set produces a client that can send the same query sent by another client.
Query caching in the environment, may be useful, you have a table not often changed, the server received many of the same query. This is typically the case for many Web servers that produce database based content for many dynamic pages.
The query cache does not return obsolete data. When the query cache table is modified, any related entries are refreshed.
How did you find my MySQL query cache is working or not?
MySQL provides the statistics, simply type the following command at the mysql> prompt:
Copy Code code as follows:
Mysql> Show variables like ' query% ';
do not use the features of indexed columns
The index on the column can be a great performance gain, but if you use the function in that column, the exponent is never used.
Always try to rewrite the function that the query does not use indexed columns.
Copy Code code as follows:
WHERE to_days (current_date)-To_days (event_date) <= 7
May be
Copy Code code as follows:
WHERE event_date >= ' 2011/03/15 '-INTERVAL 7 days
Today's date is generated from PHP. In this way, the query cache of the sequence event_date can be stored and queried.
Understanding Zen's SQL code
SQL code is the basis for optimizing database performance. The primary SQL encoding technology, such as the SQL statement that overrides the subquery, uses the connection, eliminating the connection and similar cursors.
The performance of writing a database of huge SQL code will be huge.
using on DUPLICATE KEY UPDATE
If you specify on DUPLICATE key update, inserting a row will cause the old row to be updated with a duplicate value of the unique index or primary key.
Copy Code code as follows:
INSERT into WordCount (Word, count) VALUES (' A_word ', 1) on DUPLICATE KEY UPDATE count=count+1;
You save the Access server (then select Update), clean your code to remove all, if record_exists inserts other updates.
If you follow this hint, the database will be appreciated to you.
