Some simple ideas about SQL optimization and Architecture Design in MySQL

Author: heiyeluren
Blog:Http://blog.csdn.net/heiyeshuwu
Time: 2006-10-14

It is fast enough to run a common MySQL with a small amount of data and traffic. However, when the data volume and traffic volume increase sharply, it is obvious that MySQL is slow or even down, we need to consider optimizing our MySQL.

Optimization is based on three aspects:
FirstFrom hardware, hardware, and servers
SecondIs to optimize our MySQL server, increase the cache size, open multiple ports, read/write Separation
ThirdIt is our application optimization, index creation, SQL query statement optimization, and cache creation.

I will briefly discuss the optimization of SQL query statements. If our web server has more or better performance than the database server, we can transfer the database pressure to the Web server, because if a single MySQL, or the database servers in the Master/Slave architecture have a heavy load, so you can consider putting MySQL operations on the Web server. Of course, if your web server is worse than the database server, put the pressure on the database server.

If the pressure on the MySQL server is placed on the web server, many operations need to be executed by our program. For example, all the web programs are handed over to the PHP script to process data. If a single MySQL Server queries, updates, inserts, and deletes all data on one server, there is a large volume of access, and you will obviously find the lock performance. When you update or delete a table, other operations will be rejected, which will lead to the lock table. The simplest and most direct solution to this problem is to take two MySQL servers and one is responsible for the select operation, the other one is responsible for changing (update/delete/insert) and then synchronizing the data to avoid locking the table. If there are more servers, it will be better handled, the Distributed Database Architecture and Data Hash Storage can be used, which will be briefly described below.

 

I. SQL optimization and precautions

Now we assume that we only have one MySQL server, and all the select/update/insert/delete operations are performed on this server. We have three web servers at the same time, which are accessed through DNS round robin, so how can we optimize our applications and SQL.

1. Where condition
In the query,WhereConditions are also an important factor. It is very important to write every where condition as few as possible and as reasonable as possible, when there are multiple conditions as much as possible, put the conditions that will extract as little data as possible before, this will reduce the query time of the next where condition.
Sometimes some where conditions will cause the index to be invalid. When the MySQL function is used, the index will be invalid, for example: Select * From tbl1 where left (name, 4) = 'hylr ', in this case, the index is invalid.LikeDuring search matching, the index of the statement is invalid: Select * From tbl1 where name like '% xxx %', but the index is valid as follows: select * From tbl1 where name like 'xxx % ', so it is important to write your SQL statement carefully.

