mysql笔记

暑假学习的一点东西，给自己做个笔记，以后可以看看。

mySQL三层：

上层服务器{C/S结构}，核心功能{查询、优化...}，并发控制{服务器层，存储引擎层}

mysql的存储引擎架构：
将查询处理和其他任务处理分离，即：把对数据的操作与其对应的存储操作方式分离，提高系统的灵活性，类似策略模式，可以改变系统的引擎，以提供不同的存储策略，这个和linux的（VFS）文件系统类似。

隔离分级：未提交读（回滚后，有脏读现象），提交读（仅MVCC不加X锁，不同的事务之间不能满足重复读，因为并发会发生并发冲突，虽然是共享内存，但是不加锁的情况下，事务在不同时间看到的数据可能不一样（根据lamport时间戳的因果关系，不能确定因果（先后）关系的操作成为并发），这也是因为不同事务之间消息通信方式的异步性），可重复读（使用MVCC+X锁可以实现，会出现幻读，要加间隙锁），可串行化（这个使用同步的通信方式可以实现）。

如果使用MVCC+X锁+间隙锁，就可以解决脏读，可重复读，幻读，并且可以保证并发读，仅在写时加锁。

MVCC的这种实现，其实是行锁的变种，即：只锁应该锁的行。当然，也有改进的地方，保证了并发读。

schema设计原则：
1.使用最小的数据类型-使用的资源少，并且因此缓存量和IO量不变的情况下，可以一次操作更多数据。
2.使用内建类型，mysql的内建类型往往操作比字符串更快。比如timestamp，date和整数。
3.避免使用NULL
mysql难以优化NULL列，NULL列加索引会使每条记录需要一个额外的空间，使得MYISAM的索引变成可变长的索引。

决定数据类型：
1.决定数据类型，优先使用内建类型。
2.确定特定类型（考虑存储范围，精度）

整数
 　　类型名称　　     　　占用位数　　       　　数据范围　　
　　 TINYINT      　　　 8      　   　        -2^7 ~ 2^7-1
 　　SMALLINT      　　16　　       　　-2^15~2^15-1　　
 　　MEDIUMINT　　   24　　       　　-2^23~2^23-1
 　　INT      　　　　      32      　　        -2^31~2^23-1
 　　BIGINT     　　　　64       　　       -2^63~2^63-1
 2.unsigned 不允许负数，但扩大一倍范围
 3.mysql对整数定义宽度，主要看数据类型。

 实数：
 FLOAT 4字节、DOUBLE 8字节、DECIMAL只用于需要对小数进行精确计算，其计算时转化为double，大小限制为65个数字（包含小数点）。

 字符串类型：
 varchar保存可变长的字符串，分配真正需要的空间，保存数值不会去掉末尾的空格。
 char保存固定长度的字符串，保存数值时，去掉尾部的空格。
 text
 保存大量数据，可能会使用大量的外部存储来存储text
 排序方式不同于其他字符类型，只会按照max_sort_length个字符进行排序。
 使用要点：
 一般经常改变的值使用char，因为固定串不容易导致碎片。对很短的列，char也比varchar好。
 可以使用ENUM代替字符串，
 ENUM可以代替65535个字符串，把串压缩为两个字节以内，只在.frm文件中存一个整数和字符串的映射表，但是ENUM的字符列的修改要用ALTER TABLE,（order by）使用数字排序，使用枚举可以减少元组大小。
内存临时表不支持BLOB和TEXT，可以使用substring（column，length），把该列切出其中部分。

 日期和时间类型：
 DATETIME:存储大范围时间，时区无关，使用8字节。
 TIMESTAMP：从1970年1月1日开始的秒数，使用4字节。
 以上都以秒为单位。

太多关联，最多61张表的关联，最好保持在12张表内进行关联。
set可以是多值，比如set('man','womam');那么可以是'','man','woman','man,woman',可以是多值的组合。


范式与反范式：
范式：
1.范式写入快
2.冗余少，写入少
3.表小，读取快
反范式：
1.单表往往比索引更快，不用使用关联

反范式对于读取而言更加友好，可以使用覆盖索引，但是如果冗余量过大，会导致数据IO量上升，而且写入要在业务层或者使用触发器来解决冗余数据的一致性问题。
范式则对于写入更加友好，没有冗余不用考虑一致性问题。


索引：

