Basic understanding of MySQL storage performance optimization

These days to learn about the MySQL database on the nature of the optimization and design content, now do a summary of their own learning, follow-up I will continue to study further, improve the following summary:

1. Use index

Up to 16 indexes per table to support multiple-column indexes and Full-text indexing

Build index: CREATE index index_name on users (username);

View index: Show index from users;

is a double-edged sword, build not indexed, mainly look at the function of the table, find a good balance point.

2. Use Explain analysis query

In the query using explain

Explain select *from users;

3, adjust the MySQL internal configuration

(1), change index buffer length (key_buffer);

It is recommended to set 25% of the total system memory.

(2), change the length of the table (read_buffer_size)

When a database scans a table frequently, MySQL allocates a memory buffer, and if it feels that the scan is too slow, it can be sized appropriately.

(3), set the number of open Table size (Table_cache)

This variable controls the maximum number of tables that MySQL opens at any time, thereby controlling the server's ability to respond to input requests. He's with max_connections.

(4), set a time limit on the slow query (Long_query_time)

Ysql has a "slow query log" that automatically records all of the queries that have not yet ended within a specific time span. This log is useful for tracking inefficient or misbehaving queries and looking for optimized objects. Long_query_time variable controls This maximum time limit, in seconds. can also be set to MS, but need to be patched.

As for security considerations, such as testing database and online separation, backup, master and subordinate, these follow-up talk, Time is pressing, write so much, to be continued ...