Lin Hong's TECH Blog! 刀不磨要生锈,人不学习要落后 - Thinking ahead

MySQL 8.0 Study 012 Tips

2023-02-21
MySQL
 

MySQL 8.0 Study 012 Tips

学习系列

  • MySQL CTE
  • MySQL Window Function 窗口函数
  • MySQL dbdeployer Percona Server
  • MySQL IO线程及相关参数调优
  • MySQL 调优参数
  • MySQL 企业版

MySQL CTE

CTE(公用表表达式)是一个命名的临时结果集，仅在单个SQL语句的执行范围内存在。与派生表类似，CTE不作为对象存储，仅在查询执行期间持续。与派生表不同，CTE可以是自引用。此外，与派生表相比，CTE提供了更好的可读性和性能。CTE的结构包括:名称，可选列列表和定义CTE的查询。定义CTE后，可以像SELECT，INSERT，UPDATE，DELETE或视图一样使用。

CTE有两种用法，非递归的CTE和递归的CTE。非递归的CTE可以用来增加代码的可读性，增加逻辑的结构化表达。递归的CTE，应用的场景也比较多，比如查询某结构下的子结构，每个子结构下面的子结构等等，就需要使用递归的方式。递归的CTE当然递归不会无限下去，不同的数据库有不同的递归限制，MySQL8.0中默认限制的最大递归次数是1000。超过最大低估次数会报错：Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value，由参数cte_max_recursion_depth决定。

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| cte_max_recursion_depth | 1000  |
+-------------------------+-------+

语法:

with_clause:
    WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...
#cte_name命名单个公用表表达式，并且可以在包含该WITH子句的语句中用作表引用。
subquery部分称为“CTE的子查询”，是产生CTE结果集的部分。
如果公用表表达式的子查询引用其自己的名称，则该表表达式是递归的，RECURSIVE关键字必须被包含。

递归示例:

mysql [localhost:8032] {msandbox} (test) > WITH RECURSIVE cte (n) AS (SELECT 1   UNION ALL   SELECT n + 1 FROM cte WHERE n < 10 ) SELECT * FROM cte;
+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
+------+
10 rows in set (0.00 sec)

mysql [localhost:8032] {msandbox} (test) > 
mysql [localhost:8032] {msandbox} (test) > WITH RECURSIVE cte (n) AS (SELECT 1   UNION ALL   SELECT n + 1 FROM cte WHERE n < 1001 ) SELECT * FROM cte;
ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
mysql [localhost:8032] {msandbox} (test) >

cte_max_recursion_depth 系统变量强制对CTE的递归水平的数量限制。服务器终止任何递归级别高于此变量值的CTE的执行。 max_execution_time 系统变量强制用于执行超时 SELECT在当前会话中执行的语句。该MAX_EXECUTION_TIME 优化器提示强制为每个查询执行超时SELECT在它出现的语句。

#### 在执行CTE语句之前执行如下语句：

SET max_execution_time = 1000; -- impose one second timeout

#### 或者，在CTE语句本身中包含优化程序提示：

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte
)
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM cte;

窗口函数和CTE(公用表表达式)的增加，简化了SQL代码的编写和逻辑的实现，新特性的增加，可以用更优雅和可读性的方式来写SQL。 不过这都是在MySQL8.0中实现的新功能，在MySQL8.0之前，只能按照较为复杂的方式实现。

MySQL Window Function 窗口函数

MySQL8.0窗口汗水 Window Function

序号函数：ROW_NUMBER()、RANK()、DENSE_RANK()
分布函数：PERCENT_RANK()、CUME_DIST()
前后函数：LAG()、LEAD()
头尾函数：FIRST_VALUE()、LAST_VALUE()
其他函数：NTH_VALUE()、NTILE()

MySQL dbdeployer Percona Server

下载:(2023/02)

https://www.percona.com/downloads

wget https://downloads.percona.com/downloads/Percona-Server-8.0/Percona-Server-8.0.31-23/binary/tarball/Percona-Server-8.0.31-23-Linux.x86_64.glibc2.28-minimal.tar.gz

Ref:

Using dbdeployer to manage MySQL, Percona Server and MariaDB sandboxes

