mysql对cpu的利用特点：
5.1之前，多核支持较弱；5.1可利用4个核；5.5可利用24核；5.6可利用64个核；每个连接对应一个线程，每个并发query只能使用一个核
mysql对内存的利用特点：
有全局内存和会话内存，建议将会话内存设置较小；
内存管理简单、有效（如在高并发环境下，可以增加物理内存以减少物理IO，提高并发性能）；
query cache建议关闭或设置很小（query cache 有一个全局的锁，有任何的DML，都会导致query cache再更新一下）；
执行计划没有缓存；
建议按照热点数据的总量的15%-20%来规划，专用单实例可以分配为物理内存的50%-70%；
类似KV简单数据，建议采用redis等NOsql来缓存
mysql对磁盘IO的利用特点：
binlog，redo log，undo log主要是顺序IO
datafile主要是随机IO
OLTP以随机IO为主；OLAP以顺序IO为主
myisam是堆组织表（HOT），innodb是索引组织表（IOT）
innodb相比myisam更消耗磁盘空间（1.5倍到2倍）
innodb基于主键的读写相较于myisam更快（原因：myisam有大量碎片；myisam把索引文件和数据文件分开，需要先检索索引文件后再检索数据文件）
确认瓶颈：
cpu利用较高，通常为索引使用不当
内存发生swap，通常为内存分配不当（分配过多或过少）
IOwait太高，通常为内存不足，IO设备性能太差，索引使用不当，正在频繁的读取大量数据(select *)，正进行频繁的排序(group by)
方法：
（操作系统级别）
top（整个系统当前的状态以及某个进程或某个服务消耗最多的cpu或内存）
sar（-u 看cpu；-b 看io；-r 看内存）
vmstat（类似sar 看cpu，io，内存）
iotop（看那个进程的IO消耗最高）
dstat（结果集类似vmstat，sar）
（数据库级别）
slow log（慢查询，定位耗时较长的SQL），优先处理发生频率较高的慢查询，在处理耗时最长的；
show global status（1.连接数（活跃，不活跃的），设置interactive_timeout,wait_timeout值，可以减少不活跃的连接；2.TPS,QPS,DML_Active;3.各种buffer，cache的利用率、命中率）；
TPS=(handler_commit_d+handler_rollback_d)/uptime_d
QPS=(questions_d2-questions_d1)/uptime_d
DML_Active=[conn_select_d+conn_insert_d+conn_update_d+conn_delete_d]/uptime_d
show processlist
show engine innodb status（锁，事务，等待）
innodb_buffer_pool_wait_free若大于1，则buffer pool不够用
innodb_row_lock_current_waits:当前的行锁数量
innodb_row_lock_time_avg:行锁的平均耗时
innodb_row_lock_waits:等待行锁的次数
slow_queries:慢查的次数
table_locks_immediate:立即锁的次数，table_locks_waited:立即等待的次数
TPS变得很低（100）原因：数据库负载很高；前端出现情况，业务进不来
pt-ioprofile:可以直接查看innodb内部那些文件被频繁的读写,
制定方案：
并发太高（thread pool）;频繁读取或频繁排序（索引）；swap（调整内存）；
硬件优化：
BIOS设置优化：1.system profile（系统配置）选择performance per wait optimized(DAPC)，发挥最大功耗性能
2.memory frequency(内存频率)选择maximum preformance(最佳性能)
3.关闭C1E（允许处理器处于闲置状态时启用或禁用处理器切换至最低性能状态，默认启用）
4.关闭C states（允许启用或禁用处理器所有可用电源状态下运行，默认启用）
IO子系统优化:
1.阵列卡配置cache(缓存)及BBU(为缓存提供后备电量)模块，提高IOPS
2.设置写策略为WB(write back，即把数据先写在cache卡，然后在刷回到磁盘里面),或FORCE WB,禁用WT策略
3.关闭预读，将其cache用作写缓存
4.阵列级别使用RAID1+0(写IO更高)而不是raid 5(读IO更高)
5.关闭物理磁盘cache策略，防止丢数据
6.使用高转速硬盘，不使用低速盘
7.使用SSD或者PCIe-SSD盘
操作系统的优化
1.vm.swappiness RHEL 7 之前设置为0(可能导致进程OOM-killer)，RHEL 7以上，可以设置为10，减少发生swap
2.调整io scheduler(/sys/block/sdX/queue/scheduler)，SAS建议设置为deadline；SSD建议设置为noop或deadline
3.文件系统的选择，建议使用XFS其次是ext4
XFS目录内容是B+树，文件分配是B+树
ext4目录内容是Htree(一种特殊的hash B树)，文件分配Extents/bitmap
ext3目录内容是Htree（一种特殊的hash B树），文件分配bitmap
mysql层面的优化
全局参数：1.interactive_timeout(一个活跃的连接等待超时的时间)，wait_timeout(断开一个不活跃连接的超时时间)，建议将两个值设置一样；如果有连接池，可以把timeout调大一些或用默认值，如果没有连接池，建议把timeout值调到300以内
2.open_files_limit(设置不够会导致报错can't open file:xxx;os error:too many open files)，可以通过ulimit -n修改内核级别的限制
其他类似问题：mysql error message：can not connect to mysql server;SQL:can't create a new thread
解决方法：vi /etc/security/limits.d/90-nproc.conf 修改为 - nproc 65536
或者 vi /etc/bashrc 添加ulimit -u 65536
3.max_connections ,连接数过高时，应该调低timeout值；thread pool线程池功能，官方版本不支持，可使用percona、mariadb分支版本
长连接：指在一个连接上可以连续发送多个数据包，在连接保持期间，如果没有数据包发送，需要双方发链路检测包。mysql的长连接如果长期闲置，mysql会8小时后（默认时间）主动断开该连接。
短连接：是指通讯双方有数据交互时，就建立一个连接，数据发送完成后，则断开此连接，即每次连接只完成一项业务的发送。
mysql内存相关的优化
mysql使用总内存=global_buffers+thread+buffers
全局参数：innodb_buffer_pool_size,innodb_additional_mem_pool_size,innodb_log_buffer_size,key_buffer_size,query_cache_size,table_open_cache,table_definition_cache,thread_cache_size
会话/线程内存：
（read_buffer_size,read_rnd_buffer_size,sort_buffer_size,join_buffer_size建议为2M）,binlog_cache_size,tmp_table_size,max-heap-table-size,thread_stack,net_buffer_length,bulk_insert_buffer_size
尤其注意：不要把tmp_table_size,max-heap-table-size设置过大，建议不高于100M
innodb相关配置优化
innodb_buffer_pool_size:缓存大量的脏数据，缓存事务的信息，缓存锁的信息，建议设置为50%-80%（太小会导致1.tps很低，大量等待；2.可能会table full；3.锁不够用，大量的所等待）
innodb_data_file_path：建议设置innodb共享表空间文件ibdata1初始化的大小至少为1G（避免在高并发的情景下导致ibdata1急剧增加，大大影响性能）
ibdata1包含的部分信息（data dictionary,double write buffer,insert buffer,rollback segments ,undo space）
innodb_flush_log_at_trx_commit：0每秒把log刷新一次到磁盘；1每个事务刷新一次；2.介于0-1之间
innodb_log_buffer_size（建议设置为16M）&innodb_log_file_size:不应设置太大(不应超过512M)，以免在mysql重新启动时读取redo log恢复过程太慢
transaction_isolation：事务隔离级别(默认为RR)，
其他配置参数优化
general log:一般不用打开
log_bin：建议一定要打开，将binlog和数据隔离或使用IO能力强的设备，可以提高响应；密集写很高的应用可以启用SSD
sync_binlog：需要高的事务的一致性需要设置为1，即每个SQL语句需要刷一次binlog；不要求数据一致性可以设置为0
raid0时binlog丢失怎么办：需要重新生成binlog，若slave也打开了binlog，可以复制从的
long_query_time
log_slow_query
设计优化
建议使用innodb存储引擎（优点：支持并发；可以保证数据的一致性；支持crash recovery(故障自动修复)；有更高存取效率(行锁降低锁粒度，更高内存利用率、索引存取效率)）
schema设计优化
不管存储引擎是否为innodb，都应该设计自增列主键
日期、时间、ipv4使用int unsigned类型存储
性别、是否等枚举类型，使用ENUM/TINYINT,而不是char/varchar
杜绝TEXT/BLOB,可以做垂直拆分，或者转成myisam表
所有字段显示定义not null
索引设计优化
基数很低的字段不创建索引（mysql还不支持bitmap索引）
采用第三方系统实现text/blob全文检索
常用排序(order by)、分组(group by )、取唯一(distinct) 字段上创建索引
索引数量不要太多
多使用联合索引，少用单独索引
字符类型需要索引时，创建前缀索引
无法使用索引的场景
通过索引扫描的记录超过30%，变成全表扫描
联合索引中，第一个索引列使用范围查询(<,>,<=,>=,between)
如：select ... where key_part1>? and key_part2=?
联合索引中，第一个查询条件不是最左索引列
如：select ... where key_part2>? and key_part3=?
模糊查询条件列最左以通配符%开始
如：select ... where key_part1 like '%xx' and key_part2=?
内存表(heap 表)使用hash索引(只能用于=或！=才使用hash索引)时，使用范围检索或order by,group by或模糊匹配
两个独立索引，其中一个用于检索，一个用于排序(只能用到一个索引)
如：select ... where key1= order by key2=;---执行计划中，会有using filesort
表关联字段类型不一样(也包括长度不一样) ：会导致隐式类型转换
索引字段条件上使用函数 如：where func(key1)=?；where key+?=?
注意：where key=?+?? ---可以使用索引；where key+？=？？---不可以使用索引
常见杀手级SQL
select *
order by rand()
limit huge_num,offset
select count(*) on innodb table（在innodb频繁count(*)）
架构设计优化
减少物理IO(方法：前端加cache)
转变随机IO为顺序IO(方法：本地写队列，最后合并写)
减小活跃数据(方法：冷热数据分离)
分库分表（方法：垂直、水平拆分，分布式集群）
读写分离
OLTP、OLAP分离
优化工具
pt-ioprofile(可以查看innodb里面那个数据库文件被频繁的读写)
mysqldumpslow（可以分析slow log）
pt-query-digest+box anemometer/query-digest-ui
---------------------
作者：jh993627471
来源：CSDN
原文：https://blog.csdn.net/jh993627471/article/details/79278041
版权声明：本文为博主原创文章，转载请附上博文链接！