1. Introduction to MySQL logical architecture

1.2. The layering idea of mysql

  1. MySQL is a bit different from other databases in that its architecture can be used and used well in many different scenarios. This is mainly reflected in the architecture of the storage engine.
  2. Plug-in storage engine architecture separates query processing from other system tasks and data storage and extraction. In this architecture, you can select an appropriate storage engine based on service requirements.

1.3. Mysql four-tier architecture

Connection layer: The top layer is a number of client and connection services, including local SOCK communication and most TCP/IP-like communication based on client/server tools. Mainly complete some similar connection processing, authorization and authentication, and related security schemes. On this layer, the concept of thread pools is introduced to provide threads for clients that are securely accessed through authentication. SSL – based secure links can also be implemented at this layer. The server also validates that it has operational permissions for each client that is securely connected. Service layer: The second layer architecture mainly completes most of the core service functions, such as SQL interface, and completes the cache query, SQL analysis and optimization, and the execution of some built-in functions. All cross-storage engine functionality is also implemented in this layer, such as procedures, functions, and so on. In this layer, the server parses the query and creates the corresponding internal parse tree, and performs the corresponding optimization such as determining the order of the query table, whether to utilize the index, and finally generates the corresponding execution operations. In the case of a SELECT statement, the server also queries the internal cache. If the cache space is large enough, it can greatly improve system performance in an environment where a large number of read operations are handled.

Management Serveices & Utilities System management and control tools
SQL > SQL > Interface SQL interface. Accepts the user’s SQL command and returns the result that the user needs to query. For example, select from calls SQL Interface
Parser Meaning in Chinese The parser. SQL commands passed to the parser are validated and parsed by the parser
4. Optimizer Query optimizer. SQL statements use the query optimizer to optimize the query before it is queried, such as when there is a WHERE condition, and the optimizer decides whether to cast or filter first.
Cache and Buffer Query cache. If the query cache has a matching query result, the query statement can directly fetch data from the query cache. This caching mechanism consists of a series of small caches. Such as table cache, record cache, key cache, permission cache and so on

Engine layer: Storage engine layer. Storage engine is responsible for storage and extraction of data in MySQL. Server communicates with storage engine through APl. Different storage engines have different functions, so you can select them according to your actual needs. MyISAM and InnoDB are introduced later

Storage layer: The data storage layer, which stores data on file systems running on raw devices and interacts with storage engines.

1.4. MySql Components

  • Connectors: Refers to interaction with SQL in different languages
  • Management Serveices & Utilities: System Management and control tools
  • Connection Pool: a connection pool
    • Manage buffering user connections, thread processing, and other caching requirements. Listen for various requests to MySQL Server, receive connection requests, forward all connection requests to the thread management module.
    • Each client request that connects to MySQL Server is assigned (or created) a connection thread to serve separately. The connection thread is responsible for the communication between MySQL Server and the client. Accept client command request, transfer Server result information and so on. The thread management module is responsible for managing and maintaining these connected threads. Including thread creation, thread cache and so on
  • SQL Interface: indicates the SQL Interface. Accepts the user’s SQL command and returns the result that the user needs to query. For example, select from calls SQL Interface
  • Parser: the parser
    • SQL commands passed to the parser are validated and parsed by the parser. The parser, implemented by Lex and YACC, is a long script.
    • In MySQL, all commands sent from the Client to the Server are called queries. In MySQL Server, when the thread receives a Query from the Client, The Query is passed directly to the specialized module responsible for classifying the various Queries and then forwarding them to the corresponding processing module.
    • The main functions of the parser:
      • The SQL statement is analyzed semantics and syntax, decomposed into data structures, and then classified according to different operation types, and then made targeted forward to the subsequent steps, the transfer and processing of SQL statements is based on this structure
      • If an error is encountered in the decomposition composition, then the SQL statement is unreasonable
  • Optimizer: query optimizer
    • SQL statements use the query optimizer to optimize the query before it is queried. Is to optimize the SQL statement sent by the client, according to the query statement requested by the client, and some statistical information in the database, on the basis of a series of algorithms for analysis, get an optimal strategy, tell the following program how to obtain the results of the query statement
    • He used the phrase”Select-project-join“Policy for query
      • Select uid,name from user where gender = 1;
      • The SELECT query selects based on the WHERE statement, rather than having all the tables queried first and then gender filtering
      • The select query projects attributes based on UID and name, rather than fetching all attributes and filtering them later
      • Join the two query criteria to generate the final query result
  • The Cache and Buffer: Query cache
    • Its main function is to cache the return result set of the Select query request submitted by the client to MySQL, and make a corresponding hash value of the query. MySQL automatically invalidates the Cache of this Query after any data changes to the base table of the data retrieved from it. In applications with very high read/write ratios, Query Cache improves performance significantly. Of course, it is also very high memory consumption.
    • If the query cache has a matching query result, the query statement can directly fetch data from the query cache. This caching mechanism consists of a series of small caches. Such as table cache, record cache, key cache, permission cache and so on
  • Storage engine interface
    • Storage engine interface module can be said to be the most distinctive point of MySQL database. At present, among all kinds of database products, MySQL is basically the only one that can realize the plug-in management of its underlying data storage engine. This module is really just an abstract class, but its success in highly abstracting various data processing is what makes MySQL’s pluggable storage engine so special today
    • As you can see from the figure above, the most important thing that sets MySQL apart from other databases is its plug-in table storage engine. MySQL plug-in storage engine architecture to provide a series of standard management and service support, these standards has nothing to do with the storage engine itself, each database system itself may be required, such as SQL analyzer and optimizer, and the storage engine is the realization of the underlying physical structure, each storage engine developers can develop according to their wishes.
    • Note: Storage engines are table based, not database based.

