优化效果(从大到小):
硬件设备 -> MySQL数据库 -> Linux操作系统 -> 表设计
----------------------------------------------------------------------------------------------------------------
1
硬件设备优化
提升硬件设备,例如使用SSD高速磁盘,CPU等。
----------------------------------------------------------------------------------------------------------------
2
MySQL数据库配置优化
# 物理内存的50%~80%,这里为
innodb_buffer_pool_size=2000000000
# 双1模式
# 按事物刷盘,刷日志
innodb_flush_log_at_trx_commit=1
# 提交1次事物刷1次,可以为n
sync_binlog=1
# 脏页占innodb_buffer_pool_size的比例时,触发刷脏页到磁盘
# 25%~50%
innodb_max_dirty_pages_pct=30
# 后台进程最大IO性能指标
# 默认200,如果SSD,调整为5000~20000
innodb_io_capacity=200
# 默认10M。防止高并发下,数据库受影响
innodb_data_file_path=ibdata1:1024M:autoextend
# 默认2,单位s。慢查询时间。建议0.1~0.5
long_qurey_time=0.3
# 8.0默认row。记录格式,让数据安全可靠
binlog_format=row
# 默认8小时。交互等待时间和非交互等待时间
# 建议300~500s,两参数值必须一致,且同时修改
interactive_timeout=500
wait_timeout=500
# 过大,容易OOM(内存溢出)
# 调高该参数应降低interactive_timeout、wait_timeout的值
# 默认151
max_connections=200
# 过大,实例恢复时间长;过小,造成日志切换频繁
# 默认50331648,50MB
innodb_log_file_size=50331648
# 全量日志建议关闭
# 默认关闭
general_log=0
----------------------------------------------------------------------------------------------------------------
3
Linux操作系统层面优化
cat /sys/block/sda/queue/scheduler
noop deadline [cfq]
修改为deadline或noop,cfq,严重影响数据库性能
文件系统采用xfs,其次是ext4,不用ext3
内核参数vm.swappiness,默认60;0表示不用swap,容易OOM;100表示使用swap,而不释放内存
cat /proc/sys/vm/swappiness
无法修改,则编辑/etc/sysctl.conf,加入vm.swappiness的值
还有
vm.dirty_background_ratio:默认10,建议不超过10
vm.dirty_ratio:默认20,建议不超过20
----------------------------------------------------------------------------------------------------------------
4
表设计及其他优化
1.库名、表名、字段名小写,用“_”分隔。
2.使用自增列int或bigint作为主键
3.ipv4使用int存储,inet_aton、inet_ntoa
4.sex、status使用tinyint
5.业务表切忌出现大类型字段,sql避免or子句
6.只需获取必要字段时,避免使用select *,还可能用到覆盖索引
7.建立索引在选择性高的字段上,不要在sex、status上建
8.单表索引数量不要超过4~5个
9.字段默认加上not null,避免出现null,count()统计不会计null
10.需要存储表情需要UTF8mb4
11.使用搜索引擎sphinx替代模糊搜索
12.explain执行计划中的extra项中有Using filesort或Using temporary时,考虑创建排序索引和分组索引
13.pt-query-digest捕获慢查询语句
----------------------------------------------------------------------------------------------------------------
5(附加)
优化一条SQL语句:
1.表的数据类型是否设计得合理,数据类型越简单越小原则
2.表中碎片整理,alter table comment_infos engine = innodb;
3.查看表的统计信息,select * from information_schema.tables where table_name="comment_infos"\G;
4.explain查看执行计划
5.建索引前查看该字段的选择性,越接近1越高,主键索引和唯一索引的选择性是1
6.在查看explain,对比索引效果。在合理的字段建立索引:经常出现在where后;经常order by或group by;经常表连接。