[In-depth understanding of MySQL series]

[In-depth understanding of MySQL series] Note: This series of articles mainly discusses some knowledge points related to MySQL memory utilization and execution plan, thus laying a better foundation for MySQL optimization.
Author/translator: Ye Jinrong (Email:), Source: http://imysql.cn, reproduced please indicate as/translator and source, and cannot be used for commercial purposes, offenders must investigate.
Note: This series of articles mainly discusses some knowledge points related to MySQL memory utilization and execution plan, thus laying a better foundation for MySQL optimization.
Environment description OS: AS4U6, 2.6.9-67.0.15.ELsmp, 16G Ram, MD3000 array, xfs file system
MySQL 5.1.26-percona (innodb plugin, innodb stat, user stat, msl, show patch, acc-pslist patch)
MySQL main configuration parameters
Default_table_type = innodb
Log_slow_queries
Long_query_time = 0.001.
Log_slow_verbosity = query_plan, innodb
Innodb_data_file_path = ibdata1: 1024 M: autoextend
Innodb_log_file_size = 400 M
Innodb_log_files_in_group = 3
Innodb_file_per_table
Innodb_file_format = "Barracuda"
Other parameters are default values. Therefore, the values of other memory parameters are as follows:
Innodb_buffer_pool_size = 8388608
Join_buffer_size = 131072
Key_buffer_size = 8388600
Max_heap_table _size = 16777216
Query_cache_size = 0
Read_buffer_size = 131072
Read_rnd_buffer_size = 262144
Sort_buffer_size = 2097144
Tmp_table_size = 16777216
If not specified in all subsequent examples, the InnoDB engine will be used for testing related tables.
1. sort buffer parameters: sort_buffer_size, read_rnd_buffer_size
Explain select SQL _NO_CACHE * FROM T1 WHERE ID <10000 ORDER BY ID DESC;
+ ---- + ------------- + ------- + --------------- + --------- + ------ + ------- + ------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------- + --------------- + --------- + ------ + ------- + ------------- +
| 1 | SIMPLE | T1 | range | PRIMARY | 8 | NULL | 14872 | Using where |
+ ---- + ------------- + ------- + --------------- + --------- + ------ + ------- + ------------- +
# Query_time: 0.207893 Lock_time: 0.000056 Rows_sent: 9999 Rows_examined: 9999
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No
# Filesort: No Filesort_on_disk: No Merge_passes: 0
# InnoDB_IO_r_ops: 91 InnoDB_IO_r_bytes: 1490944 InnoDB_IO_r_wait: 0.083391
# InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000
# InnoDB_pages_distinct: 93
SELECT SQL _NO_CACHE * FROM T1 WHERE ID <10000 ORDER BY ID DESC;
Because primary key/index sorting is performed, no temporary table is required.
1.2 Use InnoDB to sort explain select SQL _NO_CACHE * FROM T1 WHERE ID <10000 ORDER BY C1 DESC;
+ ---- + ------------- + ------- + --------------- + --------- + ------ + ------- + ------------------------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------- + --------------- + --------- + ------ + ------- + ------------------------------- +
| 1 | SIMPLE | T1 | range | PRIMARY | 8 | NULL | 14872 | Using where; Using filesort |
+ ---- + ------------- + ------- + --------------- + --------- + ------ + ------- + ------------------------------- +
# Query_time: 0.120879 Lock_time: 0.000023 Rows_sent: 9999 Rows_examined: 19998
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No
# Filesort: Yes Filesort_on_disk: Yes Merge_passes: 1
# InnoDB_IO_r_ops: 0 InnoDB_IO_r_bytes: 0 InnoDB_IO_r_wait: 0.000000
# InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000
# InnoDB_pages_distinct: 93
SELECT SQL _NO_CACHE * FROM T1 WHERE ID <10000 ORDER BY C1 DESC;
Because C1 is not an index Field, additional sorting is required, and disk files are also used because sort_buffer and read_rnd_buffer are not large enough.
Increase sort_buffer_size and check again
Set session sort_buffer_size = 1024*1024*5;
Run the test again and the result changes.
# Query_time: 0.080727 Lock_time: 0.000030 Rows_sent: 9999 Rows_examined: 19998
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No
# Filesort: Yes Filesort_on_disk: No Merge_passes: 0
# InnoDB_IO_r_ops: 0 InnoDB_IO_r_bytes: 0 InnoDB_IO_r_wait: 0.000000
# InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000
# InnoDB_pages_distinct: 93
SELECT SQL _NO_CACHE * FROM T1 WHERE ID <10000 ORDER BY C1 DESC;
As you can see, Filesort_on_disk is changed to No, and Merge_passes is also changed to 0, indicating that the disk files are directly sorted in the memory instead of being used.
1.3 increase read_rnd_buffer_size to see if there is any impact on filesort. explain select SQL _NO_CACHE * FROM T1 AS T1 WHERE ID <10000 ORDER BY C1 DESC;
+ ---- + ------------- + ------- + --------------- + --------- + ------ + ------- + ------------------------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------- + --------------- + --------- + ------ + ------- + ------------------------------- +
| 1 | SIMPLE | T1 | range | PRIMARY | 8 | NULL | 14872 | Using where; Using filesort |
+ ---- + ------------- + ------- + --------------- + --------- + ------ + ------- + ------------------------------- +
# Query_time: 0.103654 Lock_time: 0.000045 Rows_sent: 9999 Rows_examined: 19998
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No
# Filesort: Yes Filesort_on_disk: Yes Merge_passes: 1
# InnoDB_IO_r_ops: 0 InnoDB_IO_r_bytes: 0 InnoDB_IO_r_wait: 0.000000
# InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000
# InnoDB_pages_distinct: 93
SELECT SQL _NO_CACHE * FROM T1 AS T1 WHERE ID <10000 ORDER BY C1 DESC;
The specific process is no longer repeated, and the result is from 1 M to 512 M. It has never changed, and does not help filesort.