MySQL common precautions

MySQL common precautions

• Fuzzy search like indexes the name field by default.

  Note: When fuzzy search is used, the index cannot be used when % is at the first letter. However, % indexes can be used at other locations.

   

  # Select * from tableName where name like "% zhangsan ";? Can I use indexes? No.

  Analysis: Any row of records in the table may meet the query conditions because the query is not certain.

   

  # Select * from tableName where name like "zh %"; can I use it? Yes

  # Select * from tableName where name like "zh % 3"; can I use it? Yes

  # Select * from tableName where name like "z % san ";? Can it be used? Yes. First, you can quickly locate the part starting with a z letter. Within the range of z letters, you can only compare them row by row.

   

• Do not use functions or operations on the queried columns. Otherwise, the index cannot be used.

  # Select * from tableName where id + 1 = 1000;

  # Select * from tableName where id = 999; equivalent.

   

   

  Sometimes, a function is used on the query field. When using functions, you cannot use all functions. The general solution is to hand over the query results to php programs (strings and arrays) for processing. Do not put the function processing in MySQL.

   

• In your development, what fields are suitable for indexing? Why?

  A:

• Fields that serve as query conditions after where are suitable for index creation.
  1. Note: It is not suitable for creating an index for the only very poor field, for example, only the male and female fields.
• You can also create an index on the fields to be sorted.

   

  Execute order by without restriction, full table scan, filesort meaning Note: Question?

   

• A general solution for MySQL optimization?

  A:

• Enable the slow query record function of MySQL to run the system for a period of time (the test time is from half a month to one month)
• Check slow query log information and analyze SQL statements that may be faulty
• Use the profile tool to analyze in detail the time spent in each step during SQL statement execution, sending data (the possible problem is that MySQL does not use an index, will get data from the disk: experience)
• Optimization of SQL statements (the SQL statements written by themselves have problems, such as using a function for the queried fields) or structure of the table (some query fields in the table are not indexed) make appropriate adjustments (index reconstruction or establishment is more appropriate)
• Use the explain tool to analyze how these SQL statements are executed (indexes may be used or those indexes are used)
  1. Type: const \ index \ The index is normal
• Repeat the detailed analysis of the above process.

   

• MySQL's own Cache

  Explanation: After the MySQL client sends an SQL statement to the MySQL server, it first checks the permission and then checks whether the cache information of the SQL statement exists. If yes, if the SQL statement does not exist, the MySQL server needs to analyze the SQL statement, perform lexical syntax analysis, compile the statement, produce the execution tree, and obtain data from the disk. After obtaining the data, it is cached in a cache container and then the returned data.

   

  Usage:

  # Show variables like "% cache % ";

  Change the MySQL cache size (32 MB). Note that the unit is B (bytes)

  # Set global query_cache_size = 1024*1024*32;

  Note: the first is to add the keyword global. The second is the size unit of B (bytes). The third is the appropriate value, depending on the memory size of the operating system.

  Test comparison:

   

  Note: pay attention to the following two points for the MySQL cache:

• The SQL statement of MySQL cannot contain uncertain information (for example, using the now () function in the condition), and the cache of MySQL itself cannot be used. The query structure is not cached.

  Analysis:

   

• MySQL's own cache is strictly based on SQL statements (MySQL's own cache is strictly case sensitive to SQL statements) select === SELECT keywords are actually the same

   

   

• MySQL Architecture Analysis (read/write splitting)

  Because 70% of a website's businesses are read operations, the rest are write operations. Therefore, the read pressure is too high at this time, so we need to use a certain method to reduce the pressure. In this case, we can use the read/write splitting architecture to share the pressure.

  How does one query the read-dominated data?

  # Show status like "% Com _ % ";

  Through the above analysis for a period of time, you can roughly calculate the website's read and write status

   

  # Show status; you can view a MySQL status.

   

   

   

  Read/write splitting Overview:

  The mysql-proxy tool can analyze SQL statements and determine whether the SQL statements are read (select keyword) or write (insert, update, delete ). Finally, connect to different servers to complete the business.

  When the read is completed, MySQL-proxy selects one (round robin, weighted, ip_hash) from the read server to complete the read operation.

  When writing is completed, directly connect to the write Server

   

  Problem:

  Since the data is only written on the master server, but the write operation is not completed on the slave server, the data will be inconsistent at this time.

  Need to solve the consistency problem?

  A: You can use a bin log of MySQL to solve data consistency problems.

   

  Procedure:

  Master Server Configuration:

• First enable bin log on the master server (bin log is a binary log function of MySQL, which records SQL statements that cause changes to MySQL data to form a log file)
• Defining a server_id = Number in the configuration file on the master server is equivalent to marking the server
• Add an authorized account to the master server to obtain the bin log.

   

  Slave Server Configuration:

• Enable a relay log of the slave server. This log is mainly used to convert the bin log of the master server (which cannot be used directly and must be converted to a relay log first.
• Define a server_id = Number in the configuration file on the slave server. Do not repeat it with the master server.
• Use the authorized account of the master server to connect to the master server to obtain the bin log, then read it locally to form an intermediate log, and then execute it again in the local MySQL, it is consistent with the data file on the master server.
• Start the master-slave replication function of the slave server.

   

  Conclusion: master-slave replication is a basis for completing read/write splitting. (A little delayed)

   

   

For more details, please continue to read the highlights on the next page:

1 2 Next Page