1.Sql performance is not high

  • Too much query data (can be removed, filtering conditions as little as possible)
  • Too many tables associated, too many Joins. Scan all data in table B with each entry in table A. So try to filter it first.)
  • No use of indexes
  • Server tuning and parameter Settings (buffering, thread count, etc.)

2. Sequence of Sql compilation and execution

  • Write the order
select distinct  'fields'   
from 'table'   
join 'table' on 'conditions'   
where 'conditions'   
group by 'fields'   
having 'conditions'   
order by 'fields'   
limit 'conditions'
Copy the code
  • Execution order
Join type 'join 'table' where 'group' by 'having' condition 'select distinct 'fields'
order by 'fields'
limit 'conditions'
Copy the code
  • Sql image resolution

Index of 3.

3.1 What is an index

An Index is a data structure that helps MySQL retrieve data efficiently. In addition to the data, the database system maintains data structures that satisfy specific lookup algorithms, and that point to the data in a way that allows efficient lookup algorithms to be implemented on those data structures. This data structure is called an index. Indexes themselves are generally too large to be stored in memory, so they are often stored on disk as index files. We usually refer to the BTree index unless otherwise specified. The clustered index, secondary index, overwrite index, composite index, prefix index, and unique index are all BTree indexes by default. In addition to the BTree index, there is a hash index

3.2 Principles of Indexing

3.2.1 shows

  • Index (Index) is a data structure that helps MySQL get data efficiently. Different engines use different data structures.
  • MyISAM takesBTreeAnd what InnoDB does isB+TREE

    The database in our work is generally InnoDB, so we will focus on the explanationB+TREE.B+ tree index is a realization of B+ tree in the database, which is the most common and most frequently used index in the database. The B ina B+ tree stands for balance, not binary, because B+ trees evolved from the earliest balanced binary trees. Before we talk about B+ trees, we must first understand binary search trees, balanced binary trees (AVLTree) and balanced multipath lookup tree (B-Tree), B+ trees are gradually optimized from these trees.

3.2.2 Binary search tree

Binary trees have the following properties: the key value of the left subtree is smaller than that of the root, and the key value of the right subtree is larger than that of the root. The following figure shows a binary search tree

The binary search tree can be constructed arbitrarily. The same six numbers, 2,3,5,6,7,8, can also be constructed as follows:

3.2.3 Balanced AVL Tree

Balanced binary trees (AVL trees) also satisfy the maximum difference of height between two subtrees of any node is 1, provided that it conforms to the condition of binary search tree. In the following two pictures, on the left is an AVL tree whose height difference between the two subtrees of any node is <=1; The one on the right is not an AVL tree. The height of the left subtree of the root node is 3, while the height of the right subtree is 1.

Each of these four off-balance poses has its own definition:

LL: That’s right. After a node is inserted or deleted, the Left Child of the root node also has non-empty nodes, resulting in the height of the Left subtree of the root node being 2 higher than the height of the right subtree, and the AVL tree losing balance. RR: RightRight. After a node is inserted or deleted, the Right Child of the root node also has non-empty nodes, resulting in the height of the Right subtree of the root node being 2 higher than the height of the left subtree, and the AVL tree losing balance. LR: LeftRight, also known as left and right. After a node is inserted or deleted, the Left Child of the root node and the Right Child of the root node also have non-empty nodes, resulting in the height of the Left subtree of the root node being 2 higher than the height of the Right subtree, and the AVL tree losing balance. RL: RightLeft, also known as “RightLeft”. After a node is inserted or deleted, the Left Child of the Right Child of the root node also has non-empty nodes, resulting in the height of the Right subtree of the root node being 2 higher than the height of the Left subtree, and the AVL tree losing balance.

Once an AVL tree is out of balance, it can be rotated back into balance. The following four corresponding rotation methods are introduced respectively in the case of imbalance. Rotation of LL. If LL is out of balance, the AVL tree can be restored to balance by a single rotation. The steps are as follows:

  1. Make the left child of the root node the new root node.
  2. Make the right child of the new root the left child of the original root.
  3. The original root node is the right child of the new root node.

LL rotation is shown as follows:

