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

In this issue, the editor will bring you about how to solve the SQL optimization encountered in the work. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

-sample table CREATE TABLE `employees` (`id` int (11) NOT NULL AUTO_INCREMENT, `name` varchar (24) NOT NULL DEFAULT''COMMENT' name', `age`int (20) NOT NULL DEFAULT'0' COMMENT 'age', `position`varchar (20) NOT NULL DEFAULT''COMMENT' position', `hire_ time`timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'entry time', PRIMARY KEY (`id`), KEY `idx_name_age_ position` (`name`, `age`, `position`) USING BTREE KEY `idx_ age` (`age`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=136326 DEFAULT CHARSET=utf8 COMMENT=' employee Table 'Order by and Group by optimized EXPLAIN select * from employees WHERE name='LiLei' and position='dev' order by age

Use the leftmost prefix rule: the middle field cannot be broken, so the query uses the name index, and you can see from key_len=74 that the age index column is used in the sorting process because there is no using filesort in the Extra field.

EXPLAIN select * from employees WHERE name='LiLei' order by position

From the execution result of explain: key_len=74, the query uses name index, due to the use of position for sorting, skipping age, the emergence of Using filesort.

EXPLAIN select * from employees WHERE name='LiLei' order by age,position

The lookup uses only the name index, age and position are used for sorting, and there is no Using filesort.

EXPLAIN select * from employees WHERE name='LiLei' order by position,age

Unlike the previous case, Using filesort appears in Extra because the index is created in the order of name,age,position, but age and position are reversed when sorting.

EXPLAIN select * from employees WHERE name='LiLei' order by age asc, position desc

Although the sorted fields are in the same order as the federated index, and order by is the default ascending order, where position desc is descending, resulting in a different sort from the index, resulting in Using filesort. This query method is supported by descending indexes in Mysql8 and above.

EXPLAIN select * from employees WHERE name in ('LiLei',' zhuge') order by age, position

For sorting, multiple equality conditions are also range queries.

EXPLAIN select * from employees WHERE name >'a 'order by name

Can be optimized with an overlay index

EXPLAIN select name,age,position from employees WHERE name >'a 'order by name

Filesort sort EXPLAIN select * from employees where name='LiLei' order by position

Check the trace result corresponding to this sql (only the sorted part is shown):

Set session optimizer_trace= "enabled=on", end_markers_in_json=on;-- enable traceselect * from employees where name = 'LiLei' order by position;select * from information_schema.OPTIMIZER_TRACE {"join_execution": {--sql execution phase "select#": 1, "steps": [{"filesort_information": [{"direction": "asc", "table": "`employees`" "field": "position"}] / * filesort_information * /, "filesort_priority_queue_optimization": {"usable": false, "cause": "not applicable (no LIMIT)"} / * filesort_priority_queue_optimization * / "filesort_execution": [] / * filesort_execution * /, "filesort_summary": {--File sorting information "rows": 1,-- estimated number of scan lines "examined_rows": 1,-- lines participating in sorting "number_of_tmp_files": 0,-- number of temporary files used A value of 0 means to sort all in sort_buffer memory, otherwise use disk file sort "sort_buffer_size": 200704,-- sort cache size "sort_mode": "--sort method The one-way sort used here} / * filesort_summary * /}] / * steps * /} / * join_execution * /}

Modify max_length_for_sort_data=10

Set max_length_for_sort_data = 10;-- the total length of all fields in the employees table must be greater than 10 bytes select * from employees where name = 'LiLei' order by position;select * from information_schema.OPTIMIZER_TRACE {"join_execution": {"select#": 1, "steps": [{"filesort_information": [{"direction": "asc", "table": "`employees`" "field": "position"}] / * filesort_information * /, "filesort_priority_queue_optimization": {"usable": false, "cause": "not applicable (no LIMIT)"} / * filesort_priority_queue_optimization * / "filesort_execution": [] / * filesort_execution * /, "filesort_summary": {"rows": 1, "examined_rows": 1, "number_of_tmp_files": 0, "sort_buffer_size": 53248 "sort_mode": ""-sort by two-way sorting} / * filesort_summary * /}] / * steps * /} / * join_execution * /}

Comparing the two sorting modes, one-way sorting will put all the field data that need to be queried into sort_buffer, while two-way sorting will only sort the primary key id and the fields to be sorted in sort_buffer, and then return to the field data needed for query in the original table through the primary key id. MySQL controls sorting through the parameter max_length_for_sort_data and uses different sorting modes in different scenarios to improve sorting efficiency.

Optimization summary

Mysql supports two ways of sorting filesort and index,using index means that Mysql scans the index itself to complete the sorting. The efficiency of index is high, while that of filesort is low.

Order by uses using index in two situations. The order by statement uses the leftmost front column of the index. Use the where clause and order by clause conditional column combination to satisfy the leftmost front column of the index.

Try to sort on the index column and follow the leftmost prefix rule when the index is established (the order in which the index is created).

If the condition of order by is not on the index column, using filesort is generated.

The above is the editor for you to share how to solve the SQL optimization encountered in the work, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are 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.