Website Optimization-mysql explain execution plan

Explain execution plan

About MySQL tuning:

2. Find the problem first (slow query, profile)
4. For using indexes and not using indexes, learn that indexes can quickly find data
6. Understanding what an index is (an index is an ordered, fast-looking data structure)
8. Management of indexes (view, create, delete)
10. Data structure of the index itself (b-tree structure and clustered structure)
12. How MySQL is going to use these indexes (explain tools), need to find out whether the index is being used reasonably, and prevent the index from abusing.

    ?

    Note: Indexes are helpful for queries, but there is no benefit to updating them. The index should be established reasonably.

    ?

    a general idea for MySQL tuning (interview questions) : MySQL can be tuned with the above steps, You can use MySQL to work optimally, as long as the above steps are repeated and executed repeatedly.

    ?

    Profile Definition: A tool provided by MySQL that can parse SQL statements Execute procedures , It gives you a general idea of how MySQL will execute the SQL statements that the user passes over.

    Use

    Whenever you want to precede the corresponding SQL statement with the keyword explain or synonyms desc

    # desc SELECT * from tableName where id = 1000000;

    # explain select * from tableName where id = 1000000;

    The above two SQL execution effect is equivalent.

    ?

    You can compare indexed and non-indexed execution plans in a simple first step:

    no_index without Index Analysis:

    ?

    rows: represents the result of the query satisfying the SQL statement and may require querying the number of rows.

    ?

    id the existence of a primary key index:

    ?

    Type: Critical analysis

    ?

14. All represents a full table scan, which is typically the next time you do not use an index

    Full table Scan: A line-by-row comparison of rows and then returns records that meet the criteria .

    ?

16. Const, which represents the primary key index used. Development, try to appear this. Constant Lookup

    ?

    3. Range represents a range operation, and the index can also be used when the SQL statement being executed is a range lookup.

    range Meaning: because the index is a well-ordered structure. Along this orderly structure, and then to intercept a sequence of data, it can be used on the index.

    ?

    ?

    Interview question: What kind of field is appropriate for indexing? (What are the considerations when building an index?) ）

    For:

18. Fields that are queried after the where condition need to be indexed.
20. Indexes can also be used in these cases (sort order Range Range Group group), so it is also possible to add indexes after the sort fields.

    Note: Not all fields are indexed after the where condition, because the index itself is also cost-based.

    ?

22. Index represents the use of indexes

    As record statistics, you can directly use the index to return the number of rows of records, there is no need to go to the number of disk files to do statistics.

    ?

24. system, when a table has only one row of records, MySQL thinks it can directly return record information without having to use the index file. Generally appear in a row of records (self-established tables) or system-level tables (Mysql.user) general MySQL at boot time, some of the system-level table priority load into memory to do a cache.

    ?

26. NULL, which can occur if this appears, also represents good. Basic not easy to appear.

    Summary: null "SYSTEM" Const "range" index "all"

    Note: Try not to show all

    ?

Website Optimization-mysql explain execution plan