1. The target* * * *

  1. What is optimization
  2. Learn how to optimize queries
  3. Master the method of optimizing database structure
  4. Learn how to optimize MySQL server

2. What is optimization?* * * *

  1. Reasonable arrangement of resources and adjustment of system parameters make MySQL run faster and save resources.
  2. Optimization is multifaceted, including queries, updates, servers, and so on.
  3. Principle: Reduce system bottlenecks, reduce resource occupancy, and increase system response speed.
  4. IO number
  5. CPU computing complexity

3. Database performance Parameters* * * *

1. Run the SHOW STATUS statement to check the performance parameters of the MySQL database • SHOW STATUS LIKE ‘value ‘2. Common parameters are as follows: • Slow_queries Number of slow queries • Com_(CRUD) operations • Uptime online time

4. Query optimization* * * *

SQLA: complex, IO 1000 times frequency 10 SQLB: IO 20 times 10000 times

Principle: Optimization requires optimized SQL****

Which Sql needs to be optimized more? Is the performance bottleneck of defining Sql more frequently executed an IO problem, or a CPU problem? Is it caused by data access or data manipulation? Where to start? The execution plan

4.1. EXPLAIN* * * *

MySQL > EXPLAIN SELECT * FROM tb_item; MySQL > EXPLAIN SELECT * FROM tb_item

4.1. The result shows that* * * *

4.1.1. id* * * *

SELECT identifier. This is the SELECT query serial number. It doesn’t matter.

4.1.2. select_type* * * *

Represents the type of the SELECT statement. The values are as follows: 1. SIMPLE indicates a SIMPLE query, excluding join query and sub-query. PRIMARY indicates the PRIMARY query, or the outermost query statement.3. UNION represents the second or subsequent query statement of the join query.4, DEPENDENT UNION The second or subsequent SELECT statement in the UNION, depending on the external query. 5, UNION RESULT Indicates the RESULT of the connection query. The first SELECT statement in the SUBQUERY.7, DEPENDENT SUBQUERY the first SELECT statement in a DEPENDENT SUBQUERY. 8, DERIVED SELECT(subquery FROM clause)

Holdings.table* * * *

Represents the table to be queried.

4.2.4. type (important)* * * *

Represents the join type of the table. The following join types are in order from best to worst: 1. The system table has only one row, which is a const column. 2, Const tables are optimized for PRIMARY KEY and UNIQUE indexes, because they match only one row.3, eq_ref mysql manual says :” For each combination of rows from the previous table, read a row from that table. This is probably the best join type, except for const. It is used when all parts of an index are joined and the index is UNIQUE or PRIMARY KEY”. Eq_ref can be used to compare indexed columns using =.Select * from ref where PRIMARY KEY is neither UNIQUE nor PRIMARY KEY. Ref can be used for indexed columns with the = or < or > operators.Ref_or_null This join type is the same as ref, but with the addition that MySQL can specifically search for rows containing NULL values. Optimization of this join type is often used in solving subqueries. These five cases are ideal for index use. Index_merge This join type represents the use of the index merge optimization method. In this case, the key column contains the list of indexes used, and key_len contains the longest key element of the index used. Unique_subquery This type replaces the ref IN subquery of the following form: Value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery is an index lookup function that can completely replace subqueries with higher efficiency. Index_subquery This join type is similar to unique_subquery. You can replace IN subqueries, but only for non-unique indexes IN subqueries of the following form: Value IN (SELECT key_column FROM single_table WHERE some_expr)This join type is the same as ALL, except that only the index tree is scanned. This is usually faster than ALL because index files are usually smaller than data files. 11. ALL Performs a full table scan for each combination of rows from the previous table. (Worst performance)

4.2.5 possible_keys* * * *

Indicates which index MySQL can use to find rows in this table. If the column is NULL, no index is in use, and you can create an index on the column to improve performance.

4.2.6 key* * * *

Shows the key (index) that MySQL actually decides to use. If no index is selected, the key is NULL. Indexes can be forced or ignored:

1.1.3. key_len* * * *

Displays the key length that MySQL decides to use. If the key is NULL, the length is NULL. Note: key_len determines the actual index length that MySQL will use. The prefix index

1.1.4. ref* * * *

Shows which column or constant is used with the key to select rows from the table.

1.1.5. rows* * * *

Shows the number of rows that MySQL thinks it must check when executing a query.

1.1.6. Extra* * * *

· Distinct: When MySQL finds the first matching row, it stops searching for more rows for the current combination of rows. · Not exists:MySQL can optimize a query for LEFT JOIN. If a row matching the LEFT JOIN standard is found, no more rows in this table are checked for the previous row combination. · range checked for each record (index map: #):MySQL did not find a good index to use, but found that if the column value from the previous table is known, it may be part of the index to use. · Using Filesort :MySQL needs an extra pass to figure out how to retrieve rows in sort order. · Using index: Retrieving column information in a table from reading the actual rows Using only the information in the index tree without further searching. · To solve a query,MySQL needs to create a temporary table to hold the results. · Using WHERE: The WHERE clause is used to restrict which row matches the next table or is sent to the customer. , Using sort_union (…). , Using union(…) , Using intersect(…) These functions explain how to merge index scans for the Index_Merge join type. · Using index for group-by: Similar to Using index,Using index for group-by indicates that MySQL has found an index, which can be used to query groups BY or DISTINCT query all columns without additional search hard disk access to the actual table.

4.2.7 Query optimization precautions* * * *

The condition of adding indexes: 1) often as query conditions (sort of) field (2) not often modified index can provide query speed, but it is not used with the index of field query will take effect, it is not effective in some cases, need to pay attention!

4.2.8. Don’t do calculations in the database* * * *

Do not use stored procedures, views, triggers, and Events. In the case of high concurrency, these features are likely to drag the database across, business logic in the service layer has better scalability, can easily achieve “more machines, more performance”

4.2.9. Different types cannot be compared* * * *

If different types are compared, the Mysql base will automatically convert data types, which greatly affects the efficiency

4.3.0 Do not use reverse query* * * *

Negative query conditions: NOT,! =, <>! <,! “>”, “NOT IN”, and “NOT LIKE” cause full table scan

4.3.1. Avoid queries that use the LIKE keyword* * * *

In a query using the LIKE keyword, if the first character of the matching string is “%”, the index does not work. The index takes effect only if ‘%’ is not in the first position.