B树是一种平衡多叉树，m阶B树，是一棵平衡的m路搜索树，满足以下性质：
1.定义非叶子节点最多M个儿子，M>2
2.根节点的儿子数为[2,M];
3.根节点外的非叶子节点儿子数[M/2,M];
4.每个节点存放至少M/2-1之多M-1个关键字;
（以上两点可以推出：当节点的儿子数等于M时，节点会分裂成儿子数为M/2的两个节点，这可能会从底向上引起连锁反应，导致树多了一层）
5.非叶子节点的关键字个数=指向儿子的指针个数-1
6.非叶子节点的关键字K[1],K[2],...,K[M-1];且K[i]<K[i+1]
7.非叶子节点的指针P[1]指向关键字小于K[1]的字数，P[M]指向关键字大于K[M-1]的子树，其它P[i]指向关键字属于(K[i-1],K[i])的子树。
8.所有叶子节点位于统一层。

B树的搜索是从根节点开始，使用二分查找。
特性：
1.所有关键字分布在树的每个节点中
2.任何一个关键字出现且只出现在一个节点中
3.搜索可能在非叶子结束
4.自动层次控制。

B+tree
基本定义基本和B-树同，除了：
1.非叶子节点的子树指针和关键字个数相同
2.非叶子节点的子树指针P[i],指向关键字[K[i],K[i+1])
3.为所有叶子节点增加一个链指针
4.所有关键字在字节点出现。

特性：
1.所有关键字出现在叶子节点的链表中（稠密索引），且链表中的关键字恰好有序。
2.非叶子节点是叶子节点的稀疏索引，叶子节点是存储数据的数据层。

B-tree使得所有的数据都比较集中，每次顺序IO可以取出大量的数据，减少磁头移动的延迟，有利于文件系统的IO效率。而且查询前不用做全表索引，只需要做二分查找，和顺序IO则可。

InnoDB：
有自适应哈希索引，而哈希索引的缺点在于只能定位而不能做范围查询。
这是由系统建立的，在数据访问量多的数据上建立。
而我们可以利用SHA/CRC32在B+树上建立索引或建立二级索引，使数值变小，IO数据更多。
支持事务
使用MVCC（undo信息，实现数据多版本读取），InnoDb在事务的同时，保证并发读。
行锁，基于MVCC和索引实现。
外键保证引用完整性

InnoDb用.frm存放表的结构定义，但是数据和Index放在一起，即聚簇索引。
data文件
data+index(主键/其他索引)
表空间：
1.共享表空间，所有表和索引被放在同一个表空间。
2.独享表空间，表的数据和索引北方在单独的idb文件中。
InnoDb无法通过索引加锁时。就会对表加锁。
在主键索引（和数据放在一起的聚蔟索引），其中每个叶子节点含有Primary key、事务ID，回滚指针（指向undo日志），用于MVCC实现行锁。



MYISAM：
.frm表结构定义，.MYD表数据，.MYZ索引数据
三种索引B-tree，全文索引，
B-tree索引的字段和不能超过1000字节。
表放在同名的.MYD文件中，分为静态，动态，压缩三种，使用Row_format来选，默认非压缩，压缩使用（compressed），表中有变长字段，则为DYNAMIC，无则为FIXED。

表文件在一下三种情况会出错：
1.mysqld写时被kill
2.硬件故障
3.主机crash

Myisam可以实现读时写，插入文件未。

表锁，而且memory引擎也用的表锁，text和blob无法使用memory，用substring进行优化后即可使用。

MYisam只缓存索引文件MYZ，而MYD依靠OS进行缓存。每个Index只存放同一个Index不会有一个Index分到两个文件中，提高索引的连续读性能。
索引文件包括文件头（state、base、keydef、recinfo）。

数据读入以fileblock的数据读入
1.query请求，读key cache
2.无则到MYI中，以fileblock读
3.完全相同的格式放入cache block中
4.返回
index的过程相同但是多了dirty标志和写回操作。

myisam逻辑上表现为多个index block，但是并非物理结构，物理上，索引是以文件块的形式存放在磁盘上。在key cache中缓存的索引信息是以索引块进行组织。

cache缓存
MYisam只有key cache
key cache的配置文件，
key_buffer_size和key_buffer_block_size可以调节buffer大小和buffer的每一块大小。

设置时要考虑thread_usage,和system_usage,在这两个都满足的前提下可以使用key_size=key_number * (key_length+4)/0.67。


myisam的缓存LRU利用中点插入的方式，把LRU链分成hot子表和warm子表。
这种方法不知考虑了最近命中，还考虑了命中次数。
保护频繁的热点数据。

