Mysql数据库架构

1、服务器硬件优化

   

   内存(不一定越多越好，避免浪费)

1. 索引和数据可以存在缓存
   
2. 缓存区大小
   
3. 给其他服务提供更多内存
   

   磁盘

           传统机器磁盘

1. 存储容量
   

1. RAID技术可以将小磁盘变成大磁盘，增加传统机器硬盘的性能。（常用）（选择最好带缓存功能的）
   

1. RAID 0 是最简单，最少使用2块以上硬盘即可 成本低 没有提供冗余和错误修复能力。后续费用可能更高。（可通过软件）
   
2. RAID 1 又称磁盘镜像，保证可以修复，利用的磁盘可能就是一半的空间（可通过软件，用作读取日志等使用读块，写慢需要镜像）
   
3. RAID 5 分布式奇偶校验磁盘阵列（读取很快，写的时候比较慢，使用在 从服务器上）。
   
4. RAID 10 分片的镜像（推荐）
   

2. 
3. 固态磁盘 :  适用于存在大量随机I/O的场景      解决单线程负载的I/O瓶颈      最好使用在从服务器上    适合MYSQL
   

1. 有更好随机读写性能
   
2. 更好的支持并发
   
3. 更容易损坏
   
4. SSD
   

1. 使用SATA
   
2. 使用过SATA支持RAID技术 。选择支持SSD RAID配置
   

5. PCI-E SSD  闪存Fusion IO
   

1. 无法使用SATA接口
   
2. 价改比SSD高性能更好 会使用CPU内存空间（代价）
   
3. 可以不使用RAID了 ，从成本考虑
   

4. 网络存储 SAN和NAS ： 适用于文件存储   数据库备份文件  
   

                                       

       

1. 传输速度
   
2. 访问时间
   
3. 主轴转速
   
4. 物理尺寸
   

 

总结 ： 64位 对应 64位

           对于并发比较高 cpu的数量比频率重要

           对于复杂sql      频率重要        

           内存尽可能大

           I/O子系统

2、操作系统 Centos系统参数优化

  内核相关参数  /etc/sysctl.conf

           TCP 链接

               net.core.somaxconn = 65535

              net.core.netdev_max_backlog = 65535

               net.ipv4.tcp_max_syn_backlog = 65535

           

           加快tcp回收

             

           TCP缓存区 发送和接受

               

           失效链接加速tcp回收

               

       

 

 

 

   

3、MyISAM  5.5版本以前默认

1. 表级锁
   
2. 读写混合并发不是特别好  。 适合读表
   
3. 表修复 可能数据会丢失
   

1. 通过 check table ***
   
2. 通过 repair table ***
   

4. 支持压缩 这张表只读
   

1. 通过命令 myisampack -b -f ***.MYI
   
2. 通过压缩之后进行读写有什么问题
   

1. 只读
   

5. mysql 5.0 默认单表大小为4G 扩大需要修改MAX_Rows 和 AVG_ROW_LENGTH
   
6. mysql 5.0之后为256TB
   
7. 适用场景
   

1. 非事务
   
2. 只读类（报表）
   
3. 空间类应用 例如GPS
   

4、MySQL5.5 之后默认为Innodb

1. MySQL 5.6中，这个属性默认值是ON 默认使用独立表空间
   
2. (innodb_file_per_table = OFF) 或者为每张表的数据单独放在一个.ibd文件(innodb_file_per_table = ON)。每张表一个文件允许你在drop、truncate或者rebuild表时回收磁盘空间。这对于一些高级特性也是有必要的，比如数据压缩。但是它不会带来任何性能收益。你不想让每张表一个文件的主要场景是：有非常多的表(比如10k+)。
   
3. 使用独立表空间推荐
   
4. 
   
5. 支持事务ACID
   
6. 支持行级锁
   

1. 由存储引擎层实现
   

1. 共享锁（也称读锁）
   
2. 独占锁（也称写锁）
   

5、CSV存储引擎

1. 
   
2. 可以直接对数据文件直接编辑
   
3. 
   

   

6、Archive存储引擎