1.5. SQL general query process

Mysql query flow:

1, mysql client establishes connection with mysql server through protocol, sends query statement, first checks query cache, if hit, directly returns the result, otherwise carries on statement parsing, that is, before parsing query, The server first accesses the Query cache, which stores SELECT statements and the corresponding query result set. If a query result is already in the cache, the server will no longer parse, optimize, and execute the query. It simply returns the cached results to the user, which greatly improves the performance of the system.

2, syntax parser and preprocessing: first, mysql will parse SQL statements through keywords, and generate a corresponding “parse tree” mysql parser will use mysql syntax rules to verify and parse queries; The preprocessor further checks that the parsed number is valid based on some mysql rules

Query optimizer When the parse tree is considered legal, it is converted into an execution plan by the optimizer. A query can be executed in many ways, all returning the same result. The optimizer’s job is to find the best execution plan.

4, Then, mysql defaults to using the BTree index, and a general direction is: no matter what you do with SQL, at least for now, mysql only uses one index in a table

1.6. MySQL Storage Engine

1.6.1 Viewing storage engines supported by mysql

show engines;
Copy the code

1.6.2 Viewing the default storage engine of mysql

show variables like '%storage_engine%';
Copy the code

1.6.3 Comparison between MyISAM engine and InnoDb engine

Compare the item MyISAM InnoDB
The main foreign key Does not support support
The transaction Does not support support
Line table lock Table locks, which lock the entire table even when operating on a single record, are not suitable for high-concurrency operations Row lock: Locks only one row during operation without affecting other rows. Suitable for high-concurrency operations
The cache Only indexes are cached, not real data Caching not only indexes but also real data requires a lot of memory, and memory size has a decisive impact on performance
Table space small big
concerns performance The transaction
The default installation Y Y

2. What is the index

2.1. Index Definition

  • An Index is a data structure that helps MySQL obtain data efficiently. You get the essence of an index: an index is a data structure
  • You can think of it simply as “sorted fast lookup data structure”, i.e. index = sort + lookup
  • Generally, indexes themselves occupy a large amount of memory space and cannot be stored in memory. Therefore, indexes are usually stored as files on hard disks
  • When we refer to an index, if not specified, we refer to an index organized in a B-tree (multi-way search tree, not necessarily binary tree) structure.
  • Clustered indexes, secondary indexes, overwrite indexes, compound indexes, prefix indexes, and unique indexes all use B+ tree indexes by default. Of course, in addition to B+ tree type indexes, there are hash indexes, etc.

2.2 index principle

Think of an index as “a quickly ordered lookup data structure”

  • In addition to the data, the database system maintains data structures that satisfy specific lookup algorithms, and that reference (point to) the data in a way that makes it possible to implement advanced lookup algorithms on these data structures. This data structure is called an index.
  • Here is an example of a possible index:
    • On the left is the data table, with seven records in two columns. The leftmost hexadecimal number is the physical address of the data record
    • To speed up col2 lookup, you can maintain a binary lookup tree as shown on the right, with each node containing an index key value and a pointer corresponding

2.3. Advantages and Disadvantages of indexing

Advantages of indexes

  1. Similar to bibliographic index of university library, improve data retrieval efficiency and reduce IO cost of database
  2. Sorting data through index columns reduces the cost of sorting data and CPU consumption

Disadvantages of indexes

  1. The index is actually a table that holds the primary key and index fields and points to the records of the entity table, so the index columns also take up space
  2. While indexes greatly speed up queries, they also slow down the speed of updating tables for INSERTS, updates, and deletes. MySQL does not only save the index file when updating the table, but also changes the index file when updating the index column
  3. Indexes are only one factor in improving efficiency. If you have MySQL with large tables, you may need to spend time on building good indexes or optimizing queries

2.4, MySQL index classification

Reference data: www.cnblogs.com/luyucheng/p…

Normal index: is the most basic index, it has no restrictions, that is, an index only contains a single column, a table can have multiple single-column index; It is recommended that there be no more than five indexes in a table. The composite index unique index is preferred. The index column must have unique values, but empty values are allowed. If it is a composite index, the combination of column values must have a unique primary key index: a special unique index that allows only one primary key for a table and no empty values. Composite index: indicates an index created on multiple fields. The index is used only when the first field is used in the query condition. When using composite indexes, follow the left-most prefix set full-text indexes: they are used primarily to find keywords in text, not to compare them directly with 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

2.5, MySQL index syntax

2.5.1,SQL statement to build index

  • Create indexes
    • For CHAR and VARCHAR, length can be smaller than the actual field length.
    • If it is BLOB and TEXT, length must be specified.
      • CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));
      • ALTER mytable ADD [UNIQUE] INDEX [indexName] ON(columnname(length));
  • Remove the index
    • DROP INDEX [indexName] ON mytable;
  • View index(\GRepresents the landscape to be queriedTable vertical output, easy to read)
    • SHOW INDEX FROM table_name\G

2.5.2,There are four ways to add an index to a table:

1. This statement adds a primary key, which means the index value must be unique and cannot be NULL.

ALTER TABLE tbl_name ADD PRIMARY KEY(column_list)
Copy the code

2. This statement adds a primary key, which means the index value must be unique and cannot be NULL.

ALTER TABLE tbl_name ADD PRIMARYThe KEY (column_list) :Copy the code

This statement creates an index that must be unique (NULL may occur multiple times).

ALTER TABLE tbl_name ADD INDEX index_name(column_list)
Copy the code

4, this statement specifies the index as FULLTEXT, which is used for full-text indexing.

ALTER TABLE tbl_name ADD FULLTEXT index_name(column_list)
Copy the code

