MySQL:XtraBackup8.0 流程图和存储过程中的隐式

---

最近比较忙，虽然案例不少，但是没时间整理。而学习了一些东西但是暂时也没有成体系，因此公众号也没有过多更新。在这个节日来临之前，首先祝大家节日快乐，心想事成。然后简单分享一下XtraBackup 8 流程图和一个小案例，供大家参考。

---

一、XtraBackup 8 流程图

这里简单画了一个流程图，不太详细，供大家参考，网上都是5.7的，这部分主要注意lock instance和ps.log_status的使用。

image.png

而在xtrabackup_checkpoints文件中的几个lsn含义如下：

• from_lsn 为增量备份相关的LSN metadata_from_lsn（忽略掉）
• to_lsn 为最后一次检查点(CKPT)的 metadata_to_lsn 来自log_status
• last_lsn 为拷贝结束的LSN为 metadata_last_lsn 来自log_status
• flushed_lsn 为备份开始时数据刷盘到的lsn来自backup_redo_log_flushed_lsn

其中的last_lsn为8.0 prepare应用redo时候的结束点，其来自ps.log_status。

image.png

二、存储过程中的隐式转换

这个为大概是一个语句单独跑就很快，但是放到存储过程里面就非常的慢了几乎跑不出来。这里用模拟的来说明，建表如下：

mysql> CREATE DATABASE `testproc1` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
Query OK, 1 row affected (0.01 sec)
mysql> 
mysql> use testproc1;
Database changed
mysql> show create table  tu1 \G
*************************** 1. row ***************************
       Table: tu1
Create Table: CREATE TABLE `tu1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show create table  tu2 \G
*************************** 1. row ***************************
       Table: tu2
Create Table: CREATE TABLE `tu2` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

查看执行计划
mysql> desc select * from tu1,tu2 where tu1.id=tu2.id and tu1.name='test' and tu2.name='g';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | tu1   | NULL       | ref  | name          | name | 63      | const |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | tu2   | NULL       | ref  | name          | name | 63      | const |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+

很显然这个执行计划是没有问题，接着做下面的操作，

mysql> CREATE DEFINER=`root`@`localhost` PROCEDURE `proc5`(in intput_name varchar(10)) begin    select * from tu1,tu2 where tu1.id=tu2.id and tu1.name='test' and tu2.name=intput_name;    desc select * from tu1,tu2 where tu1.id=tu2.id and tu1.name='test' and tu2.name=intput_name;    show warnings; end $$
mysql> call proc5('g') $$
Empty set (0.00 sec)

+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | tu1   | NULL       | ref  | name          | name | 63      | const |    1 |   100.00 | NULL                                               |
|  1 | SIMPLE      | tu2   | NULL       | ALL  | NULL          | NULL | NULL    | NULL  |    1 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+----------------------------------------------------+
2 rows in set (0.00 sec)

+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                     |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `testproc`.`tu1`.`id` AS `id`,`testproc`.`tu1`.`name` AS `name`,`testproc`.`tu2`.`id` AS `id`,`testproc`.`tu2`.`name` AS `name` from `testproc`.`tu1` join `testproc`.`tu2` where ((`testproc`.`tu2`.`id` = `testproc`.`tu1`.`id`) and (`testproc`.`tu1`.`name` = 'test') and (convert(`testproc`.`tu2`.`name` using utf8mb4) = 'g')) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

这个时候这个存储过程虽然和单独执行的语句相同，但是执行计划却使用BNL，如果表的数据量比较大那么全表扫描就会带来巨大的开销，同时我们也可以看到下面的转换：

• (convert(testproc.tu2.name using utf8mb4) = 'g'))

很明显这里tu2.name='g'的条件被转换了，因为'g'是utf8mb4的因此导致了索引的失效。

那么对于存储过程中的变量到底使用的是什么字符集，这实际上如果不显示指定的情况下就是和当前库用关，我们show create procedure的时候会看到如下信息

character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8mb4_general_ci

而存储过程变量的字符集类型就是Database Collation，这个和当前库的字符集是一样的。而从代码来看，实际上就是根据这个来确认的变量类型，对于这个存储过程而言intput_name是一个sp_variable变量，其初始化来自Create_field::init函数而其字符集来自函数fill_field_definition中的

  if (field_def->init(thd, (char*) "", field_type, lex->length, lex->dec,
                      lex->type, (Item*) 0, (Item*) 0, &cmt, 0,
                      &lex->interval_list,
                      lex->charset ? lex->charset :
                                     thd->variables.collation_database, //来自线程的collation
                      lex->uint_geom_type, NULL))
  {
    return true;
  }

其实就是thd->variables.collation_database，那么问题转变为thd->variables.collation_database什么时候转换从跟踪来看在函数Stored_program_creation_ctx::change_env函数中进行thd->variables.collation_database的转换。

  virtual void change_env(THD *thd) const
  {
    thd->variables.collation_database= m_db_cl; //这里改变了线程的字符集

    Default_object_creation_ctx::change_env(thd);
  }

而m_db_cl变量的定义如下：

  /**
    db_cl stores the value of the database collation. Both character set
    and collation attributes are used.

    Database collation is included into the context because it defines the
    default collation for stored-program variables.
  */
  const CHARSET_INFO *m_db_cl;

从注释也能看出来m_db_cl就是看到的Database Collation，而查看存储过程的Database Collation（show create procedure命令），看的也正是m_db_cl，因此这是非常重要的，调用栈：

sp_show_create_routine 
  ->sp_head::show_create_routine 
    ->Stored_program_creation_ctx::get_db_cl

因此存储过程中的变量的字符集就是show create procedure看到的Database Collation的类型，而默认就是建库的字符集。最后查看官方文档这一部分也有描述，

The server handles the data type of a routine parameter, local routine variable created with DECLARE, or
function return value as follows:
...
For character data types, if CHARACTER SET is includedd in the declaration, the specified character set
and its default collation is used. If the COLLATE attribute is also present, that collation is used rather than
the default collation.
If CHARACTER SET and COLLATE are not present, the database character set and collation in effect at
routine creation time are used. To avoid having the server use the database character set and collation,
provide an explicit CHARACTER SET and a COLLATE attribute for character data parameters.
If you alter the database default character set or collation, stored routines that are to use the new
database defaults must be dropped and recreated.
The database character set and collation are given by the value of the character_set_database
and collation_database system variables.