Technology Sharing

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

First, briefly introduce sql:

The SQL language is divided into four categories: data query language DQL, data manipulation language DML, data definition language DDL, and data control language DCL.

1. Data Query Language DQL
The basic structure of the data query language DQL is a query block consisting of the SELECT clause, the FROM clause, and the WHERE clause: SELECT <field name table> FROM <table or view name> WHERE <query condition>

2. Data Manipulation Language DML
There are three main forms of data manipulation language DML:
1) INSERT
2) Update: UPDATE
3) Delete: DELETE

3. Data Definition Language DDL
Data Definition Language (DDL) is used to create various objects in the database - tables, views, indexes, synonyms, clusters, etc. For example: CREATE TABLE / VIEW / INDEX / SYN / CLUSTER | table view index synonym cluster. DDL operations are implicitly submitted! Cannot be rolled back

4. Data Control Language DCL
Data Control Language (DCL) is used to grant or revoke certain privileges to access the database, control the time and effect of database manipulation transactions, monitor the database, etc. For example:
1) GRANT: authorization.
2) ROLLBACK [WORK] TO [SAVEPOINT]: Roll back to a certain point. Rollback --- ROLLBACK rollback command returns the database status to the last submitted status. Its format is: SQL>ROLLBACK;
3) COMMIT [WORK]: Commit. When inserting, deleting, and modifying data in a database, the transaction is considered complete only when it is committed to the database. Before a transaction is committed, only the person operating the database has the right to see what has been done. Others can only see it after the transaction is finally committed.

About authorization statements

Database authorization command:

GRANT <privilege> on table name (or column name) to user

Correct answer option B: insert,select: Permission table name: user User: nkw

Supplementary knowledge point - reclaiming permissions

REVOKE <privilege> on table name (or column name) FROM user

Explain statement introduction

I believe everyone is familiar with the explain statement, which is used to view the execution plan. Explain is often used in SQL optimization analysis.

It is worth noting here that explain does not actually execute the statement, it just displays the execution plan.

What information can I see?

• Table reading order
• Operation type of data read operation
• Which indexes can be used
• Which indexes are actually used
• References between tables
• How many rows of each table are queried by the optimizer

Basic grammar introduction

EXPLAIN SELECT select具体语句
如：
EXPLAIN SELECT * FROM userpro

Explain the role of each column

Detailed description of each column in EXPLAIN

1，id

Each SELECT keyword corresponds to an id

The larger the id value, the higher the priority, and the earlier it is executed.

If the id is the same, it can be considered as a group and executed sequentially from top to bottom.

Each ID number represents an independent query. The fewer the number of queries for a SQL statement, the better.

2，select_type

3. The table column represents the table name of the table (sometimes it is not the real table name, it may be an abbreviation).

4. partitions (optional)

5. type ☆

Common types are as follows

Generally try to avoid all

6， key and possible_keys

1. key：

   • key The field shows the index actually used by the query. If the value of this field isNULL, it means that no index is used. If this field has a value, it means that MySQL uses the specified index to execute the query.

2. possible_keys：

   • possible_keys The field shows a list of indexes that MySQL can use. These indexes are indexes that can be considered in the query, but may not be actually used. Usually,possible_keys The indexes listed in are determined based on the query conditions and table structure.

• if key The field has an index name, andpossible_keys Multiple index names are listed in the table, indicating that MySQL has selectedkey The fields are listed in the index to perform the query, while other indexes are listed inpossible_keys It was considered, but ultimately not used.

• if key Fields areNULL,and possible_keys Multiple index names are listed in the query, indicating that MySQL did not use any indexes when executing the query, which may result in a full table scan or other non-index optimized access method.

7，key_len

key_len It is a field that describes the length of the index key. It indicates the usage of the index and the length of the index key when MySQL executes a query using a specific index.

1. Single column index：

   • If a single-column index is used, and the column type is fixed-length (for example INT),but key_len The value is the length of the column.
   • If variable length fields are used (e.g. VARCHAR),but key_len The value is the maximum length of the field.

2. Composite Index：

   • For a composite index (that is, an index containing multiple columns),key_len Indicates the total length of all columns in the index.