View index structure

SHOW INDEX FROM `sys_dict_item`;
Copy the code

2.6, MySQL index structure

2.6.1 Btree index

[Introduction to initialization]

  1. A B-tree, the light blue block we call a disk block, and you can see that each disk block contains several data items (dark blue) and Pointers (yellow)
  2. For example, disk block 1 contains item 17 and 35, and Pointers P1, P2, and P3
  3. P1 indicates a disk block smaller than 17, P2 indicates a disk block between 17 and 35, and P3 indicates a disk block larger than 35
  4. Real data exists in both leaf and non-leaf nodes

[Search process]

  1. If we want to find item 29, we first load disk block 1 from disk into memory. At this time, we use binary search in memory to determine that 29 is between 17 and 35. The P2 pointer of disk block 1 is locked, and the memory time is negligible because it is very short
  2. Load disk block 3 from disk to memory using the disk address of the P2 pointer of disk block 1. When the second I/O occurs, the P2 pointer of disk block 3 is locked between 26 and 30
  3. A pointer is used to load disk block 8 into the memory, and a third I/O occurs. At the same time, binary search is performed in the memory to find 29, and the query ends.

2.6.2. B+tree index

  • B+Tree 与 BTreeThe difference between:
    • Tree keys (data items) and records are placed together; The non-leaf nodes of the B+ tree have only keywords and indexes pointing to the next node, and the records are only placed in the leaf node.
  • B+Tree 与 BTreeSearch procedure of
    • In B tree, the record search time is faster the closer to the root node, and the existence of the record can be determined as long as the key word is found. However, the search time of each record in B+ tree is basically the same, and it needs to go from the root node to the leaf node, and then compare keywords in the leaf node.
    • From this point of view, the performance of the B tree seems to be better than that of the B+ tree, but in practice, the performance of the B+ tree is better. Because the non-leaf nodes of a B+ tree do not store actual data, each node can contain more elements and the tree height is smaller than that of a B+ tree, which reduces the number of disk accesses.
    • Although B+ trees require more comparisons to find a record than B trees, a single disk access is equivalent to hundreds of memory comparisons, so in practice B+ trees may perform better, and the leaf nodes of B+ trees are linked together using Pointers for sequential traversal (range search). This is why many databases and file systems use B+ trees
  • Performance improvement:
    • The reality is that a 3-tier B+ tree can represent millions of data. If millions of data lookups take only three IO, the performance improvement is huge. If there is no index and each data item takes one IO, then the total number of IO is millions, which is obviously very, very expensive

Consider: why B+ trees are better than B- trees for file and database indexes in practical operating systems?

1. The disk read and write cost of the B+ tree is lower: the internal nodes of the B+ tree do not have Pointers to the specific information of the keyword. So the internal nodes are smaller than the b-tree. If all the keywords of the same internal node are stored in the same disk block, then the disk block can contain more keywords. Read into memory at a time to find more keywords. 2. The query efficiency of B+ tree is more stable because the non-endpoint is not the node that ultimately points to the content of the file, but only the index of the keyword in the leaf node. So any keyword lookup must take a path from root to leaf. The length of all keyword query paths is the same, resulting in the same query efficiency of each data.

2.7. When to build an index

  1. The primary key automatically creates a unique index
  2. Fields that are frequently the condition of a query should be indexed
  3. The foreign key relationship is used to index the fields associated with other tables in the query
  4. Frequently updated fields are not suitable for creating indexes
  5. Indexes are not created for fields that are not needed in the Where condition
  6. Selection of single/composite indexes, Who? (Tendency to create composite indexes under high concurrency)
  7. The sorted fields in a query that can be accessed through an index to make sorting faster
  8. Statistics or group fields in the query

2.8. When do not create an index

  1. Too few table records
  2. A watch that is often added, deleted, or modified
  3. Table fields with repetitive and evenly distributed data, so you should index only frequently queried and frequently ordered data columns. Note that if a data column contains a lot of duplicate content, there is not much practical value in indexing it

Case Study:

  1. If A table has 100,000 rows, A field A has only T and F values, and the probability of distribution of each value is about 50%, then the index of such A table A field will not improve the query speed of the database.
  2. Index selectivity refers to the ratio of the number of different values in the index column to the number of records in the table. If there are 2000 records in a table with 1980 different values in the index column, the index selectivity is 1980/2000=0.99.
  3. The closer the selectivity of an index is to 1, the more efficient the index will be.

3. Performance analysis

3.1 Overview of performance optimization

  • MySQL Query Optimizer
    • MySQL has the optimizer module specially responsible for the optimization of SELECT statements. Its main function is to provide the optimal execution plan for the Query requested by the client by calculating and analyzing the statistics collected in the system. (MySQL considers the optimal data retrieval method, but not necessarily the DBA considers it optimal, which takes the most time.)
    • When a client requests a Query from MySQL, the command parser module classifies the request to SELECT and forwards it to the MySQL Query Optimizer, which first optimizes the entire Query. Dispose of some constant expression budgets and convert them directly to constant values. And simplify and transform the Query conditions in Query, such as removing some useless or obvious conditions, structure adjustment, etc. The Hint information in the Query is then parsed (if any) to see if displaying the Hint information fully determines the execution plan for that Query. If there is no Hint or Hint information is not sufficient to fully determine the execution plan, the statistics of the object involved are read, the corresponding computational analysis is written according to Query, and the final execution plan is obtained
  • MySQL common Bottlenecks
    • CPU bottleneck: CPU saturation occurs when data is loaded into memory or read from disk
    • IO bottleneck: Disk I/O bottlenecks occur when much more data is loaded than there is memory
    • Server hardware performance bottlenecks: TOP, free, iostat, and vmstat to view the system performance status

