MySQL 8.0 Study 012 Tips

+-------------------------+-------+
| 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语句之前执行如下语句：

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;

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

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

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

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

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

启动 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线程。

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)) >