知数堂MySQL优化培训课程大纲(2020年最新版)

知数堂MySQL优化培训课程大纲(2020年最新版),未完待续。
最后更新时间:2020/5/27

序号 课程内容 知识要点
1. 基础通识篇
1 MySQL分支和版本的选择 1. MySQL简史、趣闻 2. 主流分支派别特性及区别 3. 几个主要版本5.6、5.7、8.0该如何选择
2 MySQL有别于其他数据库的特点 1. 整体技术特征 2. 如何最大化利用CPU资源 3. 内存设置有什么要注意的地方 4. 如何避免磁盘I/O性能瓶颈
3 MySQL硬件&系统优化checklist 1. 硬件层优化检查checklist 2. 系统层优化检查checklist 3. MySQL层优化检查checklist
4 玩转MySQL的几点建议 1、MySQL应用开发过程中有哪些可以提升和优化性能的地方 2、运维规范 3、安全规范
5 MySQL重点数据类型 1. 整型 2. 字符型 3. 日期时间型 4. 大对象类型 5. JSON,配合虚拟列的新玩法 6. 注意防范数据类型隐式转换

对我们的课程有兴趣的同学请扫码入群咨询

《叶问》第9期

首发:微信公众号「知数堂」
出处:https://mp.weixin.qq.com/s/idTW2BOy6eHs62_SrsdSgw

叶问
《叶问》是知数堂新设计的互动栏目,不定期给大家提供技术知识小贴士,形式不限,或提问、或讨论均可,并在当天发布答案,让大家轻轻松松利用碎片时间就可以学到最实用的知识点。

2018年10月23日,周二
MongoDB服务器CPU一直很高,最高达到900%,可能是哪些原因?

答:可能原因如下:

1、高并发场景下,服务器开启numa
2、mongo查询无索引,消耗大量内存和io
3、服务器硬件故障,例如CPU 内存 raid卡等
3、高并发写入下开启读写分离+oplog应用加锁
4、高并发短链接+最新SCRAM-SHA-1认证的情况

2018年10月30日,周二

MySQL运行环境中,当发现系统已经用到了swap,该怎么处理?

答:一般来说,发生swap的原因是系统认为内存不够了。

那么,当物理内存真的不够了,或者著名的NUMA都是引起swap的可能原因。通常的应对方法有几种:
1、通过BIOS、系统内核参数关闭NUMA,或者在mysqld启动时,利用numactl关闭NUMA的使用
2、调低系统使用swap的权重,设置内核参数 参数 vm.swappiness 不高s 不高于10
3、Linux下使用free命令查看内存使用情况,确认是否发生了内存泄露,可以去微信公众号「老叶茶馆」中发送“OOM”
4、修改MySQL参数innodb_flush_method = O_DIRECT,这样InnoDB在读写物理数据的时候会绕过cache来访问磁盘
5、优化SQL效率,避免产生额外的分组、排序、临时表情况发生,参考文章:文章:http://t.cn/EwLIuFv
6、在【夜间或业务不繁忙】时适合执行 swapoff -a,并执行sync刷新操作系统内存脏页到硬盘

2018年11月1日,周四
大量SQL语句文本,如何快速导入到MySQL中?

答:

1、可在SQL文本前,添加set session sql_log_bin=0(需要在从库也导入一次)。
2、导入期间临时修改参数sync_binlog=10000、innodb_flush_log_at_trx_commit=0、innodb_autoinc_lock_mode=2。
3、导入前,根据业务情况看能否删除除了自增列主键外的其他索引。
4、将SQL文件切割成多份,再并发多线程导入。
5、若该SQL文件是每个INSERT一行,需要先行将多行合并成一行,即启用extended-insert模式。
6、以上建议,在线上环境请谨慎评估该骚操作的风险性。
7、以上建议,仅考虑尽快导入,涉及到和具体业务需求相冲突时(例如太快导入反倒会影响在线数据库性能),以实际情况为主。

《叶问》第8期

首发:微信公众号「知数堂」
出处:https://mp.weixin.qq.com/s/ijNEm5bQvNo1sm48aXjlGA

叶问
《叶问》是知数堂新设计的互动栏目,不定期给大家提供技术知识小贴士,形式不限,或提问、或讨论均可,并在当天发布答案,让大家轻轻松松利用碎片时间就可以学到最实用的知识点。

2018年9月21日,周五
innodb已经使用了独立undo表空间,那么ibdata1还会增长吗,为什么?

答:使用独立undo表空间后,ibdata1里主要存储Data dictionary、Rollback segments、Double write buffer、change buffer、Foreign key constraint system tables等数据。

下面两种情况可能还会导致ibdata1文件增长:

1、当使用共享表空间模式时(设置innodb_file_per_table=0),ibdata1还会存储用户数据,导致ibdata1文件增长。(当然了,现在应该几乎没人再这么用了)

