MySQL Optimization Technology

MySQL Optimization Technology
Introduction

The static page technology can cache dynamic data, but sometimes you still need to request the database. Therefore, database optimization is indispensable.

Optimization ideas

DESIGN: storage engine, field, paradigm

Itself: Index, its own Cache

Architecture: read/write splitting

 

Storage engine:

Comparison between MyISAM and InnoDB. Of course, you need to know that MySQL has other storage engines (memory storage engines) besides these two storage engines ).

After MySQL 5.5, the default storage engine is InnoDB.

During the interview, you only need to tell the difference between MyISAM and InnoDB.

 

Field selection:

It is suitable. You can use tinyint instead of int.

 

Paradigm: the three paradigms mainly aim to reduce data redundancy. Basically, the tables designed are satisfied.

 

Note: during development, the relationship between tables is first designed at the beginning of design, and the table structure is rarely modified during later development. (System architecture, Manager)

 

Considerations

When optimizing MySQL itself, if we want to optimize it, What should we first find the problem? How to find problems?

Problems Found

1. Need to locate the problem?

   A: You can use the slow query log feature provided by MySQL to issue SQL statements that are problematic, and then optimize them.

2. What is slow log query?

   A: The logs provided by MySQL can be used to record SQL statements that exceed a specified time limit.

   Use MySQL's slow query function
3. Enabled. The MySQL configuration file does not exist by default. You need to add the file by yourself and restart MySQL.

    

4. Test whether SQL statements that exceed the time limit can be recorded
   1. Create a large data table

   B) Make a query on a field without an index.

   Slow query log analysis:

   Note: Perform query operations on indexed fields.

    

   Conclusion: After the index is used, the query speed is much faster, however, the time recorded by using the slow query log function of MySQL is relatively rough (the time level of the slow query log can only be 1 s or more.

   In this case, if the record time is less than 1 s, the slow query will not be able to play its role. You can use MySQL to provide another function. This function is called profile.

    

   Use of MySQL profile

    

   MySQL provides a function that records more precise time and records SQL statements less than 1 s.

    

5. Use: disabled by default. You need to enable it on your own.

   # Show varibales like "% profil %"; you can use this fuzzy match query if you cannot remember it.

6. Enable

   # Set profiling = 1;

7. Simple Test

   # Show profiles; you can view the time recorded by the profile tool

8. This tool can also be used to analyze in detail each step of the SQL statement during execution (such as connection, permission check, SQL statement compilation, and table opening .... Execution, data return) time spent.
   1. For example, the time spent on executing the fourth SQL statement is detailed.

      # Show profile for query 4;

   Note: Why is indexing so fast?

   A: When MySQL is started, it usually loads the index file into the memory. Even if it does not, the index loading is faster than searching one by one on the disk. Then the index itself is a kind of structure that is very helpful for searching. In this case, you can use the index to quickly locate the data to be queried. After the data is queried, the data will be retrieved from the disk.

    

   Conclusion: by using these tools, we can roughly find that after using indexes, the effects are significantly improved. Therefore, it is necessary to optimize the indexes of MySQL databases.

    

   MySQL index Overview

    

   MySQL Index classification
9. Primary Key Index: it is mainly used to quickly search for and constrain data, and a table can only have one primary key index. Null is not allowed in the field
10. Unique index: It mainly refers to quick search and constraints, but the field can have null, and the information on the fields that create a unique index cannot be repeated, however, you can create multiple unique indexes for multiple fields.
11. Common indexes: these indexes are mainly used for quick search. However, it is worth noting that index creation is also costly. Indexes can be quickly searched. However, when data is inserted or updated, indexes may affect the insertion efficiency. Therefore, do not create too many common indexes.
12. Full-text index: It is generally effective for text fields. However, because MySQL storage engine only supports MyISAM and only supports English, another software called sphindexing can be used to replace full-text index.
    1. In another case, you must note that the index cannot be used when you select * from tableName where name like "% sheets %" when using like for Fuzzy queries. Like queries require a full table scan.
    Index management

     

    An index is a sorted, fast-looking data structure.

    What is sorting order?

    For example, in the above structure, we can find that after the letter D, information related to the letter A cannot appear.

     

13. Index creation
    1. Create a table
    1. Create a table when modifying the table structure

     

14. Index display

     

15. Index Deletion

    Q: Why is update not provided?

    A: There is no need to design an update. You only need to delete the update and create it.

     

    Index Structure

     

    Because the index itself is a kind of data, it also needs to occupy disk space, so the index also has a data structure, the common data structure is divided into two types: B-TREE structure and cluster structure.

     

    B-TREE Structure

     

    For the index of the B-TREE structure, the index node stores the physical row address, when the query needs to do back row operation. All indexes for the MyISAM storage engine are in a B-TREE structure.

    Structure:

    Cluster Structure

     

    For the index of the cluster structure, the index node stores the records of the row, and you do not need to perform the row return operation when querying. All indexes of the InnoDB Storage engine are clustered.

    NOTE 1: For the InnoDB Storage engine, if there is no primary key index, MySQL automatically maintains a primary key index.

    NOTE 2: For the InnoDB Storage engine, the information stored in the index nodes of secondary indexes (unique and common indexes) is a reference to the primary key index.

    Note 3: For the InnoDB Storage engine, if data is added in large batches, there will be a huge performance overhead, mainly when data is inserted in large batches, the primary key index will also be rebuilt. At this time, because the index node of the primary key index stores a record of the row, it requires a large amount of data to be moved in memory, with a very high overhead.

    Structure:

     

    Extension

     

    Read books

    <High-performance MySQL>

    Address:

     

    Question 1:

    Solution:

    Make sure that either or both of the preceding conditions are invalid.

    You can set a button to refresh the cache and then clear the generated static page after it is changed within the cache validity period.

This article permanently updates the link address: