Optimization of SQL statements in hybrid MySQL, effectivemysql

Optimization of SQL statements in hybrid MySQL, effectivemysql
We recommend that you optimize SQL statements in objective MySQL.
This article mainly explains how to analyze SQL Performance, indexing principles, how to create appropriate indexes, and how to analyze performance bottlenecks of online systems.

Several auxiliary tools are also introduced:
Mysqldumpslow is used to analyze slow query logs;
Google's open-source mysql-slow-query-log-parser analyzes slow query logs;
MySQL Proxy is used in applications to collect SQL statements, QEP, and query execution time;
Open-source Maatkit checks duplicate indexes in the database;
Google's MySQL patch introduces SHOW INDEX_STATISTICS to analyze indexes;
MySQL patch microsecond-mysql-client can monitor SQL at a subtle level;
Open-source sqlstats plug-in, statistics SQL statements;

Electronic: http://download.csdn.net/detail/bbirdsky/8207119

The book is only 200 pages. The following is the directory of the book:

Chapter 1 DBA 5 minutes to 1
1.1 Identify performance issues 2
1.1.1 search for slow-running SQL statement 2
1.1.2 confirm inefficient query 3
1.2 Optimize Query 6
1.2.1 what should not be done 6
1.2.2 confirm optimization 7
1.2.3 correct method 7
1.2.4 alternative solution 9
1.2 Chapter 9

Chapter 11 basic analysis commands 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 chapter 27

Chapter 2 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 optimize data access 33
3.2.3 Table connection 35
3.2.4 result sorting 35
3.2.5 aggregation operation 35
3.3 about storage engine 36
3.4 indexing terminology 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 55

Chapter 4 Create a MySQL index 57
4.1 table 58 used in this chapter
4.2 existing indexes 59
4.3 Single Column index 61
4.3.1 syntax 61 for creating a single column Index
4.3.2 Use indexes to limit the number of rows read by queries: 62
4.3.3 connecting tables with indexes 64
4.3.4 understanding the Index base 66
4.3.5 use indexes for pattern matching 69
4.3.6 select a unique row 71
4.3.7 sorting results 73
4.4 multi-column indexes 75
4.4.1 determine which index to use 75
4.4.2 multi-column index syntax 79
4.4.3 creating better indexes 79
4.4.4 Index 82 for multiple columns
4.4.5 merge WHERE and order by statements 83
4.4.6 MySQL optimizer features 85
4.4.7 query prompt 88
4.4.8 complex query 92
4.5 impact of adding an index 93
4.5.1 DML impact 93
4.5.2 DDL impact 96
4.5.3 disk space impact 97
4.6 MySQL restrictions and limitations of less than 100
4.6.1 overhead-based optimizer 100
4.6.2 specify QEP 100
4.6.3 index statistics 100
4.6.4 function-based index 101
4.6.5 Multiple indexes on a table 101
4.7 summary of this Chapter 101

Chapter 2 creating better MySQL indexes 5th
5.1 better index 104
5.1.1 covering indexes 104
5.1.2 meaning of storage engine 109
5.1.3 local indexing 110
5.2 Summary of this Chapter 114

Chapter 2 MySQL configuration option 6th
6.1 memory-related system variables 118
6.1.1 key_buffer_size 120
6.1.2 namecode 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 related to basic tools 130
6.2.1 slow_query_logs 131
6.2.2 slow_query_log_file 131
6.2.3 general_log131
6.2.4 general_log_file 131
6.2.5 long_query_time 132
6.2.6 log_output 132
6.2.7 profills 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_packet134
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 2 SQL lifecycle 7th
7.1 intercept SQL statement 138
7.1.1 full log query 139
7.1.2 slow query log 140
7.1.3 binary log 142
7.1.4 list of processes 143
7.1.5 144 engine status
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 ins 148
7.1.11 MySQL Proxy 149
7.1.12 TCP/IP 149
7.2 identify problematic statements 149
7.2.1 slow query log analysis 152
7.2.2 TCP/IP analytics 154
7.3 statement execution 156
7.3.1 stage 156
7.3.2 time statistics 157
7.4 Statement Analysis 158
7.5 statement optimization 159
7.6 result verification 159
7.7 Summary of this Chapter 160

Chapter 1 hidden cheats for performance optimization 8th
8.1 index management optimization 162
8.1.1 integrated DDL statement 162
8.1.2 remove duplicate indexes 163
8.1.3 delete unused indexes 164
8.1.4 monitoring of invalid index 165
8.2 improvement of index columns 165
8.2.1 data type 165
8.2.2 column type 168
8.3 other SQL optimization 170
8.3.1 reduce SQL statement 171
8.3.2 simplified SQL statement 178
8.3.3 use MySQL replication 180
8.4 summary of this Chapter 181

Chapter 2 MySQL EXPLAIN commands 9th
9.1 syntax 184
9.2 explanation of each column 185
9.2.1 key 187
9.2.2 rows 187
9.2.3 possible_keys 190
9.2.4 key_len190
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 EXPLAIN output result 198
9.4 summary of this Chapter 201