MySQL基本原理

1、整理课件MySQL基础技能与原理基础技能与原理基本原理基本原理MySQL DBA Team 彭立勋（http:/）整理课件内容概要1. MySQL体系架构2. InnoDB特点3. MySQL高级调优4. MySQL复制原理5. MySQL高级备份6. MySQL关键代码实现分析整理课件MySQL体系架构MySQL结构层次MySQL处理流程存储引擎机制整理课件MySQL结构层次整理课件MySQL处理流程当客户端链接上mysql服务端时，系统为其分配一个链接描述符thd，用以描述客户端的所有信息，将作为参数在各个模块之间传递。一个典型的客户端查询在MySQL的主要模块之间的调用关系如图所示：整理

2、课件存储引擎机制MySQL提供一个抽象层，允许不同的存储应请使用相同的API对表进行访问。该接口通过一个被称为Handler的抽象类来实现，该处理器提供了一些可实现基本操作的方法，入打开和关闭表，连续扫描记录，按照键值检索记录，存储记录以及删除记录。每个存储引擎都执行处理器的一个子类以实现接口方法，以便将处理器操作转化为特定存储引擎的此层次存储/检索API调用MySQL的主要存储引擎有：MyISAM(非事务引擎)/InnoDB(事务引擎)/Archive(归档引擎)/Memory(内存引擎)/NDB(集群引擎)其他特殊引擎：Infobirght(数据仓库引擎)整理课件InnoDB特点聚集索引行

3、级锁数据文件整理课件聚集索引InnoDB实现两种B+Tree索引，一种是列值为Key，主键位置为Value即 (列值, 主键位置) 的非主键索引（Secondary Index），另一种是主键索引，两种索引的每个叶子节点都有一个双向指针分别指向前驱和后继节点。主键索引即聚集索引（Cluster Index），它不仅有主键，而且有主键所属的全部数据，所以在InnoDB中，主键索引即数据主键索引即数据。在InnoDB中，即使用户不指定主键，InnoDB也会生成一个隐含主键，这种情况下，InnoDB的性能比采用序列主键性能下降30%左右。详细参考：MySQL索引与存储方式对性能的影响 数据库算法与数

4、据结构系列B树相关整理课件聚集索引（图）整理课件行级锁InnoDB为了实现高并发，实现了一个行级锁。不同于Oracle的行级锁，InnoDB的行级锁是“间隙锁”，即如果操作 1 col1 10，哪怕只有col1=3一条记录，在可重复读隔离级别下，也无法插入 col1=2 或 col1=4 等在110范围内的记录。InnoDB的行级锁加在主键索引上，而不是加在数据块上。详细参考：InnoDB行锁的实现分析整理课件数据文件InnoDB的数据文件包括： （innodb_file_per_table）.frm 表结构文件.ibd 表数据文件（数据目录）、系统元数据和undo space（共享表空间）i

5、b_logfile* 重做日志文件，相当于Oracle的Redo Log整理课件MySQL高级调优索引调整重要性能参数调整编译参数调优整理课件索引调整主键索引必须有，并且按要满足序列化，递增或者递减索引合并：索引顺序不影响结果时，将可复用度高的字段放在索引的前面主键排序无需索引：假设最后输出结果按主键排序，无需考虑主键字段加入索引，InnoDB数据完全按主键顺序组织，最终取出数据既是按主键排序。关联表存在关联字段和条件字段，优先将条件字段放在关联字段前建立组合索引。实在无法避免的列运算但又需要很高的性能，可以通过新列和Trigger（或程序实现）实现函数索引：给MySQL做虚拟的“函数索引”整

6、理课件重要性能参数调整Sync_binlog同步刷新binlog的秒数，设为0则交由操作系统自己刷新，MySQL不强制刷新，除0外，数值越小性能越差innodb_flush_log_at_trx_commit0性能最好，但无法保证数据安全，断电可能损失较多数据。2性能较好，但可能损失1s的数据。1性能最差，不丢失数据。0只保证日志刷新到文件，不保证刷新到磁盘，2保证每秒刷新到磁盘，1保证每次提交刷新到磁盘。关于Linux Cache参考：Linux Cache 机制探究innodb_max_dirty_pages_pctInnoDB Buffer Pool中脏页的比例，建议设置为3%或者更小。

7、如果脏页比例超过这个参数，就会回写到磁盘。如果这个值太大，每次关闭就会有很多脏页需要回写。整理课件编译参数调优GCC参数（以参数（以Xeon 55XX系列系列64位系统为例）位系统为例）CXX=gcc CHOST=”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-loo

8、p-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参数参数./configure prefix=/usr/alibaba/install/mysql-ent-official-5.1.56 with-server-suffi

9、x=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

10、-infile enable-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的Xeon 55XX系列CPU编译参数整理课件MySQL复制原理MySQL Replication的基本原理是通过binlog复制应用的方式来还原数

11、据。MySQL通过Server_id来识别binlog由哪台主机产生，因此即使双Master复制，也不会出现binlog被重复应用复制线程分为Slave IO和Slave SQL两个，Slave IO线程只负责注册到Master上，读取binlog，然后解析到本地，Slave SQL线程只负责把Slave IO线程产生的可执行SQL应用到本地。避免主键冲突，MySQL提供了auto_increment_increment和auto_increment_offset来控制主键生成的序列，只要双Master的两台主机没有相同的序列，就绝对不可能复制冲突。整理课件MySQL高级备份Xtrabckup

12、物理热备份InnoDB数据：“innobackupex /data/backups” 就可以创建一个备份到/data/backups目录下，只要看到 “completed OK!” 就是备份成功。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启动时，会对数

13、据库加一个瞬时的锁，此时开始，数据库发生的所有redo log和undo space都会被Xtrabckup记录，同时还在拷贝ibd文件。当恢复的时候，Xtrabckup先利用InnoDB源码中的恢复事务函数把当时时间点之后的数据回滚，然后就可以得到一个完整备份时拷贝，然后覆盖现有文件即可。官方文档：XtraBackup Howto整理课件MySQL关键代码实现分析主要数据结构主要算法整理课件主要数据结构THD 线程描述符线程描述符（sql/sql_class.h）包含处理用户请求时需要的相关数据，每个连接会有一个线程来处理，在一些高层函数中，此数据结构常被当作第一个参数传递。NET 网络连接

14、描述符网络连接描述符（sql/mysql_com.h）网络连接描述符，对内部数据包进行了封装，是client和server之间的通信协议。TABLE 数据库表描述符数据库表描述符（sql/table.h）数据库表描述符，分成TABLE和TABLE_SHARE两部分。FIELD 字段描述符字段描述符（sql/field.h）域描述符，是各种字段的抽象基类。整理课件主要算法排序算法排序算法：MySQL排序采用sort_buffer_size设定的线程独占内存进行排序，如果缓存里内放下要排序的数据，那么MySQL会采取载入所有要返回的值进入缓存排序；如果缓存放不下，则把要排序的列放入缓存排序，然后再回表查询返回结果，如果依然放不下，就会采用归并排序，排好序的部分放入磁盘，载入未排好序的部分继续在内存中排序，然后和磁盘上的部分二路归并，直到排序全部完成。详细参考：数据库算法与数据结构系列排序相关连接算法：连接算法：MySQ