PHP 训练营 - MySQL OA 产品部 李鑫辉 2011 年 9 月. 内容提要 & 目录 1. 介绍 (10 分钟 ) 1.1. 简介 1.2. 特点 1.3. 安装 2. 语法 (60 分钟 ) 2.1. 存储引擎 2.1.1. MyISAM 2.1.2. InnoDB 2.1.3. 特点与区别.

Presentation on theme: "PHP 训练营 - MySQL OA 产品部 李鑫辉 2011 年 9 月. 内容提要 & 目录 1. 介绍 (10 分钟 ) 1.1. 简介 1.2. 特点 1.3. 安装 2. 语法 (60 分钟 ) 2.1. 存储引擎 2.1.1. MyISAM 2.1.2. InnoDB 2.1.3. 特点与区别."— Presentation transcript:

1 PHP 训练营 - MySQL OA 产品部 李鑫辉 2011 年 9 月

2 内容提要 & 目录 1. 介绍 (10 分钟 ) 1.1. 简介 1.2. 特点 1.3. 安装 2. 语法 (60 分钟 ) 2.1. 存储引擎 2.1.1. MyISAM 2.1.2. InnoDB 2.1.3. 特点与区别 2.1.4. 锁机制 2.2. 字段类型 2.3. 索引 2.3.1. 普通索引 2.3.2. 唯一性索引 2.3.3. 主键索引 2.3.4. 全文索引 2.3.5. 最左前缀 2.4. 设计

3 内容提要 & 目录 3. 管理 (30 分钟 ) 3.1. 命令 3.1.1. CURD 3.1.2. explain 3.1.3. show 3.2. 工具 3.2.1. PhpMyadmin 3.2.2. Navicat 3.2.3. Case studio 3.3. 备份 3.4. 还原 4. Php 与 Mysql (30 分钟 ) 4.1. Pdo 4.2. 实例 4.3. 防 sql 注入 4.3.1. 相关函数

4 内容提要 & 目录 5. 高级 (10 分钟 ) 5.1. 分区 5.2. 分表 5.3. 分库 5.4. 集群 6. 优化 (5 分钟 ) 6.1. sql 6.2. 索引 6.3. 配置 6.4. 工具

5 1.1. 简介 MySQL 是一个关系型数据库管理系统，开发者为瑞典 MySQL AB 公司。 MySQL 比较小巧、实用、性能比较高；适合中小型软件开 发；开发成本比较低，经济实用 其他数据库，有 Oracle （甲骨文）、 SQLServer （微软）、 DB2 （ IBM ）等；

6 时间事件 2003 年 3 月发布 MySQL 4.0 2004 年 10 月发布 MySQL 4.1 2005 年 10 月发布 MySQL 5.0 2008 年 1 月被 Sun 公司收购 2008 年 11 月发布 MySQL 5.1 （目前稳定版本） 2009 年 4 月 SUN 被 Oracle 收购（甲骨文公司） 2010 年 12 月发布 MySQL 5.5 1.1. 简介

7 版本区别

8

9 1.2. 特点 开源 免费 跨平台 安全性高 成本低 支持各种开发语言 支持强大的内置函数 数据存储量大

10 1.3. 安装 Windows a. msi 格式（可执行安装） b. zip 格式（解压缩版） Linux a. 编译安装 b. 安装包安装 Xampp 集成了 mysql ， php ， apache 下载地址 http://dev.mysql.com/downloads 1.3. 安装

11 详细可以 google 一下，按步骤安装，参考搜索 “mysql 安装图解 ”

12 1.3. 安装

13 2.1. 存储引擎 存储引擎就是如何存储数据、如何为存储的数据建立 索引和如何更新、查询数据等技术的实现方法。 在关系数据库中数据的存储是以表的形式存储，所以 存储引擎也可以称为表类型（即存储和操作此表的类 型） 类型有 MyISAM ， InnoDB ， MERGE ， MEMORY (HEAP) 等 2.1. 存储引擎

14 2.1.1. MyISAM 默认引擎 表级锁定 适用于大量的读操作的表 文件存储结构.frm 描述表的结构，格式文件.myd 描述表的数据，数据文件.myi 描述表的索引 2.1.1. MyISAM

15

16

17 2.1.2. InnoDB 支持外键，事务处理 行锁定 具有提交，回滚和崩溃恢复能力的事务安全 适用于大量的写读作的表 2.1.2. InnoDB

18 -MyISAMInnoDB 锁定表级锁定行锁定 事务不支持支持 读写串行方式并行方式 特点适用于大量的读操 作的表 适用于大量的写读 作的表 MyISAM - 适用于大量的读操作的表，因为是表锁定，读写是串行方式，索引文件和数据 文件分开 InnoDB - 适用于大量的写读作的表，因为是行锁定，读写是并行方式，索引和数据存放在 同一个表空间，表空间可以包含数个文件（或原始磁盘分区）。这与 MyISAM 表不同，比 如在 MyISAM 表中每个表被存在分离的文件中。 InnoDB 表可以是任何尺寸，即使在文件尺 寸被限制为 2GB 的操作系统上 2.1.3. 特点与区别

