MySQL optimization query

first, the concept of the index index: Dictionary-like directory, set up the index can speed up the data lookup, the data constraints;

Second, the index type:

Primary KEY index: Guaranteed data uniqueness, cannot repeat + cannot be empty

Normal index: Speed up data lookup

Unique index: Accelerated find + cannot be duplicated

Combined index (multiple columns combined into one index):

Third, the index principle of work 1, index:

If you want to find the data, not set the index, will be the front-to-back carpet search data, if set up an index, will create an additional table, in a format to save the column data location, easy to find;

2. Format of data of Index Table 2.1,

Hash format: The column data will be hashed into a hash value (number) corresponding to the storage address of the data;

Index Table:
Data hash value (number) storage location (number)

888 666

988 444

Features: The data in the index table is inconsistent with the data order in the database table; (hash index, suitable for single data lookup, not suitable for range checking
Find

2.2.

Btree format: Two fork tree, on the left side of the number smaller than their own, the right to put bigger than their own data

Features: Fast Range search Speed

Iv. Creating an index:

1. Normal index:

CREATE INDEX name on ... Table of: Column
CREATE INDEX Zhanggen on day61. ' User ' (email);

2. Unique index:

Create unique index index name on table name (column name)
Drop unique index index name on table name

3. Combined index (leftmost prefix match):

Create unique index index name on table name (column name, column name)
Drop unique index index name on table name
Create INDEX Ix_name_email on Userinfo3 (Name,email,)

4. Name index: Overwrite index, merge index

4.1 Overlay Index: Search directly in the index file, do not need to find the database file using the index method;

SELECT ID from day61. ' User ' WHERE id=9999;

4.2 Merging indexes: How to use multiple single-column indexes to find data together;

Select ID from day61. ' User ' WHERE id=9999 and email= "[email protected]";

4.3 the difference between a combined index and an index merge:

Composite index: Multiple columns made into an index

Index merging: Use only multiple single-column indexes when using indexes;

Composite Index Efficiency > Index Merging

Combined index
----(Name,email)

Select  from where name='Alex' and email='asdf'; Select  from where name='Alex';

Index Merge
----Name
----Email

SELECT * from day61. ' User ' WHERE id=1993 and email='[email protected]'; Select  from where name='id=1993'; Select  from where email='[email protected]';

respective application scenarios:

Two columns are often federated using a federated index
Index Merge: Single column data using index merge

v. Use index considerations

When you add an index to a database table, it does make the query take off, but the premise must be the correct query using the index, and if used in an incorrect way, even indexing will not work.
The index does not take effect even if the index is established:

1. Common error Usage Index

-Like '%xx '

Select  from where ' %CN ';

-Using functions

Select  from where ' Wupeiqi ';

-OR

Select* fromTb1whereNid =1or email ='[email protected]'; Special: When the OR condition has an unindexed columns failure, the following indexSelect* fromTb1whereNid =1or name ='Seven'; Select* fromTb1whereNid =1or email ='[email protected]'and name ='Alex'

-Inconsistent type    if the column is a string type, the incoming condition must be enclosed in quotation marks, otherwise ...

Select  from where 999;

- !=

   Select  from where ' Alex '     Special: If it is a primary key, it will still go index         Selectfromwhere123

->

Select  from where ' Alex '     Special: If the primary key or index is an integer type, then the index         Selectfromwhere123        Select  from where 123

-ORDER BY

  Select  from tb1 ORDER BY name Desc;    When sorting by index, the selected mappings are not indexed if they are not indexes:    If the primary key is sorted, then the index:        Select from tb1 order by nid desc;

-Combined index leftmost prefix

  If the combined index is: (name,email)    name and       email-- use index    name                 -- use index    Email                - Don't use index

2. Other matters needing attention

-Avoid using SELECT *-count (1) or count (column) instead of COUNT (*)-CREATE table when possible char instead of varchar-table field order fixed Length field precedence-composite index instead of multiple single-column indexes (when multiple conditional queries are used frequently)-use short cables as much as possible Citation-use Join to replace subquery (sub-queries)-Liangui when the condition type needs to be consistent-index hash value (less repetition) does not fit index, example: gender inappropriate

Six Execution Plan

Explain + query SQL: For evaluating SQL execution efficiency, SQL optimization based on reference information

EXPLAIN SELECT * from day61. ' User ';

ID Query Order ID as: MySQL> ExplainSelect* from(SelectNid,name fromTb1whereNid <Ten) asB; +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+            | ID | Select_type | Table | Type | Possible_keys | Key | Key_len |ref| Rows |            Extra | +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+            |1| PRIMARY | <derived2> | All | NULL | NULL | NULL | NULL |9|            NULL | |2| DERIVED | tb1 | Range | PRIMARY | PRIMARY |8| NULL |9| Usingwhere| +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+Special: If using union connection gas value may be null Select_type query type simple query PRIMARY Outermost query subquery map to subquery DERIVED Sub-query Union Union RES ULT using federated results ... table name you are accessing, type of access when querying, performance: all< index < range < Index_merge < Ref_or_null <ref< Eq_ref < system/ConstAll full table scan, find the data sheet from beginning to endSelect* fromtb1; Special: If there is a limit restriction, then no further downward scan will be foundSelect* fromTb1whereemail ='[email protected]'                                   Select* fromTb1whereemail ='[email protected]'Limit1;            Although both statements perform a full-table scan, and the second sentence uses limit, the scan is no longer resumed when one is found. Index full index Scan, look through the index from start to finishSelectNid fromtb1; Range Lookup for indexed columnsSelect* fromTb1whereName <'Alex'; Ps:between andinch> >= < <=Operation Note:! = and >symbol index_merge Merge index, search using multiple single-column indexesSelect* fromTb1whereName ='Alex'or Nidinch( One, A, -); REF finds one or more values based on an indexSelect* fromTb1whereName ='Seven'; Eq_ref using primary KEY or unique type when connectingSelectTb2.nid,tb1.name fromTb2 left join tb1 on tb2.nid =Tb1.nid;                            The const constant table has a maximum of one matching row, because only one row, in which the column values in this row can be considered constants by the remainder of the optimizer, the const table is fast because they are read only once. SelectNid fromTb1whereNid =2 ; System Systems table has only one row (=system tables).                            This is a special case of the const join type. Select* from(SelectNid fromTb1whereNid =1) asA; Possible_keys may use the index key for real use in Key_len MySQL using the index byte length rows MySQL estimates the number of rows to read in order to find the desired rows 
    ------just a pre-valuation extra The column contains the details of the MySQL resolution query "using index" This value indicates that MySQL will use the overwrite index to avoid accessing the table.        Do not confuse the overlay index with the index access type. The Usingwhere"This means that the MySQL server will filter after the storage engine retrieves rows, and many of the where conditions involve the columns in the index, and when (and if) it reads the index, it can be tested by the storage engine, so not all queries with a WHERE clause will show" Usingwhere”。 Sometimes the Usingwhere"is a hint: A query can benefit from a different index.        "Using temporary" this means that MySQL uses a temporary table when sorting the results of the query. "Using Filesort" This means that MySQL uses an external index to sort the results instead of reading rows from the table in the index order.        MySQL has two kinds of file sorting algorithms, both of which can be done in memory or on disk, explain will not tell you which sort of file MySQL will use, and will not tell you whether the sort will be done in memory or on disk. The Rangechecked  foreach record (index map:n) "This means that there is no good index, the new index will be recalculated on each line of the join, N is the bitmap displayed in the Possible_keys column, and is redundant. Detail

Seven, slow log query

1. Slow log format:

-Execution Time > 10
-Missing Index
-Log file path

2. Configure the slow log on the MySQL command line:

Show variables like '%query% '
Set global variable name = value

3. Configure slow log in MySQL configuration file:

Mysqld--defaults-file= ' E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\my-default.ini '

My.conf content:
Slow_query_log = On
Slow_query_log_file = d:/....

4. Note: After you modify the configuration file, you need to restart the service

Viii. Limit Paging * *

Limit paging is a matter of concern, whether or not it has an index

Jump between page:

Calculates the previous page based on the current page's maximum ID and minimum ID, and the next page shows the required data entry;

Specify jump between pages:

Select all the IDs for the data you need on this page to jump pages. In the Select

1, prev: Get the current page maximum ID (assuming the current page: 10 data required, the current page min id=20 max id=30)

Step 1:where The condition to filter out the data less than the minimum ID on this page, id=20----the first data row;

Step 2: Find the data line before this page, reverse, take 10 of them, is the page to display the required data;

SELECT * from day61. ' User ' WHERE ID < The ORDER by ID DESC LIMIT 10;

2, Next: Get the current page maximum ID (assuming the current page: 10 data required, the current page min id=20 max id=30)

Step 1:where The condition to filter out the data larger than the maximum ID on this page, id=30----The data row at the end;

Step 2: Find the data line after this page, take 10 of them, is the next page to display the required data;

SELECT * from day61. ' User ' WHERE ID >30 LIMIT 10;

3. Page 3, 4, 5, 6, 7, 8 ... next page (jump to the specified page, assuming that the current page is displayed: 10 data required, current page min id=20 max id=30)

Let's say we jump from page 3rd to page 8th.

Step 1: Get the data row that shows the maximum ID on this page, limit (8-3) *10 data row

Step 2: Get 3-8 rows between all data rows limit (10) is the data required to display the jump page;

Step 3, select ID gets the data rows that are required to display the page, all IDs, in the select Jump page ID;

SELECT * from Userinfo3 where ID in (

Select ID from (SELECT ID from Userinfo3 where ID > max_id limit) as N order BY n.id DESC LIMIT 10

Get all IDs of jump page data rows
)

MySQL optimization query