MySQL-Engine, Explain, permissions

First, Engine introduction InnoDB engine

The InnoDB engine provides support for database acid transactions and implements four isolation levels for the SQL standard. The engine also provides row-level and foreign-key constraints, which are designed to handle a large-capacity database system, which is itself a complete database system based on the MySQL backend, and the MySQL runtime InnoDB creates buffer pools in memory for buffering data and indexes. However, the engine does not support an index of type Fulltext, and it does not save the number of rows in the table, and the full table needs to be scanned when Select COUNT (*) from table. The engine is of course preferred when it is necessary to use a database transaction. Because the lock granularity is smaller, the write operation does not lock the full table, so using the INNODB engine increases efficiency when concurrency is high. However, using row-level locks is not absolute, and if MySQL does not determine the scope to scan when executing an SQL statement, the INNODB table will also lock the full table.

Myiasm engine

Myiasm is the default engine for MySQL, but it does not provide support for database transactions or row-level and foreign keys, so it is less efficient to write operations that require the entire table to be locked when insert (insert) or update (updated) data. Unlike InnoDB, however, the number of rows in the table is stored in myiasm, so the Select COUNT (*) from table only needs to read the saved values directly and does not require a full table scan. Myiasm is also a good choice if the table reads much more than writes and does not require support for database transactions.

Main differences

1, Myiasm is non-transactional security, and InnoDB is a transaction security

2, Myiasm lock granularity is table-level, and InnoDB support row-level lock

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

4, myiasm relatively simple, more efficient than InnoDB, small applications can consider the use of myiasm

5, Myiasm table saved as a file form, cross-platform use more convenient

Scenario 1, Myiasm manages non-transactional tables, provides high-speed storage and retrieval, and full-text search capabilities, and if you perform a large number of select operations in the application, you should choose MyIASM2, InnoDB for transaction processing, and ACID transaction support. If you perform a large number of insert and update operations in your app, you should select InnoDB

MySQL explain detailed

In the daily work, we will sometimes slow query to record some long-running SQL statements, to find out these SQL statements does not mean that it is done, we often use the explain command to view the execution plan of these SQL statements, to see whether the SQL statement is used index, There is no full table scan, which can be viewed through the explain command. So we have an in-depth look at the cost-based optimizer of MySQL, as well as the details of the access policies that might be considered by the optimizer, and which strategy is expected to be adopted by the optimizer when running the SQL statement. (Qep:sql generates an execution plan query execution plan)

1Mysql> explainSelect* fromservers;2+----+-------------+---------+------+---------------+------+---------+------+------+-------+3| ID | Select_type | Table | Type | Possible_keys | Key | Key_len |ref| Rows | Extra |4+----+-------------+---------+------+---------------+------+---------+------+------+-------+5|1| Simple | Servers | All | NULL | NULL | NULL | NULL |1| NULL |6+----+-------------+---------+------+---------------+------+---------+------+------+-------+7Rowinch Set(0.03Sec

There are 10 columns of expain information, namely ID, select_type, table, type, Possible_keys, key, Key_len, ref, rows, and Extra, which can be explained in the following fields:

First, ID

My understanding is the identification of the order of SQL execution, SQL from large to small execution

1. Same ID, execution order from top to bottom

2. If it is a subquery, the ID number is incremented, the higher the ID value, the higher the priority, the more executed

3.id if the same, can be considered a group, from the top down in order to execute; In all groups, the higher the ID value, the higher the priority, the higher the first execution

Second, Select_type

the type of each SELECT clause in the display query

(1) Simple (easy Select, do not use union or sub-query, etc.)

(2) PRIMARY (if any complex sub-parts are included in the query, the outermost select is marked as PRIMARY)

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

(4) DEPENDENT Union (the second or subsequent SELECT statement in Union, depending on the query outside)

(5) Union result (Results of Union)

(6) Subquery (the first select in a subquery)

(7) DEPENDENT subquery (the first select in a subquery, depending on the query outside)

(8) DERIVED (select of derived table, subquery from clause)

(9) Uncacheable subquery (the result of a subquery cannot be cached, the first row of the outer link must be re-evaluated)

Third, table

Show the data in this row about which table, sometimes not the actual table name, see Derivedx (X is a number, my understanding is the result of the first few steps)

1Mysql> explainSelect* from(Select* from(Select* fromT1whereId=2602) a) b;2+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+3| ID | Select_type | Table | Type | Possible_keys | Key | Key_len |ref| Rows | Extra |4+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+5|1| PRIMARY | <derived2> | System | NULL | NULL | NULL | NULL |1| |6|2| DERIVED | <derived3> | System | NULL | NULL | NULL | NULL |1| |7|3| DERIVED | T1 |Const| primary,idx_t1_id | PRIMARY |4| |1| |8+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

Iv. type

Indicates how MySQL finds the desired 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 poor to good)

All:full table Scan, MySQL will traverse the full table to find a matching row

