mysql基础知识介绍

mysql体系架构

连接层：响应连接，身份以及安全性的验证

SQL层：权限判断，SQL解析，执行计划的优化，检查高速缓存是否有该数据，处理常用的内置函数，存储过程，触发器，视图

存储层：

mysql常见的存储引擎

myisam：堆表，不支持事务，不支持crash_recovery_safe，不支持MVCC，对cpu和内存利用率不高

memory：基于内存的hash列表的存储引擎，请求速度快，但数据不安全（适用于如临时计数）

innodb：索引组织表，只能有一个聚集索引，类似oracle，尤其适用于OLTP

TokuDB：支持事务，高压缩，高速写入，适合日志库，频繁的写入，偶尔的删除，很少的更新和查询（查询主要作归类），高压缩比（10：1）

inforbright：列式存储。高压缩，快速加载数据，适用于OLAP

mysql文件目录：

配置文件

数据文件：frm,myi,myd,ibd,ibdata*,ib_logfile*

日志文件：error log,general log,bin log,redo log ,slow query log

mysql内存=global_buffers+thread _buffers（每个会话的内存）

全局buffer：mysql实例启动之后只分配一次

thread_buffer(会话buffer)：新产生的连接都会分配一次；不能设置太大，并发太大时，会导致内存不够用。

myisam和innodb在利用内存时候的区别：

innodb在内存中既缓存数据又缓存索引；myisam只缓存索引，缓存数据交给操作系统层（os cache）。

在大批量导入数据时先禁用索引(alter table table_name disable keys)，在完全导入后，再开启索引（alter table table_name enable keys），一次性重建索引的效率会更高

mysql的缺点：

无share pool，每个SQL都需要解析，但可利用query cache提高效率

不支持CBO(目前只有RBO)

每个SQL只能使用到一个核

随着连接数的增加性能下降严重（thread pool解决了该问题）

mysql5.6对子查询进行了优化

目前不支持hash join

不要让mysql跑复杂的应用(BI、复杂关联、复杂子查询)

mysql使用场景：

在线OLTP环境，业务模型简单，要求高速响应，并发度高（mysql在固态卡下基于tpcc-mysql测试能达到6万多tpmc；业务每秒4万多DML很简单）

大数据环境（hadoop集群中，后台数据处理在mysql中完成；在线海量数据处理，如：tokudb，infobright，infinidb）

在线交易（电商，彩票，在线旅游，物流系统）

新闻媒体：sina，sohu

互动社区：微博，sns，交友，linkin

mysql每个query只能用到一个cpu

mysql没有sql预编译（可以减少cpu的开销），

mysql随着连接数的上涨，性能会下降(thread pool可以解决)

使用连接池的好处：可以限制连接数，不用每次都创建连接

可以通过从库来处理复杂SQL查询

mysql对磁盘的利用特点：

binlog、redo log、undo log顺序IO；

datafile随机IO和顺序IO相结合；

OLTP需要随机IO(可以利用内存做缓存，从而减少随机IO)；

OLAP需要更多的顺序IO（内存缓存作用不大）

一个核4-8个连接

生产上需要禁用performance_schema(收集数据库服务器性能参数)来提高性能

基于同步的高可用架构：

MMM：基于mysql同步构建，利用VIP切换做故障转移，对gtid复制不支持，切换时间只做show slave status中判断（一致性并不高）

MHA：在发生master故障时，会做binlog或relay log的数据补齐，不cover slave（slave故障不会处理）

zookeeper，dns需自主研发

在数据一致性方面pxc和MHA表现比较好

keepalived+lvs的检测脚本较复杂

DRBD：同步基于网络同步，不应有大量数据的导入；使用于团购类网站

基于galera的cluster结构:percona-xtradb-cluster,mariadb-galera-cluster(限制：每个节点机器的硬件配置必须一样，一般控制为3个节点（最少3个，最多8个），每次写入需要在兄弟节点上验证，节点太多死锁率会增高)

特点：同步复制；支持多主复制；并行复制；

mysql拆分设计及约束：

单库并发较大（query响应时间太长）

单库物理文件太大（跟库的恢复时间有关）

单表过大，DDL无法接受（5.6.17后可支持在线DDL，但从库延迟较大）

出现性能瓶颈

出现抖动不稳定现象（锁竞争，随机访问磁盘IO）

出现利用机器的资源

垂直拆分（按功能拆分）：把不同功能的库独立出来，然后按功能进行拆分

优点：服务相互独立，

缺点：多个功能之间不能进行join，

需要在每个功能DB前引入数据访问层，也基于API对外提供服务，这样以后每个功能的DB拆分可以做到对外拆分

事务控制需要借助消息队列进行控制

对于功能中特别大的表还有可能存在性能瓶颈

过度拆分会造成管理复杂

水平拆分：

优点：同一功能内的关联，事务操作都可以进行

不会存在超大规模的表

应用程序端改动比较小

拆分规则设计好的情况下，易扩展，

缺点：数据分散，group by，order by，sum，count等排序，聚集函数不能支持，如果一定需要需要在某一个地方存全量

分片过多，会造成维护难度增加，

后期迁移较复杂

NOSQL优点：

数据基本存储在内存中，可以利用内存加速

基于API的访问，减化请求复杂度

NO schema设计，使用灵活，方便存储热点缓存

使用SQL限制：

不要过分依赖于Nosql的持久化

不要对Nosql操作事务要求特别严格

注意数据大小（尽量在内存之下）

译者介绍：家华，从事mysqlDBA的工作，记录自己对mysql的一些总结