MySQL培训.ppt

mysql 学习分享,卢骏,2012-03-02,mysql大纲,一. mysql 简介 二. mysql 体系结构 三. mysql 开发 四. mysql 管理 五. mysql 优化 六. mysql 企业级应用,1.1 mysql 介绍,1.4 mysql 安装,1.3 mysql 资源,mysql 简介,1.2 mysql 版本及特性,mysql,一. mysql 简介,1.1 mysql介绍,一. mysql 简介,mysql,历史： 1979年，始于一款报表工具、数据引擎。 1996年，发布 mysql 1.0 版本。 2000年，成立 mysql ab 公司。 2008年，sun 以 10 亿元收购了 mysql ab 公司。 2009年，oracle 以 76 亿元收购了 sun 公司。,定义： mysql 是一款优秀的开源小型关系数据库系统。,特点（从db产品对比角度）： 1.开源，基于通用公共许可证协议（gpl）可以免费使用修改。 2.引擎，采用开放式存储引擎架构，有多种免费或付费引擎可选择。 3.可用度高，通过优秀的架构设计及相关手段能实现企业级高可用性、高扩展性。 4.免费，有效降低企业投资运营成本。 5.趋势，产品成熟度逐渐提高，占行业应用比重增大，相关产业链丰富，从业人员逐步形成规模效应。,1.1 mysql介绍,一. mysql 简介,mysql,特点（从开发管理角度）： 1.不简单，mysql 由于引擎众多，且各自内部机制和实现技术差别很大，完全掌握需要花很多精力，并不像很多人想象中的那样简单。 2.dba角色，支撑好应用，以下均需掌握好：需求、架构、设计、优化。,学习建议： 1.深入理解 mysql 官方手册。 2.多练习，加深理解和实际运用能力。,使用 mysql 的企业：,1.2 版本及特性,一. mysql 简介,mysql,版本： 企业版本（mysql enterprise）： 收费，更稳定，完整的配套监控工具，官方技术支持。 社区版本（mysql） 免费，没有企业版稳定，没有完整监控工具及官方支持：。,版本及主要特性：,1.3 mysql资源,一. mysql 简介,mysql,下载（社区版）： /downloads/,文档： /doc/index.html,1.4 mysql安装（以solaris非rpm版为例）,介质准备： rootmysolariscd /opt/uniload/dev_install rootmysolarisgizp -d mysql-5.6.2-m5-solaris10-i386.tar.gz rootmysolaristar -xvf mysql-5.6.2-m5-solaris10-i386.tar,用户准备： rootmysolarisgroupadd mysql rootmysolarisuseradd -g mysql mysql,一. mysql 简介,mysql,1.4 mysql安装（以solaris非rpm版为例）,目录准备： rootmysolariscd /usr/local rootmysolarisln -s /opt/uniload/dev_install/mysql-5.6.2-m5-solaris10-i386 mysql rootmysolarischown -r mysql mysql rootmysolariscd /opt/uniload/dev_install rootmysolarischown -r mysql mysql-5.6.2-m5-solaris10-i386,安装： rootmysolariscd /usr/local/mysql/scripts mysqlmysolaris./mysql_install_db -user=mysql -basedir=/usr/local/mysql -datadir=/usr/local/mysql/data,启动关闭（可以使用chkconfig命令将其添加到系统默认启动服务中）： cd /usr/local/mysql/bin mysqlmysolaris./mysqld mysqlmysolaris./mysqladmin shutdown -uroot -p123456,2.1 mysql 逻辑架构,2.3 mysql 存储引擎,mysql 体系结构,2.2 mysql 并发控制,mysql,二. mysql 体系结构,二. mysql 体系结构,mysql,2.1 mysql 逻辑架构,优化器： 1.支持hint决策提示。 2.可提供优化说明。 3.优化器请求存储引擎提供操作相关的性能与开销信息。,二. mysql 体系结构,mysql,2.2 mysql 并发控制,相关概念： 并发控制：当多个查询同时修改数据时，可能会产生相互影响，这就是并发控制问题。 并发控制db采用工具（mysql）：锁,共享锁（读锁）、排他锁（写锁）,mvcc。 锁的作用对象：表锁、行锁（innodb）。 事务：一个工作单元，其中执行一系列语句，事务具有acid特性。 acid：原子性、一致性、隔离性、持久性。 并发控制方法：设置隔离级别+应用控制。 隔离级别： 1.read uncommmitted （读取未提交内容） 2.read committed （读取提交内容） 3.repeatable read （可重读）（innodb、falcon） 4.serializable （可串行化） 可能会出现的并发问题： 1.脏读：读到了未提交的数据。 2.不可重复读：同一事务中，两次读取的数据内容不一样。 3.幻读：同一事务中，两次读取的数据条数不一样。 mvcc:多版本并发控制，通过系统版本号保存数据快照，实现数据视图的一致性。 命令设置：set session transaction isolation level read committed,二. mysql 体系结构,mysql,2.2 存储引擎,mysql select * from information_schema.engines; -当前版本引擎支持情况,3.1 mysql 数据类型,mysql 开发,3.2 mysql db对象,mysql,三. mysql 开发,三. mysql 开发,mysql,3.1 数据类型,原则（db通用）： 原则1：在符合应用要求（范围，精度适）的前提下，尽量采用短的类型。 优点：1.节省存储空间 2.节省内存缓存空间 3.加快查询连接效率。 原则2：尽量不采用浮点类型float、double，而采用精确类型decimal(m,n)。 原因：1.使数据计算能够精确 2.有时也能节省空间 如百分比 decimal(4,2)即可,需要说明的： 1.时间类型的选择：能用timestamp则用，否则采用datetime，因为timestamp比datetime格式一样，只是范围小些，但也一般够用了，到2038年。timestamp字段在插入时若没有值，则会自动填取当前值。 2.很多类型都有一个族如（ bigintintmediumintsmallinttinyint ），可以根据范围进行选择。,备注： 操作符及各种类型处理函数，见文档说明。,三. mysql 开发,mysql,3.2 对象,备注： 对象各种ddl dml，请见文档。,常用对象： 1.数据库：可指定字符集，排序规则，其后所有对象上均有此功能。 2.表： 3.视图： 4.索引：两种 btree/hash 5.触发器： 6.存储过程： 7.事件：可定时执行任务 8.日志组：只适用于cluster环境（ndb/ ndbcluster） 9.表空间：只适用于cluster环境（ndb/ ndbcluster）,4.1 基本命令,4.4 安全管理,4.3 对象信息查看,mysql 管理,4.2 通用信息查看,mysql,四. mysql 管理,4.5 备份与恢复,四. mysql 管理,mysql,4.1 基本命令,基本管理工具（mysql.exe）：相当于 oracle 的 sqlplus。,四. mysql 管理,mysql,4.2 通用信息查看,参数信息：,字符集：,四. mysql 管理,mysql,4.2 通用信息查看,状态信息：,四. mysql 管理,mysql,4.3 对象信息查看,库/表：,四. mysql 管理,mysql,4.4 安全管理,权限操作：,四. mysql 管理,mysql,4.5 备份与恢复,备份恢复：,5.1 mysql 配置参数,5.3 mysql 查询优化,mysql 优化,5.2 mysql 索引,mysql,五. mysql 优化,五. mysql 优化,mysql,5.1 配置参数,确定配置参数文件： mysqld -verbose -help - default options are read from the following files in the given order: c:windowsmy.ini c:windowsf c:my.ini c:f e:dev_installmysql5.5mysql server 5.5my.ini e:dev_installmysql5.5mysql server 5.5f -,配置参数语法： 小写，使用“_”或“-”分割单词。 在文件中设置参数时，其值可以带单位m,g，不可以使用表达式。 使用set命令设置参数，其值不可以带单位，但可以是表达式，如10x1024。,备注： 尽量避免在线更改，设置配置参数，这样可能导致服务器做大量工作。,五. mysql 优化,mysql,5.1 配置参数,重要参数,五. mysql 优化,mysql,5.2 索引,b-tree 索引,索引实现：实现于底层存储引擎，引擎的支持种类，实现方式都可能不一样。,适用场景： 1.匹配全名 2.匹配左前缀 3.匹配范围值 4.只访问索引的查询,局限场景： 1.查找没有从索引最左边开始 2.不能使用“范围查找”右边的列优化,hash 索引,适用场景： 1.精确查找,局限场景： 1.不支持排序 2.不支持部分键匹配 3.只支持=,in(),相等比较，不支持范围查找 4.如果有很多碰撞则会效率低下,五. mysql 优化,mysql,5.2 索引,索引使用策略,1.隔离列 示例：where id+1=5 应改为 where id=4 示例：where to_days(current_date)-to_date(datacol)=date_sub(current_date,interval 10 day) 2.覆盖索引（查询的列就是索引的一部分） 要求：1).where 子句中不能有 like 2).select 子句中不能有 * 3.排序使用索引扫描 要求： 1).排序不能是降序（因为索引都是升序） 2).order by中有非索引列 3).有范围条件 或 多个等于条件 4).高位移分页查询，应该为内联关联子查询,碎片的处理,1.optimize table table_name 2.转储重新加载,五. mysql 优化,mysql,5.3 查询优化,优化数据访问,1.尽量请求较少的数据，只提取需要的列。 2.重构查询复杂查询 分解为 多个简单查询。 3.重构查询每次只处理一小部分数据。（如delete） 4.重构查询将连接查询分解为多个单个查询。 5.重构查询大表关联改为 in()。 6.连接优化确保on,using使用的列上有索引。 7.连接优化去报group by和order by只引用同一张表中的字段。 8.子查询优化子查询应该尽可能的使用内连接。 9.分页查询优化应尽可能的使用内连接。,explain,执行方式： 1.explain sql -查看查询计划 2.explain extended sql -反编译，查看执行计划中的sql 3.explain partitions -查看数据分片,五. mysql 优化,mysql,5.3 查询优化,explain,局限性： 1.只是一个近似值。 2.对触发器、存储过程、函数无效。 3.不说明 mysql 内部做了哪些优化。 4.无法显示查询计划中的所有信息。 5.某些特定时候可能其值还是错了。,计划字段含义： 1.id:行号。 2.select_type:选择类型:simple（简单）、primary（复杂-最外层）、subquery（子查询）、union（联合）、derived（from子句子查询递归执行）。 3.table:表名。 4.type:访问类型:all（全表）、index（索引）、range（范围）、ref（索引查找）、const（常量）。 5.key:索引标识。 6.rows:预估行数。,6.1 相关概念,6.3 负载平衡,mysql 企业级应用,6.2 伸缩性实现方案,mysql,六. mysql 企业级应用,六. mysql 企业级应用,mysql,6.1 相关概念,性能：满足某一目标的能力。如：相应时间、吞吐率等。 负载能力：满能够承受的总负载。 伸缩性：在不降低性能的前提下，给应用增加负载的能力。 可用性：响应请求时间的百分比，如：98%时间可用。 容错性：衡量当应用或系统出现错误后的还原能力。,负载量的一些衡量指标： 数据量：总数据量/单位时间数据量 用户数：,六. mysql企业级应用,mysql,6.2 伸缩性实现方案,总方案1：向上扩展：升级硬件。不推荐使用，因为mysql对多cpu性能提升不够好。,总方案2：向外扩展：通过业务切割，增加节点来分担负载。（推荐）,复制,slave,节点：mysql架构中的一个功能单元，常有以下几种组成形式。,单服务器,（1）,master,master,复制,（2）,drbd,master,slave,（3）,master,复制,（4）,slave,slave,3不常用，性价比不高； 2优点是可以相互切换； 4优点是读写分离； 可以根据应用需要组合出其它拓扑结构。,六. mysql企业级应用,mysql,6.2 伸缩性实现方案,方案2.1：功能拆分，将不同的功能由不同的节点来承载。（垂直拆分）,客户端,web服务器,节点1 功能1,节点2 功能2,节点3 功能3,缺点： 扩展能力有限。因为应用功能和节点耦合度很高，如果某个功能需要很高的伸缩性及负载能力，且业务非常复杂，会给扩展带来很大麻烦。,六. mysql企业级应用,mysql,6.2 伸缩性实现方案,方案2.2：数据分块，将数据分片存储到不同的节点上。（水平拆分）,客户端,web服务器,复制：互备,共享存储：功能1,全局数据库,共享存储：功能2,分块,备注： 每种拓扑，结合各节点采用的存储引擎， 会有不同的特点，需要根据业务去 考虑。,六. mysql企业级应用,mysql,6.2 伸缩性实现方案,方案2.2：数据分块，将数据分片存储到