MySQL Advanced 13 - Optimizing SQL through Indexes

1. Overview

Binary tree——>Index file: Efficiency log2N

Retrieve 10 times: 2 to the 10th power. 1024 records.

Overhead caused by index

Looking at the data files (data directory under the installation directory), you will find three files,

.frm: Representing the structure of the table

.myd: Represents the data

.myi: Represents the indexed file

Problems caused by the index: It will lead to the efficiency of insert, update and delete

Fields that are updated frequently are not suitable for creating indexes.

Fields with poor uniqueness are not suitable for creating indexes. For example, only if the gender of a person is male or female

, an index will be created if the following conditions are met

1. It must be often used in the where condition.

2. This field will not change too frequently.

2. Index usage scenarios

1. Quickly find records that meet the where condition.

2. Quickly determine the candidate set. If the where condition uses multiple index fields, MySQL will give priority to using the index that can minimize the size of the candidate set in order to eliminate records that do not meet the conditions as soon as possible.

3. If there is a joint index composed of several fields in the table, when searching for records, the leftmost prefix matching field of the joint index will also be automatically used as an index to speed up the search.

For example: If three indexes are created for a certain table, a joint index composed of (c1, c2, c3), then (c1), (c1, c2), (c1, c2, c3) are all will be used as an index, (c2, c3) will not be used as an index, and (c1, c3) actually only uses the c1 index.

4. Indexes will be used when joining multiple tables (if the fields participating in the join are indexed in these tables)

5. If a field has been indexed, please When performing sort or group operations on this field, MySQL will use the index.

3. Execution plan for inefficient SQL execution through EXPLAIN analysis

For example:

mysql> explain select * from taxgrouptaxes\G  
*************************** 1. row ***************************  
           id: 1  
  select_type: SIMPLE  
        table: taxgrouptaxes  
         type: ALL  
possible_keys: NULL  
          key: NULL  
      key_len: NULL  
          ref: NULL  
         rows: 1  
        Extra:

select_type: Indicates the type of SELECT,

common values There are

SIMPLE: simple table, which does not use table joins or subqueries

PRIMARY: the main query, the outer query

union: the second in UNION or the following query statement

SUBQUERY: the first select in the subquery.

table: the table that outputs the result set

type means that MYSQL finds all the results in the table The way that needs to be done. Or called access type.

Common types include the following;

all

index

range

ref

eq_ref

const,system

null

From top to bottom, the performance ranges from the worst to the best.

1: type=all full table scan.

2: type=index index scan

3: type=tange index range scan. Commonly used in operations such as < <= > >= between

4: type=ref s uses a unique index scan or a prefix scan of a unique index.

5: type=eq_ref is similar to ref. The difference is that the index used is a unique index. For each index key value, only one record in the table matches.

6: type=const/system There is at most one matching row in a single table, and the query is very fast.

7: type=null MYSQL does not need to access tables or indexes. You can get the results directly.

possible_keys: Indicates the indexes that may be used in the query

key: Indicates the actual index used

key_len: The length of the index field used

rows: Number of scan lines

Extra: Description and scanning of execution. Including other columns that do not fit in the display is important to the execution plan.

4. Analyze SQL through show profile

1. First check whether MySQL supports show profile

mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES              |
+------------------+
1 row in set (0.00 sec)

2. If the profile is closed, you can use the set statement at the session level Open profile

set profiling = 1;

3. After execution, you can view the queryID of the current SQL through the show profiles statement.

4. Through show profile for query queryID

The above is the content of MySQL Advanced 13 - Optimizing SQL through indexing. For more related content, please pay attention to the PHP Chinese website (www.php.cn )!