MySQL基本原理课件

MySQL基础技能与原理基本原理,MySQLDBATeam,）,1,学习交流PPT,内容概要,1.MySQL体系架构2.InnoDB特点3.MySQL高级调优4.MySQL复制原理5.MySQL高级备份6.MySQL关键代码实现分析,2,学习交流PPT,MySQL体系架构,MySQL结构层次MySQL处理流程存储引擎机制,3,学习交流PPT,MySQL结构层次,4,学习交流PPT,MySQL处理流程,当客户端链接上mysql服务端时，系统为其分配一个链接描述符thd，用以描述客户端的所有信息，将作为参数在各个模块之间传递。一个典型的客户端查询在MySQL的主要模块之间的调用关系如图所示：,5,学习交流PPT,存储引擎机制,MySQL提供一个抽象层，允许不同的存储应请使用相同的API对表进行访问。该接口通过一个被称为Handler的抽象类来实现，该处理器提供了一些可实现基本操作的方法，入打开和关闭表，连续扫描记录，按照键值检索记录，存储记录以及删除记录。每个存储引擎都执行处理器的一个子类以实现接口方法，以便将处理器操作转化为特定存储引擎的此层次存储/检索API调用MySQL的主要存储引擎有：MyISAM(非事务引擎)/InnoDB(事务引擎)/Archive(归档引擎)/Memory(内存引擎)/NDB(集群引擎)其他特殊引擎：Infobirght(数据仓库引擎),6,学习交流PPT,InnoDB特点,聚集索引行级锁数据文件,7,学习交流PPT,聚集索引,InnoDB实现两种B+Tree索引，一种是列值为Key，主键位置为Value即(列值,主键位置)的非主键索引（SecondaryIndex），另一种是主键索引，两种索引的每个叶子节点都有一个双向指针分别指向前驱和后继节点。主键索引即聚集索引（ClusterIndex），它不仅有主键，而且有主键所属的全部数据，所以在InnoDB中，主键索引即数据。在InnoDB中，即使用户不指定主键，InnoDB也会生成一个隐含主键，这种情况下，InnoDB的性能比采用序列主键性能下降30%左右。详细参考：MySQL索引与存储方式对性能的影响数据库算法与数据结构系列B树相关,8,学习交流PPT,聚集索引（图）,9,学习交流PPT,行级锁,InnoDB为了实现高并发，实现了一个行级锁。不同于Oracle的行级锁，InnoDB的行级锁是“间隙锁”，即如果操作1col110，哪怕只有col1=3一条记录，在可重复读隔离级别下，也无法插入col1=2或col1=4等在110范围内的记录。InnoDB的行级锁加在主键索引上，而不是加在数据块上。详细参考：InnoDB行锁的实现分析,10,学习交流PPT,数据文件,InnoDB的数据文件包括：（innodb_file_per_table）.frm表结构文件.ibd表数据文件（数据目录）、系统元数据和undospace（共享表空间）ib_logfile*重做日志文件，相当于Oracle的RedoLog,11,学习交流PPT,MySQL高级调优,索引调整重要性能参数调整编译参数调优,12,学习交流PPT,索引调整,主键索引必须有，并且按要满足序列化，递增或者递减索引合并：索引顺序不影响结果时，将可复用度高的字段放在索引的前面主键排序无需索引：假设最后输出结果按主键排序，无需考虑主键字段加入索引，InnoDB数据完全按主键顺序组织，最终取出数据既是按主键排序。关联表存在关联字段和条件字段，优先将条件字段放在关联字段前建立组合索引。实在无法避免的列运算但又需要很高的性能，可以通过新列和Trigger（或程序实现）实现函数索引：给MySQL做虚拟的“函数索引”,13,学习交流PPT,重要性能参数调整,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,学习交流PPT,编译参数调优,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-optimize2CXXFLAGS=”$CFLAGS”MySQL参数./configureprefix=/usr/alibaba/install/mysql-ent-official-5.1.56with-server-suffix=alibaba-mysqlwith-mysqld-user=mysqlwith-plugins=partition,blackhole,csv,heap,innobase,myisam,myisammrgwith-charset=utf8with-collation=utf8_general_ciwith-extra-charsets=gbk,gb2312,utf8,asciiwith-big-tableswith-fast-mutexeswith-zlib-dir=bundledenable-assemblerenable-profilingenable-local-infileenable-thread-safe-clientwith-readlinewith-pthreadwith-embedded-serverwith-client-ldflags=-all-staticwith-mysqld-ldflags=-all-staticwithout-query-cachewithout-geometrywithout-debugwithout-ndb-debug详细参考：适合MySQL的Xeon55XX系列CPU编译参数,15,学习交流PPT,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,学习交流PPT,MySQL高级备份,Xtrabckup物理热备份InnoDB数据：“innobackupex/data/backups”就可以创建一个备份到/data/backups目录下，只要看到“completedOK!”就是备份成功。Xtrabckup物理恢复InnoDB数据：“Innobackupex-apply-log/data/backups/2010-09-08_11-25-44/”，将数据文件处理为可以直接copy的内容“Innobackupexcopy-back/data/backups/2010-09-08_11-25-44/”，将数据文件拷贝到datadir，从而恢复数据原理：Xtrabckup启动时，会对数据库加一个瞬时的锁，此时开始，数据库发生的所有redolog和undospace都会被Xtrabckup记录，同时还在拷贝ibd文件。当恢复的时候，Xtrabckup先利用InnoDB源码中的恢复事务函数把当时时间点之后的数据回滚，然后就可以得到一个完整备份时拷贝，然后覆盖现有文件即可。官方文档：XtraBackupHowto,17,学习交流PPT,MySQL关键代码实现分析,主要数据结构主要算法,18,学习交流PPT,主要数据结构,THD线程描述符（sql/sql_class.h）包含处理用户请求时需要的相关数据，每个连接会有一个线程来处理，在一些高层函数中，此数据结构常被当作第一个参数传递。NET网络连接描述符（sql/mysql_com.h）网络连接描述符，对内部数据包进行了封装，是client和server之间的通信协议。TABLE数据库表描述符（sql/table.h）数据库表描述符，分成TABLE和TABLE_SHARE两部分。FIELD字段描述符（sql/field.h）域描述符，是各种字段的抽象基类。,19,学习交流PPT,主要算法,排序算法：MySQL排序采用sort_buffer_size设定的线程独占内存进行排序，如果缓存里内放下要排序的数据，那么MySQL会采取载入所有要返回的值进入缓存排序；如果缓存放不下，则把要排序的列放入缓存排序，然后再回表查询返回结果，如果依然放不下，就会采用归并排序，排好序的部分放入磁盘，载入未排好序的部分继续在内存中排序，然后和磁盘上的部分二路归并，直到排序全部完成。详细参考：数据库算法与数据结构系列排序相关连接算法：MySQL关联采用j