[Optimize] MySQL optimization and architecture

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:
The first is to add hardware and servers from hardware.
The second is to optimize our MySQL server, increase the cache size, enable multiple ports, and separate read/write.
The third 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 we put the pressure on the MySQL server on the Web server, we need a lot of operations.ProgramFor 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. The following is a brief introduction to the shocking private server.

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. the where condition is also an important factor in queries. It is important to write every where condition as few as possible and a reasonable where condition, 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 MySQL functions are used, the index will be invalid, for example: Select * From tbl1 where left (name, 4) = "hylr ", in this case, the index is invalid. When like is used for search matching, such statement indexes are invalid: Select * From tbl1 where name like "% xxx % ", however, this index is valid: Select * From tbl1 where name like "xxx %", so it is important to write your SQL statement carefully.

2. an important feature of associated queries and subqueries is associated queries, left join and full join, especially when multiple tables are associated, because when each associated table is queried, the number of scans is an order of magnitude of the Cartesian product. The number of scans is large. If Association operations are required, 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 were me, you wouldn't have to worry about private servers.

3. Some time-consuming and resource-consuming SQL statements, such as distinct, Count, group by, and 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. The group by operation is the same. If you really need to group data, please be careful. If it is 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 operation, or perform time conversion. Many functions are used, such as substr (), Concat (), date_format (), to_days (), max (), min (), MD5 (), and so on. These operations can be done by the script program to relieve the burden on MySQL.

4. it is a very important way to reasonably establish an index to improve the speed. The index performs select operations on some frequently, and the field with unique values is quite effective, for example, the ID field of the primary key and 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 will play a key role. Improper indexes will reduce efficiency maintenance and increase the burden of maintaining private server indexes.

5. to monitor the SQL Execution efficiency, you can use the explain statement before the SELECT statement to view the execution status of the current SELECT statement, including the operations used, the probability of return, 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.
The advantage is that it can relieve the temporary server pressure, put different operations on different ports, or put different project modules on different ports for operations, shares the pressure of a single daemon.
The disadvantage is that data may be disordered and may cause many unknown private server 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. The advantage is to rationally share the update and query pressure and avoid the lock table issue. The disadvantage is that the update department is 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. The advantage is that the 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. The disadvantage is that any database server is down or data is lost, so the users of these servers will be very depressed and the data will be gone. Of course, this requires a good backup mechanism.
I. Database Design
Try to occupy less disk space for database design. 1 ). use smaller integer types as much as possible. (mediumint is more suitable than INT ). 2 ). define the field as not null as much as possible, unless this field needs to be null. (this rule is only applicable when the field is key.) 3 ). if the variable length field is not used, such as varchar, a fixed-size record format is used, such as char. (char is always faster than varchr) 4 ). the primary index of the table should be as short as possible. in this way, each record has a name Mark and is more efficient. 5 ). only the required indexes are created. Indexing is conducive to record retrieval, but is not conducive to quick record storage. If you always need to search on the combined fields of the table, create an index on these fields. The first part of the index must be the most commonly used field. If you need to use many fields, you should first copy these fields to make the index more compressed. (This table is only applicable to the MyISAM engine. For InnoDB, it does not matter much when saving records, because InnoDB is based on transactions. If you want to save records quickly, (6 ). all data must be processed before being saved to the database. 7). All fields must have default values. 8). In some cases, splitting a frequently scanned table into two tables is much faster. When scanning a dynamic table to obtain relevant records, it may use a smaller static table. (The specific performance is: MyISAM table's merge type, and MyISAM and InnoDB common partitions. For details, see the manual.) 9). Do not use foreign keys where foreign key constraints are not used.
Supplement 2: system usage
1). Close the connection to MySQL in time. 2) explain complex SQL statements. (This will determine how to optimize your select statement ). if you want to compare two joined tables, the fields to be compared must be of the same type and length. (create an index when the data volume is large) 4 ). the limit statement should be followed by order by or distinct. this avoids a full table scan operation. 5 ). to clear all records of a table, we recommend that you use truncate table tablename instead of Delete from tablename. however, there is a problem that truncate will not roll back in transaction processing. Because she wants to call the create table rule private server statement. (The truncate TABLE statement first deletes the table and then recreates it. This is a file-level statement, so it is naturally faster than n.) test example: song2 is an InnoDB table. Mysql> select count (1) From song2; + ---- + | count (1) | + ---- + | 500000 | + ---- + 1 row in SET (0.91 Sec)
Mysql> Delete from song2; query OK, 500000 rows affected (15.70 Sec) mysql> truncate table song2; query OK, 502238 rows affected (0.17 Sec)
6 ). when store procedure or user function can be used. (routine always reduces the server overhead) 7 ). insert multiple records into the miracle world private server format in an insert statement. in addition, using load data infile to import a large amount of data is much faster than the pure indert. (in MySQL, insert into tableq values (),(),... ();) (Also, when a large number of records are inserted in the MyISAM table, the keys are disabled after keys and then created. Statement: alter table Table1 disable keys; alter table Table1 enable keys; for innnodb tables, set autocommit = 0 before insertion, and set autocommit = 1 after insertion. This is more efficient .) 8). Optimize table is often used to sort fragments. 9). In addition, data of the date type should be saved as quickly as possible in the unsigned int type if compared frequently.

From: http://hi.baidu.com/phper_yang/blog/item/ff69b94893c000fb83025c6f.html