Why do you do performance analysis

Do you have a situation like this .

In front of a person you haven't written much about 、 Complex business , You've been thinking about it for a long time , Finally, I started typing the first piece of code .

The process of writing is vague , Sometimes I can make myself dizzy .

But you finally finished it .

But click on a run , Finished , Yes bug.

What do I do ?

debug There are many ways , Console printing is a kind of .

Information printed through the console , We can modify the code based on the feedback , Until the code works .

It's the same with building an index .

Last post 《 Talking about sql Indexes 》, I said that the difficulty of index is to make the most appropriate index for a specific table .

Because it doesn't just depend on what's in your watch , It depends more on your business , What keywords do you often search for in your business .

We can write code every day , Index can't be built every day .

So most of the time , Because I'm not familiar , The indexes we build in the beginning are often not the best , Only adjust the index according to the feedback , In order to make a most suitable index for this table .

What I want to share today is how to understand this feedback , That is how to do performance analysis .

How to do performance analysis

Use EXPLAIN keyword !

Use EXPLAIN Keywords can tell MySQL How to deal with you SQL Of the statement , Analyze the performance bottleneck of your query statement or table structure .

but EXPLAIN It doesn't give us direct feedback on the quality of the index we built .

The usage is like this , We built the index , Take the most common sentences in business SQL Statement to EXPLAIN once , If the feedback works well , Then the index is the most suitable for this table , On the contrary, it needs to be improved .

Or change the index , If you don't change SQL.

EXPLAIN How to play

sentence

explain To view sql sentence ( Horizontal table ) or explain To view sql sentence \G( Vertical table )

A concrete example

Each field explains

As can be seen from the picture above ,explain The information that comes out has the following fields :

id select_type table type possible_keys key key_len ref rows Extra

Take a close look at , Is it a bit like a payslip ?

