MySQL Database optimization

1. Table Design
Following the three paradigms, but when necessary to do data redundancy, for example: In the permissions model may use 5 table User table, Role table, permission table, as well as User Role association table and Role Permission Association table. If you want to query permissions through the user at this time must be associated with the query or use multiple SQL queries, you can add a field in the user table to store the user's permissions (for example, the permission values are separated by commas), so that you can query a user's permissions can be directly in the User table query, query and then use the program to process.

Design the table to choose the appropriate storage engine, for the table frequently queried, and do not need transactions and other applications, priority to choose MyISAM (such as news table), if the need for transaction processing for high stability requirements of the table preferred InnoDB storage engine (such as order form, order Commodity table), Currently InnoDB has become the default storage engine for the new MySQL, and it is recommended to use this engine when the stability of high concurrent writes is higher than MyISAM and faster at read speed. Memory storage engine (e.g. session, shopping cart) is optional for data that does not need to be persisted

Choose the appropriate field type for the different fields, for example, the IP address should be stored as an integer, see storing the IP in the database as an integral type. Fixed-length strings to use char, such as MD5 encrypted passwords.

2. Sub-table Technology
Horizontal sub-table (the field type of each table and the number is exactly the same), such as user table, can be divided by the user's first letter, the transaction can be divided by year or month, the field of each table is consistent, as to what rules to distinguish can see the specific business.
Vertical Sub-table (The fields of each table are inconsistent, but the number of bars is consistent), such as a mall site user table using the MyISAM engine, can deal with the user login when the query operation, but the transfer between users need transactions to ensure security, so you can separate the User Balance field to form a new table InnoDB engine, and user tables, both to meet the efficient query, but also to meet the stability.
Also can be partitioned, or sub-library, are similar ideas.
3. Index optimization
Common indexes have primary key index, unique index, normal index, full-text index (MyISAM storage engine support only, and Chinese is not supported, if need to support Chinese need to install plug-in). You usually need to establish the corresponding index on the criteria field, the Sort field, the grouping field, and the associated field. However, the high repetition rate of the field should not be indexed, such as the Status field (whether deleted, shelves, etc.), Emum type (gender, etc.), and the index will be added to the speed of adding and removing changes, so frequently updated fields are not suitable for indexing, such as the number of clicks on the article.
4.SQL Optimization
You can use explain to test SQL statements at development time
The test results will have the following parameters, briefly explaining
Select_type
The way the query is simple represents the select type, there is no connection or subquery, primary represents the main query (note: Not a primary key, such as a subquery when the outer query, union query when the first select), DEPENDENT subquery represents a subquery statement, Union represents a statement other than primary (the first statement) when the union query
Table name
Type scan types (important) if all represents a full table scan, inefficient; If const means that there is a maximum of one row matching the result, high efficiency; The system represents a single piece of data in the table and is certainly efficient; Eq_ref indicates that the primary key or unique index is used; Range indicates the query for a range (the data of the scope); Index indicates that the query performance is generally
Possible_keys indexes that may be used (important)
Key actually used index (important)
Key_len Index Length
Ref
Rows MySQL considers the number of rows that it must check when it executes the query (important) as small as possible.
Extra additional information using filesort means that order by is used in the query and cannot be sorted by index, and if it does not need to be ordered, an order by null can be added at the end of the SQL statement. Using temporary some operations use temporary tables, not. The using where uses the index.

Here are a few items to keep in mind when writing code and SQL statements:

1) If there is or in the query condition, all fields used must have an index in order to be valid;

2) If the index in the like query fails with the% start;

3) The combined (Multi-column) index will only take effect if the left column index is used;

4) Where there are multiple query conditions should take precedence over the ability to filter the large amount of data conditions (order is important);

5) Do not use sub-queries (inefficient, unless the amount of data is very small), as little as possible with the associated query, rather than multiple queries, conditionally can be encapsulated into a stored procedure.

6) Do not use SQL statements in loops, if queries are queried as soon as possible and then evaluated by the program. If insert can be inserted using an extension, such as insert INTO tablename values (11,22), (33,33), (45,42).
To detect the index usage of MySQL, you can use the following command
Show global status where Variable_name like ' handler_read% ';
Explain the main parameters
Handler_read_first the number of full index scans, used to index, but all indexes;
The larger the Handler_read_key the better, the use of the index case;
Handler_read_rnd_next indicates no hit, the higher the value, the lower the index utilization;

For already on-line projects you can turn on MySQL's slow query to locate inefficient SQL, see slow query logs using MySQL to find inefficient SQL statements and optimize with explain analysis
Use the following command
Show global status like ' com% ';
Show global status like ' innodb_rows% ';
Show variables like ' long_query_time ';
Show variables like '%slow% ';

View MySQL various SQL execution frequency and slow query status

Using the Optimize table name; the command to tune the table, which consumes a lot of resources when executed, so it is recommended to perform at a time when the user has a low amount of access.
5. Configuration optimization

Modify the MySQL configuration file, usually under Windows for My.ini, under Linux for MY.CNF

Query_cache_size = 64M

This value can be changed by using the show status like ' qcache% '; command to view the MySQL status to make the appropriate changes, if the Qcache_lowmem_prunes value is very large, it indicates that buffering is often not enough to increase the cache value If the value of qcache_hits is very large, it indicates that the query buffer is used very frequently, is an ideal state, and if the value is small, it can affect efficiency, then consider not querying the buffer.
For MyISAM settings
Key_buffer_size = 32M
The MyISAM table uses the operating system's cache to cache the data, depending on system memory, index size, amount of data, and load.
For InnoDB settings
Innodb_buffer_pool_size = 2.4G
You can set about 70% of the memory, of course, consider the overall memory usage.

6. Hardware Architecture Design Optimization

Replacing a hardware hard drive with an SSD can improve efficiency several times,
Delayed Write (update), for data that is less real-time, can write data to a file, and write (update) to the database in batches at intervals
Build MySQL master-slave synchronization architecture, using read-Write separation technology
Reduce the pressure on MySQL server by using Sphinx Full-Text Search server

MySQL Database optimization