KEY_CACHE_DIVISION_limit LRU链表中hotarea和warmarea的分界值，为1-100之间，默认100,只有warm area。
KEY_CACHE_AGE_CHRESHOLD，控制cachebloc从hotarea降到warmarea的限制，默认为300,最小100,值小，降级可能性大。当hotarea顶部的block停留时间超过一个阀值后，会降级到warmarea，具体计算方法：N为cache的block个数，如果最近的（N×key_ccache_age_threshold/100）次访问中，hotarea没有被访问到，那么插到warmarea顶部。
具体操作：
LRU分为hotarea和warmarea，利用key_cache_division_limit来划分。
1,在warm sub-chain中某个block被访问的次数超过某个值时，放到hotarea底部。
2.hotarea的block会随着每次hit调整位置，hit越多，越接近底部，在顶部停留时间过长就会被降级到warmarea。
时间分析：
每次只需要先hash取得位置，然后修改命中次数。
如果在warm中，往底部挪，如果次数已经达到阀值，就往hotarea放，顺便把最顶部的hotblock放入warm底部。
如果在hot中，往底部挪，增加次数。
最后检测当前的hot底部和warm底部，如果未命中次数足够多，那么把hot挪到warm顶部，然后把warm底部挪到hot顶部;把warm底部的数据移除。

innodb 有key/data cache
buffer pool
InnoDb使用LRU算法：
所有buffer位于同一列表，后3/8为old，每当读入一个一个数据块先从尾部移除同等块数，然后插入old头部，再次访问将该块移到new子列头部。

这个算法没有考虑访问次数，不过实现简单。不过也可以减少被访问频繁的数据被剔除。就是刚被剔除new的数据不能马上回到new中。

Innodb_buffer_pool_size:buffer pool大小
Innodb_buffer_pool_instances:子buffer pool数量
Innodb_old_blocks_pct:范围5-95,默认37即3/8,指定old的比例
Innodb_old_blocks_time:以ms为单位，新插入old子列buffer必须等待制定时间才能移入new列，使用于one-time scan频繁的操作，避免经常访问的数据块被踢出。

使用 show status like ‘Innodb_buffer_pool%’;可以查看bufferpool的

mysql> show status like 'Innodb_buffer_pool_%'
    -> ;
+---------------------------------------+---------+
| Variable_name                         | Value   |
+---------------------------------------+---------+
| Innodb_buffer_pool_pages_data         | 252     |
| Innodb_buffer_pool_bytes_data         | 4128768 |
| Innodb_buffer_pool_pages_dirty        | 0       |
| Innodb_buffer_pool_bytes_dirty        | 0       |
| Innodb_buffer_pool_pages_flushed      | 0       |
| Innodb_buffer_pool_pages_free         | 7939    |
| Innodb_buffer_pool_pages_misc         | 0       |
| Innodb_buffer_pool_pages_total        | 8191    |
| Innodb_buffer_pool_read_ahead_rnd     | 0       |
| Innodb_buffer_pool_read_ahead         | 0       |
| Innodb_buffer_pool_read_ahead_evicted | 0       |
| Innodb_buffer_pool_read_requests      | 2789    |
| Innodb_buffer_pool_reads              | 253     |
| Innodb_buffer_pool_wait_free          | 0       |
| Innodb_buffer_pool_write_requests     | 0       |
+---------------------------------------+---------+

由上可知：一页16KB，我们可以看出有8191pages，还有7939pages未使用，只用了252pages。其中read请求2789次，其中253次通过读取物理磁盘得到。
预读的总量和预读后一次都没用被剔除的次数，可以求得预读命中率。
还可以通过Innodb_buffer_pool_read_ahead_rnd求得预读随机读次数。



Innodb_buffer_pool_wait_free:缓存中没有空闲页满足当前请求，必须等待部分页回收或刷新，记录等待次数
出现这个过多，说明缓存空间不足。
Innodb_buffer_pool_pages_misc:用于维护行级锁(MVCC，redo中的数据读入，利用rollback指针和事务ID)或自适应hash索引的内存页
Innodb_buffer_pool_pages_free:未使用的页数量
Innodb_buffer_pool_pages_total:页数量
Innodb_buffer_pool_bytes_data:当前buffer_pool缓存的数据大小，包括脏数据
Innodb_buffer_pool_pages_data:缓存数据的页数量
Innodb_buffer_pool_bytes_dirty:缓存脏数据大小
Innodb_buffer_pool_pages_dirty:缓存数据的页数量
Innodb_buffer_pool_pages_flushed:刷新页请求数量
可以看出缓冲区的基本情况

