12694| 221
|
网易技术部-深入浅出MySQL数据库开发、优化与管理维护 |
网易技术部-深入浅出MySQL数据库开发、优化与管理维护
第一篇开发篇.............................................................................................................................8 第1 章帮助的使用...........................................................................................................................8 1.1 按照层次看帮助..................................................................................................................8 1.2 快速查阅帮助....................................................................................................................10 第2 章表类型(存储引擎)的选择.............................................................................................11 2.1 Mysql 存储引擎概述..........................................................................................................11 2.2 各种存储引擎的特性........................................................................................................11 2.3 如何选择合适的存储引擎................................................................................................12 第3 章选择合适的数据类型.........................................................................................................13 3.1 选择数据类型的基本原则................................................................................................13 3.2 固定长度数据列与可变长度的数据列........................................................................... 13 3.2.1 char 与varchar.........................................................................................................13 3.2.2 text 和blob...............................................................................................................15 3.3 浮点数与定点数................................................................................................................15 第4 章字符集................................................................................................................................16 4.1 字符集概述........................................................................................................................16 4.2 Mysql 支持的字符集简介................................................................................................. 17 4.3 Unicode 简述...................................................................................................................... 17 4.4 怎样选择合适的字符集....................................................................................................17 4.5 Mysql 字符集的设置......................................................................................................... 18 第5 章索引的设计和使用.............................................................................................................18 5.1 Mysql 索引概述..................................................................................................................18 5.2 设计索引的原则................................................................................................................19 5.3 btree 索引与hash 索引...................................................................................................... 20 5.4 Mysql 如何使用索引......................................................................................................... 21 第6 章锁机制和事务控制.............................................................................................................21 6.1 如何加锁............................................................................................................................21 6.2 死锁...................................................................................................................................22 6.3 事务控制............................................................................................................................22 第7 章SQL 中的安全问题............................................................................................................29 7.1 SQL 注入简述....................................................................................................................29 7.2 开发中可以采取的措施....................................................................................................30 7.2.1 prepareStatement + Bind-variable ........................................................................... 30 7.2.2 使用应用程序提供的转换函数:........................................................................ 31 7.2.3 自己定义函数进行校验........................................................................................ 31 第8 章SQL Mode 及相关问题......................................................................................................31 8.1 Mysql SQL Mode 简介.......................................................................................................31 8.2 SQL Mode 与可移植性......................................................................................................33 8.3 SQL Mode 与数据效验......................................................................................................34 第9 章常用SQL 技巧...................................................................................................................34 9.1 检索包含最大/最小值的行.............................................................................................. 34 9.2 巧用rand()/rand(n)提取随机行........................................................................................34 9.3 利用group by 的with rollup 子句做统计...................................................................... 35 9.4 用bit group functions 做统计........................................................................................... 36 第10 章其他需注意的问题...........................................................................................................39 10.1 数据库名、表名大小写问题..........................................................................................39 10.2 使用外键需注意的地方..................................................................................................41 第二篇优化篇................................................................................................................................45 第11 章SQL 优化.......................................................................................................................... 45 11.1 优化SQL 的一般步骤....................................................................................................45 11.1.1 通过show status 和应用特点了解各种SQL 的执行频率................................ 45 11.1.2 定位执行效率较低的SQL 语句:.....................................................................46 11.1.3 通过EXPLAIN 分析低效SQL 的执行计划:..................................................46 11.1.4 确定问题,并采取相应的优化措施:.............................................................. 47 11.2 索引问题..........................................................................................................................48 11.2.1 索引的存储分类...................................................................................................48 10.2.2 MySQL 如何使用索引....................................................................................... 49 10.2.3 查看索引使用情况......................................................................................... 49 11.3 两个简单实用的优化方法..............................................................................................50 11.3.1 定期分析表:.......................................................................................................50 11.3.2 使用optimize table :...........................................................................................50 11.4 常用SQL 的优化............................................................................................................ 51 11.4.1 大批量插入数据:...............................................................................................51 11.4.2 优化insert 语句:................................................................................................52 11.4.3 优化group by 语句:.......................................................................................... 52 11.4.4 优化order by 语句:........................................................................................... 52 11.4.5 优化join 语句:...................................................................................................53 11.4.6 mysql 如何优化or 条件:................................................................................... 54 11.4.7 查询优先还是更新(insert、update、delete)优先:.....................................54 11.4.8 使用SQL 提示:.................................................................................................55 11.5 其他优化措施..................................................................................................................56 第12 章优化数据库对象...............................................................................................................56 12.1 优化表的数据类型..........................................................................................................56 12.2 通过拆分,提高表的访问效率..................................................................................... 57 12.3 逆规范化..........................................................................................................................57 12.4 使用冗余统计表..............................................................................................................57 12.5 选择更合适的表类型......................................................................................................58 第13 章锁问题...............................................................................................................................58 13.1 获取锁等待情况..............................................................................................................58 13.2 什么情况下使用表锁......................................................................................................59 13.3 什么情况下使用行锁......................................................................................................59 13.4 insert …select …带来的问题..........................................................................................60 13.5 next-key 锁对并发插入的影响....................................................................................... 61 13.6 隔离级别对并发插入的影响..........................................................................................61 13.7 如何减少锁冲突..............................................................................................................62 第14 章优化Mysql Server............................................................................................................63 14.1 查看Mysql server 当前参数...........................................................................................63 14.2 影响Mysql 性能的重要参数......................................................................................... 63 14.2.1 key_buffer_size 的设置.........................................................................................63 14.2.2 table_cache 的设置................................................................................................65 14.2.3 innodb_buffer_pool_size 的设置:...................................................................... 65 14.2.4 innodb_flush_log_at_trx_commit 的设置:........................................................ 65 14.2.5 innodb_additional_mem_pool_size :...................................................................66 14.2.6 innodb_table_locks:............................................................................................ 66 14.2.7 innodb_lock_wait_timeout:................................................................................ 66 14.2.8 innodb_support_xa:.............................................................................................67 14.2.9 innodb_doublewrite:........................................................................................... 67 14.2.10 innodb_log_buffer_size:...................................................................................... 67 14.2.11 innodb_log_file_size :........................................................................................67 第15 章I/O 问题.............................................................................................................................67 15.1 使用磁盘阵列或虚拟文件卷分布I/O...........................................................................68 15.2 使用Symbolic Links 分布I/O........................................................................................68 第16 章应用优化...........................................................................................................................69 16.1 使用连接池......................................................................................................................69 16.2 减少对Mysql 的访问..................................................................................................... 70 16.2.1 避免对同一数据做重复检索:.......................................................................... 70 16.2.2 使用mysql query cache:................................................................................... 70 16.2.3 加cache 层:........................................................................................................71 16.3 负载均衡..........................................................................................................................71 16.3.1 利用mysql 复制分流查询操作:......................................................................71 16.3.2 采用分布式数据库架构:.................................................................................. 71 第三篇管理维护篇.........................................................................................................................73 第17 章mysql 安装升级................................................................................................................73 17.1 安装.................................................................................................................................73 17.1.1 安装方法比较.....................................................................................................73 17.1.2 rpm 安装步骤......................................................................................................74 17.1.3 二进制安装步骤.................................................................................................74 17.2 源码安装步骤..................................................................................................................75 17.3 源码安装的性能考虑:..................................................................................................75 17.3.1 去掉不需要的模块:.........................................................................................75 17.3.2 只选择要使用的字符集:................................................................................ 76 17.3.3 使用pgcc 编译:...............................................................................................76 17.3.4 使用静态编译以提高性能:............................................................................ 77 17.4 mysql 升级........................................................................................................................77 17.5 mysql 降级........................................................................................................................78 第18 章Mysql 日志管理................................................................................................................78 18.1 错误日志:......................................................................................................................78 18.2 BINLOG:........................................................................................................................79 18.3 查询日志..........................................................................................................................80 18.4 慢查询日志:..................................................................................................................80 第19 章数据备份与恢复:...........................................................................................................81 19.1 备份/恢复策略:.............................................................................................................81 19.2 冷备份:..........................................................................................................................81 19.3 逻辑备份:......................................................................................................................81 19.4 单个表的备份:..............................................................................................................82 19.5 使用备份工具ibbackup:..............................................................................................82 19.6 时间点恢复:..................................................................................................................83 19.7 位置恢复:......................................................................................................................84 19.8 MyISAM 表修复:..........................................................................................................84 第20 章Mysql 安全:....................................................................................................................85 20.1 正确设置目录权限:......................................................................................................85 20.2 尽量避免以root 权限运行mysql:..............................................................................85 20.3 删除匿名帐号:..............................................................................................................85 20.4 给mysql root 帐号设置口令:...................................................................................... 86 20.5 设置安全密码并定期修改:..........................................................................................86 20.6 只授予帐号必须的权限:..............................................................................................86 20.7 除root 外,任何用户不应有mysql 库user 表的存取权限:....................................86 20.8 不要把FILE、PROCESS 或SUPER 权限授予管理员以外的帐号:.....................86 20.9 load data local 带来的安全问题:..................................................................................87 20.10 尽量避免通过symlinks 访问表:...............................................................................88 20.11 使用merge 存储引擎潜藏的安全漏洞:....................................................................88 20.12 防止DNS 欺骗:..........................................................................................................88 20.13 drop table 命令并不收回以前的相关访问授权:.......................................................88 20.14 使用SSL:....................................................................................................................88 20.15 如果可能,给所有用户加上访问IP 限制:..............................................................90 20.16 严格控制操作系统帐号和权限:............................................................................... 90 20.17 增加防火墙:................................................................................................................90 20.18 其他安全设置选项:....................................................................................................90 20.18.1 allow-suspicious-udfs:............................................................................ 90 20.18.2 old-passwords:.............................................................................................90 20.18.3 safe-user-create:...................................................................................... 91 20.18.4 secure-auth:.................................................................................................91 20.18.5 skip-grant-tables:.................................................................................... 91 20.18.6 skip-networking:.........................................................................................91 20.18.7 skip-show-database:.................................................................................. 91 第21 章Mysql 复制:....................................................................................................................92 21.1 Mysql 复制概述:........................................................................................................... 92 21.2 安装配置:......................................................................................................................92 21.3 日常管理维护:..............................................................................................................93 21.3.1 经常查看slave 状态........................................................................................ 93 21.3.2 怎样强制主服务器阻塞更新直到从服务器同步?........................................94 21.3.3 master 执行的语句在slave 上执行失败怎么办?.......................................94 21.3.4 Slave 上出现log event entry exceeded max_allowed_packet 错误怎么 办?..................................................................................................................................94 21.3.5 多主复制时,自动增长变量的冲突问题........................................................95 21.3.6 怎么样知道slave 上现在复制到什么地方了................................................95 21.4 需要注意的问题:..........................................................................................................95 第22 章Mysql Cluster:.............................................................................................................95 22.1 Mysql Cluster 概述:.......................................................................................................95 22.2 Mysql Cluster 架构:.......................................................................................................96 22.3 安装配置:......................................................................................................................96 22.3.1 管理节点配置步骤:.........................................................................................96 22.3.2 sql 节点和数据节点的配置:......................................................................... 98 22.4 管理维护:......................................................................................................................98 22.4.1 Cluster 的启动..................................................................................................98 22.4.2 Cluster 的关闭..................................................................................................99 22.5 数据备份和恢复:..........................................................................................................99 第23 章Oracle 向Mysql 数据迁移:....................................................................................... 100 23.1 数据类型的差异:........................................................................................................100 23.2 利用导出文本迁移:....................................................................................................100 23.2.1 导出为insert sql 文本................................................................................ 100 23.2.2 导出为固定格式文本...................................................................................... 101 23.3 利用工具软件迁移:....................................................................................................102 23.4 使用DBA 组开发的迁移工具:.................................................................................102 23.5 数据迁移常见问题:....................................................................................................103 23.5.1 字符集问题:...................................................................................................103 23.5.2 特殊字符处理:...............................................................................................103 23.5.3 日期字段的处理:...........................................................................................103 23.5.4 如何使迁移过程不被SQL 错误中断:..........................................................104 23.5.5 如何查找产生warnings 的原因:................................................................ 104 第24 章应急处理:.....................................................................................................................104 24.1 一般处理流程:............................................................................................................104 24.2 忘记root 密码:........................................................................................................... 105 24.3 表损坏如何处理:........................................................................................................105 24.4 MyISAM 表超过4G 无法访问:.................................................................................106 24.5 数据目录磁盘空间不足怎么办?............................................................................... 106 24.6 如何禁止DNS 反向解析?..........................................................................................107 第25 章Mysql 管理中一些常用的命令和技巧:..................................................................... 107 25.1 参数设置方法:............................................................................................................107 25.2 mysql.sock 丢失后怎么连接数据库?......................................................................... 107 25.3 同一台机器运行多个mysql:.....................................................................................108 25.4 查看用户权限:............................................................................................................109 25.5 修改用户密码:............................................................................................................110 25.6 怎样灵活的指定连接的主机:....................................................................................111 25.7 到底匹配哪个符合条件的用户:................................................................................111 25.8 不进入mysql,怎样运行sql 语句?..........................................................................112 25.9 客户端怎么访问内网数据库?....................................................................................113
购买主题
已有 10 人购买
本主题需向作者支付 5 金币 才能浏览
| |
发表于 2014-12-15 18:39:28
|
显示全部楼层
| ||
发表于 2014-12-16 09:29:24
|
显示全部楼层
| ||
发表于 2014-12-16 10:20:33
|
显示全部楼层
| ||
发表于 2014-12-24 10:13:30
|
显示全部楼层
| ||
发表于 2014-12-24 22:06:57
|
显示全部楼层
| ||
发表于 2014-12-27 21:03:23
|
显示全部楼层
| ||
发表于 2014-12-28 20:19:40
|
显示全部楼层
| ||
发表于 2014-12-29 23:23:22
|
显示全部楼层
| ||