思路：
———————————————————————
1.找到瓶颈处
2.针对瓶颈进行优化
3.数据库优化策略分为三步走：
- 配置优化，cpu，内存，io
- 表结构优化：表引擎，索引，表拆分（大表转小表，减少大字段，控制表数量及表大小），分表分库，主从分离，读写分离，多主
- SQL优化：简化SQL，减少join，减少sql计算，减少存储过程，使用preparedstatement，找到慢sql逐个优化
4.程序优化：
- 减少事务，降低锁的概率
- 使用缓存应对读（但要保证数据的及时性）
- 异步写操作（但要保证事务顺序和成功）
实践：
———————————————————————
寻找瓶颈，有两个思路：
- 根据监控找到耗时较大的功能、业务：
- 程序级别的监控，每个请求的时间记录，access_log或者程序在某个controller的切面中将时间打印出来；
- 根据用户反馈，这个往往不准，因为用户看到慢的时候可能已经形成了雪崩效应；
- 捋日志，找第一个慢的，或者找到累计最多的，然后单独测试该功能复现和模拟；
- 原因可能会很多：比如数据库锁住了，内存不够，SQL性能太低....根据原因找；
- 根据性能监控，查看资源使用情况
- 在应用服务器，数据库服务器多方面监控资源使用情况，核心关注CPU，内存，IO和网络三点的使用情况
- CPU忙：
- 通常在进行计算，计算包括数据处理，图像，正则，科学计算等；从直观上来看，就是循环多，有很多for循环..
- 常用监控命令：vmstat,mpstat
- 如果要看某个进程的情况，可以使用"while :; do ps -eo pid,ni,pri,pcpu,psr,comm | grep 'firefox'; sleep 1; done" 类似的语句来查看
- IO忙：大数据load到内存中，比如数据库、文件检索，本身匹配并不耗CPU，但是数据比较大，比较多需要频繁换页；
- 监控换页数量，io读写次数，以及iowait数量
- 常用命令：iostat,lsof
- 需要注意，swap其实挺慢的，可以通过top、cat /proc/meminfo、vmstat 来监控swap的使用情况；
- 网络监控：
- 监控起来最复杂，主要监控网络流出和流入情况，以及丢包、延迟等；
- 在linux下面可以使用：iptraf,netperf,tcpdump,tcptrace,tcpcopy
- 工具：salt+zabbix,negios等都可以实现细粒度的监控；
- 压力测试：针对某些怀疑的点进行压力测试，模拟发现对应的问题，同时检查系统的吞吐能力；
- 常用工具及命令：Vmstat、sar、iostat、netstat、free、ps、top等
- 常用组合方式
- 用vmstat、sar、iostat检测是否是CPU瓶颈
- 用free、vmstat检测是否是内存瓶颈
- 用iostat检测是否是磁盘I/O瓶颈
- 用netstat检测是否是网络带宽瓶颈
针对瓶颈优化：
- 不同的问题原因不同，优化方案也不一样，通常来讲，分为代码优化和配置优化两条腿，配置优化又分为操作系统，服务和应用的优化。
mysql配置优化：
—————————
- 安装以后在mysql的配置文件中有：my-huge.cnf,my-mediam.cnf,my-small.cnf，可以根据需要自己拷贝一份做修改；/usr/local/share/mysql/
- innodb_buffer_pool_size：数据缓冲池，是数据和索引缓存的地方，这个值越大越好，这能保证你在大多数的读取操作时使用的是内存而不是硬盘。典型的值是5-6GB(8GB内存)，20-25GB(32GB内存)，100-120GB（128GB内存）；根据内存情况来设定。
- innodb_log_file_size：redo日志的大小，用于确保写操作快速可靠，且在崩溃的时候可以恢复；默认512M，4G比较合适；
- max_connections：最大连接数，默认151，根据实际情况扩大，但要注意程序端不会无限的增加（连接池）
- innodb专属设置：
- innodb_file_per_table，建议为on，每个表单独有自己的idb文件，可以再drop,truncate或者delete的时候会收表空间，如果表特别多的话，不建议使用（比如超过10k）
- innodb_flush_log_at_trx_commit，是否支持事务一致性，通常为1，当性能要求大于数据一致性要求是可以设为0，比如从库；
- innodb_flush_method，数据和日志写入方式，如果有raid，且不会down机，使用O_Driect，否则用fdatasync，可以用sysbench来测试；
- inodb_log_buffer_size，为未执行的事务分配的缓存，默认1M，可以根据innodb_log_waits状态来看，如果不够的话可以适当增加；
- 其他设置：
- query_cache_size，查询缓存，这个可能会有坑，建议设为0，通过其他方式来提升缓存；
- 设定主从：log_bin,
- 是否跳过域名解析：skip_name_resolve，这样数据中只能使用ip来进行授权；
- table_cache,表缓存，可以根据open_tables（show global status）来进行优化
- show global status;show status like 'xxx';
- show variables
- show innodb status
- show processlist
- show tables status
- show processlist
- 事务和锁的检查
- slow_query_log，设置为on
- long_query_time，超时时间，比如设置为1，就是1s
- slow_query_log_file，记录慢日志的文件
- log_queries_not_using_indexes，记录没有使用到索引的查询语句
根据某个语句进行优化：
- desc(explain) 某条语句，查看执行计划，可以看到该语句是否使用了索引，临时表、文件排序检索行数等；
- 设置profiling，然后执行语句，查看profiling的日志：set profiling=1;execute sql;show profiles;show profile for query 1;
mysql结构优化：
—————————
- 每个表有主键，
- 字段简单：
- 类型选择好，
- 数字：尽量用整型，包括枚举、时间、ip等都可以数字化，decimal少用
- 文本：varchar/char，禁用text
- 时间：性能考虑可以用bigint，否则用timestamp（不用datetime）
- 二进制、文本、图片等不要用数据库存储，大字段可以分开表存放，减少io;
- 字符集编码统一，join的时候可能会出现字符集不匹配无法使用索引的问题；
- 尽量not null
- 可以适当冗余字段，减少join
- 拆表拆库：主要将资源分配到更多cpu和内存上，对结构简化有帮助
- 单表不超过：1000w，有字符串的尽量在500w以内，单库不超过400张表；字段在30-50个；
- 每个表都有主键，且建议为数据库自增int
—————————
- 原则：每个sql尽量小，尽量不用大事务，
- 尽量每条语句都要是用到索引
- 语句中尽量少join
- 在语句中少使用子查询，如果一定要，用join比子查询好；
- 不用函数或在SQL中进行计算，包括类型转换
- 少用or
- union all 代替union
- 少select *，而是需要什么查询什么
- 尽早过滤
- 尽量少排序
- 优先优化数量多，性能没那么好的SQL；再优化频率低，杀伤力大的sql
- 大量数据插入用load data，且放到低峰期执行
- 索引数量不是越多越好，而是用得越多越好
- 索引字段不要在sql中计算，可能无法使用索引
- 尽量不用外键等约束，由程序来保障，避免高并发死锁
程序优化：
———————————————————
1.控制事务大小
2.充分利用应用服务器的资源
3.在应用端缓存，分布式、文件、本地都可以
Refer:
- https://www.zhihu.com/question/19719997
- 腾讯云数据库建议： https://www.qcloud.com/community/article/164816001481011831?fromSource=gwzcw.82729.82729.82729
- 性能监控： http://blog.csdn.net/huangjin0507/article/details/51879705
- linux下面5款监控工具： http://www.ttlsa.com/monitor-safe/monitor-software-5/
- query_cache_size: http://jackyrong.iteye.com/blog/2173523
- 参考某个语句优化：http://www.apeblog.cn/article/18.html
- mysql库表及sql优化：http://blog.csdn.net/renzhenhuai/article/details/9128755
没有评论:
发表评论