dbdeployer unpack --prefix=ps Percona-Server-8.0.31-23-Linux.x86_64.glibc2.28-minimal.tar.gz
or
dbdeployer --sandbox-binary=/opt/mysql/ unpack mysql-8.0.31-linux-glibc2.12-x86_64.tar.xz 

dbdeployer --sandbox-binary=/opt/mysql/ deploy single 8.0.31

企业版: mysql-commercial-8.0.32-linux-glibc2.12-x86_64.tar.xz

dbdeployer --sandbox-binary=/opt/mysql/ --prefix=mysqlee unpack mysql-commercial-8.0.32-linux-glibc2.12-x86_64.tar.xz

dbdeployer --sandbox-binary=/opt/mysql/ --sandbox-directory=mysqlee_8_0_32 deploy single 8.0.32
mysqlee8.0.32

DBDeployer 常用命令速查表

MySQL IO线程及相关参数调优

参考:

MySQL IO线程及相关参数调优

MySQL 隐式类型转换

常见索引失效：

1. 条件索引字段"不干净"：函数操作、运算操作

2. 隐式类型转换：字符串转数值；其他类型转换

3. 隐式字符编码转换：按字符编码数据长度大的方向转换，避免数据截取

如:

c2:`c2` varchar(11) NOT NULL,
select * from t_num where c2 = "2";
# 去掉等号右边值的引号，即字符串和数值进行比较，索引失效
select * from t_num where c2 = 2;

utf8mb4和utf8两种不同字符集（编码）类型的字符串在做比较时，MySQL会先把 utf8 字符串转成 utf8mb4 字符集，再做比较。
为什么？字符集 utf8mb4 是 utf8 的超集，再做隐式自动类型转换时，为了避免数据在转换过程中由于截断导致数据错误，会“按数据长度增加的方向”进行转换。

表连接过程中，被驱动表的索引字段上加函数操作，会导致对被驱动表做全表扫描。


优化手法：

1.修改统一join字段的字符集

2.对驱动表下手，将连接字段的字符集转换成被驱动表连接字段的字符集
如:explain select n.* from t_num n join t_cou c  on convert(n.c2 using utf8) = c.c2 where n.c1 = 1;

类型转换:

1、字符串转整型
# 字符开头的一律为0
select convert("abc", unsigned integer);
->1
# 'abc' = 0是成立的，因此查询时等号右边使用对应的类型很重要，0匹配出字段字符开头数据，'0'只匹配0
select 'abc' = 0;
->0
# 数字开头的，直接截取到第一个不是字符的位置
select convert("123abc", unsigned integer);
->123

2、时间类型转换
# 1.date转datetime：末尾追加 00:00:00
select * from time_demo where c1 between "2022-01-06" and "2022-01-08";
# 结果分析：c1是datetime类型，进行比较时，between and中的date类型会转换成datetime
# 即 where c1 between "2022-01-06 00:00:00" and "2022-01-08 00:00:00";
# 同 where c1 >= "2022-01-06 00:00:00" and c1 <= "2022-01-08 00:00:00";
# 格式化date转datetime
select date_format("2022-01-08","%Y-%m-%d %H:%i:%s");
# 2.datetime转date：直接截取date部分
# 3.date转time，没有意义，直接变成 00:00:00

MySQL 的 Thread pool

性能优化·thread pool 原理分析

大连接问题

现有mysql 处理客户端连接的方式会触发mysql 新建一个线程来处理新的连接，新建的线程会处理该连接所发送的所有 SQL 请求，即 one-thread-per-connection 的方式

mysql [localhost:8032] {msandbox} ((none)) > show global variables like '%thread_handling%';
+-----------------+---------------------------+
| Variable_name   | Value                     |
+-----------------+---------------------------+
| thread_handling | one-thread-per-connection |
+-----------------+---------------------------+
1 row in set (0.01 sec)

mysql [localhost:8032] {msandbox} ((none)) >

优点及存在的问题

在连接数较小的情况下可以很快的响应客户端的请求，但当连接数非常大时会创建很多线程，这样会引起以下问题：
1. 过多线程之间的切换会加重系统的负载，造成系统资源紧张且响应不及时；
2. 频繁的进行线程的创建及销毁以及线程间同时无序的竟争系统资源加重了系统的负载。

thread_pool正是为了解决以上问题而产生的；

什么是thread_pool

