Effective MySQL之SQL语句最优化

章节摘录

版权页：   4.5.2 DDL影响 随着表大小的不断增长，对性能的影响也不断加大。例如，在主表上添加索引平均需要20～30秒。 mysql>ALTER TABLE album ADD INDEX ml(album_type_id，country_id);Query OK,553875 rows affected(21.05 sec) 在以往版本中，ALTER语句的开销是阻塞其他语句，就像创建一个新版本的表那样。在这期间可以SELECT数据，但根据标准的升级法则，任何DML操作都会导致所有语句被阻塞。当表的大小有1G或者100G，这个阻塞时间可能会非常长。但比较近期的版本在包括MySQL产品方面和创新的解决方案方面都有了很多改进。 添加索引带来的影响并不总是一样，也会有些例外情况。InnoDB提供了快速创建索引的特性，从MySQL 5.1版本开始就可以在InnoDB的插件中使用了，并且在MySQL 5.5或更高版本中已经成为默认设置了。 其他搜索引擎也可以以不同方式来实现执行锁定的快速索引的创建，Tokutek就是其中的一个。 对磁盘空间的影响也是一个重要的考虑因素，尤其是当你在InnoDB中使用默认的公共表空间配置的时候。MySQL会为你的表创建一份副本。如果表的大小有200GB，那么在执行ALTERTABLE时你需要至少200GB额外的磁盘空间。使用InnoDB时，在执行期间这些额外的磁盘空间会被添加到公共表空间中。这部分磁盘空间在命令完成后不会被文件系统回收，而是当InnoDB需要额外磁盘空间时在内部被重复利用。尽管你可以调整策略让每个表用单独的表空间，但对于写操作密集的系统，这也是有影响的。 技巧 有一些技巧可以让阻塞操作减少到最低限度。你可以选择使用一个高可用性的容错度高的主表复制技术来支持在线变更表结构。比如近期Shlomi Noach介绍的oak-online-alter-table工具。 4.5.3磁盘空间影响 使用第2章介绍的INFORMATION SCHEMA.TABLES查询可以查看本章使用的album表的大小。

内容概要

Ronald Bradford是一位在关系型数据库领域拥有20多年丰富经验的专家。他拥有深厚的专业背景以及10年以上Ingres和Oracle系统的工作知识，在过去12年中他致力于MySQL——世界上最流行的开源数据库的发展。他曾在2009年被提名为MySQL社区成员和2010年的Oracle ACE Director，其咨询领域的专家背景以及多次在国际会议上的发言也为他赢得了广泛的国际知名度。他还是Planet MySQL(2010)最受欢迎的个人MySQL技术博客作者，并且是清华大学出版社引进并出版的《PHP+MySQL专家编程》一书的作者之一。

MySQL在被Oracle公司收购之后成为主要的数据库解决方案，并获得了更多社区推广的机会。Ronald是世界范围的Oracle用户组中最受欢迎的MySQL的受邀发言人，该用户组的范围遍及北美、南美、欧洲以及亚太地区。

书籍目录

目    录

第1章  DBA五分钟速成
1

1.1  识别性能问题
2

1.1.1  寻找运行缓慢的SQL语句
2

1.1.2  确认低效查询
3

1.2  优化查询
6

1.2.1  不应该做的事情
6

1.2.2  确认优化
7

1.2.3  正确的方式
7

1.2.4  备选的解决方案
9

1.2  本章小结
9

第2章  基本的分析命令
11

2.1  EXPLAIN命令
12

2.1.1  EXPLAIN PARTITIONS命令
14

2.1.2  EXPLAIN EXTENDED命令
15

2.2  SHOW CREATE TABLE命令
16

2.3  SHOW INDEXES命令
18

2.4  SHOW TABLE STATUS命令
19

2.5  SHOW STATUS命令
22

2.6  SHOW VARIABLES命令
25

2.7  INFORMATION_SCHEMA
26

2.8  本章小结
27

第3章  深入理解MySQL的索引
29

3.1  示例表
30

3.2  MySQL索引用法
31

3.2.1  数据完整性
32

3.2.2  优化数据访问
33

3.2.3  表连接
35

3.2.4  结果排序
35

3.2.5  聚合操作
35

3.3  关于存储引擎
36

3.4  索引专业术语
37

3.5  MySQL索引类型
38

3.5.1  索引数据结构理论
39

3.5.2  MySQL实现
43

3.6  MySQL分区
54

3.7  本章小结
55