19

20 2.1.4. 锁机制 锁是计算机协调多个进程或线程并发访问某一资源的机制 MyISAM ，表级锁（ table-level-locking ），对整个文件进行锁定 InnoDB ，既支持行级锁，也支持表级锁，默认情况下是采用行级锁，对 文件中局部数据进行锁定， InnoDB 是一整个文件，把索引、数据、结构 全部保存在 ibdata 文件里 2.1.4. 锁机制

21 表级锁：开销小，加锁快；不会出现死锁； 行级锁：开销大，加锁慢；会出现死锁； 死锁 - 当某组资源的两个或多个线程之间有循环相关性时， 将发生死锁。 A 需要的资源 B 正使用中，因此 A 不能提交； 然而 B 也在等待着 A 提交以便释放 B 所需要的资源，才会发 生 “ 死锁 ” 2.1.4. 锁机制

22 2.2. 字段类型 常用字段 整数 tinyint ， smallint ， mediumint ， int ， bigint 字符 varchar ， char ， text ， blog 时间 datetime ， timestamp 2.2. 字段类型

23 在设计上尽量选择合适的字段类型，值不能太大也不要太 小，合适最好，尽量节省空间 一个字节 =8 个二进制位 一个中文 2 个字节 一个英文为 1 个字节 Varchar 最大 21843 Char 最大 255 2.2. 字段类型

24 一个 TEXT 是一个大小写不敏感的 BLOB tinyint ， -128 到 127 （ 100-200 ） smallint ， -32768 到 32767 （ 3-6 万） int ， -2147483648 到 2147483647 （ 20-40 亿） 2.2. 字段类型

25

26

27

28

29 2.3. 索引 目的，加快检索查询 按个数分 单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不 是组合索引。 组合索引，即一个索包含多个列, 最左前缀法则 abc 建立索引相当于 a,ab,abc 建立索引 按类型分 普通索引 唯一索引 主键索引 全文索引 2.3. 索引

30 2.3.1. 普通索引 在 where ， order by ， group by 中经常使用的字段，创 建索引，可以加快查询速度 explain 语句，可以查看 sql 查询情况，是否使用了索 引 like ‘%test%’ ，不使用索引 like ‘test%’ ，使用索引 2.3.1. 普通索引

31 2.3.2. 唯一性索引 unique ，不允许重复，可以为空值 双唯一性索引 可以对多个字段设置成唯一性索引 2.3.2. 唯一性索引

32 2.3.3. 主键索引 自增，不允许重复，不能为空值， 一般是表的主键 id 2.3.3. 主键索引

33 2.3.4. 全文索引 分词 FULLTEXT 索引仅可用于 MyISAM 表 可以从 CHAR 、 VARCHAR 或 TEXT 列中作为 CREATE TABLE 语句的一部分被创建，或是随后使用 ALTER TABLE 或 CREATE INDEX 被添加 2.3.4. 全文索引

34 2.3.5. 最左前缀 abc 建立索引相当于 a ， ab ， abc 建立索引 2.3.5. 最左前缀

35 2.4. 设计 1. 命名规范，加前缀 2. 表结构设计，结合业务 3. 字段类型，大小 4. 索引建立，加快查询 5. 高并发，海量数据，可分表，分库考虑 2.4. 设计

36 3.1. 命令 #mysql -h10.241.12.120 -uroot –p 连接数据库 mysql>show databases; 显示所有数据库 mysql>use databasename; 选择数据库 3.1. 命令

37 mysql>show tables; 显示表 mysql>desc tablename; 表的详细描述 mysql>select version(), current_date; 显示当前 mysql 版本和当前日期 \G ，参数 纵向排列，如果列比较多，看不清楚，可以添加这个参 数，比如 mysql>desc tablename\G; 3.1. 命令

38

39 3.1.1. CURD 增删改查 查询 selec * from 表名 where 条件 插入 insert into 表名 set 字段名 ="" insert into 表名 (' 字段名 ') values('') 更新 update 表名 set 字段名 ="" where 条件 删除 delete from 表名 where 条件 3.1.1. CURD

40 3.1.2. explain 可以查看 sql 执行效率 比如 explain select * from user; 3.1.2. explain

41 3.1.3. show show databases 列举数据库 show tables 列举表 show processlist 显示哪些线程正在运行 show status 服务器状态信息 show variables 显示系统变量的值 3.1.3. show

42 3.2.1. PhpMyadmin 连接工具， web 方式 是一个用 PHP 编写的，可以通过 web 方式控制和操 作 MySQL 数据库。通过 phpMyAdmin 可以完全对数 据库进行操作，例如建立、复制、删除数据等等 官方网站， http://www.phpmyadmin.net/ 3.2.1. PhpMyadmin

43

44 3.2.2. Navicat 连接工具，客户端 功能强大，操作方便快捷 导入导出功能，支持多种格式 查询方便 官方网站， http://www.navicat.com 3.2.2. Navicat

45

46 3.2.3. Case studio 建模工具，客户端 支持 mysql ， oracle ， sybase 等数据库 可以方便导入导出 3.2.3. Case studio