thread_pool(线程池)，是指mysql 创建若干工作线程来共同处理所有连接的用户请求，用户的请求的方式不再是 ‘one thread per connection’，而是多个线程共同接收并处理多个连接的请求，在数据库的底层处理方面(mysql_execute_command)，单线程的处理方式和线程池的处理方式是一致的。

thread_pool 的工作原理

启动 thread_pool 的mysql 会创建thread_pool_size 个thread group , 一个timer thread, 每个thread group 最多拥有thread_pool_oversubscribe个活动线程，一个listener线程，listener线程负责监听分配到thread group中的连接，并将监听到的事件放入到一个queue中，worker线程从queue中取出连接的事件并执行具体的操作，执行的过程和one thread per connection 相同。timer threaad 则是为了监听各个threadgroup的运行情况，并根据是否阴塞来创建新的worker线程。

threadpool在使用中存在的问题：

1. 由于threadpool严格控制活跃线程数的限制，如果同时有多个大查询同时分配到了同一个thread group，则会造成此group中的请求过慢，rt 升高，最典型的就是多个binlog dump 线程同时分配到了同一个group内；

2. 开启了事务模式时，非事务模式的请求会放入低优先级队列，因此可能在较长时间内得不到有效处理，极端情况下，会导致实例hang 住，例如某个连接执行了 flush tables with read lock ,并且此连接的后续请求都会放入低优先级，那么有可能会造成实列hang住；

3. 较小并发下，threadpool 性能退化的问题；

mysql [localhost:8033] {msandbox} ((none)) > show variables like 'plugin_dir';
+---------------+-------------------------------+
| Variable_name | Value                         |
+---------------+-------------------------------+
| plugin_dir    | /opt/mysql/8.0.32/lib/plugin/ |
+---------------+-------------------------------+
1 row in set (0.01 sec)

mysql [localhost:8033] {msandbox} ((none)) >

[root@ol8mysql01 mysqlee_8_0_32]# find /opt/mysql -name thread_pool.so
/opt/mysql/mysqlee8.0.32/lib/plugin/debug/thread_pool.so
/opt/mysql/mysqlee8.0.32/lib/plugin/thread_pool.so
[root@ol8mysql01 mysqlee_8_0_32]# 

[root@ol8mysql01 mysqlee_8_0_32]# cat my.sandbox.cnf  | tail -6
### Thread Pool
plugin_dir=/opt/mysql/mysqlee8.0.32/lib/plugin/
plugin-load-add=thread_pool=thread_pool.so
plugin-load-add=tp_thread_state=thread_pool.so
plugin-load-add=tp_thread_group_state=thread_pool.so
plugin-load-add=tp_thread_group_stats=thread_pool.so
[root@ol8mysql01 mysqlee_8_0_32]# ./restart

mysql [localhost:8033] {msandbox} ((none)) > SELECT PLUGIN_NAME, PLUGIN_STATUS
    ->        FROM INFORMATION_SCHEMA.PLUGINS
    ->        WHERE PLUGIN_NAME LIKE 'thread%' OR PLUGIN_NAME LIKE 'tp%';
+-----------------------+---------------+
| PLUGIN_NAME           | PLUGIN_STATUS |
+-----------------------+---------------+
| thread_pool           | ACTIVE        |
| TP_THREAD_STATE       | ACTIVE        |
| TP_THREAD_GROUP_STATE | ACTIVE        |
| TP_THREAD_GROUP_STATS | ACTIVE        |
+-----------------------+---------------+
4 rows in set (0.00 sec)

mysql [localhost:8033] {msandbox} ((none)) > 
mysql [localhost:8033] {msandbox} ((none)) > show global variables like '%thread_handling%';
+-----------------+--------------------+
| Variable_name   | Value              |
+-----------------+--------------------+
| thread_handling | loaded-dynamically |
+-----------------+--------------------+
1 row in set (0.01 sec)

mysql [localhost:8033] {msandbox} ((none)) >

MySQL 调优参数

MySQL(パフォーマンスチューニング編)

MySQL 企业版

如何下载MySQL企业版数据库

MySQL Enterprise Edition

Referece

参考:

MySQL8.0之CTE(公用表表达式)

Have a good work&life! 2023/02 via LinHong

Similar Posts

上一篇 Oracle Migration Methods Advisor Tips

下一篇 MySQL 8.0 Study 013 Tips

Comments

Content