mysql设计实现概念点滴，

 

数据库是一个比较复杂的系统，因此，不论是从设计概念上还是实现上，必须要把维度划分清晰。

架构

把接入和解析拆分不同功能模块。

其中在核心模块，我们也得从不同的维度考虑和设计系统。

并发控制，缓存，存储

 --------------------------------------------------------------

缓存

Mysql核心中的缓存

查询缓存 key-value，表更新则失效

索引缓存 25%

线程缓存   线程描述，连接缓存区（客户端连接信息和发送数据缓冲区），数据读取缓冲区（MyISAM专用）
表缓存 （元数据）
其他（批量插入缓冲，链接操作缓冲，排序缓冲等）

Note:

mysql处理中间数据(如排序等)时会使用(对包含Blob和Text则用MyISAM，所以一般要尽量避免，否则会导致涉及隐式临时表的操作开销极大)

 

InnoDB引擎中的缓存

缓冲池：插入缓存，数据页，索引页，自适应哈希，锁信息，数据字典

日志缓冲

额外内存池：数据结构本身分配内存，如缓冲控制对象，

 

并发控制

包括了锁和事务，然后它们自身又有不同的维度和级别

锁

      类型：读写锁
      粒度：行锁，表锁

事务

      隔离级：Read Uncommited, ReadCommited, repeatable read(MVCC),serializable




      Note

：


           操作事务，不同的引擎支持不一样。innodb 

和 MyISAM

表的混合操作失败后会导致数据不一致 

MVCC


避免很多操作的加锁操作。乐观并发VS

悲观并发。更新操作都会对数据增加版本号，select

操作只对比当前版本号小的数据进行操作。

不同的存储引擎比较

MyISAM



支持全文检索，压缩（适合不修改的数据，压缩的表若要改需要解压），不支持事务和行级别锁


存储结构：数据文件+

索引文件

InnoDB



将所有数据共同存储在一个或几个数据文件中（表空间）。 





MVCC


提高并发能力。基于聚簇索引建立（可以提供非常快速的主键查找性能）。支持事务和行级别锁

物理存储上的分层：表空间，段，区，页

线程：Master, read, write, log, insert buffer，可以根据配置增加read,write的线程数

Master线程的定时任务1秒，10秒， background

根据配置同步脏页，合并缓存，删除REDU，同步日志

对非聚集索引的插入和更新，会先加入到缓冲中， 后面批量写入

两次写，增加可靠性

Memory



速度快，但是功能有限制，不支持Blob
和Text
，Varchar
性能有问题。

Archive

不支持索引，针对高速插入和压缩进行了优化。

Blackhole

不存储，只写日志；用于复制设置或审核日志

 




其它,CVS, NDB Cluster, Falcon, solidDB,PBXT,Maria,,,

总结：

InnoDB

使用了聚集索引，而且有自己的缓冲区，把表数据载入内存，因此，有时远比MyISAM

快

监控

长查询日志，我们在设计系统中也可以考虑这种比较通用的业务级别日志


 

使用注意

varchar

适合那些最大长度远大于平均长度，而且很少发生更新。（更新会导致碎片）

BLOB
和Text
，MySQL
当做实体对象处理，存储引擎会特别的保存它们，较大时使用“
外部”
存储区域

 

优化技巧：

COUNT(*):


代表忽略所有的列，否则统计时会对那些NULL
的列进行忽略，性能就很不好了。COUNT(*)
在没有WHERE
限制下性能很高

limit 10000,20 :

性能开销，
导致读取10020,
忽略前10000
行；优化技巧：   

在覆盖索引上进行偏移而非全行数据偏移。如select f.id,f.desc from t.f inner join 
(select id from t.f order by orderfield limit 10000,20) as f using (id);

   改成

使用位置查询 select id, desc from t where position between 10000 and 
10020 order by position;

 

缓存

查询语句中有不确定函数，比如now()


就不会使用缓存。另外，必须要完全一致，大小写或空格都将导致缓存不命中

InnoDB


使用MVCC

架构，使查询缓存交互相对复杂。表上的任何锁导致查询不可缓存。根据事务ID

判断缓存是否失效。

    


可以把缓存策略改成DEMAND
，只针对部分插叙使用缓存。 

另外批量写入也会比多行写入效率高（只引起一次缓存失效）。

索引

B-Tree vs Hash Index

Hash


索引速度快，但是其局限：

1. 
无法使用索引中的值来避免读取行（不过一般内存中的行速度不错)

2. 
无法使用索引进行排序

3. 
不支持部分键值匹配。 这样导致多键值表中，查询前两个也会导致全表扫描。

4. 
无法范围查询（跟排序有些重合）

InnoDB


对查询较多的数据建立自适应哈希索引（Adaptive Hash Index)
，把常用这些值在内存中hash
索引起来。

伪hash
作法，对 string 
计算其crc32
，然后把该值作为索引。 

R-Tree, 
空间索引。 




全文索引， FULLTEXT
， MyISAM
表的特殊索引。全文索引引用于MATCH AGAINST
操作，而不善于WHERE
。
前缀索引（字符串的前几位索引） 

聚集索引，更新聚集索引列开销昂贵。插入新行可能导致分页，浪费存储空间。对聚集索引的表顺序插入会导致单点竞争.



索引减少了访问的行数，这样对于支持行锁的，比如InnoDB,

可以减少了锁定

排序优化

当无法使用索引排序时，就要自己对结果排序（内存或磁盘上），这个过程叫文件排序（FileSort)

双路排序（Two Passes)

读取行指针和被排序的列，排完后对再根据行指针查询输出。缺点是，输出时会根据行指针再去读取数据，导致大量的随机I/0
。

单路排序(Single Pass)

根据输出需要，第一次把输出的列和排序列按照 ORDERBY 
列进行排序。

 

存储过程

减少网络和解析消耗

合并表和分区表

合并表

从逻辑上合并几个表。用于日志记录和数据仓库，可方便的增删表。 

分区表的限制

避免查询访问所有分区但是仍然锁定了所有分区

分区不支持外键

无法使用Load Index into cache

数据刷新

innodb_flush_log_at_trx_commit
，
0
， 每秒刷新日志缓存到日志文件


1
，缓冲些到文件中，并且在事务提交时把缓存刷写到持久性磁盘。 ( 
会导致IO
阻塞直至写入磁盘完成。 )

2
，每次提交写日志文件。每秒清理缓冲

InnoDB


双写缓存，写入磁盘前先写入双缓冲中。这样可以放在磁盘没有写入完全的数据损坏。

并发写入调优

MyISAM

的concurrent_insert变量

0, 
不允许并发，每次插入都锁表

1, 
只要表中没有空缺就允许（默认）

2, 
强制并发插入到表尾，会导致表的碎片增多，需要定期对表进行优化

 

查询缓存的空间不要设置的太大。

因为查询缓存是靠一个全局锁操作保护的，如果查询缓存配置的内存比较大且里面存放了大量的查询结果，当查询缓存失效的时候，会长时间的持有这个全局锁。因为查询缓存的命中检测操作以及缓存失效检测也都依赖这个全局锁，所以可能会导致系统僵死的情况