Shulou(Shulou.com)06/01 Report--

This article mainly introduces how to optimize SQL. It is very detailed and has a certain reference value. Friends who are interested must finish it!

I. existing problems

After optimizing the sql slow query, we have found the following types of problems in our system:

1. Unindexed: the whole table is not indexed; 2. Index misses: there is an index, but the index misses under some query conditions; 3. Search for additional unnecessary fields, resulting in a return to the table; 4. Sorting, aggregation leads to slow query; 5. Query the database for many times with the same content; 6. Unrestricted search scope or restricted search scope is not expected, resulting in a full scan

II. Solutions

1. Optimize the index, add or modify the current index; 2. Rewrite sql;3. Using redis cache to reduce the number of queries; 4. Add conditions to avoid unnecessary queries; 5. Increase the condition and reduce the scope of query

III. Case study

(1) search interface for medicinal materials

The complete sql statement is in the appendix. To facilitate reading and desensitization, some commonly used fields are in Chinese.

Here we mainly talk about the whole analysis process after we get the SQL statement, think about the logic, and then adjust the process and the final solution.

To provide you with some reference, but also hope that you can put forward better suggestions.

This sql sentence requires that according to the pinyin or Chinese searched by the doctor, conduct a fuzzy query to find the medicinal materials, and then find the following suppliers according to the medicine storehouse selected by the doctor, and then match according to the supplier, excluding the medicinal materials that the supplier does not have, and then sort out the medicinal materials according to the real name before, the alias in the back, the exact match in front, the partial matching in the last half a year, and the use habits of the doctor in the last six months. Finally, the same medicine with different names is aggregated and displayed in the form of real name (alias).

1. Analyze sql

(1) 14-8

Analysis of explain results with row 14 and id 8:

① Explain8,DERIVED,ssof,range, "ix_district,ix_ supplier id", ix_district,8,NULL,18,Using where; Using index; Using temporary ② SqlSELECT DISTINCT (ssof. Supplier id) AS supplier id FROM drugstore supplier relationship table AS ssof WHERE ssof. Id IN (1, 2, 8, 9, 10, 11, 12, 13, 14, 15, 17, 22, 24, 25, 26, 27, 31, 33) AND ssof. Prescription id IN (1) ③ index PRIMARY KEY (`id`), UNIQUE KEY `ix_ district` (`drugstore id`, `prescription formulation id`, `supplier id`) USING BTREE,KEY `ix_ supplier id` (`supplier id`) USING BTREE ④ analysis

The index is used and a temporary table is created, where the index is completely overwritten, but there is also a table return operation.

The reason is to use in, which causes the table to be returned. If in can be automatically optimized to equal by mysql, it will not return to the table. If it cannot be optimized, go back to the table.

Temporary tables are unavoidable because of distinct.

When using in at the same time, it should be noted that if there are a large number of values, there are tens of thousands of values. Even if the degree of discrimination is high, it will cause the index to fail, which requires multiple batch queries.

2. 12-7

(1) Explain

7,DERIVED,ALL,NULL,NULL,NULL,NULL,18,Using temporary; Using filesort

(2) Sql

INNER JOIN (14-8 temporary table above) tp ON tp. Supplier id= ms. Supplier id

(3) Index

None

(4) Analysis

For temporary table operations, no index, file sorting is used.

This part is part of the association operation between the temporary table and the medicine list, and the sorting of files is caused by the need to group by the medicine list id.

1. By default, mysql generates temporary tables after using group by, and then sorts them (the sort here defaults to Quick sort), which consumes performance.

2. The essence of group by is grouping and then sorting [rather than sorting before grouping].

3. By default, group by column is grouped according to column and then sorted according to column ascending order; group by column order by null is grouped by default according to column, and then sorted according to the target primary key ID in ascending order.

3. 13-7

(1) Explain

7 ix_title,idx_audit,idx_mutiy DERIVEDJournal Ms ref, "ix_title,idx_audit,idx_mutiy", idx_mutiy,5, "tp. Supplier id,const", 172 null

(2) Sql