RR rotation: When RR is out of balance, the rotation method is symmetric with LL rotation, and the steps are as follows:

  1. Take the right child of the root node as the new root node.
  2. Make the left child of the new root the right child of the original root.
  3. The left child of the new root.

RR rotation is shown as follows:

LR rotation: When LR is out of balance, two rotations are required as follows:

  1. RR rotation around the left child of the root node.
  2. LL rotation around the root node.

The rotation of LR is shown as follows:

Rotation of RL: Two rotations are also required when RL is out of balance. The rotation method is symmetric with LR rotation, and the steps are as follows:

  1. Rotate LL around the right child of the root node.
  2. Perform RR rotation around the root node.

The rotation of RL is shown as follows:

3.2.4 Balanced Multiple Lookup Tree (B-tree)

B-tree is a balanced search Tree designed for external storage devices such as disks. So before we talk about B-tree, we need to know about disks.

When the system reads data from disk to memory, the basic unit is disk block. The data in the same disk block is read at one time, rather than what is needed.

InnoDB storage engine has the concept of pages, the smallest unit of disk management. The default page size in the InnoDB storage engine is 16KB. The page size can be set to 4K, 8K, or 16K with the innodb_page_size parameter.

The storage space of a system disk block is usually not that large, so InnoDB uses several contiguous disk blocks to achieve a page size of 16KB each time it requests disk space. InnoDB reads data from disk to disk on a page basis. If each piece of data on a page helps locate data records, this will reduce disk I/O times and improve query efficiency.

An M-level B-tree has the following characteristics:

  1. Each node has a maximum of M children.
  2. Each node except the root node and leaf node has at least Ceil(m/2) children.
  3. If the root node is not a leaf node, there are at least 2 children
  4. All leaf nodes are in the same layer and contain no other keyword information
  5. Each non-terminal node contains n keywords (P0,P1… Pn, k1,… Kn)
  6. The number of keywords n is ceiL (m/2)-1 <= N <= M-1
  7. Ki (I = 1,… N) indicates the keyword in ascending order.
  8. Pi (I = 1,… N) is the pointer to the child root node. The keywords of all nodes of the subtree pointed to by P(i-1) are less than ki, but greater than K (i-1).

Each node in the B-tree can contain a large number of keyword information and branches according to the actual situation. The following figure shows a third-order B-tree:

Each node occupies the disk space of a disk block. A node has two keywords in ascending order and three Pointers to the child root node. The Pointers store the address of the disk block where the child node resides. The three scope fields divided into two keywords correspond to the scope fields of the data of the subtree pointed to by the three Pointers. For the root node, the keywords are 17 and 35. The data range of the subtree to which the P1 pointer points is less than 17, the data range of the subtree to which the P2 pointer points is 17-35, and the data range of the subtree to which the P3 pointer points is greater than 35.

Simulate the process of finding keyword 29:

  1. Locate disk block 1 based on the root node and read it into memory. [Disk I/O operation 1]
  2. Compare keyword 29 in the interval (17,35) to find pointer P2 to disk block 1.
  3. Locate disk block 3 according to P2 pointer and read into memory. [Disk I/O operation 2nd]
  4. Compare keyword 29 in interval (26,30) to find pointer P2 to disk block 3.
  5. Locate disk block 8 according to P2 pointer and read into memory. [Disk I/O operation 3]
  6. Find keyword 29 in the keyword list in disk block 8.

Analyzing the above procedure, we found that three disk I/O operations and three memory lookups were required. Since the keyword in memory is an ordered table structure, dichotomy lookup can be used to improve efficiency. Three disk I/O operations affect the efficiency of the entire B-tree search. Compared with AVLTree, B-tree reduces the number of nodes so that each disk I/O takes effect, improving the query efficiency.

3.2.5 B + Tree

B+Tree is an optimization on the basis of B-Tree, which makes it more suitable for the implementation of external storage index structure. InnoDB storage engine uses B+Tree to achieve its cable structure.

From the B-tree structure diagram in the previous section, you can see that each node contains not only the key value but also the data value. The storage space of each page is limited. If the data is large, the number of keys that can be stored on each node (that is, a page) is small. If the data is large, the b-tree depth is large, which increases the disk I/O times during query, affecting the query efficiency. In a B+Tree, all data record nodes are stored on the leaf nodes at the same layer according to the order of key values, instead of the non-leaf nodes only storing key values. This greatly increases the number of key values stored on each node and reduces the height of the B+Tree.

