This article has participated in the “Digitalstar Project” and won a creative gift package to challenge the creative incentive money.

When do you need to create an index?

  1. The primary key automatically creates a unique index;
  2. Fields that are frequently queried should be indexed;
  3. Query and other related fields, foreign key relationship index;
  4. Frequently updated fields are not created indexes;
  5. Indexes are not created for fields that are not used in the WHERE condition.
  6. Single key/composite index selection, usually in favor of creating composite indexes under high concurrency;
  7. The sorted field in the query, if the sorted field is accessed through the index will greatly improve the sorting speed;
  8. Statistics or grouping fields in the query;

When do you not need to create an index?

  1. Too few table records

  2. Fields that are often added or deleted

Although creating an index improves the query speed, it also slows down the speed of updating tables such as insert, update, and delete because MySQL not only saves data but also index files when updating tables.

  1. Table fields that are repetitive and evenly distributed, so only the most frequently queried and ordered data columns should be indexed. If a data column contains multiple duplicates, there is little practical value in indexing it.

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

MySQL Performance Analysis

Common performance bottlenecks in MySQL

  • CPU: CPU saturation occurs when data is loaded into memory or read from disk
  • IO: Disk I/O bottlenecks occur when far more data is loaded than there is memory
  • Server hardware performance bottlenecks: TOP, free, iostat, and vmstat to view the system performance status

MySQL Execution Plan

  • What is an execution plan?

    Use the EXPLAIN keyword to simulate the optimizer’s execution of SQL queries to see how MySQL is handling your SQL statements and to analyze performance bottlenecks in your queries or table structures.

  • What can executing a plan help us accomplish?

    A. Table read sequence B. Operation types of data read operations C. Which indexes can be used D. Which indexes are actually used e. References between tables f. How many rows per table are queried by the optimizer

Execution Plan CASE

Case presentation

Case analysis

Line 1 (execution order 4) : Mysql > select * from select_type; mysql > select * from select_type; mysql > select * from select_type [select d1.name…] [select d1.name…]

Line 2 (execute order 2) : id 3, part of the third select in the whole query, because the query is contained in from, so it is called. 【 SELECT id, name from where other_column = ”】

Select subquery (select id from t3) as select_type; select subquery (select id from t3) as select_type;

Select name,id from t2 as select_type; select * from t2 as select_type; select * from t2 as select_type; select * from t2 as select_type;

Row 5 (execution order 5) : represents the stage of reading rows from the temporary table for union.

of the table columns indicates the union operation with the first and fourth select results. [Two result union operations]
,>