The meaning of each field is like this :

  • id: View the read order of the table .

     The example above only queries one table , But if it's a multi table joint query , Then there are :
    id Same words , The order of execution is from top to bottom ;
    id Different words ,id The higher the value, the higher the priority ;
  • select_type: Show differential union queries 、 Subquery 、 General query, etc .

     Here are the possible values , And the corresponding information :
    SIMPLE -- ordinary select Inquire about , Does not contain subqueries or union;
    PRIMARY -- If the query contains any complex sub parts , The outermost query is marked PRIMARY;
    SUBQUERY -- stay select or where Include subqueries ;
    DERIVED -- stay from The list contains subqueries marked as DERIVED( derivative ),MySQL These subqueries will be executed recursively , Put the results on the provisional list ;
    UNION -- If the second select Appear in the union after , Is marked as union; if union Included in from Clause , Outer layer select Will be marked as :DERIVED;
    UNION RESULT -- from union Table to get the result select;
  • table: Display table name .

  • type: Shows what type of query is used .

     Here are the possible values , And the corresponding information :
    system -- There is only one line in the table ( It's equal to the system table ), This is a const Special case of type , Not usually , Can ignore not remember .
    const -- Indicates that it is found through index once ,const For comparison primary key( Primary key ) perhaps unique( only ) Indexes . Because only one line of data is matched , So soon , If you put the primary key in where In the list ,mysql You can convert the query to a constant .
    eq_ref -- Unique scan index , For each index key , In the table ** It just happened to be ** There's a record that matches . Commonly used for primary key or unique index scanning .
    ref -- Non unique scan index , Returns all rows that match a single value . above `eq_ref` The multivalued case of . Such as where age=1,age=1 There's just one line of data , It shows eq_ref,age=1 There are multiple lines of data , It shows ref.
    range -- Retrieve only rows in the given range , Use an index to select rows . If type The value of the column is `range`,key Column will show which index is used . It's usually in where In the sentence between、<、>、in And so on . This kind of range scan query is better than full table scan .
    index -- Full Index Scan( Scan all indexes ),index And ALL The difference for index Type only traverses the index tree . Read the whole watch , however index Read from index ,all It's read from the hard disk , And index files are usually smaller than data files .
    all -- Full Table Scan( Scan the whole table ), Traverse the entire table to find the matching rows , The index is totally useless .
    From the best to the worst is :system>const>eq_ref>ref>range>index>ALL
    details :
    1. type yes ALL, When the data reaches more than one million, it must be optimized .
    2. Generally speaking , If you want to optimize the query to at least range Level , It's best to achieve ref.
  • possible_keys and key

    possible_key: Show the indexes that may be applied to this table , One or more . Indexes that could theoretically be used , But it doesn't have to be actually used by the query .
    key: Actual index used , If null, No index is used .
    There are four situations in these two columns :
    1. possible_key Valuable ,key Valuable : normal , Sometimes the former has multiple values, but only one of the latter is normal .
    2. possible_key Valuable ,key No value :** Index failed , There's a problem **.
    3. possible_key No value ,key Valuable : Conditions of the query ( Such as where) No index or no condition query is used , But the columns of the query (select Later fields ) Just in sequence 、 The quantity is consistent with the index .
    4. possible_key No value ,key No value : normal , You didn't build the index .
  • key_len: Represents the number of bytes used in the index .

     This column can be used to calculate 【 The length of the index used in the query 】, With the same query results , The smaller the value, the better .
    key_len The displayed value is the maximum possible length of the index field , Instead of the actual length , It is calculated by table definition , It's not a search in a table .
    Suppose you build a composite index (col1,col2), If 【 adopt col1 Conditions of the query 】 and 【 adopt col1 and col2 Conditions of the query 】 The result is the same , So the former is better , Because you only need to use one field ,key_len The value of will be smaller , As mentioned above, it is determined by the length of the table definition key_len Value .
  • ref: Show key The value of the index reference in the column .

     There are two possible values , And the corresponding information :
    Library name . Table name . Field name -- Indicates which field of which database, which table the index refers to ;
    const -- The value representing the index reference is a constant , It's usually where id=1 That's how it comes out ;
  • rows: According to table statistics and index selection , Roughly estimate the number of rows that need to be read to find the record to be found .

  • Extra: Very important additional information .

     Here are the possible values , And the corresponding information :
    Using filesort -- explain mysql Completely or partially not sorted according to the index you built , It needs to be optimized .MySQL A sort operation that cannot be done with an index is called “ File sorting ”;
    Using temporary-- Temporary tables are used to save intermediate results ,mysql Use temporary tables when sorting query results . It also needs to be optimized , Because the creation and deletion of the temporary table are cost-effective , Common in order by and group by;
    Using index -- It means corresponding select The override index is used in the operation , Avoid accessing the data rows of the table , Good efficiency . If it appears at the same time Using where, Indicates that the index is used to perform a query of the key value of the index , If not , Indicates that the index is used to read data rather than perform lookup actions ;
    Using where -- Used where Filter ;
    Using join buffer -- Connection caching is used , If this field always appears , You can increase this value appropriately in the configuration file ;
    Impossible where -- where The value of the clause is always false, Can't be used to get any tuples ;
    Select tables optimized away -- In the absence of group by In the case of clause , Index based optimization MIN/MAX Operation or for MyISAM Storage engine optimization count(*) operation , You don't have to wait until the execution phase to do the calculation , The query execution plan generation phase completes the optimization ;
    distinct -- Optimize distinct operation , Stop looking for the same value immediately after finding the first set of matching tuples .
    Be careful :
    1. Use group by When sorting , If it's useful to index , It's best to follow the order of the index , such as , There is a composite index (col1,col2), When sorting, if you skip col1, Use it directly col2 Sort , Will lead to Using filesort、Using temporary And other serious problems .
    2. Try to use overlay index ,select The following column names are exactly in the order of the index created 、 The number is the same . In this way, the index can be used to read data directly , Avoid reading data rows from the table .

Case study

Finally, a simple case , I'll put the topic first , Let's think again , Finally, put the answer .

subject

The requirement is to write SQL Execution order of .

Ideas

  1. First of all to see id Column ,id The higher the priority, the higher , Index from id by 4 I'm going to start looking at , In this line table by t2, The query is t2, So the first thing to look for is the last part select name,id from t2.

  2. id by 3 This line ,table yes t1, So what we're looking for is select id,name from t1 where other_column=''; At the end of the line Extra Column Using where It also proves this point .

  3. id by 2 This line ,table yes t3, So what we're looking for is select id from t3; in addition ,key The column is primary The primary key is used as index ,Extra Column Using Index Indicates that the overlay index is used ( That is to say, the reference is in select Back ).

  4. id by 1 This line ,table yes , According to 【id by 3 The query result of the line of 】 To query ( You can also see the corresponding id by 3 Of that line select_type As a Derived), So what we're looking for is select d1.name,(select id from t3)d2 from (select id,name from t1 where other_column = '') d1

  5. id by NULL This business ,table yes <union1,4>, According to 【id by 1 and 4 The query results of those two lines 】 To achieve union Inquire about .

answer

1.select name,id from t2
2.select id,name from t1 where other_column=''
3.select id from t3
4.select d1.name,(3.result)d2 from (2.result) d1
5.(4.result) union (1.result)

Finally, it is suggested to combine the explanation of each field above , It's best to have your own thinking process .

Last

What I'm talking about today is how to look at it explain Result .

It's like our console prints information to see how the code works , It's over , If there is bug, We have to do something about it .

And we're just talking about how to look at it today , next step , It's how to change .

MySQL More articles on index performance analysis

  1. MySQL Index performance analysis summary

    Last article  MySQL Index design summary   This paper introduces several factors that affect index design , Including filter factors . The width and size of index slice, matching column and filtering column . In the second half of the article, it introduces   Database index design and optimization   In a Book , Ideal Samsung index design process and ...

  2. Mysql Index optimization analysis

    MySQL Index optimization analysis Why did you write sql Slow query ? Why do your indexes often fail ? Through the content of this chapter , You will learn to MySQL The reason for the performance degradation , Introduction to index , Principles of index creation ,explain Use of commands , as well as explain Output word ...

  3. mySql Index optimization analysis

    MySQL Index optimization analysis Why did you write sql Slow query ? Why do your indexes often fail ? Through the content of this chapter , You will learn to MySQL The reason for the performance degradation , Introduction to index , Principles of index creation ,explain Use of commands , as well as explain Output word ...

  4. MySQL Optimize - Performance analysis and query optimization ( turn )

    Source :  MySQL Optimize - Performance analysis and query optimization Optimization should run through the whole product development cycle , For example, writing complex SQL Check the execution plan when you are finished , install MySQL The server should be configured as reasonably as possible ( I've seen too many cases where the default configuration is used completely ), According to the application load ...

  5. MySQL Optimize - Performance analysis and query optimization

    Optimization should run through the whole product development cycle , For example, writing complex SQL Check the execution plan when you are finished , install MySQL The server should be configured as reasonably as possible ( I've seen too many cases where the default configuration is used completely ), Choose reasonable hardware configuration according to the application load . 1. Performance analysis Performance analysis includes ...

  6. oracle Performance analysis with index and without index

    First, prepare a table of millions of data , This analysis of the data gap is more vivid ! Next, we use paging table data to analyze the table , according to EMP_ID Field sorting , The performance gap between using index and not using index ! sql Query syntax preparation , The specific business should be written according to the specific table sql grammar : ...

  7. mysql Explain Performance analysis keywords

    EXPLAIN Output format select_typetabletypepossible_keyskeykey_lenrowsExtra MySQL Provides a EXPLAIN command , It can be to SELECT ...

  8. mysql Index simple analysis

    Index has a crucial influence on the speed of query , Understanding indexes is also the starting point for database performance tuning . Consider the following , Suppose a table in the database has 10^6 Bar record ,DBMS The page size of is 4K, And store 100 Bar record . If there is no index , The query will add... To the entire table ...

  9. mysql Statement performance analysis case

    Two items with different writing but identical functions SQL The difference in performance . Example 1 requirements : Take out one group( hypothesis id by 100) The user number under (id), The user nickname (nick_name). User's gender ( sexualit ...

  10. mysql Analysis of the advantages and disadvantages of index

    Reprinted from :http://blog.csdn.net/linminqin/article/details/44342205  The advantages and disadvantages of index and how to judge , Whether index is needed I believe readers know that index can greatly improve the efficiency of data retrieval ...

Random recommendation

  1. Windows Live Writer Solutions to failed online installation .

    Here's a solution : Download the offline installation package , My version is 2011 Of . If you are interested, you can download it : http://wl.dlservice.microsoft.com/download/8/3/D/83D75 ...

  2. mysql-Federated storage , remote table , amount to sql server Of linked server

    MySQL Different storage engines are provided for different functional requirements . The so-called storage engine is MySQL The specific implementation of specific interface . FEDERATED It's one of the implementations for remote databases . In general, creating a table in a local database will be done in the database directory ...

  3. You can use it skillfully Dictionary Dictionary and List List ?( turn )

    Namespace System.Collections.Generic Two of them are very important , And common generic collection classes , They are Dictionary<TKey,TValue> Dictionary and List<T> ...

  4. Hanio Tower of Hanoi code recursive implementation

    1. Background introduction Hanio ( Hanoi , Also known as the river tower ) The problem is a puzzle toy from an ancient Indian legend . Vatican made three diamond pillars when he created the world , Stack on a column from bottom to top in order of size 64 A golden disk . Brahman ordered Brahman to put the disc ...

  5. android studio annotation The configuration process

    Reference to a number of configurations , It's always like this , A question like that . Environmental Science :androidstudio 1.5 preview 2 sdk 6.0 1. First, create a new one android project . The process is brief 2. To configure project Of buil ...

  6. C++ c++ First time to know

    //c++ First time to know #include<iostream> // contain c++ Standard header file //<iostream> and <iostream.h> The format is different , The former has no suffix , actually , ...

  7. BZOJ2721 [Violet 5] Cherry blossoms

    Shilling n! = a: 1 / x + 1 / y = 1 / a  =>  x = y * a / (y - a) Re order  k = y - a: therefore x = a + a ^ 2 / k  => ...

  8. Spring Study -01

    One .Srping A lightweight DI.IOC.AOP The container frame of DI: Dependency injection IOC: Inversion of control AOP: Face to face Two . Constructor Injection Constructor-arg attribute :index/name/type/r ...

  9. BeautifulReport-- Apply to unittest Visual reporting of automated testing

    install : Because it's shared by the great God , Can be directly in github<https://github.com/TesterlifeRaymond/BeautifulReport> Upload and download git clone g ...

  10. Django RESTful Web Services, This book studies DRF Pretty good

    Full name <Django RESTful Web Services: The easiest way to build Python RESTful APIs and web services wi ...