B+Tree is different from B-tree in the following aspects:

  1. Non-leaf nodes only store key-value information.
  2. There is a chain pointer between all leaf nodes.
  3. Data records are stored in leaf nodes.

The b-tree in the previous section is optimized. Since the non-leaf nodes of B+Tree only store key value information, assuming that each disk block can store four key values and pointer information, the structure of the B+Tree is as follows:

There are usually two head Pointers on a B+Tree, one to the root node and the other to the leaf node with the smallest keyword, and there is a chain-ring structure between all the leaf nodes (that is, data nodes). Therefore, there are two kinds of lookup operations on B+Tree: a range and paging lookup on the primary key, and a random lookup starting from the root node.

There are only 22 data records in the above example, so we can not see the advantages of B+Tree. Here is a calculation:

InnoDB storage engine page size is 16KB, the typical table primary key type is INT (4 bytes) or BIGINT (8 bytes), pointer type is also generally 4 or 8 bytes, That is, a page (a node in B+Tree) stores 16KB/(8B+8B)=1K keys (K = [10] ^3). This means that a depth of 3 B+Tree index can maintain 10^3 * 10^3 * 10^3 = 1 billion records.

In practice, each node may not be fully filled, so in the database, the height of B+Tree is generally between 2 and 4 layers. Mysql’s InnoDB storage engine is designed to have the root node resident in memory, meaning that finding a row record for a particular key requires at most one to three disk I/O operations.

The B+Tree indexes in the database can be divided into clustered index and secondary index. The above B+Tree example diagram is implemented in the database as a clustered index. The leaf nodes in the CLUSTERED index B+Tree store the row record data of the whole table. The difference between a secondary index and a clustered index is that the leaf node of a secondary index does not contain all the data of the row record, but rather stores the clustered index key, the primary key, of the corresponding row. When querying data through secondary indexes, the InnoDB storage engine traverses the secondary index to find the primary key, which then finds the full row record data in the clustered index

3.3 Advantages and disadvantages of indexes

  • Advantages: Improves data query efficiency and reduces DATABASE I/O cost Sorting data through indexes reduces data sorting cost and CPU consumption
  • Disadvantages: The index itself is also a table, which stores the primary key and index fields and points to the records of the entity table. Therefore, the index column also takes up space. Although the index greatly improves the speed of query, it also adversely affects the efficiency of add, delete, and modify operations. Therefore, we also need to update the index table information, which increases the workload of the database. As the business changes constantly, the indexes established before may not meet the query requirements, and we need to consume our time to update the index

3.4 Categories of indexes

3.4.1 Common Indexes

Is the most basic index, it has no restrictions.

CREATE indexIndex nameonTable name (column name)Copy the code

3.4.2 Unique Index

Similar to the normal index above, except that the value of the index column must be unique, but empty values are allowed. If it is a composite index, the combination of column values must be unique.

CREATE UNIQUE indexIndex nameonTable name (column name)Copy the code

3.4.3 Primary key Index

Is a special unique index. A table can have only one primary key and no null values are allowed. The primary key index is typically created while the table is being built. The primary key constrains the default index

3.4.4 Composite indexes

Indicates an index created on multiple fields. The index is used only when the first field is used in the query condition. Follow the leftmost prefix set when using composite indexes

CREATE indexIndex nameonTable name (column name, column name...)Copy the code

3.4.5 Full-text index

It is used primarily to find keywords in the text, not to compare them directly to values in the index. The fulltext index is very different from other indexes in that it is more like a search engine than a simple where statement parameter matching. The fulltext index is used with the match against operation, rather than the usual WHERE statement plus like. It can be used on create TABLE, ALTER TABLE, or CREATE Index, but only char, vARCHar, and TEXT columns can be used to create full-text indexes. It is worth mentioning that when there is a large amount of data, it is much faster to put the data into a table with no global index and then CREATE the fulltext index with CREATE Index than to CREATE the fulltext for a table and then write the data

