性能优化

1、优化维度

SQL及索引
数据库表结构
系统配置
硬件
（效果自下而上递增，成本自上而下递减）

2、硬件优化

Mysql数据库常见的两个瓶颈是CPU和I/O的瓶颈。
CPU瓶颈：发生在数据装入内存或从磁盘上读取数据的时候。
磁盘I/O瓶颈：发生在装入数据远大于内存容量的时候
分布式中，瓶颈还有可能发生在网络上，可以用top和iostat查看性能状态。

3、mysql参数调优

innodb调优
innodb_file_per_table
这个配置项会决定InnoDB是使用共享表空间来存储数据和索引，还是为每个表使用一个单独的.idb文件，一张表一份文件。
innodb_data_file_path
用来指定innodb tablespace文件ibdata1初始大小，预估所需磁盘空间大小
innodb_data_home_dir
innodb_automic_lock_mode
0、traditional：insert语句开始时，加锁，执行完以后才会释放锁，效率很低。
1：现分配id，后面慢慢插入
2：没有锁
innodb_buffer_pool_size:
内存大小分配：一个专用数据库服务器，那么它可以找占到内存的50%～80%。
数据库是和内存或者缓冲区交互的，而不是和磁盘。
innodb_buffer_pool_instances
将buffer pool分成几个区，每个区用独立的锁保护，各个分区是相互独立的，不会互相影响，这样就减少了访问buffer pool时需要上锁的粒度，减少不同线程会缓存页面进行读写的争用，以提高性能。
innodb_max_dirty_pages_pct
控制Innodb的脏页在配置值的百分比，默认是75，超过了以后，将脏页刷到磁盘中
innodb_log_file_size = 2G & innodb_log_files_in_group(默认为2)
innodb_log_file_size是redo_log文件的大小。
innodb_log_files_in_group是redo_log文件的个数，一般默认为2。
innodb_log_buffer_size
redo日志缓冲区大小，用于设置写redo日志刷盘之前的缓冲区大小。
sync_binlog
控制数据库的binlog刷新到磁盘中的动作。
sync_binlog = 0，表示mysql不控制binlog的刷新，由文件系统自己控制他的缓存的刷新
最安全的就是sync_binlog = 1，表示每次事务提交，Mysql都会把binlog刷下去，是最安全但是性能损耗最大的配置
innodb_flush_log_at_trx_commit
控制数据库redo日志落盘动作
0:每秒write os cahce & flush disk
1:每次commit都write os cahce & flush disk
2：每次commit都write os cahce，集中flush disk
innodb_io_capacity
定义了innodb后台任务每秒可用的I/O操作数。
5000-20000
innodb_write_io_threads
innodb_read_io_threads
innodb_undo_tablespaces
控制undo log是否开启独立的表空间的参数，如果为0，表示使用共享的表空间ibdata1。
innodb_undo_directory
当开启独立的undo表空间时，指定undo文件存放的目录
innodb_purge_threads
事务被提交以后，其所使用的undolog撤销日志可能不再需要，因此需要Purge Thread清理线程来回收已经使用并分配的undo页，这个设置用来设置回收线程数。
innodb_purge_batch_size
表示每次回收undo的数量
binlog_cache_size
为每次session分配的内存
max_binlog_cache_size = 8M
max_binlog_size = 12M
指定binlog文件的大小，超过了写到另一份binlog文件中
expire_logs_days = 7
有效日期
max_connections
最大连接数
back_log
连接数量超过最大连接数时，新来的请求存在堆栈中，back_log是堆栈的数量，超过了back_log的数量后，就会报错
long_query_time
慢日志记录的时间阙值
wait_timeout
长连接时，如果八个小时都没有请求申请过来，就会断开连接
general_log = 0
全量日记建议关闭

4、索引

索引的目的在于提交查询效率，可以类比字典

磁盘读取数据靠的是机械运动，每次读取数据的时间花费为 寻道时间+旋转延迟+传输时间

指令依靠的是电的性质
执行一次IO的时间可以执行40万条指令

磁盘预读

5、b+

一个树的节点就是一个磁盘块，磁盘块包含数据项和指针，真实数据存储在叶子结点。
IO次数取决于B+树的高度h，假设当前数据库的数据表为N，每个磁盘块的数据项是的数量是m，则有h = log（m+1）N。

最左匹配原则

当B+树的数据项是符合数据结构的时候，从最左一个字段进行搜索。

聚集索引

数据库中行数据的物理顺序和索引顺序相同。
1、有主键的时候，主键就是聚集索引
2、有唯一非空索引的时候，唯一非空索引就是聚集索引
3、innodb内部生成一个隐藏的主键作聚集索引
叶子节点，存储的是整行的记录（真实数据）

非聚集索引和覆盖索引

非聚集索引：叶子节点存放主键字段的值，需要回表查询（通过聚集索引定位到行记录）。
覆盖索引：（非聚集索引的一种）可以直接通过覆盖索引得到值

6、慢查询优化

超过1s认定其为慢查询

7、EXPLAIN

EXPLAIN列头信息：
ID相同，执行顺序从上到下
...

7、库表结构优化

关系型数据库逻辑设计：
第一范式：要求有主键，并且要求每一个字段原子性不可再分
第二范式：满足第一范式，非主键字段完全依赖于主键，不能产生部分依赖
第三范式：满足第二范式，所有非主键字段和主键字段不能产生传递依赖。
物理设计：

命名规范、选择合适的存储引擎、选择合适的数据类型

银行中浮点类型一般选择 decimal，精度更高

varchar：字符串字节长度+额外记录字符串长度的字节

日期：datatime timestamp data：所有精确到天的字段用data time：不常用

待补充

8、SQL调优

索引的首字段，必须在where条件中
将区分度高的字段靠前放
合理利用覆盖索引，不使用更新频率高的列作为索引
索引不是越多越好，不能超过5个

索引使用原则

遵循最左原则
不在索引列进行数学运算和函数运算
区分度大的放前面

高可用框架

RTO：停机时间
RPO：数据不一致

1、Mysql主从复制

和HapProxy、Keepalived同时使用
但是将双节点数据库扩展到多节点数据库，此时HapProxy、Keepalived方案就不适用
MHA工具

错题：
1、Innodb_buffer_pool 参数在MYSQL里是可以动态修改的。 Innodb_buffer_pool_instance 不可以。
2、数据备份不适合用MySQL复制来完成。读写分离、容灾适合。