mysql原理及部分优化

MySQL常用的有两个数据库引擎，myisam和innodb

2者底层都是都是使用的B+tree，略有不同，myisam 叶子存指针，innodb存数据
如果插入，更新较多，使用innodb引擎，innodb行级锁
如果更新插入少，读取多可以使用myisam引擎，表级锁，不适合更新，查询比较快，

为什么innodb引擎推荐使用自增id

大部分人都会说innodb引擎推荐使用自增id，为什么？没有人说，我在这里解释一下
大家可能看过HashMap的实现原理，数组+红黑树，在进行put和delete操作是，红黑树为了保持平衡，要进行分裂，合并，旋转操作来维护平衡。
B+tree也一样，delete和insert也需要进行复杂的操作来维护全局平衡（所有叶子节点层高相同）
自增id有一点好处就是B+tree向右膨胀，不需要复杂操作来完成平衡。所以推荐用自增id，其实使用有序增加的id都可以。

磁盘io与优化

文件系统和数据库普遍采用B+tree来实现，是因为他的树高更低，红黑树是层级更高，每一层树意味着一次io的读写，应该尽量避免多次io。
目前一般磁盘 ATA sata等的iops在200以下，和磁盘转速有关
IOPS = 1000 / (3 + 60000/7200/2) = 140，7200转速的磁盘
IOPS = 1000 / (3 + 60000/10000/2) = 167 ，10000转速的磁盘
IOPS = 1000 / (3 + 60000/15000/2) = 200，15000转速的磁盘
SSD磁盘是一种电子装置，避免了磁盘寻道和旋转上花费的时间，iops理论上可以达到几万到10万。
而数据库读写一个事务就需要一次io，这个知识点牵扯到一处mysql优化，就是
innodb_flush_log_at_trx_commit=1 就是每次事务都写入磁盘，一般的磁盘都在200一下，真受不了高并发，=0代表刷新到内存，但不同步到磁盘，每秒同步一次，这对磁盘iops要求没有那么高了，也快了很多，但是如果系统断电，丢失上一秒的数据。
=2不刷新到内存，也不同步到磁盘，性能没有比0高很多，但是系统断电，mysql崩溃，都会丢失上一秒的数据，如果一般磁盘想支持高并发，又对数据没有那么严格的要求，可以使用innodb_flush_log_at_trx_commit=0，来降低磁盘io需求，提高速度。

数据库设计id尽量不要用uuid

现在很多人都在使用uuid作为数据库主键，有好处，也有缺点，我的建议是尽量不要使用，如果真的需要用，也要用有序的uuid。
如果想避免单点故障，可以考虑雪花算法算出的id，下面解释为什么不要使用uuid
b+tree存储数据里
在innodb中，最小的单位是page，16k，b+tree的非叶子节点存id+指针，如果id是bigint 占8个字节，指针6个字节
所以一个page 可以存放非叶子节点个数是 16k/(8+6)~=1170
叶子节点行数就可以每行数据大小有关了，我们假设每行1k数据，那一个page可以存16行
b+tree特点就是所有叶子节点在同一层级
所以2层b+tree可以存大约 117016=18720行。约18兆数据
3层b+tree可以存大约1170117016~=2190万行 约21G数据,数据里再大不建议使用mysql了
mysql主键尽量别用uuid，就算我们可以产生有序uuid，更新时不会分裂，但是同层级的b+tree存的数据里太小了。
我们假设数据表编码用的是utf8，一个字符占3个字节
uuid去掉连接号-是32个字符
所以一个page 可以存放非叶子节点个数是 16k/(323+6)~=156
所以2层b+tree可以存大约 15616K=2496 约2兆数据
所以3层b+tree可以存大约 15615616K=389376 约389兆数据
所以4层b+tree可以存大约 15615615616K=60742656 约60G数据
基本上我们使用bigint，三层基本上够用了，但是使用uuid，就需要4层b+tree，需要多一次磁盘寻址，一次寻址时间大概需要5-15毫秒
当然，如果我们的id使用的是int或者byte，那就更好了。