MySQL performance optimization tips to help your database
- 2020-05-15 02:20:40
- OfStack
As you complete your brand new app, one-slice work like a charm. Users come to use your network. Everyone is happy.
Then, all of a sudden, an outbreak of users kills your MySQL server and your site is down. What's the problem? How can you stop it?
Here are some tips for MySQL performance optimization that will help you, and your database.
Think big
In the early stages of development, you should know the number of users expected for your application. If you want a lot of users to say, you should think big, start with 1, plan for replication, scalability and performance.
However, if you optimize your SQL code, architecture, and indexing strategy, you may not need the environment. You must always think before you leap performance and scalability are not the same.
Be sure to use EXPLAIN
The EXPLAIN statement can be used as a proxy for how MySQL executes the SELECT statement.
When you use the previous 1 keyword EXPLAIN SELECT statement, MySQL's query execution plan is optimized to display information. That is, MySQL's instructions for how it will handle SELECT, including the order in which the information table is added. You can use the EXPLAIN extension to provide additional information.
Select the correct data type
Usually stored on disk (except for 1 database, memory database 1 like it is stored in memory). This means that in order to get the 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 when comparing other forms of data storage.
As your database grows larger, it takes longer to start reading. Poorly designed databases handle this problem by allocating more space on the disk than they actually need. This means that the disks that the database takes up space are used inefficiently.
Choosing the right data type can help ensure that we store the data and keep the database as small as possible. To do this, we select only the data types we need.
Using persistent connections
The reason for using permanent connections is that reducing the number of connections is quite expensive, even though they are faster with MySQL with most other databases.
There is some debate on this topic, the mysqli extension has disabled persistent connectivity on the web, so I'll write more about this topic. The only disadvantage of persistent connections is that if you have multiple concurrent connections, you can reach the max_connections setting. It's easy to change the Settings of Apache, 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. Because of the above disadvantages, use them wisely.
Learn about query caching
Query the text of the SELECT statement stored in the cache and send it to the client with the corresponding results. If the same statement is received, the server retrieves the result from the query cache instead of analyzing and executing the statement again. The query cache is Shared between sessions so that one client can send the same query as the other.
Querying a cached environment can be useful if you have tables that don't change often and the server receives many of the same queries. This is a typical situation where many Web servers produce many dynamic pages of database-based content.
The query cache does not return stale data. When the query cache table is modified, any associated entries are refreshed.
How do you find if my MySQL query cache is working or not?
For statistics provided by MySQL, simply type the following command in mysql > At the prompt:
Do not use the index column feature
An index on a column can be a great performance gain, but if you use a function in that column, the exponent is never used.
Always try to override queries that do not use indexed columns.
May be
Today's date is generated from PHP. This way, the index column EVENT_DATE can be stored and queried in the query cache.
Understand the zen SQL code
The SQL code is the foundation for optimizing database performance. The main SQL encoding techniques, such as the SQL statement of the rewrite subquery, use joins, eliminating joins and similar cursors.
The performance of writing a huge SQL code database would be huge.
Use ON DUPLICATE KEY UPDATE
If you specify ON DUPLICATE KEY UPDATE, inserting a row will result in an UNIQUE index or PRIMARY KEY duplicate value, updating the old row.
You save the access server (then select update), clean up your code and delete all if record_exists inserts other updates.
If you follow this tip, the database will be grateful to you.
Then, all of a sudden, an outbreak of users kills your MySQL server and your site is down. What's the problem? How can you stop it?
Here are some tips for MySQL performance optimization that will help you, and your database.
Think big
In the early stages of development, you should know the number of users expected for your application. If you want a lot of users to say, you should think big, start with 1, plan for replication, scalability and performance.
However, if you optimize your SQL code, architecture, and indexing strategy, you may not need the environment. You must always think before you leap performance and scalability are not the same.
Be sure to use EXPLAIN
The EXPLAIN statement can be used as a proxy for how MySQL executes the SELECT statement.
When you use the previous 1 keyword EXPLAIN SELECT statement, MySQL's query execution plan is optimized to display information. That is, MySQL's instructions for how it will handle SELECT, including the order in which the information table is added. You can use the EXPLAIN extension to provide additional information.
Select the correct data type
Usually stored on disk (except for 1 database, memory database 1 like it is stored in memory). This means that in order to get the 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 when comparing other forms of data storage.
As your database grows larger, it takes longer to start reading. Poorly designed databases handle this problem by allocating more space on the disk than they actually need. This means that the disks that the database takes up space are used inefficiently.
Choosing the right data type can help ensure that we store the data and keep the database as small as possible. To do this, we select only the data types we need.
Using persistent connections
The reason for using permanent connections is that reducing the number of connections is quite expensive, even though they are faster with MySQL with most other databases.
There is some debate on this topic, the mysqli extension has disabled persistent connectivity on the web, so I'll write more about this topic. The only disadvantage of persistent connections is that if you have multiple concurrent connections, you can reach the max_connections setting. It's easy to change the Settings of Apache, 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. Because of the above disadvantages, use them wisely.
Learn about query caching
Query the text of the SELECT statement stored in the cache and send it to the client with the corresponding results. If the same statement is received, the server retrieves the result from the query cache instead of analyzing and executing the statement again. The query cache is Shared between sessions so that one client can send the same query as the other.
Querying a cached environment can be useful if you have tables that don't change often and the server receives many of the same queries. This is a typical situation where many Web servers produce many dynamic pages of database-based content.
The query cache does not return stale data. When the query cache table is modified, any associated entries are refreshed.
How do you find if my MySQL query cache is working or not?
For statistics provided by MySQL, simply type the following command in mysql > At the prompt:
mysql> show variables like 'query%';
Do not use the index column feature
An index on a column can be a great performance gain, but if you use a function in that column, the exponent is never used.
Always try to override queries that do not use indexed columns.
WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(event_date) <= 7
May be
WHERE event_date >= '2011/03/15' - INTERVAL 7 DAYS
Today's date is generated from PHP. This way, the index column EVENT_DATE can be stored and queried in the query cache.
Understand the zen SQL code
The SQL code is the foundation for optimizing database performance. The main SQL encoding techniques, such as the SQL statement of the rewrite subquery, use joins, eliminating joins and similar cursors.
The performance of writing a huge SQL code database would be huge.
Use ON DUPLICATE KEY UPDATE
If you specify ON DUPLICATE KEY UPDATE, inserting a row will result in an UNIQUE index or PRIMARY KEY duplicate value, updating the old row.
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 up your code and delete all if record_exists inserts other updates.
If you follow this tip, the database will be grateful to you.