MySQL内存统计的分析与实践--刘开洋|爱可生

November 26, 2021 · 7 min read

演讲议题：

MySQL内存统计的分析与实践

讲师简介：

刘开洋，爱可生|技术工程师

供职于上海爱可生，从事数据库运维和产品交付，对数据库及周边有浓厚的学习兴趣，喜欢看书，追求技术。

议题介绍： 对于MySQL的内存泄漏在业内总是一件让人很头疼的事情，这次和大家分享下，我们从不同的维度对MySQL的内存统计进行相关的认识，进一步分析内存使用异常的现象，从而将其拓展到其他数据库甚至更加宽泛的C++系统中。

嘉宾专访

这次小编邀请到我们的老朋友-爱可生的技术专家，刘开洋老师，为我们带来《MySQL 内存统计的分析与实践》

问题一

小编知道，爱可生里卧虎藏龙，有非常多的技术专家走进了大家的视野，也有很多技术大佬隐藏在技术一线，首先我们邀请刘老师向3306π的小伙伴介绍下自己吧。

刘开洋 大家好，我是来自爱可生交付服务部北京团队的一名技术工程师，就像小编说的，加入爱可生之后接触到很多技术牛人，便开始了内卷，技术即是服务，工作就是学习，个人对数据库及周边技术有浓厚的学习兴趣，喜欢看书，追求技术。

问题二

小编刘老师不仅从事数据库运维，还负责相关产品交付，小编请刘老师介绍下目前您这边主要负责爱可生的什么产品呢，有什么优势呢？

刘开洋 来爱可生之后，对我司的云树系列产品都有所接触，其中北京这边的业务产品还是以DMP、RDS、DBLE以及SQLe为主，负责的客户也涉及银行、证券、保险、通信以及制造业等诸多领域，感触最深的是爱可生的DMP的自动化运维给客户带来的诸多便利，节省了很多的人力物力；DBLE也拥有很多社区贡献者，分布式使用体量很大，有独到之处，很多用户也可以进行二次开发，被深入到用户业务的各个方面。

问题三

小编说到产品，好的产品不仅仅在需求上拿捏用户痛点，在设计上精益求精，在产品交付的过程也是非常有学问的，而且也是被大家很容易忽视的一个能力，刘老师可否分享下您在产品如何有效交付方面的经验呢？

刘开洋 对于一个优秀的交付来讲，首先需要明确产品的定位，有条理地设计用户体验，在进行POC的过程中衡量相关指标，能够经受重复的检验，以客户为导向，专注于产品是否能真正解决客户的问题。在交付完成之后并不是真正的完成交付流程，还需要不断关注客户后续的反馈与体验，在一次次地沟通中不断对自己的能力与经验进行迭代。

问题四

小编本次刘老师带给我们的主题《MySQL内存统计的分析与实践》，所以小编采访下刘老师，主题设想的出发点是什么呢？会带给3306π小伙伴哪些惊喜呢？

刘开洋 官方一直没有过多关注MySQL内存的使用，对它的监控也不太全面，因为开源的原因，MySQL对于自身源码逻辑的设计仍不可避免地存在一定的缺陷，很容易在触发某个模块的bug导致了虚拟内存暴增，内存泄漏的问题，这次和大家分享下近期的一些内存问题带给我的一些内存统计上的思考，我们除了DBA常用的工具，还有哪些手段可以帮助我们定位MySQL的内存问题，从而扩展到对其他数据库以及C++程序的内存统计中

问题五

小编最后小编邀请刘老师和大伙讲讲，在您丰富的经验中，通过内存统计主要都可以解决我们哪些MySQL的常见问题呢？

刘开洋 通过MySQL不同维度的内存统计，可以帮助我们更好地理解数据库内存的使用，分析解决生产环境中的内存暴增、内存泄漏等问题，降低运维工程师的血压。

通过采访刘老师，小编其实心里非常期待刘老师的现场分享，已经提前感受到了很多的实践干货，相信12.18号3306π深圳专场还会有更多的惊喜等待着大家，我们拭目以待哦

Databend architecture

November 22, 2021 · 8 min read

