Shulou(Shulou.com)05/31 Report--

Editor to share with you the example analysis of engine, Explain and permissions in mysql. I believe most people don't know much about it, so share this article for your reference. I hope you will gain a lot after reading this article. Let's learn about it together.

engine

Brief introduction

Innodb engine

The Innodb engine provides support for database ACID transactions and implements four isolation levels of the SQL standard. The engine also provides row-level locks and foreign key constraints, which are designed to deal with high-capacity database systems. It is actually a complete database system based on the background of MySQL. MySQL runtime Innodb will establish a buffer pool in memory to buffer data and indexes. However, the engine does not support indexes of type FULLTEXT, and it does not save the number of rows of the table, and the whole table needs to be scanned when SELECT COUNT (*) FROM TABLE. This engine is of course the first choice when you need to use database transactions. Because the granularity of the lock is smaller, the write operation does not lock the entire table, so using the Innodb engine can improve efficiency when concurrency is high. However, the use of row-level locks is not absolute, and if MySQL cannot determine the range to scan 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, so when INSERT (insert) or UPDATE (update) data, write operations need to lock the entire table, which is less efficient. However, unlike Innodb, the number of rows of the table is stored in MyIASM, so SELECT COUNT (*) FROM TABLE only needs to read the saved values directly without the need for a full table scan. MyIASM is also a good choice if the table has far more reads than writes and does not require the support of database transactions.

Main differences

1. MyIASM is non-transactional secure, while InnoDB is transactional secure.

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

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

4. MyIASM is relatively simple and superior to InnoDB in efficiency. Small applications can consider using MyIASM.

5. The MyIASM table is saved as a file, which is more convenient to use across platforms.

Application scenario

1. MyIASM manages non-transactional tables and provides high-speed storage and retrieval as well as full-text search capabilities. If a large number of select operations are performed in the re-application, MyIASM should be selected.

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 detailed explanation

In our daily work, we sometimes slow down the query to record some SQL statements that have been executed for a long time, and finding these SQL statements does not mean we are done. Sometimes we often use the explain command to check the execution plan of these SQL statements, to see if the SQL statement has been indexed, and whether it has done a full table scan, which can be viewed through the explain command. So we take a closer look at MySQL's cost-based optimizer and get a lot of details about access policies that might be considered by the optimizer and which policies are 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 | 1 | NULL | + -+ row in set (0.03 sec)

There are 10 columns of information from expain, namely id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra. The possible occurrence of these fields is explained below:

1. Id

My understanding is the identification of the order in which SQL is executed, and the execution of SQL from large to small

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

two。 If it is a subquery, the sequence number of the id will be incremented. The higher the id value, the higher the priority, and the more it will be executed first.

If the 3.id is the same, it can be considered as a group, which is executed sequentially from top to bottom. In all groups, the higher the id value, the higher the priority and the first execution.

II. Select_type

Shows the type of each select clause in the query

(1) SIMPLE (simple SELECT, no UNION or subquery, etc.)

(2) PRIMARY (if the query contains any complex subsections, the outermost select is marked PRIMARY)

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

(4) DEPENDENT UNION (the second or subsequent SELECT statement in UNION, depending on the external query)

(5) UNION RESULT (result of UNION)

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

(7) DEPENDENT SUBQUERY (the first SELECT in the subquery, depending on the external query)

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

(9) UNCACHEABLE SUBQUERY (the result of a subquery cannot be cached and the first line of the outer link must be reevaluated)

III. Table

