Recommended this SQL optimization book, "Effective MySQL SQL statement optimization."
The main explanation: How to analyze the performance of SQL, index principle, how to create appropriate index, how to analyze the performance bottleneck of the line system.
Several additional tools are also introduced:
Mysqldumpslow to analyze the slow query log;
Google open-source mysql-slow-query-log-parser analysis slow query log;
The application uses MySQL proxy to collect SQL statements, QEP, query execution time;
Open source Maatkit checks for duplicate indexes in the database;
Google's MySQL patch, introduced by show Index_statistics to analyze the index;
MySQL patch microsecond-mysql-client can be a subtle level of monitoring of SQL;
Open source Sqlstats plugin, statistical SQL statement;
Electronic version: http://download.csdn.net/detail/bbirdsky/8207119
Book is not thick also on 200 pages, the following is the directory of this book:
1th dba five-minute crash 1
1.1 Identifying Performance Issues 2
1.1.1 Looking for SQL statements that run slowly 2
1.1.2 Confirm Inefficient Query 3
1.2 Optimization Query 6
What 1.2.1 should not do 6
1.2.2 Confirm Optimization 7
1.2.3 The right way 7
1.2.4 Alternative Solution 9
1.2 Summary of this Chapter 9
2nd. Basic Analysis Command 11
2.1 Explain Command 12
2.1.1 EXPLAIN Partitions Command 14
2.1.2 EXPLAIN Extended Command 15
2.2 SHOW CREATE Table command 16
2.3 SHOW Indexes Command 18
2.4 SHOW TABLE Status Command 19
2.5 SHOW Status Command 22
2.6 SHOW Variables Command 25
2.7 Information_schema 26
2.8 Summary of this chapter 27
3rd. In-depth understanding of MySQL Index 29
3.1 Example Table 30
3.2 MySQL Index usage 31
3.2.1 Data Integrity 32
3.2.2 Optimizing data Access 33
3.2.3 Table Connection 35
3.2.4 Result Sort 35
3.2.5 Aggregation Operations 35
3.3 About the storage engine 36
3.4 Index Professional Terms 37
3.5 MySQL Index Type 38
3.5.1 index data Structure theory 39
3.5.2 MySQL Implementation 43
3.6 MySQL Partition 54
3.7 Summary of this chapter 55
4th. Create MySQL Index 57
4.1 Table 58 In the example of this chapter
4.2 Existing Indexes 59
4.3 Single-column index 61
4.3.1 Creating a single-column index syntax 61
4.3.2 Number of rows read by using index limit query 62
4.3.3 using the index join table 64
4.3.4 Understanding the cardinality of the index 66
4.3.5 using indexes for pattern matching 69
4.3.6 Select a unique row 71
4.3.7 Result Sort 73
4.4 Multi-column index 75
4.4.1 Determining which index to use 75
4.4. Syntax for 2 multi-column Indexes 79
4.4.3 Creating a better index 79
4.4. Index 82 on more than 4 columns
4.4.5 merging where and order by statements 83
4.4.6 features of the MySQL optimizer 85
4.4.7 Query Tips 88
4.4.8 Complex Query 92
4.5 Effects of adding indexes 93
4.5.1 DML Impact 93
4.5.2 DDL Impact 96
4.5.3 Disk space Impact 97
4.6 MySQL limitations and less than 100
4.6.1 cost-based optimizer 100
4.6.2 designation QEP 100
4.6.3 Statistics for Indexes 100
4.6.4 Function-based index 101
4.6. Multiple indexes on 51 tables 101
4.7 Summary of this chapter 101
5th. Create better MySQL index 103
5.1 Better Index 104
5.1.1 Coverage Index 104
What the 5.1.2 storage engine means 109
5.1.3 Local Index 110
5.2 Summary of this chapter 114
6th MySQL configuration options 117
6.1 Memory-related system variables 118
6.1.1 Key_buffer_size 120
6.1.2 Name Code Buffer 121
6.1.3 Innodb_buffer_pool_size 122
6.1.4 Innodb_additional_mem_pool_size 124
6.1.5 Query_cache_size 125
6.1.6 max_heap_table_size 126
6.1.7 Tmp_table_size 127
6.1.8 Join_buffer_size 129
6.1.9 Sort_buffer_size 129
6.1.10 read_buffer_size 130
6.1.11 read_rnd_buffer_size 130
6.2 Variables about the underlying tool 130
6.2.1 Slow_query_log 131
6.2.2 Slow_query_log_file 131
6.2.3 General_log 131
6.2.4 General_log_file 131
6.2.5 Long_query_time 132
6.2.6 Log_output 132
6.2.7 Profiling 132
6.3 Other optimization variables 133
6.3.1 Optimizer_switch 133
6.3.2 Default_storage_engine 133
6.3.3 Max_allowed_packet 134
6.3.4 Sql_mode 134
6.3.5 Innodb_strict_mode 134
6.4 Other variables 134
6.5 Summary of this chapter 135
Chapter 7th life Cycle of SQL 137
7.1 Intercepting SQL statements 138
7.1.1 Full Query Log 139
7.1.2 Slow query Log 140
7.1.3 Binary Log 142
7.1.4 Process List 143
7.1.5 Engine Status 144
7.1.6 MySQL connector 145
7.1.7 Application Code 146
7.1.8 INFORMATION_SCHEMA 148
7.1.9 Performance_schema 148
7.1.10 SQL statement Statistics Plugin 148
7.1.11 MySQL Proxy 149
7.1.12 TCP/IP 149
7.2 Identifying the problematic statement 149
7.2.1 Slow query Log Analysis 152
7.2.2 TCP/IP analysis 154
7.3 Confirm Statement Execution 156
7.3.1 Environment 156
7.3.2 Time Statistics 157
7.4 Statement Analysis 158
7.5 Statement Optimization 159
7.6 Results Validation 159
7.7 Summary of this chapter 160
8th. Performance Optimization Hidden cheats 161
8.1 Index management Optimization 162
8.1.1 Consolidating DDL Statements 162
8.1.2 Removing duplicate Indexes 163
8.1.3 Deleting unused indexes 164
8.1.4 Monitoring Invalid Index 165
8.2 Improvement of indexed columns 165
8.2.1 Data Type 165
Type of 8.2.2 column 168
8.3 Other SQL Optimizations 170
8.3.1 Reducing SQL statements 171
8.3.2 Simplifying SQL statements 178
8.3.3 using MySQL's copy function 180
8.4 Summary of this chapter 181
9th. MySQL Explain command detailed 183
9.1 Syntax 184
9.2 Detailed Columns 185
9.2.1 Key 187
9.2.2 Rows 187
9.2.3 Possible_keys 190
9.2.4 Key_len 190
9.2.5 Table 192
9.2.6 Select_type 193
9.2.7 Partitions 194
9.2.8 Extra 195
9.2.9 ID 197
9.2.10 ref 197
9.2.11 Filtered 197
9.2.12 Type 198
9.3 Interpreting explain output 198
9.4 Summary of this chapter 201
Effective MySQL SQL statement optimization