BohuTang

Co-creator of Databend

Databend 是一个开源的、完全面向云架构的新式数仓，它提供快速的弹性扩展能力，并结合云的弹性、简单性和低成本，使 Data Cloud 构建变得更加容易。 Databend 把数据存储在像 AWS S3 ，Azure Blob 这些云上的存储系统，可以使不同的计算节点挂载同一份数据，从而做到较高的弹性，实现对资源的精细化控制。

Databend architecture

弹性在 Databend 中，存储和计算资源可以按需、按量弹性扩展。
安全 Databend 中数据文件和网络传输都是端到端加密，并在 SQL 级别提供基于角色的权限控制。
易用 Databend 兼容 ANSI SQL，并可以使用 MySQL 和 ClickHouse 客户端接入，几乎无学习成本。
成本 Databend 处理查询非常高效，用户只需要为使用的资源付费。

技术分享 | MySQL进程号、连接ID、查询ID、InnoDB线程与系统线程如何对应

November 22, 2021 · 8 min read

叶金荣

万里数据库开源生态负责人，Oracle MySQL ACE Director，腾讯云TVP成员

导读

一文快速掌握 MySQL进程号、连接ID、查询ID、InnoDB线程与系统线程的对应关系。

有时候，怀疑某个MySQL内存查询导致CPU或磁盘I/O消耗特别高，但又不确定具体是哪个SQL引起的。

或者当InnoDB引擎内部有semaphore wait时，想知道具体是哪个线程/查询引起的。多说一下，当有semaphore wait事件超过600秒的话，InnoDB会发出crash信号：

InnoDB: ###### Diagnostic info printed to the standard error stream
2020-12-13T09:41:33.810011Z 0 [ERROR] [FATAL] InnoDB: Semaphore wait has lasted > 600 seconds. We intentionally crash the server because it appears to be hung.
2020-12-13 10:41:33 0x7f3d92a4e700 InnoDB: Assertion failure in thread 139902430013184 in file ut0ut.cc line 917
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
09:41:33 UTC - mysqld got signal 6 ;

因此也要监控InnoDB的semaphore wait状态，一旦超过阈值，就要尽快报警并分析出问题原因，及时杀掉或停止引起等待的查询请求。

不过本文想讨论的是，MySQL的进程ID、内部查询ID、内部线程ID，和操作系统层的进程ID、线程如何对应起来。

数据库索引总结：模型数空运最快

November 22, 2021 · 10 min read

大家好，我是知数堂SQL 优化班老师网名：骑龟的兔子

我今天刷头条的时候看到了一位叫《老猿说开发》总结的索引不能使用口诀模型数空运最快

我觉得挺好，但是因为没有具体的案例，还有没说是哪个数据库这些需要完善的地方，所以写一篇文章。

模：模糊查询%在前面情况不能使用索引，这种情况非得说一个特例就是不回表情况

[email protected]>[employees]>desc select dept_no from t_group2 where dept_no like '%005';
+----+-------------+----------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t_group2 | NULL       | index | NULL          | ix_dept_no2 | 16      | NULL |   10 |    11.11 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+

型：数据类型有变化的时候不能使用

[email protected]>[employees]>desc test1 ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | varchar(10) | YES  | MUL | NULL    |       |
| n     | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

[email protected]>[employees]>select * from test1 ;
+------+------+
| id   | n    |
+------+------+
| 1000 | NULL |
| NULL | NULL |
+------+------+
2 rows in set (0.00 sec)

[email protected]>[employees]>show index from test1 ;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| test1 |          1 | ix_id    |            1 | id          | A         |           2 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)

[email protected]>[employees]>desc select * from test1 where id=1000;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test1 | NULL       | ALL  | ix_id         | NULL | NULL    | NULL |    2 |    50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)

[email protected]>[employees]>desc select * from test1 where id='1000';
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test1 | NULL       | ref  | ix_id         | ix_id | 43      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

数：索引列套用函数不能使用索引，这时候mysql 5.7可以用虚拟列，8.0可以直接使用函数索引，5.6的时候可以利用类似解方程方法解开