2、在高I/O负载时,可能会来不及刷新和回收change buffer page,也会导致ibdata1文件增长。(此种情况再高并发压测情形下更容易出现)

2018年9月30日,周日

怎么安心过好国庆节?

答:

1、检查备份。不管是逻辑备份、物理备份,还有binlog也要备份。备份文件可恢复,才是好备份。

2、做好安全措施。授权合理不要过大,在外不要连陌生的网络办公。危险操作,尽量等节后执行。

3、健康巡检。节前巡检包括:数据库桩体、硬盘空间、日志信息、物理硬件、系统负载是否预警等。

「知数堂」祝大家节日快乐,DB不宕机跑得欢。

2018年10月9日,周二
MySQL线上实例insert慢常见原因有哪些?

答:

1、锁等待:SQL产生的间隙锁、自增锁、死锁、MDL锁、外键检查锁,锁等待时间

2、iops达到瓶颈:例如备份任务、高频binlog redolog等文件写入

3、semi-sync:因为网络抖动,MySQL半同步、增强半同步导致语句卡住

4、高并发:高并发场景下,导致系统资源达到瓶颈,从而SQL执行慢

5、大字段:当前表索引过多,或者写入大量的text类型数据

6、硬件故障:因为磁盘、raid卡、内存等物理硬件故障导致写入慢

7、磁盘资源耗尽:操作系统的磁盘、inode资源耗尽

8、文件系统故障:MySQL data目录的所在挂在的不可写、或者被设置为只读

9、binlog group commit等待

10、参数配置:innodb_buffer、redo_buffer过小

11、autocommit:事物非自动提交,等待程序提交。

2018年10月12日,周五
虽然命中索引,但SQL效率仍然慢,可能有哪些原因?

答:

1、索引字段重复值或者空值太多。

2、查询条件范围太广返回结果数太多,全索引扫描

3、没有利用到覆盖索引,造成大量回表

4、查询字段过多,并且包含大字段

5、索引字段数据分布太随机,回表不多也会引起大量随机io

6、统计信息不准

7、表的单行数据值很大,需要较多io

8、表中包含多个索引, 命中的索引不是最优的索引。

《叶问》第7期

首发:微信公众号「知数堂」
出处:https://mp.weixin.qq.com/s/PPFE5xXUZiz5UY_h5oSpGA

叶问
《叶问》是知数堂新设计的互动栏目,不定期给大家提供技术知识小贴士,形式不限,或提问、或讨论均可,并在当天发布答案,让大家轻轻松松利用碎片时间就可以学到最实用的知识点。

2018年8月9日,周四
MySQL的表中有唯一索引,设置unique_checks为0时,还能否写入重复值?

首先,即便设置unique_checks=0,也无法往唯一索引中写入重复值。

其次,设置unique_checks=0的作用在于,批量导入数据(例如load data)时,在确保导入数据中无重复值时,无需再次检查其唯一性,加快导入速度。

所以,unique_checks=0并不是允许唯一约束失效,而是再批量导数据时不再逐行检查唯一性。

2018年8月15日,周六

某人曰,在数据检索的条件中使用!=操作符时,存储引擎会放弃使用索引。 理由:因为检索的范围不能确定,所以使用索引效率不高,会被引擎自动改为全表扫描。
你认可他的说法吗?

答:通常情况下,这个说法是正确的。当然,也有特殊情况,话不能说绝对了。
有一个测试表共80万条数据,其中type列只有1、2两个值,分别占比97%和3%。
这种情况下,查询条件 WHERE type != 1,是有可能也可以走索引的。
下面是两个SQL的执行计划:
mysql> desc select * from t1 where type = 1\G
************ 1. row ************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ref
possible_keys: type
key: type
key_len: 4
ref: const
rows: 399731
filtered: 100.00
Extra: NULL

mysql> desc select * from t1 where type != 1\G
************ 1. row ************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ref
possible_keys: type
key: type
key_len: 4
ref: const
rows: 10182
filtered: 100.00
Extra: NULL

type数据分布
mysql> select type, count(*) as cnt from t1 group by type order by cnt;
+——+——–+
| type | cnt |
+——+——–+
| 2 | 38304 |
| 1 | 761690 |
+——+——–+

2018年8月17日,周一
Redis集群的slot迁移是如何实现的?

答:迁移源slot设置为migrating 状态,迁移目标slot设置为importing状态。

在内部用dump & restore命令,把数据迁移到目标节点,迁移结束之后,移除migrating和importing状态。

在迁移过程中如果有数据访问,如果数据没迁移到目标节点,那么直接返回结果,如果迁移到目标节点,那么给客户端返回ASK重定向。

2018年8月27日,周一
在大表执行ddl的过程中,若临时中断,会发生什么状况,需要特别处理吗 ?

前提说明:MySQL5.7.23、innodb表、“双1”

1、添加/删除列,采用copy的方式

1.1、ctrl+c。在当前session中,手动执行ctrl+c。无影响,并且会自动删除产生的临时文件。