4, Explain

4.1 what is Explain? Explain means to view the execution plan

  1. Use the EXPLAIN keyword to simulate the optimizer’s execution of SQL statements to see how MySQL handles your SQL statements. Analyze performance bottlenecks in your query or structure
  2. Website address: dev.mysql.com/doc/refman/…

4.2. What can you do?

  1. Table read order (ID field)
  2. Operation type of data read operation (select_Type field)
  3. Which indexes can be used (possible_keys field)
  4. Which indexes are actually used (keys field)
  5. References between tables (REF fields)
  6. How many rows per table are queried by the optimizer (rows fields)

4.3. How to play?

Explain + SQL statements

mysql> explain select * from tbl_emp;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | tbl_emp | ALL  | NULL          | NULL | NULL    | NULL |    8 | NULL  |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
Copy the code

4.4, Explain

id

Id Serial number of a SELECT query, containing a set of numbers indicating the order in which the SELECT clause or operation table is executed in the query

There are three possible values of id:

1. The ids are the same and the execution sequence is from top to bottomCopy the code

2. The ID is different. If it is a sub-query, the id sequence number will increaseCopy the code

3. If the ids are the same, they can be considered as a group and executed from top to bottom. In all groups, the greater the ID value, the higher the priority and the earlier the execution. Derivative = DERIVEDCopy the code

select_type

Select_type: specifies the query type. It is used to distinguish the common query, joint query, and subquery

  1. SIMPLE: A SIMPLE select query that does not contain subqueries or unions
  2. PRIMARY: The outermost query is marked as PRIMARY if it contains any complex subparts
  3. SUBQUERY: Contains subqueries in a SELECT or WHERE list
  4. DERIVED: Subqueries contained in the FROM list are labeled as DERIVED, and MySQL performs these subqueries recursively, putting the results in temporary tables
  5. UNION: If the second SELECT appears after the UNION, it is marked as UNION; If UNION is included in the subquery of the FROM clause, the outer layer SELECT will be marked: DERIVED
  6. UNION RESULT: SELECT the RESULT from the UNION table

UNION and UNION RESULT are examples

explain
    -> select * from tbl_emp e left join tbl_dept d on e.deptId = d.id
    -> union
    -> select * from tbl_emp e right join tbl_dept d on e.deptId = d.id;
+----+--------------+------------+------+---------------+------------+---------+-----------+------+---------------------- ------------------------------+
| id | select_type  | table      | type | possible_keys | key        | key_len | ref       | rows | Extra                                              |
+----+--------------+------------+------+---------------+------------+---------+-----------+------+---------------------- ------------------------------+
|  1 | PRIMARY      | e          | ALL  | NULL          | NULL       | NULL    | NULL      |    8 | NULL                                               |
|  1 | PRIMARY      | d          | ALL  | PRIMARY       | NULL       | NULL    | NULL      |    5 | Using where; Using join buffer (Block Nested Loop) |
|  2 | UNION        | d          | ALL  | NULL          | NULL       | NULL    | NULL      |    5 | NULL                                               |
|  2 | UNION        | e          | ref  | fk_dept_Id    | fk_dept_Id | 5       | db01.d.id |    1 | NULL                                               |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL       | NULL    | NULL      | NULL | Using temporary                                    |
+----+--------------+------------+------+---------------+------------+---------+-----------+------+---------------------- ------------------------------+
5 rows in set (0.00 sec)

Copy the code

table

Table: Shows which table this row is about

type

Type: Access type arrangement to show which type is used in the query

  1. Type shows the type of access, which is a more important indicator. The result values, from best to worst, are: system>const>eq_ref>ref>fultext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL
  2. System >const>eq_ref>ref>range>index>ALL. In general, ensure that the query reaches at least range and preferably ref

From best to worst: system>const>eq_ref>ref>range>index>ALL

1.systemThe: table has only one row (equal to the system table). This is a special case of the const type and can be ignored

2,constConst is used to compare primary key or unique indexes. Because it only matches one row, it’s fast. By placing the primary key in the WHERE list, MySQL can convert the query to a constant3,eq_ref:Unique indexFor each index key, there is only one record in the table that matches it. This is common for primary key or unique index scans4,ref: non-unique index scan that returns all rows matching a single value. It is essentially an index access that returns all rows that match a single value. However, it may find multiple rows that match the criteria, so it should be a mixture of lookup and scan

Range: retrieves only rows in a given range, using an index to select rows. A range scan is better than a full table scan because it only needs to start at one point of the index and end at another, rather than scanning all the indexes

6, index: Full index Scan, index is different from ALL, and only the index tree is traversed. This is usually faster than ALL because index files are usually smaller than data files. (all and index are both read from the full table, but index is read from the index, and all is read from the hard disk database file)

7, all: FullTable Scan will run all tables to find matching rows.

possible_keys

  1. Displays one or more indexes that may be applied to this table
  2. If an index exists on a field involved in a query, the index is listed but not necessarily used by the query

key

  1. The actual used index, if null, is not used
  2. If an overridden index is used in the query, it only appears in the key list

key_len

  1. Represents the number of bytes used in the index, which is used to calculate the length of the index used in the query. With no loss of accuracy, the shorter the length, the better
  2. The value key_len displays is the maximum possible length of the index, not the actual length, that is, key_len is calculated from the table definition, not retrieved from the table

ref

  1. Show which column of the index is used, preferably a constant if possible. Which columns or constants are used to find values on index columns
  2. Col1 in t1 matches COL1 in T2. Col2 in T1 matches a constant ‘ac’.

rows

