mysql版本不一致会导致uuid_MySQL性能优化和高可用架构建议

1.主从复制binlog_format要使用row，statement会导致主从数据不一致(基于语句，rand或uuid等函数没法恢复)

2.如果使用binlog恢复数据，标准做法是用mysqlbinlog工具把binlog中内容解析出来，然后把解析结果发给mysql执行

3.innodb_buffer_pool_size是缓存用户表及索引数据的最主要缓存空间，建议可以设置到50%到80%的内存大小，调整这个参数期间会阻塞用户的请求，直到调整完毕，建议在低峰期调整

4.对于某些工作负载，如使用like和%的范围查询以及高并发的joins,不适合使用自适应哈希索引，维护哈希索引结构的额外开销会带来严重性能损耗，这种情况建议关掉，set global innodb_adaptive_hash_index=off/on命令

5.innodb_flush_log_at_trx_commit的参数取值

设为1：最安全，数据肯定一致，配合sync_binlog=1

设为2：适合数据一致性和完整性要求不高的

设为0：只要求性能，高并发日志服务

6.对于可能产生大量更新记录的大事务，增加innodb_log_buffer_size的大小，可以避免在事务提交前就执行不必要的日志写入磁盘操作，提高事务处理性能

7.关于query_cache，任何更新操作都会导致其失效，并发高的时候也有影响，还有bug,所有大部分情况下只是鸡肋，建议全面禁用，5.7中默认关闭

8.当发生锁等待情况时，可以通过语句select * from sys.innodb_lock_waits \G来在线查看，最主要是看waiting_pid等待事务的线程pid、waiting_query等待锁释放的语句、blocking_pid阻塞事务的pid、blocking_query阻塞事务的SQL语句这4个参数，pid对应show full processlist命令里面输出的线程id号；阻塞事务语句显示可能为NULL，想要找到相应id对应的SQL可以使用select SQL_TEXT from performance_schema.events_statements_current where thread_id in (select thread_id from performance_schema.threads where processlist_id = 相应pid)

9.Innodb会自动检测死锁，并立即回滚其中某个事务，并且返回一个错误。偶然发生死锁不必担心，当死锁频繁出现是要引起注意，检查代码，调整SQL操作顺序，或者缩短事务长度

10.避免死锁建议：

1.)不同程序并发存取多个表或者涉及多行记录时，尽量约定以相同的顺序访问表

2.)把大事务尽量分解成多个小事务，使所更快释放

3.)在同一个事务中，尽可能做到一次锁定所需要的所有资源

4.)尽量使用索引，不用索引会为每一行添加锁

11.通过show full processlist是为了查看当前MySQL是否有压力、都在跑什么语句、当前语句耗时多久了，从中可以看到总共有多少链接数、哪些线程有问题，然后把有问题的线程kill掉，临时解决一些突发性问题

12.通过show engine innodb status命令来查看是否存在锁表的情况

13.事务和锁信息记录在information_schema数据库中，主要涉及innodb_trx、innodb_locks、innodb_lock_waits,可以帮我们方便地监控当前的事务并分析可能存在的锁问题

innodb_trx：查看事务情况

trx_id:唯一的事务id号

trx_state:当前事务的状态

trx_wait_started:事务开始等待的时间

trx_mysql_thread_id:线程id与show full processlist相对应

trx_query:事务运行的SQL语句

trx_operation_state:事务运行的状态

innodb_locks:查询事务锁情况，包括事务正在申请加的锁和事务加上的锁

innodb_lock_waits查看锁阻塞情况

requesting_trx_id:请求锁的事务ID(等待方)

blocking_trx_id:阻塞该锁的事务ID(当前持有方，待释放)

14.查询语句避免使用select *且加上limit限制

15.使用索引尽量避免事后才想起添加索引

16.开启ICP(Index Condition Pushdown)可以过滤掉大量的数据，减少IO，提高查询语句性能，开启方法：set optimizer_switch="index_condition_pushdown=on"

