Original article: http://blogold.chinaunix.net/u/29134/showart_264480.html
If you have any objection, you can add it later. I will update it at any time.
The following are some examples: (I hope you can add them)
1. 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). Try to define the field as not null unless this field needs to be null. (this rule is only applicable when the field is key)
3). If variable length fields are not used, such as varchar, a fixed-size record format, such as Char. (char is always faster than varchr)
4). The primary index of the table should be as short as possible. In this case, each record has a name Mark and is more efficient.
5). Create only the required indexes. 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, especially when a large number of import records are used)
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.
(For details, see the manual)
9). Do not use the foreign key whenever possible.
2. usage of the system
1). Close the connection to MySQL in time.
2) explain complex SQL statements. (In this way, you can determine how to optimize your select statement)
3). If two joined tables are to be compared, the type and length of the fields to be compared must be the same (index is created when data is huge)
4) the limit statement should be followed by order by or distinct as much as possible. This can avoid a full table scan.
5). If you want 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 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) |
+ ---------- +
| 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 the store procedure or user function can be used. (routine always reduces the overhead on the server)
7 ). insert multiple records in One 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 (),(),... ();)
(In addition, when inserting a large number of records in the MyISAM table, disable the function before creating the keys. The specific statement is as follows:
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). Data of the date type should be saved as quickly as possible in the unsigned int type if compared frequently.
3. system bottlenecks
1). Disk search.
In parallel search, data is stored separately in multiple disks to accelerate the search time.
2). disk read/write (IO)
Data can be read concurrently from multiple media.
3). CPU cycle
Data is stored in the primary memory. In this way, you need to increase the number of CPUs to process the data.
4). memory bandwidth
When the CPU needs to store more data in the CPU cache, the memory bandwidth becomes a bottleneck.
====Another article more about tuning details:
Http://www.informit.com/articles/article.aspx? P = 29406 & seqnum = 1
I will summarize the optimization again the next day.