Show which table the data in this row is about, sometimes not the real table name, and see derivedx (x is a number, my understanding is the result of which 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 | | system | NULL | 1 | | 2 | DERIVED | | system | NULL | 1 | | 3 | DERIVED | T1 | const | PRIMARY Idx_t1_id | PRIMARY | 4 | | 1 | | +-+

IV. Type

Represents the way in which MySQL finds the required rows in the table, also known as "access type".

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

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

Index: Full Index Scan,index differs from ALL in that index only traverses the index tree

Range: only retrieve a given range of rows, using an index to select 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 the unique index. For each index key value, only one record in the table matches. To put it simply, primary key or unique key is used as the association condition in multi-table joins.

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

NULL: MySQL breaks up statements during optimization and executes without even accessing a table or index. For example, selecting a minimum value from an index column can be done through a separate index lookup.

5. Possible_keys

Indicates which index MySQL can use to find records in the table. If there is an index on the fields involved in the query, the index will be listed, but not necessarily used by the query.

This column is completely independent of the order of the tables shown in the EXPLAIN output. This means that some keys in possible_keys cannot actually be used in the order of the generated table.

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 the index. If so, create an appropriate index and check the query again with EXPLAIN

VI. Key

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

If no index is selected, the key is NULL. To force MySQL to use or ignore indexes in possible_keys columns, use FORCE INDEX, USE INDEX, or IGNORE INDEX in the query.

7. Key_len

Indicates the number of bytes used in the index, which 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 used length, that is, key_len is calculated according to the table definition, not retrieved within the table)

Without losing accuracy, the shorter the length, the better.

VIII. Ref

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

IX. Rows

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

10. Extra

This column contains the details of the MySQL resolution query, and there are several situations:

Using where: column data is returned from a table that only uses the information in the index and does not read the actual action. This occurs when all the request columns on the table are part of the same index, indicating that the mysql server will filter after the storage engine retrieves the rows.

Using temporary: indicates that MySQL needs to use temporary tables to store result sets, which is common in sorting and grouping queries

The sort operation that cannot be done with indexes in Using filesort:MySQL is called "file sorting".

Using join buffer: the change emphasizes that the index is not used when getting the join condition and that a connection buffer is required to store the intermediate results. If this value occurs, it should be noted that indexes may need to be added to improve energy depending on the specific circumstances of the query.

Impossible where: this value emphasizes that the where statement results in no rows that match the criteria.

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

Summary:

EXPLAIN will not tell you about triggers, stored procedures, or the impact of user-defined functions on queries.

EXPLAIN does not consider all kinds of Cache

EXPLAIN cannot show the optimization work done by MySQL when executing the query

Some of the statistics are estimated, not exact values

EXPALIN can only explain SELECT operations. Other operations should be rewritten as SELECT to view the execution plan.

MySQL authorization management

Syntax:

Show grants for 'user' @'IP address';-- View permissions

Grant permissions on database. Table to 'user' @'IP address';-- authorization

Revoke permissions on database. Table from 'user' @'IP address';-- revoke permissions

Permission parameter

All permissions of all privileges except grant

Select check permissions only

Select,insert lookup and insert permissions

...

Usage has no access permission

Alter uses alter table

Alter routine uses alter procedure and drop procedure

Create uses create table

Create routine uses create procedure

Create temporary tables uses create temporary tables

Create user uses create user, drop user, rename user, and revoke all privileges

Create view uses create view

Delete uses delete

Drop uses drop table

Execute uses call and stored procedures

File uses select into outfile and load data infile

Grant option uses grant and revoke

Index uses index

Insert uses insert

Lock tables uses lock table

Process uses show full processlist

Select uses select

Show databases uses show databases

Show view uses show view

Update uses update

Reload uses flush

Shutdown uses mysqladmin shutdown (turn off MySQL)

Super?? Use change master, kill, logs, purge, master, and set global. Mysqladmin???? is also allowed Debug landing

Access to replication client server location

Replication slave is used by replication slaves

For permissions

Permission parameter

Database parameters

For the target database and other internal:

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

User name @ IP address users can only access it under the changed IP

User name @ 192.168.1% users can only access it under the changed IP segment (wildcard% indicates any)

User name @% user can be accessed under any IP (default IP address is%)

Example

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

Refresh permissions

Flush privileges, which reads the data into memory, which takes effect immediately.

Forget the password.

# start the license-free server mysqld-- skip-grant-tables # client mysql-u root-p # modify the username password update mysql.user set authentication_string=password ('666') where user='root';flush privileges; forget the password above is all the contents of the article "sample Analysis of engines, Explain and permissions in mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.