In-depth explanation of MySQL learning engine, explain and permissions

engine

Introduction

Innodb engine

The Innodb engine provides support for database ACID transactions and implements the four isolation levels of the SQL standard. The engine also provides row-level locks and foreign key constraints. It is designed to handle large-capacity database systems. It is actually a complete database system based on the MySQL backend. When MySQL is running, Innodb will establish a buffer pool in memory to buffer data and indexes. However, this engine does not support FULLTEXT indexes, and it does not save the number of rows in the table. When SELECT COUNT(*) FROM TABLE is executed, the entire table needs to be scanned. This engine is of course the first choice when database transactions are needed. Since the lock granularity is smaller, write operations will not lock the entire table, so when concurrency is high, using the Innodb engine will improve efficiency. However, the use of row-level locks is not absolute. If MySQL cannot determine the range to be scanned when executing a SQL statement, the InnoDB table will also lock the entire table.

MyIASM Engine

MyIASM is the default engine for MySQL, but it does not provide support for database transactions, row-level locks, and foreign keys. Therefore, when INSERT (insert) or UPDATE (update) data, the write operation needs to lock the entire table, which will be less efficient. However, unlike Innodb, MyIASM stores the number of rows in the table, so when SELECT COUNT(*) FROM TABLE, you only need to directly read the saved value without scanning the entire table. If the table has far more read operations than write operations and does not require the support of database transactions, MyIASM is also a good choice.

Main Differences

1. MyIASM is non-transaction-safe, while InnoDB is transaction-safe

2. The granularity of MyIASM locks is table-level, while InnoDB supports row-level locks

3. MyIASM supports full-text indexing, while InnoDB does not support full-text indexing

4. MyIASM is relatively simple and more efficient than InnoDB. Small applications can consider using MyIASM

5. MyIASM table is saved in file format, which is more convenient for cross-platform use

Application Scenario

1. MyIASM manages non-transactional tables, provides high-speed storage and retrieval, and full-text search capabilities. If you perform a large number of select operations in your application, you should choose MyIASM.

2. InnoDB is used for transaction processing and has features such as ACID transaction support. If a large number of insert and update operations are performed in the application, InnoDB should be selected

MySQL Explain

In daily work, we sometimes run slow queries to record some SQL statements that take a long time to execute. Finding these SQL statements does not mean that the job is done. Sometimes we often use the explain command to view the execution plan of these SQL statements to see whether the SQL statement uses an index and whether a full table scan is performed. This can be viewed through the explain command. So we have a deep understanding of MySQL's cost-based optimizer, and we can also get a lot of details about the access strategies that may be considered by the optimizer, and which strategy is expected to be adopted by the optimizer when running SQL statements. (QEP: sql generates an execution plan query execution plan)

mysql> explain select * from servers;
+----+-------------+---------+------+---------------+------+---------+------+------+------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+------+
| 1 | SIMPLE | servers | ALL | NULL | NULL | NULL | NULL | 1 | NULL |
+----+-------------+---------+------+---------------+------+---------+------+------+------+
row in set (0.03 sec)

The information output by expain has 10 columns, namely id, select_type, table, type, possible_keys, key, key_len, ref, rows, and Extra. The following is an explanation of the possible appearance of these fields:

1. id

My understanding is that it is the order in which SQL is executed. SQL is executed from large to small.

1. When the id is the same, the execution order is from top to bottom

2. If it is a subquery, the ID number will increase. The larger the ID value, the higher the priority and the earlier it will be executed.

3. If the id is the same, it can be considered as a group and executed from top to bottom; in all groups, the larger the id value, the higher the priority and the earlier it is executed

2. select_type

Indicates the type of each select clause in the query

(1) SIMPLE (simple SELECT, without using UNION or subqueries, etc.)

(2) PRIMARY (If the query contains any complex sub-parts, the outermost select is marked as PRIMARY)

(3) UNION (the second or subsequent SELECT statement in UNION)

(4) DEPENDENT UNION (the second or subsequent SELECT statements in a UNION depend on the outer query)

(5) UNION RESULT

(6) SUBQUERY (the first SELECT in a subquery)

(7) DEPENDENT SUBQUERY (the first SELECT in a subquery that depends on the outer query)

(8) DERIVED (derived table SELECT, subquery in FROM clause)

(9) UNCACHEABLE SUBQUERY (the result of a subquery cannot be cached and the first row of the outer link must be re-evaluated)

3. Table

Displays which table the data in this row is about. Sometimes it is not the actual table name, but derivedx (x is a number, which I understand as the result of the execution of the step)

mysql> explain select * from (select * from ( select * from t1 where id=2602) a) b;
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | DERIVED | <derived3> | system | NULL | NULL | NULL | NULL | 1 | |
| 3 | DERIVED | t1 | const | PRIMARY,idx_t1_id | PRIMARY | 4 | | 1 | |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

Type

Indicates how MySQL finds the required row in the table, also known as the "access type".

Common types are: ALL, index, range, ref, eq_ref, const, system, NULL (from left to right, performance from worst to best)

ALL: Full Table Scan, MySQL will traverse the entire table to find matching rows

index: Full Index Scan. The difference between index and ALL is that the index type only traverses the index tree.

range: retrieve only the rows in a given range, using an index to select the rows