1. 
   
2. 
   
3. 
   

7、Memory 存储引擎

   

1.     结构保存在磁盘 数据保存在内存
   
2. 
   
3. 默认值只有16M 对已经存在memory表修改之后无效 ，除非重建
   
4. 
   
5. 
   

8、Federated存储引擎一个数据库可以读取另一个数据库

1.    
   
2. 
   
3. 
   

9、Mysql服务器配置

1. 
   
2. 
   
3. 
   
4. 
   
5. 
   
6. Innodb_flush_method = O_DIrect 适用linux
   
7. 
   
8. 
   

10、安全

1. 
   
2. 后面两个针对主从结构
   
3. 不要轻易改动
   

11、基准测试 mysqllap 和 sysbeanch

   

12、

13、mysql 二进制日志

1. 基于段 statement
   
2. 基于行 row（官方推荐）
   

1. 优点
   

1. 主从复制更加安全
   
2. 对每行数据修改比基于段复制更搞笑
   

2. 缺点
   

1. 日志较大 binglog_row_image = [full(默认) | minimal | noblob]
   

3. 运行 mysql -vv **.
   

3. 混合 MIXED 上述混合
   
4. 
   
   

14、复制

15、复制工作方式及要求

   

1.  主服务器查看是否开启二进制日志
   
2.  从服务器上
   

16、配置Mysql复制--基于日志点的复制

1.    基于日志点的复制
   

1. 配置账号
   

1. 
   

2. 配置主服务器
   

1. 
   

3. 配置从服务器
   

1. log_slave_update = on 可用于从服务器作为另一个服务器的主服务器
   

4. 初始化从服务器数据
   

1. 
   

5. 启动复制链路
   

1. 在从服务器上启动
   

                       

                           start slave

           

17、配置Mysql复制--基于 GTID 复制优缺点

           

1.      在主DB服务器上建立复制账号
   
2. 配置主服务器
   

       

       3.配置从数据库服务器        

       

       4. 初始化 从数据库数据

           

           

18、选择复制模式

   5.6之后才有GTID辅助

       

19、MySQL复制拓扑

1. 一主多从
   

1. 优点
   

1. 配置简单
   
2. 可以从多个从库分担读负载
   

2. 用途
   

1. 为不同业务使用不同从库（分担查询，使用不同索引，使后台查询不影响前端查询）
   
2. 将一台从库放到远程IDC作为灾备
   
3. 分担读负载
   

2. 双主复制  主-主复制
   

1. 
   
2. 容易产生冲突 导致链路中断 耗时长
   
3. 适用于 一个地区保存另一个地区数据
   
4. 建议操作
   

1. 两个主中操作的表最好分开 ： 上海的库只使用上海的表 北京的库只使用北京库
   
2. 使用下面参数控制自增
   

1. 
   
2. 
   

1. 推荐使用主备模式下 主主
   
2. 
   

3. 
   

1. 主有问题时候，从也需要关闭
   
2. 从多的时候会对主性能有压力（解决这个问题 出现级联复制） 需要配置slave_log_updates
   

20、MySQL复制性能优化

1.   主从延迟的因素
   

1. 
   
2. 推荐后者
   
3. 
   

2. 配置多线程复制
   

1. mysql5.7 设置4个线程 并设置并发为逻辑适中
   

3. 复制常见问题
   

1. 
   
2. 
   
3. 
   
4. 一定要设置read_only
   
5. 在从服务器的时候多个从服务器server_id一样比较隐蔽
   
6. 
   
7. 
   

21、高可用

22、MMM架构

   同一时间只有一台主服务器提供外部服务 从服务器read-only

   

   

   

22、MHA架构

23、索引策略

   

24、SQL查询优化  通过日志形式

   

1. 
   
2. 
   
3. 
   
4. 
   
   
5. 
   
6. 
   
7. 
   

   

25、SQL查询优化  实时查询

    做个定时访问

26、sql解析预处理及生成执行计划

1.    
   
2. 
   
3. 
   

27、分库分表

   

   

终极大招：表的水平拆分