MySQL基础技能与原理――基本原理.pptVIP

MySQL基础技能与原理——基本原理 MySQL DBA Team 彭立勋（） 内容概要 1. MySQL体系架构 2. InnoDB特点 3. MySQL高级调优 4. MySQL复制原理 5. MySQL高级备份 6. MySQL关键代码实现分析 MySQL体系架构 MySQL结构层次 MySQL处理流程 存储引擎机制 MySQL结构层次 MySQL处理流程 当客户端链接上mysql服务端时，系统为其分配一个链接描述符thd，用以描述客户端的所有信息，将作为参数在各个模块之间传递。一个典型的客户端查询在MySQL的主要模块之间的调用关系如图所示： 存储引擎机制 MySQL提供一个抽象层，允许不同的存储应请使用相同的API对表进行访问。该接口通过一个被称为Handler的抽象类来实现，该处理器提供了一些可实现基本操作的方法，入打开和关闭表，连续扫描记录，按照键值检索记录，存储记录以及删除记录。 每个存储引擎都执行处理器的一个子类以实现接口方法，以便将处理器操作转化为特定存储引擎的此层次存储/检索API调用 MySQL的主要存储引擎有：MyISAM(非事务引擎)/InnoDB(事务引擎)/Archive(归档引擎)/Memory(内存引擎)/NDB(集群引擎) 其他特殊引擎：Infobirght(数据仓库引擎) InnoDB特点 聚集索引 行级锁 数据文件 聚集索引 InnoDB实现两种B+Tree索引，一种是列值为Key，主键位置为Value即 (列值, 主键位置) 的非主键索引（Secondary Index），另一种是主键索引，两种索引的每个叶子节点都有一个双向指针分别指向前驱和后继节点。 主键索引即聚集索引（Cluster Index），它不仅有主键，而且有主键所属的全部数据，所以在InnoDB中，主键索引即数据。 在InnoDB中，即使用户不指定主键，InnoDB也会生成一个隐含主键，这种情况下，InnoDB的性能比采用序列主键性能下降30%左右。 详细参考：《MySQL索引与存储方式对性能的影响》 《数据库算法与数据结构系列——B树相关》 聚集索引（图） 行级锁 InnoDB为了实现高并发，实现了一个行级锁。 不同于Oracle的行级锁，InnoDB的行级锁是“间隙锁”，即如果操作 1 col1 10，哪怕只有col1=3一条记录，在可重复读隔离级别下，也无法插入 col1=2 或 col1=4 等在1~10范围内的记录。 InnoDB的行级锁加在主键索引上，而不是加在数据块上。 详细参考：《InnoDB行锁的实现分析》 数据文件 InnoDB的数据文件包括： （innodb_file_per_table） .frm 表结构文件 .ibd 表数据文件（数据目录）、系统元数据和undo space（共享表空间） ib_logfile* 重做日志文件，相当于Oracle的Redo Log MySQL高级调优 索引调整 重要性能参数调整 编译参数调优 索引调整 主键索引必须有，并且按要满足序列化，递增或者递减 索引合并：索引顺序不影响结果时，将可复用度高的字段放在索引的前面 主键排序无需索引：假设最后输出结果按主键排序，无需考虑主键字段加入索引，InnoDB数据完全按主键顺序组织，最终取出数据既是按主键排序。 关联表存在关联字段和条件字段，优先将条件字段放在关联字段前建立组合索引。 实在无法避免的列运算但又需要很高的性能，可以通过新列和Trigger（或程序实现）实现函数索引：《给MySQL做虚拟的“函数索引”》 重要性能参数调整 Sync_binlog 同步刷新binlog的秒数，设为0则交由操作系统自己刷新，MySQL不强制刷新，除0外，数值越小性能越差 innodb_flush_log_at_trx_commit 0性能最好，但无法保证数据安全，断电可能损失较多数据。2性能较好，但可能损失1s的数据。1性能最差，不丢失数据。 0只保证日志刷新到文件，不保证刷新到磁盘，2保证每秒刷新到磁盘，1保证每次提交刷新到磁盘。 关于Linux Cache参考：《Linux Cache 机制探究》 innodb_max_dirty_pages_pct InnoDB Buffer Pool中脏页的比例，建议设置为3%或者更小。如果脏页比例超过这个参数，就会回写到磁盘。如果这个值太大，每次关闭就会有很多脏页需要回写。 编译参数调优 GCC参数（以Xeon 55XX系列64位系统为例） CXX=gcc CHOST=”x86_64-pc-linux-gnu” CFLAGS=”-O3 -fomit-frame-pointer -pipe -march