深入浅出MySQL全文（文字版）.pdf

发布时间：2022-05-29 发布人：admin 分类：说明书资料大小：6.02M 资料格式：pdf 举报版权申诉

xiaoyu5256-9836758-4744302543315815578.pdf-第1页.png

第1页 / 共421页

xiaoyu5256-9836758-4744302543315815578.pdf-第2页.png

第2页 / 共421页

xiaoyu5256-9836758-4744302543315815578.pdf-第3页.png

第3页 / 共421页

xiaoyu5256-9836758-4744302543315815578.pdf-第4页.png

第4页 / 共421页

xiaoyu5256-9836758-4744302543315815578.pdf-第5页.png

第5页 / 共421页

xiaoyu5256-9836758-4744302543315815578.pdf-第6页.png

第6页 / 共421页

xiaoyu5256-9836758-4744302543315815578.pdf-第7页.png

第7页 / 共421页

xiaoyu5256-9836758-4744302543315815578.pdf-第8页.png

第8页 / 共421页

文本预览

http://showmecode.cn/links/book 目录第 1 部分基础篇第 1 章 MySQL 的安装与配置 .................................................... 1 1.1 MySQL 的下载................................................................................................................... 1 1.1.1 在 Windows 平台下下载 MySQL .......................................................................... 2 1.1.2 在 Linux 平台下下载 MySQL ............................................................................... 2 1.2 MySQL 的安装................................................................................................................... 5 1.2.1 在 Windows 平台下安装 MySQL .......................................................................... 5 1.2.2 在 Linux 平台下安装 MySQL ............................................................................. 10 1.3 MySQL 的配置................................................................................................................. 11 1.3.1 Windows 平台下配置 MySQL .............................................................................. 11 1.3.2 Linux 平台下配置 MySQL ................................................................................... 22 1.4 启动和关闭 MySQL 服务 ............................................................................................... 22 1.4.1 在 Windows 平台下启动和关闭 MySQL 服务 ................................................... 22 1.4.2 在 Linux 平台下启动和关闭 MySQL 服务 ........................................................ 23 1.5 小结.................................................................................................................................. 25 第 2 章 SQL 基础 ............................................................. 26 2.1 SQL 简介 .......................................................................................................................... 26 2.2 (My)SQL 使用入门 .......................................................................................................... 26 2.2.1 SQL 分类 ............................................................................................................... 26 2.2.2 DDL 语句 .............................................................................................................. 26 2.2.3 DML 语句 .............................................................................................................. 35 2.2.4 DCL 语句 ............................................................................................................... 50 2.3 帮助的使用 ...................................................................................................................... 51 2.3.1 按照层次看帮助 ................................................................................................... 52 2.3.2 快速查阅帮助 ....................................................................................................... 53 2.3.3 常用网址 ............................................................................................................... 54 2.4 小结.................................................................................................................................. 54 第 3 章 MySQL 支持的数据类型 ................................................. 55 3.1 数值类型.......................................................................................................................... 55 3.2 日期时间类型 .................................................................................................................. 61 3.3 字符串类型 ...................................................................................................................... 68 3.3.1 CHAR 和 VARCHAR 类型 ................................................................................... 69 3.3.2 BINARY 和 VARBINARY 类型 ........................................................................... 69 3.3.3 ENUM 类型 ........................................................................................................... 70 3.3.4 SET 类型................................................................................................................ 70 3.4 小结.................................................................................................................................. 71 第 4 章 MySQL 中的运算符 ..................................................... 72 4.1 算术运算符 ...................................................................................................................... 72 4.2 比较运算符 ...................................................................................................................... 73 4.3 逻辑运算符 ...................................................................................................................... 76 4.4 位运算符.......................................................................................................................... 77 4.5 运算符的优先级 .............................................................................................................. 79 1

http://showmecode.cn/links/book 4.6 小结.................................................................................................................................. 80 第 5 章常用函数............................................................. 81 5.1 字符串函数 ...................................................................................................................... 81 5.2 数值函数.......................................................................................................................... 84 5.3 日期和时间函数 .............................................................................................................. 86 5.4 流程函数.......................................................................................................................... 90 5.5 其他常用函数 .................................................................................................................. 92 5.6 小结.................................................................................................................................. 95 第 6 章图形化工具的使用 ..................................................... 96 6.1 MySQL Administrator....................................................................................................... 96 6.1.1 连接管理 ............................................................................................................... 96 6.1.2 健康检查 ............................................................................................................... 97 6.1.3 备份管理 ............................................................................................................... 99 6.1.4 Catalogs ................................................................................................................ 100 6.2 MySQL Query Brower .................................................................................................... 101 6.3 phpMyAdmin .................................................................................................................. 102 6.3.1 数据库管理 ......................................................................................................... 102 6.3.2 数据库对象管理 ................................................................................................. 103 6.3.3 权限管理 ............................................................................................................. 103 6.3.4 导入导出数据 ..................................................................................................... 104 6.4 小结................................................................................................................................ 106 第 2 部分开发篇第 7 章表类型（存储引擎）的选择 ............................................ 107 7.1 MySQL 存储引擎概述 ................................................................................................... 107 7.2 各种存储引擎的特性 .................................................................................................... 109 7.2.1 MyISAM .............................................................................................................. 110 7.2.2 InnoDB ................................................................................................................. 111 7.2.3 MEMORY ............................................................................................................ 116 7.2.4 MERGE ................................................................................................................ 119 7.3 如何选择合适的存储引擎 ............................................................................................ 121 7.4 小结................................................................................................................................ 122 第 8 章选择合适的数据类型 .................................................. 123 8.1 CHAR 与 VARCHAR..................................................................................................... 123 8.2 TEXT 与 BLOB .............................................................................................................. 124 8.3 浮点数与定点数 ............................................................................................................ 128 8.4 日期类型选择 ................................................................................................................ 130 8.5 小结................................................................................................................................ 131 第 9 章字符集.............................................................. 132 9.1 字符集概述 .................................................................................................................... 132 9.2 Unicode 简述 .................................................................................................................. 132 9.3 汉字及一些常见字符集 ................................................................................................ 134 9.4 怎样选择合适的字符集 ................................................................................................ 135 9.5 MySQL 支持的字符集简介 ........................................................................................... 135 9.6 MySQL 字符集的设置 ................................................................................................... 137 2

http://showmecode.cn/links/book 9.6.1 服务器字符集和校对规则 ................................................................................. 137 9.6.2 数据库字符集和校对规则 ................................................................................. 138 9.6.3 表字符集和校对规则 ......................................................................................... 138 9.6.4 列字符集和校对规则 ......................................................................................... 139 9.6.5 连接字符集和校对规则 ..................................................................................... 139 9.7 字符集的修改步骤 ........................................................................................................ 139 9.8 小结................................................................................................................................ 140 第 10 章索引的设计和使用 ................................................... 141 10.1 索引概述 ...................................................................................................................... 141 10.2 设计索引的原则 .......................................................................................................... 142 10.3 BTREE 索引与 HASH 索引 ........................................................................................ 143 10.4 小结.............................................................................................................................. 144 第 11 章视图.................................................................. 145 11.1 什么是视图 .................................................................................................................. 145 11.2 视图操作 ...................................................................................................................... 145 11.2.1 创建或者修改视图 ........................................................................................... 145 11.2.2 删除视图 ........................................................................................................... 147 11.2.3 查看视图 ........................................................................................................... 147 11.3 小结 .............................................................................................................................. 149 第 12 章存储过程和函数 ..................................................... 150 12.1 什么是存储过程和函数 .............................................................................................. 150 12.2 存储过程和函数的相关操作 ...................................................................................... 150 12.2.1 创建、修改存储过程或者函数 ....................................................................... 150 12.2.2 删除存储过程或者函数 ................................................................................... 154 12.2.3 查看存储过程或者函数 ................................................................................... 155 12.2.4 变量的使用 ....................................................................................................... 157 12.2.5 定义条件和处理 ............................................................................................... 157 12.2.6 光标的使用 ....................................................................................................... 160 12.2.7 流程控制 ........................................................................................................... 161 12.3 小结.............................................................................................................................. 166 第 13 章触发器............................................................. 167 13.1 创建触发器 .................................................................................................................. 167 13.2 删除触发器 .................................................................................................................. 170 13.3 查看触发器 .................................................................................................................. 170 13.4 触发器的使用 .............................................................................................................. 172 13.5 小结.............................................................................................................................. 172 第 14 章事务控制和锁定语句 ................................................. 173 14.1 LOCK TABLE 和 UNLOCK TABLE .......................................................................... 173 14.2 事务控制 ...................................................................................................................... 174 14.3 分布式事务的使用 ...................................................................................................... 180 14.3.1 分布式事务的原理 ........................................................................................... 180 14.3.2 分布式事务的语法 ........................................................................................... 181 14.3.3 存在的问题 ....................................................................................................... 182 3

http://showmecode.cn/links/book 14.4 小结.............................................................................................................................. 186 第 15 章 SQL 中的安全问题 ................................................... 187 15.1 SQL 注入简介 .............................................................................................................. 187 15.2 应用开发中可以采取的应对措施 .............................................................................. 188 15.2.1 PrepareStatement+Bind-variable ....................................................................... 188 15.2.2 使用应用程序提供的转换函数 ....................................................................... 188 15.2.3 自己定义函数进行校验 ................................................................................... 189 15.3 小结.............................................................................................................................. 190 第 16 章 SQL Mode 及相关问题 ................................................ 191 16.1 MySQL SQL Mode 简介 .............................................................................................. 191 16.2 常用的 SQL Mode ....................................................................................................... 196 16.3 SQL Mode 在迁移中如何使用 .................................................................................... 196 16.4 小结.............................................................................................................................. 198 第 3 部分优化篇第 17 章常用 SQL 技巧和常见问题 ............................................. 199 17.1 正则表达式的使用 ...................................................................................................... 199 17.2 巧用 RAND()提取随机行 ........................................................................................... 202 17.3 利用 GROUP BY 的 WITH ROLLUP 子句做统计 ................................................... 203 17.4 用 BIT GROUP FUNCTIONS 做统计 ........................................................................ 205 17.5 数据库名、表名大小写问题 ...................................................................................... 207 17.6 使用外键需要注意的问题 .......................................................................................... 207 17.7 小结.............................................................................................................................. 209 第 18 章 SQL 优化 ........................................................... 210 18.1 优化 SQL 语句的一般步骤 ........................................................................................ 210 18.1.1 通过 show status 命令了解各种 SQL 的执行频率 ......................................... 210 18.1.2 定位执行效率较低的 SQL 语句 ..................................................................... 211 18.1.3 通过 EXPLAIN 分析低效 SQL 的执行计划： .............................................. 211 18.1.4 确定问题，并采取相应的优化措施： ........................................................... 212 18.2 索引问题 ...................................................................................................................... 213 18.2.1 索引的存储分类 ............................................................................................... 213 18.2.2 MySQL 如何使用索引 ...................................................................................... 214 18.2.3 查看索引使用情况 ........................................................................................... 218 18.3 两个简单实用的优化方法 .......................................................................................... 219 18.3.1 定期分析表和检查表 ....................................................................................... 219 18.3.2 定期优化表 ....................................................................................................... 220 18.4 常用 SQL 的优化 ........................................................................................................ 221 18.4.1 大批量插入数据 ............................................................................................... 221 18.4.2 优化 INSERT 语句 ........................................................................................... 222 18.4.3 优化 GROUP BY 语句 ..................................................................................... 223 18.4.4 优化 ORDER BY 语句： ................................................................................. 223 18.4.5 优化嵌套查询 ................................................................................................... 224 18.4.6 MySQL 如何优化 OR 条件 .............................................................................. 225 18.4.7 使用 SQL 提示 ................................................................................................. 227 18.5 小结.............................................................................................................................. 229 4

http://showmecode.cn/links/book 第 19 章优化数据库对象 ..................................................... 230 19.1 优化表的数据类型 ...................................................................................................... 230 19.2 通过拆分提高表的访问效率 ...................................................................................... 232 19.3 逆规范化 ...................................................................................................................... 233 19.4 使用中间表提高统计查询速度 .................................................................................. 234 19.5 小结.............................................................................................................................. 235 第 20 章锁问题............................................................. 236 20.1 MySQL 锁概述............................................................................................................. 236 20.2 MyISAM 表锁 .............................................................................................................. 236 20.2.1 查询表级锁争用情况 ....................................................................................... 237 20.2.2 MySQL 表级锁的锁模式 .................................................................................. 237 20.2.3 如何加表锁 ....................................................................................................... 238 20.2.4 并发插入（Concurrent Inserts） ..................................................................... 240 20.2.5 MyISAM 的锁调度 ........................................................................................... 242 20.3 InnoDB 锁问题 ............................................................................................................. 242 20.3.1 背景知识 ........................................................................................................... 242 20.3.2 获取 InnoDB 行锁争用情况 ............................................................................ 244 20.3.3 InnoDB 的行锁模式及加锁方法 ...................................................................... 246 20.3.4 InnoDB 行锁实现方式 ...................................................................................... 249 20.3.5 间隙锁（Next-Key 锁） .................................................................................. 253 20.3.6 恢复和复制的需要，对 InnoDB 锁机制的影响 ............................................ 255 20.3.7 InnoDB 在不同隔离级别下的一致性读及锁的差异 ...................................... 260 20.3.8 什么时候使用表锁 ........................................................................................... 262 20.3.9 关于死锁 ........................................................................................................... 262 20.4 小结.............................................................................................................................. 268 第 21 章优化 MySQL Server .................................................. 270 21.1 查看 MySQL Server 参数 ........................................................................................... 270 21.2 影响 MySQL 性能的重要参数 ................................................................................... 273 21.2.1 key_buffer_size 的设置 ..................................................................................... 273 21.2.2 table_cache 的设置 ............................................................................................ 275 21.2.3 innodb_buffer_pool_size 的设置 ....................................................................... 278 21.2.4 innodb_flush_log_at_trx_commit 的设置 ......................................................... 278 21.2.5 innodb_additional_mem_pool_size 的设置....................................................... 279 21.2.6 innodb_lock_wait_timeout 的设置 .................................................................... 279 21.2.7 innodb_support_xa 的设置 ................................................................................ 279 21.2.8 innodb_log_buffer_size 的设置 ......................................................................... 279 21.2.9 innodb_log_file_size 的设置 ............................................................................. 280 21.3 小结.............................................................................................................................. 280 第 22 章磁盘 I/O 问题 ....................................................... 281 22.1 使用磁盘阵列 .............................................................................................................. 281 22.1.1 常见 RAID 级别及其特性 ............................................................................... 281 22.1.2 如何选择 RAID 级别 ....................................................................................... 282 22.2 虚拟文件卷或软 RAID ............................................................................................... 282 22.3 使用 Symbolic Links 分布 I/O .................................................................................... 282 5

http://showmecode.cn/links/book 22.4 禁止操作系统更新文件的 atime 属性 ...................................................................... 283 22.5 用裸设备（Raw Device）存放 InnoDB 的共享表空间............................................ 284 22.6 小结.............................................................................................................................. 284 第 23 章应用优化........................................................... 285 23.1 使用连接池 .................................................................................................................. 285 23.2 减少对 MySQL 的访问 ............................................................................................... 285 23.2.1 避免对同一数据做重复检索 ........................................................................... 285 23.2.2 使用查询缓存 ................................................................................................... 285 23.2.3 增加 CACHE 层 ............................................................................................... 286 23.3 负载均衡 ...................................................................................................................... 287 23.3.1 利用 MySQL 复制分流查询操作 .................................................................... 287 23.3.2 采用分布式数据库架构 ................................................................................... 287 23.4 其他优化措施 .............................................................................................................. 287 23.5 小结.............................................................................................................................. 288 第 4 部分管理维护篇第 24 章 MySQL 高级安装和升级 ............................................... 289 24.1 Linux/UNIX 下的安装 ................................................................................................. 289 24.1.1 安装包比较 ....................................................................................................... 289 24.1.2 安装 RPM 包 .................................................................................................... 290 24.1.3 安装二进制包 ................................................................................................... 290 24.1.4 安装源码包 ....................................................................................................... 291 24.1.5 参数设置方法 ................................................................................................... 291 24.2 源码包安装的性能考虑 .............................................................................................. 293 24.2.1 去掉不需要的模块 ........................................................................................... 293 24.2.2 只选择要使用的字符集 ................................................................................... 293 24.2.3 使用静态编译以提高性能 ............................................................................... 293 24.3 升级 MySQL................................................................................................................ 294 24.4 MySQL 降级................................................................................................................. 295 24.5 小结.............................................................................................................................. 295 第 25 章 MySQL 中的常用工具 ................................................. 296 25.1 mysql（客户端连接工具） ......................................................................................... 296 25.2 myisampack（MyISAM 表压缩工具） ...................................................................... 304 25.3 mysqladmin（MySQL 管理工具） ............................................................................. 306 25.4 mysqlbinlog（日志管理工具） ................................................................................... 307 25.5 mysqlcheck（MyISAM 表维护工具） ....................................................................... 312 25.6 mysqldump（数据导出工具） .................................................................................... 313 25.7 mysqlhotcopy（MyISAM 表热备份工具） ................................................................ 318 25.8 mysqlimport（数据导入工具） .................................................................................. 319 25.9 mysqlshow（数据库对象查看工具） ......................................................................... 320 25.10 perror（错误代码查看工具） ................................................................................... 322 25.11 replace（文本替换工具） ......................................................................................... 323 25.12 小结............................................................................................................................ 324 第 26 章 MySQL 日志 ......................................................... 325 26.1 错误日志 ...................................................................................................................... 325 6

http://showmecode.cn/links/book 26.2 二进制日志 .................................................................................................................. 326 26.2.1 日志的位置和格式 ........................................................................................... 326 26.2.2 日志的读取 ....................................................................................................... 326 26.2.3 日志的删除 ....................................................................................................... 327 26.2.4 其他选项 ........................................................................................................... 330 26.3 查询日志 ...................................................................................................................... 330 26.3.1 日志的位置和格式 ........................................................................................... 330 26.3.2 日志的读取 ....................................................................................................... 331 26.4 慢查询日志 .................................................................................................................. 331 26.4.1 文件位置和格式 ............................................................................................... 331 26.4.2 日志的读取 ....................................................................................................... 332 26.4.3 其他选项 ........................................................................................................... 333 26.5 小结.............................................................................................................................. 333 第 27 章备份与恢复......................................................... 335 27.1 备份/恢复策略............................................................................................................. 335 27.2 逻辑备份和恢复 .......................................................................................................... 335 27.2.1 备份 ................................................................................................................... 335 27.2.2 完全恢复 ........................................................................................................... 336 27.2.3 基于时间点恢复 ............................................................................................... 338 27.2.4 基于位置恢复 ................................................................................................... 338 27.3 物理备份和恢复 .......................................................................................................... 338 27.3.1 冷备份 ............................................................................................................... 339 27.3.2 热备份 ............................................................................................................... 339 27.4 表的导入导出 .............................................................................................................. 341 27.4.1 导出 ................................................................................................................... 342 27.4.2 导入 ................................................................................................................... 345 27.5 小结.............................................................................................................................. 349 第 28 章 MySQL 权限与安全 ................................................... 350 28.1 MySQL 权限管理......................................................................................................... 350 28.1.1 权限系统的工作原理 ....................................................................................... 350 28.1.2 权限表的存取 ................................................................................................... 350 28.2 账号管理 ...................................................................................................................... 353 28.3 MySQL 安全问题......................................................................................................... 363 28.3.1 操作系统相关的安全问题 ............................................................................... 363 28.3.2 数据库相关的安全问题 ................................................................................... 366 28.4 其他安全设置选项 ...................................................................................................... 384 28.4.1 old-passwords ..................................................................................................... 384 28.4.2 safe-user-create................................................................................................... 385 28.4.3 secure-auth ......................................................................................................... 387 28.4.4 skip-grant-tables ................................................................................................. 387 28.4.5 skip-network ....................................................................................................... 388 28.4.6 skip-show-database ............................................................................................ 388 28.5 小结.............................................................................................................................. 389 第 29 章 MySQL 复制 ......................................................... 390 7