Innodb_buffer_pool_read_request:Innodb逻辑读请求次数（总次数）
Innodb_buffer_pool_reads:直接从磁盘读取数据的逻辑读次数（未命中）
Innodb_buffer_pool_write_requests:向缓存的写数量
以上四个，可以计算出cache写和读取的命中率。

Innodb_buffer_pool_read_ahead:预读入缓存的页数量
Innodb_buffer_pool_read_ahead_evicted:预读入但是1次都没用就被提出缓存的页
通过以上两个，可以看出预读的命中率



日志：
undo log
1.事务原子性
原子性：要么都做，要么都不做。使用undo log对事务进行回滚。
2.事务持久性
持久性：事务完成后，数据库会将修改后的数据持久化到持久的存储上。

保证持久化和原子性：
a.更新记录前记录undo log
b.为了保证持久性，必须将数据在事务提交前写到磁盘。只要事务提交成功，数据必然已经持久化。
c.undo log 必须先于数据持久化到磁盘。
d.在数据持久化到磁盘之前，我们可以保证磁盘上数据都和事务开始前一样。在undo log写入磁盘时出错，那么，此次的更新作废，因为数据尚未写入。
在数据写入出错时，那么会出现undo log完整，而数据不一致，那么我们可以安心回滚。
缺陷：每次事务提交前，都要把undo log和数据写入磁盘，会导致大量的IO。如果我们缓存数据，那么就可以减少IO。但是丧失了事物的持久性。加入redo log解决。
redo log
1.事务持久性
持久性：redo记录新数据的备份，事务提交前，只需要对redo log 持久化就可以了，如果系统崩溃，我们可以把所有数据更新到最新。
但是如果发生错误，就无法回滚了，因为原来的数据已经被破坏了。

Undo+redo事务：
A:为了保证持久性，必须在事务提交前把redo log持久化。
B:数据不需要写磁盘
C:redo log保证事务持久性
D:undo log保证原子性
E:预写式日志意味着redo log早于数据持久化，而且undo log和数据可以进行批量持久化（因为undo log的操作会记录在redo log中）。

IO性能：
undo+redo可以缓存数据，减少IO。
redo log 只在事务提交的时候顺序写入文件，而且redo log是一个环形缓冲区，在check point之后，旧数据可以覆盖。
并发事务可以共享redo log的存储空间，所以当一个事务将redo log写入磁盘，其它未提交事务也会写入磁盘。

那么这种事务交叉在数据恢复时如何实现rollback操作？
恢复：重做所有事务，包括未提交和回滚了的事务，那么如何回滚？
Innodb 在重做redo log时，不关心事务性。恢复时，没有begin，commit，rollback的行为，因为只有当rollback时候才会对数据造成影响，我们只需要关注什么时候rollback就好了，根据状态机的顺序一致性，我们知道两台机器只要初态一致（check point），执行的修改操作一致，最终状态也一致。所以，我们不需要关注琐碎的东西，只需要关注顺序一致性，即：一切有关数据的修改的顺序一致，而已生成的redo日志（此时没有MVCC，因为此时的记录实在解决并发冲突之后记录下来的）只有一个地方会用到undo日志-rollback。
rollback
redo log在记录的时候，会把undo log的操作也记录在redo log中(insert操作)，并且rollback本质上是一种修改操作，只要在rollback的时候写入redo日志，就可以保证事务的顺序一致性。

物理日志：记录page中被修改的部分（page中的偏移，内容和长度）。
优点：因为恢复时，不依赖原页面的内容
缺点：log记录的内容很多，会记录所有修改的元组的内容。

逻辑日志：记录对元组的操作
缺点：部分执行，由于crash，会导致一个写入操作只执行了部分，其余部分没有执行。
一个日志所记录的写入操作可能不是原子的，会导致写断裂。

逻辑和物理结合的日志：
物理到page，把操作细分到页级，为每个页上单独记录日志。
page内日志保持逻辑性。
当多个页面间的数据需要同时操作，否则可能出现不一致时，日志所指向操作就不满足一致性了，在crash的时候可能只执行了一半（写断裂），而事务的后半部分没有执行，从而导致一致性的丢失，并且redo log 无法感知其宕机前已经做了多少，所以此处需要两次写机制。