3. Composite Index：

   • If a composite index of multiple columns is used in the query,key_len Is the total length of all columns in the composite index.

4. Index prefix：

   • In some cases, MySQL might use only part of an index. For example, you might be able to execute a query using a prefix of the index as part of the index. In this case,key_len The length of the index portion actually used will be displayed.

8，ref

ref It is a field that describes the join conditions between tables or uses non-unique indexes for search. It indicates which join conditions or indexes MySQL uses to access tables when executing queries.

9，rows

rows Is an estimate of the number of rows that are accessed or examined when executing a query.

• For simple SELECT Inquire,rows Indicates the estimated number of rows to be returned.
• For join queries (JOIN) or a subquery,rows Probably indicates the number of rows accessed during a join operation.
• For a table scan (full table scan or index scan),rows Probably indicates the number of rows scanned.

10，filtered

Indicates the percentage of rows filtered according to WHERE conditions and index conditions. Filtered reflects the optimizer's estimated query optimization effect.

when filtered When it is close to 100%, it means that the query condition effectively filters out most of the rows that do not meet the condition, which is usually a good optimization indicator.

On the contrary, if filtered A low value may indicate that the query condition is not precise enough or that the optimizer is not effectively using the index to filter data.

11，Extra

Additional Information Field

Here are some common Extra Fields and their meanings:

1. Using index：

   • Indicates that the query uses a covering index, that is, the query results can be returned entirely through the index without accessing the actual data rows of the table.

2. Using where：

   • Indicates that the MySQL server will perform the conditional filtering after the storage engine retrieves the row, rather than doing it in the index.

3. Using temporary：

   • Indicates that MySQL created a temporary table in memory to process the query. This is common in sorting operations or queries containing aggregate functions.

4. Using filesort：

   • Indicates that MySQL performed a file sort to process the query. This usually occurs when the sort cannot be completed using an index.

5. Range checked for each record (index map: ...)：

   • Indicates that MySQL uses the index to check whether each record is within the specified range. This usually happens when range Query type.

6. Full scan on NULL key：

   • Indicates that MySQL performed a full table scan in an index to find rows matching NULL values.

7. Distinct：

   • Indicates that MySQL will stop looking for duplicate rows after finding the first matching row.

8. Using join buffer (Block Nested Loop)：

   • Indicates that MySQL is using the connection buffer to process connection operations, which usually occurs when there are a large number of connection tables or the size of the connection table is large.

9. Impossible WHERE：

   • Indicates that the MySQL optimizer determined that the condition in the WHERE clause cannot possibly be satisfied, so no rows will be scanned.

10. No tables used：

    • Indicates that the query does not involve any tables, for example SELECT NOW()。

explain actual execution display

data preparation

Create table s1

CREATE TABLE s1 (
id INT AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
INDEX idx_key1 (key1),
UNIQUE INDEX idx_key2 (key2),
INDEX idx_key3 (key3),
INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

Create table s2

CREATE TABLE s2 (
id INT AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
INDEX idx_key1 (key1),
UNIQUE INDEX idx_key2 (key2),
INDEX idx_key3 (key3),
INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

Prepare the data yourself.

Sample Execution and Results

Simple query

Single table query

EXPLAIN SELECT * FROM `s1`;

Connection query

EXPLAIN SELECT * FROM `s1` INNER JOIN `s2`;

Subqueries

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field
= 'a');

Special Exhibition

Display select_type

#Union 去重
EXPLAIN SELECT * FROM `s1` UNION SELECT * FROM `s2`;
#Union 全查
EXPLAIN SELECT * FROM `s1` UNION ALL SELECT * FROM `s2`;

The last step is to remove duplicates, so a temporary table will be used for this. However, UNION ALL queries all data, so no temporary table query message will appear.

Type class

const (index is found once, only one row of results)

EXPLAIN SELECT * FROM s1 WHERE id = 10002;

eq_ref (use a unique index or primary key to find a row from another table)

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

ref (use a non-unique index to retrieve one or more rows from another table)

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

range (returns a range of rows using an index)

EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');

index (full scan of the index to find the row)

EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';

Other Exhibitions

Filtered decimal time

EXPLAIN SELECT * FROM s1 WHERE key1 > 'za' AND common_field = 'la'