扛得住mysql 性能优化

1 对数据库性能产生影响的几个方面
1.1 硬件
1.2 服务器的操作系统
1.3 数据库存储引擎的选择
1.4 数据库配置参数
1.5 数据库的设计和sql语句

1.1 硬件 CPU资源和可用内存大小
mysql 体系结构
MyISAM 5.5.8 之前默认的存储引擎
有myd和myi组成，前者存储数据，后者存储索引信息
还有所有存储引擎都有的frm 存储表结构
特性：
1 并发性和锁级别 用的是表级锁，所以对于读写混合操作的话支持不是很好
2 表损坏修复 check table tablename
repaire table tablename
3 支持的索引类型
全文索引
4 支持数据压缩 命令：myisampack 压缩之后只能读取，不能写入和修改
限制：
1 mysql5.0之前单表大小为4G，现在默认为支持256T
使用场景：
1 非事务性应用，即不支持事物。事物是关系型数据库和非关系型数据库的一大区别，但很明显这里不能这么说，因为 这只是一个存储引擎而已
2 只读类应用（因为支持压缩），性能不错
3 空间类应用
Innodb
mysql5.5.8之后，成为默认的存储引擎
使用表空间进行数据存储，通过参数innodb_file_per_talbe的on off值来选择不同的存储方式，当值为on时，使用独立表空间tablename.idb来存储，当为off时，使用系统表空间ibdatax来存储。那应该如何选择呢？
mysql 5.7之后也开始支持全文索引和空间，

mysql 服务器参数
内存配置相关参数
确定为操作系统保留多少内存
如何为缓存池分配内存 innodb_buffer_pool_size

安全相关配置
expire_logs_days 指定自动清理binlog的天数

*基准测试*
什么是基准测试？
如何进行基准测试
1.1 从系统入口进行测试（如web前台入口，app入口等）。有点：能测试整个系统的性能，包括服务器、缓存、数据库的性能
1.2 单独对某一个组件（如mysql）进行测试
mysql性能基准测试常用的性能指标
1.1 TPS 单位时间内所处理的事物数
1.2 QPS 单位时间内所处理的查询数
1.3 响应时间
1.4 并发量：同时处理的查询请求的数量
基准测试的步骤
1.1 计划和设计基准测试
1.1.1 是对整个系统还是对某一组件
1.1.2 使用什么样的数据
编写测试脚本，收集mysql运行时的各种信息，如cpu使用率，连接数，innodb状态等等
1.1.3 完成测试，收集和保存测试结果
基准测试时需要注意的问题
1.1 使用数据的问题，是否使用真实的生产环境数据
1.2 多用户场景中，只模拟单用户的测试。应该模拟多线程的并发测试
1.3 反复执行同一条查询，容易缓存命中，无法真实反映真实查询性能
如果是想对整个系统进行测试，可以使用apache的ab httpload等进行测试
基准测试工具之
1.1 mysqlslap mysql5.1之后，自带的测试工具
特点：
1.2 sysbench

第4章 MySQL数据库结构优化

首先要明确，进行数据库结构优化要达到的目的
4.1.1 减少数据冗余
什么是冗余呢？举个例子：一个列的数据是可以通过其他列的计算得出或者已经存在。这里我们可能不需要再次存储这些数据，当然必要的冗余有好处，这里说的是减少，而不是全部去除
4.1.2 尽量避免数据维护中出现插入、修改、删除异常（这里的异常不是说插入的时候不能插入的问题，是指比如修改了一条数据对逻辑产生影响这类问题）
4.1.3 节约数据库的存储空间

4.2 mysql 结构设计
4.2.1 设计步骤
1 了解需求 ：存储需求、数据处理需求、数据的安全性和完整性
2 逻辑分析
4.3 数据库设计范式 ———–》 数据库设计过程中遵守的规范要求——三范式
4.3.1 第一范式，基本所有数据库表都符合第一范式，看到这句话我基本就知道，第一范式没什么屁用了。都是些只要设计表就自动不自动会遵循的一些规则，真的是没有什么实际意义
数据库表中的所有字段都只有单一的属性，且必须为单一的字段等等，不赘述
4.3.2 基本也用不到啊亲，这都算他们什么规则
4.3.3
4.4 需求分析及逻辑设计
实际业务需求分析（1）本网站只销售图书类商品（2）需要具有以下功能：用户登录 用户管理 商品展示 商品管理 供应商管理 在线销售
逻辑分析：用户登录及用户管理功能
用户必须注册并登录才能进行网上交易
同一时间同一用户只能在一个地方登录
用户信息{用户名 手机号 密码等等}
4.5 选择合适的数据类型
选择原则：当一个字段可以选择多种数据类型时首先应该选择数字类型，其次是日期或二进制类型，最后是字符类型。对于级别小的数据类型应该优先选择空间占用小的类型
整型比较容易理解，这里着重讲一下带小数点的float double和decimal 。float和double都不是精确的类型，decimal是精确的。
类型名称 占用空间
float 4个字节
double 8个字节
decimal 占用空间根据长度而定。此外，float和double之间的区别是前者占用7个精度，而double占用16个，而且double比float要慢很多，不建议使用。float和double如果存储的值超过了设置的值会进行四舍五入存储，这就不是我们想看到的了，所以原则是尽量考虑到我们可能存储的值宜选用合适的类型或者等到真的选择了四舍五入的时候的后果是我们完全能够承受的，那你选择这种类型也是没问题的。
varchar用于存储变长字符串，varchar(num)中num指的是字符，不是字节。举例来说在utf8下一个汉字占用3个字节（包括繁体字）。当我们设置varchar(255)的时候，可以存储包括汉字、英文字母在内的255个字符而不是只能存储80多个汉字。当然，相应的varchar(255)存储255个汉字和255个英文字母，占用的空间肯定是不一样的了。要差到510个字节。
这里在举个例子吧：如果我们用varchar存储性别（实际上我们不会这样的），男/女，varchar(1)和char(1)分别会占用多个字节呢？首先，utf8下一个汉字占用三个字节这是通用的，然后，在长度小于255的时候，varchar会在占用一个字节来存储字段的长度。所以，这时，varchar会占用4个字节，char占用三个字节。