47

48 3.3. 备份 远程备份，本地备份（不用加 -h ） 备份库 # mysqldump -h10.241.12.120 -uroot -p database_name > /var/log/database_name.bak.20110908.sql 备份表 # mysqldump -h10.241.12.120 -uroot -p database_name talbe_name > /var/log/database_name- talbe_name.bak.20110908.sql 3.3. 备份

49 3.4. 还原 远程还原，本地还原（不用加 -h ） 还原库 mysql> mysql -h10.241.12.120 -uroot -p database_name < /var/log/database_name.bak.20110908.sql 还原表 mysql> mysql -h10.241.12.120 -uroot -p database_name < /var/log/database_name- talbe_name.bak.20110908.sql 3.4. 还原

50 4.1. Pdo PDO 扩展为 PHP 访问数据库定义了一个轻量级的、一致性 的接口，它提供了一个数据访问抽象层，这样，无论使用 什么数据库，都可以通过一致的函数执行查询和获取数据。 PDO 随 PHP5.1 发行，在 PHP5.0 的 PECL 扩展中也可以使 用。 php.ini extension=php_mysql_libmysql.dll extension=php_mysqli_libmysql.dll extension=php_pdo.dll extension=php_pdo_mysql_libmysql.dll 4.1. Pdo

51 4.2. 实例

52

53

54 代码 <?php $str_db_dsn = 'mysql:dbname=test;host=127.0.0.1'; $str_db_user = 'root'; $str_db_pwd = ''; // 连接 try { $obj_db = new PDO($str_db_dsn, $str_db_user, $str_db_pwd, array( PDO::ATTR_PERSISTENT => true // 长连接 )); // 出错模式 $obj_db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // 字符集设置 $obj_db->exec("SET NAMES 'UTF8'"); } catch( PDOException $e ) { echo 'Connection failed: '. $e->getMessage(); } /* 获取结果集 */ $str_sql = 'select * from user'; // fetchAll, 多条记录 $obj_statm = $obj_db->prepare($str_sql); $obj_statm->execute(); $arr_result = $obj_statm->fetchAll(PDO::FETCH_ASSOC); 4.2. 实例

55 // fetchAll $arr_result = $obj_db->query($str_sql)->fetchAll(PDO::FETCH_ASSOC); // fetch, 单条记录 $arr_result = $obj_db->query($str_sql)->fetch(PDO::FETCH_ASSOC); // fetchColumn, 获取字段 $arr_result = $obj_db->query($str_sql)->fetchColumn(2); //var_dump($arr_result); /* 插入 */ // exec $str_sql = 'insert into user(name) values(" 李四 ")'; $bln_result = $obj_db->exec($str_sql); /* 更新 */ // exec $str_sql = 'update user set name=" 张三 _update" where id=1'; $bln_result = $obj_db->exec($str_sql); /* 删除 */ // exec $str_sql = 'delete from user where name=" 李四 "'; $bln_result = $obj_db->exec($str_sql); 4.2. 实例

56 4.3. 防 sql 注入 SQL 注入攻击是黑客攻击网站最常用的手段。如果你的站点没有使用严格的用户输入检验， 那么常容易遭到 SQL 注入攻击。 SQL 注入攻击通常通过给站点 数据库提交不良的数据或查询 语句来实现，很可能使数据库中的纪录遭到暴露，更改或被删除。 实例 /* sql 注入 */ $int_id = "1;delete from user;"; $str_sql = "select * from user where id={$int_id}"; $bln_result = $obj_db->exec($str_sql); 真实执行 sql ， select * from user where id=1;delete from user; 防止注入，主要就是去除和转义一些特殊字符，使用 php 提供的函数处理 4.3. 防 sql 注入

57 4.3.1. 相关函数 addslashes() 单字节字符处理 mysql_real_escape_string() 多字节字符处理 考虑到连接的当前字符集 mysql_escape_string() 不考虑到连接的当前字符集 4.3.1. 相关函数

58 5.1. 分区 分区是把一张表的数据分成 N 多个区块，分区后还是一张表 目的 提升数据库的访问性能 适用情况 平时访问的量不是特别大，表结构变更也不多，而且历史数据很少访问的情况下， 可能会做成分区表，这样平时基本上只要访问最近的分区段，还有利用老数据的 清理。 5.1. 分区

59 5.2. 分表 把一张表分成多个表 目的 提升数据库的访问性能 适用情况 数据量非常大，而且访问上没有绝对的热点，基本上所有的数据都有可能会 访问到 5.2. 分表

60 5.3. 分库 把一个数据库分成多个库 目的 提升数据库的访问性能 5.3. 分库

61 5.4. 集群 集群 (cluster) 就是多个 mysql 服务器，减轻数据库负担 主从分离，读写分离 master 用来写 slave 用来读，减轻压力 5.4. 集群

62

63 6. 优化 6.1. sql where ， order ， group by ，建立索引 6.2. 索引 6.3. 配置 my.cnf 6.4. 工具 tuning-primer.sh 6. 优化

64 Thank you!