MySQL Common considerations and optimization

Common considerations for MySQL

2. Fuzzy query like the default is to index the name field

   Note: when using a fuzzy query, the index cannot be used when the% is in the position of the first letter. However, the index can be used when the% is in the other position.

   ?

   # select * from TableName where name is like "%zhangsan";? Can I use the index? No.

   Analysis: Because it is an indeterminate query, any row of records in the table may satisfy the query criteria.

   ?

   #select * from TableName where name is like "zh%"; Can I use it? OK

   #select * from TableName where name is like "zh% three"; Can I use it? OK

   # select * from TableName where name is like "Z%san";? Can I use it? Yes, first you can quickly locate the beginning of the z-letter section. Reading the z-letter range can only be compared line by row.

   ?

4. Do not use functions or operations on the columns of the query . 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 above the query field. It is also not possible to use all of the functions, and the general solution is to give the results of the query to the PHP Program (strings and arrays) for processing. Do not put the processing of functions in MySQL to complete.

   ?

6. In your development, what fields are appropriate for indexing? A brief description of why?

   For:

8. The field that is behind the where is the query condition is appropriate for indexing.
   1. Note: The only bad field is not suitable for indexing, such as a field for both male and female cases.
10. You can also create an index on the field that you want to sort on.

    ?

    Perform order by without qualification, full table scan, filesort meaning note: Question?

    ?

12. A common scenario for MySQL optimization?

    For:

14. Turn on the MySQL slow query recording function, let the system run for a period of time (test time is half a month to one months time)
16. Check the log information of the slow query and analyze the SQL statements that may be problematic
18. When using the Profile tool to analyze SQL statement execution in detail, the time spent on each step, sending data (the possible problem is that MySQL does not use the index, it will go to the disk to get it: experience)
20. The SQL statement is optimized (the SQL statement itself is problematic, such as using a function on the field of the query) or the structure of the table (some query fields in the table are not indexed) make the appropriate adjustments (index rebuild or build more appropriate)
22. Use the Explain tool to analyze how these SQL statements execute when they are executed (perhaps by using an index or using those indexes)
    1. Type:const \ Index \ The presence of these representative indexes normal use
24. Repeat the above process for specific analysis.

    ?

26. MySQL's own cache

    Explanation: After the MySQL client sends the SQL statement to the MySQL server, it checks the permissions first, then queries the SQL statement for the existence of the cached information, and if it does exist, the MySQL server needs to parse the SQL statement, do lexical parsing, It then compiles, produces the execution tree, goes to the disk to fetch the data, gets the data, caches it into its own cache container, and then returns the data.

    ?

    Use:

    # Show variables like "%cache%";

    Change the size of the MySQL cache (32M), and note that the unit is B (bytes)

    # set global query_cache_size = 1024*1024*32;

    Note: The first one is to add the keyword to the global second is the size of the unit B (bytes) for the third specific given how much is appropriate, depending on the memory size of its operating system.

    Test comparison:

    ?

    Note: MySQL's own cache requires two points to note:

28. MySQL's SQL statement cannot contain indeterminate information (for example, using the now () function in the condition), and MySQL's own cache will not be used. The structure of the query is not cached.

    Analysis:

    ?

30. MySQL's own cache is strictly based on SQL statements (MySQL's own cache is strictly case sensitive to SQL statements) select = = Select keyword is actually the same

    ?

    ?

32. MySQL Architecture analysis ( read and write separation )

    Since 70% of the business in a Web site is basically read, the rest is written. So the pressure to read at this time is too high, and it takes a certain way to reduce stress, and this time you can use a read-write separation structure to achieve stress sharing.

    How to query is read-oriented?

    # Show status like "%com_%";

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

    ?

    # Show status; You can view a status message for MySQL.

    ?

    ?

    ?

    Read-Write Separation overview:

    Mysql-proxy This tool enables parsing of SQL statements to determine whether the SQL statement is a read (select keyword) or a write operation (insert, UPDATE, delete). Finally, to connect the different servers to achieve business completion.

    When the reading is complete, Mysql-proxy will select one (poll, weighted, Ip_hash) to complete the read operation from the read server to the table.

    When the write is complete, go directly to the server that connects the write

    ?

    Problem:

    Since the data is only implemented on the primary server, but the write operation is not completed from the server, the data will be inconsistent at this time.

    Need to resolve the issue of consistency?

    A: You can use one of MySQL's bin logs to complete data consistency issues.

    ?

    Steps to use:

    Primary server configuration:

