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 7cb9091baf3e2c81106f6565e75575c8 >= 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 (m.sbmmt.com )!