MySQL optimization--Ye Jinlong Teacher Lecture Notes

Copy to TMP table

When you modify the table structure by ALTER TABLE

Recommendation: Early morning execution

Copying to TMP table

Copy data to a temporary table in memory, common in group by operations

Recommendation: Create an index

Copying to TMP table on disk

The temporary result set is too large to fit in memory, and the temporary tables in memory need to be copied to disk to form a #sql * * *. MYD, #sql * * *. MYI

Recommendation: Create indexes and increase sort_buffer_size/tmp_table_size/max_heap_table_size

Creating Sort Index

Temporary tables need to be sorted in order by in the current select

Recommendation: Create an index

Creating tmp table

When you create a temporary table that is based on memory or disk, the state changes to a temporary table that is converted from memory to a disk: Copying to TMP table on disk

Recommendation: Create an index

Sending data

Sending data from the server to the client is also likely to receive the data returned by the storage engine layer and send it to the client, especially when the volume of data is large.

Recommendation: Reduce the amount of data that needs to be scanned by index or limit

Sending data is not a network send, it is read from the hard disk

Send to network is writing to net

Using Temporary

A staging table is required to store the result set, usually because there is no index on the group by column. It is also possible because both group by and order by, but group by and order by columns are not the same

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6B/E3/wKiom1U49SfR1BUXAAPxCfN2-rk832.jpg "style=" float: none; "title=" Image 1.png "alt=" Wkiom1u49sfr1buxaapxcfn2-rk832.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6B/E0/wKioL1U49oWSDkpVAAK54gern7M347.jpg "style=" float: none; "title=" image 2.jpg "alt=" Wkiol1u49owsdkpvaak54gern7m347.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6B/E3/wKiom1U49Sfj3ShIAALM_OKHJJo070.jpg "style=" float: none; "title=" Image 3.jpg "alt=" Wkiom1u49sfj3shiaalm_okhjjo070.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6B/E0/wKioL1U49oaj61euAAIczDhsjbo032.jpg "style=" float: none; "title=" Image 4.png "alt=" Wkiol1u49oaj61euaaiczdhsjbo032.jpg "/>

This article is from the "Ke Xiao" blog, please be sure to keep this source http://kexl908.blog.51cto.com/605006/1637738

MySQL optimization--Ye Jinlong Teacher Lecture Notes