MySQL核心原理4

性能优化

1.硬件选择

CPU：OLTP 高可用小事务查询、OLAP分析系统仓库

RAM：越大越好

快磁盘存储：SSD、阵列

优网络、系统：关DNS、mysql

接入区

DMG

内网core：DNS、mysql

文件句柄打开数量即开辟io通道可命令：ulimit -n查看    1024

2. 缓存

Redis、memcrched 内存中，减少mysql压力

缓存穿透：布隆过滤器bloom filter

缓存雪崩：主从机制加高可用方案、高并发限流降级、数据预热

主[从]复制：用户、授权、开日志

                 master->binary log二进制日志<-io thread->[relay log<->sql thread]

                 master_log_pos=0  从哪里开始、replicate_do_db 过滤

                  级联主从解决不同步

3.分库分表 mycat

分库：系统复杂度上升，联合查询、数据聚合

分表：水平拆分，2000万以下数据可用索引，再多查询时间指数型增加，系统复杂度上升，拆分规则（取余分表、一次hash）

4.mysql配置作用域 热加载Dynamic

全局参数、会话参数

找配置文件、覆盖的

sort_buffer_size/join_buffer_size                  缓存池256~2M

innodb_buffer_pool_size                               物理机80~90%给它

innodb_buffer_read/write_io_thread             cpu核数2倍

innodb_print_all_deadlocks=1                      死锁日志

innodb_log_file(buffer)_size                          默认48M

innodb_flush_log_at_trx_commit=1

刷盘   buffer    oscache（0：mysql进程级别，1：v，2：操作系统级别）       logfile

刷脏数   innodb_io_capacity  默认zoom

库表设计：1NF 多列        2NF 多关联           3NF   外键

5.慢查询定位：业务驱动、测试驱动、慢查询日志

Time user@Host query_time        lock_time           Row_sent Rows_examined set  timestamp select

mysqldumpslow  --help

-s   order  排序、-t   Num  前几名

慢查询排查：执行计划看type、rows、filtered、extra、key、key_len

show profile 分析sql调优，会话级别属性先打开

6.sql优化

1. 基于索引完成扫描（sql结果集）      2.索引不用函数或运算      3.where  like    %放右边      4.索引扫描，联合索引左->右越多越好      5.索引排序      6.有效列      7.join  小结果驱动大的