MySQL

  • 从XtraBackup的RC版本到1.0版本,一直在使用这个产品进行数据库备份。最近在群里面,依旧有一些在讨论MySQL的备份到底是应该使用物理备份还是逻辑备份。在本文中,我们将比较这两种备份方式,并提供有关它们的概要介绍。

    当前, MySQL 数据库备份,有两种主要的备份方式,即 mysqldump(逻辑备份)和 XtraBackup(物理备份)。

    mysqldump vs Xtrabackup

    mysqldumpXtraBackup
    归属Oracle MySQL(是MySQL的一部分)Percona(是一家非常了不起的MySQL开源服务商)
    备份文件格式SQL(文本)物理数据文件(二进制)
    使用场景小到中型数据库,定期备份和还原,跨版本迁移大型数据库,高性能备份和还原,避免表锁定
    优点– 备份以文本格式存储,易于查看和编辑。– 高性能备份和还原,适用于大型数据库。
    – 跨 MySQL 版本兼容性较好。– 备份期间不会锁定表,对生产环境影响小。
    – 可备份指定数据库、表或查询结果。– 支持增量备份,可节省存储空间和备份时间。
    缺点– 对于大型数据库,备份和还原速度相对较慢。– 备份文件通常较大。(通常压缩率要低一些)
    – 在备份期间,可能会锁定表,影响生产环境查询。– 不容易查看备份内容,因为是二进制备份。
    原理通过导出 SQL 语句来备份数据和结构。备份 InnoDB 存储引擎的物理数据文件和日志文件。
    增量支持不直接支持。(可以通过备份binlog来实现)支持增量备份,可减少备份数据的大小。

    一些其他的重要差异

    两者除了上述差异,根据经验一些重要的建议如下:

    • mysqldump是逻辑备份,在恢复的时候一方面跨版本的效果会更好,因为都是SQL语句;同时,也可能会因为字符集、数据库参数配置(如SQL_MODE等),导致恢复的时候失败或者出现一致性的问题。而XtraBackup是物理备份,数据一致性总是可以保障,但是跨版本恢复能力比较弱。
    • mysqldump的备份与恢复的是官方自带的产品,所以也是被广泛使用的产品,但是因为他在大数据量(例如超过几百GB)时备份的性能、恢复的性能较差,所以,XtraBackup会是很好的补充。
    • 在实践中,XtraBackup也是广泛被使用的,稳定性有比较好的保障,但是配置与使用的成本略微要高一些。在构建自己的复制方案的时候,对于大型的生产系统中,比较建议使用。

    概述mysqldump(逻辑备份)

    概要介绍: mysqldump 是 MySQL 数据库备份的一种经典工具,它通过导出 SQL 语句来备份数据库中的数据和结构。这种备份方式被称为逻辑备份,因为它备份的是数据的逻辑表示,而不是物理数据文件。以下是 mysqldump 的一些关键特点:

    • 适用性: mysqldump 适用于小型到中型的 MySQL 数据库,或者数据库大小不超过几百 GB 的情况。
    • 备份内容: mysqldump 会生成包含 CREATE TABLE 和 INSERT 语句的 SQL 文件,其中包括数据表结构和数据内容。
    • 优点:
      • 数据备份以文本格式存储,易于查看和编辑。
      • 跨 MySQL 版本的兼容性较好,可以在不同版本之间迁移备份数据。
      • 具有备份数据库、表或特定查询结果的灵活性。
    • 缺点:
      • 对于大型数据库,备份和还原速度相对较慢。
      • 在备份期间,数据库可能会锁定表,影响生产环境的查询。
      • 逻辑备份的还原速度相对较慢。

    概述XtraBackup(物理备份)

    概要介绍: XtraBackup 是一种高性能的 MySQL 物理备份工具,主要用于备份 InnoDB 存储引擎的数据文件和二进制日志文件。以下是 XtraBackup 的一些关键特点:

    • 适用性: XtraBackup 适用于大型 MySQL 数据库,对备份性能和恢复速度有高要求的情况。
    • 备份内容: XtraBackup 备份的是数据库的物理数据文件,包括 InnoDB 存储引擎的数据和日志文件。
    • 优点:
      • 高性能备份和还原,特别适用于大型数据库。
      • 备份期间不会锁定表,对生产环境的影响较小。
      • 支持增量备份,可以节省存储空间和备份时间。
    • 缺点:
      • 备份文件通常较大。
      • 不容易查看备份内容,因为它是二进制备份。

    建议

    根据你的数据库需求和性能要求,选择适当的备份方式:

    1. mysqldump: 适用于小型数据库或需要定期备份和还原的数据库,以及对备份文件易于查看和编辑的情况。
    2. XtraBackup: 适用于大型数据库或对备份性能和恢复速度有高要求的数据库,尤其是在需要避免表锁定和支持增量备份的情况下。
    3. 在某些情况下,可以结合使用两种备份方式,以满足不同的需求。这也是一种常用策略。
    4. 无论选择哪种备份方式,都应定期测试备份恢复过程,以确保备份的可用性和完整性。这一点非常重要。

    最终的选择取决于数据库的特定需求和性能要求。根据实际情况,你可以灵活地使用 mysqldump 和 XtraBackup 来满足你的数据库备份和恢复需求。

  • 在过去二十年中,移动互联网飞速发展,催生了大量LBS相关应用,这也让空间信息处理成为较为基础的诉求。PostgreSQL因为其起源就与空间信息处理关系很大,所以在该领域一直有着非常大的优势。MongoDB也在很早就对空间信息处理做了很强的支持,这也帮助MongoDB在发展过程中拿下来部分市场。MariaDB也在去年8月(参考)收购了厂商“CubeWerx”以增强其在地理信息的存储与分析上的能力。

    在过去的十年,MySQL也在不断的增强空间信息的处理能力,本文概述了,当前MySQL在这一块的能力现状。

    当前,数据库访问与处理GIS相关的信息主要参考的是:OGC Standards中的“Simple Feature Access – Part 2: SQL Option”(链接)。当前,MySQL支持的空间类型主要包括:

    • 点、折线、多边形、空间(可以存储前面三种类型中的任何一种)
    • 多点、多折线、多多边形、多空间(可以存储多个任何类型)

    使用MySQL空间类型,则最好使用InnoDB或NDB引擎(其他还支持空间类型的引擎有MyISAM、ARCHIVE),其中InnoDB支持较为完整,对空间索引也有比较好的支持。

    如果要支持地理信息,则可以使用“WGS 84系统”标准的坐标系统,正式名称是:世界大地测量系统(World Geodetic System, WGS),对应的SRID为4326。WGS是当前被广泛使用的地球空间坐标系统,例如GPS就是使用该坐标系统。该坐标系统,建立以地球的质心为中心的一套坐标系统,里面包括地球的一些基础数据等。

    在MySQL可以通过如下Query可以查看MySQL中的WGS 84系统的一些基本信息:

    SELECT *
           FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS
           WHERE SRS_ID = 4326\G
    *************************** 1. row ***************************
                    SRS_NAME: WGS 84
                      SRS_ID: 4326
                ORGANIZATION: EPSG
    ORGANIZATION_COORDSYS_ID: 4326
                  DEFINITION: GEOGCS["WGS 84",DATUM["World Geodetic System 1984",
                              SPHEROID["WGS 84",6378137,298.257223563,
                              AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],
                              PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],
                              UNIT["degree",0.017453292519943278,
                              AUTHORITY["EPSG","9122"]],
                              AXIS["Lat",NORTH],AXIS["Long",EAST],
                              AUTHORITY["EPSG","4326"]]
                 DESCRIPTION:

    基础对象和对应的WKT表达

    MySQL或者说SQL标准中支持的对象包括如下,:Point、Linestring、Polygon、Multi Point、Multi Linestring、Multi Polygon、Geometry Collection。

    具体的:

    • Point、LineSting都比较好理解
      • 在WKT表达中,点的X/Y坐标,之间并没有逗号;但是,MySQL的Point函数,则是需要逗号的。
      • 这里省略了一个叫“LineRing”的概念,LineRing就是一个不相交且头尾相连的LineString
    • Polygon有两种,一种是普通的,一种是带hole的Polygon
    • Polygon是一个Surface,不仅仅是组成边缘的点,还包含内部覆盖区域
    • Polygon即便是普通的(不带hole),外部也会有一个括号
    • Polygon通常是由多个LineRing组成

    在这个规范下,就可以非常简单的创建OGC标准中的对象,例如:

    CREATE TABLE geom (
        id int,
        p POINT SRID 0
    );
    
    INSERT INTO geom(id,p) values (1,Point(12,30));

    在实际查询中,默认的空间类型会返回二进制类型数据,所以客户端查询需要进行一次转换,将其转换容易阅读的文本类型,即标准的WKT(Well-Known Text):

    转化前:

    mysql> select * from geom;
    +------+---------------------------+
    | id   | p                         |
    +------+---------------------------+
    |    1 |              (@      >@ |
    +------+---------------------------+
    1 row in set (0.00 sec)

    使用了WKT函数转换后:

    mysql> select id,ST_AsText(p) from geom;
    +------+--------------+
    | id   | ST_AsText(p) |
    +------+--------------+
    |    1 | POINT(12 30) |
    +------+--------------+

    好的,这是一个简单的入门介绍,更多的能力需要自己探索,祝玩得开心。

  • 使用MySQL的函数索引

    ·

    从MySQL 8.0.13起,开始支持函数索引功能,该功能可以很好的帮助开发人员或者DBA去优先生产环境的SQL语句。通常,我们是并建议在SQL语句的查询条件中对列进行任何的函数计算的,因为这种做很有可能导致原本可以使用索引的查询条件,变得无法使用索引。

    例如,我们看如下的查询条件:

    SELECT * FROM user_info WHERE substr(id_card_no,1,6) = '330106';

    虽然,该表的id_card_no上原本是存在索引的,但是上面的写法则会导致SQL无法正常使用id_card_no列上的索引。一般来说,我们会建议开发人员,避免这种写法,更多的是将表达式放到右侧,如上的SQL则建议修改为:

    SELECT * FROM user_info WHERE id_card_no like '330106%';

    但某些情况下,我们可能无法修改SQL,例如:

    • 有很多的函数计算或表达式操作并不能简单的转换到表达式的右边,例如:uid%1024 = 7
    • 应用程序来自第三方,而我们并不拥有第三方的代码,也无法进行修改SQL
    • 生产故障已经由此产生,可能来不及修改线上的代码或SQL
    • 还有一个略微“牵强”的说法:SQL语法更加注重解释性,并不关注实现,基于此大原则,上面的SQL写法并没有什么问题。

    函数索引使用示例

    MySQL的函数索引是8.0版本引入的重要特性之一。它允许开发人员在查询中使用函数,并且依旧可以有效地加速查询性能。具体的,函数索引的作用是通过在函数表达式上创建索引,在处理字符串、日期、数值等类型的数据时特别有用。

    (more…)
  • 近期,据可靠的非正式消息,MySQL可能很快会发布新的版本管理模式:通过长期稳定版(Long-term Support)和短期创新版本(Innovation Releases)的方式进行管理。如果采用这种模式,将会更加有利于新特性新功能的引入,同时保持LTS的长期处于较稳定的状态(可以参考ClickHouse的版本管理现状),缺点则是版本会非常多,对新手不是那么友好。另外,因为MySQL 5.7的生命周期将于今年10月正式结束,如果届时依旧没有新的版本的话,MySQL 8.0就会是唯一的稳定版,通常对于快速发展的开源软件来说,这并不健康。所以,前述的消息虽然是非正式的,但是相信是非常可靠的。那么新的版本是号会是8.1、9.0、或者23、2023,拭目以待”。

    本文总结了过去20年,MySQL的版本发展历史,回顾一下其重大功能发布的情况,以及大版本发布的节奏。

    (more…)
  • MySQL的不可见索引

    ·

    不可见索引(Invisible Indexes)MySQL 8.0之后引入的一个重要特性。也可以帮助DBA或者开发者更好的管理和维护数据库的索引。本文将介绍不可见索引的一些常见使用场景和注意事项。

    更加安全的删除没用的索引

    通常,线上运行时间较长的系统,可以通过索引使用统计信息知道哪些索引是从来不被使用的,但依旧会占用磁盘空间,并且会影响系统的写入/更新/删除操作的性能。但是删除索引的操作,有时候也会带来意想不到的系统性能下降,所以,在正式删除之前,可以先将索引修改为不可用,待观察数日后再进行删除,会更加安全。

    性能分析

    有时候在对线上系统进行SQL性能分析时,有时候为了排除某些索引对查询性能影响时,可以暂时的将某些索引暂时标记为不可用,再统计此时的SQL执行时长与性能。然后,再将索引置为可见,再次执行SQL,并统计执行时长与性能。

    通过这样对比,可以非常简单直观、量化的观察到索引对对于具体SQL性能的提升。

    不可见索引依旧会有维护成本

    虽然不可见索引不会被查询优化器使用,但在对数据进行DML操作(如:插入、更新、删除)时仍会被维护。这意味着不可见索引可能会对数据库性能产生一定影响。在使用不可见索引进行性能测试或分析时,请务必权衡这一点。

    不建议长期保留不可见索引

    如果确定某个不可见索引对查询性能没有帮助,建议尽早删除该索引,以节省存储空间和减小维护成本。否则,该索引对系统性能没有起到任何正面作用,反而会占用空间,并影响DML的性能。

    优化器选项可以让不可见索引生效

    在优化器选项(optimizer_switch)中,可以通过打开标记位(use_invisible_indexes),来强制优化器忽略索引的不可见属性,这在增加了SQL性能调试时的灵活性。

    可以通过SELECT/SHOW命令查询优化器选项,也可用通过SET命令变更该选项:

    SET [GLOBAL|SESSION] optimizer_switch='command[,command]...';
    
    SET SESSION optimizer_switch = 'use_invisible_indexes=ON';
  • 在日常编写SQL中,使用变量来灵活的构建执行的SQL语句是比较常见的。就像在编程中使用变量、函数参数等是一样的道理。本文介绍使用JSON函数(JSON_ARRAYJSON_SEARCH)、以及 FIND_IN_SET 如何简单的实现带有数据组变量的SQL拼接与执行。使用该方法,则无需使用CONCAT函数拼接SQL,再使用PREPARE/EXECUTE语法执行,所以会更加简洁,可读性、扩展性更强。

    具体的,例如需要再查询结果中找到u_name在数组@u_list_j中的记录,那么使用JSON方式可以按照如下模式实现:

    SET @u_list_j = JSON_ARRAY('zhou','wu','zheng','wang','zhuge');
    
    SELECT 
        id,u_name 
    FROM 
        t_01 
    WHERE 
        JSON_SEARCH(@u_list_j,'one',t_01.u_name);

    另一种较为常见的方法是使用 FIND_IN_SET 函数。其使用方法如下:

    set @u_list_s = 'zhou,wu,zheng,wang,zhuge';
    
    SELECT 
        u_name 
    FROM 
        t_01 
    WHERE 
        FIND_IN_SET(t_01.u_name,@u_list_s);

    另外,还可以尝试使用 LOCATE 函数实现,只是使用该函数需要注意,在做字符串匹配的时候容易出现的重复、错误匹配问题。例如,数组中存在”zhuge”,那么匹配”zhu”,则可能匹配成功,但这并不是期望的结果。

    示例表:

    本文中使用的一些示例表如下:

    CREATE TABLE t_01(
      id int UNSIGNED AUTO_INCREMENT primary key,
      u_name varchar(32)
    );
    
    INSERT INTO t_01(u_name) values
      ('wu'),
      ('zhao'),
      ('qian'),
      ('sun'),
      ('li');