SELECT ms. List of medicinal materials id, max (ms.audit) AS audit, max (ms.price) AS price,max (ms.market_price) AS market_price,max (ms.is_granule) AS is_granule,max (ms.is_decoct) AS is_decoct, max (ms.is_slice) AS is_slice,max (ms.is_cream) AS is_cream, max (ms.is_extract) AS is_extract,max (ms.is_cream_granule) AS is_cream_granule Max (ms.is_extract_granule) AS is_extract_granule,max (ms.is_drychip) AS is_drychip, max (ms.is_pill) AS is_pill,max (ms.is_powder) AS is_powder, max (ms.is_bolus) AS is_bolus FROM supplier list AS ms INNER JOIN (SELECT DISTINCT (ssof. Supplier id) AS supplier id FROM drugstore supplier relationship table AS ssof WHERE ssof. Id IN (1, 2, 8, 9, 10, 11, 12, 13, 14, 15, 17, 22, 24, 25, 26, 27, 31, 33) AND ssof. Id IN (1) tp ON tp. Supplier id= ms. Supplier id WHERE ms.audit = 1 GROUP BY ms. List of Medicinal Materials id

(3) Index

KEY `idx_ mutiy` (`supplier id`, `audit`, `medicine list id`)

(4) Analysis

The index is hit, and the supplier id is used for the connection between tables, and the order of index establishment is the audit,Group by conditional medicine table id in the supplier id,where condition.

This part does not need to be changed for the time being.

4.10-6

(1) Explain

6DERIVEDDERIVEDDifferentium range, PRIMARY,id,idx_timeline,idx_did_timeline,idx_did_isdel_statuspay_timecreate_payorderid,idx_did_statuspay_ischecked_isdel, idx_did_timeline,8,NULL,546,Using where; Using index; Using temporary; Using filesort

(2) Sql

SELECT count (*) AS total, rc.i AS m prescription list id FROM prescription table AS rc INNER JOIN prescription table AS r ON r.id = rc. Prescription table _ id WHERE r.did = 40 AND r.timeline > 1576115196 AND rc.type_id in (1,3) GROUP BY rc.i

(3) Index

KEY `idx_did_ timeline` (`did`, `timeline`)

(4) Analysis

Driving table and driven table, small table driving large table.

First understand which table is the driven table and which table is the driven table when the join is connected:

1. When using left join, the left table is the driven table and the right table is the driven table

two。 When using right join, the right table drives the table, and the left table drives the table.

3. When using join, mysql selects the table with a small amount of data as the driven table and the large table as the driven table.

4. In is followed by a driver table, and exists is preceded by a driver table

5. 11-6

(1) Explain

6DERIVEDQuery id ref, "orderid_ medicine list, prescription list _ id,5,r.id,3,Using where",

(2) Sql

Same as above

(3) Index

KEY `idx_ prescription list _ id` (`prescription list _ id`, `type_ id`) USING BTREE

(4) Analysis

There is no problem with the order of the indexes, and it is still in that causes the table to be returned.

6.8-5

(1) Explain

5,UNION,malias,ALL,id_tid,NULL,NULL,NULL,4978,Using where

(2) Sql

SELECT mb.id, mb.sort_id, mb.title, mb.py, mb.unit, mb.weight, mb.tid, mb.amount_max, mb.poisonous, mb.is_auxiliary, mb.is_auxiliary_free, mb.is_difficult_powder, mb.brief, mb.is_fixed_recipe ASE WHEN malias.py = 'GC' THEN malias.title ELSE CASE WHEN malias.title =' GC' THEN malias.title ELSE''END END AS atitle, alias.py AS apy, CASE WHEN malias.py =' GC' THEN 2 ELSE CASE WHEN malias.title = 'GC' THEN 2 ELSE 1 END END AS ttid FROM list AS mb LEFT JOIN list AS malias ON malias.tid = mb.id WHERE alias.title LIKE'% GC%' OR malias.py LIKE'% GC%'

(3) Index

KEY `id_ tid` (`tid`) USING BTREE

(4) Analysis

Because like is a left and right like, you can't build an index, so you can only build tid. Type is an all that traverses the table to find matching rows, about the same size, and estimates the number of rows that need to be read to find the required records at 4978. This cannot be optimized because of like. Instead of indexing, the medicine table AS mb FORCE INDEX (id_tid) is changed to a mandatory index, and the number of rows read is reduced by 700rows.

7.9-5

(1) Explain

5Jing UNION mam mb recorder eqipref, PRIMARY,ix_id, PRIMARY,4,malias.tid,1,NULL

(2) Sql

Same as above

(3) Index

PRIMARY KEY (`id`) USING BTREE

(4) Analysis

Take the primary key index, the number of rows is also small, through.

8.7-4

(1) Explain

4,DERIVED,mb,ALL,id_tid,NULL,NULL,NULL,4978,Using where

(2) Sql

SELECT mb.id, mb.sort_id, mb.title, mb.py, mb.unit, mb.weight, mb.tid, mb.amount_max, mb.poisonous, mb.is_auxiliary, mb.is_auxiliary_free, mb.is_difficult_powder, mb.brief, mb.is_fixed_recipe,'AS atitle,'AS apy CASE WHEN mb.py = 'GC' THEN 3 ELSE CASE WHEN mb.title =' GC' THEN 3 ELSE 1 END END AS ttid FROM list AS mb WHERE mb.tid = 0 AND (mb.title LIKE'% GC%' OR mb.py LIKE'% GC%')

(3) Index

KEY `id_ tid` (`tid`) USING BTREE

(4) Analysis

Tid int (11) id' of NOT NULL DEFAULT'0' COMMENT 'real name drug

He is also like, which can't be optimized.

9.6-3

(1) Explain

3,DERIVED,ALL,NULL,NULL,NULL,NULL,9154,Using filesort

(2) Sql

UNION ALL

(3) Index

None

(4) Analysis

Is to merge the real name search results with other people's search results. Avoid using or connections, speed up the formation of a munion table, initially complete the medicine search, and then sort.

This one has been queried twice and then joined with union, which can be considered as a single query. Use case when to distinguish and calculate the weight.

This is an optimization point.

10.4-2

(1) Explain

2,DERIVED,ALL,NULL,NULL,NULL,NULL,9154,NULL

(2) Sql

SELECT munion.id, munion.sort_id, case when length (trim (group_concat (munion.atitle SEPARATOR')) > 0 then concat (munion.title,'(') Trim (group_concat (munion.atitle SEPARATOR')),') else munion.title end as title, munion.py, munion.unit, munion.weight, munion.tid, munion.amount_max, munion.poisonous, munion.is_auxiliary Munion.is_auxiliary_free, munion.is_difficult_powder, munion.brief, munion.is_fixed_recipe,-- trim (group_concat (munion.atitle SEPARATOR'')) AS atitle, # #-- trim (group_concat (munion.apy SEPARATOR')) AS apy # # max (ttid) * 100000 + id AS ttid FROM munion GROUP BY id-end of all real-name medicinal materials # #

(3) Index

None

(4) Analysis

It's all searched in the temporary table.

11.5-2

(1) Explain

2,DERIVED,ref,5,m.id,10,NULL

(2) Sql

Select fields from all real name medicine table as m LEFT JOIN (personal use medicine statistics table) p ON m.id = medicine list id

(3) Index

None

(4) Analysis

2 virtual tables left join

The index generated by the optimizer for the derived table is used

Here is a waste of performance, each query, the doctor's historical prescription records are counted, and the statistics are the results of several large tables. However, if it is only optimized by sql, it cannot be optimized here for the time being.

12.2-1

(1) Explain

1,PRIMARY,ALL,NULL,NULL,NULL,NULL,3096,Using where; Using temporary; Using filesort

(2) Sql

(3) Index

(4) Analysis

Temporary table operation

13.3-1

(1) Explain

1,PRIMARY,ref,4,msu. List of Medicinal Materials id,29,NULL

(2) Sql

(3) Index

(4) Analysis

Temporary table operation

14.null

(1) Explain

NULL,UNION RESULT, ", ALL,NULL,NULL,NULL,NULL,NULL,Using temporary

(2) Sql

(3) Index

(4) Analysis

Temporary watch

(2) optimize sql

Above we only optimize the index, following the following principles:

1. The leftmost prefix matching principle, a very important principle, mysql will always match to the right until it encounters a range query (>, 3 and d = 4). If you build an index in the order of (ameme bforce c), d does not need an index, if you build an index (a meme bdre c), then you can use it, and the order of aforce b d can be adjusted at will. 2. = and in can be out of order, for example, a = 1 and b = 2 and c = 3 index can be built in any order, and mysql's query optimizer will help you optimize it into a form that the index can recognize. 3. Try to select a highly differentiated column as the index. The formula for distinguishing degree is count (distinct col) / count (*), indicating the proportion of non-repetitive fields. The larger the proportion, the less the number of records we scan, and the differentiation degree of the only key is 1. While some status and gender fields may be 0 in front of big data, then some people may ask, is there any empirical value for this ratio? It is difficult to determine this value for different scenarios. Generally, we need more than 0.1 for the fields that require join, that is, an average of 10 records are scanned. 4. Index columns can not participate in the calculation, keep the column "clean", for example, from_unixtime (create_time) = '2014-05-29' can not use the index, the reason is very simple, the b + tree is stored in the data table field values, but for retrieval, all elements need to be compared with the application function, obviously the cost is too high. So the statement should be written as create_time = unix_timestamp ('2014-05-29'). 5. Expand the index as much as possible, do not create a new index. For example, if you already have an index of an in the table, and now you want to add the index of (a), you only need to modify the original index.

Query optimization artifact-explain command

I believe you are no stranger to the explain command. Please refer to the official website explain-output for specific usage and field meaning. It needs to be emphasized that rows is the core indicator, and most statements with small rows must be executed quickly (there are exceptions, which will be discussed below). So optimization statements are basically optimizing rows.

Change the basic steps:

0. First run to see if it is really slow, pay attention to set the SQL_NO_CACHE1.where condition sheet look-up, lock the minimum return record table. This sentence means that the where of the query statement is applied to the table with the least number of records returned in the table, and each field of the single table is queried separately to see which field has the highest degree of differentiation; 2.explain checks whether the execution plan is consistent with 1 expectation (starting with locking the table with fewer records); the sql statement in the form of 3.order by limit gives priority to the sorted table; 4. Understand the business side usage scenarios; 5. When adding an index, refer to several major principles of indexing; 6. Observation results, not in line with expectations, continue to analyze from zero

Each step has been analyzed in detail above, removing the union operation and adding the index according to the sql above. It can be seen that although it has been improved after optimization. But there is still a long way to go from our hope, but just to do sql optimization, there is not much room for improvement, so I decided to solve it in other ways.

(3) split sql

As the speed is still not satisfactory, especially the statistics of personal drug use, it is not necessary to count all of them every time, and to optimize them again, it should not work just by modifying the index, so consider using caching.

The next step is to modify the php code to split all the sql statements and then assemble them.

(1) search for real name, alias (cache)

SELECT mb.id, mb.sort_id, mb.title, mb.py, mb.unit, mb.weight, mb.tid, mb.amount_max, mb.poisonous, mb.is_auxiliary, mb.is_auxiliary_free, mb.is_difficult_powder, mb.brief, mb.is_fixed_recipe, IFNULL (group_concat (malias.title),'') atitle, IFNULL (group_concat (malias.py) '') apy FROM Drug list AS mb LEFT JOIN Drug Table AS malias ON malias.tid = mb.id WHERE mb.tid = 0 AND (malias.title LIKE'% GC%' OR malias.py LIKE'% GC%' or mb.title LIKE'% GC%' OR mb.py LIKE'% GC%') group by mb.id

(2) if there is a medicinal material

① sorting

The real name comes first, the alias comes after, the exact match comes first, and the partial match comes after.

/ / A pair of search results are processed, and the weight ② is added to search SELECT ms for suppliers of medicinal materials. List of id, max (ms.audit) AS audit, max (ms.price) AS price, max (ms.market_price) AS market_price, max (ms.is_granule) AS is_granule, max (ms.is_decoct) AS is_decoct, max (ms.is_slice) AS is_slice, max (ms.is_cream) AS is_cream, max (ms.is_extract) AS is_extract, max (ms.is_cream_granule) AS is_cream_granule Max (ms.is_extract_granule) AS is_extract_granule, max (ms.is_drychip) AS is_drychip, max (ms.is_pill) AS is_pill, max (ms.is_powder) AS is_powder, max (ms.is_bolus) AS is_bolus FROM supplier list AS ms WHERE ms.audit = 1 AND ms. Supplier idin (SELECT DISTINCT (ssof. Supplier id) AS supplier id FROM drugstore supplier relationship table AS ssof WHERE ssof. Medicine bank id IN (1, 2, 8, 9, 10, 11, 12, 13, 14, 14, 14, 17, 24, 25, 26, 27, 31, 33) AND ssof. Id IN (1) AND ms. List of Medicinal Materials id IN (78pr 205rec. 206pr. 207pr.). List of Medicinal Materials id IN (78pr. 205.) 4404. 4404. 4408.) AND ms.is_slice = 1. GROUP BY ms. List of Medicinal Materials id

③ takes the doctor's historical prescription dosage data (cache) SELECT count (*) AS total, rc.i AS prescription table id FROM prescription table AS rc INNER JOIN prescription table AS r ON r.id = rc. Prescription list _ id WHERE r.did = 40 AND r.timeline > 1576116927 AND rc.type_id in (1p3) GROUP BY rc.i

④ assembly and sorting fine-tuning

(3) Summary

Running speed, for doctors who do not prescribe a lot of prescriptions, both are about 0.1 seconds. However, if you encounter a doctor with a large prescription, the optimized sql speed is relatively stable, which can always be maintained at about 0.1s, and the sql speed before optimization will exceed 0.2s. The speed has more than doubled.

Finally, the search results are compared with those before optimization, and the number and order of the results are exactly the same. This optimization is over.

IV. Appendix:

SELECT sql_no_cache * FROM (--mbu start## SELECT M.E., ifnull (p.total, 0) AS total FROM (--all real-name medicinal materials begin # # SELECT munion.id, munion.sort_id Case when length (trim (group_concat (munion.atitle SEPARATOR') > 0 then concat (munion.title,', trim (group_concat (munion.atitle SEPARATOR')),') else munion.title end as title Munion.py, munion.unit, munion.weight, munion.tid, munion.amount_max, munion.poisonous, munion.is_auxiliary, munion.is_auxiliary_free, munion.is_difficult_powder, munion.brief, munion.is_fixed_recipe,-- trim (group_concat (munion.atitle SEPARATOR')) AS atitle # #-trim (group_concat (munion.apy SEPARATOR')) AS apy,## max (ttid) * 100000 + id AS ttid FROM (--# union start) to get all the medicinal materials # # (SELECT mb.id, mb.sort_id, mb.title) Mb.py, mb.unit, mb.weight, mb.tid, mb.amount_max, mb.poisonous, mb.is_auxiliary, mb.is_auxiliary_free, mb.is_difficult_powder, mb.brief Mb.is_fixed_recipe,''AS atitle,''AS apy CASE WHEN mb.py = 'GC' THEN 3 ELSE CASE WHEN mb.title =' GC' THEN 3 ELSE 1 END END AS ttid FROM list AS mb WHERE mb.tid = 0 AND (mb.title LIKE'% GC) %'OR mb.py LIKE'% GC%')-- end of real name medicine # # UNION ALL (SELECT mb.id Mb.sort_id, mb.title, mb.py, mb.unit, mb.weight, mb.tid, mb.amount_max, mb.poisonous, mb.is_auxiliary, mb.is_auxiliary_free, mb.is_difficult_powder Mb.brief, mb.is_fixed_recipe, CASE WHEN malias.py = 'GC' THEN malias.title ELSE CASE WHEN malias.title =' GC' THEN malias.title ELSE''END END AS atitle, malias.py AS apy CASE WHEN malias.py = 'GC' THEN 2 ELSE CASE WHEN malias.title =' GC' THEN 2 ELSE 1 END END AS ttid FROM list AS mb LEFT JOIN list AS malias ON malias.tid = mb.id WHERE malias.title LIKE'% GC%' OR malias.py LIKE'% GC%' )-- end of other medicinal materials #-- # union end##) munion GROUP BY id-- end of all real-name medicinal materials # #) m LEFT JOIN (--start statistics of personal use of medicinal materials # # SELECT count (*) AS total Rc.i AS m prescription table id FROM prescription list AS rc INNER JOIN prescription table AS r ON r.id = rc. Prescription list _ id WHERE r.did = 40 AND r.timeline > 1576115196 AND rc.type_id in (1 3) GROUP BY rc.i-end of statistics on personal use of medicinal materials # #) p ON m.id = list of medicinal materials id-mbu end # #) mbu INNER JOIN (--msu start supplier screening of medicinal materials # # SELECT ms. List of id, max (ms.audit) AS audit, max (ms.price) AS price, max (ms.market_price) AS market_price, max (ms.is_granule) AS is_granule, max (ms.is_decoct) AS is_decoct, max (ms.is_slice) AS is_slice, max (ms.is_cream) AS is_cream Max (ms.is_extract) AS is_extract, max (ms.is_cream_granule) AS is_cream_granule, max (ms.is_extract_granule) AS is_extract_granule, max (ms.is_drychip) AS is_drychip, max (ms.is_pill) AS is_pill, max (ms.is_powder) AS is_powder Max (ms.is_bolus) AS is_bolus FROM supplier list of medicinal materials AS ms INNER JOIN (SELECT DISTINCT (ssof. Supplier id) AS supplier id FROM drugstore supplier relationship table AS ssof WHERE ssof. Id IN (1, 2, 8, 9, 10, 11, 12, 13, 14, 15, 17, 22, 24, 25, 26, 27, 31, 33) AND ssof. Id IN (1) tp ON tp. Supplier id= ms. Supplier id WHERE ms.audit = 1 GROUP BY ms. List of medicinal materials id-msu end # #) msu ON mbu.id = msu. List of medicinal materials idWHERE msu. Medicine list id > 0 AND msu.is_slice = 1order by total desc, ttid desc above is all the content of this article "how to optimize SQL", thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.