5 mysql复制功能
5.1 二进制日志
二进制日志文件中记录了所有对mysql的修改事件，包括增删改查和对表结构的修改事件。这里只记录成功执行的事件，对于那些已经回滚的事物等等的操作不会记录在binlog里。binlog存储方式有三种
binlog_format = statement 记录所有的sql语句，所以占用空间小，但相比来说不太安全，怕出现数据不统一的问题
优点：占用磁盘空间小。数据库表结构可以不同（因为执行的是sql语句，所以比如说字段顺序不一致可以接受，但这种问题几乎不会涉及）
缺点：对于非确定性事件，无法保证主从复制数据的一致性。
binlog_format = row mysql5.7之后默认使用的格式
优点：传说中可以防止主从复制时的数据不一致问题，因为记录了每一行的更改，不记录sql语句，主从复制数据完全一致。另外可以减少从库上锁的使用（前提是大批量的操作的情况下，个人认为几乎可以忽略）
缺点：占用空间大。无法触发触发器
binlog_format = mixed 混合上面两种 根据sql语句由系统决定使用哪一种
综合考虑，使用row这种格式。
二进制日志在mysql默认是不开启的，通过在配置文件中设置log_bin = mysql 即可开启；通过datadir 查看二进制日志的存放地址
5.4 mysql 复制工作原理
1 主服务器将所有操作记录到二进制日志中
2 从服务器读取主服务器上的二进制日志记录到relay_log中
3 从服务器执行relay_log中的操作
5.5 配置MYSQL 复制
基于日志的复制
1 在主库上建立复制账号 create user ‘username’@’ip’ identified by ‘password’ 需要注意的是是这里的ip指的是允许复制的ip地址，也就是从库的ip
2 进行授权 grant replication slave on . to ‘username’@’ip’
3 配置主库
log_bin = mysql_bin 用于开启二进制日志，并且指定名称.mysql_bin可以自己定义
server_id = 1
4 配置从库
log_bin = mysql_bin
server_id = 2（主和从的server_id是不同的）
relay_log = mysql-relay-bin
log_slave_update = on [可选] 对于将该从库当做主库来进行复制时必须开启
read_only = on [可选] 可以限制所有对于该服务器的写操作
至此，配置主从的所有配置操作都已完成。那么到了实际的生产环境中去，对于要配置主从关系的时候，从服务器不一定是和主库上所有数据结构和数据一样的东西，我们要在主从正式开始运行之前保持数据结构和内容是一致的，所以这时我们需要对主库进行备份，导入到从库然后进行操作。那么如何进行备份呢？？？？？
mysql 备份
mysqldump 这是mysql内置的备份命令，使用此命令时需要对表进行加锁
xtrabackup 热备工具。对于全部使用innodb的库备份时可以不阻塞服务器，优先选择。对于混合的时候，同样会进行所操作。
启动复制链路
这是在从库上进行的操作，告诉从库开始同步主库的binlog,同时也可以告诉从库从什么位置开始进行同步
change master xxxxxxxxxxxxxxxxx
使用start master 启动复制链路
这时会在从库上开启两个关于复制的线程，第一个是将主库binlog同步过来的线程。第二是执行relay_log的线程

 基于日志点复制的优缺点： 优点：最早支持复制的技术，bug相对较少。对sql没有任何限制（因为这里执行的只是单纯的sql）.故障处理比较容易 缺点：

基于GTID的复制 mysql5.6以上才开始支持
1 建立复制账号
2 授权
3 主库配置
bin_log = /usr/local/mysql/log/mysql-bin
server_id
gtid_mode = on 是否启用gtid模式
enforce_gtid_consite 强制gtid的一致性，用于保证事物的安全
log_slave_updates = on 用于从库记录主库传输过来的修改日志
4 从库配置
server_id
relay_log =
gtid_mode = on
enforce_gtid_consite
read_only = on [可选]
启动基于GTID的复制
change master xxxxxxx

6
7 数据库监控
7.1 对服务可用性进行监控 进程或端口号存在不能够说明数据库就是可用的，我们必须通过网络连接切实连接到数据库中并做一些操作才能说明数据库是可用的
7.2 对性能进行监控
7.3 对主从复制进行监控
7.4 对服务器的资源进行监控