Index:full index Scan,index is different from all for index type only traversal index tree

Range: Retrieves only the rows for a given range, using an index to select rows

Ref: Represents the connection matching criteria for 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 match in the table, simply, the use of primary key or unique key as the association condition in a multi-table connection

Const, System: Use these types of access when MySQL optimizes a 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, the system is a special case of the const type, and when the queried table has only one row, use the system

Null:mysql decomposes statements during optimization, and does not even have access to tables or indexes, such as selecting a minimum value from an indexed column to be completed by a separate index.

Wu, Possible_keys

Indicates which index MySQL can use to find records in the table, and if there are indexes on the fields involved in the query, the index will be listed but not necessarily used by the query.

The column is completely independent of the order of the tables shown in the explain output. This means that some keys in Possible_keys are not actually used in the generated table order.
If the column is null, there is no index associated with it. In this case, you can improve your query performance by examining the WHERE clause to see if it references some columns or columns that fit the index. If so, create an appropriate index and check the query again with explain

Six, Key

The key column shows the keys (indexes) that MySQL actually decides to use

If no index is selected, the key is null. To force MySQL to use or ignore the indexes in the Possible_keys column, use the forces Index, using index, or ignore index in the query.

Seven, Key_len

Represents the number of bytes used in the index, which calculates the length of the index used in the query (the value displayed by Key_len is the maximum possible length of the indexed field, not the actual length, i.e., Key_len is computed from the table definition, not retrieved from the table)

Without loss of accuracy, the shorter the better

Viii. ref

Represents the connection matching criteria for the above table, that is, which columns or constants are used to find the value on the index column

Nine, rows

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

Ten, Extra

This column contains the details of the MySQL resolution query, in the following cases:

Using Where: Column data is returned from a table that uses only the information in the index and does not read the actual action, which occurs when all the request columns of 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 are common in sorting and grouping queries

Sort operations that cannot be done with indexes in the using Filesort:mysql are called "File sorting"

Using join buffer: The change emphasizes that the index is not used when getting the join condition, and the connection buffer is required to store the intermediate result. If this value is present, it should be noted that depending on the specifics of the query, you may need to add an index to improve the performance.

Impossible where: This value emphasizes that the where statement results in rows that do not meet the criteria.

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

Summarize:
explain won't tell you about triggers, stored procedures, or user-defined functions that affect queries
explain does not consider the various caches
Explain cannot display the optimizations that MySQL made when executing queries
• Some of the statistics are estimates, not exact values
Expalin can only interpret the select operation, and other operations are rewritten as Select to view the execution plan.

MySQL Authorization management Syntax:

1Show grants for 'User'@'IP Address'； --View Permissions2Grant permissions on the database. Table to'User'@'IP Address'； --Authorized3Revoke permissions on the database. Table from 'User'@'IP Address';---Cancel permissions

Permission parameters

1 all privileges permissions except Grant2             SelectCheck Permissions only3             Select, insert check and insert permissions4             ...5 usage does not have access rights6 alter using ALTER TABLE7 alter routine using ALTER procedure and drop procedure8 create using CREATE TABLE9 create routine using CREATE PROCEDURETen Create temporary tables using the Create temporary tables One The Create user uses the Create user, drop user, rename user, and revoke all privileges A CREATE view using CREATE VIEW - Delete using Delete - drop using drop table the execute uses call and stored procedures - file using SELECT INTO outfile and load data infile - Grant OPTION uses GRANT and revoke - index using index + Insert using Insert -             Locktables using the lock table + process using show full processlist A             Selectusing Select at show databases using show databases - Show view using Show view - Update using Update - Reload using flush - shutdown using mysqladmin shutdown (turn off MySQL) -Super???? Use change master, kill, logs, purge, master, and setGlobal. also allows mysqladmin???????? Commissioning Login in replication Client server location Access -Replication slave used by replication slaves

Database parameters

1 for the target database and other internal: 2             Database name. * All 3 database names            in the database             . Table          Specifies a table 4 database name in the database             . Stored Procedures     Specifying stored procedures in a database 5             * *                all databases

User IP parameters

1 User name @ip address         users can only access the 2 user name @192.168under IP change. 1.%   users can only be accessed under the IP segment (wildcard% = any)3 user name @%             user may access under any IP (default IP address is%)

Example

1Grant all privileges the DB1.TB1 to'User name'@'IP';2  3GrantSelectOn db1.* to'User name'@'IP';4  5GrantSelect, insert on *. * To'User name'@'IP';6  7RevokeSelectOn DB1.TB1 from 'User name'@'IP'；

View CodeRefresh Permissions

1 flush privileges, which reads data into memory for immediate effect.

Forgot password

1 # start the license-free service2Mysqld--skip-grant-Tables3  4 # Client5Mysql-u Root-P6  7 # Modify user name Password8Update Mysql.userSetAuthentication_string=password ('666')whereUser='Root';9Flush privileges;

MySQL-Engine, Explain, permissions detailed