文档章节

MySQL 8.0 优化

LoSingSang
 LoSingSang
发布于 01/07 10:53
字数 811
阅读 10
收藏 0


优化效果(从大到小):

硬件设备 -> 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;经常表连接。

© 著作权归作者所有

共有 人打赏支持
LoSingSang
粉丝 3
博文 53
码字总数 16173
作品 0
深圳
程序员
私信 提问
MySQL 8.0里GROUP BY有变化,注意了

导 读 如题所示,MySQL 8.0开始 group by 默认是没有排序的,那MySQL 8.0之前和 8.0 就有可能结果出现不同 需要警惕 运行如下SQL: 下面是8.0 版本: 运行相同的SQL: 如果这种情况下 进行分...

老叶茶馆_
2018/11/28
0
0
【MySQL】【翻译】8.0 GA版本的新特性 What’s New in MySQL 8.0【上】

嗦一嗦 MySQL 8.0的新特性 What’s New in MySQL 8.0? (Generally Available) 原文链接:https://mysqlserverteam.com/whats-new-in-mysql-8-0-generally-available 原文作者:Geir Hoydalsv......

对唔住
2018/05/01
0
0
技术大牛养成指南:吃的草够多,你也能成为大牛(附思维导图)

非常高兴的向大家宣布MySQL 8.0 GA版本发布,MySQL 8.0是一个得到全面增强且极具吸引力的新版本。不限于下面几点: We proudly announce General Availability of MySQL 8.0. Download now!...

技术小能手
2018/05/15
0
0
高手问答第 196 期 —— 号称性能提升 2 倍的 MySQL 8 来袭,我们该如何应对?

OSCHINA 本期高手问答(2018 年 5 月 8 日 — 5 月 14 日)我们邀请到了@叶金荣 和吴炳锡@wubx 两位老师和大家一起聊聊 MySQL 8。 MySQL 8.0 正式版发布已有两周,最大的变化除了突然从 “5” ...

局长
2018/05/07
7K
40
【MySQL】【翻译】8.0 GA版本的新特性 What’s New in MySQL 8.0

嗦一嗦 MySQL 8.0的新特性 What’s New in MySQL 8.0? (Generally Available) 原文链接:https://mysqlserverteam.com/whats-new-in-mysql-8-0-generally-available 原文作者:Geir Hoydalsv......

对唔住
2018/06/29
0
0

没有更多内容

加载失败,请刷新页面

加载更多

大数据教程(11.9)hive操作基础知识

上一篇博客分享了hive的简介和初体验,本节博主将继续分享一些hive的操作的基础知识。 DDL操作 (1)创建表 #建表语法CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name [(col_name ...

em_aaron
今天
0
0
OSChina 周四乱弹 —— 我家猫真会后空翻

Osc乱弹歌单(2019)请戳(这里) 【今日歌曲】 @我没有抓狂 :#今天听这个# 我艇牛逼,百听不厌,太好听辣 分享 Led Zeppelin 的歌曲《Stairway To Heaven》 《Stairway To Heaven》- Led Z...

小小编辑
今天
2
0
node调用dll

先安装python2.7 安装node-gyp cnpm install node-gyp -g 新建一个Electron-vue项目(案例用Electron-vue) vue init simulatedgreg/electron-vue my-project 安装electron-rebuild cnpm ins......

Chason-洪
今天
3
0
scala学习(一)

学习Spark之前需要学习Scala。 参考学习的书籍:快学Scala

柠檬果过
今天
3
0
通俗易懂解释网络工程中的技术,如STP,HSRP等

导读 在面试时,比如被问到HSRP的主备切换时间时多久,STP几个状态的停留时间,自己知道有这些东西,但在工作中不会经常用到,就老是记不住,觉得可能还是自己基础不够牢固,知识掌握不够全面...

问题终结者
昨天
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部