Basic understanding of mysql Storage Performance Optimization

I have learned about mysql database performance optimization and design over the past few days. Now I am going to make a summary of my own learning. In the future, I will continue to study in depth and make a summary:

1. Use Indexes
Each table can have a maximum of 16 indexes, including multi-column indexes and full-text indexes.
Create index index_name on users (username );
View indexes: show index from users;
It is a double-edged sword. If indexes are not created, the table function is used to find a balance point ,.


2. Use explain to analyze and query
Explain in the query
Explain select * from users;

3. Adjust the internal configuration of mysql

(1) Change the index buffer length (key_buffer );
We recommend that you set 25% of the system memory.

(2) Change the table length (read_buffer_size)
When the database frequently scans a table, mysql allocates a memory buffer. If you think the scan is too slow, you can adjust the size of the value.

(3) set the size of the opened table (table_cache)
This variable controls the maximum number of tables opened by mysql at any time, thereby controlling the server's ability to respond to input requests. He and max_connections

(4) set a time limit for slow query (long_query_time)
YSQL has a "Slow query log", which automatically records all queries that have not been completed within a specific time range. This log is useful for tracking inefficient or misperforming queries and searching for optimization objects. The long_query_time variable controls the maximum time limit, in seconds. It can also be set to ms, but patch is required.

As for security considerations, such as test database and online separation, backup, and Master/Slave, we will talk about it later, and the time is short. We will write so much about it first, To be continued...