17.开启MRR(Multi-Range Read Optimization),优化器将随机IO转化为顺序IO，目的是减少磁盘的随机访问，降低查询过程中的IO开销，对IO-bound类型的SQL语句性能带来极大的提升；开启方式set optimizer_switch="mrr=on,mrr_cost_based=on",cost base=on表示当发现优化后的代价高时就不会使用该优化项

18.开启BKA(batched key access),提高表join性能，开启方式set optimizer_switcher="mrr=on,mrr_cost_based=off,batched_key_access=on",BKA必须使用MRR，所以mrr_cost_based必须关闭

19.慢SQL优化思路：开启慢日志，设置超过几秒为慢SQL语句，抓取慢SQL语句，通过explain查看执行计划，对慢SQL语句分析，创建索引并调整语句，再查看执行计划，对比调优结果

1.抓取慢SQL:可以使用slow_query_log_file,可以使用mysqldumpslow进行分析，命令为mysqldumpslow -t 10 xxx/xxx/xxx.log,显示出日志中最慢的10条SQL

2.通过explain分析语句，其中type字段有ALL(代表全表扫描，没有用到index)、index、range(优化目标至少到range级别)、ref、eq_ref、const、system、NULL(从左到右，性能从差到好)；key字段出现NULL，代表没有使用索引；rows字段值越大意味着需要扫描的行数越多，相应耗时越长；extra字段出现Using filesort，一般是因为order by后的条件导致索引失效，最好进行优化；extra字段出现Using join buffer,应该注意，根据查询的具体情况可能需要添加索引来改进；extra列要避免出现Using filesort或Using temporary，很影响性能

3.show profiles也有助于分析慢SQL

20.索引使用原则

1.)表一定要有主键，显示定义主键且采用与业务无关的列以避免修改，建议采用自增列来使数据顺序插入

2.)经常被查询的列、经常用于表连接的列、经常排序分组的列，需要创建索引

3.)组合索引区分度高的放在最左边，字段数不建议超过5个，如果5个字段还不能极大地缩小row范围，那么设计肯定有问题

4.) 单张表的索引数量建议控制在5个以内

5.)注意隐式类型转换会导致索引失效

21.通过设置innodb_buffer_pool_dump_pct的值可以在重启时自动预热，提高访问性能(尤其是在业务高峰时)

22.innodb_undo_log_tablespaces建议设置为3，避免在自动清除undo log时系统处于不可用状态，2也可以，但3更安全

23.硬件建议：

1.使用SSD或者PCIe SSD设备，至少获得百倍甚至万倍的IOPS提升

2.购置阵列卡同时配备Cache及BBU模块，有阵列卡时设置阵列写策略为WB，甚至FORCE WB,严禁使用WT策略

3.尽可能选用RAID10，而非RAID 5

3.建议在BIOS层面关闭NUMA，CPU设置为最大性能模式，选择performance per watt optimized来充分发挥CPU的最大功耗性能，同时建议关闭节能选项

24.Linux操作系统层面优化

1.防火墙(iptables)和SElinux需要关闭

2.关于IO调度，建议使用deadline或者noop模式，不要使用cfg模式，会影响数据库性能

3.设置内核参数vm.swappiness=1,尽量避免使用swap(交换)分区

4.推荐使用xfs文件系统，其次选择ext4文件系统，放弃ext3;CentOS 7将xfs作为默认的文件系统

25.MySQL配置参数优化

query_cache_type=0,query_cache_size=0 关闭查询缓存

innodb_buffer_pool_size一般设置为物理内存的50%-80%

innodb_io_capacity与innodb_io_capacity_max:取决于硬盘IOPS，即每秒的输入输出量(或读写次数)，capacity一般建议设置如下：SAS:200,SSD：3000，PCI-E：10000-50000,capacity_max为capacity的2倍，也可以通过sysbench或其他基准工具来测试磁盘的吞吐量