Based on table statistics and index selection, the number of rows that need to be read to find the desired record \ is estimated roughly

Extra

Extra: Contains important additional information that is not suitable for display in other columns

  • Using filesort:
    • MySQL can not use index to complete a sort operation called “file sort”
    • Note mysql uses an external index sort for data, rather than reading data in the order of the indexes in the table
    • Using Filesort is bad, need to optimize SQL asap
    • The first query in the example uses only COL1 and col3, and the original index is useless, so external file sorting is done
    • The second query in the example uses col1, col2, and col3, and the original index comes in handy without the need for file sorting
  • Creating a temporary table
    • Temporary tables are used to hold intermediate results. MySQL uses temporary tables when sorting query results. Common in sort order by and group by queries
    • Using temporary is super bad and needs to optimize SQL immediately
    • The first query in the example uses only COL1, and the original index is useless, so temporary tables are created for grouping
    • The second query in the example uses col1, col2, and the original index is used instead of creating a temporary table
  • Using index:

    • Coveing Index (Coveing Index) is used in the select operation, avoiding access to rows of the table.

    • Using WHERE indicates that 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

    • Covering Index (Covering Index), also said for Index coverage

      • MySQL can use the index to return columns from the select list. MySQL does not need to read the data file again based on the index. In other words, the query column is overwritten by the index.
      • An index is an efficient way to find rows, but a database can also use an index to find data for a column, so it doesn’t have to read the entire row. After all, index leaf nodes store the data they index; When you can get the data you want by reading the index, you don’t need to read rows. An index that contains (or overwrites) the data that meets the query result is called an overwritten index.
      • Note: If you want to use an overwrite index, be sure to select only the required columns from the select list, not select *. If you index all columns together, the index file will be too large and the query performance will deteriorate.
  • Using WHERE: Indicates that where filtering is used
  • Using join buffer: Indicates that the join buffer is used
  • Impossible WHERE: The value of the where clause is always false and cannot be used to retrieve any tuples
  • select tables optimized away: Optimizes MIN/MAX operations based on indexes or for MyISAM storage engines without the GROUPBY clauseCOUNT(*)Operations do not have to wait until the execution phase to perform the calculation, the query execution plan generation phase is completed optimization.
  • Distinct: Optimizes distinct to stop looking for the same value once the first matching tuple is found

4.5 Explain examples

Line 1 (execution order 4) : Select * from select_type; select * from select_type; select * from select_type; select * from select_type; Select with ID 3. [select d1.name…] Line 2 (execution order 2) : id 3 is part of the third select in the entire query. Derived because the query is contained in from. [select id, name from T1 where other_column= “”] select subquery from select_type (select subquery from t1 where select_type = subquery); Select * from t3 as select_type (select id from t2) select * from t3 as select_type (select name, id from t2) select * from t3 as select_type (select name, id from t2) select * from t3 as select_type (select name, id from t2) select * from t3 as select_type (select name, id from t2) Represents the stage of reading rows from the union’s temporary table, and the

of the table column indicates the union operation with the results of the first and fourth select. [Two results perform uinion operation]
,>

5. Index optimization

5.1. Single table index optimization

Single table index optimization analysis

