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,下载（社区版）：http:/,文档：http:/,1.4 MySQL安装（以Solaris非rpm版为例）,介质准备：rootmysolariscd/opt/uniload/dev_installrootmysolarisgizp-d mysql-5.6.2-m5-solaris10-i386.tar.gzrootmysolaristar-xvf mysql-5.6.2-m5-solaris10-i386.tar,用户准备：rootmysolarisgroupadd mysqlrootmysolarisuseradd-g mysql mysql,一.MySQL 简介,MySQL,1.4 MySQL安装（以Solaris非rpm版为例）,目录准备：rootmysolariscd/usr/localrootmysolarisln-s/opt/uniload/dev_install/mysql-5.6.2-m5-solaris10-i386 mysqlrootmysolarischown-R mysql mysqlrootmysolariscd/opt/uniload/dev_installrootmysolarischown-R mysql mysql-5.6.2-m5-solaris10-i386,安装：rootmysolariscd/usr/local/mysql/scriptsmysqlmysolaris./mysql_install_db-user=mysql-basedir=/usr/local/mysql-datadir=/usr/local/mysql/data,启动关闭（可以使用chkconfig命令将其添加到系统默认启动服务中）：cd/usr/local/mysql/binmysqlmysolaris./mysqldmysqlmysolaris./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/Hash5.触发器：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 f-,配置参数语法：小写，使用“_”或“-”分割单词。在文件中设置参数时，其值可以带单位M,G，不可以使用表达式。使用set命令设置参数，其值不可以带单位，但可以是表达式，如10 x1024。,备注：尽量避免在线更改，设置配置参数，这样可能导致服务器做大量工作。,五.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-反编译，查看执行计划中的sql3.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：数据分块，将数据分片存储到不同的节点上。,分块的优点：1.提高可管理性：备份、恢复、修改索引等便于操作。2.便于移动：当需要平衡数据分块时便于操作。3.扩展了应用的写操作负载能力。,常用分块方法：1.一个块对应一个库2.表名标识：dbname.table_01 3.库名标识：dbname01.table4.库名表名标识：dbname01.table01,数据分配方法：1.固定分配：分配函数只依赖一个分配键，如对某值取模，得出块编号。2.动态分配：每个数据分到哪块单独处理，可以做到精细控制，能够适应大规模查询带来的问题。3.混合分配：结合固定分配和动态分配。,分块带来的问题：跨分块查询：解决方法（1）借助汇总表或存储过程（2）借助应用程序,六.MySQL企业级应用,MySQL,6.2 伸缩性实现方案,案例：淘宝,六.MySQL企业级应用,MySQL,6.2 伸缩性实现方案,案例：新浪（高可用性+容灾）,六.MySQL企业级应用,MySQL,6.3 负载平衡,负载平衡相关概念：可伸缩性：可通过在节点上增加服务器来提高其处理能力。高效性：通过控制请求路由，能更有效的使用服务器资源。可用性：保证服务器时刻可用。透明性：用户感知不到其存在。一致性：状态会话，不丢失有关状态信息，能重定向客户端请求。,负载平衡方法：1.引入中间件（硬件 or 软件）。2.浮动IP地址。3.读写分离（根据查询及版本）。4.应用配置。,六.MySQL企业级应用,MySQL,6.3 负载平衡,案例：常用架构,客户端,负载均衡器,负载均衡器,WEB服务器,MySQL服务器,Thank You!,