34. Start the bin log on the primary server (the bin log is a MySQL binary log feature that records SQL statements that cause changes to MySQL data , forming a log file)
36. Define a server_id = number in the configuration file on the primary server to indicate this server
38. Add an authorized account on the master server to get the bin log.

    ?

    From the server configuration:

40. Turn on a relay log from the server, this log is mainly after the primary server bin log read (cannot be used directly, you need to convert to a relay log), to form a conversion.
42. Define a server_id =number from the configuration file on the server do not repeat with the primary server
44. Use the master server's authorized account to link to the master server to get the bin log , and then read to the local, to form an intermediate log, and then in the local MySQL to execute again, and the master server on the same data file.
46. Start the master-slave replication feature from the server.

    ?

    Summary: master-slave replication is a basis for complete read-write separation . (a little bit delayed)

    ?

    Optimization technology

    Index overrides

    For MyISAM's storage engine, if the query field information appears exactly in the index file, this time does not need to do the back row operation, directly can be returned from the index file is called Index overlay. (the index exactly covers the fields of the query)

    ?

    Application: One page turn effect under Big data (millions of data)

    Technical point: How does the page turn?

    Answer: SELECT * from TableName limit offset,page;

    Like now it's n pages, each page shows page bar

    offset = (N-1) * page

    ?

    Actual use:

    Paging operations

    When there's a lot of page numbers,

    ?

    The above comparison found that MySQL when using the limit paging, when the page number is large enough, the efficiency is very low, why?

    A: The main reason is that when MySQL uses limit to do queries, the following SQL:

    SELECT * from TableName limit offset,page;

    Execution process:

    The offset+page record is removed, and the offset bar record is discarded, and the page record is returned.

    Therefore, it is necessary to optimize the operation of this situation:

    ?

48. Done from the business (restricting the user's behavior, not allowing page pages to exceed the specified page number)

    Baidu restricts user behavior:

    ?

49. Do not consider restricting the behavior of the user,You can use indexes to implement。
    1. SELECT * from tableName where ID > number limit page;

    When the paging page number becomes larger:

    By using where ID > number limit 10; This makes it possible to use the index of the primary key on the ID so that it can be quickly positioned to achieve the paging effect of a big data.

    ?

    ?

    Problem:

    A. Select * from tableName where ID > number limit page;

    B. Select * from tableName limit number, page;

    ?

    When the above two SQL statements are executed, when are the results identical and when are they inconsistent?

    Answer: When the data is not deleted by the physical row. This time the data is consistent, but when there are physical rows deleted, the data is inconsistent.

    How to solve the above problem?

    A: Since the physical row is deleted, then do not do the deletion of physical rows, only to do the tombstone (set a is_delete field 0 means that no deletion of 1 represents has been deleted).

    After using the tombstone, the data will be consistent, as long as the data is displayed at the display level (HTML) so that the is_delete=1 is not displayed. if (Is_delete = = 1) echo ' This message has been deleted! For example: The common Baidu Bar, NetEase news end.

    ?

    3. Implement physical row deletion without restricting user behavior

    A: This can be achieved by using the index overlay + lag correlation technique .

    Analysis:

    Program code implementation:

    Implement???????? at the PHP level

51. Because the primary key ID is quick to find, first identify the satisfying condition ID

    ?

53. Then quickly find the corresponding record based on the ID.

    foreach ($data as $k = = $v) {

    $sql = select * from tableName where id = $v;

    $res = mysql_query ($sql);

    $row = Mysql_fetch_assoc ($res);

    $result []= $row;

    }

    $result//Paging data

    ?

    At the MySQL level, the table processing:

    Using tables to perform paging operations on big data

    ?

    Explain

MySQL Common considerations and optimization