innodb_log_file_size:redo日志大小，通常应用是频繁写入的，可以设置为2G

innodb_flush_method:默认是fdatasync,当服务器硬件有SSD硬盘、RAID控制器、断电保护、采取write-back缓存机制的时候，可以改为0_DIRECT，避免了buffer pool和系统buffer的双缓存，效率更高

innodb_max_dirty_pages_pct:脏页占buffer pool的比例，建议调整为50，表示当脏块达到innodb_buffer_pool_size的50%时触发检查点，写磁盘

binlog_format建议使用row模式，数据更加安全可靠，在主从复制过程中不会丢失数据

26.设计规范建议

1)字符集统一使用utf8mb4,降低乱码风险，初始化时就设置好，修改后只会对修改后创建的表生效

2)小数字段推荐使用decimal类型，float/double精度不够

3)避免使用text/blob来存储大段文本、二进制数据、图片、文件等内容，应该保存成磁盘文件，数据库中保存其索引即可

4)不使用外键，在应用层实现外键逻辑，外键会降低性能，容易产生死锁

5)字段尽量定义为NOT NULL并加上默认值，NULL会给SQL开发带来很多问题，从而导致用不了索引，对NULL计算时只能用IS NULL和IS NOT NULL来判断

6)不要使用存储过程、触发器、视图、函数等高级功能，移植性和可扩展性较差

7.)varchar是可变长字符串，不预先分配存储空间，长度不超过5000，如果大于此值，定义为text类型，独立出来一张表，用主键来对应

27.SQL规范建议

1.)禁止使用insert into t values(xx),必须显示指定插入的列属性，避免表结构变动导致数据出错

2.)禁止使用select *,必须指定查询字段

3.)join时必须让小表做驱动表，join列必须字符集一致并且都建有索引

28.主从复制建议

1.)在从库上master_info_repository="TABLE"和relay_log_info_repository=“TABLE”和relay_log_recovery=1确保在slave上和复制相关的元数据表也采用innodb引擎，受到innodb事务安全的保护，并开启relay-log自动修复机制

2.)主库上innodb_flush_log_at_trx_commit=1且sync_binlog=1确保数据高安全

3.)从节点上授权只读模式set global read_only=1避免人为误操作在从库中修改数据，导致主从不一致，对应拥有super权限用户使用set global super_read_only=ON

4.)建议使用和主库规格一样好的硬件设备作为slave,存储采用PCIE-SSD才是王道

5.)建议使用GTID复制模式(待定)

6.)工具使用perconna-toolkit,可以跳过错误pt-slave-restart、主从一致性检查pt-table-checksum、修复不一致数据pt-table-sync

7.)主从延迟排查show slave status 看Seconds_Behind_Master参数的值来判断，该值为0表示主从复制良好，如果是正值表示已出现延迟，数字越大延迟越严重或者用percona-toolkit的pt-heartbeat来监测延迟情况

8.)主从延迟解决建议

a.)硬件配置和主库一致，强烈建议使用SSD硬盘，并且修改配置参数innodb_flush_method为0_DIRECT提升写入性能

b.)适当增大从库innodb_buffer_pool_size，减少IO压力

c.)从库不需要太高的数据安全，可以将sync_binlog设置为0或者500，且innodb_flushlog_at_trx_commit设置成2，减少磁盘IO压力

d.)开启并行复制MTS，最少升级到5.7.19版本，修复了很多bug

29.高可用方案

MGR 单写 HAProxy+自定义脚本 proxySQL 秒级切换

MHA

PXC

keepalived+双主复制 云服务器上keepalived只能设置单播，通常不支持浮动IP，往往需要用户提请工单来开通高可用虚拟IP

30.分库分表

数据迁移：先读出历史数据，然后按照分片规则写入，计算需要多少分片，建议单分片单表容量不超过1000w,数据尽可能均匀分布到各个节点