2. join query and subquery
A very important feature of databases is associated queries,Left joinAndFull Association, Especially when multiple tables are joined, because each associated Table query is a Cartesian product of magnitude during scanning, and the number of scans is large. If Association operations are required, please index the where or on condition.
The associated operation may also be handed over to the application, depending on the size of the data volume. If the data volume is not very large, for example, less than 0.1 million records, it can be handed over to the program for processing (totododo puts forward a mistake, the program extracts data from both the left and right tables, performs cyclic scanning, and returns results. This process also consumes Web Server resources, it depends on your willingness to put pressure on the Web server or database server.
Subquery is a function supported in MySQL 5, such as select * From tbl1 where ID in (select ID from tbl1). This is very low in efficiency and should be avoided as much as possible, if it's me, no.

3. Some time-consuming and resource-consuming operations
Waste of operations in SQL statements, suchDistinct, Count, group,Various MySQL functions. These operations are resource-consuming. I would like to use the Count statement most. If you use count, try not to count (*). It is best to count a field, such as Count (ID ), or count (1), (totododo test efficiency is actually the same), can also play a role in statistics. If it is not necessary, try not to use the distinct operation, that is, to extract the unique value. You can submit this operation to the script program to extract the unique value, reducing the burden on MySQL. Group
The by operation is the same. If grouping is required, proceed with caution. For small batches of data, you can consider handing it over to the script program.
MySQL functions are often used. For example, some users prefer to take the truncated string and give it to MySQL for operations, or perform time conversion operations. Many function images are used.Substr (), Concat (), date_format (), to_days (), max (), min (), MD5 ()And so on. These operations can be handed over to the script program to reduce the burden on MySQL.

4. Properly create an index
An important way to improve the speed of indexing. The index is very effective for some select operations that are often performed, and the unique value field, such as the ID field of the primary key, the unique name field.
However, the index is of little significance for fields with few unique values, such as gender fields and few category fields. Because gender has a 50% probability, the index has little significance. For tables with very frequent update, delete, and insert operations, you must carefully consider index creation, because these frequent operations also have a great workload for index maintenance. In the end, the loss outweighs the loss, this requires careful consideration. The more indexes, the better. Appropriate indexes play a key role. Improper indexes reduce efficiency maintenance and increase the burden of index maintenance.

5. Monitor SQL Execution Efficiency
Before the SELECT statement, you can use the explain statement to view the execution of the current SELECT statement, including the operations used, the probability of returned results, and the usage of indexes, effectively analyzes the execution efficiency and rationality of SQL statements.
In addition, using the slow query log in MySQL itself: Slow-log can also record SQL statements that take a lot of time in the query, so as to optimize and rewrite the corresponding statements.
In addition, on the MySQL terminal, the show processlist command can effectively view the threads currently running in MySQL, including the thread status, whether to lock the table, and so on. It can view the SQL Execution status in real time, optimize some lock table operations.

 

Ii. Database Server architecture and distribution ideas

For the server architecture design, this is actually more important. A reasonable design can make the application run better. Of course, the architecture design depends on your application and the actual situation of your hardware. I will simply talk about several different database architecture design methods. The right is a personal idea and I hope it will be helpful.

1. open multiple processes and ports on a single server
For a single MySQL server, long connections cannot solve the problem of too many connections due to heavy load, it is uncommon to consider using a MySQL instance to enable multiple MySQL daemon using multiple ports to relieve pressure. Of course, the premise is that your application must support multiple ports and your CPU and memory are sufficient to run multiple daemon.
AdvantagesIt can effectively relieve the pressure on the temporary server, put different operations on different ports, or put different project modules on different ports for operations, shares the pressure of a single daemon.
DisadvantagesData may cause disorder and many unknown errors. Haha

2. server structure using Master/Slave
Mysql itself has the synchronization function, which can be fully used. To build a Master/Slave server structure, at least two MySQL servers are required. We can update master server users, including update/delete/insert, and use the slave server for query operations, the Select Operation is included, and the two machines are synchronized.
AdvantagesIt is reasonable to share the update and query pressure, and can avoid the lock table problem.
DisadvantagesIt is the update department in real time. If the network is busy, there may be latency issues, and it is very troublesome for any server to go down.

3. Distributed Hash Storage
This structure is suitable for large data volumes, large loads, and ample servers. The distributed storage structure is simple and can be multiple servers. The functions of each server are similar, but the stored data is different. For example, to build a user system, therefore, the user IDs are stored on server a, 10-20 million on server B, and 20-3-10 thousand on server C, and so on. If each user fails to access the server, you can build a group server, that is, each group of users has multiple servers. For example, you can create two MySQL servers, one master and one slave in a user group, they can also separate their update and query operations, or they can be designed for Bidirectional synchronization. At the same time, your application must support cross-database and cross-server operation capabilities.
AdvantagesThe server load is properly shared, and each server is responsible for some users. If one server goes down, normal access by users with other user IDs will not be affected. It is easy to add nodes at the same time. If you add another 0.1 million users, you can add a node server, which is easy to upgrade.
DisadvantagesIf any database server is down or data is lost, the users of these servers will be very depressed and the data will be gone. Of course, this requires a good backup mechanism.

In addition, mysql5.1 has a Chinese Manual, chapter 7 describes the knowledge of MySQL Optimization in detail, it is worth learning: http://www.mysql.org/doc/refman/5.1/zh/optimization.html