After the success of the first day to answer the leader’s questions, my leader thought hard all night, all kinds of baidu, all kinds of search, all kinds of search, finally found a way to deal with me… Damn, I guess I’m not far from leaving
Leader: You did a good job yesterday. I still have some questions today. Can you answer them
Me: Boss, I was wrong. I really don’t know anything else!
Leader: No, no, no, no, no, no, you are so good, how can you not? Cut the crap. If you can’t answer the question, Hem, you rattail juice
Leader: Listen, yesterday you said overwrite index. Today I’m going to ask you, do you know what the left-most prefix rule is?
Me: (to myself: That’s it? Look yesterday eldest brother this baidu did not baidu come out what thing) I want to…
Leftmost prefix rule
When it comes to the left-most prefix rule, composite indexes follow the left-most prefix rule. As the name implies, the index will be inoperable if it starts at or after the second column of the index
Select name, age, sex from user where name=’xx’ and age=’22’ select name, age, sex from user where name=’xx’ Where name=’xx’ and age=’22’ and sex=’ male
Select name, age, sex from user where age=’22’ and sex=’ male ‘select name, age, sex from user where age=’22’ Select * from left where name=’xx’ and sex=’ male ‘select * from left where name=’xx’ and sex=’ male’ select * from left where name=’xx’ and sex=’ male ‘ Then none of them go through the index.
Leader: Don’t be complacent. This is just foreplay. Tell me how to optimize SQL. Make sure you say everything, or you’ll find out.
Me:… It’s…
SQL optimization
When it comes to SQL optimization, one must mention a tool, Explain, that analyzes SELECT statements and outputs details of select execution for developers to optimize
Same statement from yesterday
EXPLAIN select userID,userName,age from user where userName='Joe'
Copy the code
A brief analysis:
Select_type: indicates the query type. The common values are as follows:
- SIMPLE: indicates that the query statement does not contain subquery or union
- PRIMARY: indicates that the query is the outermost query
- UNION: Indicates that this query is the second or subsequent query to the UNION
- DEPENDENT UNION: The second or subsequent query statement in the UNION that uses external query results
- UNION RESULT: the RESULT of the UNION
- SUBQUERY: SELECT the SUBQUERY statement
- DEPENDENT SUBQUERY: The SELECT SUBQUERY depends on the results of the outer query
The most common query type is SIMPLE, which means that our query has no subqueries and no UNION queries
Table: This column indicates which table is being accessed.
Type: indicates the mode used by the storage engine to query data. An important property to determine whether a query is a full table scan or a partial index-based scan. The commonly used attribute values are as follows, increasing efficiency from top to bottom.
- ALL: indicates full table scan, with the lowest performance
- Index: indicates a full table scan based on an index. The index is scanned first and then all table data
- Range: indicates the range of indexes to be queried. Use >,>=,<,<=,in, and so on
- Ref: indicates that a non-unique index is used for single-value query
- Eq_ref: usually occurs in multi-table JOIN query, indicating that each record of the previous table matches only one row of the subsequent table.
- Const: use primary key or unique index for equivalent query, constant query
- Null: indicates that the table is not accessed and the fastest.
Possible_keys: indicates the possible_keys index that can be used in query. Note that it is not always used; the index name is displayed
Key: indicates the actual index used in the query. The index name is displayed
Rows: The MySQL query optimizer uses statistics to estimate how many rows a SQL query needs to scan to obtain the result. In principle, the less rows a SQL query has, the more efficient it is
Key_len: indicates the number of bytes used by the index in the query. We can determine whether the combined index key_len is used as follows:
- String type
- The length of a string depends on the character set: latin1=1, GBK =2, UTf8 =3, UTF8MB4 =4;
Char (n) : nCharacter set length vARCHAR (n) : nCharacter set length +2 bytes
- Numeric types
- TINYINT: 1 byte
- SMALLINT: 2 bytes
- MEDIUMINT: 3 bytes
- INT,FLOAT: 4 bytes
- BIGINT, DOUBLE: 8 bytes
- Time to type
- DATE: 3 bytes
- TIMESTAMP: 4 bytes
- DATETIME: 8 bytes
- The field properties
- The Null attribute takes 1 byte. If a field is Not Null, this item is Not available
Ref: This column displays the columns or constants used by the table to find values in the index of the key column. Common examples are const (constant), column name (column: film.id).
Extra
“Extra” means a lot of Extra information. Relevant information will be displayed in “Extra” for various operations. Common ones are as follows:
- Using where
- Indicates that data needs to be queried by index back to the table
- Using index
- Indicates that the query needs to pass the index. The index can meet the required data
- Using filesort
- Using filesort indicates that the results of the query need additional sorting, the amount of data is small in memory, large in disk, therefore, Using filesort recommended optimization
- Using temprorary
- The query uses a temporary table, which is typically used for de-weighting, grouping, and so on.
At this point, I believe you do query, must be a lot of use like fuzzy query, right? Does an index work with a like query?
The index can be used only if the % character is appended to it. It’s the same table as before
ALTER TABLE user ADD INDEX u (userName,age);
EXPLAIN select userID,userName,age from user where userName like'% thirty percent'
Copy the code
Type = ALL; key = null; no index is used
EXPLAIN select userID,userName,age from user where userName like 'thirty percent'
Copy the code
You can see the difference between userName and age and u.
% = % = %; % = %; % = %; % = %; % = %;
In MySQL, query sorting is supported by filesort and index. In filesort, the query results are sorted first and then sorted in cache or disk, which is inefficient. Using index means that the index can be automatically sorted without additional sorting operations.
Filesort has two sorting algorithms: double-path sort and single-path sort
Dual-path sort: Two disk scans are required to obtain user data. The first time the sort field is read out, and then sort; The second time to read the other field data.
Single-way sort: Query the desired column data from disk, then sort the results in memory and return them. If the query data exceeds the cache sort_buffer, it will result in multiple disk reads and the creation of temporary tables, resulting in multiple I/OS, which will increase the burden. Solution: use select * sparingly; Increase sorT_BUFFer_SIZE capacity and max_LENGTH_FOR_SORT_data capacity
If we Explain SQL analysis, the Extra attribute shows Using filesort, indicating that filesort sorting method is used, which needs to be optimized. If Extra shows Using index, it means overwriting the index, and also means that all operations are completed on the index. Index sorting can also be used. It is recommended that you use overwriting the index as much as possible.
In the following cases, sort by index is used
- The Order by clause index column combination satisfies the left-most front of the index
explain select id from user order by id; //The corresponding (id) and (id,name) indexes are validCopy the code
- WHERE clause +ORDER BY clause index column combination satisfies the left front of the index
explain select id from user where age=18 order by name; //Corresponds to the (age,name) indexCopy the code
In the following cases, an filesort-style sort is used
- Use both ASC and DESC for indexed columns
explain select id from user order by age asc,name desc; //Corresponds to the (age,name) indexCopy the code
- The WHERE and ORDER BY clauses satisfy the leftmost prefix, but the WHERE clause uses range queries (e.g. >, <, in, etc.)
explain select id from user where age>10 order by name; //Corresponds to the (age,name) indexCopy the code
- The ORDER BY or WHERE+ORDER BY index column does not satisfy the left-most front of the index
explain select id from user order by name; //Corresponds to the (age,name) indexCopy the code
- MySQL only uses one index at a time. ORDER BY involves two indexes
explain select id from user order by name,age; //Correspond to (name) and (age)Copy the code
- The WHERE clause uses a different index than the ORDER BY clause
explain select id from user where name='tom' order by age; //Corresponding to (name), (age) indexCopy the code
- Indexed columns in the WHERE or ORDER BY clause use expressions, including function expressions
explain select id from user order by abs(age); //Corresponding (age) indexCopy the code
Here are some examples of common cases where the index is not taken:
- Any operation performed on an index column (calculation, function, (automatic or manual) type conversion) will invalidate the index
- Use! = or <>, not in, not exists cannot use a call, < <, > >, <=, >=. The mysql internal optimizer evaluates whether to use an index based on the index ratio, table size, and other factors
- Is null, is not NULL Generally, indexes cannot be used
- Index failure without single quotation marks on string (this is a very bad situation, we have a small error in our project, someone wrote a SQL string without single quotation marks, such as phone set vARCHar type, but when the query is directly called phone=123, instead of phone=’123′, the query is directly called phone=123. Due to the large amount of data, the whole cluster is under great pressure and the service is stuck. It took a long time to find this problem, which is also where most people are easy to be careless
- Use or or in less. Mysql does not necessarily use an index when querying with it. The mysql internal optimizer evaluates whether to use an index based on multiple factors such as the percentage of searches, table size, and so on.
- If it is a range query, the scope is relatively large, may not go to the index, it is recommended to split the range, split into two small ranges, is likely to go to the index
Another time before, when we were querying slower interfaces, we finally found the SQL problem, because the table was not a problem at the beginning of the pressure test, because the amount of data is relatively small, but when the amount of data is large, we found the problem, all invalid indexes. Very afflictive
Actually database optimization, also is not only at the SQL level, hardware, table structure design, business needs have relations, in some cases, SQL optimization, will slowly, then this can take a look at this demand is reasonable, for example, billions of data, you will all see the somebody else, all kinds of query conditions, so do you think this is reasonable, I think is not reasonable demand level, everyone at the time of writing code, don’t just in order to complete the business, it is more to think about performance issues, business either simple or difficult, can only say that ascension is of help to you, but if you want to go to good Internet companies, performance tuning is a must here, and the overall architecture of the system are explored. That’s what exercises you the most.
Leader: Ok, young man, this is the last long speech, but you are not too detailed ah, this specific SQL optimization
I: eldest brother, I this want to say fine, I can not say a day, if this article point praise can break fifty, after I single door out an article specific actual combat about SQL optimization!
Leader: That’s what you said, young man. Today I still can’t hold you down.
Me:…
Leader: Got it! Why don’t you briefly tell me about mysql transactions and locks?
Me:… Old man, you really don’t talk about martial virtue, I was careless, didn’t flash… I haven’t learned it yet! I said index here!!
Me:… Slip away, slip away. Go home and take a good look at this place and I’ll answer you next time!