第4章  创建MySQL索引
57

4.1  本章范例中用到的表
58

4.2  已有的索引
59

4.3  单列索引
61

4.3.1  创建单列索引的语法
61

4.3.2  利用索引限制查询读取的行数
62

4.3.3  使用索引连接表
64

4.3.4  理解索引的基数
66

4.3.5  使用索引进行模式匹配
69

4.3.6  选择唯一的行
71

4.3.7  结果排序
73

4.4  多列索引
75

4.4.1  确定使用何种索引
75

4.4.2  多列索引的语法
79

4.4.3  创建更好的索引
79

4.4.4  多个列上的索引
82

4.4.5  合并WHERE和ORDER BY语句
83

4.4.6  MySQL优化器的特性
85

4.4.7  查询提示
88

4.4.8  复杂查询
92

4.5  添加索引造成的影响
93

4.5.1  DML影响
93

4.5.2  DDL影响
96

4.5.3  磁盘空间影响
97

4.6  MySQL的限制和不足
100

4.6.1  基于开销的优化器
100

4.6.2  指定QEP
100

4.6.3  索引的统计信息
100

4.6.4  基于函数的索引
101

4.6.5  一个表上的多个索引
101

4.7  本章小结
101

第5章  创建更好的MySQL索引
103

5.1  更好的索引
104

5.1.1  覆盖索引
104

5.1.2  存储引擎的含义
109

5.1.3  局部索引
110

5.2  本章小结
114

第6章  MySQL配置选项
117

6.1  内存相关的系统变量
118

6.1.1  key_buffer_size
120

6.1.2  命名码缓冲区
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  有关基础工具的变量
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  其他优化变量
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  其他变量
134

6.5  本章小结
135

第7章  SQL的生命周期
137

7.1  截取SQL语句
138

7.1.1  全面查询日志
139

7.1.2  慢查询日志
140

7.1.3  二进制日志
142

7.1.4  进程列表
143

7.1.5  引擎状态
144

7.1.6  MySQL连接器
145

7.1.7  应用程序代码
146

7.1.8  INFORMATION_SCHEMA
148

7.1.9  PERFORMANCE_SCHEMA
148

7.1.10  SQL语句统计插件
148

7.1.11  MySQL Proxy
149

7.1.12  TCP/IP
149

7.2  识别有问题的语句
149

7.2.1  慢查询日志分析
152

7.2.2  TCP/IP分析
154

7.3  确认语句执行
156

7.3.1  环境
156

7.3.2  时间统计
157

7.4  语句分析
158

7.5  语句优化
159

7.6  结果验证
159

7.7  本章小结
160

第8章  性能优化之隐藏秘籍
161

8.1  索引管理优化
162

8.1.1  整合DDL语句
162

8.1.2  去除重复索引
163

8.1.3  删除不用的索引
164

8.1.4  监控无效的索引
165

8.2  索引列的改进
165

8.2.1  数据类型
165

8.2.2  列的类型
168

8.3  其他SQL优化
170

8.3.1  减少SQL语句
171

8.3.2  简化SQL语句
178

8.3.3  使用MySQL的复制功能
180

8.4  本章小结
181

第9章  MySQL EXPLAIN命令详解
183

9.1  语法
184

9.2  各列详解
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  解释EXPLAIN输出结果
198

9.4  本章小结
201

编辑推荐

《Effective MySQL之SQL语句最优化》提供了很多可以用于改进数据库和应用程序性能的最佳实践技巧，并对这些技巧做了详细的解释，希望能够通过一步步详细介绍SQL优化的方法，帮助读者分析和调优有问题的SQL语句。

作者简介

《Effective MySQL之SQL语句最优化》是由MySQL专家Ronald Bradford撰著，书

中提供了很多可以用于改进数据库和应用程序性能的最佳实践技巧，并对这些技巧

做了详细的解释。本书希望能够通过一步步详细介绍SQL优化的方法，帮助读者分

析和调优有问题的SQL语句。

主要内容

●　找出收集和诊断问题必备的分析命令

●　创建MySQL索引来改进查询性能

●　掌握MySQL的查询执行计划

●　找出影响查询执行和性能的关键配置变量

●　用SQL语句优化的生命周期来识别、确

认、分析然后优化SQL语句，并检查优

化的结果

●　学习使用不为常人所知的一些性能技巧

来改进索引效率并简化SQL语句

图书封面

---

 Effective MySQL之SQL语句最优化下载 更多精彩书评

---