MySQL基本原理ppt课件

MySQL基础技能与原理 基本原理 MySQLDBATeam 彭立勋 1 内容概要 1 MySQL体系架构2 InnoDB特点3 MySQL高级调优4 MySQL复制原理5 MySQL高级备份6 MySQL关键代码实现分析 2 MySQL体系架构 MySQL结构层次MySQL处理流程存储引擎机制 3 MySQL结构层次 4 MySQL处理流程 当客户端链接上mysql服务端时 系统为其分配一个链接描述符thd 用以描述客户端的所有信息 将作为参数在各个模块之间传递 一个典型的客户端查询在MySQL的主要模块之间的调用关系如图所示 5 存储引擎机制 MySQL提供一个抽象层 允许不同的存储应请使用相同的API对表进行访问 该接口通过一个被称为Handler的抽象类来实现 该处理器提供了一些可实现基本操作的方法 入打开和关闭表 连续扫描记录 按照键值检索记录 存储记录以及删除记录 每个存储引擎都执行处理器的一个子类以实现接口方法 以便将处理器操作转化为特定存储引擎的此层次存储 检索API调用MySQL的主要存储引擎有 MyISAM 非事务引擎 InnoDB 事务引擎 Archive 归档引擎 Memory 内存引擎 NDB 集群引擎 其他特殊引擎 Infobirght 数据仓库引擎 6 InnoDB特点 聚集索引行级锁数据文件 7 聚集索引 InnoDB实现两种B Tree索引 一种是列值为Key 主键位置为Value即 列值 主键位置 的非主键索引 SecondaryIndex 另一种是主键索引 两种索引的每个叶子节点都有一个双向指针分别指向前驱和后继节点 主键索引即聚集索引 ClusterIndex 它不仅有主键 而且有主键所属的全部数据 所以在InnoDB中 主键索引即数据 在InnoDB中 即使用户不指定主键 InnoDB也会生成一个隐含主键 这种情况下 InnoDB的性能比采用序列主键性能下降30 左右 详细参考 MySQL索引与存储方式对性能的影响 数据库算法与数据结构系列 B树相关 8 聚集索引 图 9 行级锁 InnoDB为了实现高并发 实现了一个行级锁 不同于Oracle的行级锁 InnoDB的行级锁是 间隙锁 即如果操作1 col1 10 哪怕只有col1 3一条记录 在可重复读隔离级别下 也无法插入col1 2或col1 4等在1 10范围内的记录 InnoDB的行级锁加在主键索引上 而不是加在数据块上 详细参考 InnoDB行锁的实现分析 10 数据文件 InnoDB的数据文件包括 innodb file per table frm表结构文件 ibd表数据文件 数据目录 系统元数据和undospace 共享表空间 ib logfile 重做日志文件 相当于Oracle的RedoLog 11 MySQL高级调优 索引调整重要性能参数调整编译参数调优 12 索引调整 主键索引必须有 并且按要满足序列化 递增或者递减索引合并 索引顺序不影响结果时 将可复用度高的字段放在索引的前面主键排序无需索引 假设最后输出结果按主键排序 无需考虑主键字段加入索引 InnoDB数据完全按主键顺序组织 最终取出数据既是按主键排序 关联表存在关联字段和条件字段 优先将条件字段放在关联字段前建立组合索引 实在无法避免的列运算但又需要很高的性能 可以通过新列和Trigger 或程序实现 实现函数索引 给MySQL做虚拟的 函数索引 13 重要性能参数调整 Sync binlog同步刷新binlog的秒数 设为0则交由操作系统自己刷新 MySQL不强制刷新 除0外 数值越小性能越差innodb flush log at trx commit0性能最好 但无法保证数据安全 断电可能损失较多数据 2性能较好 但可能损失1s的数据 1性能最差 不丢失数据 0只保证日志刷新到文件 不保证刷新到磁盘 2保证每秒刷新到磁盘 1保证每次提交刷新到磁盘 关于LinuxCache参考 LinuxCache机制探究 innodb max dirty pages pctInnoDBBufferPool中脏页的比例 建议设置为3 或者更小 如果脏页比例超过这个参数 就会回写到磁盘 如果这个值太大 每次关闭就会有很多脏页需要回写 14 编译参数调优 GCC参数 以Xeon55XX系列64位系统为例 CXX gccCHOST x86 64 pc linux gnu CFLAGS O3 fomit frame pointer pipe march nocona mfpmath sse m128bit long double mmmx msse msse2 maccumulate outgoing args m64 ftree loop linear fprefetch loop arrays freg struct return fgcse sm fgcse las frename registers fforce addr fivopts ftree vectorize ftracer frename registers minline all stringops fbranch target load optimize2 CXXFLAGS CFLAGS MySQL参数 configure prefix usr alibaba install mysql ent official 5 1 56 with server suffix alibaba mysql with mysqld user mysql with plugins partition blackhole csv heap innobase myisam myisammrg with charset utf8 with collation utf8 general ci with extra charsets gbk gb2312 utf8 ascii with big tables with fast mutexes with zlib dir bundled enable assembler enable profiling enable local infileenable thread safe client with readline with pthread with embedded server with client ldflags all static with mysqld ldflags all static without query cache without geometry without debug without ndb debug详细参考 适合MySQL的Xeon55XX系列CPU编译参数 15 MySQL复制原理 MySQLReplication的基本原理是通过binlog复制应用的方式来还原数据 MySQL通过Server id来识别binlog由哪台主机产生 因此即使双Master复制 也不会出现binlog被重复应用复制线程分为SlaveIO和SlaveSQL两个 SlaveIO线程只负责注册到Master上 读取binlog 然后解析到本地 SlaveSQL线程只负责把SlaveIO线程产生的可执行SQL应用到本地 避免主键冲突 MySQL提供了auto increment increment和auto increment offset来控制主键生成的序列 只要双Master的两台主机没有相同的序列 就绝对不可能复制冲突 16 MySQL高级备份 Xtrabckup物理热备份InnoDB数据 innobackupex data backups 就可以创建一个备份到 data backups目录下 只要看到 completedOK 就是备份成功 Xtrabckup物理恢复InnoDB数据 Innobackupex apply log data backups 2010 09 08 11 25 44 将数据文件处理为可以直接copy的内容 Innobackupex copy back data backups 2010 09 08 11 25 44 将数据文件拷贝到datadir 从而恢复数据原理 Xtrabckup启动时 会对数据库加一个瞬时的锁 此时开始 数据库发生的所有redolog和undospace都会被Xtrabckup记录 同时还在拷贝ibd文件 当恢复的时候 Xtrabckup先利用InnoDB源码中的恢复事务函数把当时时间点之后的数据回滚 然后就可以得到一个完整备份时拷贝 然后覆盖现有文件即可 官方文档 XtraBackupHowto 17 MySQL关键代码实现分析 主要数据结构主要算法 18 主要数据结构 THD线程描述符 sql sql class h 包含处理用户请求时需要的相关数据 每个连接会有一个线程来处理 在一些高层函数中 此数据结构常被当作第一个参数传递 NET网络连接描述符 sql mysql com h 网络连接描述符 对内部数据包进行了封装 是client和server之间的通信协议 TABLE数据库表描述符 sql table h 数据库表描述符 分成TABLE和TABLE SHARE两部分 FIELD字段描述符 sql field h 域描述符 是各种字段的抽象基类 19 主要算法 排序算法 MySQL排序采用sort buffer size设定的线程独占内存进行排序 如果缓存里内放下要排序的数据 那么MySQL会采取载入所有要返回的值进入缓存排序 如果缓存放不下 则把要排序的列放入缓存排序 然后再回表查询返回结果 如果依然放不下 就会采用归并排序 排好序的部分放入磁盘 载入未排好序的部分继续在内存中排序 然后和磁盘上的部分二路归并 直到排序全部完成 详细参考 数据库算法与数据结构系列 排序相关 连接算法 MySQ