Mysql Doc Read notes

Mysql Doc Read notes

Self-turning GitHub

Speed of SELECT statements

1. Rational use of indexes
2. Isolate expensive parts of a debug query, such as whether a function call is executed in a row in the result set or a row in a full table
3. Minimize the number of full-table scans, especially in large tables
4. Use ANALYZE TABLE statements to update the statistics of a table on a regular basis so that the query optimizer can make more reasonable optimizations.
5. Learn 存储引擎 the various special debugs, indexes and configurations
6. Optimizing a single read-only query transaction
7. Avoid writing queries in a way that is difficult to understand because the query optimizer might use the same optimization decisions without sacrificing the readability of SQL
8. If a performance problem cannot be solved by a basic GUIDELINES resolution, use the EXPLAIN statement to view more specific information
9. Adjust the area of memory used by MySQL to use as a cache, for example (InnoDB buffer pool, MyISAM key cache, MySQL query cache)
10. Handle the lock problem with care

EXPLAIN Statement

Result Field

• id: The identifier of the Select
• select_type: Type of SELECT
  • SIMPLE: Simple SELECT (not using UNION or subqueries)
  • PRIMARY: Outermost SELECT
  • UNION: Second or later SELECT statement in a UNION
  • DEPENDENT UNION: Second or later SELECT statement in a UNION, dependent on outer query
  • UNION RESULTRESULT of a UNION
  • SUBQUERYFirst SELECT in subquery
  • DEPENDENT SUBQUERYFirst SELECT in subquery, dependent on outer query.
  • DERIVEDDerived table SELECT (subquery in FROM clause)
  • MATERIALIZEDMaterialized subquery
  • UNCACHEABLE SUBQUERYA subquery for which the result cannot is cached and must be re-evaluated for each row of the outer query
• table: The name of the table to which the row of output refers
  • <unionM,N> M,n is the ID of a subquery
  • <deriveN> n is the ID of the subquery
  • <subqueryN> n is the ID of the subquery
• partitions: the partition used
• type: JOIN TYPE
• possible_keys: An optional index
• key: The key or index used to query
• key_len: The Key_len column indicates the length of the key that is MySQL decided to use. The length is null if the key column says NULL. Note that the value of Key_len enables your to determine how many parts of a Multiple-part key MySQL actually uses
• ref: The ref column shows which columns or constants is compared to the index named in the key column to select rows from th E table.
• rows: The Rows column indicates the number of rows MySQL believes it must examine to execute the query
• filtered: The filtered column indicates an estimated percentage of table rows that would be filtered by the table condition. That's, rows shows the estimated number of rows examined and rowsxfiltered/100 shows the number of rows that would be Joined with previous tables.
• Extra: This column contains additional information on how to MySQL resolves the query. See Explain Extra Information

MySQL Index

1. Multi-column index, where the query criteria needs to be in the order of the columns and start column of the index, there can be no interval between

2. Using min () or max () query, the use of multi-column indexes also follows the principle of the leftmost column. That is, in the Where to first use the leftmost column in the multicolumn index, the query optimizer makes a scan of min () and Max () on a key, and then replaces the result of the constant. For example: select MIN (KEY_PART2), MAX (Key_part2) from Tbl_name WHERE key_part1=10;

   The
3. like can also use the index, as long as the argument is constant and does not start with a wildcard character
4. if you want to use an index in a comparison of non-pass columns, A data type of two columns is required, and CHAR (10) and varchar (10) are considered to be the same.
5. Multi-column index implementation: A Multiple-column index can considered a sorted array, the rows of which contain values that is CR Eated by concatenating the values of the indexed columns.
6. Multi-column index details see multiple index
7. details of where the MySQL is optimized see where optimization
The clustered index of the
8. innodb contains column data, prioritizing the combined index rather than the individual index of each column. The leaf node of the InnoDB level two index stores the PK, not the line number. The index of MyISAM is a non-clustered index, and the leaf node is a row pointer. See more
9. the effect of query conditions on index usage in the Where Condition:

Any index this does not span all and levels in the WHERE clause are not used to optimize the query. In other words, to is able to use an index, a prefix of the index must is used in every and group. The following WHERE clauses use indexes:

... WHERE index_part1=1 AND index_part2=2 AND other_column=3   /* index = 1 OR index = 2 */... WHERE index=1 OR A=10 AND index=2   /* optimized like "index_part1=‘hello‘" */... WHERE index_part1=‘hello‘ AND index_part3=5   /* Can use index on index1 but not on index2 or index3 */... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;These WHERE clauses do not use indexes:   /* index_part1 is not used */... WHERE index_part2=1 AND index_part3=2   /*  Index is not used in both parts of the WHERE clause  */... WHERE index=1 OR A=10   /* No index spans all rows  */... WHERE index_part1=1 OR index_part2=10

Buffer pool

Buffer pool is the area of memory in MySQL that is used to hold InnoDB tables and index data, which are divided into pages. Buffer pool uses the linked list as the implementation of pages. The exchange of pages uses the LRU algorithm, in large memory systems, you can divide the buffer pool into multiple buffer pool instances. The memory size configured in Innodb_buffer_pool_size is split by all buffer pool instances, and the multi-buffer pool instance is more suitable for applications where the INNODB buffer pool divides more than a few G space, Each instance can be given a size of 1G or more space.

Read-only transactions

If InnoDB can know in advance that a transaction is a read-only transaction, it can avoid using the transaction ID (TRX_ID). The Transaction ID only needs to be used in cases where the statement has an update or a lock exists in the Read statement. This is how InnoDB detects read-only transactions:

1. The transaction starts with the start TRANSACTION READ only statement, but this situation can still be updated on the session-specific temporary table because updates to these tables do not affect other transactions.
2. When the AUTOCOMMIT flag bit is open, all transactions are one but statements, then statements that are not updated will be wrapped as read-only transactions.
3. The transaction is not using the Read Only option, but there is no UPDATE statement or explicit lock statement, when the transaction is a read-only transaction, knowing that an explicit lock or UPDATE statement has been encountered. So, for query statements, you can put them in a read only transaction, or open the AUTOCOMMIT option before executing a SELECT statement, or simply do not put DML in the middle of dql.

How to implement the Otherinnodb row lock

For InnoDB tables, inserts, UPDATE, delete, and so on are all row-level locks, but these row locks are implemented by locking the index entries on the index, which means that the InnoDB can use row-level locks only if the data is retrieved by index criteria, otherwise InnoDB will use table-level locks. * When retrieving without indexing, InnoDB uses a table lock, not a row lock. For example:

create table tab_no_index(id int,name varchar(10)) engion=innodb; create table tab_with_index(id int,name varchar(10),key id_idx(id)) engion=innodb; select * from tab_no_index where id=1 for update;The statement is added with a table lock, and a select * from tab_with_index where id=1 for update; lock is added.

• Although you are accessing records that are not in the same row, there will still be a lock conflict if you use the same index key. For example, in the example table Tab_with_index, there are two records (1, ' 1 ') and (1, ' 4 '), then the SELECT * from Tab_with_index where id=1 and name= ' 1 ' for update; the two records are locked.

• When a table has multiple indexes, different transactions can use different indexes to lock different rows. In addition, InnoDB uses row locks to lock data, whether it is using a primary key index, a unique index, or a normal index.

InnoDB Clearance Lock (Next-key Lock)

When retrieving data using a range condition instead of an equality condition and requesting a shared or exclusive lock, InnoDB locks the index entry for the qualifying existing data record, and for records that do not exist in the condition range of the key value, called "Gap", InnoDB also locks the "gap". For example, there are only 101 records in the EMP table, where Empid is the index key, with a value of,......, 100,101, the statement select * from EMP where empid>100 for update, and not only the 101 record is locked, There is also a "gap" lock on Empid greater than 101 (these records do not exist).

Mysql Doc Read notes