使用double write：
A:在覆盖磁盘的数据前，先将当前页面先写在两次写的共享页空间中，进行持久化
B:将页面内容覆盖磁盘原来的数据
如果A出错（写断裂），原来数据页完整，可以通过redo log恢复。
如果B出错（写断裂），可以通过两次写的共享表空间直接恢复。
有任何由于脏数据的刷盘操作，都会使得writebuffer也刷盘，当然数据满也会刷盘。

mysql切片：
1.按表切分（纵向切分）
切分比较简单
但是这会导致无法在数据库系统上做join操作，只能在使用数据库代理，通过代理进行join操作。
或者使用反范式，但是会有一致性问题。
架构改变较大，因为要增加许多代理进行join。

2.按表中的数据且分（水平切分）
不同的行会分到不同的机器节点上，未来数据分离变得困难，因为本来是一份的数据，通过水平切分，分布到多台机器上了。
无法在数据库系统上使用直接完成排序操作，只能使用数据库代理，把map的数据reduce起来。

锁：

共享锁：代号是s，获取后可以对数据进行读操作
互斥锁：代号是x，获取后可对数据进行写操作
意向锁：
lock in share model执行成功会给数据库加上一个共享锁，所以这是意向共享锁。
for update执行成功会给数据库加上一个互斥锁，这是意向互斥锁。
下面是兼容矩阵：
    X    S    IX    IS
X  n     n    n     n
S  n     y    n     y
IX n     n    y     y
IS n     y    y     y  

意向锁之间相互兼容，S和IS锁互相兼容，因为IX锁还没获得所，只是一种意向，那么什么时候可以提出这种意向？当然是还没人有锁的时候了。

myisam：

MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。
a、当concurrent_insert设置为0时，不允许并发插入。
b、当concurrent_insert设置为1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。
c、当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

myisam中认为写的优先级高于读，所以写操作会在操作队列的前面。

只支持表锁。

innodb：
通过MVCC支持行锁，同时使用间隙锁防止幻读。
mysql的MVCC其实算不上乐观锁，其实在写操作发生时会有锁定行的操作，只是读并发而已，实际上真正的MVCC是用不了的，因为当事务中出现多行修改时，rollback的时候可能把之后的数据冲掉，引起数据的不一致性，出现脏读。

innodb的行锁，其实是在索引上的索引项上加锁实现的，那么意味着，只有通过索引条件检索数据，innodb才使用行级锁，否则，使用表锁。
在DDL和插入数据时使用表锁，DDL和插入数据会创建主键索引，加上一个S锁，因为此时还没有使用B+树索引，索引和数据放在一起，只要检查索引，就可以发现数据是否被使用。
由于使用行锁，所以innodb还有一个死锁检测功能，选择一个引起死锁的事务，回收资源，并且回滚。
若并发执行的所有事务均遵守两段锁协议，则对这些事务的任何并发调度策略都是可串行化的。
使用两阶段锁，前一个阶段是加锁，不能解锁，并可以操作数据，后阶段解锁，不能加锁，也可操作数据，两阶段锁协议，提高了事务的并发度，但是它无法解决死锁问题。两阶段锁再加上MVCC应该叫做多版本两阶段封锁协议。
什么是可串行化？
多个事务并发执行是正确的（可串行化作为一种事务调度的判断标准），当且仅当其结果某一次序串行地执行这些事务的结果相同，称这种调度策略为可串行化调度。
对于串行调度，各个事务的操作没有交叉，也就没有相互干扰，当然也不会产生并发所引起的。事务对数据库的作用是将数据库从一个一致的状态转变为另一个一致的状态。多个事务串行执行后，数据库仍旧保持一致的状态。
而两阶段锁本质上使用锁让每个可能导致冲突的数据操作（同时对一个数据发生读写冲突，写写冲突）都满足原子性，即当前事务完成对某一数据的操作之前，其他的操作不能操作该数据。

比如对数据A，T1和T2两个事务不会出现T1,T2，T1的操作，因为数据A在T1中又lock，unlock，然后在lock。

所以两阶段封锁可以保证事务对一个元组操作的原子性。
MVCC的行锁，可以实现不同事务之间的隔离性和原子性。
redo log和double write 可以保证写入的原子性和持久性。
一致性需要靠原子性，隔离性和持久性来维持，还需要，每一个事务都能使数据库一致，这个就需要设计事务的人注意了。