Website Optimization-mysql Optimization

About MySQL Optimization

Because the page static technology can realize the cache of dynamic data, sometimes it is necessary to request the database. Therefore, the optimization of the database is also indispensable.

Optimization ideas

Design: Storage engine, field, paradigm

itself: The index, its own cache

Architecture: Read-Write separation

?

Storage Engine:

The contrast between MyISAM and InnoDB. Of course you need to know that MySQL has other storage engines (memory storage engine) In addition to these two storage engines.

MySQL The default storage engine is InnoDB after version 5.5

In the course of the interview, just say the difference between MyISAM and InnoDB

?

Field selection:

The right is good, you can use the tinyint not using int

?

Paradigm: The three paradigms are mainly designed to reduce a redundant data, and basically the tables are satisfied.

?

Note: Generally at the time of development, at the beginning of design, the first design is the relationship between table and table, in the late development process is rarely modified table structure . (System architecture, manager)

?

Self-consideration

When it comes to optimizing the MySQL itself, it is necessary to find the problem first. How do I find a problem?

Discover problems

2. Need to locate the problem?

   A: You can use a slow query log feature provided by MySQL to discover problematic SQL statements and then optimize them.

4. What's the slow query log?

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

   Using the slow query feature of MySQL

6. Open, MySQL configuration file inside, default is not present, need to add themselves, need to restart MySQL

   ?

8. Test to see if you can record an SQL statement that exceeds the time limit
   1. Need to build a large data table

   b) Make a query on a field that does not have an index

   Slow Query Log Analysis:

   Note: do a query operation on an indexed field

   ?

   Summary: By comparing the discovery using the index , the apparent query will be much faster, but the time logged with the slow query log feature of MySQL is relatively rough (the recording time level for slow queries can only be more than 1s) .

   This time if you need to record time is less than 1s, slow query will not play its role, you can use MySQL to provide another function to complete, this function is called profile.

   ?

   The use of the MySQL profile

   MySQL provides a feature that can record more precise time, and can be recorded in SQL statements below 1s.

   ?

10. Use: Default is off, you need to open it yourself

    # show Varibales like "%profil%"; This fuzzy matching query can be used when there are times when it is not possible to remember.

12. Open

    # set profiling=1;

14. A simple test

    # show Profiles; Can view the time recorded by the profile tool

16. The tool can also be used to analyze this SQL statement in detail at the time of execution, each step (for example: Connection, permission check, SQL statement compilation, open Table ...). Time spent on execution, data return).
    1. For example now detailed positioning fourth SQL statement execution time each step takes

       # Show profiles for query 4;

    Note: Why is it so fast after using the index?

    A: Since MySQL will normally load the index file into memory, even if not, the index loading is faster than a search on the disk to find a single bar. Then the index itself is a very useful structure to find, this time can be quickly indexed by the index to locate the data to be queried , when the query, and then on the disk to take out the data.

    ?

    Summary: By using the tools above, it can be found that, after using the index, the effect is significantly improved, so it is necessary to optimize the MySQL database indexing.

    ?

    About MySQL Index

    Classification of MySQL Indexes

18. Primary KEY index: it is mainly the effect of Quick Find and constraint , and there can only be one primary key index in a single table. Null is not allowed inside the field
20. Unique indexes: Primarily quick find and constrain, but fields can have null, the information above the fields that make a unique index is not allowed to repeat, but multiple unique indexes can be established for multiple fields.
22. Normal indexes: Primarily for quick lookups, but it is important to note that indexing is also a cost-building. Indexes can be quickly found , but when data is inserted and updated, the index affects the efficiency of the insertion. So the normal index does not build too much.
24. Full-Text Indexing: The text field generally takes effect, but since MySQL's storage engine is only supported by MyISAM and only supports English, another software called Sphinx can be used instead of full-text indexing.
    1. There is also the need to pay attention to the general use like to do fuzzy query when the SELECT * from TableName where the name like "%"; is not available for indexing. Like this query will do a full table scan.

    Management of indexes

    An index is a well-ordered , fast-looking data structure.

    What do you mean, orderly ?

    For example, the above structure, it can be found that after the D letter is not possible in the occurrence of a letter-related information.

    ?

26. Creation of indexes
    1. Build a table when creating
    1. When modifying the table structure, create

    ?

28. Index display

    ?

29. Deletion of indexes

    Question: Why is the update operation not provided?

    A: There is no need to specifically design an update, only need to first delete, under construction.

    ?

    Structure of the Index

    Because the index itself is also a kind of data, also need to occupy disk space, so the index also has data structure, the common data structure divides into two kinds:b-tree structure and cluster structure .

    ?

    B-tree structure

    For the index of the B-tree structure, the index node holds the physical line address, and when the query needs to do the back row operation. For all indexes of the MyISAM storage engine, it is the b-tree structure.

    Structure diagram:

    Clustered structure

    For the index of the clustered structure, the record of the row is stored in the index node, and no return action is required when the query is made. For all indexes of the InnoDB storage engine, it is a clustered structure.

    Note 1: For InnoDB's storage engine, if there is no primary key index, MySQL will automatically maintain a primary key index at this time.

    NOTE 2: for the InnoDB storage engine, the information stored in the index node of the secondary index (unique and normal index) is a reference to the primary key index.

    Note 3: for the InnoDB storage engine, if it is a large number of add data, this time there will be a lot of performance overhead, mainly when the data is inserted in large batches, the primary key index will be rebuilt, this time because the index node of the primary key index is stored in a record of the row, So it's very expensive to move around in memory in large quantities.

    Structure diagram:

    ?

    Extended

    Read books

    << High Performance mysql>>

    Address:

    Http://www.linuxidc.com/Linux/2014-10/108464.htm

    ?

    Question 1:

    Solution Ideas:

    So long as one or two of the two conditions above are not established.

    You can make a button that refreshes the cache, and then, if you change it within the cache validity period, clear the generated static page.

Website Optimization-mysql Optimization