Database Optimization Measures

This article is original yanjiangbo @ 2012-03-07, please respect copyright, reprint please explain the source http://blog.csdn.com/yanjiangbo

Which of the following aspects will you consider if you want to optimize the database? The following are some of my considerations:

Database Design

1. When designing fields, try to select the appropriate field type. If char is used, do not use varchar. If Enum is used, do not use Char. If smallint is used, do not use Int, use less text fields;
2. Create an appropriate index, but not too many. For those fields that are frequently queried, you do not need to create indexes for those fields that are often not used. Avoid: too many indexes may lead to deadlocks;
3. When creating a data table, run proper data redundancy to avoid some join operations and improve query efficiency;
4. Create a fast and slow table, store frequently queried data in the fast table, and store infrequently used historical data in the slow table. Separate the data appropriately to improve query efficiency;
5. When writing a SELECT statement, use order by, group by, and Other aggregated statements as little as possible. Do not select * for each query *;
6. Use join queries instead of subqueries. Use the like keyword and wildcard characters as little as possible;
7. Select a suitable and appropriate database engine. For example, use the MyISAM engine for tables that are frequently queried but not frequently updated, and use the InnoDB Engine for tables that require relational databases (only for MySQL db );
8. When the data volume of a table reaches a certain level, perform the table sharding operation;

Database architecture

1. read/write splitting (which can be achieved through dbproxy)
2. the master and slave databases. At the same time, the master database only supports write and read from the slave database only. At the same time, the master database supports multiple slaves.
3. A memcached Cache Server is installed on the database, and some select results are cached. The cache is invalidated only when the database is updated, this greatly reduces the database query pressure.
4. try to use an SSD hard disk for the Database Host, because the I/O performance of the SSD hard disk is about three times faster than that of the ordinary hard disk.