本文主要是介绍MySql技术内 幕：InnoDB存储引擎 读书笔记，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
| 
|
| 书名 | 《MySql技术内幕：InnoDB存储引擎》 |
| 作者 | 姜承尧 |
| 书摘 |
| 第一章：MySQL体系结构和存储引擎
-
定义数据库和实例：
-
定义数据库和实例
-
数据库：文件的集合，frm、MYD、MYI、ibd等 -
实例：是有后台线程以及一个共享内存区组成。操作数据库文件。单进程 -
配置文件的读取：/etc/my.cnf --> /etc/mysql/my.cnf --> /usr/local/mysqetc/my,cnf --> ~/.my,cnf
-
MySQL体系结构：
-
连接池组件： -
管理服务和工具组件： -
SQL接口组件： -
查询分析器组件： -
优化器组件： -
缓冲组件： -
插件式存储引擎：基于表的 -
物理文件：
-
MySQL存储引擎：
-
InnoDB：
-
支持事务，面向在线事务处理(OLTP)的应用. -
特点：行锁设计、支持外键、支持非锁定读(默认读取操作不会产生锁) -
数据存放在逻辑表空间，每个表放到独立的ibd文件 -
使用多版本控制MVCC，开获得高并发性，实现了4种隔离级别，默认REPEATABLE，同时，使用一种被称为next-key locking的错略避免幻读，提供了插入缓冲、二次写、自适应哈希索引、预读等高性能功能。 -
采用聚集方式，每张表的存储，按主键顺序存放，没有主键会生成一个6字节的ROWID
-
MyISAM：
-
不支持事务、表锁。支持全文所有、面向OLAP数据库 -
缓冲池只缓冲了索引文件， -
数据文件：MYD：索引文件：MYI -
-
NDB：
-
集群存储引擎，数据全部放内存，查找快，通过添加节点增加数据库性能 -
缺点：join连接操作是在mysql数据库层完成的，存在巨大网络开销。
-
Memory：
-
数据存放内存，不安全，默认使用哈希索引 -
速度快，只支持表锁，并发性能差，不支持TEXT、BLOB类型，不存在变长字段，varchar，回浪费空间，常用作数据临时表。 -
mysql 使用Memory存储时，如果中间结果存在TEXT，等不符合的数据，会将其转换到MyISAM存储引擎表放到磁盘。
-
Archive：
-
只支持，insert、select，5.1支持索引，存储引擎使用zlib算法将数据行进行压缩后存储，1:10 -
适合归档，支持行锁，不支持事务
-
Federated：
-
不存放数据，指向一台远程MySQL数据库服务器上的表
-
Maria：
-
支持缓存和索引文件，行锁，提供MVCC，事务和非事务安全的选项，为MyISAM进化版。
-
其他：
-
连接MySQL：
-
TCP/IP：mysql会先校验查一张权限视图，然后判断是否允许连接Mysql实例。 -
mysql -u db2-dev -S /tmp/mysql.sock
|
| 第二章：InnoDB存储引擎
-
概述：支持：行锁，MVCC、外键、一致性非锁定读等 -
版本 -
InnoDB体系架构：
-
-
后台线程：
-
Master Thread
-
缓冲池中数据异步刷新到磁盘、保证数据的一致性、脏页的刷新、合并插入缓冲、UNDO页的回收
-
IO Thread：AIO的IO回调线程
-
write、read、insert buffer、log IO Thread，四个 -
innodb_read_io_threads 和 innodb_write_io_threads配置 -
show engine innodb status -
show variables like '%read%';
-
Purge Thread：
-
回收已经使用并分配的undo页。 -
innodb_purge_threads
-
Page Cleaner Thread：
-
脏页刷新，减轻Master负担，以及对用户查询查询的阻塞。 -
innodb_page_cleaners
-
内存：
-
缓冲池：
-
读取页操作时，将页fix到缓冲池中，允许多个缓冲池。 -
修改：先修改缓冲池的数据，再以一定频率刷新到磁盘。机制：Checkpoint； -
配置：
-
innodb_buffer_pool_size -
innodb_buffer_pool_instances 数量
-
数据：索引页、数据页、undo、插入缓冲、自适应哈希索引、InnoDB存储的锁信息、数据字典信息等。
-
LRU List、Free List 和 Flush List
-
缓冲区管理：LRU 最近最少使用算法， -
InnoDB策略：新读的页插入到列表中间5/8处。 -
参数：innodb_old_blocks_pct, = 37% midpoint为分界线，前面是new数据，后面是old数据 -
参数：innodb_old_blocks_time，mid的数据多久之内再次读取，会加入到new里面。 -
可以算取热点数据的百分比，以及热点数据的读取时间，配置参数。合理95%，低于需检查是否全表扫描 -
LRU列表中的页被修改后，就是脏页。需要checkpoint机制刷新到磁盘 -
脏页存在于LRU和Flush列表，LRU列表管理缓冲池中页的可用性，Flush列表用来将页刷新到磁盘。
-
重做日志缓冲：是循环使用的
-
缓冲池到磁盘的时机：
-
默认每一秒将缓冲池的重做日志信息刷新到磁盘。 -
每个事物提交 -
缓冲池剩余空间小于1/2
-
innodb_log_buffer_size: 64M
-
Checkpoint技术：
-
原因：为了解决内存数据丢失，没有刷新到磁盘 -
解决方式：Write Ahead Log策略：当事务提交时，先写重做日志，在修改页， -
解决的问题：确保持久性
-
缩短数据库恢复时间 -
缓冲池不够用时，刷新脏页 -
重做日志不可用时，刷新脏页
-
版本号：页、重做日志、checkpoint都有 -
innodb对于checkpoint的规则很复杂
-
Master Thread：异步，1-10s一次 -
FLUSH_LRU_LIST：保证LRU列表中又100个空闲页可用现在，这个操作是，Page Cleaner线程在执行。innodb_lru_scan_depth : 1024 页 -
Async/Sync Flush Checkpoint：重做日志不可用时，重做日志快满的时候75%，mysql 5.6之前会阻塞触发的线程，之后的版本在Page Cleaner线程中执行。 -
innodb_max_dirty_pages_pct : 75%脏页数据占据比，强制Checkpoint
-
Master Thread 工作方式
-
InnoDB 1.0x版本之前的Master Thread
-
最高优先级线程，多个loop组成，主循环，后台循环，刷新循环，暂停循环等。 -
每秒操作
-
日志刷新到磁盘没提交事物也算（总是） -
合并插入缓冲（可能）
-
1s内IO次数小于5次，表明压力小可以进行合并插入缓冲
-
至多刷新100个脏页到磁盘（可能）
-
缓冲池中的脏页比，默认90%
-
如果当前没有用户活动，切换到background loop（可能）
-
每10秒操作
-
刷新100脏页到磁盘（可能）
-
10s内IO操作小于200次，IO能力充足
-
合并至多5个插入缓冲（总是） -
将日志缓冲刷新到磁盘（总是） -
删除无用的Undo页（总是）
-
提交事务后的undo页
-
刷新100个或者10%脏页到磁盘（总是）
-
超过70%的脏页，刷新100不然刷新10%
-
background loop：空闲或关闭时
-
删除无用Undo页（总是） -
合并20个插入缓冲（总是） -
跳回到主循环（总是） -
不断刷新100个页直到符合条件（可能跳转到flush loop中完成）
-
InnoDB 1.2x版本之前的Master Thread，的改进
-
由于硬编码，100个脏页20个插入缓冲等，太慢
-
磁盘IO吞吐量：innodb_io_capacity：200 -
刷新数量为：innodb_io_capacity -
合并插入缓冲：innodb_io_capacity * 5%
-
脏页的占比90%太高，太低磁盘压力大
-
innodb_max_dirty_pages_pct：综合比较定于到了75% -
自适应刷新：innodb_adaptive_flushing，每秒刷新脏页的数量，之前是100.系统通过生成重做日志的数量来决定。
-
full purge操作最多20个undo页。
-
innodb_purge_batch_size：300 可修改
-
可通过各个循环进行的比例判断服务器是否压力大
-
InnoDB 1.2x版本
-
刷新脏页的操作分离到单独的Page Cleanner Thread。
-
InnoDB 关键特性
-
插入缓冲：物理页,性能提升
-
解决非顺序性的插入带来的索引消耗。 -
使用条件：辅助索引，且不是唯一索引 -
操作：对非聚集索引，先判断非聚集索引页是否在缓冲池中，在直接插入，不在先插入inset buffer。数量大时，会进行合并插入缓冲，大大提高了非聚集索引的插入性能。 -
写密集会占用过多缓冲池内存，默认最大可占到1/2。 -
配置：ibuf_pool_size_per_max_size: 3 插入缓冲可占用最大缓存比，1/3 -
升级：change buffer：可对DML操作进行缓冲，INSET、DEL、UP，各自对应一个buffer。
-
update操作；先标记删除，然后真正删除 -
innodb_change_buffer_max_size: 25 等于1/4，最大有效值为50。
-
实现：insert buffer内部是B+树(全局一个树)，每个叶子结点是一个search key 9字节
-
Space：4表id、marker：1兼容老版本、offset：4偏移量。 -
插入时，先构造一个key
-
两次写: 可靠性
-
问题：redo损坏，数据库宕机，恢复数据需要一个数据副本。 -
doublewrete：分为两部分
-
内存中的doublewrete buffer：2M -
物理磁盘共享空间中连续的128个页。2个区
-
操作：对脏页刷新的时候，先复制到内存buffer，然后每次1M的写入磁盘共享空间。然后再同步到磁盘。
-
自适应哈希索引.
-
InnoDB会监控表上各索引页的查询，如果发现建立哈希索引可以带来速度提升，就建立。通过缓冲池的B+树页构造而来的。建立速度很快，不需要对整张表结构建立索引，InnoDB会自动给某频率给热点页建立索引。 -
要求：这个页的连续访问模式必须是一样的。 -
提升：读写速度2倍。辅助索引的连续操作5倍以上完全自动化。
-
异步IO:AIO
-
多个io请求异步，可以合并io请求（连续页的访问） -
参数：innodb_use_native_aio ，是否启用本地aio，负责的话会采用代码模拟aio速度会提升75% -
使用：脏页的刷新，磁盘的写入都是aio
-
刷新邻接页
-
当刷新一个脏页时，InnoDB会检测改页所在区的所有页，如果是脏页，就一起刷新。可以对多个io进行合并 -
问题：将不怎么脏的临近页刷新后，很快就脏了，固态硬盘的iops比较高，有必要吗？ -
参数：innodb_flush_neighbors：1
-
启动、关闭、恢复、
-
-
关闭配置参数：innodb_fast_shutdown：1 0，1，2
-
0:必须完成所有的full purge和merge insert buffer。并且将所有脏页刷新。可能需要几小时，在进行InnoDB升级时可以设置为0 -
1：只需要完成缓冲池中的脏页就可以了。 -
2: 只需要将日志都卸乳日志文件，下次开启会进行恢复操作。
-
恢复参数：innodb_force_recovery:0
-
时机：数据页corrupt，宕机，等 -
0: 进行所有的恢复操作。 -
1: 会略煎茶道的corrupt页 -
2: 阻止MasterThread线程的运行，主线程的full puege会造成宕机 -
3：不进行事物会滚：对表进行改变，人为操作更快 -
4: 不进行插入缓冲的合并操作 -
5: 不查看撤销日志（undo）InnoDB存储引擎会将未提交的事物视为已提交。 -
6: 不进行前滚的操作
|
| 第二章：文件
-
参数文件：
-
通过：show variables like ‘%xxx%’； -
参数类型：
-
动态参数：运行时可改@@global。静态参数不可改@@session
-
日志文件：
-
错误日志：启动运行关闭过程的日志
-
日志目录：show variables like “%log_error%
-
二进制文件：binary log
-
记录所以对数据库的更改操作。 -
作用：
-
恢复：point-in-time的恢复 -
复制：主从同步 -
审计：判断是否对数据库进行注入攻击
-
参数：
-
路径：show variables like ‘%datadir%’ -
max_binlog_size：满了会开启下一个日志，后缀+1 -
binlog_cacahe_size：未提交的事务会放到cache中，提交后写入log中，binlog是基于会话的，默认32k,超过会写入临时文件
-
通过show global status like ‘%binnglog_cache%’：查看缓冲使用次数和，临时文件使用次数。
-
Bing log-do-db、binglog-ignore-db表示哪些写入、忽略库的日志 -
binlog_format：log记录的格式
-
statement：逻辑sql语句，隔离级别：可重复度 -
row：表的行更改情况，占内存 -
mined：
-
慢查询日志：
-
条件大于这个值：show variables like '%long_query_time%’; 2s -
开关：log_slow_admin_statements: off 默认关 -
没有索引sql日志：log_queries_not_using_indexes：默认开 -
没有使用索引记录的次数：防止slow log不断增大降低性能：log_throttle_queries_not_using_indexes：0 没有限制 -
文件查询命令：mysqldumpslow： -
慢sql存数据库查询：show create table mysql.slow_log\G;
-
ENGINE=CSV 存储引擎，查询效率低，大数据查询可设置为MyISAM。并在start_time列加索引
-
存储方式：show variables like '%log_ou%’; 动态参数，全局变量， -
慢查询的统计包含：逻辑读取和物理读取：逻辑指：磁盘+缓冲池，物理指：磁盘
-
参数：long_query_io：逻辑io次数限制 -
参数：slow_query_type：记录log限制类型。
-
查询日志：
-
所有对数据库的请求记录，可以放在general_log表中
-
套接字文件：
-
show variables like ‘socket’； /tmp/mysql.sock
-
pid文件：
-
Mysql实例启动后，会会将进程id写入一个文件中，该文件为pid文件由参数pid_file控制。show variables like ‘pid_file’；
-
表结构定义文件：
-
后缀为：.frm
-
InnoDB存储引擎文件：
-
InnoDB采用将存储的数据按表空间进行存放设计，默认10MB，名为idbata1的文件，可通过innodb_data_file_path设置。 -
innodb_file_per_table：ON，每张表生成单独的.ibd表空间文件。仅存储，数据，索引，插入缓冲BITMAP等信息。其余默认在表空间。 -
重做日志文件：
-
在innodb存储引擎的数据目录下会有两个名为ib_logfile0和ib_logfile1的重做日志文件，记录事务的日志。 -
redo和binlog的区别：
-
redo只记录有关改存储引擎本身的事物，binlog记录所有mysql的日志 -
内容：二进制日志是逻辑日志，记录每个事物的具体操作内存。redo记录的是每个页的更改的物理情况 -
写入时间：binlog是事务提交之前提交，只写磁盘一次。redo，在一个事务中会不断有重做日志写入。
-
-
重做日志的写入：
-
先写入一个重做日志缓冲中，然后按照一定条件顺序写入日志文件。 -
从缓冲写入磁盘是按512字节写入，也就是一个扇区，写入的最小单位。可以保证写入必定成功。不需要双写。 -
主线程每秒会将重做日志写入磁盘不论事务提交状态。还有就是参数控制的innodb_flush_log_at_trx_commit，提交事务的时候要不要提交重做日志。
-
0:不写 -
1:写伴有fsync的调用 -
2：写到文件系统的缓存中。
|
| 第三章：表
-
索引组织表：innoDB中表是根据主键顺序存放，
-
没有主键的情况下的选择：
-
判断表中是否有单列非空唯一索引（Unique NOT NULL） -
自动创建一个6字节大小的指针：
-
InnoDB逻辑存储结构：
-
表空间(tabespae) > 段(segment) > 区(extent) > 页(块) > 行 -
表空间：
-
存储结构的最高层，InnoDB默认有一个共享表空间idbdata1， -
参数：innodb_file_per_table,则每张表内的数据可以单独放到一个表空间，仅仅是数据，其余还在共享空间。 -
执行sql会进入undo，事务提交会进入redo，rollback后undo不会立刻回收
-
段：
-
表空间是由各个段组成，有数据段、索引段、回滚段等。 -
索引段即为B+树的非索引节点，数据段为叶子节点。 -
段由引擎自身操作，DBA无法操作。
-
区：
-
连续页组成的空间，任何情况下每个区都是1MB，InnoDB存储引擎第一次从磁盘申请4~5个区，默认，引擎页大小为16KB，一个区中一共有64个连续的页。 -
1.2x版本增加参数innodb_page_size，可设置页大小为4K，8K -
innodb_file_per_table:OF开启的话，新建表结构，初始大小96kb？
-
原因：区中是64个连续的页至少1M，每个段开始时，先用32个页大小的碎片页存放数据，使用完了再申请64页的区，开始时对于小表和undo段，可以减少磁盘消耗。 -
实验流程：
-
新建表table；默认96k，6个页。一行字段7000+字节。2行一个页。 -
插入两行数据，只占一个页，索引是一个叶子节点，不需要索引。 -
再插入一条数据，B+树分裂新产生了一个索引页，两个数据页。 -
再插入60条，此时，索引段还是一个页，数据段32个页， -
再插入1条：数据段超过32页，申请一个区1MB，64个页。
-
页：常见页类型：
-
数据页（B-tree Node） -
undo页（undo Log Page） -
系统页（System Page） -
事务数据页（Transaction system Page） -
插入缓冲位图页（Insert Buffer Bitmap） -
插入缓冲空闲列表页（Insert Buffer Free List） -
未压缩的二进制大对象页（Uncompressed BLOB Page） -
压缩的二进制的大对象页（compressed BLOB Page）
-
行：
-
InnoDB是面向列的，按行存放 -
每个页对多存放16KB/2 -200行的记录7992行
-
InnoDB行记录格式
-
InnoDB提供两种格式存储Compact和Redundant行记录。Redundant是为了兼容老版本。新的是Compact。
-
数据库实例的作用之一就是读取行记录，如果用户指定行记录的规则，也可自行编写工具读取。
-
Compact行记录格式：5.0版本引入，更高效的存储。
-
-
首部是一个非NULL变长自从长度列表，按照列顺序，逆序放置， -
列长度小于255字节，用一字节表示，大于255用2字节表示。 -
变长字段长度，最长2字节，因为varchar类型最长65535。NULL标志位1字节。 -
头部信息5字节（40位）。 -
列数据中，NULL不占任何空间，只占用NULL标志位 -
每行数据还有两列隐藏列，事务ID列和回滚指针列。分别6字节和7字节，如没有主键，则增加6字节的rowid。 -
示例： -
示例2：有null值，06 = 000000110代表，第二列和第三列为null不管是定长还是不定长；
-
Redundant行记录格式：
-
-
头部信息6字节（48位） -
char的null占空间
-
行溢出数据：
-
一般默认BLOB、LOB、这类大对象类型的存储会放到数据页之外。但是BLOB可能不会，varchar类型可能会。 -
varchar长度最长65532（字节），因为有其余开销，跟编码有关，GBK会除2，utf8会除3. -
官方规定，所有varchar列综合不能超过65535 -
一个数据页最多16k（16384字节），放在B-tree的node中，当发生行溢出，会放在也类型为UncompreeBLOB页中。未压缩二进制大对象页。 -
例如：插入一个65532的varchar数据，会产生一个node节点，和4个大对象页，数据全在大对象页，数据页存放的是前缀和偏移量 -
什么时候溢出：规定B+tree一页最少放两条数据，不能放两个数据的时候会放到溢出页。阈值为：8098 -
Text和Blog数据也是 一样，存在阈值，超过阈值存放BLOB页，数据页只存放768字节。
-
Compressed和Dynamic行记录格式
-
1.0x版本引入新的文件格式（页），以前支持的Compact和Redundant格式称为Antelope文件格式，新的文件格式称为Barracuda文件格式，新的格式的BLOB数据存储，完全采用行溢出，数据中存放的是指针。 -
Compressed会对行数据zlib算法进行压缩，对于BLOB、TEXT、VARCHAR很有效
-
CHAR的行结构存储
-
4.1版本之后 char(N) N是指的字符，不同的编码不一样。GBK2字节，utf83字节。 -
如：’ab’ , ‘我们'，都是两个字符，但是存储上，是2，4。因此：UTF-8下的CHAR(10)，最少存放10字节的汉字，和30字节的字母。 -
InnoDB中，char定位为变长长度的字符类型。未占满会填充’ ’， -
在多字节字符集的情况下，char和varchar存储基本上没有区别。
-
InnoDB数据页结构
-
-
组成：
-
File Header ：文件头
-
大小固定38字节
-
Page Header ：页头
-
大小固定56字节 
-
Infimun 和Supremum Records
-
每个数据页都有两个虚拟的行记录，用来限定记录的边界。 -
Infilmun行：记录比该页中任何主键值都要小的值，页目录中记录的n_owned=1 -
Supremum：比任何可能大的值还要大的值，页目录中记录的n_owned=[1，8]
-
User Records：用户记录，行记录
-
行记录：InnoDB存储引擎表总是B+树索引组织的。
-
Free Space ：空闲空间
-
链表结构，当一条记录被删除后，会加到链表中
-
Page Directory ： 页目录
-
索引定位到页后，会加载这一页到内存， 查找具体数据，会更具页目录进行二分查找，得到具体数据的相对位置。时间复杂度很低，快。 -
记录了行记录指针，称为Slots，每槽可能包含多个记录，是一个稀疏目录， -
新增数据n_owned=[4,8]，插入和删除都会对槽进行分裂或平衡操作。
-
File Trailer ：文件结尾信息
-
大小固定8字节，LIL_PAGE_END_LSN -
检验页是否已经完整的写入磁盘，校验页完整性，有一定开销 -
校验算法参数：innodb_checksum_algorithm， -
crc32为5.6.6版本新算法， -
-
——1、2、7记录页信息包含B+树的所有层数等，4、5、6实际的行记录存储空间，大小动态的。
-
InnoDB存储引擎中页的类型
-
Named File Formats机制：
-
用于解决不同版本下页结构兼容性问题，如果不兼容会在错误日志文件中表示
-
约束：数据完整性的手段
-
InnoDB域完整性的保证途径
-
合适数据类型确保值满足特定条件 -
外键约束 -
编写触发器 -
用DEFAULT约束作为强制域完整性的一方面
-
InnoDB本身提供的约束：
-
主键、外键、唯一索引、default、not null
-
约束的创建和查找：
-
表创建时进行约束定义； -
ALERT TABLE命令创建约束 -
唯一索引：create unique index建立
-
约束和索引的区别：
-
约束是一个逻辑概念，保证数据的完整性， -
索引是一个数据结构，既有逻辑概念，还是数据库中的物理存储的方式
-
对错误数据的约束：
-
Mysql对于错误的插入会进行改正，但是会有worning ：如not null插入null 会被改为0，日期’2019-01-01’ 会改成’2019-01-01 00:00:00’ -
参数：sql_mode =STRICT_TRANS_TABLES 严格审核输入的参数，会报错，他得值有很多
-
ENUM 和 SET 约束
-
输入错误的值会爆worning。需要配合mode参数使用。
-
触发器与约束：
-
作用：在执行insert、delete、update、之前或之后自动调用sql命令或存储过程。 -
版本：5.1 -
命令：create trigger，只有super权限的用户才能执行。 一个表最多6个， before，after + IDU -
触发方式为：for each row，每行记录进行触发
-
外键约束：保证参照完整性，
-
被引用的表为父表，引用的为子表。 -
ON DELETE、UPDATE时对父表进行DELETE和UPDATE操作。 -
对子表所做的操作可定义为：
-
casecade：父表DU时子表DU -
set nnull ： 父表DU时子表set null，子表必须允许set null -
no action：父表DU时抛异常，不允许DU -
restrict：父表DU时抛异常，不允许DU -
默认restrict
-
及时检查：耗时间对于大量数据处理可以忽略检查 
-
视图：view sql查询的临时表。
-
试图的作用：
-
主要用途之一：做一个抽象装置
-
物化视图
-
分区表：
-
概述：
-
版本：5.1， -
分区的过程是将一个表或索引分解为多个更小、更可管理的部分。只支持水平分区。 -
是局部分区索引，一个分区中及存放了数据又存放了索引， -
参数：show plugins; partition; -
效果：可能给某些sql语句性能带来提升、主要用于数据库高可用性的管理， -
如果表中存在唯一索引，分区列必须是唯一索引的一部分。如果没有，任何一列都可以作为分区列。
-
分区类型：
-
RANGE：
-
行数据基于属于一个给定连续区间的列值被翻那个如分区。 -
values less than ：分区条件 -
启用后，表不再由一个ibd文件组成，插入数据会严格按照分区的定义今年入不同的分区。 -
使用最多的分区，主要按日期分 -
要根据分区特性写sql
-
LIST：
-
和RANGE类型比，只是分区面向的是离散的值 -
values in： -
不在分区定义中会报错，非连续插入，会造成事务的异常。
-
HASH:
-
根据用户自定义的表达式的返回值来进行分区，返回值不能为负数。 -
目的：数据均匀的分布在各个分区， -
create table语句加上partition by hash(expr) partitions 5；
-
KEY：
-
根据MYSQL提供的哈希函数来进行分区。 -
类似hash，hash是用户定义函数
-
COLUMNS：
-
以上分区中，数据必须是整数， -
COLUMNS支持多种类型：
-
子分区：
-
分区再分区：subpartition，名字唯一
-
分区中的null值：
-
null 是最小的值，会放入最左边分区。 -
list分区下，要指明null属于哪个分区。会报错
-
分区和性能：
-
数据库应用分类：
-
OLTP：在线事务处理：没必要会降低性能，如果查询条件不是分区的列会造成大量io -
OLAP：在线分析处理：分区可以
-
在表和分区间交换数据：
-
5.6开始支持alert table … exchange partition。交换分区数据 -
条件
|
| 第五章：索引与算法
-
InnoDB存储引擎索引概述：常见索引
-
B+树索引： -
全文索引： -
哈希索引：自适应的，InnoDB会更具表使用情况自动我表生成哈希索引，不能人为干预
-
数据结构与算法：
-
二分查找法：顺序存放的数据查找。InnoDB用于 Page Directory 页目录进行二分查找 -
二叉查找树和平衡二叉树：
-
二叉查找树：
-
左子树小于根，根大于右子树 -
-
平衡二叉树：插入需要多次旋转
-
B+树：
-
简介：
-
由B树和索引顺序访问方法演化而来。 -
B+树是为磁盘或其他直接存储辅助设备设计的一种平衡查找树，
-
B+树的插入操作：
-
插入的三种情况： -
删除：B+树使用填充因子来控制树的删除变化，50%是最小值，小于的时候会进行页的合并
-
B+树索引：
-
简介：一般2-4层，机械硬盘1s 100次io。可分为聚集索引和辅助索引 -
聚集索引： 主键构成的B+树，叶子节点存放整行信息。因此可以直接拿到数据每个数据页都通过一个双向链表来进行链接。
-
实际的数据页只能按照一颗B+树进行排序，所以一个表只有一个聚集索引。 -
定义了数据的逻辑顺序，可以进行快速的范围查询， -
-
辅助索引：
-
叶子结点只有索引字段和一个书签，用来告诉在哪里找到索引对应的行数据。 -
通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后获取完整记录。 -
-
B+树索引的分裂：
-
并不是所有的分裂都是从中间分裂的，只有随机插入。 -
InnoDB的Page Header中用三个部分保存插入的顺序信息：可以判断是向左还是向右分裂。
-
PAGE_LAST_INSERT； -
PAGE_DIRECTION： -
PAGE_N_DIRECTION:
-
B+树索引的管理：
-
索引管理：
-
添加：create/drop index -
show index from baseflow_avatar.baseflows;
-
Non_unique：非唯一索引 -
Seq_in_index：索引列的位置 -
Cardinality：索引中唯一的数目的预估值, 越小可以考虑是否有必要存在索引，优化器的选择条件。 -
Sub_part: 列的前多少个数据进行索引，整列索引位NULL； -
Packed：关键字是否被压缩 -
NULL：索引是否含有null -
建议在非高峰的时候，使用ANALYZE TABLE矫正Cardinality字段
-
fast index creation
-
mysql 5.5之前对数据库进行DDL操作的时候入索引，会进行偷梁换柱。很费劲，会造成数据库不可用 -
InnoDB1.0以后支持FIC快速创建索引方式。 -
对于辅助索引的创建，InnoDB会对创建索引的表加上一个S锁，在创建的时候不会重新建表，删除辅助索引只需要更新内部视图，然后标记索引空间为可用， -
FIC在索引的创建过程加了S锁。只读，不能写。只作用于辅助索引，聚集索引一样需要偷梁换柱。
-
online schema channge
-
FaceBook实现的在线执行DDL的方式。指，在事务的创建的过程中，可以有读写事务对表进行操作，提供并发性。
-
online DDL：
-
FIC索然不会创建临时表，但是会阻塞表上的MDL操作。5.6支持 Online DDL在线数据定义操作，其允许辅助索引创建的同事，可以insert、update、delete、的DML操作。 -
使用场景： -
新的alert table 可以选择索引的创建方式
-
-
copy：表示按照5.1之前的模式，创建临时表 -
inplace：索引创建删除不需要创建临时表， -
default：根据参数old_alert_table来判断是通过inplace还是copy算法，默认inplace； -
LOCK: 对标添加锁情况
-
NONE：并发高，事务可执行 -
SHARE：S锁 类似FIC可读，写会阻塞 -
EXCLUSIVE：X锁 不能读写 -
DEFAULT：选择最大的可并发的
-
实现方式： 将DML操作写入缓存中，带完成索引创建后再将重做应用到表上，以此达到数据一致性，缓存大小可由参数innodb_online_alert_log_max_size控制，默认128M。索引创建过程中SQL优化器不会选择正在创建的索引。
-
Cardinality值
-
什么是Cardinality：数值越小代表搜索的范围越小，建立索引越有价值。 -
InnoDB存储引擎的Cardinality统计,
-
采样方法：
-
取得B+树索引中叶子节点的数量A -
默认随机选取8个叶子节点，统计每个页不同记录的个数，P1,P2….P8 -
Cardiality=(P1+…P8) * A / 8;
-
时机：Cardinality更新发送在两个操作，Insert、update且符合策略：
-
表中1/16的数据以及发生过变化 -
stat_modified_counnter>2000000000：表示update次数超过20亿
-
每次统计不一样。 -
innodb_stats_sample_pages：采样数量 -
参数：innodb_stats_method：默认：nulls_equal：null值都一视同仁。nulls_unequal，null都不一样。nulls_ignored：null不算 -
当执行analyze table、show table status、show index以及访问information_schema架构下的表tables和statistics都会导致重新计算索引的Cardinality。 -
1.2新增参数：
-
B+树索引的使用：
-
联合索引：多个列进行索引
-
非首例是排序的。等于 order by 第二列。 -
如果explain 中 extra中有 Using filesort 则需要额外进行一次排序。 -
如：a_b_c三列索引 ，where a=1 and b=2 order by c 是可以直接拿到的。
-
覆盖索引：从辅助索引中就可以拿到查询的记录，而不需要查询聚集索引中的记录。辅助索引占内存小，可以减少io。
-
例如：select count(*) 会选择占内存小的索引。 extra 列的Using index代表有呼气进行了覆盖索引操作。
-
优化器不选择使用索引的情况
-
如果查询的条件是范围辅助索引，但是查询的数据含有非索引的字段，如果量超过表的20%，会选择全表扫。因为走辅助索引的话会造成大量的回表操作。顺序读远远快于离散读。 -
如果不能进行索引覆盖情况，优化器会在查询数据量少的情况下选择使用辅助索引，或者很有自信可以使用force index(xxx)
-
索引提示： index hint 显式的告诉优化器使用哪个索引。
-
大部分情况mysql优化器是准确的，mysql选择错误。 -
索引多的情况，优化器的执行时间开销很大。 -
Use index() 结果以优化器为准 、force index()以force为准。
-
Multi_range Read 优化 MRR： 减少磁盘的随机访问。5.6
-
适用范围：可适用于explain 的type = range 、ref、eq_ref查询 -
好处：
-
MRR是数据访问变得顺序，查询辅助索引的时候，根据得到的结果按照主键的顺序进行顺序查找。 -
减少缓冲池中页被替换的次数。 -
批量处理对键值的查询操作。
-
范围查询和join查询的原理：
-
辅助索引查到的值放到缓存中，顺序是辅助索引的顺序 -
按照rowid排序 -
根据rowid顺序访问时机数据文件。
-
例如：select * from xxx where ctim>’'
-
-
-
-
范围查询，条件拆分为key - value：
-
-
不启用会先拿到范围的key1然后再过滤key2 -
启用会直接查询(k1,k2)(k1,k2)…...
-
开启： 参数：read_rnd_buffer_size：缓冲区大小。
-
Index Condition Pushdown (ICP) 优化。5.6
-
在数据库取出索引的同时，判断是否可以进行where条件过滤，也就是将where的部分过滤操作放在了存储引擎层。在某些查询下，可以大大减少上层SQL层对数据的索取(fetch)，提高性能。 -
优化支持：range、ref、eq_ref、ref_or_null -
extra列可看到 Using index condition。 -
当查询命中索引，且索引中有where字段，
-
自适应哈希索引：只能等值匹配。 -
全文索引：
-
概述：content字段添加索引是支持的。
-
如果是'%xxx%’则不支持，innodb不支持。但是对于搜索引擎和电商网站，则需要。 -
全文搜索：是将存储于数据库中的整本书或者整篇文章中的任意内容信息查找出来的技术，可以根据需要获得任意新信息和统计分析。 -
1.2x版本支持全文检索。
-
倒排索引：存储单词和文档位置的映射关系：
-
inverted file index：{单词，文档id}
-
-
full innverted index：{单词，(文档id, 位置)}
-
-
InnoDB全文索引：
-
采用full inverted Index方式：（DocumentId，Position）=> ilist，在全文索引的表中有两个列，一个word，一个ilist，word有索引，并且可以进行临近搜索。MyISAM不支持 -
倒排索引需要将word存放到一张表中，Auxiliary Table(辅助表) ，共有6张，每张表根据word的Latin编码进行分区，
-
辅助表示持久表，在磁盘，
-
FTS Index Cache(全文检索索引缓存)：红黑树结构，根据(word, ilist) 进行排序, innodb会批量更新Auxiliary table，当成进行查询时，table首先会在FTS中对于的word合并到table再进行查询。 -
FTS Document ID：与word列进行映射的索引列，bigint unsigned not null。唯一索引 -
插入：事务提交完成时，删除，只是删除FTS Cache Index记录。optimize table会物理删除记录，删除会影响性能。 -
SQL： -
限制：
-
全文索引：
-
查询：
|
| 第六章：锁
-
mysql 锁介绍：
-
行锁，以及InnoDB内部多个地方使用：操作缓冲池中LRU列表，一定LRU列表元素，
-
lock 与 latch ： 都是锁
-
latch：轻量级的锁，要求锁定的时间必须非常短，时间长性能很差，
-
InnoDB中latch又分为 mutex(互斥锁) 和 rwlock(读写锁) 用来保证并发线程操作临界资源的正确性，并且通常没有死锁检的机制。
-
Lock的对象是事务，用来锁定的是数据库中的对象，如表、页、行一般lock的对象仅在事务commit或rollback后进行释放。 -
-
InnoDB存储引擎中锁：
-
锁的类型：InnoDB 实现两种行级锁
-
共享锁S Lock：允许事务读一行数据 -
排他锁X Lock：允许事务删除或更新一行数据 -
同一行记录： -
意向锁IX Lock：
-
意向共享锁IS：事务想要获得一张表中某几行的共享锁 -
意向排他锁IX：事务想要获得一张表中某几行的排它锁 -
-
select * from information_schema.INNODB_TRX; -
select * from information_schema.INNODB_locks;
-
一致性非锁定读： Innodb存储引擎通过行多版本控制的方式读取当前执行时间数据库中的行数据。
-
当读取正在删除获修改的数据时，不会等待X锁释放，会直接去读行的快照数据，在undo中，回滚日志中。并发高。 -
没行都有多个版本控制多版本并发控制MVCC！！！ -
事务隔离级别Read Committed 和 Repeatable Read。下InnoDB使用非锁定一致性读。
-
Read Committed：最新快照 -
Repeatable Read：事务开始时快照
-
一致性锁定读：
-
Sql语句的两种两种方式：
-
select … for update： 加x锁，唯一 -
select … lock in share mode：加s锁，可以加多个s。不能加x -
以上必须加事务。事务提交锁释放。
-
自增长与锁：
-
获取自增id：select max(auto_inc_col) from t for update; -
实现方式：AUTO-INC Locking：特殊表锁机制，不是在事务结束，而是在完成对自增长值插入的sql语句后立即释放。插入并发不好 -
MySQL 5.1.22版本提供了参数：
-
innodb_autoinc_lock_mode：
-
外键和锁：
-
用于引用完整性的约束检查。InnoDB会自动加索引。 -
对外键更新时会先select 父表，不是非一致性锁定读，索引会加上S锁。select .... lock in share mode
-
锁的算法：
-
行锁的三种算法
-
Record Lock：单个行记录上的锁，锁住索引记录，主键 -
Gap Lock：间隙锁，锁定一个范围，但不包含记录本身。 -
Next-Key Lock：1+2锁定一个范围并且锁定记录本身，innodb查询就是这种，如查询key= 1 锁定范围为（0，10），谓词锁的一种改进
-
当查询唯一索引时，会进行降级Record Lock，锁住索引本身。 -
查询多列的唯一索引，也会降级。
-
解决Phantom Problem(幻读、不接重复读)
-
innodb默认可重复读repeatable read，默认加的是next-key算法， 读已提交read committed，默认加的是record lock。 -
Select * from xx where a > 1 for update; 加了排他锁，其他事物插入数据后，前后两次读到的数据不一致。 -
-
锁问题：
-
锁可以实现事务的隔离性要求，但是会带来三个为题。 -
脏读：
-
事务对缓冲池中行记录的修改，还没有被提交 -
原因：一个事务可以读到另一个事务中未提交的数据。违反了隔离性。 -
条件：read uncommitted,读未提交，隔离级别
-
不可重读：
-
一个事务内多次读取同一数据集合，结果是不一样的。 -
条件：read committed,读未提交，隔离级别，可以接受不可重复读。（oracle、mss默认） -
存在phantom problem问题：通过next-lock算法可解决，所以Innodb 默认隔离级别是read repeatable。
-
丢失更新：
-
一个事务的更新操作会被另一个事务的更新操作所覆盖，从而导致数据的不一致。 -
在数据库层次：任何事务都不会出现这个问题，因为，即使是read uncommitted，也会对加x锁，会阻塞 -
问题：生产环境中，两个web请求到数据，会先后更新， -
解决：读的时候加x锁。所有的读串行化。无并发。
-
阻塞：
-
innodb_lock_wait_timeout:阻塞等待时间50s -
innodb_rollback_on_timeout:超时是否回滚 -
超时的错误：1205，一般都不会回滚
-
死锁：
-
解决的方式：
-
超时：FIFO方式：问题：先超时的修改的行数多，不合适 -
等待图：进行死锁检测，需要数据库保存两个信息：
-
锁的信息链表 -
事务等待链表 -
时机：每个事务请求锁并发生等待时，会判断是否存在回路，存在则有死锁，innodb会回滚undo量最小的事务。 -
wait-for-graph：检测采用深度优先算法。
-
死锁示例：对外建加索引
-
锁升级：
-
概念：将当前锁的粒度降低：1000行的锁升级为页锁，为了避免锁的开销，会频繁的锁升级。 -
innodb：不存在锁升级的问题：他不是根据每个记录来产生行锁的，是根据每个事物访问的页对锁进行管理的，采用位图的方式，因此不管一个事务锁住了野种一个记录还是多个记录都是要一样的。
|
| 第七章：事务
-
认识事务：
-
ACID：
-
原子atomicity、一致consistency、隔离isolation、持久durability
-
是个各种数据项的执行单元，目的是要么全修改要么都不做，也是区别文件系统的重要特征。需要遵循ACID
-
oracle：默认read committed，但是无法满足隔离性， -
innodb：完全遵循和满足事务的ACID特性。 -
分类：
-
A 原子性： -
C 一致性：事务开始结束后，数据库的完整性约束没有被破坏：如主键的唯一 -
I 隔离性：并发控制、可串行化、锁。所有的数据库都采用一种粒度锁的策略 -
D 持久性：事务提交，结果是永久性的。
-
分类：
-
扁平事物：
-
begin work - commit work
-
带有保存点的扁平事物：
-
支持扁平事务的同时，支持在事务执行过程中回滚到同一事物中较早的一个状态。 -
保存点是易失的，非持久的。 -
可以回到任意节点
-
链事务：
-
在提交一个事务的时候，释放不需要的数据对象，将必要的上下文传递给下一个事务，提交和开启下一个事务是原子操作。 -
只能回滚到最近一个事务，
-
嵌套事务：
-
若干个事务组成的一棵树，每个节点的事务都是扁平事务或嵌套事物 -
分为根事务，子事务 -
子事务可以提交可以回滚。提交不是马上生效，只有父事务提交才会提交 -
树中的每个节点回滚都会引起他所有的子事务回滚。子事务仅保留ACI特效，不具备D特性 -
Moss理论：实际工作是叶子节点完成，
-
分布式事务
-
事务的实现：
-
ACID的实现：
-
事务的隔离性是由锁实现的。 -
原子性A、持久性D通过数据库的redolog重做日志实现。 -
一致性通过undolog实现 -
redo和undo都可以视为一种恢复操作，
-
redo恢复提交事务修改的页操作，物理日志，记录也的物理修改操作 -
undo回滚行记录到某个特定版本。逻辑日志，记录每行记录。回滚、mvcc功能。随机读写。
-
redo
-
基本概念：实现D持久性，由两部分组成，重做日志缓冲、重做日志文件。
-
Commit的时候先将事务所有日志写入文件中持久化 -
顺序写。一般不会读取。 -
为了确保每次日志都写入重做日志文件。缓冲写入文件后，innodb要进行一次fsync操作。确保日志写入磁盘、 -
commit的刷新设置innodb_flush_log_at_trx_commit，每次都fsync、1秒一次、只提交到文件缓存中。
-
50万行数据的操作时间
-
Binlog与redo对比：
-
Binlog：用老进行POINT_IN_TIME(PIT) 的恢复以及主从赋值环境的建立，与redo类似，但是 -
本质上redo是在Innodb存储引擎层产生的，二进制是在MySQL数据库产生的。 -
记录形式不同：redo记录的是每个页的修改，一个事务会记多条，二进制是sql，一个事务一条。 -
写入时间点：二进制只在事务提交完成后进行一次写入，redo会不断写入
-
log block
-
重做日志以512字节进行存储，缓存，文件都是以块的方式保存 -
磁盘扇区也是512，所有保存可以保证原子性，不需要双写， -
包含日志头12，日志尾8，日志492字节 -
buffer类似一个数组，头里面包含了一些指针位置的信息。
-
log group：
-
重做日志组，有多个文件，innodb只有一个组，组是逻辑上的概念。每个文件都是相同的。最大512G。 -
文件存储的是重做buffer内容，刷新规则：
-
事务提交时， -
当log buffer有一般内存空间已经被使用时 -
log checkpoint时
-
只有第一个file是顺序写入，其余的要空2kb。这2kb要存放每次的写入checkpoint信息。
-
重做日志格式： -
LSN：Log Squence Number日志序号，单调递增
-
表示含义：
-
重做日志的写入总量：当前写了多少子节，当前的LSN -
checkpoint的位置：刷新到此批的LSN -
页的版本：每个页的头部都包含了当前页的LSN。数据恢复的时候会用。
-
恢复：
-
Innodb在启动时会进行恢复。 -
恢复checkpoint的lsn到redo的lsn的数据。 -
redo log是物理日志恢复快，且幂等 -
二进制日志不是幂等的，因为是sql，日志格式不能是row
-
undo：
-
简介：
-
主要作用：回滚使用，是逻辑日志 -
存放位置：数据库内部的一个特殊段中，undo段，位于共享空间内。 -
查看参数：py_innodb_page_info.py工具查看 -
恢复的时候不会恢复数据库到之前的状态， -
undo回滚的操作都是反操作，inset->DELETED -
作用：实现MVCC，当读取得数据被其事务占用，可以通过undo读取之前的行版本信息，实现非锁定读。 -
undo log 也会产生redolog
-
Undo存储管理：
-
存储方式：innodb引擎有rollback段，每个段记录了1024个undo段 -
每次事务提交：
-
将undo log放入链表中供purge操作，是否可删除由purge线程决定 -
判断undo log所在的页是否可以重用，分配给下个事务
-
事务提交并不能马上回收，可能存在其他的时候，需要通过undo log得到其他版本的数据 -
undo可重用的标准：使用的空间是否大于3/4，可重用的页，新的事务会追加到后面。 -
由于日志重用，purge操作需要离散读取，所以很慢。
-
undo log 格式：
-
分类：insert undo log
-
只对事务本身可见，隔离性，提交后直接删除
-
分类：update undo log：
-
记录update和delete：需要提供MVCC机制，提交后放入链表
-
查看undo信息：
-
数据表：INNODB_TRX_ROLLBAXK_SEGMENT。查看rollback segment数据 -
数据表：INNODB_TRX_UNDO：记录事务对应的unod log，方便查询一个事务有多少undo log日志
-
purge
-
update和delete操作：
-
由于innodb支持mvcc需要保留事务开始之前的数据。真正的删除是在该记录不被任何事务引用的时候删除。purge操作会判断update和delete是否可以完成。
-
history列表：
-
根据提交事务的顺序，将undo log进行连接，
-
参数：
-
innodb_purge_batch_size：设置每次purge操作需要清理的undo page数量，越大清理的越多，可供重用的undo page就越多，减少了磁盘存储和分配的开销。太大的haul，每次purge都需要处理很多undo page cpu和io过于集中，性能下降。 -
innodb_max_purge_lag：控制history长度，忙的时候purge会挤压，导致history list 很长.>0的时候回延缓DML的操作
-
group commit
-
为了提高磁盘fsync的效率。提供了group commit功能：一次fsncy可以刷新确保多个事务日志被写入文件。事务提交的操作
-
修改内存中事务对应的信息，并且将日志写入重做日志缓冲 -
调用fsncy将确保日志都从重做日志缓冲写入磁盘。（慢） -
存在group commit失效的问题
-
BLGC：binary log group commit ：解决group commit失效问题
-
Mysql上层提交的时候按照顺序放入一个队列中，第一个为leader，其他事务为follower。leader控制其他的行为 -
Flush阶段：将每个事务的二进制日志写入内存中 -
Sync阶段：将内存中的二进制日志刷新到磁盘，若队列中有多个事务，name仅一次fsncy操作就完成了二进制日志的吸入人。 -
Commit阶段：leader根据顺序调用存储引擎层事务的提交，
-
事务控制语句
-
简介：
-
MySQL默认自动提交，执行完sql就会commit，显式开启事务需要Begin、start transaction。或者set autocommit=0、 -
事务都是原子的，一条语句失败并跑出异常的时候，并不会导致先去的操作回滚，所有执行都会保存，用户自己决定是否回滚。
-
语句：
-
start transaction、begin：开启事务 -
commit：提交事务 commit work在completion_type=0时和commit一样，=1时表示马上开启一个自动隔离级别的事务。 -
rollback：回滚 -
savepoint identifer：允许在事务中创建一个保存点，可以多个 -
release savepoint identifer：删除一个事务保存点，没有的话会抛异常 -
rollback to [savepoint] identifer：与savepoint命令一起使用：可以回滚到保存节点。并不会提交事务。 -
set transaction：用来设置事务的隔离级别：
-
read uncommitted、 read committed、repeatable read、 serializable
-
隐式提交的SQL语句
-
简介：执行完sql会commit操作：
-
DDL操作：alert、create、drop、rename、 -
修改MYsql架构的操作：create user、drop user、grant、rename user、revoke、set password -
管理语句：analyze table、cache index、check table 、load index into cache、optimize table、repair table
-
对于事务操作的统计
-
TPS，每秒事务提交量，只有显示提交才算，com_commit和com_rollback
-
事务的隔离级别
-
Read uncommitted：浏览访问 -
Read committed：游标稳定 -
Repleatable Read：2.9999°隔离，没有幻读保护，Innodb 默认，使用Next-key Lock锁避免幻读，能够达到事务隔离级别的隔离性的要求，达到Serializable的级别。 -
Serializable：称为隔离，3°的隔离
-
每个select后都会加一个lock in share mode 共享锁，不支持一致性的非锁定读。 主要用于本地事务用repleatablle 即可分布式事务。 -
隔离性越低，性能越高， 其实read commit和serializable 的性能差不多，不会提升很多。
-
语句：set xxx transaction isolation level
-
分布式事务
-
简介
-
Innodb支持XA事务，必须使用serializable隔离级别，支持不同的数据库。 -
XA事务是由一个或多个资源管理器，一个事务管理器，以及一个应用程序组成。
-
资源管理器：提高访问食物资源的方法，通常一个数据库就是一个资源管理器。 -
事务管理器：协调参数全局食物中的各个事务，需要和参与全局食物的所有资源管理器进行通信 -
应用程序：定义事务的边界，指定全局食物中的操作。
-
2.mysql 内部使用XA事务
-
存在于binlog与innodb之间。由于主从复制的一致性的需要所以开启， -
当事务提交时。Innodb会先做一个PREPARE操作，将事务的xid卸乳，接着进行二进制日志的写入，若果在innodb提交之前发送宕机，mysql重启后先检查uxid事务是否已经提交，没有在进行一次提交操作。
|
| 第八章：备份与恢复
-
备份与恢复概述：
-
备份的方式分类：
-
hit backup 热备： 运行中直接备份，对正在运行的数据库操作没有任何的影。 -
cold backup 冷备：在数据库停止的情况下，最简单复制物理文件即可 -
warm backup 温备：运行中备份，会对当前数据库的操作有所影响，如加一个全局读锁保证数据的一致性。
-
备份后的文件内容分类：
-
逻辑备份：备份处的文件内容时可读的，文本文件，内容是一条条sqlselect * into outfile，适用于数据升级、迁移工作 -
裸文件备份：复制数据库的物理文件，速度快，可以在运行时复制
-
备份数据库的内容分类：
-
完全备份：完整备份。 -
增量备份：对上次完全备份的基础上更改的数据进行备份。
-
真正的增量备份只需要记录当前每页最后的检查点的LSN，如果大于之前全备份的LSN则备份改页面。速度快
-
日志备份：二进制日志的备份，进行重做来完成数据库的point-in-time的恢复
-
一致性：因为支持MVCC，所以比较容易实现。事务隔离级别为REPEATABLE READ。
-
冷备
-
原理：备份MySQL数据库的frm文件，共享表空间文件，独立表空间文件*.ibd，重做日志文件。定期备份Mysql数据库的配置文件my.cnf，有利于恢复操作。通常使用脚本完成 -
优点：
-
备份简单，只需要复制相关文件 -
备份文件易于在不同操作系统，不同的mysql版本恢复 -
恢复简单，只需要把文件恢复到指定位置即可 -
恢复速度快，不需要执行任何sql语句，也不需要重建索引。
-
缺点：
-
INnodb存储引擎冷备文件比逻辑文件大很多，因为表空间中存放很多其他的数据如undo段，插入缓冲。 -
冷备不容易跨平台、操作系统、musql版本、文件大小写敏感和浮点数据格式都会是问题
-
逻辑备份
-
mysqldump：用来完成转存数据库的备份以及不同数据库之间的移植，导出的是sql语句 -
select .... INTO OUTFILE：导出一张表中的数据 -
逻辑备份的恢复：mysqldumo：直接执行这个文件。 mysql -u -p <TEST_BACKup.sql -
load data infile -
mysqlimport
-
二进制日志备份与恢复
-
正确使用二进制日志的参数：
-
log-bin = mysql-bin -
sync_binlog = 1 -
innodb_support_xa = 1
-
恢复命令：mysqlbinlog binlog.000001
-
热备
-
ibbackup：热备工具
|
| 第八章：性能调优
-
选择合适的CPU：
-
innodb的架构中可知：主要的后台操作是在单独 -
OLTP数据库的特点：
-
操作的并发量大 -
事务处理时间一般比较短 -
查询语句简单，都走索引 -
复杂的查询比较少
-
内存的重要性：
-
需要缓存索引，内存小会影响性能，通过命中率不小于99%。小了要增加内存 -
-
硬盘对数据库性能的影响：
-
机械硬盘：两个指标：寻道时间(3ms)和转速15000(RPM),最大的问题是写磁头，可以随机访问，但是访问需要耗费长时间的磁头旋转和定位来查找，多块磁盘组成RAID提高数据库性能。 -
固态：基于闪存
-
低延迟，低功耗，防震性。企业级应用一般使用固态。通过并联多块闪存进一步提高数据传输的吞吐量。 -
闪存：是一个完全的电子设备，可以提供一致的随机访问时间。 -
闪存中的数据不可以更新，只能通过扇区的覆盖重写，而在重写之前，需要执行非常耗时的擦除操作。 擦除操作不能再所含数据的扇区完成，需要在删除整个被称为擦除块的基础上完成，其大约删除的大小，通常128kb，每个擦除块有次数限制， -
读写速度非对称：对远快于写。
-
读：主机发布读请求，固态的控制器会把IO命令从逻辑地址映射成时机的物理地址。访问延迟一般小于0.1ms -
写：需要修改相应的映射表信息， -
-
合理的设置RAID：
-
RAID类型：独立磁盘冗余数组，把多个相对便宜的磁盘组合起来，称为磁盘数组，性能达到超过一个价格昂贵，容量巨大的硬盘。多个硬盘组合成一个逻辑扇区，RAID就像一个硬盘。
-
作用:
-
增强数据集成度： -
增强容错功能 -
增加处理量或容量
-
常见组合：
-
RAID0：速度最快，没有冗余功能，一个挂，数据都丢失， -
RAID1：两组以上N个磁盘互相为镜像，类似主从，可靠性最高，磁盘利用率低。 -
RAID5：是一种存储性能，数据安全和存储成本兼顾的存储及解决方案，使用Disk striping（磁盘分区）技术，至少需要3个磁盘，不对存储的数据进行备份，把数据和相应的校验存储到Raid5个各个硬盘，可以恢复。R0和R1的折中方案。磁盘利用率高。读取快，只是多了个数据校验，写入非常慢 -
RAID10：结合R0和R1，速度快，需要4个以上的偶数硬盘才能用， -
RAID50：镜像阵列条带。R0和R5。至少6块硬盘，提高r5的读写。 -
：：：R10，最好的选择，一个磁盘挂的时候，性能缺失严重。条带会成为瓶颈。 -
-
RAID Write Back 功能：
-
简介：指RAID控制器能够将写入的数据放入自身的缓存中，并把它们安排到后面再执行。
-
好处：不用等待物理磁盘时机的写入完成，写入更快了。重做日志、二进制日志、脏页的刷新都提升了。 -
坏处：异常情况，缓存的数据会出现问题，现在都有电池备份BBU单元。
-
功能需要开启 -
不开启，和开启，和关闭但是不是每次提交事务都写入重做日志的速度。
-
RAID配置工具：
-
操作系统的选择：
-
linux：考虑稳定性而不是新特性 -
FreeBSD： -
windows -
64位操作系统：寻址快
-
不通的文件系统对数据库性能的影响：
-
windows：支持NTFS。 -
linux：有的支持EXT3.有的ReiserFS有的EXT4. XFS
-
选择合适的基准测试工具：
-
对数据库后或操作系统调优后的性能进行对比，sysbench和mysql-tpcc -
可以更好的知道当前系统的负载能力。
|
| 随想 |
|
这篇关于MySql技术内 幕：InnoDB存储引擎 读书笔记的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
