Summary of database SQL Optimization 2-millions database optimization scheme + Case study

Project Background

There are three millions data sheets

Knowledge Point Table (ex_subject_point) 9,316 data

Question Table (Ex_question_junior) 2,159,519 data has 45 fields

Knowledge Point question Relation table (Ex_question_r_knowledge) 3,156,155 data

The test database is: MySQL (5.7)

7. The use of parameters in the WHERE clause iswon'tCauses a full table scan. Case analysis

8. The expression of a field in the WHERE clause iswon'tCauses a full table scan. However, the query speed will be slow, so try to avoid using. Case analysis

Execution time is 1.064s optimization scheme

SELECT Ex_question_junior. question_id fromWHERE=4/2;

The execution time is 0.012S9, and you should try to avoid function operations on the field in the Where clause, which causes the engine to discard the full table scan using the index. Case analysis

Optimization scenarios

SELECT *  from  WHERE>= "2018----"  and < "2018--"

But explain, it's still a full-scale scan.

Is it because the Date field index has no effect? Or is it because of the >= and < op symbols? To verifynarrow the scope of the query and find that the index is effective. So the issue is not a date field.

Change the field query, with >= and < operation symbols, the index is still effective. But what's the reason?

later on the Internet to find information, because the number of queries is more than a portion of the table, Mysql30%,oracle 20% (this data may not be accurate, not official description, for informational purposes only), resulting in invalid index. 10. Do not perform functions, arithmetic operations, or other expression operations on the left side of "=" in the WHERE clause, or the index may not be used correctly by the system. For examples, see 8th and 9th. 11. When using an indexed field as a condition, if the index is a composite index,then you must use the first field in the index as a condition to ensure that the system uses the index, otherwise the index will not be used (this is not true in MySQL), and you should match the order of the fields to the index order as much as possible. Case Study composite index field: path,parent_point_id

Swaps the order of conditions in the WHERE clause. You can still use the indexed

The composite index only queries the first field, which is effective

The composite index only queries the second field and finds that the index is not working.

12. Do not write meaningless queries, such as the need to generate an empty table structure: (General development will not be so boring, on the official project to write this kind of thing)Select Col1,col2 into #t from T where1=0 This type of code does not return any result sets, but consumes system resources and should be changed to this:CREATE TABLE #t (...)

Reference:

https://mp.weixin.qq.com/s?__biz=MzIxMjg4NDU1NA==&mid=2247483684&idx=1&sn= f5abc60e696b2063e43cd9ccb40df101&chksm= 97be0c01a0c98517029ff9aa280b398ab5c81fa1fcfe0e746222a3bfe75396d9eea1e249af38&mpshare=1&scene=1& Srcid=0606xghebs4rbzlovv786wby#rd

***************************************************************************

Xiao Xu Zhu
Any form of reprint is welcome, but please be sure to indicate the source.
Limited to my level, if the article and code are not described in the wrong place, please do not hesitate to enlighten.

Summary of database SQL Optimization 2-millions database optimization scheme + Case study