ref: indicates the join matching condition of the above table, that is, which columns or constants are used to find the value on the index column

eq_ref: Similar to ref, the difference is that the index used is a unique index. For each index key value, there is only one record matching in the table. In simple terms, it is to use the primary key or unique key as the join condition in multi-table joins.

const, system: When MySQL optimizes a part of the query and converts it into a constant, it uses these types of access. If you put the primary key in the where list, MySQL can convert the query into a constant. system is a special case of the const type. When the query table has only one row, use system.

NULL: MySQL decomposes statements during optimization and does not even access tables or indexes during execution. For example, selecting the minimum value from an index column can be done with a single index lookup.

5. possible_keys

Indicates which index MySQL can use to find records in the table. If an index exists on the field involved in the query, the index will be listed, but it may not be used by the query.

This column is completely independent of the order of the tables shown in the EXPLAIN output. This means that some of the keys in possible_keys cannot actually be used in the order the table is generated.
If the column is NULL, there is no associated index. In this case, you can improve your query performance by checking the WHERE clause to see if it references certain columns or columns that are suitable for indexing. If so, create an appropriate index and check the query again with EXPLAIN.

6. Key

The key column shows the key (index) that MySQL actually decided to use.

If no index was chosen, key is NULL. To force MySQL to use or ignore the index on the possible_keys column, use FORCE INDEX, USE INDEX, or IGNORE INDEX in your query.

7. key_len

Indicates the number of bytes used in the index. This column can be used to calculate the length of the index used in the query (the value displayed by key_len is the maximum possible length of the index field, not the actual length used, that is, key_len is calculated based on the table definition, not retrieved from the table)

The shorter the length, the better without losing accuracy.

8. Ref

Indicates the join matching conditions of the above tables, that is, which columns or constants are used to find the values ​​on the index columns

9. rows

Indicates the number of rows that MySQL estimates it needs to read to find the required records based on table statistics and index selection.

10. Extra

This column contains detailed information about how MySQL solves the query. There are several cases:

Using where: Column data is returned from the table using only the information in the index without actually reading the table. This happens when all requested columns for the table are part of the same index, meaning that the MySQL server will perform filtering after the storage engine retrieves the rows.

Using temporary: Indicates that MySQL needs to use a temporary table to store the result set, which is common in sorting and grouping queries

Using filesort: The sorting operation in MySQL that cannot be completed using indexes is called "file sorting"

Using join buffer: This value emphasizes that no index is used when obtaining the join condition, and a join buffer is needed to store intermediate results. If this value appears, you should be aware that you may need to add an index to improve performance depending on the specific circumstances of the query.

Impossible where: This value emphasizes that the where clause will result in no qualifying rows.

Select tables optimized away: This value means that the optimizer may return only one row from the aggregate function result by using only the index.

Summarize:

• EXPLAIN does not tell you about triggers, stored procedures, or how user-defined functions affect the query

• EXPLAIN does not consider various caches

• EXPLAIN does not show the optimization work that MySQL does when executing the query

• Some statistics are estimates and not exact values

• EXPALIN can only explain SELECT operations. Other operations must be rewritten as SELECT and then the execution plan must be viewed.

MySQL authorization management

grammar:

show grants for 'user'@'IP address'; -- View permissions
Grant permission on database.table to 'user'@'IP address'; -- Authorization
revoke permission on database.table from 'user'@'IP address'; -- revoke permission

Permission Parameters

all privileges All privileges except grant
Select only check permissions
select,insert query and insert permissions
...
usage No access rights
alter Use alter table
alter routine using alter procedure and drop procedure
create Use create table
create routine Use create procedure
create temporary tables Use create temporary tables
create user Use create user, drop user, rename user, and revoke all privileges
create view Use create view
delete Use delete
drop Using drop table
execute using call and stored procedure
file uses select into outfile and load data infile
grant option using grant and revoke
index Using index
insert Use insert
lock tables Using lock table
process Use show full processlist
select Use select
show databases Using show databases
show view Use show view
update
reload using flush
shutdown Use mysqladmin shutdown (shut down MySQL)
super 􏱂􏰈Use change master, kill, logs, purge, master, and set global. Also allows mysqladmin􏵗􏵘􏲊􏲋debug login
replication client server location access
replication slave Used by replication slaves

For permissions

Permission Parameters

Database parameters

For the target database and others inside:
Database name.* All in the database
Database name. Table specifies a table in the database
Database name.Stored procedure Specifies the stored procedure in the database
*.* All databases

User IP parameters

Username@IP address The user can only access it under the changed IP address [email protected].% The user can only access it under the changed IP segment (the wildcard % means any)
Username@% The user can access the site from any IP address (the default IP address is %)

Example

grant all privileges on db1.tb1 TO 'username'@'IP';
 
   grant select on db1.* TO 'username'@'IP';
 
   grant select,insert on *.* TO 'username'@'IP';
 
   revoke select on db1.tb1 from 'username'@'IP';

Refresh permissions

Flush privileges, read the data into memory, so that it takes effect immediately.

forget the password

# Start the authorization-free server mysqld --skip-grant-tables
 
# Client mysql -u root -p
 
# Change username and password update mysql.user set authentication_string=password('666') where user='root';
flush privileges;
 
forget the password

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.