[email protected]>[employees]>desc select * from test1 where id='1000';
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test1 | NULL       | ref  | ix_id         | ix_id | 43      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

[email protected]>[employees]>desc select * from test1 where concat(id,'')='1000';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
[email protected]>[employees]>create index idx_t1_ke1 on test1((concat(id,'')));
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

[email protected]>[employees]>desc select * from test1 where concat(id,'')='1000';
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test1 | NULL       | ref  | idx_t1_ke1    | idx_t1_ke1 | 43      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

空：索引列有空值使用is null 就不能使用索引，这个在oracle 是没问题的但是在mysql 这个是错的

[email protected]>[employees]>desc test1 ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | varchar(10) | YES  | MUL | NULL    |       |
| n     | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

[email protected]>[employees]>select * from test1 ;
+------+------+
| id   | n    |
+------+------+
| 1000 | NULL |
| NULL | NULL |
+------+------+
2 rows in set (0.00 sec)

[email protected]>[employees]>show index from test1 ;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| test1 |          1 | ix_id    |            1 | id          | A         |           2 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)

[email protected]>[employees]>desc select * from test1 where id is null;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | test1 | NULL       | ref  | ix_id         | ix_id | 43      | const |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

[email protected]>[employees]>desc select * from test1 where id is not  null;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test1 | NULL       | range | ix_id         | ix_id | 43      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

运：索引列有数字运算的时候不能使用索引

[email protected]>[employees]>desc select * from test1 where id='1000';
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
|1| SIMPLE      | test1 | NULL       | ref  | ix_id         | ix_id | 43      | const |    1 |100.00| NULL  |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

[email protected]>[employees]>desc select * from test1 where id+0='1000';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|1| SIMPLE      | test1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |100.00| Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

最：复合索引，最左边原则，这个也需要分情况分版本，因为有skip index的存在

[email protected]>[employees]>show index from dept_emp2 ;
+-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table     | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| dept_emp2 |          1 | ix_dept_emp |            1 | dept_no     | A         |           8 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| dept_emp2 |          1 | ix_dept_emp |            2 | emp_no      | A         |      315555 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)
[email protected]>[employees]>desc select * from dept_emp2 where emp_no=10001 limit 10 ;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | dept_emp2 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 331008 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

[email protected]>[employees]>desc select emp_no,dept_no from dept_emp2 where emp_no=10001 limit 10 ;
+----+-------------+-----------+------------+-------+---------------+-------------+---------+------+-------+----------+----------------------------------------+
| id | select_type | table     | partitions | type  | possible_keys | key         | key_len | ref  | rows  | filtered | Extra                                  |
+----+-------------+-----------+------------+-------+---------------+-------------+---------+------+-------+----------+----------------------------------------+
|  1 | SIMPLE      | dept_emp2 | NULL       | range | ix_dept_emp   | ix_dept_emp | 20      | NULL | 33100 |   100.00 | Using where; Using index for skip scan |
+----+-------------+-----------+------------+-------+---------------+-------------+---------+------+-------+----------+----------------------------------------+
1 row in set, 1 warning (0.00 sec)

快：如果优化器认为使用全表比使用索引快，那就不会使用索引，说白了就是基于优化器或者统计信息，这个就是所谓的百分比，这里最重要的一点是使用索引一般情况下是回表，也就是发生随机IO,这个随着回表的量的大小变大，一般OLTP都是在表下查询少量数据情况，所以还是使用索引的情况会好

当然上面的情况之外，还有别的情况，如日期类型，还有字符串比较的时候等等，这些在课堂中有详细的解答。我是知数堂SQL 优化班老师~ ^^

最新一期SQL优化课，在12月份开始。

如有关于SQL优化方面疑问和一起交流的请加并且 @兔子@知数堂SQL优化

高性能MySQL,SQL优化群有叶金荣，吴炳锡两位大神坐镇：579036588

欢迎加入知数堂大家庭。

我的微信公众号：SQL开发与优化(sqlturning)

问题一​

问题二​

问题三​

问题四​

问题五​

导读​

问题一

问题二

问题三

问题四

问题五

导读