5.1.1. Create a table

  • Build table SQL
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 VARCHAR(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
  • Test data in the table
mysql> SELECT * FROM article;
+----+-----------+-------------+-------+----------+-------+---------+
| id | author_id | category_id | views | comments | title | content |
+----+-----------+-------------+-------+----------+-------+---------+
|  1 |         1 |           1 |     1 |        1 | 1     | 1       |
|  2 |         2 |           2 |     2 |        2 | 2     | 2       |
|  3 |         1 |           1 |     3 |        3 | 3     | 3       |
+----+-----------+-------------+-------+----------+-------+---------+
3 rows in set (0.00 sec)

Copy the code

5.1.2. Query cases

  • Query category_id (category_id = 1 and Comments > 1) and category_id (category_id = 1 and category_id = 1)
mysql> SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-----------+
| id | author_id |
+----+-----------+
|  3 |         1 |
+----+-----------+
1 row in set (0.00 sec)

Copy the code
  • There is only one primary key index in the Article table at this point
mysql> SHOW INDEX FROM article;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+---- --------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+---- --------+---------+---------------+
| article |          0 | PRIMARY  |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+---- --------+---------+---------------+
1 row in set (0.00 sec)

Copy the code
  • Analyze the execution efficiency of SQL statements using Explain

EXPLAIN SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

mysql> EXPLAIN SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | article | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where; Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

Copy the code

1. What is the worst case? 2, Extra also appears Using filesort, which is also the worst case. Optimization is a must.

5.1.3 Start optimization: Create an index

  • SQL command to create an index
ALTER TABLE article ADD INDEX idx_article_ccv('category_id'.'comments'.'views'); 
create index idx_article_ccv on article(category_id, comments, views);

Copy the code
  • Build a joint index on the category_id, Comments, and Views columns
mysql> create index idx_article_ccv on article(category_id, comments, views);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM article;
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+---- --+------------+---------+---------------+
| Table   | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+---- --+------------+---------+---------------+
| article |          0 | PRIMARY         |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_ccv |            1 | category_id | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_ccv |            2 | comments    | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_ccv |            3 | views       | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+---- --+------------+---------+---------------+
4 rows in set (0.00 sec)

Copy the code
  • Execute the query again: Type becomes range, which is tolerable. Using filesort in Extra is still unacceptable.
mysql> EXPLAIN SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+----------------------- ----------------+
| id | select_type | table   | type  | possible_keys   | key             | key_len | ref  | rows | Extra                                 |
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+----------------------- ----------------+
|  1 | SIMPLE      | article | range | idx_article_ccv | idx_article_ccv | 8       | NULL |    1 | Using index condition; Using filesort |
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+----------------------- ----------------+
1 row in set (0.00 sec)

Copy the code
  • Analysis:
    • But we already have an index, so why not?
    • This is because from how the B+Tree index 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’.
    • When the Comments field is in the middle of the union index, becausecomments>1The condition is a range value (so-called range value), MySQL cannot use the index to retrieve the following part of the views, i.eThe index following the range query field is invalid.
  • Query the conditions incomments > 1Instead ofcomments = 1And found thatUse filesortMagically disappear, as can be seen from this:Indexes after the range cause index loss
mysql> EXPLAIN SELECT id, author_id FROM article WHERE category_id = 1 AND comments = 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------+-----------------+-----------------+---------+-------------+------+-------------+
| id | select_type | table   | type | possible_keys   | key             | key_len | ref         | rows | Extra       |
+----+-------------+---------+------+-----------------+-----------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | article | ref  | idx_article_ccv | idx_article_ccv | 8       | const,const |    1 | Using where |
+----+-------------+---------+------+-----------------+-----------------+---------+-------------+------+-------------+
1 row in set (0.00 sec)

Copy the code

5.1.3 Drop indexes

  • SQL instructions to drop indexes
DROP INDEX idx_article_ccv ON article;

Copy the code
  • Delete the idx_article_CCV index you just created
mysql> DROP INDEX idx_article_ccv ON article;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM article;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+---- --------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+---- --------+---------+---------------+
| article |          0 | PRIMARY  |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+---- --------+---------+---------------+
1 row in set (0.00 sec)

Copy the code

5.1.4 Create index again

  • SQL instructions for creating indexes
ALTER TABLE article ADD INDEX idx_article_ccv('category_id'.'views'); 
create index idx_article_ccv on article(category_id, views);

Copy the code
  • Due to range (comments > 1), and this time we need to discard the COMMENTS column directly and start looking at the data we need from the joint index of category_id and views before extracting from itcomments > 1(I think it should be)
mysql> create index idx_article_ccv on article(category_id, views);
Query OK, 0 rows affected (0.30 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM article;
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+---- --+------------+---------+---------------+
| Table   | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+---- --+------------+---------+---------------+
| article |          0 | PRIMARY         |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_ccv |            1 | category_id | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_ccv |            2 | views       | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+---- --+------------+---------+---------------+
3 rows in set (0.00 sec)

Copy the code
  • Execute the query again: you can see that type has changed to ref, and the Using filesort in Extra has disappeared
ysql> EXPLAIN SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------+-----------------+-----------------+---------+-------+------+-------------+
| id | select_type | table   | type | possible_keys   | key             | key_len | ref   | rows | Extra       |
+----+-------------+---------+------+-----------------+-----------------+---------+-------+------+-------------+
|  1 | SIMPLE      | article | ref  | idx_article_ccv | idx_article_ccv | 4       | const |    2 | Using where |
+----+-------------+---------+------+-----------------+-----------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

Copy the code
  • To not affect future tests, drop the idx_article_CCV index of this table
mysql> DROP INDEX idx_article_ccv ON article;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM article;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+---- --------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+---- --------+---------+---------------+
| article |          0 | PRIMARY  |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+---- --------+---------+---------------+
1 row in set (0.01 sec)

Copy the code

5.2. Index optimization of two tables

Two table index optimization analysis: primary foreign key

5.2.1 create a table

  • Build table SQL
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 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
  • Test data in the class table
mysql> select * from class;
+----+------+
| id | card |
+----+------+
|  1 |   12 |
|  2 |   13 |
|  3 |   12 |
|  4 |   17 |
|  5 |   11 |
|  6 |    3 |
|  7 |    1 |
|  8 |   16 |
|  9 |   17 |
| 10 |   16 |
| 11 |    9 |
| 12 |   17 |
| 13 |   18 |
| 14 |   16 |
| 15 |    7 |
| 16 |    8 |
| 17 |   19 |
| 18 |    9 |
| 19 |    6 |
| 20 |    5 |
| 21 |    6 |
+----+------+
21 rows in set (0.00 sec)

Copy the code
  • Test data in the book table
mysql> select * from book;
+--------+------+
| bookid | card |
+--------+------+
|      1 |   16 |
|      2 |    1 |
|      3 |   17 |
|      4 |    3 |
|      5 |   20 |
|      6 |   12 |
|      7 |   18 |
|      8 |   13 |
|      9 |   13 |
|     10 |    4 |
|     11 |    1 |
|     12 |   13 |
|     13 |   20 |
|     14 |   20 |
|     15 |    1 |
|     16 |    2 |
|     17 |    9 |
|     18 |   16 |
|     19 |   14 |
|     20 |    2 |
+--------+------+
20 rows in set (0.00 sec)

Copy the code

5.2.1. Query cases

  • Realize the connection of two tables, the connection condition is class.card = book.card
mysql> SELECT * FROM class LEFT JOIN book ON class.card = book.card;
+----+------+--------+------+
| id | card | bookid | card |
+----+------+--------+------+
|  1 |   12 |      6 |   12 |
|  2 |   13 |      8 |   13 |
|  2 |   13 |      9 |   13 |
|  2 |   13 |     12 |   13 |
|  3 |   12 |      6 |   12 |
|  4 |   17 |      3 |   17 |
|  5 |   11 |   NULL | NULL |
|  6 |    3 |      4 |    3 |
|  7 |    1 |      2 |    1 |
|  7 |    1 |     11 |    1 |
|  7 |    1 |     15 |    1 |
|  8 |   16 |      1 |   16 |
|  8 |   16 |     18 |   16 |
|  9 |   17 |      3 |   17 |
| 10 |   16 |      1 |   16 |
| 10 |   16 |     18 |   16 |
| 11 |    9 |     17 |    9 |
| 12 |   17 |      3 |   17 |
| 13 |   18 |      7 |   18 |
| 14 |   16 |      1 |   16 |
| 14 |   16 |     18 |   16 |
| 15 |    7 |   NULL | NULL |
| 16 |    8 |   NULL | NULL |
| 17 |   19 |   NULL | NULL |
| 18 |    9 |     17 |    9 |
| 19 |    6 |   NULL | NULL |
| 20 |    5 |   NULL | NULL |
| 21 |    6 |   NULL | NULL |
+----+------+--------+------+
28 rows in set (0.00 sec)

Copy the code
  • Using explain to analyze the performance of SQL statements, you can see that the driver table is the left class table
mysql> EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------- -------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                              |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------- -------------+
|  1 | SIMPLE      | class | ALL  | NULL          | NULL | NULL    | NULL |   21 | NULL                                               |
|  1 | SIMPLE      | book  | ALL  | NULL          | NULL | NULL    | NULL |   20 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------- -------------+
2 rows in set (0.00 sec)

Copy the code

SQL > select * from class; SQL > select * from class; SQL > select * from class; SQL > select * from class

5.2.2 Adding an index: Add an index to the right table

  • SQL instructions to add indexes
ALTER TABLE 'book' ADD INDEX Y ('card');

Copy the code
  • Add index to card field of book
mysql> ALTER TABLE book ADD INDEX Y (card);
Query OK, 0 rows affected (0.30 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM book;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------ ------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------ ------+---------+---------------+
| book  |          0 | PRIMARY  |            1 | bookid      | A         |          20 |     NULL | NULL   |      | BTREE      |         |               |
| book  |          1 | Y        |            1 | card        | A         |          20 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------ ------+---------+---------------+
2 rows in set (0.00 sec)

Copy the code
  • Test result: You can see that in the second row, type is changed to ref and rows is optimized.
mysql> EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
+----+-------------+-------+------+---------------+------+---------+-----------------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref             | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-----------------+------+-------------+
|  1 | SIMPLE      | class | ALL  | NULL          | NULL | NULL    | NULL            |   21 | NULL        |
|  1 | SIMPLE      | book  | ref  | Y             | Y    | 4       | db01.class.card |    1 | Using index |
+----+-------------+-------+------+---------------+------+---------+-----------------+------+-------------+
2 rows in set (0.00 sec)

Copy the code

Analysis:

  • 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.
  • If the left table is connected to the right table, the data in the left table must be queried in the right table, and the index must be created in the right table

5.2.3 Adding an index: Add an index to the right table

  • Delete the index in the previous book table
DROP INDEX Y ON book;
Copy the code
  • Create an index on the card field of the class table
ALTER TABLE class ADD INDEX X(card);

Copy the code
  • Perform the left connection again to cool off
mysql> EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------- --------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                              |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------- --------------+
|  1 | SIMPLE      | class | index | NULL          | X    | 4       | NULL |   21 | Using index                                        |
|  1 | SIMPLE      | book  | ALL   | NULL          | NULL | NULL    | NULL |   20 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------------------------------- --------------+
2 rows in set (0.00 sec)

Copy the code
  • Don’t worry, let’s do the right join: you can see that in the second line, type is changed to ref and rows is optimized.
mysql> EXPLAIN SELECT * FROM class RIGHT JOIN book ON class.card = book.card;
+----+-------------+-------+------+---------------+------+---------+----------------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref            | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+----------------+------+-------------+
|  1 | SIMPLE      | book  | ALL  | NULL          | NULL | NULL    | NULL           |   20 | NULL        |
|  1 | SIMPLE      | class | ref  | X             | X    | 4       | db01.book.card |    1 | Using index |
+----+-------------+-------+------+---------------+------+---------+----------------+------+-------------+
2 rows in set (0.00 sec)

Copy the code

Analysis:

  • This is because the RIGHT JOIN condition is used to determine how to search for rows from the left table, which must be on the RIGHT, so the left is our key point and must be indexed.
  • Class RIGHT JOIN book: the data in book must be in the result set. We need to search the class table with the data in book, so we need to create the index in the class table
  • To not affect future tests, drop the idx_article_CCV index of this table
mysql> DROP INDEX X ON class;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM class;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------ ------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------ ------+---------+---------------+
| class |          0 | PRIMARY  |            1 | id          | A         |          21 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------ ------+---------+---------------+
1 row in set (0.00 sec)

Copy the code

5.3. Index optimization of three tables

Three table index optimization analysis

5.3.1 Create a table

  • Build table SQL
CREATE TABLE IF NOT EXISTS phone(
	phoneid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	card INT(10) UNSIGNED NOT NULL.PRIMARY KEY(phoneid)
)ENGINE=INNODB;

INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));

Copy the code
  • Test data in the phone table
mysql> select * from phone;
+---------+------+
| phoneid | card |
+---------+------+
|       1 |    7 |
|       2 |    7 |
|       3 |   13 |
|       4 |    6 |
|       5 |    8 |
|       6 |    4 |
|       7 |   16 |
|       8 |    4 |
|       9 |   15 |
|      10 |    1 |
|      11 |   20 |
|      12 |   18 |
|      13 |    9 |
|      14 |    9 |
|      15 |   20 |
|      16 |   11 |
|      17 |   15 |
|      18 |    3 |
|      19 |    8 |
|      20 |   10 |
+---------+------+
20 rows in set (0.00 sec)

Copy the code

5.3.2. Query cases

  • Implement three table join query:
mysql> SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card;
+----+------+--------+------+---------+------+
| id | card | bookid | card | phoneid | card |
+----+------+--------+------+---------+------+
|  2 |   13 |      8 |   13 |       3 |   13 |
|  2 |   13 |      9 |   13 |       3 |   13 |
|  2 |   13 |     12 |   13 |       3 |   13 |
|  8 |   16 |      1 |   16 |       7 |   16 |
| 10 |   16 |      1 |   16 |       7 |   16 |
| 14 |   16 |      1 |   16 |       7 |   16 |
|  8 |   16 |     18 |   16 |       7 |   16 |
| 10 |   16 |     18 |   16 |       7 |   16 |
| 14 |   16 |     18 |   16 |       7 |   16 |
|  7 |    1 |      2 |    1 |      10 |    1 |
|  7 |    1 |     11 |    1 |      10 |    1 |
|  7 |    1 |     15 |    1 |      10 |    1 |
| 13 |   18 |      7 |   18 |      12 |   18 |
| 11 |    9 |     17 |    9 |      13 |    9 |
| 18 |    9 |     17 |    9 |      13 |    9 |
| 11 |    9 |     17 |    9 |      14 |    9 |
| 18 |    9 |     17 |    9 |      14 |    9 |
|  6 |    3 |      4 |    3 |      18 |    3 |
|  4 |   17 |      3 |   17 |    NULL | NULL |
|  9 |   17 |      3 |   17 |    NULL | NULL |
| 12 |   17 |      3 |   17 |    NULL | NULL |
|  1 |   12 |      6 |   12 |    NULL | NULL |
|  3 |   12 |      6 |   12 |    NULL | NULL |
|  5 |   11 |   NULL | NULL |    NULL | NULL |
| 15 |    7 |   NULL | NULL |    NULL | NULL |
| 16 |    8 |   NULL | NULL |    NULL | NULL |
| 17 |   19 |   NULL | NULL |    NULL | NULL |
| 19 |    6 |   NULL | NULL |    NULL | NULL |
| 20 |    5 |   NULL | NULL |    NULL | NULL |
| 21 |    6 |   NULL | NULL |    NULL | NULL |
+----+------+--------+------+---------+------+
30 rows in set (0.00 sec)

Copy the code
  • Analyze SQL instructions with Explain:
mysql> EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card;
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------- -------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                              |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------- -------------+
|  1 | SIMPLE      | class | ALL  | NULL          | NULL | NULL    | NULL |   21 | NULL                                               |
|  1 | SIMPLE      | book  | ALL  | NULL          | NULL | NULL    | NULL |   20 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | phone | ALL  | NULL          | NULL | NULL    | NULL |   20 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------- -------------+
3 rows in set (0.00 sec)

Copy the code

Conclusion:

  • ○ Type indicates All, and rows indicates the total number of rows in the table. This indicates that the class, book, and phone tables are All queried
  • ○ Using join buffer in Extra indicates that join buffer is used during connection

5.3.3 Creating an index

  • SQL statement to create an index
ALTER TABLE book ADD INDEX Y (card);
ALTER TABLE phone ADD INDEX Z (card);
Copy the code
  • LEFT JOIN always indexes the fields of the right table
mysql> ALTER TABLE book ADD INDEX Y (card);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM book;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------ ------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------ ------+---------+---------------+
| book  |          0 | PRIMARY  |            1 | bookid      | A         |          20 |     NULL | NULL   |      | BTREE      |         |               |
| book  |          1 | Y        |            1 | card        | A         |          20 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------ ------+---------+---------------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE phone ADD INDEX Z (card);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM phone;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------ ------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------ ------+---------+---------------+
| phone |          0 | PRIMARY  |            1 | phoneid     | A         |          20 |     NULL | NULL   |      | BTREE      |         |               |
| phone |          1 | Z        |            1 | card        | A         |          20 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------ ------+---------+---------------+
2 rows in set (0.00 sec)

Copy the code
  • Execute the query: the type of the last 2 rows is ref, and the total rows optimization is good, the effect is good. So indexes are best placed in fields that need to be queried frequently.
mysql> EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card=book.card LEFT JOIN phone ON book.card = phone.card;
+----+-------------+-------+------+---------------+------+---------+-----------------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref             | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-----------------+------+-------------+
|  1 | SIMPLE      | class | ALL  | NULL          | NULL | NULL    | NULL            |   21 | NULL        |
|  1 | SIMPLE      | book  | ref  | Y             | Y    | 4       | db01.class.card |    1 | Using index |
|  1 | SIMPLE      | phone | ref  | Z             | Z    | 4       | db01.book.card  |    1 | Using index |
+----+-------------+-------+------+---------------+------+---------+-----------------+------+-------------+
3 rows in set (0.00 sec)

Copy the code

Conclusions of Join statement optimization:

Think of the left Join as a two-level nested for loop

  1. Minimize the total number of NestedLoop loops in Join statements;
  2. Always use small result sets to drive large result sets (build indexes in large result sets, traverse the entire table in small result sets);
  3. The inner loop of NestedLoop is optimized first.
  4. Ensure that Join condition fields on the driven table in the Join statement are indexed;
  5. If you cannot ensure that the Join condition fields of the driven table are indexed and the memory resources are sufficient, do not be too skimpy with the JoinBuffer Settings.

I understand it

  1. Using small tables to drive large tables is equivalent to less for loops on the outer layer and more for loops on the inner layer
  2. Then we index the large table, which makes the inner for loop much more efficient
  3. In summary, small tables are used to drive large tables, and indexes are built in large tables

subsequent

Due to space limitation, please skip to:Juejin. Cn/post / 705223…