http://showmecode.cn/links/book 29.1 安装配置 ...................................................................................................................... 390 29.2 主要复制启动选项 ...................................................................................................... 394 29.2.1 log-slave-updates................................................................................................ 394 29.2.2 master-connect-retry........................................................................................... 394 29.2.3 read-only ............................................................................................................ 394 29.2.4 指定复制的数据库或者表 ............................................................................... 396 29.2.5 slave-skip-errors ................................................................................................. 397 29.3 日常管理维护 .............................................................................................................. 397 29.3.1 查看从服务器状态 ........................................................................................... 398 29.3.2 主从服务器同步维护 ....................................................................................... 399 29.3.3 从服务器复制出错的处理 ............................................................................... 399 29.3.4 log event entry exceeded max_allowed_packet 的处理 .................................... 401 29.3.5 多主复制时的自增长变量冲突问题 ............................................................... 401 29.3.6 查看从服务器的复制进度 ............................................................................... 404 29.4 切换主从服务器 .......................................................................................................... 406 29.5 小结.............................................................................................................................. 407 第 30 章 MySQL Cluster ...................................................... 408 30.1 MySQL Cluster 架构 .................................................................................................... 408 30.2 MySQL Cluster 的配置 ................................................................................................ 409 30.2.1 MySQL Cluster 的版本支持 ............................................................................. 409 30.2.2 管理节点配置步骤 ........................................................................................... 410 30.2.3 SQL 节点和数据节点的配置 ........................................................................... 411 30.3 开始使用 Cluster ......................................................................................................... 412 30.3.1 Cluster 的启动 ................................................................................................... 412 30.3.2 Cluster 的测试 ................................................................................................... 414 30.3.3 Cluster 的关闭 ................................................................................................... 417 30.4 维护 Cluster ................................................................................................................. 418 30.4.1 数据备份 ........................................................................................................... 418 30.4.2 数据恢复 ........................................................................................................... 419 30.4.3 日志管理 ........................................................................................................... 421 30.5 小结.............................................................................................................................. 423 第 31 章 MySQL 常见问题和应用技巧 ........................................... 424 31.1 忘记 MySQL 的 root 密码 .......................................................................................... 424 31.2 如何处理 MyISAM 存储引擎的表损坏 .................................................................... 425 31.3 MyISAM 表超过 4GB 无法访问的问题 ..................................................................... 426 31.4 数据目录磁盘空间不足的问题 .................................................................................. 427 31.4.1 对于 MyISAM 存储引擎的表 ......................................................................... 427 31.4.2 对于 InnoDB 存储引擎的表 ............................................................................ 428 31.5 DNS 反向解析的问题 .................................................................................................. 428 31.6 mysql.sock 丢失后如何连接数据库 ............................................................................ 428 31.7 同一台服务器运行多个 MySQL 数据库 ................................................................... 429 31.8 客户端怎么访问内网数据库 ...................................................................................... 429 31.9 小结.............................................................................................................................. 432 8

分享到：

赞收藏

资料库

深入浅出MySQL全文（文字版）.pdf

相关推荐

开发技术

热门标签

最新资料