1.2、kill -9。在执行ddl的时候,服务器发生意外宕机或者手动执行kill -9。待MySQL启动后,则会自动执行InnoDB Recovered流程。并且不会删除产生的临时文件,需要手工处理。

2、添加/删除索引,采用INPLACE方式

2.1、ctrl+c,同1.1

2.2、kill -9。不会删除临时文件,也不会执行InnoDB Recovered流程并且报错 Operating system error number 2 in a file operation ….OS error: 71

在开始执行alter table的过程中,在没有结束的时候,并不会写入到binglog文件中。

《叶问》第6期

首发:微信公众号「知数堂」
出处:https://mp.weixin.qq.com/s/7eJ26lrK5Txp1zkg9zND7w

关 于 叶 问

《叶问》是知数堂新设计的互动栏目,不定期给大家提供技术知识小贴士,形式不限,或提问、或讨论均可,并在当天发布答案,让大家轻轻松松利用碎片时间就可以学到最实用的知识点。

2018年7月26日,周四
专访黄炎:MySQL中间件的性能测试和常规业务性能测试相比有什么不同?

性能测试的方法论基本都一样, 以“观察-分析-改进-再观察”这个循环进行。

常规业务由于业务交互复杂、技术栈庞杂、性能瓶颈通常集中于业务, 性能测试使用的分析方法比较简单, 通过诊断业务通常可以低成本地找到性能瓶颈。

MySQL中间件的应用场景比较简单、技术栈稳定、性能瓶颈通常集中于架构和环境, 性能测试使用的分析方法比较多, 对性能瓶颈的分析通常成本比较高. 另外在这一方面的现有知识积累并不很成体系, 也是成本较高的原因之一。

2018年8月2日,周四
《全方位认识SYS系统库》公开课精彩互动问答:

1、为什么我用root用户调用call ps_setup_enable_instrument(‘wait’);报错说存储过程不存在?

答:sys schema是从MySQL 5.7之后才默认支持,请确保你的数据库版本正确,且先使用use语句切换默认数据库,否则请带上 sys.库名称限定前缀。

2、myisam锁如何查询?

答:MyISAM 不支持事务,所以不存在事务锁,但可以查询表级锁(例如:MDL锁),通常表级锁是Server层添加的锁,与具体的存储引擎无关,所以与InnoDB存储引擎查询方法一致,建议多多尝试即可得出答案。

3、为什么我查询session系统表,当前正在执行SQL的会话的progress为 NULL 呢?

答:对于progress信息,仅支持stages事件(performance_schema.setup_instruments表的name字段以stages开头的采集项),其他事件类型不支持,且就算是stages类型事件,也不是所有的采集项都支持,可以通过观察performance_schema.events_stages_current表的WORK_COMPLETED和WORK_ESTIMATED字段,需要不为NULL值,progress信息就是根据这些不为NULL的值进行计算的

注意:要成功采集stages性能数据,必须打开stages事件相关的instruments和consumers

如果不满足以上条件,session视图查询到的progress字段就会显示NULL。

4、如果要系统的学习SYS 库,可以看什么资料,有什么途径?

答:(1)MySQL 官方网站:https://dev.mysql.com/doc/refman/5.7/en/sys-schema.html

(2)关注微信公众号:”数据浮云”,我们后续会陆续推送 sys 系统库相关的文章

(3)关注知数堂或3306pai社区,后续会以gitbook或电子书的方式发布 sys 系统库完整的官方翻译版

5、线上数据库,开启ps和关闭ps功能,mysqld使用的内存会相差20G,可以判断ps会用到很多主机内存。怎么判断ps功能回来多少内存?怎么进行优化ps对内存的使用?

答:ps的整体功能无法动态开关,必须在数据库启动之前就设置好,能够动态开关的只是ps的具体的instruments采集项和consumers存储表,对于查询ps使用的内存总量,可以使用语句 select sys.format_bytes(sum(current_alloc)) from sys.x$memory_global_by_current_bytes where event_name like ‘memory/performance_schema%’; 查询,对于ps内存使用的优化,MySQL 提供了一系列performance_schema打头的系统变量来进行灵活配置,请根据需要自行调整,默认情况下不建议调整,除非你真的需求,否则就会浪费内存空间。

2018年8月7日,周二
在MySQL中如果发现乱码的情况该如何判断原因及应对?

1、直接修改法. alter或者pt-osc等其他工具直接对数据进行修改。

2、备份修改法. 利用mysqldump或者其他逻辑备份进行备份,备份的结果集再利用iconv进行转换

3、跳过字符集备份.利用mysqldump备份的时候跳过字符集-t –skip-set-charset。在恢复的时候指定表的字符集。

那么应该如何避免乱码呢?

1、首先要从应用端到MySQL,采用统一编码格式。

2、在MySQL的配置中,指定编码格式。

3、在上线或者导入SQL的时候,要注意本地的编码集。