Transcript 项目演示PPT模板 Young 090307

MySQL基础技能与原理
——基本原理
MySQL DBA Team
彭立勋（http://www.penglixun.com）
内容概要
•
•
•
•
•
•
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=nocona -mfpmath=sse -m128bitlong-double -mmmx -msse -msse2 -maccumulate-outgoing-args -m64 -ftree-looplinear -fprefetch-loop-arrays -freg-struct-return -fgcse-sm -fgcse-las -frenameregisters -fforce-addr -fivopts -ftree-vectorize -ftracer -frename-registers -minlineall-stringops -fbranch-target-load-optimize2″
CXXFLAGS=”${CFLAGS}”
MySQL参数
./configure –prefix=/usr/alibaba/install/mysql-ent-official-5.1.56 –with-serversuffix=alibaba-mysql –with-mysqld-user=mysql –withplugins=partition,blackhole,csv,heap,innobase,myisam,myisammrg –withcharset=utf8 –with-collation=utf8_general_ci –with-extracharsets=gbk,gb2312,utf8,ascii –with-big-tables –with-fast-mutexes –with-zlibdir=bundled –enable-assembler –enable-profiling –enable-local-infile enablethread-safe-client –with-readline –with-pthread –with-embedded-server –withclient-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复制应用的方式来还原数据。
•
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物理热备份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启动时，会对数据库加一个瞬时的锁，此时开始，数据库发生
的所有redo log和undo space都会被Xtrabckup记录，同时还在拷贝ibd文件。当恢
复的时候，Xtrabckup先利用InnoDB源码中的恢复事务函数把当时时间点之后的
数据回滚，然后就可以得到一个完整备份时拷贝，然后覆盖现有文件即可。
•
官方文档：《XtraBackup Howto》
MySQL关键代码实现分析
•
•
主要数据结构
主要算法
主要数据结构
•
THD 线程描述符（sql/sql_class.h）
•
包含处理用户请求时需要的相关数据，每个连接会有一个线程来处理，在一些
高层函数中，此数据结构常被当作第一个参数传递。
•
•
NET 网络连接描述符（sql/mysql_com.h）
网络连接描述符，对内部数据包进行了封装，是client和server之间的通信协议。
•
•
TABLE 数据库表描述符（sql/table.h）
数据库表描述符，分成TABLE和TABLE_SHARE两部分。
•
•
FIELD 字段描述符（sql/field.h）
域描述符，是各种字段的抽象基类。
主要算法
•
•
•
•
•
•
•
•
•
排序算法：
MySQL排序采用sort_buffer_size设定的线程独占内存进行排序，如果缓存里内放
下要排序的数据，那么MySQL会采取载入所有要返回的值进入缓存排序；如果
缓存放不下，则把要排序的列放入缓存排序，然后再回表查询返回结果，如果
依然放不下，就会采用归并排序，排好序的部分放入磁盘，载入未排好序的部
分继续在内存中排序，然后和磁盘上的部分二路归并，直到排序全部完成。
详细参考：《数据库算法与数据结构系列——排序相关》
连接算法：
MySQL关联采用join_buffer_size设定的线程独占内存进行Join操作，Join过程会采
用下推算法，如果有WHERE条件，MySQL会先进行过滤，然后才用关联字段去
匹配被驱动表，这样可以有效减少参与关联的记录数。
详细参考：《MySQL LEFT/RIGHT JOIN算法效率分析》
缓存算法：
MySQL缓存才用LRU算法，由系统维护一个LRU双向链表。
详细参考：《数据库算法与数据结构——Cache&Buffer&Lock》
谢谢