CREATE TABLE `table` ( 
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`), FULLTEXT (content));Copy the code

3.5 Basic Syntax of indexes

  • create
ALTER mytable ADD [UNIQUE] INDEX [indexName] ONTable name (column name)Copy the code
  • delete
DROP INDEX [indexName] ONThe name of the table.Copy the code
  • To view
SHOW INDEX FROMThe name of the tableCopy the code
  • The alter command
‐‐ there are four ways to add indexes to a table:ALTER TABLE tbl_name ADD PRIMARY KEYColumn_list: This statement adds a primary key, which means that the index value must be unique and cannot beNULL.ALTER TABLE tbl_name ADD UNIQUEIndex_name (column_list): This statement creates an index whose value must be unique (exceptNULLOutside,NULLMay occur multiple times).ALTER TABLE tbl_name ADD INDEXIndex_name (column_list): Adds a common index. The index value can appear multiple times.ALTER TABLE tbl_name ADDFULLTEXT INDEX_NAME (column_list): This statement specifies the index as FULLTEXT, which is used for full-text indexing.Copy the code

3.6 Index Usage Scenarios

3.6.1 Suitable for indexes

  1. Fields that are frequently queried should be indexed
  2. A field associated with other tables in a multi-table query is indexed by a foreign key relationship
  3. Single column index/composite index choice, high concurrency tends to create composite index
  4. A field that is often used to sort a query
  5. Often used in queries to count or group fields

3.6.2 Indexes are not suitable

  1. Frequently updated fields: Each update affects the index tree
  2. Fields that are not used in a WHERE conditional query
  3. Too few table records
  4. Frequently added/deleted table: when a table is updated, the index must be updated as well
  5. Note: If there are a lot of duplicate records in a table, it doesn’t make much sense to index it

4. Performance analysis

4.1 the Query Optimizer

MySQL Optimizer is an Optimizer module that is specifically responsible for the optimization of SELECT statements. Its main function is to calculate and analyze various statistics collected in the system, and give the optimal execution plan for the Query requested by the client, that is, the optimal data retrieval method.

4.2 Common MySQL Bottlenecks

  1. CPU saturation :CPU saturation occurs when data is loaded into memory or read from disk
  2. IO bottleneck: Disk IO bottlenecks occur when far more data is loaded than memory
  3. Performance bottlenecks in server hardware

4.3 Execute plan Explai

4.3.1 the Explain overview

Use the Explain keyword to simulate the optimizer’s execution of SQL queries to see how MYSQL processes SQL statements. Execution plans can be used to analyze performance bottlenecks in query statements or table structures

4.3.2 the Explain role

  1. View the order in which the table is read
  2. View the operation type of the database read operation
  3. See which indexes are likely to be used
  4. See which indexes are actually used
  5. View references between tables
  6. See how many rows in the table are queried by the optimizer

4.3.3 grammar

  • grammar
explain sqlstatementsCopy the code

4.3.4 Description of each field

  • The preparatory work
create table t1(
id int primary key.name varchar(20), 
col1 varchar(20), 
col2 varchar(20), 
col3 varchar(20));create table t2( 
id int primary key.name varchar(20), 
col1 varchar(20), 
col2 varchar(20), 
col3 varchar(20));create table t3( 
id int primary key.name varchar(20),
col1 varchar(20),
col2 varchar(20), 
col3 varchar(20));insert into t1 values(1.'zs1'.'col1'.'col2'.'col3'); 
insert into t2 values(1.'zs2'.'col2'.'col2'.'col3'); 
insert into t3 values(1.'zs3'.'col3'.'col2'.'col3'); 
create index ind_t1_c1 on t1(col1);
create index ind_t2_c1 on t2(col1); 
create index ind_t3_c1 on t3(col1);
create index ind_t1_c12 on t1(col1,col2);
create index ind_t2_c12 on t2(col1,col2); 
create index ind_t3_c12 on t3(col1,col2);
Copy the code
  • After executing the EXPLAIN SQL statement:

4.3.4.1 id

  • The serial number of a SELECT query that contains a set of numbers indicating the order in which the SELECT clause or operation table is executed in the query

  • Three cases:

    1. The ids are the same, and the execution sequence is from top to bottom
    explain select t2.* from t1,t2,t3 where t1.id = t2.id and t1.id= t3.id and t1.name = 'zs';
Copy the code

    2. The ID value is different. The higher the ID value is, the higher the priority is

    explain select t2.* from t2 where id = (select id from t1 where id = (select t3.id from t3 where t3.name='zs3'));
Copy the code

    3. Some ids have the same or different values. If the ids are the same, the command is executed from top to bottom

    explain select t2.* from (select t3.id from t3 where t3.name='zs3') s1,t2 where s1.id = t2.id;
Copy the code

4.3.4.2 select_type

Query type, mainly used to distinguish

  • SIMPLE: A SIMPLE select query that does not contain subqueries or unions
  • PRIMARY: If the query contains a complex subquery, the outermost query is marked as PRIMARY
  • SUBQUERY: Contains subqueries in a SELECT or WHERE list
  • DERIVED: Containing subqueries in the FROM list is marked as DRIVED DERIVED, and MYSQL executes these subqueries recursively, putting the results into temporary tables
  • UNION: Marked as UNION if the second SELECT appears after UNION, and marked as: Derived if UNION is included in the subquery of the FROM clause
  • UNION RESULT: Select the RESULT from the UNION table
explain select col1,col2 from t1 union select col1,col2 from t2;
Copy the code

4.3.4.3 table

Show which table this row is associated with

4.3.4.4 type

Access types: all, index,range,ref,eq_ref, const,system,null System > const > eq_ref>ref >range > index > all

  • System: There is only one row in the table (system table), which is a special case of const type and is almost never present
  • Const: found in a single query,const is used to compare a primary key or unique index. Because it matches only one row, it is fast. If you place a primary key in a WHERE list,mysql will convert that query to a constant
explain select * from (select * from t1 where id=1) s1;
Copy the code
  • Eq_ref: unique index scan. For each index key, only one record in the table matches it. This is common in primary key or unique index scans
explain select * from t1,t2 where t1.id = t2.id;
Copy the code
  • Ref: A non-unique index scan that returns all rows matching a single value. Essentially an index access that returns all eligible rows, however it may return multiple eligible rows
explain select * from t1 where col1='zs1';
Copy the code
  • range : The key column shows which index is actually used, usually between,>,<,in, etc. This kind of index range scan is better than the full table scan, because it only scans in a certain range. There is no need to scan all indexes
explain select * from t1 where id between 1 and 10;
Copy the code
  • Index: scans the entire index table. The difference between index and all is that only the index tree is traversed. This is usually faster than all because index files are usually smaller than data files. Although index and ALL both read the full table, index reads from the index and all reads from the hard disk
explain select id from t1;
Copy the code
  • All: Full table scan A full table scan is performed to find matching rows
explain select * from t1;
Copy the code
  • Note: In development, we need to ensure that the query is at least range level, preferably ref. If all occurs on millions of data, index optimization is generally considered

4.3.4.5 possible_keys

An index that may be used in an SQL query, but may not actually be used during the query

4.3.4.6 key

The index that is actually used in the query. If null, it indicates that an overwrite index is used in the query and only appears in the key list

explain select t2.* from t1,t2,t3 where t1.col1 = ' ' and t1.id = t2.id and t1.id= t3.id;
Copy the code
explain select col1 from t1;
Copy the code

4.3.4.7 key_len

The number of bytes used in the index, which can be used to calculate the length of the index used in the query. The shorter the length is, without loss of precision, the better. Key_len is the maximum possible length of the index field, not the actual length, that is, key_len is calculated from the table definition

explain select * from t1 where col1='c1';
Copy the code
explain select * from t1 where col1='col1' and col2 = 'col2'; ‐‐ Note: to demonstrate this result, we removed the index above C1alter table t1 drop indexind_t1_c1; ‐‐ when the execution is complete, create the index againcreate index ind_t1_c1 on t1(col1);
Copy the code

4.3.4.8 ref

Shows which column of the index is used, if possible, as a constant. Which columns or constants are used to find values on index columns

explain select * from t1,t2 where t1.col1 = t2.col1 and t1.col2 = 'col2';
Copy the code

4.3.4.9 rows

Estimate the number of rows to be read (how many rows are read by the optimizer) for the required record based on table statistics and index selection, as few as possible

4.3.4.10 extra

Contains some other very important additional information

  • Using filesort: indicates that mysql uses an external index to sort data, instead of reading data in the order of the indexes in the table
explain select col1 from t1 where col1='col1' order by col3;
Copy the code
‐ the SQL statement above shows the use of filesort, but the SQL statement below does not show the use of filesortexplain select col1 from t1 where col1='col1' order by col2;
Copy the code
‐‐ how to optimize the first SQL statement?create index ind_t1_c13 on t1(col1,col3);
explain select col1 from t1 where col1='col1' order by col3;
Copy the code
  • Using temporary: A temporary table is used to hold intermediate results. Mysql uses temporary tables to sort query results. This is common in order by and group by
explain select col1 from t1 where col1>'col1' group by col2;
Copy the code
explain select col1 from t1 where col1 >'col1' group by col1,col2;
Copy the code
  • Using index :
  • In the query operation, an overwrite index is used (the queried column is the same as the index column) to avoid accessing the data row of the table, which is efficient
  • If using WHERE is also present, the index is used to perform a lookup of the index key value
  • If using WHERE is not present at the same time, the index is used to read data rather than perform lookup actions
  • MySql can use the index to return the columns in the select list without having to read the data file again based on the index
explain select col2 from t1 where col1='col1';
Copy the code
explain select col2 from t1;
Copy the code
  • Using WHERE: Indicates that where condition filtering is used
  • Using join buffer: indicates that the join buffer is used. Too many joins may occur
  • Impossible WHERE: The value in the where clause is always false and cannot be used to retrieve any data
explain select * from t1 where col1='zs' and col1='ls';
Copy the code
  • select tables optimized away :
  • In the absence of the group by clause, optimization of min/ Max operations based on indexes or count(*) operations for MyISAM storage engines does not have to wait until the execution phase to perform the calculation. Optimization is completed during the query execution plan generation phase
  • Distinct: Optimizes the DISTINCT operation to stop looking for the same value once the first matching data is found

5. Best practices

5.1 Single Table Query Optimization

Requirement: Query category_id (‘ category_id ‘= 1 and comments > 1) and Max views article_id.

  • Build table statements
CREATE TABLE IF NOT EXISTS `article` (
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`author_id` INT(10) UNSIGNED NOT NULL.`category_id` INT(10) UNSIGNED NOT NULL.`views` INT(10) UNSIGNED NOT NULL.`comments` INT(10) UNSIGNED NOT NULL.`title` VARBINARY(255) NOT NULL.`content` TEXT NOT NULL 
);
INSERT INTO `article`(`author_id`.`category_id`.`views`.`comments`.`title`.`content`) VALUES
(1.1.1.1.'1'.'1'),
(2.2.2.2.'2'.'2'),
(1.1.3.3.'3'.'3');
Copy the code
  • The execution plan
EXPLAIN SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
Copy the code
  • No index case
  • Optimization of a:
create index idx_article_ccv on article(category_id,comments,views);
Copy the code

Conclusion: Type becomes range, which is tolerable. Using filesort in Extra is still unacceptable. But we already have an index, so why not? This is because, in accordance with how BTree indexing works, you sort ‘category_id’ first, and if you approach the same category_id then sort ‘Comments’, and if you approach the same comments then sort’ views’. If comments > 1 is a range value,MySQL cannot use the index to retrieve the following views, that is, the index behind the range type query field is invalid.

  • Optimization of 2:
‐‐ first delete optimized indexDROP INDEX idx_article_ccv ONarticle; ‐‐ create an indexcreate index idx_article_cv on article(category_id,views);
Copy the code

5.2 Associated Query Optimization

5.2.1 sample

Requirement: Use the left outer join to query class and book

  • Build table statements
CREATE TABLE IF NOT EXISTS `class` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`));CREATE TABLE IF NOT EXISTS `book` (
`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`bookid`));INSERT INTO class(card) VALUES(FLOOR(1 + (RAND(*)20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND(*)20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND(*)20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND(*)20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND(*)20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND(*)20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND(*)20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND(*)20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND(*)20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND(*)20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND(*)20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND(*)20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND(*)20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND(*)20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND(*)20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND(*)20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND(*)20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND(*)20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND(*)20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND(*)20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND(*)20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND(*)20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND(*)20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND(*)20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND(*)20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND(*)20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND(*)20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND(*)20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND(*)20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND(*)20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND(*)20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND(*)20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND(*)20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND(*)20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND(*)20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND(*)20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND(*)20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND(*)20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND(*)20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND(*)20)));
Copy the code
  • The execution plan
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
Copy the code
  • To optimize the
ALTER TABLE `book` ADD INDEX Y ( `card`);
Copy the code

You can see in the second line that type is changed to ref and rows is optimized. This is determined by the left join property. The LEFT JOIN condition is used to determine how to search for rows from the right table. That is: the outer join creates the index on the opposite side

5.2.2 Optimization Suggestions

  • Ensure that the join field of the driven table is indexed.
  • When left JOIN, select the small table as the driver table and the large table as the driven table.
  • When an inner join is performed, mysql itself selects the tables of small result sets as driver tables for you.

5.3 Subquery Optimization

  • Try not to put subqueries in the driven table, it may not use the index.
select a.name ,bc.name from t_emp a left join 
    (select b.id , c.name from t_dept b
    inner join t_emp c on b.ceo = c.id)bc
    onId = a. table id = a. table id = a. table id = a. table id = a. table id The upper segment query can be directly used by two tablesleft joinTo optimize theselect a.name , c.name from t_emp a 
    left outer join t_dept b on a.deptid = b.id 
    left outer join t_emp c onIf a subquery must be used, set the subquery to the driver table, because the driver tabletypeIt must be all, and the result table returned by a subquery with no index must be allCopy the code

5.4 the order by optimization

5.4.1 Environment Preparation

  • Build table statements
CREATE TABLE tblA(
id int primary key not null auto_increment, 
age INT,
birth TIMESTAMP NOT NULL.name varchar(200));INSERT INTO tblA(age,birth,name) VALUES(22.NOW(),'abc');
INSERT INTO tblA(age,birth,name) VALUES(23.NOW(),'bcd'); 
INSERT INTO tblA(age,birth,name) VALUES(24.NOW(),'def');

CREATE INDEX idx_A_ageBirth ON tblA(age,birth,name);
Copy the code

5.4.2 instructions

MySQL supports two kinds of sorting methods, FileSort and Index, Index is efficient. It means that MySQL scans the index itself for sorting. The efficiency of FileSort method is low.

5.4.3ORDER BY will be sorted BY Index

  • The ORDER BY statement uses the left-most front row of the index
EXPLAIN SELECT * FROM tbla WHERE age > 1 ORDER BY age
Copy the code
  • Use the combination of the Where clause and the Order BY clause condition columns to satisfy the left-most front of the index
EXPLAIN SELECT * FROM tbla WHERE age = 1 ORDER BY birth
Copy the code
  • The order by index is invalidated if a range query is present in the WHERE clause
  • If the Order by composite condition is sorted by index, the Order by index will be invalid

5.5 GROUP BY optimization

5.5.1 instructions

Group by is essentially sorted and then grouped, following the best left prefix for the index

5.5.2 note

Where having is higher than where having can be qualified

5.6 limit optimization

5.6.1 instructions

Limit is often used for pagination, often with an order by clause, so Filesorts are often used, which can cause a lot of IO problems

5.6.2 optimization

  • Step 1: Use indexed columns or primary keys for the Order By operation
  • Step 2: Record the primary key returned last time and use annotation filtering in the next query (if the primary key is not consecutive and is a string, create a column record)

6. Summary

  • Full value matches my favorite
  • Best left prefix rule (If you index multiple columns, follow the left – most prefix rule. The query starts at the left-most front of the index and does not skip columns in the index.
  • Not doing anything on the index column (calculation, function, (automatic or manual) type conversion) will cause the index to fail and move to a full table scan
  • The storage engine cannot use the column to the right of the range condition in the index
  • Minimize select * by using overridden indexes (queries that access only the index (the index column is the same as the query column)
  • Mysql in use does not equal (! = or <>) will cause a full table scan
  • Is not NULL also cannot use an index, but is NULL can use an index
  • Like begins with a wildcard (‘% ABC… Mysql index failure becomes a full table scan operation
  • The index of a string without single quotation marks is invalid
  • Use or sparingly, as it will cause index failure when used for joins