高性能MySQL+深入浅出MySQL(高清版)
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
2
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
3
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
4
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
5
第 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
6
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
7
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
8
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
Hibernate简单使用
Hibernate简单使用。。。。。。。。。。。。。。。。。。。。
List<Map<String, List>> data保存到SharedPreference和读取
List<Map<String, List>> data保存到SharedPreference和读取
Android Drawer
Android的一个Drawer的简单实现
Andorid Dialog 九种形式
Dialog是常用的一种Android控件,一般常用的形式有几种。
Android spinner 省市联动
在Android的开发中,经常需要添加地址,这里是一个简单的根据xml文件来解析去获取省市地址的联动
Android 点击监听事件的四种写法
Android点击监听事件经常用到,经常用到的有以下几种
Android 从网络上获取图片保存到SD卡中
Android 从网络上获取图片保存到SD卡中
fragment的显示与隐藏
fragment的显示与隐藏,避免重复请求,浪费资源
RadioGroup+fragment实现切换
RadioGroup+fragment实现切换
ExpandableListView子item选中颜色发生改变
经常会遇到ExpandableListView这个控件,有的时候会有需求是当下拉列表子item被选中时要进行颜色的改变,以便标识
ListView选中item颜色改变
经常需要在项目中实现以下效果,点击listview中子item的颜色会改变
网络数据快还获取神器Volley
可以快还的获取网络数据,方便解析网络图片,简单易用