漫天花雨_个人页_阿里云开发者社区

发表了文章 2021-09-03

innodb表空间加密

有客户想用innodb的加密功能。只支持一张表一个表空间的加密；只支持AES (Advanced Encryption Standard)加密算法。InnoDB表空间加密表空间密钥加密采用ECB块加密方式，数据加密采用CBC块加密方式；alter table加密时只支持COPY算法，不支持INPLACE算法；不能move或copy加过密的表空间到非加密的表空间；空间加密只对数据文件有效，对redo，undo，binlog文件无效；不支持修改加密表空间的存储引擎

发表了文章 2020-11-10

阿里云数据库RDS MySQL的数据安全预防与恢复

事前预防事前预防大于事后处理，对数据库的管理来说，更是要防患于未然。您可以通过以下方式进行数据安全的事前预防：权限隔离环境隔离内核回收站设置实例备份规则使用数据库管理DMS 使用备份服务DBS 权限隔离您可以通过设置高权限账号和普通账号进行数据权限隔离。包括读写权限，DML权限，DDL权限，创建表，修改表，等，除了只读权限之外都需要进行管理和关注。权限说明见下图：可在数据库管理界面配置不同的权限。更多信息请参见创建数据库账号和授权服务账号帮助文档。环境隔离对环境的隔离也是同样重要，测试环境、开发环境、生产环境要分开，不要共用一套数据库。内核回收站第三个预防的重点是内核上面的回收站，当执行drop时，与表无关的对象会做保留，防止误删。MySQL 8.0的回收机制执行DROP TABLE/DATABASE语句时，只保留相关的表对象，并移动到专门的recycle bin目录中。其它对象的删除策略如下：如果是与表无关的对象，根据操作语句决定是否保留，不做回收。如果是表的附属对象，可能会修改表数据的，做删除处理，例如Trigger和Foreign key。但Column statistics不做清理，随表进入回收站。 MySQL 8.0的清理机制回收站会启动一个后台线程，来异步清理超过recycle_bin_retention时间的表对象。在清理回收站表的时候，如果遇到大表，会再启动一个后台线程异步删除大表。具体请参考帮助文档。设置实例备份规则还可以对数据进行备份来预防潜在的风险。实例备份规则为默认按照整实例进行备份和可以按照表进行备份。如下图所示：具体操作，参见帮助文档。使用数据库管理DMS进行安全预防在事前预防中，阿里云提供强大的数据库管理DMS服务和备份恢复DBS服务。DMS是一款强大的数据库管理和设计工具，直观的GUI开发环境让用户简单管理多达25种数据库，包括表结构设计、数据操作、数据开发及性能诊断优化等操作。DMS通过研发规范、研发流程、权限控制（可控制到列级别、行级别）、操作拦截、数据脱敏、流程审批、安全审计及变更回滚等功能有效保障数据安全。 DMS是一种新的工作模式：多种角色参与整个研发流程，包括管理员、DBA、安全管理员及开发、测试、产品、运营、财务等。可消除集中管控的人员瓶颈、减少沟通成本、规范化管理减少故障误操作、变更周期可控、业务迭代速度快、提升研发效率。对接企业账号系统，数据安全解决方案-从“审计”到“拦截”。可控的SQL操作包含：全局权限管控、风险识别、数据脱敏、操作审计、安全规则引擎。可进行灵活的安全规则设置：变更稳定-覆盖数据变更、结构变更、代码发布的全流程把控。尤其是对于大批量的数据变更，大表结构变更等，会有针对性的流程把控和精细化的管理，在不影响业务的情况下，提升整体的安全性。使用阿里云备份服务DBS 备份服务DBS为数据库提供连续数据保护、低成本的备份服务。它可以为多种环境的数据提供强有力的保护，包括企业数据中心、其他云厂商、混合云及公共云。RDS备份的数据是可见的，如果有数据分析的需求还可以用云原生数据湖进行支持，整个产品生态是打通和健全的。产品功能：可恢复任1秒数据实例、单库、多表、单表，备份及恢复粒度自由选择，RTO大幅降低，备份有效性实时验证。安全加密无论传输，还是存储使用最严苛的加密技术，保护用户数据安全。低成本，免运维低成本异地备份，让企业快速满足三级等保要求，无需为灾备提供前期成本，配置到运行只需几分钟。多环境支持支持自建IDC、云上自建数据库、其他云环境的数据库备份。备份服务DBS与RDS自带备份的区别：针对RDS数据库，DBS提供转储备份和逻辑备份，满足RDS客户的异地备份和灵活备份诉求。针对RDS数据库，RDS提供物理备份，满足RDS客户的本地备份和快速恢复诉求。 DBS控制台入口：详细内容请参见帮助文档。事中谨慎即使非常谨慎，数据库在现场运行时是不可能不对数据进行查询、修改和删除的，我们不能因噎废食，仍然要进行这些看似有风险的操作，但是我们可以采取以下措施，让整个数据库的使用过程更加安全。使用内核回收站操作隔离操作备份使用内核回收站如果要使用DROP语句，可以在操作之前可以先打开回收站，回收站是可以进行清理和查询的。回收站会有id与原表相对应，并会对过程进行记录。以下示例是操作以MySQL 8.0为例进行说明： SET SESSION RECYCLE_BIN=ON;--------------------打开会话级回收站 CALL DBMS_RECYCLE.SHOW_TABLES();--------------看回收站里的表 DROP TABLE tablename;--------------------------------------------------删除表 CALL DBMS_RECYCLE.PURGE_TABLE (‘<TABLE>’);-----清理回收站里的表表示例： mysql> call dbms_recycle.show_tables(); +-----------------+---------------+---------------+--------------+---------------------+---------------------+ | SCHEMA | TABLE | ORIGIN_SCHEMA | ORIGIN_TABLE | RECYCLED_TIME | PURGE_TIME | +-----------------+---------------+---------------+--------------+---------------------+---------------------+ | __recycle_bin__ | __innodb_1063 | product_db | t1 | 2019-08-08 11:01:46 | 2019-08-15 11:01:46 | | __recycle_bin__ | __innodb_1064 | product_db | t2 | 2019-08-08 11:01:46 | 2019-08-15 11:01:46 | | __recycle_bin__ | __innodb_1065 | product_db | parent | 2019-08-08 11:01:46 | 2019-08-15 11:01:46 | | __recycle_bin__ | __innodb_1066 | product_db | child | 2019-08-08 11:01:46 | 2019-08-15 11:01:46 | +-----------------+---------------+---------------+--------------+---------------------+---------------------+ 4 rows in set (0.00 sec) 更多信息请参见帮助文档。操作隔离如果我们要执行DML类的操作，在执行变更的时候最好走一下变更的流程。如果缺少这样流程，大批量的操作语句最好有多人review。注意：1、业务相关的操作：一定带WHERE条件语句，把执行变更的范围变到最小。2、运维/后台相关操作注意读写分离针对数据统计分析类/定时任务类/大批量操作类尽量避免业务高峰多人Review保证正确性操作备份最好在每次操作执行之前，都把数据备份一遍，这样即使真正出现问题，也可以执行回滚。备份方法每次执行DML/DDL之前，导出执行前的SQL，作为回滚脚本。运维/后台相关操作立即执行默认开启为“是”，提交即刻执行；可按需指定在业务特定时间执行。事务控制默认不开启为“否”，逐条提交，遇到失败则终止但不回滚；开启该功能遇到失败则全部回滚（仅限DML，DDL不在此范围内）。备份数据默认开启为“是”，针对update、delete操作进行将要影响数据的全记录行，insert脚本生成附件。帮助文档事后恢复及时我们非常谨慎，也不能百分百避免问题的发生，如果真的发生问题了，我们可以通过以下操作来进行事后恢复。 DMS工单操作历史。 DMS数据追踪。内核回收站。 RDS克隆实例。 RDS库/表恢复。 DBS恢复。 DMS工单操作历史如果您使用了阿里云数据管理DMS平台，可以使用DMS查看工单操作历史。使用场景 MySQL 5.5/5.6/5.7/8.0DELETE/UPDATE/INSERT需要管控的数据库实例开通了DMS安全协同管控模式恢复方法对于变更执行后出现异常不符合诉求需要回滚的，可以直接在工单页面内，选择右侧>工单操作历史>查看备份，找到备份文件。下载备份脚本下载，做相应处理。确认数据满足需求后，重新提交变更工单。更多内容请参见帮助文档。 DMS数据追踪如果您不是通过DMS进行的变更，也可以通过DMS进行数据的追踪，逆向解析Binlog，本地Binlog和本地清理后到OSS上的Binlog都可以追踪。（前提在binlog仍然存在，一般云数据库Binlog保留周期为7天，如需更长时间可在RDS控制台按需调整）使用场景 MySQL 5.5/5.6/5.7/8.0 DELETE/UPDATE/INSERT 少量功能在线搜索日志内容，无需手工下载Binlog。支持数据的插入/更新/删除日志搜索，无需手工解析Binlog。支持逐条数据恢复，无需手工生成回滚语句。支持的Binlog OSS Binlog（RDS会定时将Binlog备份到OSS上）。本地热Binlog（数据库服务器上Binlog）。操作示例如下图所示：更多内容请参见帮助文档。示例—少量数据追踪操作示例如下图所示：更多内容请参见帮助文档。从回收站恢复内核支持从回收站进行恢复，我们只需要确认回收站中有我们想要的表。使用场景 MySQL 8.0 DROP TABLE/SCHEMA 其它对象的删除策略是：与表无关的对象，比如 procedure，根据操作语句决定是否保留，不做回收。表的附属对象，比如 trigger，Foreign key，column statistics等，只要存在可能修改表数据的，做删除，比如 trigger，Foreign key。但columns statistics不做清理，随表进入回收站。找回方法查看回收站表 CALL DBMS_RECYCLE.SHOW_TABLES();--------------看回收站里的表。找回方法 CREATE TABLE SRC_SCHEMA.SRC_TABLE LIKE SCHEMA.TABLE;-------------------- 加粗部分是要替换的内容。 INSERT INTO SRC_SCHEMA.SRC_TABLE SELECT * FROM SCHEMA.TABLE; ----- 加粗部分是要替换的内容。示例—从回收站恢复T1的数据 mysql> call dbms_recycle.show_tables(); +-----------------+---------------+---------------+--------------+---------------------+---------------------+ | SCHEMA | TABLE | ORIGIN_SCHEMA | ORIGIN_TABLE | RECYCLED_TIME | PURGE_TIME | +-----------------+---------------+---------------+--------------+---------------------+---------------------+ | __recycle_bin__ | __innodb_1063 | product_db | t1 | 2019-08-08 11:01:46 | 2019-08-15 11:01:46 | | __recycle_bin__ | __innodb_1064 | product_db | t2 | 2019-08-08 11:01:46 | 2019-08-15 11:01:46 | | __recycle_bin__ | __innodb_1065 | product_db | parent | 2019-08-08 11:01:46 | 2019-08-15 11:01:46 | | __recycle_bin__ | __innodb_1066 | product_db | child | 2019-08-08 11:01:46 | 2019-08-15 11:01:46 | +-----------------+---------------+---------------+--------------+---------------------+---------------------+ 4 rows in set (0.00 sec) mysql>create table product_db.t1 like __recycle_bin__. __innodb_1063; mysql>insert into product_db.t1 select * from __recycle_bin__. __innodb_1063; 克隆实例如果您还没有使用DMS，也可以用控制台上实例的备份来恢复，克隆示例支持按照时间点或备份集恢复，最安全的方式建议恢复到一个新实例，不在原实例原地恢复。使用场景通过备份克隆出整个实例。所有误操作(DDL/DML)。还原方式指定时间点。指定备份集。操作步骤恢复到一个新实例。验证数据准确性。将数据迁回原实例。操作示例如下图所示：更多内容请参见帮助文档。库/表级别恢复如果您之前设置了库/表级别备份的话，您的误操作如果只是一个库或者表，那么您可以进行库/表级别恢复，但是库/表级别恢复有很多限制。使用场景通过备份指定恢复误删的数据库或表。所有误操作(DDL/DML)。操作示例如下图所示：限制事项必须打开库/表级别备份。每次最多选择50个库/表。运行中且没有被锁定。如果要按时间点进行恢复，需要确保日志备份已开启。若要按备份集恢复，则原实例必须至少有一个备份集。更多内容请参见帮助文档。从DBS恢复如果您之前选择了DBS备份，您就可以通过DBS进行事后恢复。使用场景支持整个实例、多个数据库、单个数据库、多张表或一张表恢复。支持秒级任意时间点恢复，并且用户可以灵活选择恢复对象。操作示例如下图所示：可以进行选择恢复操作的时间点等操作配置，操作示例如下图所示：

发表了文章 2019-01-30

子查询中有个不存在的列居然不报错是bug吗？

问题描述有开发问我这样一个问题： mysql> select * from aaa; +----+---------------------+----------+---------------------+ | id | dt | name | dtt | +----+---------------------+----------+---------------------+ | 1 | 2019-01-14 18:15:39 | aaaaaaaa | 2019-01-30 17:14:08 | +----+---------------------+----------+---------------------+ 1 row in set (0.00 sec) mysql> select * from bbb; +----+---------------------+----------+ | id | dt | name | +----+---------------------+----------+ | 1 | 2019-01-14 18:19:19 | aaaaaaaa | | 2 | 2019-01-14 18:20:49 | aaaaaaaa | +----+---------------------+----------+ 2 rows in set (0.00 sec) mysql> select * from aaa where id in (select id from bbb where dtt<now()); +----+---------------------+----------+---------------------+ | id | dt | name | dtt | +----+---------------------+----------+---------------------+ | 1 | 2019-01-14 18:15:39 | aaaaaaaa | 2019-01-30 17:14:08 | +----+---------------------+----------+---------------------+ 1 row in set (0.00 sec) 上面内容里dtt这个字段在bbb表中并不存在，但是在外表中存在，但是为什么不报错反而查出来结果了呢？原因我请PostgreSQL的同事也做了同样的操作，也是不报错的；这个在SQLServer和Oracle里面同样成立，why？一般规则是，语句中的列名由FROM子句中引用的表隐式地限定在同一级别。如果子查询的FROM子句中引用的表中不存在列，则由外部查询的FROM子句中引用的表隐式地限定列。即：在块结构语言计算子查询时，它开始在本地查找以解析列名。如果失败，则转到外部范围，直到找到具有该名称的列或失败为止。风险如果有开发这样执行delete语句，可能会导致全表被误删！ mysql> delete from aaa where id in (select id from bbb where dtt<now()); 正确又安全的写法带上表名 mysql> select aaa.* from aaa where aaa.id in (select bbb.id from bbb where bbb.dtt<now()); mysql> ERROR 1054 (42S22): Unknown column 'bbb.dtt' in 'where clause' 参考 https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms178050(v=sql.105) https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4cfebb2e-6fdf-4ebf-9a6f-ee1911615eba/query-returning-wrong-data-even-inner-subquery-has-syntax-error?forum=transactsql

发表了文章 2019-01-15

timestamp类型在not null时可以插入null值？

背景有同学问在RDS MySQL 5.6在timestamp 设置为 not null 并且SQL模式是严格模式时，仍然可以插入空值，理论上应该有报错，是不是RDS的bug？环境 MySQL 5.6 5.7 8.0，在RDS和自建数据库上都会遇到现象从上图中可以看到，在timestamp设置为not null时会自动补齐: DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 在客户的自建环境中timestamp类型没有自动补齐,在严格模式下会报错；在RDS MySQL的环境中datetime类型没有自动补齐,,在严格模式下会报错：溯源 If the explicit_defaults_for_timestamp system variable is disabled, TIMESTAMP columns by default are NOT NULL, cannot contain NULL values, and assigning NULL assigns the current timestamp. To permit a TIMESTAMP column to contain NULL, explicitly declare it with the NULL attribute. In this case, the default value also becomes NULL unless overridden with a DEFAULT clause that specifies a different default value. DEFAULT NULL can be used to explicitly specify NULL as the default value. (For a TIMESTAMP column not declared with the NULL attribute, DEFAULT NULL is invalid.) If a TIMESTAMP column permits NULL values, assigning NULL sets it to NULL, not to the current timestamp.在explicit_defaults_for_timestamp值为OFF时，会自动补齐上文中的内容，而客户的环境里面explicit_defaults_for_timestamp是ON的，所以跟RDS MySQL结果有差异，跟客户说过后，验证就是explicit_defaults_for_timestamp值差异导致。结论每一件不合理的事情背后，必然有一个自己还未了解的合理原因.

发表了文章 2019-01-10

如何定位RDS MySQL内存使用率高？

打开performance_schema 控制台可开打开内存监控 update performance_schema.setup_instruments set enabled = 'yes' where name like 'memory%'; 查看监控 select * from sys.x$memory_by_host_by_current_bytes ; select * from sys.x$memory_by_thread_by_current_bytes ; select * from sys.x$memory_by_user_by_current_bytes ; select * from sys.x$memory_global_by_current_bytes ; select * from sys.x$memory_global_total ; event_name内存事件名称。 current_count事件发生的总次数。 current_alloc事件尚未释放的已分配字节的当前数目。 current_avg_alloc为事件的每个内存块分配的当前字节数。 high_count分配给事件的内存块数量的最高标记。 high_alloc为事件分配的字节数的高水位标记。 high_avg_alloc为事件分配的每个内存块的平均字节数的最高标记。参考 http://www.innomysql.com/mysql-5-7-oom%E9%97%AE%E9%A2%98%E8%AF%8A%E6%96%AD-%E5%B0%B1%E6%98%AF%E8%BF%99%E4%B9%88%E7%AE%80%E5%8D%95/ https://dev.mysql.com/doc/refman/5.7/en/sys-memory-global-by-current-bytes.html https://www.percona.com/blog/2018/06/28/what-to-do-when-mysql-runs-out-of-memory-troubleshooting-guide/

发表了文章 2018-12-29

MySQL的活跃连接数和连接数

前言很多用户经常会问，我购买的连接是2000，怎么活跃了连接到了500就报警啊？就响应慢了啊？通过命令我们来看下关于连接数有多少种： mysql> show global variables like "max_connections"; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 1800 | +-----------------+-------+ mysql> show global status like "%Threads%"; +------------------------------------------------+-------+ | Variable_name | Value | +------------------------------------------------+-------+ | Threads_cached | 3 | | Threads_connected | 0 | | Threads_created | 7 | | Threads_rejected | 0 | | Threads_running | 2 | +------------------------------------------------+-------+ 11 rows in set (0.00 sec) 总连接数客户购买的DB连接数是这个。max_connections，允许同时连接DB的客户端的最大线程数。如果客户端的连接数超过了max_connections,应用就会收到“too many connections”的错误。下面是线程的状态信息：已经创建的连接数 Threads_created是为处理连接而创建的线程数。再明确一点来说是连接到DB的，客户端的线程数。它包含Threads_running。如果Threads_created很大，可能需要调整thread_cache_size。线程cache命中率=Threads_created/Connections，cache命中率当然越大越好，如果命中率较低，可以考虑增加thread_cache_size。已经连接的连接数 Thread_connected当前打开的连接数。活跃连接数 Threads_running官方的说法是“没有sleep的线程数”。顾名思义是：在DB端正在执行的客户端线程总数。Server端保持这些连接同时客户端等待回复。有些线程可能消耗CPU或者IO，有些线程可能啥也没做单纯等表锁或行锁释放。当DB执行完这个线程，客户端收到回复，线程的状态就会从"running" 变成 "connected". 如果发现活跃链接数突然增高，通常是以下原因：应用缓存失效突发流量参考 https://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html#statvar_Threads_running https://dba.stackexchange.com/questions/176679/what-is-the-difference-between-threads-connected-and-threads-running

发表了文章 2018-12-25

RDS MySQL 5.7 4核和8核共享规格性能压测

写在前面这次压测只用了一台ECS，也没有调整各种压测比和压测参数，把实例压到最高；这里的压测只是为了说明不同的参数对性能影响，要想得出一个结论，必须要在可对比的情况下，比如：实例规格一样，从压测机到被压测机网络延迟一样，MySQL的配置文件一样等等，否则都是耍流氓。压测环境被压测的RDS实例规格 MySQL 5.7 物理机版新规格8核32G MySQL 5.7 物理机版新规格4核16G RDS主要参数配置 MySQL [(none)]> SHOW global VARIABLES WHERE Variable_name in ("sync_binlog","innodb_flush_log_at_trx_commit","rpl_semi_sync_slave_enabled","rpl_semi_sync_master_enabled","query_cache_type","have_query_cache"); +--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | have_query_cache | YES | | innodb_flush_log_at_trx_commit | 1|2 | | query_cache_type | OFF | | rpl_semi_sync_master_enabled | ON|OFF | | rpl_semi_sync_slave_enabled | OFF | | sync_binlog | 1|1000 | +--------------------------------+-------+ 关于参数 innodb_flush_log_at_trx_commit的作用可以参考：MySQL · 参数故事 · innodb_flush_log_at_trx_commitsync_binlog的作用可以参考：MySQL · 答疑解惑 · binlog 位点刷新策略MySQL · 引擎特性 · InnoDB 事务子系统介绍半同步的作用可以参考：MySQL · 源码分析 · MySQL BINLOG半同步复制数据安全性分析MySQL · 源码分析 · MySQL 半同步复制数据一致性分析网络延迟从压测机ping RDS 64 bytes from *** (***): icmp_seq=1 ttl=64 time=1.69 ms 64 bytes from *** (***): icmp_seq=2 ttl=64 time=1.72 ms 64 bytes from *** (***): icmp_seq=3 ttl=64 time=1.72 ms 64 bytes from *** (***): icmp_seq=4 ttl=64 time=1.73 ms 64 bytes from *** (***): icmp_seq=5 ttl=64 time=1.73 ms 64 bytes from *** (***): icmp_seq=6 ttl=64 time=1.72 ms 64 bytes from *** (***): icmp_seq=7 ttl=64 time=1.73 ms 64 bytes from *** (***): icmp_seq=8 ttl=64 time=1.73 ms 64 bytes from *** (***): icmp_seq=9 ttl=64 time=1.72 ms 压测方法 sysbench /usr/share/sysbench/*** \ --mysql-host=*** \ --mysql-port=3306 \ --mysql-user=*** \ --mysql-password='***' \ --mysql-db=*** \ --db-driver=mysql \ --tables=10 \ --table-size=1000000 \ --report-interval=10 \ --threads=128 \ --time=120 prepare/run/cleanup 压测结果 8核32G 读写场景双1 MySQL [(none)]> SHOW global VARIABLES WHERE Variable_name in ("sync_binlog","innodb_flush_log_at_trx_commit","rpl_semi_sync_slave_enabled","rpl_semi_sync_master_enabled","query_cache_type","have_query_cache"); +--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | have_query_cache | YES | | innodb_flush_log_at_trx_commit | 1 | | query_cache_type | OFF | | rpl_semi_sync_master_enabled | ON | | rpl_semi_sync_slave_enabled | OFF | | sync_binlog | 1 | +--------------------------------+-------+ 6 rows in set (0.00 sec) oltp_read_write.lua SQL statistics: transactions: 262843 (2187.92 per sec.) queries: 5256860 (43758.44 per sec.) General statistics: total time: 120.1320s total number of events: 262843 Latency (ms): min: 20.91 avg: 58.45 max: 339.70 95th percentile: 86.00 sum: 15361894.55 # mysqlslap -a --concurrency=128 --number-of-queries 10000 --iterations=5 --engine=innodb --debug-info -u*** -p*** -h*** -P3306 --create-schema=*** Benchmark Running for engine innodb Average number of seconds to run all queries: 11.504 seconds Minimum number of seconds to run all queries: 9.150 seconds Maximum number of seconds to run all queries: 16.737 seconds Number of clients running queries: 128 Average number of queries per client: 78 sync_binlog:1000和trx_commit=2 MySQL [(none)]> SHOW global VARIABLES WHERE Variable_name in ("sync_binlog","innodb_flush_log_at_trx_commit","rpl_semi_sync_slave_enabled","rpl_semi_sync_master_enabled","query_cache_type","have_query_cache"); +--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | have_query_cache | YES | | innodb_flush_log_at_trx_commit | 2 | | query_cache_type | OFF | | rpl_semi_sync_master_enabled | OFF | | rpl_semi_sync_slave_enabled | OFF | | sync_binlog | 1000 | +--------------------------------+-------+ 6 rows in set (0.00 sec) Threads started! #oltp_read_write.lua SQL statistics: queries performed: read: 3809666 write: 1088476 other: 544238 total: 5442380 transactions: 272119 (2266.76 per sec.) queries: 5442380 (45335.11 per sec.) General statistics: total time: 120.0462s total number of events: 272119 Latency (ms): min: 20.08 avg: 56.45 max: 339.44 95th percentile: 84.47 sum: 15361371.70 # mysqlslap -a --concurrency=128 --number-of-queries 10000 --iterations=5 --engine=innodb --debug-info -u*** -p*** -h*** -P*** --create-schema=*** Benchmark Running for engine innodb Average number of seconds to run all queries: 9.238 seconds Minimum number of seconds to run all queries: 8.394 seconds Maximum number of seconds to run all queries: 9.624 seconds Number of clients running queries: 128 Average number of queries per client: 78 只读场景 #oltp_point_select.lua SQL statistics: queries performed: read: 11432749 write: 0 other: 0 total: 11432749 transactions: 11432749 (95255.06 per sec.) queries: 11432749 (95255.06 per sec.) General statistics: total time: 120.0208s total number of events: 11432749 Latency (ms): min: 0.90 avg: 1.34 max: 18.23 95th percentile: 1.79 sum: 15351945.95 4核16G读写场景双1 MySQL [(none)]> SHOW global VARIABLES WHERE Variable_name in ("sync_binlog","innodb_flush_log_at_trx_commit","rpl_semi_sync_slave_enabled","rpl_semi_sync_master_enabled","query_cache_type","have_query_cache"); +--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | have_query_cache | YES | | innodb_flush_log_at_trx_commit | 1 | | query_cache_type | OFF | | rpl_semi_sync_master_enabled | ON | | rpl_semi_sync_slave_enabled | OFF | | sync_binlog | 1 | +--------------------------------+-------+ oltp_read_write.lua SQL statistics: queries performed: read: 1643950 write: 469700 other: 234850 total: 2348500 transactions: 117425 (977.46 per sec.) queries: 2348500 (19549.17 per sec.) General statistics: total time: 120.1314s total number of events: 117425 Latency (ms): min: 28.19 avg: 130.88 max: 446.28 95th percentile: 155.80 sum: 15368416.16 # mysqlslap -a --concurrency=128 --number-of-queries 10000 --iterations=5 --engine=innodb --debug-info -u*** -p*** -h*** -P3306 --create-schema=*** Benchmark Running for engine innodb Average number of seconds to run all queries: 12.546 seconds Minimum number of seconds to run all queries: 12.445 seconds Maximum number of seconds to run all queries: 12.657 seconds Number of clients running queries: 128 Average number of queries per client: 78 sync_binlog:1000和trx_commit=2 mysql> SHOW global VARIABLES WHERE Variable_name in ("sync_binlog","innodb_flush_log_at_trx_commit","rpl_semi_sync_slave_enabled","rpl_semi_sync_master_enabled","query_cache_type","have_query_cache"); +--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | have_query_cache | YES | | innodb_flush_log_at_trx_commit | 2 | | query_cache_type | OFF | | rpl_semi_sync_master_enabled | OFF | | rpl_semi_sync_slave_enabled | OFF | | sync_binlog | 1000 | +--------------------------------+-------+ oltp_read_write.lua SQL statistics: queries performed: read: 1650866 write: 471676 other: 235838 total: 2358380 transactions: 117919 (981.66 per sec.) queries: 2358380 (19633.27 per sec.) General statistics: total time: 120.1198s total number of events: 117919 Latency (ms): min: 21.31 avg: 130.32 max: 439.36 95th percentile: 155.80 sum: 15366638.87 只读场景 oltp_point_select.lua SQL statistics: queries performed: read: 4924841 write: 0 other: 0 total: 4924841 transactions: 4924841 (41031.29 per sec.) queries: 4924841 (41031.29 per sec.) General statistics: total time: 120.0248s total number of events: 4924841 Latency (ms): min: 0.90 avg: 3.12 max: 65.22 95th percentile: 28.16 sum: 15356974.94 性能对比图

发表了文章 2018-11-16

分页语句的优化

前言经常会碰到分页语句的优化，今天在遍历网页的时候又碰到了，想想还是写篇文章，以后碰到类似的问题就可以拿这篇文章回复了。举个例子 SELECT B FROM hugeTable ORDER BY A LIMIT 10000 OFFSET 500000 需要在ON hugetable (A,B)建索引，同时修改语句 SELECT i.B, k.A FROM ( SELECT j.A FROM hugeTable j WHERE j.A > $value_of_A_from_row_520000 LIMIT 10000 ) k JOIN hugetable i ON i.A = k.A ORDER BY k.A 参考： https://stackoverflow.com/questions/17812794/why-does-mysql-innodb-creating-sort-index-when-unique-index-exists

发表了文章 2018-11-14

如何查看MySQL的表空间

常规方法通常情况下，我们一般的思路是通过下面的方式获取表空间： SELECT CONCAT( table_schema, '.', table_name ) table_name, CONCAT( ROUND( data_length / ( 1024 *1024 ) , 2 ) , 'M' ) data_length, CONCAT( ROUND( index_length / ( 1024 *1024 ) , 2 ) , 'M' ) index_length, CONCAT( ROUND( data_free / ( 1024 *1024 ) , 2 ) , 'M' ) free_length, CONCAT( ROUND( ROUND( data_length + index_length + data_free ) / ( 1024 *1024 ) , 2 ) , 'M' ) total_size, CONCAT( ROUND( ROUND( data_free ) / ( 1024 *1024 ) , 2 ) , 'M' ) total_wasted, (100*(data_free/(data_length + index_length + data_free))) percent_wasted FROM information_schema.TABLES WHERE table_schema = '[NAME_OF_DPA_DATABASE]' AND (100*(data_free/(data_length + index_length+data_free))) > [MINIMUM_PERCENT_WASTED] AND (data_length + index_length + data_free) > [MINIMUM_FILESIZE_IN_BYTES] ORDER BY (data_length + index_length + data_free) DESC; 例如： SELECT CONCAT( table_schema, '.', table_name ) table_name, CONCAT( ROUND( data_length / ( 1024 *1024 ) , 2 ) , 'M' ) data_length, CONCAT( ROUND( index_length / ( 1024 *1024 ) , 2 ) , 'M' ) index_length, CONCAT( ROUND( data_free / ( 1024 *1024 ) , 2 ) , 'M' ) free_length, CONCAT( ROUND( ROUND( data_length + index_length + data_free ) / ( 1024 *1024 ) , 2 ) , 'M' ) total_size, CONCAT( ROUND( ROUND( data_free ) / ( 1024 *1024 ) , 2 ) , 'M' ) total_wasted, (100*(data_free/(data_length + index_length + data_free))) percent_wasted FROM information_schema.TABLES WHERE table_schema = 'dpa_repo' AND (100*(data_free/(data_length + index_length+data_free))) > 75 AND (data_length + index_length + data_free) > 52428800 ORDER BY (data_length + index_length + data_free) DESC; 可能没有注意到的是对于上述方法中的data_length 或 index_length的值MySQL并不是实时更新的，而是周期性地维护，通过测试发现当10%的行被改变时，data_length 或 index_length与正在更新的统计数据一致。而table_rows, data_free 或 update_time却是实时更新的。那么有没有方法在我们查询information_schema时获取到data_length和 index_length的值呢？在MySQL 5.7里面如果想获取information_schema精确值，就要disable innodb_stats_persistent 和 enable innodb_stats_on_metadata,这两种方法都有明显的副作用。disable innodb_stats_persistent意味着每次在MySQL启动时才会刷新统计信息，这中代价非常昂贵并且会在重启之间生成易失性查询计划。enabling innodb_stats_on_metadata会使每次访问information_schema的时候非常非常慢。有没有更好的办法可以查看information.INNODB_SYS_TABLESPACES查看真实的文件大小，不像index_length and data_length，INNODB_SYS_TABLESPACES的值是实时更新的，也不需要额外的配置： mysql> select * from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES where name='sbinnodb/sbtest1' G *************************** 1. row *************************** SPACE: 42 NAME: sbinnodb/sbtest1 FLAG: 33 FILE_FORMAT: Barracuda ROW_FORMAT: Dynamic PAGE_SIZE: 16384 ZIP_PAGE_SIZE: 0 SPACE_TYPE: Single FS_BLOCK_SIZE: 4096 FILE_SIZE: 245937209344 ALLOCATED_SIZE: 245937266688 1 row in set (0.00 sec) 用这个表还有一个更好的事情是：它可以处理MySQL 5.7 新的“Innodb Page Compression”，这不同于file_size（它是在磁盘上的逻辑文件的大小），也不同于allocated_size（它是文件已经分配的空间，可以小很多） mysql> select * from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES where name='sbinnodb/testcomp' G *************************** 1. row *************************** SPACE: 48 NAME: sbinnodb/testcomp FLAG: 33 FILE_FORMAT: Barracuda ROW_FORMAT: Dynamic PAGE_SIZE: 16384 ZIP_PAGE_SIZE: 0 SPACE_TYPE: Single FS_BLOCK_SIZE: 4096 FILE_SIZE: 285212672 ALLOCATED_SIZE: 113004544 1 row in set (0.00 sec) 结论查INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES获取INNODB表的真实文件大小。参考 https://www.percona.com/blog/2016/01/26/finding_mysql_table_size_on_disk/ https://support.solarwinds.com/Success_Center/Database_Performance_Analyzer_(DPA)/Knowledgebase_Articles/MySQL_repository_table_optimization_for_DPA https://dev.mysql.com/doc/refman/5.7/en/innodb-persistent-stats.html

发表了文章 2018-10-29

innodb_sort_buffer_size手册

说明在创建InnoDB索引时用于指定对数据排序的排序缓冲区的大小。利用这块内存把数据读进来进行内部排序然后写入磁盘。这个参数只会在创建索引的过程中被使用，不会用在后面的维护操作；在索引创建完毕后innodb_sort_buffer会被释放。这个值也控制了在执行online DDL期间DML产生的临时日志文件。默认 1048576 bytes (1MB).对于一个典型的排序操作来说，可以用下面的公式评估它消耗的内存: (6 /*FTS_NUM_AUX_INDEX*/ * (3*@@GLOBAL.innodb_sort_buffer_size) + 2 * number_of_partitions * number_of_secondary_indexes_created * (@@GLOBAL.innodb_sort_buffer_size/dict_index_get_min_size(index)*/) * 8 /*64-bit sizeof *buf->tuples*/") @@GLOBAL.innodb_sort_buffer_size/dict_index_get_min_size(index) indicates the maximum tuples held. 2 (@@GLOBAL.innodb_sort_buffer_size/dict_index_get_min_size(index)/) 8 /64-bit size of buf->tuples*/ indicates auxiliary pointers allocated. 对于在全文索引上的并行排序，需要再*innodb_ft_sort_pll_degree: (6 /*FTS_NUM_AUX_INDEX*/ * @@GLOBAL.innodb_ft_sort_pll_degree) 参考： https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_sort_buffer_size

发表了文章 2018-10-10

查询分区表的语句

select * from information_schema.PARTITIONS where TABLE_SCHEMA not in ('performance_schema','mysql') and SUBPARTITION_EXPRESSION is not null group by table_name

发表了文章 2018-10-09

常用MySQL排查网站

官方手册：https://www.mysql.com/ 查是否有bug：https://dev.mysql.com/worklog/

发表了文章 2018-10-09

ERROR 2020 (HY000): Got packet bigger than 'max_allowed_packet' bytes

今天在测试环境发生了貌似怪异的报错，表结构如下： CREATE TABLE `test` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `step` bigint(20) NOT NULL COMMENT 'step', `data` longtext COMMENT 'step data', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1778606 DEFAULT CHARSET=utf8 COMMENT='test' 要查询一条数据 select char_length(data) from test where id=1; 结果却是： mysql> ERROR 2020 (HY000): Got packet bigger than 'max_allowed_packet' bytes 而实际情况是： mysql> select length(data),char_length(data) from test where id=1; +--------------+-------------------+ | length(data) | char_length(data) | +--------------+-------------------+ | 22270339 | 22270339 | +--------------+-------------------+ 1 row in set (0.12 sec) 为什么已经把max_allowed_packet设置成最大了，还是报ERROR 2020 (HY000)错啊？ mysql> show global variables like "max_allowed_packet"; +--------------------+------------+ | Variable_name | Value | +--------------------+------------+ | max_allowed_packet | 1073741824 | +--------------------+------------+ 1 row in set (0.00 sec) 即使里面存的中文utf8占3个字节，22270339*3<1073741824啊，忽然灵机一动，看了 mysql> show global variables like "innodb_buffer_pool_size"; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | innodb_buffer_pool_size | 880803840 | +-------------------------+-----------+ 1 row in set (0.00 sec) mysql> select 880803840/(1024*1024*1024.0); +------------------------------+ | 880803840/(1024*1024*1024.0) | +------------------------------+ | 0.8203 | +------------------------------+ 1 row in set (0.00 sec) max_allowed_packet已经大于innodb_buffer_pool_size了，怀疑是规格太小导致，等升级下实例规格看看

发表了文章 2018-10-09

理解innodb buffer pool

前言 innodb buffer pool有几个目的：缓存数据--众所周知，这个占了buffer pool的大半空间缓存目录--数据字典 insert buffer 排序的内部结构--比如自适应hash的结构或者一些行锁 1.查看表的数据和索引使用情况? SELECT engine, count(*) as TABLES, concat(round(sum(table_rows)/1000000,2),'M') rows, concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA, concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx, concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size, round(sum(index_length)/sum(data_length),2) idxfrac FROM information_schema.TABLES WHERE table_schema not in ('mysql', 'performance_schema', 'information_schema','test') GROUP BY engine ORDER BY sum(data_length+index_length) DESC LIMIT 10; 得到的结果: +--------+--------+----------+---------+--------+------------+---------+ | engine | TABLES | rows | DATA | idx | total_size | idxfrac | +--------+--------+----------+---------+--------+------------+---------+ | InnoDB | 71608 | 1644.51M | 130.79G | 82.76G | 213.55G | 0.63 | +--------+--------+----------+---------+--------+------------+---------+ idxfrac这个值越低越好，举个例子，表里只有一个唯一索引的数据如下： +--------+--------+----------+---------+--------+------------+---------+ | engine | TABLES | rows | DATA | idx | total_size | idxfrac | +--------+--------+----------+---------+--------+------------+---------+ | InnoDB | 16 | 3120.61M | 386.59G | 58.09G | 444.68G | 0.15 | +--------+--------+----------+---------+--------+------------+---------+ 可见idxfrac可见这个值越低越好。 2.获取buffer pool占的page个数： select count(*) from information_schema.innodb_buffer_page; 结果： +----------+ | count(*) | +----------+ | 262142 | +----------+ 聪明的同学自己算下使用的buffer pool是多大吧。 3.获取page类型: select page_type as Page_Type,sum(data_size)/1024/1024 as Size_in_MB from information_schema.innodb_buffer_page group by page_type order by Size_in_MB desc; 结果： +-------------------+--------------+ | Page_Type | Size_in_MB | +-------------------+--------------+ | INDEX | 158.66378689 | | UNKNOWN | 0.00000000 | | TRX_SYSTEM | 0.00000000 | | SYSTEM | 0.00000000 | | FILE_SPACE_HEADER | 0.00000000 | | IBUF_BITMAP | 0.00000000 | | EXTENT_DESCRIPTOR | 0.00000000 | | ALLOCATED | 0.00000000 | | INODE | 0.00000000 | | BLOB | 0.00000000 | | UNDO_LOG | 0.00000000 | | IBUF_FREE_LIST | 0.00000000 | | IBUF_INDEX | 0.00000000 | +-------------------+--------------+ 从这里可以看到数据和索引占了buffer pool的大部分空间。也可以看出来这里有几种重要的页类型： INDEX: B-Tree index IBUF_INDEX: Insert buffer index UNKNOWN: not allocated / unknown state TRX_SYSTEM: transaction system data 眼亮的同学可能会问，你上面不是说会缓存数据吗？怎么这里出来只有INDEX类型占多半buffer pool?数据哪里去了？数据在INDEX里！！！数据在聚簇索引的叶子节点上。 4.buffer pool里每个索引的使用 select table_name as Table_Name, index_name as Index_Name,count(*) as Page_Count, sum(data_size)/1024/1024 as Size_in_MB from information_schema.innodb_buffer_page group by table_name, index_name order by Size_in_MB desc; 结果： +--------------------------------------------+-----------------+------------+-------------+ | Table_Name | Index_Name | Page_Count | Size_in_MB | +--------------------------------------------+-----------------+------------+-------------+ | `magento`.`core_url_rewrite` | PRIMARY | 2829 | 40.64266014 | | `magento`.`core_url_rewrite` | FK_CORE_URL_... | 680 | 6.67517281 | | `magento`.`catalog_product_entity_varchar` | PRIMARY | 449 | 6.41064930 | | `magento`.`catalog_product_index_price` | PRIMARY | 440 | 6.29357910 | | `magento`.`catalog_product_entity` | PRIMARY | 435 | 6.23898315 | +--------------------------------------------+-----------------+------------+-------------+ 5.一个典型的buffer pool使用监控：从这里图里我们可以看到buffer pool几乎是被填满的，另外预留了10%的空间用来做其他用途。 6.一般怎么设置buffer pool大小呢？ warm rows data size + warm indexes size (excl. clustered) + 20% 7.如何预热buffer pool？在InnoDB上面执行select语句：对于聚簇索引来说，大多数情况通过SELECT COUNT(*) 加载到buffer pool中了。对于二级索引来说，要执行一些简单的语句来抓取全部数据，比如select from tbname where 索引的第一列。或者select from tbname force index(二级索引) where colname <>0. 另外，MySQL5.7支持动态修改buffer pool: mysql> SET GLOBAL innodb_buffer_pool_size=size_in_bytes; 8.Dump & restore 在MySQL (5.6+), Percona Server (5.5.10+) or MariaDB (10.0+)可以通过以下配置把buffer pool里面的数据dump出来，并在启动的时候加载到内存中：innodb_buffer_pool_dump_at_shutdown=ONinnodb_buffer_pool_load_at_startup=ON 参考资料: https://michael.bouvy.net/blog/en/2015/01/18/understanding-mysql-innodb-buffer-pool-size/ http://www.speedemy.com/mysql/17-key-mysql-config-file-settings/innodb_buffer_pool_size/

发表了文章 2018-09-29

MySQL 8.0.3加force index优化跳过index dive

在优化阶段index dive会帮助选择到底使用哪一个索引。现在当指定了force index，优化器仍然通过index dive进行代价估算。在某些情况下可以避免index dive，这样就能提升执行时的速度。在MySQL 8.0.3 因为 WL#6526 优化器在使用了force index时仍然跳过index dive。什么是index dive？ Jorgen’s说：“在MySQL里只要存在范围查找方法，就可以通过下钻索引来估计范围内的行数，方法是找出范围的开始和结束，并计算出他们之间的行数。这项技术更精确，所以也是制定良好执行计划的一个基础。”下面的语句，将会为每个range查询执行两次index dive（每次都在range里查找最大最小值） SELECT * FROM t1 WHERE (c1 > 1 AND c1 < 10) OR (c1 > 10 AND c1 < 20) ; SELECT * FROM t1 WHERE c1 IN (11, 22) ; 当有force index时index dive可以被跳过，因为索引的选择已经被提前决定，查询方法依赖于where条件（ref-access只在简单等值查询时才会被用到，其他情况都是选择的范围查找）。优化器在下面的情况会跳过index dive：查询时只访问一张表 force index(某个索引) 没有子查询没有涉及全文索引没有GROUP-BY or DISTINCT 没有ORDER-BY 这种优化目前并不适用于多表查询。如何确定查询是否使用该优化使用EXPLAIN EXPLAIN FORMAT=TRADITIONAL FOR CONNECTION 将会看到下面的变化: - 当index dive被跳过后对应表的行数会变成NULL - “filtered”的值显示NULL EXPLAIN FORMAT=JSON FOR CONNECTION将会看到下面的变化: - 当index dive被跳过后“rows_examined_per_scan” and “rows_produced_per_join” 不会被提及 - 当index dive被跳过后对应表的行数会变成NULL - “filtered”的值显示NULL. EXPLAIN FORMAT=TRADITIONAL and EXPLAIN FORMAT=JSON的展现没有改变使用Optimizer trace Optimizer trace包含“skipped_due_to_force_index”. "range_scan_alternatives": [ { "index": "c1_idx", "ranges": [ "1 < c1 < 10", "10 < c1 < 20" ], "index_dives_for_range_access": "skipped_due_to_force_index", "rowid_ordered": false, "using_mrr": false, "index_only": true, "rows": "not applicable", "cost": "not applicable", "chosen": true } ], 那么什么样的语句将会从该优化中受益呢？包含很多IN 或者OR的查询范围条件里面包含很多行的查询举个例子，第一条语句没有用FORCE INDEX ，那么该优化不会起作用。第二条语句加了FORCE INDEX，将会看到在执行时，“statistics”阶段花了很少的时间。 mysql> TRUNCATE TABLE performance_schema.events_stages_history; Query OK, 0 rows affected (0.00 sec) mysql> TRUNCATE TABLE performance_schema.events_statements_history ; Query OK, 0 rows affected (0.01 sec) mysql> SELECT c1 FROM t1 WHERE ( c1 > 10 AND c1 < 11) OR ... <200 clauses>; +------+ | c1 | +------+ | 25 | <more rows> +------+ 832 rows in set (0.02 sec) mysql> mysql> SELECT c1 FROM t1 FORCE INDEX(c1_idx) WHERE ( c1 > 10 AND c1 < 11) OR ... <200 clauses>; +------+ | c1 | +------+ | 25 | <more rows> +------+ 832 rows in set (0.01 sec) mysql> mysql> select SQL_TEXT, stages.EVENT_NAME, stages.TIMER_WAIT/1000000000 "Time (ms)" -> from performance_schema.events_stages_history AS stages JOIN performance_schema.events_statements_history AS statements -> ON (stages.EVENT_ID > statements.EVENT_ID AND stages.EVENT_ID <= statements.END_EVENT_ID) -> WHERE stages.EVENT_NAME LIKE '%statistics%' AND SQL_TEXT LIKE '%SELECT c1 FROM t1%' ; +-------------------------------------------------------------------------+----------------------+------------+ | SQL_TEXT | EVENT_NAME | Time (ms) | +-------------------------------------------------------------------------+----------------------+------------+ | SELECT c1 FROM t1 WHERE ( c1 > 10 AND c1 < 11) ... | stage/sql/statistics | 29.5890 | | SELECT c1 FROM t1 FORCE INDEX(c1_idx) WHERE ( c1 > 10 AND c1 < 11) ... | stage/sql/statistics | 2.3026 | +-------------------------------------------------------------------------+----------------------+------------+ 2 rows in set (0.00 sec) mysql> 这一特性来自Facebook.从MySQL 8.0.3该优化将会被默认使用。你不用启动任何设置或者跑任何命令，欢迎试用。翻译自：https://mysqlserverteam.com/optimization-to-skip-index-dives-with-force-index/ http://jorgenloland.blogspot.com/2012/04/on-queries-with-many-values-in-in.html

发表了文章 2018-08-22

慎用mysql replace语句

结论如果业务逻辑强依赖自增ID，建议不要用REPLACE 当存在PK冲突的时候是先DELETE再INSERT 当存在UK冲突的时候是直接UPDATE，UPDATE操作不会涉及到AUTO_INCREMENT的修改很大程度上会导致主备中断，存在容灾风险 REPLACE的语法 REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [PARTITION (partition_name,...)] [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),... Or: REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [PARTITION (partition_name,...)] SET col_name={expr | DEFAULT}, ... Or: REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [PARTITION (partition_name,...)] [(col_name,...)] SELECT ... 原理 REPLACE的工作机制有点像INSERT,只不过如果在表里如果一行有PRIMARY KEY或者UNIQUE索引，那么就会把老行删除然后插入新行。如： root@test 03:23:55>show create table lingluo\G *************************** 1. row *************************** Table: lingluo Create Table: CREATE TABLE `lingluo` ( `a` int(11) NOT NULL DEFAULT '0', `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY (`a`),--------------------------同时存在PK约束 UNIQUE KEY `uk_bc` (`b`,`c`)----------------唯一索引约束 ) ENGINE=InnoDB DEFAULT CHARSET=gbk 1 row in set (0.01 sec) root@test 02:01:44>select * from lingluo; Empty set (0.00 sec) root@test 03:27:40>replace into lingluo values(1,10000,3,4);--------表里没有已存在的记录相当于insert Query OK, 1 row affected (0.00 sec)-----------------------affect_rows是1 binlog格式： root@test 02:11:18>replace into lingluo values(1,10000,3,5);-------已经存在记录，且PK和UK同时冲突的时候，相当于先delete再insert Query OK, 2 rows affected (0.00 sec)----------------------affect_rows是2，是delete和insert行数的总和 binlog格式： root@test 02:26:09>select * from lingluo; +---+-------+------+------+ | a | b | c | d | +---+-------+------+------+ | 1 | 10000 | 3 | 5 | +---+-------+------+------+ 1 row in set (0.00 sec) root@test 02:31:54>replace into lingluo values(1,10000,4,5);-------已经存在记录，且PK同时冲突的时候，相当于先delete再insert Query OK, 2 rows affected (0.00 sec)---------------------------------affect_rows是2，是delete和insert行数的总和 root@test 02:32:02>select * from lingluo; +---+-------+------+------+ | a | b | c | d | +---+-------+------+------+ | 1 | 10000 | 4 | 5 | +---+-------+------+------+ binlog格式： root@test 02:37:04>replace into lingluo values(4,10000,6,5); Query OK, 1 row affected (0.00 sec) root@test 02:37:59>replace into lingluo values(6,10000,6,5);-------已经存在记录，且UK同时冲突的时候，直接update Query OK, 2 rows affected (0.00 sec)---------------------------------affect_rows是2 root@test 02:40:31>select * from lingluo; +---+-------+------+------+ | a | b | c | d | +---+-------+------+------+ | 1 | 10000 | 4 | 5 | | 3 | 10000 | 5 | 5 | | 6 | 10000 | 6 | 5 | +---+-------+------+------+ 3 rows in set (0.00 sec) binlog格式：这样的风险点:尽管主备库数据是一致的，但是主备库切换后，备库因AUTO_INCREMENT小于实际数据的最大值，这样会导致写入失败，失败一次后，会更新AUTO_INCREMENT为最大值+1；所以，一些REPLACE操作建议使用INSERT INTO tbname ... VALUES ... ON DUPLICATE KEY UPDATE col1=。建议：如果业务逻辑强依赖自增ID，绝对不要用REPLACE，普通环境也不建议这样用，因为会导致主键的重新组织疑问：既然UK冲突的时候是UPDATE，那么为什么affect_rows都是2呢？让我们从源码上分析看下：指定列REPLACE: root@test 03:34:37>select * from u; +----+------+------+ | id | age | d | +----+------+------+ | 0 | 1 | 126 | | 1 | 0 | 1 | | 3 | 1 | 123 | | 4 | 1 | 127 | | 5 | 0 | 12 | | 7 | 2 | 129 | +----+------+------+ 6 rows in set (0.00 sec) root@test 03:34:37>select * from u; +----+------+------+ | id | age | d | +----+------+------+ | 0 | 1 | 126 | | 1 | 0 | 1 | | 3 | 1 | 123 | | 4 | 1 | 127 | | 5 | 0 | 12 | | 7 | 2 | 129 | +----+------+------+ 6 rows in set (0.00 sec) root@test 03:34:40>replace into u (age,d)values(0,130); Query OK, 2 rows affected, 1 warning (0.01 sec) root@test 03:40:39>show warnings; +---------+------+-----------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------+ | Warning | 1364 | Field 'id' doesn't have a default value | +---------+------+-----------------------------------------+ 1 row in set (0.00 sec) root@test 03:40:47>select * from u; +----+------+------+ | id | age | d | +----+------+------+ | 0 | 0 | 130 |-----------------因为id是parimary但是没有auto_creasement,由126变成130 | 1 | 0 | 1 | | 3 | 1 | 123 | | 4 | 1 | 127 | | 5 | 0 | 12 | | 7 | 2 | 129 | +----+------+------+ 6 rows in set (0.00 sec) 用的时候需要注意的是：如果指定REPLACE列的话，尽量写全，要不然没有输入值的列数据会被赋成默认值（因为是先DELETE在INSERT），就和普通的INSERT是一样的，所以如果你要执行REPLACE语句的话是需要INSERT和DELETE权限的。如果你需要执行 SET col_name = col_name + 1,就相当于执行col_name = DEFAULT(col_name) + 1. REPLACE语句如果不深入看的话，就和INSERT一样，执行完后没什么反应。例： root@test 04:20:04>select * from u; +----+------+------+ | id | age | d | +----+------+------+ | 0 | 0 | 130 | | 1 | 0 | 1 | | 3 | 1 | 123 | | 4 | 1 | 127 | | 5 | 0 | 12 | | 7 | 2 | 129 | +----+------+------+ 6 rows in set (0.00 sec) root@test 04:20:10>replace into u (id,d) values(8,232); Query OK, 1 row affected (0.01 sec) root@test 04:20:39>select * from u; +----+------+------+ | id | age | d | +----+------+------+ | 0 | 0 | 130 | | 1 | 0 | 1 | | 3 | 1 | 123 | | 4 | 1 | 127 | | 5 | 0 | 12 | | 7 | 2 | 129 | | 8 | NULL | 232 | +----+------+------+ 7 rows in set (0.00 sec) root@test 04:20:43>replace into u (id,d) values(7,232); Query OK, 3 rows affected (0.01 sec)----------注意这里affect_rows是3,因为主键7已经存在，唯一索引232已经存在，所以需要删除id为7和8的行，然后插入新行 root@test 04:20:52>select * from u; +----+------+------+ | id | age | d | +----+------+------+ | 0 | 0 | 130 | | 1 | 0 | 1 | | 3 | 1 | 123 | | 4 | 1 | 127 | | 5 | 0 | 12 | | 7 | NULL | 232 | +----+------+------+ 6 rows in set (0.00 sec) root@test 04:20:55> MySQL给REPLACE和LOAD DATA....REPLACE用的算法是: 尝试向表里插入新行当表里唯一索引或者PRIMARY KEY冲突的时候： DELETE冲突行往表里再次插入新行如果遇到重复行冲突，存储过程很可能当作UPDATE执行，而不是DELETE+INSERT，但是显式上都是一样的。这里没有用户可见的影响除了存储引擎层Handler_xxx的状态变量。因为REPLACE ... SELECT语句的结果依赖于SELECT的行的顺序，但是顺序没办法保证都是一样的，有可能从MASTER和SLAVE的都不一样。正是基于这个原因，MySQL 5.6.4以后，REPLACE ... SELECT语句被标记为基于STATEMENT的复制模式不安全的。基于这个变化，当使用STATEMENT记录二进制日志的时候，如果有这样的语句就会在log里面输出一个告警,同样当使用MIXED行复制模式也会记录告警。在MySQL5.6.6之前的版本，REPLACE影响分区表就像MyISAM使用表级锁锁住所有的分区表一样。当使用 REPLACE ... PARTITION语句时确实会发生上述情况。(使用基于行锁的InnoDB引起不会发生这种情况。)在MySQL 5.6.6以后的版本MySQL使用分区锁，只有当分区(只要没有分区表的列更新)包含了REPLACE语句并且WHERE实际匹配到的才会锁住那个分区;否则的话就会锁住整个表。操作形式：binlog格式：参考 https://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html https://dev.mysql.com/doc/refman/5.6/en/replace.html

发表了文章 2018-08-02

这是一篇阿里云数据库专家的长期招聘帖

阿里云事业群-云数据库DBA专家职位描述 1.参与阿里云云产品数据库的总体设计、研发、交付。 2.负责阿里云数据库产品的自动化运维和改进，推动提升阿里云数据库产品的进步。 3.负责与阿里云CA和客户针对数据库产品的技术沟通和支持，为大客户提供优质的技术服务。职位要求 1.精通SQL Server/MySQL/Postgresql/Redis/MongoDB数据库(之一）的运行机制和架构体系，精通数据库架构与设计，对数据库解决方案的优劣和适用场景有深入的理解，能够根据具体场景合理进行数据库选型和模型设计。 2.熟悉Linux/Unix操作系统，熟练掌握Java/Python/Perl/Golang等语言中的一种，可以通过编写程序解决工作中遇到的问题，具备良好的编程风格。 3.熟练掌握数据库性能优化技巧，能够定位全链路上的性能瓶颈（网络、CPU、IO、操作系统等），并解决问题。 4.具有3年以上项目需求分析、方案架构设计工作经验，具有大型行业应用架构经历以及较强的客户需求调研和需求分析能力者优先。 5.具有强大的内心，具备强烈的抗压能力，具有优秀的客服第一的意识。 6.良好的团队协作能力，善为人师，渴望突破，喜欢有挑战的工作。 7.良好的沟通表达能力，具备优秀的文档能力，使用文字、图示清楚地表达架构意图，能够熟练编写各类技术文档。阿里云-技术专家-云数据库职位描述开发和维护阿里云数据库之云服务（RDS）职位要求－熟练掌握C++/JAVA/Python等一种及以上开发语言，熟悉多进程，多线程编程，网络编程等常用技术－熟悉Linux的常用命令，性能查看及调优工具的使用－熟悉关系数据库，分布式数据库－有良好的编码风格，重视代码的质量和容错性，习惯单元测试必备条件，其他：－注重团队协作，容易相处－英文阅读能力－能够在不受监督的情况下完成项目－良好的表达能力，能够清晰和准确地描述问题－良好的发现并解决问题能力－愿意追根究底的态度优先条件－具有大数据量高负载产品/系统的设计或核心开发的经验－拥有开源项目的开发经验－熟悉 Erlang 语言，熟悉 OTP 设计模式，有项目开发经验有意向者欢迎把简历发到lingluo.sss@alibaba-inc.com

发表了文章 2018-07-19

CloudDBA新功能上线--SQL过滤/限制/防火墙

1 前言 CloudDBA是阿里云数据库团队开发的智能诊断和优化平台，可以帮助用户更好使用阿里云数据库。CloudDBA不断提升算法和规则，更好的匹配更多用户场景，刚刚上线了SQL过滤功能，用来解决某类SQL给系统带来的冲击。 2 功能描述匹配用户设置的关键字和并发数，对某类SQL(SELECT/UPDATE/DELETE)进行按并发数限流，当这一类SQL的并发小于并发数的进到Server层，大于并发数的丢弃。该功能只能用来救急，不适合长时间运行。 3 对业务的影响 3.1 收益限制了问题SQL后，保障DB Server端能正常响应正常业务的语句，保障大部分的业务正常运转 3.2 弊端如果设置了SQL防火墙，该SQL被限制后，应用端会收到1317错误，即 query execution was interrupted 4 适用场景如果业务方能够接受舍小(小部分业务损失)保大(大部分业务正常运转)，可以进行限流；如果不能接受上述报错，就要接受整个业务被拖垮的可能。 4.1 能解决的场景 4.1.1 某类SQL并发急剧上升，影响正常业务比如缓存穿透，或者异常调用，可能会造成原来并发不大的SQL语句，并发量突然上升 4.1.2 有数据倾斜SQL，影响正常业务比如大促时拉取某个特别大的数据，造成整体系统繁忙 4.1.3 未创建索引SQL，影响正常业务比如新上线SQL调用量特别大，又没有创建索引，造成整体系统繁忙 4.2 处理误区 4.2.1 切换主备以前碰到上述情况，见有的运维人员是切换主备尝试解决上述问题，主备的配置是一样的，如果主库有问题，同样的请求到备库也是同样的问题，所以切换是解决不了问题的。 4.2.2 kill问题语句 kill语句是可以减少系统压力，未尝不是一种处理手段。但是，请求是不断进来的，不停kill语句手都酸了吧？ 4.3 可以尝试的解决方法上面提到这个功能只是应急手段那么如何才能解决该类问题呢？提高缓存命中率使用只读实例快速加索引 5 使用方式 5.1 路径 RDS控制台->找到具体实例->CloudDBA->问题诊断->SQL过滤 5.2 找到要限制的SQL语句通过"问题诊断"里面，观察到某类SQL执行特别慢，并发又特别多时，这条语句可能就是问题SQL,经过再三确认(根据经验，跟业务开发，跟老板)后就可以限制并发啦。例如：想限制下面这类语句： SELECT * FROM sbtest4 WHERE top_cmt_id=99738 AND status in (1,3) and parent_cmt_id >= 0 order by add_time desc limit 0,20 就要执行下面的步骤 5.3 创建过滤规则 5.3.1 SQL类型是限制SELECT语句还是限制UPDATE语句还是限制DELETE语句，安全起见默认支持SELECT语句，建议您也优先设置SELECT语句 5.3.2 最大并发度该次限制的SQL最大允许在Server端跑多少个 5.3.3 限流时间允许SQL过滤最大执行时间;超过了指定时间，就会自动终止该限制 5.3.4 SQL关键词 SQL过滤的匹配关键词，多个关键词之间以~分隔，不要有空格之类的特殊字符接着上面的例子,SQL关键词我可以这样拼写 SELECT~FROM~sbtest4~WHERE~top_cmt_id~status~parent_cmt_id~order~add_time~desc 5.4 重置/终止过滤规则当遇到设置规则错误，或者想提前终止SQL过滤，可以执行该操作。 5.5 查看过滤历史记录凡是对该实例执行过SQL过滤功能的，都可以在这里查到记录，方便排查问题。 6 注意事项该功能只能用来救急，不适合长时间运行安全起见默认支持SELECT语句，建议您也优先限制SELECT语句该功能只能运行在MySQL 5.6版本该功能是内核层面上的改进，无论是使用高安全链路还是普通链路都可以使用可以设置多个SQL过滤(根据不同的关键词) 如果同一类型的SQL关键词设置了多次，限制的并发按这几个并发的和限制要限制的SQL语句匹配越严格越精准,对业务影响最小如果设置的不够精准，例如SQL关键词是 SELECT~FROM~sbtest4 那匹配上述规则的SQL语句，哪怕是正常业务的，也可能被限制掉了被限制后的SQL在应用端会出现 1317 query execution was interrupted 该限制只对被设置的实例生效，不影响主账号下面的其他实例该限制规则只对新链接有效，如果想使用该规则，在设置过规则后，需要把老链接手动kill(在CloudDBA里可做) 如果您在使用CloudDBA过程中有什么问题，或者好建议，欢迎进群

发表了文章 2018-05-29

truncate table须知

背景最近在排查问题的时候遇到truncate table阻塞了业务语句获取MDL锁，为此记录下truncate table的东东以备后用执行权限 drop table SQL类型 DDL而不是DML，为什么会这样归类呢？先drop再create这样清理表会快些，特别是对大表来说会带来隐式提交，且不能回滚会带来隐式的还有https://dev.mysql.com/doc/refman/5.6/en/implicit-commit.html 遇到其他session持有活动的表锁该操作不会被执行对于跟其他表有关联外键的InnoDB和NDB表，truncate table会失败 truncate table 不像delete一样可以返回实际修改了多少行对于自增值AUTO_INCREMENT会被归0 只要表名合法，即使数据或索引文件被破坏的情况下，这个表可以被重新创建成一张空表。对于分区表，truncate table之后还是保留分区结构，这是因为虽然数据文件和索引文件被drop然后重新创建，但是分区表定义文件(.par)不受影响 truncate table不会唤醒DELETE触发器 table 会关闭所有已经打开的该表的文件句柄。 binlog日志在binlog日志里面，对于InnoDB表或其他支持事务的引擎来说是先drop再create记录的，而对于基于STATEMENT或者MIXED复制模式不会被记录。注意事项如果一个系统的innodb buffer pool比较大并且开启了innodb_adaptive_hash_index ，truncate table 可能会引起暂时的性能下降，因为当删除InnoDB表的自适应hash簇时需要遍历LRU链表。参考 https://dev.mysql.com/doc/refman/5.6/en/truncate-table.html

发表了文章 2018-04-28

MySQL show processlist过滤

背景有客户需要根据processlist做一些监控，需要用到过滤上面的操作。根据某个用户过滤 mysql> select * from information_schema.processlist where User='UserName'; +----------+------------+---------------------+------+------------------+----------+------------------------------------------------------------------+------+ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | +----------+------------+---------------------+------+------------------+----------+------------------------------------------------------------------+------+ | 18396093 | UserName | *.*.*.*:47712 | NULL | Binlog Dump GTID | 4608287 | Master has sent all binlog to slave; waiting for binlog to be up | NULL | +----------+------------+---------------------+------+------------------+----------+------------------------------------------------------------------+------+ 1 rows in set (0.00 sec) 杀死某些线程 mysql> select concat('kill ',ID,';') from information_schema.processlist where User='UserName'; +------------------------+ | concat('kill ',ID,';') | +------------------------+ | kill 18396093; | | kill 22658331; | | kill 482; | +------------------------+ 3 rows in set (0.00 sec) 监控统计每个用户的访问 mysql> select User,count(*) as cnt from information_schema.processlist group by user; +-----------------+-----+ | User | cnt | +-----------------+-----+ | User1 | 168 | | rep | 1 | | User2 | 2 | +-----------------+-----+ 3 rows in set (0.00 sec) 监控其他信息凡是在processlist里面的都可以用来做过滤 mysql> show create table information_schema.processlist\G *************************** 1. row *************************** Table: PROCESSLIST Create Table: CREATE TEMPORARY TABLE `PROCESSLIST` ( `ID` bigint(21) unsigned NOT NULL DEFAULT '0', `USER` varchar(16) NOT NULL DEFAULT '', `HOST` varchar(64) NOT NULL DEFAULT '', `DB` varchar(64) DEFAULT NULL, `COMMAND` varchar(16) NOT NULL DEFAULT '', `TIME` int(7) NOT NULL DEFAULT '0', `STATE` varchar(64) DEFAULT NULL, `INFO` longtext ) ENGINE=MyISAM DEFAULT CHARSET=utf8 参考 https://dev.mysql.com/doc/refman/5.7/en/show-processlist.html

发表了文章 2018-04-13

ERROR 1436 (HY000) at line 1943

今天遇到这样一个问题： ERROR 1436 (HY000) at line 1943: Thread stack overrun: 7200 bytes used of a 131072 byte stack, and 128000 bytes needed. Use 'mysqld --thread_stack=#' to specify a bigger stack. 这种情况的解决办法是：修改my.cnf提升thread_stuck到256k,这样可以永久地避免这个问题。参考 https://zend18.zendesk.com/hc/en-us/articles/203535173-MySQL-Error-THREAD-STACK-OVERRUN

发表了文章 2018-04-10

MySQL里的回收表空间

前言 MySQL里面有好几种回收表空间的方法，但是他们之间的异同是什么呢？非常感谢提问问题的同学，我在这里总结下。回收表空间有三种方法： OPTIMIZE TABLE tbname ALTER TABLE ... FORCE ALTER TABLE tbname ENGINE= INNODB OPTIMIZE TABLE的支持表类型：INNODB,MYISAM, ARCHIVE，NDB；会重组表数据和索引的物理页，对于减少所占空间和在访问表时优化IO有效果。在InnoDB表中等价 ALTER TABLE ... FORCE，ALTER TABLE ENGINE= INNODB,会重新整理在聚簇索引上的数据和索引；这意味着，我们在内部创建一个新的表，就像现有的表一样，然后我们将数据一次一行地加载到一行中。对于聚集索引(又称主键)，如果数据被插入到顺序之外，或者如果已经进行了修改，这就造成了一些影响填充因素的间隙，可能会导致一些空间浪费。对于二级索引，它们将按聚集索引顺序依次加载一行，这可能导致他们直接回到支离破碎的状态。而且InnoDB的MVCC实现在二级索引中确实有多个版本，因此可能在这里回收空间。在MySQL 5.5中，InnoDB引入了一个称为“快速索引创建”的特性，它可以通过先造数据再创建索引来更优化地创建这些二级索引。然而，在官方的Oracle MySQL版本中，这个特性并没有被绑定到优化表中。可以查看BUG# 57583。对于InnoDB和分区表，在MySQL 5.6.17之前，OPTIMIZE TABLE在拷贝表的过程中,在SQL层会锁表，执行时DML会阻塞；在MySQL 5.6.4之后在执行OPTIMIZE TABLE时可以同时执行DML。阿里云的RDS 5.6已经支持Online OPTIMIZE.对于MYISAM表，OPTIMIZE TABLE非常重要：如果表已删除或分隔行，就修复该表。如果索引页没有排序，就排序它们。如果表的统计信息不是最新的(而且修复不能通过对索引进行排序)，就更新它们。注：需要有足够的空间才能进行OPTIMIZE TABLE。如果没有磁盘空间，MySQL将不能进行优化，表也无法使用。参考 https://dev.mysql.com/doc/refman/5.7/en/optimize-table.htmlhttps://mysqlserverteam.com/mysql-5-6-17-improved-online-optimize-table-for-innodb-and-partitioned-innodb-tables/http://www.tocker.ca/2013/05/02/optimize-check-repair-analyze-table-innodb-edition.html

发表了文章 2018-04-09

Errcode: 24 - Too many open files

发表了文章 2018-04-09

MySQL的binlog_row_image

前言从MySQL5.6开始binlog的同步模式便有了binlog_row_image，那这个参数能给binlog带来什么变化呢？我们一起看下。 CREATE TABLE `a` ( `id` bigint(20) DEFAULT NULL, `a` varchar(128) DEFAULT NULL, `b` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 mysql> show global variables like 'binlog_row_image'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | binlog_row_image | FULL | +------------------+-------+ mysql> select * from a; +------+--------------------+--------+ | id | a | b | +------+--------------------+--------+ | 1 | full | bbbbbb | | 2 | buduibudui | 22222 | +------+--------------------+--------+ 2 rows in set (0.00 sec) ### UPDATE `test`.`a` ### WHERE ### @1=1 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @2='full2minimal' /* VARSTRING(384) meta=384 nullable=1 is_null=0 */ ### @3='bbbbbb' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ ### @4=1 /* LONGINT meta=0 nullable=0 is_null=0 */ ### SET ### @1=1 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @2='full2minimal2' /* VARSTRING(384) meta=384 nullable=1 is_null=0 */ ### @3='bbbbbb' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ ### @4=1 /* LONGINT meta=0 nullable=0 is_null=0 */ ### UPDATE `test`.`a` ### WHERE ### @1=1 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @2='full2minimal2' /* VARSTRING(384) meta=384 nullable=1 is_null=0 */ ### @3='bbbbbb' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ ### @4=1 /* LONGINT meta=0 nullable=0 is_null=0 */ ### SET ### @2='full2minimal' /* VARSTRING(384) meta=384 nullable=1 is_null=0 */ 注这个参数修改后只对新建的连接有效，老连接还是沿用老模式参考 https://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html https://dev.mysql.com/doc/internals/en/binlog-row-image.html

发表了文章 2018-04-08

分析MySQL网络包

前言在没有来阿里云之前，有些信息采集的不是那么全，经常用percona的工具，对于非阿里云的客户，或者您的信息采集的不是那么全的情况下，我相信这个工具会给您带来很大的便利。 MySQL信息采集对于DBA来说除了qps，tps，系统状态的监控之外，SQL的采集这是非常重要的一块，如果要想采集SQL的话，一般有几种途径：开general_log 这个开销非常大，每条SQL都会记入general_log 抓TCP包抓包方式常用的就是tcpdump,这种方式的代价要小得多 tcpdump -i en1 -s 65535 -x port 3306 想记录下来 tcpdump -i en1 -s 65535 port 3306 -x -q -n -tttt > mysql_tcpdump.txt 记录下来就要看包里的内容了，怎么看呢？ pt-query-digest tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt pt-query-digest --type tcpdump mysql.tcp.txt 或者 #!/bin/bash tcpdump -i any -s 0 -l -w - dst port 3306 | strings | perl -e ' while(<>) { chomp; next if /^[^ ]+[ ]*$/; if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER|CALL)/i) { if (defined $q) { print "$q\n"; } $q=$_; } else { $_ =~ s/^[ \t]+//; $q.=" $_"; } }' 虽然percona的包在6，7年前都已经出来，原来还是mk-系列，但是老工具拿来温习下效果也是非常不错的。参考 https://www.databasejournal.com/features/mysql/article.php/3916226/Troubleshooting-MySQL-Slow-Queries-with-Tcpdumps.htm https://www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html

发表了文章 2018-04-04

常见计算机术语汇总

稳定性相关 SLA SLA(Service Level Agreement) RTO RTO (Recovery Time tive，恢复目标时间)是指灾难发生后，从IT系统当机导致业务停顿之时开始，到IT系统恢复至可以支持各部门运作、恢复运营之时，此两点之间的时间段称为RTO。比如说灾难发生后半天内便需要恢复，RTO值就是十二小时; RTA RTA(Recovery Time Actual,恢复时间实际)是在练习、实际事件或基于恢复方法的基础上建立的，技术支持团队开发。这是技术支持所需要的时间框架，用于将已恢复的基础设施交付给业务。 The RTA-RTO Gap 业务连续性组然后记录恢复的基础设施交付的时间。通过建立RTO的业务线来协调对该基础结构的测试，如果测试成功，那么初始RTA是有效的。如果业务线的测试由于基础设施恢复问题而不成功，那么基础设施将被交还给技术支持组以进行进一步的分析和恢复，并且在初始交付时将时间重置为开始时间。如果业务线验证功能是成功的，那么业务连续性组会协调RTO和RTA之间的差异。任何间隙都被视为后续项目，并通过决议进行跟踪。(Ex:基于业务期望的RTA - RTO = gap) RPO RPO (Recovery Point tive，恢复目标点)是指从系统和应用数据而言，要实现能够恢复至可以支持各部门业务运作，恢复得来的数据所对应时的间点。如果现时企业每天凌晨零时进行备份一次，当服务恢复后，系统内储存的只会是最近灾难发生前那个凌晨零时的资料。 RPA Recovery Point Actual (RPA)是一个度量单位，它代表一个组织如果现在有灾难，并且必须从本地或离线备份中恢复的实际数据量。为了符合你的RPO政策，在任何时候你的RPA总是必须低于你的RPO。Unitrends Enterprise Plus提供了关于RPO/RTO遵从性和实际值的报告，但是如果您有其他的unitrend许可，您可以使用powershell脚本查看您的备份在任何时候的实际RPA值和遵从性。性能相关 RTT RTT=传播时延（往返的）+排队时延（路由器和交换机的）+数据处理时延（应用程序的）未完待续参考《计算机网络原理》https://en.wikipedia.org/wiki/Disaster_recovery_and_business_continuity_auditing http://joehertvik.com/rpo-rto-rta-disaster-recovery-terms-mean/ https://en.wikipedia.org/wiki/Recovery_point_objective

发表了文章 2018-03-21

关系型数据库的历史

1970年在IBM圣何塞研究实验室工作的牛津大学的数学家 Edgar F. Codd 发表了一篇论文，展示了如何在不知道信息的结构或在数据库中的位置信息的情况下，如何访问存储在大型数据库中的信息。在此之前，检索信息需要相对复杂的计算机知识，甚至是那些知道如何编写程序来获取特定信息的专家的服务——这是一项耗时且昂贵的任务。数据库被用来一次又一次地检索相同的信息，并且以一种可预测的方式——如制造材料的清单——在当时已经很好地建立起来了。Codd所做的是打开了一个新的数据独立世界的大门。用户不必是专家，也不需要知道信息在哪里，也不需要知道计算机是如何检索到信息的。他们现在可以更专注于自己的业务，更少关注他们的电脑。 Codd称他的论文是“大型共享数据银行的数据关系模型”。计算机科学家称之为“革命性的想法”。如今，关系数据库的易用性和灵活性使它们成为财务记录、制造和物流信息和人员数据的主要选择。大多数常规的数据交易——使用信用卡、交易股票、进行旅行预订、在线购买——都使用基于关系数据库理论。 Codd的关系模型起初备受争议;人们认为这个模型太过简单，它永远不会有好的表现。减少输入每个项的次数，这对减少数据输入错误有好处。每个条目只存储一次，所以即使您犯了错误，您也必须只纠正一个条目。 Ray Boyce和我想设计一种查询语言，它具有Ted Codd的关系语言的表达能力，但更容易被那些在集合理论或形式逻辑方面不是专家的用户理解。关系模型是一种谈论数据的很好的统一方式，允许比较系统、比较算法等等。它还利用了一些优美的数学理论。数据，而不是程序，是唯一的事情——应用程序是暂时的，除了获取、操作和显示数据之外没有任何价值。数据是唯一有价值的东西。 Codd最大的成就是使数据库管理成为一门科学。他通过提供一个理论框架把领域放在了坚实的科学基础上,关系模型的各种重要的问题可以以科学的方式攻击。 Codd的想法催生了一个新的IBM产品家族,集中在IBM®DB2®数据库管理系统,以及行业标准计算机语言处理关系数据库,称为SQL。据《纽约时报》的讣告Codd：关系数据专家曾在IBM DB2上成为Codd博士的一个商业伙伴--Chris Date说“…在Codd博士的工作之前电子数据库可以说完全特设和高度混乱，这也成为他进入商业产品的契机。”与许多革命性的想法一样，关系数据库也不是很容易实现。到20世纪60年代，世界上大量数据存储在新主机上——其中许多是IBM System/360机器——已经成为一个问题。主机计算很昂贵，通常每分钟花费数百美元。其中很大一部分成本是数据库管理的复杂性。Codd从他的祖国英格兰来到美国时，他在计算机科学领域获得了博士学位，开始着手解决这个问题。他以一个非常简单的前提开始:他希望能够向计算机询求信息，然后让计算机找出信息存储的位置和方式，以及如何检索信息。IBM的Don Chamberlin说，Codd的基本思想是“数据项之间的关系应该基于数据项的值，而不是单独指定的链接或嵌套。这个概念大大简化了查询的规范，并允许前所未有的灵活性以新的方式利用现有的数据集”。未完参考 http://www-03.ibm.com/ibm/history/ibm100/us/en/icons/reldb/

发表了文章 2018-02-11

理解innodb buffer pool

innodb buffer pool有几个目的：缓存数据--众所周知，这个占了buffer pool的大半空间缓存目录--数据字典 insert buffer 排序的内部结构--比如自适应hash的结构或者一些行锁查看表的数据和索引使用情况? SELECT engine, count(*) as TABLES, concat(round(sum(table_rows)/1000000,2),'M') rows, concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA, concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx, concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size, round(sum(index_length)/sum(data_length),2) idxfrac FROM information_schema.TABLES WHERE table_schema not in ('mysql', 'performance_schema', 'information_schema','test') GROUP BY engine ORDER BY sum(data_length+index_length) DESC LIMIT 10; +--------+--------+----------+---------+--------+------------+---------+ | engine | TABLES | rows | DATA | idx | total_size | idxfrac | +--------+--------+----------+---------+--------+------------+---------+ | InnoDB | 71608 | 1644.51M | 130.79G | 82.76G | 213.55G | 0.63 | +--------+--------+----------+---------+--------+------------+---------+ idxfrac这个值越低越好，举个例子，表里只有一个唯一索引的数据如下： +--------+--------+----------+---------+--------+------------+---------+ | engine | TABLES | rows | DATA | idx | total_size | idxfrac | +--------+--------+----------+---------+--------+------------+---------+ | InnoDB | 16 | 3120.61M | 386.59G | 58.09G | 444.68G | 0.15 | +--------+--------+----------+---------+--------+------------+---------+ 可见idxfrac可见这个值越低越好。获取buffer pool占的page个数 select count(*) from information_schema.innodb_buffer_page; +----------+ | count(*) | +----------+ | 262142 | +----------+ 获取page类型: select page_type as Page_Type,sum(data_size)/1024/1024 as Size_in_MB from information_schema.innodb_buffer_page group by page_type order by Size_in_MB desc; +-------------------+--------------+ | Page_Type | Size_in_MB | +-------------------+--------------+ | INDEX | 158.66378689 | | UNKNOWN | 0.00000000 | | TRX_SYSTEM | 0.00000000 | | SYSTEM | 0.00000000 | | FILE_SPACE_HEADER | 0.00000000 | | IBUF_BITMAP | 0.00000000 | | EXTENT_DESCRIPTOR | 0.00000000 | | ALLOCATED | 0.00000000 | | INODE | 0.00000000 | | BLOB | 0.00000000 | | UNDO_LOG | 0.00000000 | | IBUF_FREE_LIST | 0.00000000 | | IBUF_INDEX | 0.00000000 | +-------------------+--------------+ 从这里可以看到数据和索引占了buffer pool的大部分空间。也可以看出来这里有几种重要的页类型： INDEX: B-Tree index IBUF_INDEX: Insert buffer index UNKNOWN: not allocated / unknown state TRX_SYSTEM: transaction system data buffer pool里每个索引的使用 select table_name as Table_Name, index_name as Index_Name,count(*) as Page_Count, sum(data_size)/1024/1024 as Size_in_MB from information_schema.innodb_buffer_page group by table_name, index_name order by Size_in_MB desc; +--------------------------------------------+-----------------+------------+-------------+ | Table_Name | Index_Name | Page_Count | Size_in_MB | +--------------------------------------------+-----------------+------------+-------------+ | `magento`.`core_url_rewrite` | PRIMARY | 2829 | 40.64266014 | | `magento`.`core_url_rewrite` | FK_CORE_URL_... | 680 | 6.67517281 | | `magento`.`catalog_product_entity_varchar` | PRIMARY | 449 | 6.41064930 | | `magento`.`catalog_product_index_price` | PRIMARY | 440 | 6.29357910 | | `magento`.`catalog_product_entity` | PRIMARY | 435 | 6.23898315 | +--------------------------------------------+-----------------+------------+-------------+ 一个典型的buffer pool使用监控从这里图里我们可以看到buffer pool几乎是被填满的，另外预留了10%的空间用来做其他用途。一般怎么设置buffer pool大小呢？ warm rows data size + warm indexes size (excl. clustered) + 20% 如何预热buffer pool？在InnoDB上面执行select语句：对于聚簇索引来说，大多数情况通过SELECT COUNT(*) 加载到buffer pool中了。对于二级索引来说，要执行一些简单的语句来抓取全部数据，比如select from tbname where 索引的第一列。或者select from tbname force index(二级索引) where colname <>0. 另外，MySQL5.7支持动态修改buffer pool: mysql> SET GLOBAL innodb_buffer_pool_size=size_in_bytes; Dump & restore 在MySQL (5.6+), Percona Server (5.5.10+) or MariaDB (10.0+)可以通过以下配置把buffer pool里面的数据dump出来，并在启动的时候加载到内存中： innodb_buffer_pool_dump_at_shutdown=ON innodb_buffer_pool_load_at_startup=ON 参考 https://michael.bouvy.net/blog/en/2015/01/18/understanding-mysql-innodb-buffer-pool-size/ http://www.speedemy.com/mysql/17-key-mysql-config-file-settings/innodb_buffer_pool_size/

发表了文章 2018-02-11

长话短说mysql slow log的query time

当时MySQL卡顿的时间只有62s，为什么这个时间却是427553s? 先看下慢日志： # Time: 160601 15:09:52 # User@Host: slave[slave] @ [10.236.232.132] Id: 5668740 # Query_time: 427536.674791 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0 SET timestamp=1464764992; # administrator command: Binlog Dump; 慢日志的源码在log.cc: /* Log a query to the slow log table SYNOPSIS log_slow() thd THD of the query current_time current timestamp query_start_arg command start timestamp user_host the pointer to the string with user@host info user_host_len length of the user_host string. this is computed once and passed to all general log event handlers query_time Amount of time the query took to execute (in microseconds) ###查询语句花费的时间,单位：微秒 lock_time Amount of time the query was locked (in microseconds) is_command The flag, which determines, whether the sql_text is a query or an administrator command (these are treated differently by the old logging routines) sql_text the very text of the query or administrator command processed sql_text_len the length of sql_text string DESCRIPTION Log a query to the slow log table RETURN FALSE - OK TRUE - error occured */ .... if (thd->start_utime) { query_utime= (current_utime - thd->start_utime); #query_utime=当前时间-线程开始时间 lock_utime= (thd->utime_after_lock - thd->start_utime); } 所以这个时间就可以解释了，这个binlog dump一共执行了427536.674791s，是正常的，这次记录慢查询的时间是：Time: 160601 15:09:52，因为上一次binlog记入的时间是：# Time: 160527 16:22:34,时间上可以对的上.

发表了文章 2018-02-11

那些从MySQL5.6升级到5.7的坑--innodb_large_prefix

前言升级有风险，参数需谨慎 innodb_large_prefix 关于innodb_large_prefix这个参数，在之前的“MySQL之最”中有提到过，那在5.6和5.7中的表现哪里不一样呢？更直观一点，举个例子：在5.6中的表现： $mysql -uroot -p Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4708773 Server version: 5.6.16.12-20161009-log Source distribution Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. mysql> CREATE TABLE `cc` ( -> `id` int(11) NOT NULL AUTO_INCREMENT, -> `c` varchar(255) DEFAULT NULL, -> PRIMARY KEY (`id`), -> KEY `idx_c` (`c`) -> ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> show warnings; +---------+------+---------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------+ | Warning | 1071 | Specified key was too long; max key length is 767 bytes | +---------+------+---------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show create table cc\G *************************** 1. row *************************** Table: cc Create Table: CREATE TABLE `cc` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_c` (`c`(191)) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) mysql> show global variables like "innodb_large_prefix"; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | innodb_large_prefix | OFF | +---------------------+-------+ 1 row in set (0.00 sec) 在5.7中的表现 $mysql -uroot -p Entry Port ==== 3313 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9304 Server version: 5.7.13.1-AliSQL-20160928-log Source distribution Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. root@test 02:23:11>CREATE TABLE `cc` ( -> `id` int(11) NOT NULL AUTO_INCREMENT, -> `c` varchar(255) DEFAULT NULL, -> PRIMARY KEY (`id`), -> KEY `idx_c` (`c`) -> ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4; Query OK, 0 rows affected (0.00 sec) root@test 02:25:46>show warnings; Empty set (0.00 sec) root@test 02:26:17>show global variables like "innodb_large_prefix"; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | innodb_large_prefix | ON | +---------------------+-------+ 1 row in set (0.00 sec) root@test 02:28:43> 结论如果想在5.6和5.7统一的话，修改innodb_large_prefix这个参数同时为ON或者OFF就可以了

发表了文章 2018-02-09

MySQL里面的CONNECTION_ID

CONNECTION_ID() MySQL的这个函数返回的是这个连接的连接ID或者thread ID。对于已经建立的连接的客户端，都有一个唯一的连接ID。这个值和 INFORMATION_SCHEMA.PROCESSLIST一样，也和SHOW PROCESSLIST输出的ID列一样，也是Performance Schema threads 表里面的PROCESSLIST_ID。即CONNECTION_ID=thread ID=INFORMATION_SCHEMA.PROCESSLIST.ID=SHOW PROCESSLIST里的ID=Performance Schema threads.PROCESSLIST_ID例：参考 https://dev.mysql.com/doc/refman/5.7/en/information-functions.html https://dev.mysql.com/doc/refman/5.7/en/threads-table.html

发表了文章 2018-02-07

FLUSH TABLES WITH READ LOCK

前言昨天碰到一个flush tables with read lock阻塞其他所有操作的案例，在博客园中6年前我已经写过一篇文章，再次拿出来分享下。现象 id 账号来源ip 库名执行时间(秒) 状态线程占用的内存(字节) SQL占用的内存(字节) SQL 11316259 test_user 127.0.0.1:51533 test 710 Writing to net 48016 9296 select * from tbname5 where ...; 11316272 test_user 127.0.0.1:51589 test 707 Writing to net 60440 10056 select * from tbname5 where ...; 11316276 test_user 127.0.0.1:51657 test 704 Writing to net 60440 10056 select * from tbname5 where ...; 11316299 test_user 127.0.0.1:51849 test 696 Writing to net 60440 9616 select * from tbname5 where ...; 11316383 test_user 127.0.0.1:52379 None 672 Waiting for table flush 0 8208 flush tables with read lock 11271706 test 127.0.0.1:40421 test 667 Waiting for global read lock 0 8208 UPDATE tbname1 where ...; 11317050 test 127.0.0.1:40066 test 120 Waiting for global read lock 53352 8208 UPDATE tbname6 where ...; 11317338 test_user 127.0.0.1:58803 test 63 Waiting for table flush 39776 8208 SHOW CREATE TABLE `test`.`tbname10` 11317352 test 127.0.0.1:5680 test 35 Waiting for global read lock 47968 8368 INSERT INTO tbname9 ...; 11317390 test 127.0.0.1:5726 test 30 Waiting for global read lock 39520 8208 UPDATE tbname8 where ...; 11317395 test 127.0.0.1:40427 test 27 Waiting for global read lock 39776 8208 SELECT * FROM tbname7 where ... FOR UPDATE 11317345 test 127.0.0.1:42949 test 25 Waiting for global read lock 47968 8504 INSERT INTO tbname6 11317404 test 127.0.0.1:40435 test 19 Waiting for global read lock 39520 8208 UPDATE tbname5 where ...; 11317435 test 127.0.0.1:43028 test 9 Waiting for global read lock 47968 8368 INSERT INTO tbname4 ...; 11317422 test 127.0.0.1:40451 test 3 Waiting for global read lock 47968 8368 INSERT INTO tbname3 ...; 11317434 test 127.0.0.1:5759 test 3 Waiting for global read lock 47968 8472 INSERT INTO tbname2 ...; 11317442 test 127.0.0.1:5790 test 2 Waiting for global read lock 47968 8472 INSERT INTO tbname2 ...; 前几个执行比较慢的SQL，阻塞了FTWRL,FTWRL阻塞了后面的DML和SELECT...FOR UPDATE. flush tables with read lock 会关闭所有打开的表，同时对于所有数据库中的表都加一个读锁，直到显示地执行unlock tables，该操作常常用于数据备份的时候。也就是将所有的脏页都要刷新到磁盘，然后对所有的表加上了读锁，于是这时候直接拷贝数据文件也就是安全的。但是如果你发出命令FTWRL时，还有其他的操作，而且是很耗时的操作呢？先说写操作，这个FTWRL肯定是得等的，等写操作完成才能执行FTWRL，这个很好理解。那么对于其他的读操作呢？比如说在FLWRL发出之前有一个query： select count(*) from tb 那么FTWRL也得等待(show processlist可以看到 waiting for table flush)。为什么会阻塞读？你可能会说在mysql中读与读不是不会排斥的吗，为什么需要等待呢？因为FTWRL是要flush脏页的，只有这样才真的能保证数据一致性(比如说在xtrabackup备份MyISAM表的时候)，而在select count(*) from tb执行的时候，因为所有的操作都是在内存中操作，所以此时还不能完全flush，因此FTWRL就得等待。或许你还会有疑问，select的页不是脏页，为什么FTWRL还要等待呢？难道MySQL不能做得更完善点吗？我觉得MySQL还不是不会做的这么简单吧，等待的原因是因为这个表很大，无法一次性将所有的页都读到内存中来，而query具有原子性，总不可能执行一般被堵塞吧，所以说还是得乖乖的让它执行完，所以FTWRL就得等待了。flush tables with read lock在测试的时候，它有可能花几毫秒就可以完成，就像我遇到的情况，在生产环境也可能花几个小时才能完成。在此期间，MySQL服务完全block住了，而不仅仅是read-only。因为FTWRL会做以下动作：请求锁 flush tables with read lock请求全局read lock。当这种情况发生时，其他进程如果有修改动作的话就会被阻塞。从理论上讲，这种情况并不是很糟糕，因为flush tables with read lock只需要read lock，其它命令（只需要read lock的命令）可以和flush tables with read lock并存。然而，事实上，大多数表需要读和写锁的。例如：第一个写语句会被这个全局的读锁阻塞，而子查询又会被第一个写语句阻塞，所以真正有效果的是使用的是排它锁，所有新请求就会被阻塞，包括读查询语句。等待锁在flush tables with read lock成功获得锁之前，必须等待所有语句执行完成（包括SELECT）。所以如果有个慢查询在执行，或者一个打开的事务，或者其他进程拿着表锁，flush tables with read lock就会被阻塞，直到所有的锁被释放。请看下面的例子： mysql> show processlist; +----+------+-----------+------+------------+------+-------------------+----------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+------------+------+-------------------+----------------------------------------------------------------------+ | 4 | root | localhost | test | Query | 80 | Sending data | select count(*) from t t1 join t t2 join t t3 join t t4 where t1.b=0 | | 5 | root | localhost | test | Query | 62 | Flushing tables | flush tables with read lock | | 6 | root | localhost | test | Field List | 35 | Waiting for table | | | 7 | root | localhost | test | Query | 0 | NULL | show processlist | +----+------+-----------+------+------------+------+-------------------+----------------------------------------------------------------------+ 4 rows in set (0.00 sec) 可以看到线程6没有连进来，因为MySQL的客户端连接时没有指定-A，它尝试获取当前库下的所有的表和列。线程5也没有flush tables，因为它在等线程4释放锁。刷新表当flush tables with read lock拿到锁后，必定flush data。对于MyISAM引擎，不光是刷新它自己的data，也刷新操作系统的data到disk上（MyISAM relies on the filesystem block cache for caching reads to the data rows and indexes, while InnoDB does this within the engine itself, combining the row caches with the index caches），所以如果是MyISAM表的话有可能会花费很长时间。持有锁我们可以使用unlock tables或者其它命令来释放锁。结论一个备份系统一般都是在生产环境中用的，所以我们不能简单的认为flush tables with read lock很快就执行完。在某些情况下，执行慢是没法避免的。但是我们可以配置备份系统避免这种global lock。参考 http://dev.mysql.com/doc/refman/5.7/en/flush.html http://www.mysqlperformanceblog.com/2010/04/24/how-fast-is-flush-tables-with-read-lock/

发表了文章 2018-02-07

MySQL里面的innodb_support_xa

在看innodb_support_xa之前我们要先看下什么是XA，什么是两阶段提交。 XA 一个协调分布式事务的标准接口，为了遵从ACID原则，允许多个DB参与事务。为了查看更多，请 Section 13.3.7, “XA Transactions”。通常情况下，默认都启用了XA分布式事务支持。如果你没有用这个特性，你可以配置innodb_support_xa，避免为每个事务执行fsync带来的性能损耗。在MySQL 5.7.10版本不启用innodb_support_xa，因为它使主备复制不安全和降低了与二进制日志group commit相关的性能。在MySQL 8.0中这个innodb_support_xa选项被移除掉了。两阶段提交简言之，两阶段提交是在XA规范下，分布式事务里面的一部分操作(有时也被称为2PC)。当事务里面有多个DB操作时，要么都提交，要么都回滚。下面的图更容易理解些。下面的一段引用沃趣科技 MySQL数据库专家 @pickup112，找不到原文请见谅第一阶段：首先，协调者在自身节点的日志中写入一条的日志记录，然后所有参与者发送消息prepare T，询问这些参与者（包括自身），是否能够提交这个事务；参与者在接受到这个prepare T 消息以后，会根据自身的情况，进行事务的预处理，如果参与者能够提交该事务，则会将日志写入磁盘，并返回给协调者一个ready T信息，同时自身进入预提交状态状态；如果不能提交该事务，则记录日志，并返回一个not commit T信息给协调者，同时撤销在自身上所做的数据库改；参与者能够推迟发送响应的时间，但最终还是需要发送的。第二阶段：协调者会收集所有参与者的意见，如果收到参与者发来的not commit T信息，则标识着该事务不能提交，协调者会将Abort T 记录到日志中，并向所有参与者发送一个Abort T 信息，让所有参与者撤销在自身上所有的预操作；如果协调者收到所有参与者发来prepare T信息，那么协调者会将Commit T日志写入磁盘，并向所有参与者发送一个Commit T信息，提交该事务。若协调者迟迟未收到某个参与者发来的信息，则认为该参与者发送了一个VOTE_ABORT信息，从而取消该事务的执行。参与者接收到协调者发来的Abort T信息以后，参与者会终止提交，并将Abort T 记录到日志中；如果参与者收到的是Commit T信息，则会将事务进行提交，并写入记录。一般情况下，两阶段提交机制都能较好的运行，当在事务进行过程中，有参与者宕机时，他重启以后，可以通过询问其他参与者或者协调者，从而知道这个事务到底提交了没有。当然，这一切的前提都是各个参与者在进行每一步操作时，都会事先写入日志。引用结束 innodb_support_xa innodb_support_xa的作用？ innodb_support_xa可以开关InnoDB的xa两段式事务提交。如何开启？ innodb_support_xa=true，支持xa两段式事务提交。如何运转？ innodb_support_xa=true，支持xa两段式事务提交。此时MySQL首先要求innodb prepare，对应的redolog 将写入log buffer；如果有其他的引擎，其他引擎也需要做事务提交的prepare，然后MySQL server将binlog将写入；并通知各事务引擎真正commit；InnoDB将commit标志写入，完成真正的提交，响应应用程序为提交成功。这个过程中任何出错将导致事务回滚，响应应用程序为提交失败。也就是说，在这种情况下，基本不会出错。为什么启用innodb_support_xa？如果不启用innodb_support_xa,事务再写到binlog文件时可能会和现在的db在commit的时候顺序不一样，当这些binlog应用到恢复数据库或者在备库执行时，可能会产生不一样的数据它可以保证InnoDB两阶段提交(prepare,commit).这不仅仅在用户发起的XA，在内部的XA协调InnoDB事务日志和MySQL的binlog日志，保证数据一致。数据一致是一个非常非常重要。内部的XA就是MySQL Server层，即以binlog为准。开启innodb_support_xa带来的影响？如果在XA事务里面启用了InnoDB支持两阶段提交，在事务准备阶段将会带来额外的刷盘操作，性能影响会达到5%，所有为了提高性能，有些DBA会设置innodb_support_xa=false。这样的话，redolog和binlog将无法同步，可能存在事务在主库提交，但是没有记录到binlog的情况。这样也有可能造成事务数据的丢失。为什么会影响性能？我们来看下两阶段提交的过程： Prepare InnoDB [ha_prepare]: 1.1 Write prepare record to log buffer 1.2 fsync() log file to disk (this can currently do group commit) 1.3 Take prepare_commit_mutex Log transaction to binary log [TC_LOG_BINLOG::log_xid]: 2.1 Lock binary log 2.2 Write transaction data to binary log 2.3 Sync binary log based on sync_binlog. This forces the binlog to always fsync() (no group commit) due to prepare_commit_mutex 2.4 Unlock binary log Commit InnoDB: 3.1 Release prepare_commit_mutex 3.2 Write commit record to log buffer 3.3 Sync log buffer to disk (this can currently do group commit) 3.4 InnoDB locks are released 从上面的步骤里面，我们可以看到会调用会刷redolog和binlog，那会势必会影响写入的性能。那么这个可以控制刷盘的速度吗？可以。redolog的控制参数是innodb_flush_log_at_trx_commit 值描述 0 一秒InnoDB log buffer刷新一次到硬盘。在事务commit时不刷新log buffer到log file.由于调度问题，不保证一秒一刷新的操作一定能发生。mysqld crash后导致丢失最后一秒的事务。 1 出于完全遵从ACID原则的目的，默认值是1.在每个事务commit时，InnoDB log buffer里面的内容都要写入log file,并且log file刷到磁盘。With this value, the contents of the InnoDB log buffer are written out to the log file at each transaction commit and the log file is flushed to disk. 2 每个事务提交后，InnoDB log buffer里面的内容都要写入log file，log file大约每秒刷一次盘。the contents of the InnoDB log buffer are written to the log file after each transaction commit and the log file is flushed to disk approximately once per second.由于进程调度问题，每秒刷一次盘不保证100%的都能发生。Once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues. 因为innodb_flush_log_at_timeout.默认是1秒，操作系统crash或电源故障导致丢失最后一秒的事务。 InnoDB log 的刷新频率是由 innodb_flush_log_at_timeout 控制的, 这个值默认是1单位秒.以下两种情况不受innodb_flush_log_at_trx_commit的限制: DDL和其他InnoDB内部活动如果要刷新InnoDB日志的话 InnoDB crash recovery 工作，事务要么整个被应用要么整个被回滚当然这里还有另外一个比较重要的参数innodb_flush_method，打开连接就可以了，在这里就不再说了。 binlog的控制参数是sync_binlog默认参数是0。如果这个值比0大，在sync_binlog把commit groups提交到二进制日志，MySQL Server就会指定通过调用fdatasync()的方式把binlog同步到磁盘。值描述 0 不用同步到磁盘。在这种情况下，MySQL Server就依赖操作系统像刷新其他文件一样，不时地刷新binlog日志。 1 最安全的选择，当MySQL crash后，在binlog里面最多丢失一个commit group的数据。然而，这也是最慢的选择（除非磁盘有个电池cache，可以让同步更快）。对于InnoDB事务最安全的做法是: sync_binlog=1 innodb_flush_log_at_trx_commit=1 下面的情况可以关闭innodb_support_xa,即innodb_support_xa=false: 可以接受在服务器上只有一个线程修改数据，对于InnoDB表为了提高性能可以关闭这个选项当只有一个SQL线程修改数据的时候，可以在slave上关闭innodb_support_xa 不需要保证binlog或者主备复制的数据安全，不需要一个外部的XA事务管理者时，你可以关闭这个选项参考 https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_support_xahttps://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_xahttps://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#sysvar_sync_binloghttps://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commithttp://mysqlmusings.blogspot.com/2010/04/binary-log-group-commit-implementation.htmlhttps://linux.cn/article-1157-2.html

发表了文章 2018-02-02

Waiting for Slave Workers to free pending events

今天处理一个主备延迟的问题，看到有这个状态 Slave_SQL_Running_State : Waiting for Slave Workers to free pending events 除了大事务外，这个等待状态的意思是：当事件的大小超过了slave_pending_jobs_size_max的大小，而当时间大小低于slave_pending_jobs_size_max的限制时调度器才会恢复调度。这种情况只会发生在slave_parallel_workers大于0时。当 stop slave; set global slave_parallel_workers=0; start slave; 参考 https://docs.oracle.com/cd/E17952_01/mysql-5.7-en/slave-sql-thread-states.html

发表了文章 2018-02-01

如何获取InnoDB树的高度

前言作为DBA了解InnoDB的页组织方式是最基础的，在实际工作中，免不了会评估SQL会消耗多少IO，怎么评估呢？作为InnoDB表和树的高度或者深度有关系。查看树的高度? 之前研究了半天：https://www.percona.com/blog/2009/04/28/the_depth_of_a_b_tree/ http://code.openark.org/blog/mysql/the-depth-of-an-index-primer 根据 Scholmi notes that there are two main features determining the depth of a B-tree (or B+-tree): The number of rows in the database. We’ll call that N. The size of the indexed key. Let’s call B the number of key that fit in a B-tree node. (Sometimes B is used to refer to the node size itself, rather than the number of keys it holds, but I hope my choice will make sense directly.) Given these quantities, the depth of a B-tree is logB N, give or take a little. That’s just (log N)/log B. Now we can rephrase Scholmi’s point as noting that small keys means a bigger B, which reduces (log N)/log B. If we cut the key size in half, then the depth of the B-tree goes from (log N)/log B to (log N)/log 2B (twice as many keys fit in the tree nodes), and that’s just (log N)/(1+log B). Let’s put some numbers in there. Say you have a billion rows, and you can currently fit 64 keys in a node. Then the depth of the tree is (log 109)/ log 64 ≈ 30/6 = 5. Now you rebuild the tree with keys half the size and you get log 109 / log 128 ≈ 30/7 = 4.3. Assuming the top 3 levels of the tree are in memory, then you go from 2 disk seeks on average to 1.3 disk seeks on average, for a 35% speedup. 里面算的不对，人肉算也没达到这个高度。也可能是我没有理解作者的意思，没有用对公式。那么根据结合前面的Innodb页结构，如何正确的获取数的高度呢？继续拿这个表举例子： mysql> show create table sbtest1\G *************************** 1. row *************************** Table: sbtest1 Create Table: CREATE TABLE `sbtest1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `gmt_create` datetime NOT NULL, `gmt_modified` datetime NOT NULL, `k` int(11) NOT NULL DEFAULT '0', `c` varchar(500) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', `is_used` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `k_1` (`k`), KEY `idx_is_used` (`is_used`), KEY `idx_gmt_create` (`gmt_create`) ) ENGINE=InnoDB AUTO_INCREMENT=69313841 DEFAULT CHARSET=latin1 1 row in set (0.00 sec) 方法一 [root@localhost mysql]# innodb_space -f test/sbtest1.ibd space-index-pages-summary | head -n 10 page index level data free records 3 74 2 5166 10904 369 4 75 2 408 15834 24 5 76 2 486 15756 27 6 77 2 486 15756 27 7 74 0 15028 1192 68 8 74 0 15028 1192 68 9 74 1 14700 1030 1050 10 74 0 15028 1192 68 11 74 0 15028 1192 68 page_level是2，所以这个树高度是page_level+1=3; 方法二 mysql> show global variables like "%innodb_page%"; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | innodb_page_cleaners | 1 | | innodb_page_size | 16384 | +----------------------+-------+ 2 rows in set (0.00 sec) mysql> show table status like 'sbtest1'\G *************************** 1. row *************************** Name: sbtest1 Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 25926320 Avg_row_length: 279 Data_length: 7254032384 Max_data_length: 0 Index_length: 1293697024 Data_free: 3145728 Auto_increment: 69313841 Create_time: 2018-01-19 14:53:11 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) mysql> desc sbtest1; +--------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | gmt_create | datetime | NO | MUL | NULL | | | gmt_modified | datetime | NO | | NULL | | | k | int(11) | NO | MUL | 0 | | | c | varchar(500) | NO | | | | | pad | char(60) | NO | | | | | is_used | int(11) | YES | MUL | NULL | | +--------------+--------------+------+-----+---------+----------------+ 通常一棵B+树可以存放多少行数据？这里我们先假设B+树高为2，即存在一个根节点和若干个叶子节点，那么这棵B+树的存放总记录数为：根节点指针数*单个叶子节点记录行数。上文我们已经说明单个叶子节点（页）中的记录数=16K/279=58。（我们从上面可以看到每行记录的数据平均大小为279个字节）。那么现在我们需要计算出非叶子节点能存放多少指针，其实这也很好算，表中的主键ID为int类型，长度为4字节，而指针大小在InnoDB源码中设置为6字节，这样一共10字节，我们一个页中能存放多少这样的单元，其实就代表有多少指针，即16384/10=1638。那么可以算出一棵高度为2的B+树，能存放1638*58=95004条这样的数据记录。根据同样的原理我们可以算出一个高度为3的B+树可以存放：1638*1638*58=155616552条这样的记录。高度为4的B+树可以存放： 1638*1638*1638*58=254899912176条这样的记录。而在实际应用中，大部分是以bigint作为主键的，主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节，我们一个页中能存放多少这样的单元，其实就代表有多少指针，即16384/14=1170。根据同样的原理我们可以算出一个高度为2，3，4的B+树能够存放的记录数。 mysql> select count(*) from sbtest1; +----------+ | count(*) | +----------+ | 26313272 | +----------+ 1 row in set (4.23 sec) 我们的表一共是3层。方法三 mysql> SELECT b.name, a.name, index_id, type, a.space, a.PAGE_NO FROM information_schema.INNODB_SYS_INDEXES a, information_schema.INNODB_SYS_TABLES b WHERE a.table_id = b.table_id AND a.space <> 0 and b.name='test/sbtest1'; +--------------+----------------+----------+------+-------+---------+ | name | name | index_id | type | space | PAGE_NO | +--------------+----------------+----------+------+-------+---------+ | test/sbtest1 | PRIMARY | 74 | 3 | 45 | 3 | | test/sbtest1 | k_1 | 75 | 0 | 45 | 4 | | test/sbtest1 | idx_is_used | 76 | 0 | 45 | 5 | | test/sbtest1 | idx_gmt_create | 77 | 0 | 45 | 6 | +--------------+----------------+----------+------+-------+---------+ 4 rows in set (0.00 sec) primary key的高度是3，其他索引的可以看上表。方法四因为主键索引B+树的根页在整个表空间文件中的第3个页开始，所以可以算出它在文件中的偏移量：16384*3=49152（16384为页大小）。另外根据《InnoDB存储引擎》中描述在根页的64偏移量位置前2个字节，保存了page level的值，因此我们想要的page level的值在整个文件中的偏移量为：16384*3+64=49152+64=49216，前2个字节中。接下来我们用hexdump工具，查看表空间文件指定偏移量上的数据： [root@localhost test]# hexdump -s 49216 -n 10 sbtest1.ibd 000c040 0200 0000 0000 0000 4a00 000c04a page_level是2,B+树高度为page level+1=3 如果通过二级索引查找记录最多需要花费多少次IO呢？从上面的图中可以看出需要花费：从二级索引找到主键+主键找到记录，比如二级索引有3层，聚簇索引有3层，那么最多花费的IO次数是：3+3=6 参考姜承尧《MySQL技术内幕:InnoDB存储引擎》姜承尧 http://www.innomysql.com/查看-innodb表中每个的索引高度/https://blog.jcole.us/2013/01/10/btree-index-structures-in-innodb/?spm=a2c4e.11153940.0.0.77d994fcZH1hIv

发表了文章 2018-02-01

解读InnoDB页面--索引结构

前言很久之前了解到innodb_space，觉得很强大，可视化了InnoDB页面，但是没有形成文档，总结出来，有一些经常被问到的东东可以用这个查看了。环境 centos 7 Linux 3.10.0-693.2.2.el7.x86_64 #1 SMP Tue Sep 12 22:26:13 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux Server version: 5.7.20 MySQL Community Server (GPL) 安装 sudo yum install rubygems ruby-devel sudo gem install innodb_ruby 解读总述选项 -f 加载表空间，如ibd文件 -s 加载系统表空间，如ibd -T 指定表名 -I 指定索引名继续拿这个表做例子: mysql> show create table sbtest1\G *************************** 1. row *************************** Table: sbtest1 Create Table: CREATE TABLE `sbtest1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `gmt_create` datetime NOT NULL, `gmt_modified` datetime NOT NULL, `k` int(11) NOT NULL DEFAULT '0', `c` varchar(500) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', `is_used` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `k_1` (`k`), KEY `idx_is_used` (`is_used`), KEY `idx_gmt_create` (`gmt_create`) ) ENGINE=InnoDB AUTO_INCREMENT=69313841 DEFAULT CHARSET=latin1 1 row in set (0.00 sec) 索引结构 index-recurse [root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -I PRIMARY index-recurse | more ROOT NODE #3: 369 records, 5166 bytes NODE POINTER RECORD ≥ (id=43000569) → #9 INTERNAL NODE #9: 1050 records, 14700 bytes NODE POINTER RECORD ≥ (id=43000569) → #7 LEAF NODE #7: 68 records, 15028 bytes RECORD: (id=43000569) → (gmt_create="184616470-38-96 24:80:37", gmt_modified="-891360229-11-75 00:49:77", k=-1271582413, c="73586-15688153734-79729593694-96509299839-83724898275-8671183 3539-78981337422-35049690573-51724173961-87474696253989966", pad="21624-36689827414-04092488557-09587706818-65008859162 \x80\x00\x00\x00w\x00", is_used=-2147477504) RECORD: (id=43000570) → (gmt_create="184616470-38-96 24:80:37", gmt_modified="-891360231-94-84 49:64:33", k=-1271451079, c="70079-70972780322-70018558993-71769650003-09270326047-3241701 2031-10768856803-14235120402-93989080412-18690312264047768", pad="26683-45880822084-77922711547-29057964468-76514263618 \x80\x00\x00\x00w\x00", is_used=-2147475456) RECORD: (id=43000571) → (gmt_create="184616470-38-96 24:80:37", gmt_modified="-891360229-49-27 20:33:85", k=-1288227272, c="27441-24903985029-56844662308-79012577859-40518387141-6058841 9212-24399130405-42612257832-29494881732-71506024440268430", pad="35807-96849339132-53943793991-69741192222-48634174017 \x80\x00\x00\x00w\x00", is_used=-2080364544) ... index-record-offsets [root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -I PRIMARY index-record-offsets | more page_offset record_offset 7 127 7 348 7 569 7 790 7 1011 7 1232 7 1453 7 1674 7 1895 7 2116 ... index-level-summary [root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -I PRIMARY -l 0 index-level-summary | more page index level data free records min_key 7 74 0 15028 1192 68 id=43000569 8 74 0 15028 1192 68 id=43000637 10 74 0 15028 1192 68 id=43000705 11 74 0 15028 1192 68 id=43000773 12 74 0 15028 1192 68 id=43000841 13 74 0 15028 1192 68 id=43000909 14 74 0 15028 1192 68 id=43000977 15 74 0 15028 1192 68 id=43001045 16 74 0 15028 1192 68 id=43001113 17 74 0 15028 1192 68 id=43001181 18 74 0 15028 1192 68 id=43001249 19 74 0 15028 1192 68 id=43001317 ... 记录结构 record-dump [root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 7 -R 128 record-dump Record at offset 128 Header: Next record offset : -8702 Heap number : 512 Type : conventional Deleted : false Length : 7 System fields: Transaction ID: 38377940 Roll Pointer: Undo Log: page 112897, offset 4249 Rollback Segment ID: 0 Insert: false Key fields: id: 270727424 Non-key fields: gmt_create: "222619031-78-45 93:12:68" gmt_modified: "-370929284-39-30 89:87:40" k: -1255066825 c: "" pad: "3586-15688153734-79729593694-96509299839-83724898275-8671183" is_used: -1288359111 记录历史 record-history [root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 7 -R 128 record-history Transaction Type Undo record [root@localhost mysql]# 其他拓展 space-lists [root@localhost mysql]# innodb_space -s ibdata1 space-lists name length f_page f_offset l_page l_offset free 619 32768 6918 0 438 free_frag 5 0 398 0 318 full_frag 2 0 158 0 278 full_inodes 1 2 38 2 38 free_inodes 2 322 38 243 38 [root@localhost mysql]# space-list-iterate [root@localhost mysql]# innodb_space -s ibdata1 space-list-iterate -L free_frag start_page page_used_bitmap 384 #############........###################################........ 16384 ##.............................................................. 32768 ##.............................................................. 320 ####################################################.######.##.. 256 #############################.##.#..##...###..#.######..####.##. [root@localhost mysql]# space-inodes-summary [root@localhost mysql]# innodb_space -s ibdata1 space-inodes-summary INODE fseg_id=1, pages=3552, frag=32, full=47, not_full=8, free=0 INODE fseg_id=2, pages=1, frag=1, full=0, not_full=0, free=0 INODE fseg_id=3, pages=1, frag=1, full=0, not_full=0, free=0 INODE fseg_id=4, pages=1, frag=1, full=0, not_full=0, free=0 INODE fseg_id=5, pages=1, frag=1, full=0, not_full=0, free=0 INODE fseg_id=6, pages=0, frag=0, full=0, not_full=0, free=0 INODE fseg_id=7, pages=1, frag=1, full=0, not_full=0, free=0 INODE fseg_id=8, pages=0, frag=0, full=0, not_full=0, free=0 INODE fseg_id=9, pages=1, frag=1, full=0, not_full=0, free=0 INODE fseg_id=10, pages=0, frag=0, full=0, not_full=0, free=0 INODE fseg_id=11, pages=1, frag=1, full=0, not_full=0, free=0 INODE fseg_id=12, pages=0, frag=0, full=0, not_full=0, free=0 INODE fseg_id=13, pages=1, frag=1, full=0, not_full=0, free=0 INODE fseg_id=14, pages=0, frag=0, full=0, not_full=0, free=0 INODE fseg_id=15, pages=160, frag=32, full=2, not_full=0, free=0 INODE fseg_id=16, pages=1, frag=1, full=0, not_full=0, free=0 ... undo-history-summary [root@izhp3j8nb7nyq2g923r9fvz mysql]# innodb_space -s ibdata1 undo-history-summary Page Offset Transaction Type Table 345 2452 162569 delete 345 2512 162600 delete 345 2598 162601 update_deleted 345 2675 162569 update_existing SYS_TABLESPACES 345 2716 162569 update_existing SYS_DATAFILES 345 2763 162600 update_existing SYS_TABLESPACES 345 2817 162600 update_existing SYS_DATAFILES 347 2859 162595 delete mysql/innodb_table_stats 284 2925 162581 delete mysql/innodb_index_stats 284 3020 162581 delete mysql/innodb_index_stats 284 3115 162581 delete mysql/innodb_index_stats 284 3194 162597 delete mysql/innodb_index_stats 284 3303 162597 delete mysql/innodb_index_stats 284 3412 162597 delete mysql/innodb_index_stats 284 3521 162597 delete mysql/innodb_index_stats 284 3614 162589 delete mysql/innodb_index_stats 284 3717 162589 delete mysql/innodb_index_stats 284 3820 162589 delete mysql/innodb_index_stats 284 3923 162589 delete mysql/innodb_index_stats 284 4010 162581 delete mysql/innodb_index_stats 284 4097 162581 delete mysql/innodb_index_stats 284 4184 162581 delete mysql/innodb_index_stats 284 4271 162581 delete mysql/innodb_index_stats 312 3626 162568 delete 312 3679 162568 delete 312 3740 162568 delete ... undo-record-dump Print a detailed description of an undo record and the data it contains: [root@localhost mysql]# innodb_space -s ibdata1 -p page -R offset undo-record-dump /usr/local/share/gems/gems/innodb_ruby-0.9.15/bin/innodb_space:1582:in `undo_record_dump': undefined method `root' for nil:NilClass (NoMethodError) from /usr/local/share/gems/gems/innodb_ruby-0.9.15/bin/innodb_space:2055:in `<top (required)>' from /usr/local/bin/innodb_space:23:in `load' from /usr/local/bin/innodb_space:23:in `<main>' 引用ruby库 $ irb -r innodb > sys = Innodb::System.new("ibdata1") > idx = sys.index_by_name("sakila/film", "PRIMARY") > rec = idx.binary_search([1]) 参考 https://github.com/jeremycole/innodb_ruby/wiki 开发原理https://blog.jcole.us/2013/01/10/btree-index-structures-in-innodb/

发表了文章 2018-02-01

解读InnodDB页面--普通页结构

前言很久之前了解到innodb_space，觉得很强大，可视化了InnoDB页面，但是没有形成文档，总结出来，有一些经常被问到的东东可以用这个查看了。环境 centos 7 Linux 3.10.0-693.2.2.el7.x86_64 #1 SMP Tue Sep 12 22:26:13 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux Server version: 5.7.20 MySQL Community Server (GPL) 安装 sudo yum install rubygems ruby-devel sudo gem install innodb_ruby 解读总述选项 -f 加载表空间，如ibd文件 -s 加载系统表空间，如ibd -T 指定表名 -I 指定索引名继续拿这个表做例子: mysql> show create table sbtest1\G *************************** 1. row *************************** Table: sbtest1 Create Table: CREATE TABLE `sbtest1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `gmt_create` datetime NOT NULL, `gmt_modified` datetime NOT NULL, `k` int(11) NOT NULL DEFAULT '0', `c` varchar(500) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', `is_used` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `k_1` (`k`), KEY `idx_is_used` (`is_used`), KEY `idx_gmt_create` (`gmt_create`) ) ENGINE=InnoDB AUTO_INCREMENT=69313841 DEFAULT CHARSET=latin1 1 row in set (0.00 sec) 页结构 page-account [root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 1 page-account Accounting for page 1: Page type is IBUF_BITMAP (Insert buffer bitmap, bookkeeping for insert buffer writes to be merged). Extent descriptor for pages 0-63 is at page 0, offset 158. Extent is not fully allocated to an fseg; may be a fragment extent. Page is marked as used in extent descriptor. Extent is in full_frag list of space. [root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 2 page-account Accounting for page 2: Page type is INODE (File segment inode, bookkeeping for file segments). Extent descriptor for pages 0-63 is at page 0, offset 158. Extent is not fully allocated to an fseg; may be a fragment extent. Page is marked as used in extent descriptor. Extent is in full_frag list of space. [root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 3 page-account Accounting for page 3: Page type is INDEX (B+Tree index, table and index data stored in B+Tree structure). Extent descriptor for pages 0-63 is at page 0, offset 158. Extent is not fully allocated to an fseg; may be a fragment extent. Page is marked as used in extent descriptor. Extent is in full_frag list of space. Page is in fragment array of fseg 1. Fseg is in internal fseg of index 74. Index root is page 3. Index is test/sbtest1.PRIMARY. [root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 4 page-account Accounting for page 4: Page type is INDEX (B+Tree index, table and index data stored in B+Tree structure). Extent descriptor for pages 0-63 is at page 0, offset 158. Extent is not fully allocated to an fseg; may be a fragment extent. Page is marked as used in extent descriptor. Extent is in full_frag list of space. Page is in fragment array of fseg 3. Fseg is in internal fseg of index 75. Index root is page 4. Index is test/sbtest1.k_1. [root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 5 page-account Accounting for page 5: Page type is INDEX (B+Tree index, table and index data stored in B+Tree structure). Extent descriptor for pages 0-63 is at page 0, offset 158. Extent is not fully allocated to an fseg; may be a fragment extent. Page is marked as used in extent descriptor. Extent is in full_frag list of space. Page is in fragment array of fseg 5. Fseg is in internal fseg of index 76. Index root is page 5. Index is test/sbtest1.idx_is_used. [root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 6 page-account Accounting for page 6: Page type is INDEX (B+Tree index, table and index data stored in B+Tree structure). Extent descriptor for pages 0-63 is at page 0, offset 158. Extent is not fully allocated to an fseg; may be a fragment extent. Page is marked as used in extent descriptor. Extent is in full_frag list of space. Page is in fragment array of fseg 7. Fseg is in internal fseg of index 77. Index root is page 6. Index is test/sbtest1.idx_gmt_create. [root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 7 page-account Accounting for page 7: Page type is INDEX (B+Tree index, table and index data stored in B+Tree structure). Extent descriptor for pages 0-63 is at page 0, offset 158. Extent is not fully allocated to an fseg; may be a fragment extent. Page is marked as used in extent descriptor. Extent is in full_frag list of space. Page is in fragment array of fseg 2. Fseg is in leaf fseg of index 74. Index root is page 3. Index is test/sbtest1.PRIMARY. [root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 8 page-account Accounting for page 8: Page type is INDEX (B+Tree index, table and index data stored in B+Tree structure). Extent descriptor for pages 0-63 is at page 0, offset 158. Extent is not fully allocated to an fseg; may be a fragment extent. Page is marked as used in extent descriptor. Extent is in full_frag list of space. Page is in fragment array of fseg 2. Fseg is in leaf fseg of index 74. Index root is page 3. Index is test/sbtest1.PRIMARY. [root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 9 page-account Accounting for page 9: Page type is INDEX (B+Tree index, table and index data stored in B+Tree structure). Extent descriptor for pages 0-63 is at page 0, offset 158. Extent is not fully allocated to an fseg; may be a fragment extent. Page is marked as used in extent descriptor. Extent is in full_frag list of space. Page is in fragment array of fseg 1. Fseg is in internal fseg of index 74. Index root is page 3. Index is test/sbtest1.PRIMARY. page-dump [root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 1 page-dump | more #<Innodb::Page::IbufBitmap:0x00000001a58678>: fil header: {:checksum=>2988774564, :offset=>1, :prev=>0, :next=>0, :lsn=>114063786483, :type=>:IBUF_BITMAP, :flush_lsn=>0, :space_id=>45} fil trailer: {:checksum=>2988774564, :lsn_low32=>2394636787} ibuf bitmap: Page 0: {:free=>0, :buffered=>false, :ibuf=>false} Page 1: {:free=>0, :buffered=>false, :ibuf=>false} Page 2: {:free=>0, :buffered=>false, :ibuf=>false} Page 3: {:free=>0, :buffered=>false, :ibuf=>false} Page 4: {:free=>0, :buffered=>false, :ibuf=>false} ... [root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 2 page-dump | more #<Innodb::Page::Inode:0x00000001e4c450>: fil header: {:checksum=>2394379754, :offset=>2, :prev=>0, :next=>0, :lsn=>114066783250, :type=>:INODE, :flush_lsn=>0, :space_id=>45} fil trailer: {:checksum=>2394379754, :lsn_low32=>2397633554} list entry: {:prev=>nil, :next=>nil} inodes: {:offset=>50, :fseg_id=>1, :not_full_n_used=>19, :free=> #<Innodb::List::Xdes:0x00000001e29108 @base={:length=>0, :first=>nil, :last=>nil}, @space= <Innodb::Space file="test/sbtest1.ibd", page_size=16384, pages=529152>>, :not_full=> #<Innodb::List::Xdes:0x00000001e23be0 @base= {:length=>2, :first=>{:page=>360448, :offset=>6238}, :last=>{:page=>425984, :offset=>7478}}, @space= <Innodb::Space file="test/sbtest1.ibd", page_size=16384, pages=529152>>, :full=> #<Innodb::List::Xdes:0x00000001e22290 @base= {:length=>5, :first=>{:page=>32768, :offset=>2318}, :last=>{:page=>294912, :offset=>4998}}, @space= <Innodb::Space file="test/sbtest1.ibd", page_size=16384, pages=529152>>, :magic_n=>97937874, :frag_array=> [3, 9, 40, nil, 42, 43, 44, ... 61, 62, 63, 16386, 16387, 16388, 16389, 16390, 16391]} {:offset=>242, :fseg_id=>2, :not_full_n_used=>880, :free=> #<Innodb::List::Xdes:0x00000001dd9fe0 @base={:length=>0, :first=>nil, :last=>nil}, @space= <Innodb::Space file="test/sbtest1.ibd", page_size=16384, pages=529152>>, :not_full=> #<Innodb::List::Xdes:0x00000001dd8690 @base= {:length=>878, :first=>{:page=>376832, :offset=>7118}, :last=>{:page=>442368, :offset=>1438}}, @space= <Innodb::Space file="test/sbtest1.ibd", page_size=16384, pages=529152>>, :full=> #<Innodb::List::Xdes:0x00000001dd30c8 @base= {:length=>6032, :first=>{:page=>0, :offset=>198}, :last=>{:page=>376832, :offset=>7078}}, @space= <Innodb::Space file="test/sbtest1.ibd", page_size=16384, pages=529152>>, :magic_n=>97937874, :frag_array=> [7, 8, 10, 11, 12, 13, 14, ... 35, 36, 37, 38, 39]} {:offset=>434, :fseg_id=>3, :not_full_n_used=>0, :free=> #<Innodb::List::Xdes:0x00000001d8afd0 @base={:length=>0, :first=>nil, :last=>nil}, @space= <Innodb::Space file="test/sbtest1.ibd", page_size=16384, pages=529152>>, :not_full=> #<Innodb::List::Xdes:0x00000001d896a8 @base={:length=>0, :first=>nil, :last=>nil}, @space= <Innodb::Space file="test/sbtest1.ibd", page_size=16384, pages=529152>>, :full=> #<Innodb::List::Xdes:0x00000001d819f8 @base={:length=>0, :first=>nil, :last=>nil}, @space= <Innodb::Space file="test/sbtest1.ibd", page_size=16384, pages=529152>>, :magic_n=>97937874, :frag_array=> [4, 16393, 16424, nil, 16426, 16427, 16428, 16440, 16441, 16442, 16443, 16444, 16445, 16446, 16447, nil, nil, nil, nil, nil, nil]} {:offset=>626, :fseg_id=>4, :not_full_n_used=>82, :free=> #<Innodb::List::Xdes:0x00000001d43360 @base={:length=>0, :first=>nil, :last=>nil}, @space= <Innodb::Space file="test/sbtest1.ibd", page_size=16384, pages=529152>>, :not_full=> #<Innodb::List::Xdes:0x00000001d419e8 @base= {:length=>51, :first=>{:page=>458752, :offset=>4878}, :last=>{:page=>458752, :offset=>6878}}, @space= <Innodb::Space file="test/sbtest1.ibd", page_size=16384, pages=529152>>, :full=> #<Innodb::List::Xdes:0x00000001d40098 @base= {:length=>340, :first=>{:page=>442368, :offset=>1478}, :last=>{:page=>458752, :offset=>4838}}, @space= <Innodb::Space file="test/sbtest1.ibd", page_size=16384, pages=529152>>, :magic_n=>97937874, :frag_array=> [41, 16392, 16394, ..... page-records [root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 3 page-records | more Record 126: (id=43000569) → #9 Record 140: (id=43071969) → #40 Record 154: (id=43143369) → #42 Record 168: (id=43214769) → #43 Record 182: (id=43286169) → #44 Record 196: (id=43357569) → #45 Record 210: (id=43428969) → #46 Record 224: (id=43500369) → #47 Record 238: (id=43571769) → #48 Record 252: (id=43643169) → #49 Record 266: (id=43714569) → #50 Record 280: (id=43785969) → #51 Record 294: (id=43857369) → #52 Record 308: (id=43928769) → #53 Record 322: (id=44000169) → #54 Record 336: (id=44071569) → #55 Record 350: (id=44142969) → #56 Record 364: (id=44214369) → #57 Record 378: (id=44285769) → #58 Record 392: (id=44357169) → #59 Record 406: (id=44428569) → #60 Record 420: (id=44499969) → #61 Record 434: (id=44571369) → #62 Record 448: (id=44642769) → #63 Record 462: (id=44714169) → #16386 Record 476: (id=44785569) → #16387 Record 490: (id=44856969) → #16388 Record 504: (id=44928369) → #16389 Record 518: (id=44999769) → #16390 Record 532: (id=45071169) → #16391 Record 546: (id=45142569) → #36224 Record 560: (id=45213969) → #36225 Record 574: (id=45285369) → #36226 ... page-directory-summary [root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 5 page-directory-summary slot offset type owned key 0 99 infimum 1 1 180 node_pointer 4 (is_used=0) 2 252 node_pointer 4 (is_used=0) 3 324 node_pointer 4 (is_used=0) 4 396 node_pointer 4 (is_used=0) 5 468 node_pointer 4 (is_used=0) 6 112 supremum 8 [root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 6 page-directory-summary slot offset type owned key 0 99 infimum 1 1 179 node_pointer 4 (gmt_create="184616475-42-59 95:08:86") 2 251 node_pointer 4 (gmt_create="184616479-25-95 90:48:11") 3 323 node_pointer 4 (gmt_create="184616483-40-85 97:53:44") 4 395 node_pointer 4 (gmt_create="184616486-58-28 48:33:81") 5 467 node_pointer 4 (gmt_create="184616490-05-40 55:86:50") 6 112 supremum 8 [root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 7 page-directory-summary slot offset type owned key 0 99 infimum 1 1 790 conventional 4 (id=43000572) 2 1674 conventional 4 (id=43000576) 3 2558 conventional 4 (id=43000580) 4 3442 conventional 4 (id=43000584) 5 4326 conventional 4 (id=43000588) 6 5210 conventional 4 (id=43000592) 7 6094 conventional 4 (id=43000596) 8 6978 conventional 4 (id=43000600) 9 7862 conventional 4 (id=43000604) 10 8746 conventional 4 (id=43000608) 11 9630 conventional 4 (id=43000612) 12 10514 conventional 4 (id=43000616) 13 11398 conventional 4 (id=43000620) 14 12282 conventional 4 (id=43000624) 15 13166 conventional 4 (id=43000628) 16 14050 conventional 4 (id=43000632) 17 112 supremum 5 page-illustrate [root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 6 page-illustrate ... 索引结构 index-recurse [root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -I PRIMARY index-recurse | more ROOT NODE #3: 369 records, 5166 bytes NODE POINTER RECORD ≥ (id=43000569) → #9 INTERNAL NODE #9: 1050 records, 14700 bytes NODE POINTER RECORD ≥ (id=43000569) → #7 LEAF NODE #7: 68 records, 15028 bytes RECORD: (id=43000569) → (gmt_create="184616470-38-96 24:80:37", gmt_modified="-891360229-11-75 00:49:77", k=-1271582413, c="73586-15688153734-79729593694-96509299839-83724898275-8671183 3539-78981337422-35049690573-51724173961-87474696253989966", pad="21624-36689827414-04092488557-09587706818-65008859162 \x80\x00\x00\x00w\x00", is_used=-2147477504) RECORD: (id=43000570) → (gmt_create="184616470-38-96 24:80:37", gmt_modified="-891360231-94-84 49:64:33", k=-1271451079, c="70079-70972780322-70018558993-71769650003-09270326047-3241701 2031-10768856803-14235120402-93989080412-18690312264047768", pad="26683-45880822084-77922711547-29057964468-76514263618 \x80\x00\x00\x00w\x00", is_used=-2147475456) RECORD: (id=43000571) → (gmt_create="184616470-38-96 24:80:37", gmt_modified="-891360229-49-27 20:33:85", k=-1288227272, c="27441-24903985029-56844662308-79012577859-40518387141-6058841 9212-24399130405-42612257832-29494881732-71506024440268430", pad="35807-96849339132-53943793991-69741192222-48634174017 \x80\x00\x00\x00w\x00", is_used=-2080364544) ... index-record-offsets [root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -I PRIMARY index-record-offsets | more page_offset record_offset 7 127 7 348 7 569 7 790 7 1011 7 1232 7 1453 7 1674 7 1895 7 2116 ... index-level-summary [root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -I PRIMARY -l 0 index-level-summary | more page index level data free records min_key 7 74 0 15028 1192 68 id=43000569 8 74 0 15028 1192 68 id=43000637 10 74 0 15028 1192 68 id=43000705 11 74 0 15028 1192 68 id=43000773 12 74 0 15028 1192 68 id=43000841 13 74 0 15028 1192 68 id=43000909 14 74 0 15028 1192 68 id=43000977 15 74 0 15028 1192 68 id=43001045 16 74 0 15028 1192 68 id=43001113 17 74 0 15028 1192 68 id=43001181 18 74 0 15028 1192 68 id=43001249 19 74 0 15028 1192 68 id=43001317 ... 记录结构 record-dump [root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 7 -R 128 record-dump Record at offset 128 Header: Next record offset : -8702 Heap number : 512 Type : conventional Deleted : false Length : 7 System fields: Transaction ID: 38377940 Roll Pointer: Undo Log: page 112897, offset 4249 Rollback Segment ID: 0 Insert: false Key fields: id: 270727424 Non-key fields: gmt_create: "222619031-78-45 93:12:68" gmt_modified: "-370929284-39-30 89:87:40" k: -1255066825 c: "" pad: "3586-15688153734-79729593694-96509299839-83724898275-8671183" is_used: -1288359111 记录历史 record-history [root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 7 -R 128 record-history Transaction Type Undo record [root@localhost mysql]# 其他拓展 space-lists [root@localhost mysql]# innodb_space -s ibdata1 space-lists name length f_page f_offset l_page l_offset free 619 32768 6918 0 438 free_frag 5 0 398 0 318 full_frag 2 0 158 0 278 full_inodes 1 2 38 2 38 free_inodes 2 322 38 243 38 [root@localhost mysql]# space-list-iterate [root@localhost mysql]# innodb_space -s ibdata1 space-list-iterate -L free_frag start_page page_used_bitmap 384 #############........###################################........ 16384 ##.............................................................. 32768 ##.............................................................. 320 ####################################################.######.##.. 256 #############################.##.#..##...###..#.######..####.##. [root@localhost mysql]# space-inodes-summary [root@localhost mysql]# innodb_space -s ibdata1 space-inodes-summary INODE fseg_id=1, pages=3552, frag=32, full=47, not_full=8, free=0 INODE fseg_id=2, pages=1, frag=1, full=0, not_full=0, free=0 INODE fseg_id=3, pages=1, frag=1, full=0, not_full=0, free=0 INODE fseg_id=4, pages=1, frag=1, full=0, not_full=0, free=0 INODE fseg_id=5, pages=1, frag=1, full=0, not_full=0, free=0 INODE fseg_id=6, pages=0, frag=0, full=0, not_full=0, free=0 INODE fseg_id=7, pages=1, frag=1, full=0, not_full=0, free=0 INODE fseg_id=8, pages=0, frag=0, full=0, not_full=0, free=0 INODE fseg_id=9, pages=1, frag=1, full=0, not_full=0, free=0 INODE fseg_id=10, pages=0, frag=0, full=0, not_full=0, free=0 INODE fseg_id=11, pages=1, frag=1, full=0, not_full=0, free=0 INODE fseg_id=12, pages=0, frag=0, full=0, not_full=0, free=0 INODE fseg_id=13, pages=1, frag=1, full=0, not_full=0, free=0 INODE fseg_id=14, pages=0, frag=0, full=0, not_full=0, free=0 INODE fseg_id=15, pages=160, frag=32, full=2, not_full=0, free=0 INODE fseg_id=16, pages=1, frag=1, full=0, not_full=0, free=0 ... undo-history-summary [root@izhp3j8nb7nyq2g923r9fvz mysql]# innodb_space -s ibdata1 undo-history-summary Page Offset Transaction Type Table 345 2452 162569 delete 345 2512 162600 delete 345 2598 162601 update_deleted 345 2675 162569 update_existing SYS_TABLESPACES 345 2716 162569 update_existing SYS_DATAFILES 345 2763 162600 update_existing SYS_TABLESPACES 345 2817 162600 update_existing SYS_DATAFILES 347 2859 162595 delete mysql/innodb_table_stats 284 2925 162581 delete mysql/innodb_index_stats 284 3020 162581 delete mysql/innodb_index_stats 284 3115 162581 delete mysql/innodb_index_stats 284 3194 162597 delete mysql/innodb_index_stats 284 3303 162597 delete mysql/innodb_index_stats 284 3412 162597 delete mysql/innodb_index_stats 284 3521 162597 delete mysql/innodb_index_stats 284 3614 162589 delete mysql/innodb_index_stats 284 3717 162589 delete mysql/innodb_index_stats 284 3820 162589 delete mysql/innodb_index_stats 284 3923 162589 delete mysql/innodb_index_stats 284 4010 162581 delete mysql/innodb_index_stats 284 4097 162581 delete mysql/innodb_index_stats 284 4184 162581 delete mysql/innodb_index_stats 284 4271 162581 delete mysql/innodb_index_stats 312 3626 162568 delete 312 3679 162568 delete 312 3740 162568 delete ... undo-record-dump Print a detailed description of an undo record and the data it contains: [root@localhost mysql]# innodb_space -s ibdata1 -p page -R offset undo-record-dump /usr/local/share/gems/gems/innodb_ruby-0.9.15/bin/innodb_space:1582:in `undo_record_dump': undefined method `root' for nil:NilClass (NoMethodError) from /usr/local/share/gems/gems/innodb_ruby-0.9.15/bin/innodb_space:2055:in `<top (required)>' from /usr/local/bin/innodb_space:23:in `load' from /usr/local/bin/innodb_space:23:in `<main>' 引用ruby库 $ irb -r innodb > sys = Innodb::System.new("ibdata1") > idx = sys.index_by_name("sakila/film", "PRIMARY") > rec = idx.binary_search([1]) 参考 https://github.com/jeremycole/innodb_ruby/wiki 开发原理https://blog.jcole.us/2013/01/10/btree-index-structures-in-innodb/

发表了文章 2018-01-26

解读InnoDB页面--系统文件结构

前言很久之前了解到innodb_space，觉得很强大，可视化了InnoDB页面，但是没有形成文档，总结出来，有一些经常被问到的东东可以用这个查看了。环境 centos 7 Linux izhp3j8nb7nyq2g923r9fvz 3.10.0-693.2.2.el7.x86_64 #1 SMP Tue Sep 12 22:26:13 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux Server version: 5.7.20 MySQL Community Server (GPL) 安装 sudo yum install rubygems ruby-devel sudo gem install innodb_ruby 解读总述选项 -f 加载表空间，如ibd文件 -s 加载系统表空间，如ibd -T 指定表名 -I 指定索引名继续拿这个表做例子: mysql> show create table sbtest1\G *************************** 1. row *************************** Table: sbtest1 Create Table: CREATE TABLE `sbtest1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `gmt_create` datetime NOT NULL, `gmt_modified` datetime NOT NULL, `k` int(11) NOT NULL DEFAULT '0', `c` varchar(500) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', `is_used` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `k_1` (`k`), KEY `idx_is_used` (`is_used`), KEY `idx_gmt_create` (`gmt_create`) ) ENGINE=InnoDB AUTO_INCREMENT=69313841 DEFAULT CHARSET=latin1 1 row in set (0.00 sec) 系统文件结构 system-spaces [root@localhost mysql]# innodb_space -s ibdata1 system-spaces name pages indexes (system) 45824 7 mysql/engine_cost 6 1 mysql/gtid_executed 6 1 mysql/help_category 7 2 mysql/help_keyword 15 2 mysql/help_relation 9 1 mysql/help_topic 576 2 mysql/innodb_index_stats 6 1 mysql/innodb_table_stats 6 1 mysql/plugin 6 1 mysql/server_cost 6 1 mysql/servers 6 1 mysql/slave_master_info 6 1 mysql/slave_relay_log_info 6 1 mysql/slave_worker_info 6 1 mysql/time_zone 6 1 mysql/time_zone_leap_second 6 1 mysql/time_zone_name 6 1 mysql/time_zone_transition 6 1 mysql/time_zone_transition_type 6 1 sys/sys_config 6 1 test/id2_d_license_device_key 6 1 test/sbtest1 529152 4 test/sbtest11 6 1 test/sbtest2 3135232 2 space-indexes [root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 space-indexes id name root fseg used allocated fill_factor 74 PRIMARY 3 internal 370 479 77.24% 74 PRIMARY 3 leaf 386960 442272 87.49% 75 k_1 4 internal 25 25 100.00% 75 k_1 4 leaf 21874 25056 87.30% 76 idx_is_used 5 internal 28 28 100.00% 76 idx_is_used 5 leaf 23494 26912 87.30% 77 idx_gmt_create 6 internal 28 28 100.00% 77 idx_gmt_create 6 leaf 23494 26912 87.30% space-page-type-regions 遍历空间中的所有页面，并将页面类型的摘要合并到同类型页面的“区域”中。这里罗列了这棵树有多少个节点，包括已经分配但是未使用的节点。 [root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 space-page-type-regions start end count type 0 0 1 FSP_HDR 1 1 1 IBUF_BITMAP 2 2 1 INODE 3 16383 16381 INDEX 16384 16384 1 XDES 16385 16385 1 IBUF_BITMAP 16386 16424 39 INDEX 16425 16425 1 FREE (INDEX) 16426 32767 16342 INDEX 32768 32768 1 XDES 32769 32769 1 IBUF_BITMAP 32770 49151 16382 INDEX 49152 49152 1 XDES 49153 49153 1 IBUF_BITMAP 49154 49184 31 INDEX 49185 49185 1 FREE (INDEX) 49186 49210 25 INDEX 49211 49215 5 FREE (ALLOCATED) 49216 65535 16320 INDEX 65536 65536 1 XDES 65537 65537 1 IBUF_BITMAP 65538 65599 62 FREE (ALLOCATED) 65600 81919 16320 INDEX 81920 81920 1 XDES 81921 81921 1 IBUF_BITMAP 81922 81983 62 FREE (ALLOCATED) ...... space-page-type-summary [root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 space-page-type-summary type count percent description INDEX 456275 86.23 B+Tree index ALLOCATED 72812 13.76 Freshly allocated IBUF_BITMAP 32 0.01 Insert buffer bitmap XDES 31 0.01 Extent descriptor INODE 1 0.00 File segment inode FSP_HDR 1 0.00 File space header space-index-pages-summary [root@localhost mysql]# innodb_space -f test/sbtest1.ibd space-index-pages-summary | head -n 10 page index level data free records 3 74 2 5166 10904 369 4 75 2 408 15834 24 5 76 2 486 15756 27 6 77 2 486 15756 27 7 74 0 15028 1192 68 8 74 0 15028 1192 68 9 74 1 14700 1030 1050 10 74 0 15028 1192 68 11 74 0 15028 1192 68 通过level列我们可以看到，这棵树高度是2。 space-extents-illustrate [root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 space-extents-illustrate .... space-lsn-age-illustrate 参考 https://github.com/jeremycole/innodb_ruby/wiki 开发原理https://blog.jcole.us/2013/01/10/btree-index-structures-in-innodb/

发表了文章 2018-01-25

如何计算指定的InnoDB索引大小

前言通常情况下，获取InnoDB索引的大小通常的方法是show table status，但是如果想获取指定的索引大小呢？通常情况下我们想看索引大小的话，用的是 show table status like ""\G 例1： mysql> show create table sbtest1\G *************************** 1. row *************************** Table: sbtest1 Create Table: CREATE TABLE `sbtest1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `gmt_create` datetime NOT NULL, `gmt_modified` datetime NOT NULL, `k` int(11) NOT NULL DEFAULT '0', `c` varchar(500) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', `is_used` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `k_1` (`k`), KEY `idx_is_used` (`is_used`), KEY `idx_gmt_create` (`gmt_create`) ) ENGINE=InnoDB AUTO_INCREMENT=69313841 DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> show table status like "sbtest1"\G *************************** 1. row *************************** Name: sbtest1 Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 25926320 Avg_row_length: 279 Data_length: 7254032384 Max_data_length: 0 Index_length: 1293697024 Data_free: 3145728 Auto_increment: 69313841 Create_time: 2018-01-19 14:53:11 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) mysql> show create table sbtest2\G *************************** 1. row *************************** Table: sbtest2 Create Table: CREATE TABLE `sbtest2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `gmt_create` datetime NOT NULL, `gmt_modified` datetime NOT NULL, `k` int(11) NOT NULL DEFAULT '0', `c` varchar(500) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', `is_used` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `k_1` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=200000001 DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> show table status like "sbtest2"\G *************************** 1. row *************************** Name: sbtest2 Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 197101536 Avg_row_length: 241 Data_length: 47530901504 Max_data_length: 0 Index_length: 3117400064 Data_free: 5242880 Auto_increment: 200000001 Create_time: 2018-01-18 14:40:45 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) 根据官方手册的说法 Index_length For MyISAM, Index_length is the length of the index file, in bytes. For InnoDB, Index_length is the approximate amount of memory allocated for non-clustered indexes, in bytes. Specifically, it is the sum of non-clustered index sizes, in pages, multiplied by the InnoDB page size. 对于InnoDB表，Index_length是非聚簇索引的大小总和。那如果想知道某个索引的大小该怎么办呢？有篇文章分享了一个方法： mysql> SELECT -> sum(stat_value) pages, -> index_name, -> sum(stat_value) * @@innodb_page_size size -> FROM -> mysql.innodb_index_stats -> WHERE -> table_name = 'sbtest1' -> AND database_name = 'test' -> AND stat_description = 'Number of pages in the index' -> GROUP BY -> index_name; +--------+----------------+------------+ | pages | index_name | size | +--------+----------------+------------+ | 442751 | PRIMARY | 7254032384 | | 26940 | idx_gmt_create | 441384960 | | 26940 | idx_is_used | 441384960 | | 25081 | k_1 | 410927104 | +--------+----------------+------------+ 4 rows in set (0.00 sec) mysql> select 441384960+441384960+410927104; +-------------------------------+ | 441384960+441384960+410927104 | +-------------------------------+ | 1293697024 | +-------------------------------+ 1 row in set (0.00 sec) mysql> SELECT -> sum(stat_value) pages, -> index_name, -> sum(stat_value) * @@innodb_page_size size -> FROM -> mysql.innodb_index_stats -> WHERE -> table_name = 'sbtest2' -> AND database_name = 'test' -> AND stat_description = 'Number of pages in the index' -> GROUP BY -> index_name; +---------+------------+-------------+ | pages | index_name | size | +---------+------------+-------------+ | 2901056 | PRIMARY | 47530901504 | | 190271 | k_1 | 3117400064 | +---------+------------+-------------+ 2 rows in set (0.00 sec) 在MySQL 5.6版本，表和索引相关的数据保存在mysql.innodb_table_stats and mysql.innodb_index_stats，这样MySQL可以快速的计算出结果。如果想获取精确结果的话，最好先： mysql> analyze table sbtest1; +--------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------+---------+----------+----------+ | test.sbtest1 | analyze | status | OK | +--------------+---------+----------+----------+ 1 row in set (0.01 sec) mysql> analyze table sbtest2; +--------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------+---------+----------+----------+ | test.sbtest2 | analyze | status | OK | +--------------+---------+----------+----------+ 1 row in set (1.10 sec) 这个用法对分区表同样适用： mysql> show create table t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `a` smallint(6) DEFAULT NULL, `b` smallint(6) DEFAULT NULL, `c` smallint(6) DEFAULT NULL, KEY `a` (`a`), KEY `b` (`b`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (c) PARTITIONS 4 */ 1 row in set (0.01 sec) ANALYZE TABLE t; SELECT sum(stat_value) pages, index_name, sum(stat_value) * @@innodb_page_size size FROM mysql.innodb_index_stats WHERE table_name LIKE 't#P%' AND database_name = 'test' AND stat_description LIKE 'Number of pages in the index' GROUP BY index_name; +-------+-----------------+-----------+ | pages | index_name | size | +-------+-----------------+-----------+ | 8848 | GEN_CLUST_INDEX | 144965632 | | 5004 | a | 81985536 | | 5004 | b | 81985536 | +-------+-----------------+-----------+ 3 rows in set (0.00 sec) mysql> SELECT sum(stat_value) pages, table_name part, index_name, sum(stat_value) * @@innodb_page_size size FROM mysql.innodb_index_stats WHERE table_name LIKE 't#P#%' AND database_name = 'test' AND stat_description LIKE 'Number of pages in the index' GROUP BY table_name, index_name; +-------+--------+-----------------+----------+ | pages | part | index_name | size | +-------+--------+-----------------+----------+ | 2212 | t#P#p0 | GEN_CLUST_INDEX | 36241408 | | 1251 | t#P#p0 | a | 20496384 | | 1251 | t#P#p0 | b | 20496384 | | 2212 | t#P#p1 | GEN_CLUST_INDEX | 36241408 | | 1251 | t#P#p1 | a | 20496384 | | 1251 | t#P#p1 | b | 20496384 | | 2212 | t#P#p2 | GEN_CLUST_INDEX | 36241408 | | 1251 | t#P#p2 | a | 20496384 | | 1251 | t#P#p2 | b | 20496384 | | 2212 | t#P#p3 | GEN_CLUST_INDEX | 36241408 | | 1251 | t#P#p3 | a | 20496384 | | 1251 | t#P#p3 | b | 20496384 | +-------+--------+-----------------+----------+ 12 rows in set (0.00 sec) 参考 https://dev.mysql.com/doc/refman/5.7/en/show-table-status.htmlhttp://aadant.com/blog/2014/02/04/how-to-calculate-a-specific-innodb-index-size/

发表了文章 2018-01-19

count(*) VS count(X)

背景在平时的工作中，有些同学对count的用法还是有疑惑的，为此我做个简单的总结和测试，希望对大家有帮助。 count(*)和count(X)是不等价的表达式含义 count(*) 返回总行数，包括空和非空值 count(expression) 返回expression中的非空值，例如count(1)或count(0)和count(*)等价 count(column) 只返回column的非空值不同类型的count的速度是不一样的因为MyISAM已经在一个表里缓存了表数据量，MyISAM可以很快的返回count(*)或者count(not null)的值，如果想count(column can be null)的话就会比较慢，因为count可以为null的列要遍历列的数据的。举个例子： show create table sbtest1\G *************************** 1. row *************************** Table: sbtest1 Create Table: CREATE TABLE `sbtest1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `gmt_create` datetime NOT NULL, `gmt_modified` datetime NOT NULL, `k` int(11) DEFAULT NULL, `c` varchar(500) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', `is_used` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `k_1` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=69313841 DEFAULT CHARSET=latin1 测试环境 Server version: 5.7.20 MySQL Community Server (GPL)32核 128G 执行计划 mysql> explain extended select count(*) from sbtest1; +----+-------------+---------+------------+-------+---------------+------+---------+------+----------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+------+---------+------+----------+----------+-------------+ | 1 | SIMPLE | sbtest1 | NULL | index | NULL | k_1 | 5 | NULL | 25926320 | 100.00 | Using index | +----+-------------+---------+------------+-------+---------------+------+---------+------+----------+----------+-------------+ 1 row in set, 2 warnings (0.00 sec) mysql> explain extended select count(0) from sbtest1; +----+-------------+---------+------------+-------+---------------+------+---------+------+----------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+------+---------+------+----------+----------+-------------+ | 1 | SIMPLE | sbtest1 | NULL | index | NULL | k_1 | 5 | NULL | 25926320 | 100.00 | Using index | +----+-------------+---------+------------+-------+---------------+------+---------+------+----------+----------+-------------+ 1 row in set, 2 warnings (0.00 sec) mysql> explain extended select count(1) from sbtest1; +----+-------------+---------+------------+-------+---------------+------+---------+------+----------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+------+---------+------+----------+----------+-------------+ | 1 | SIMPLE | sbtest1 | NULL | index | NULL | k_1 | 5 | NULL | 25926320 | 100.00 | Using index | +----+-------------+---------+------------+-------+---------------+------+---------+------+----------+----------+-------------+ 1 row in set, 2 warnings (0.00 sec) mysql> explain extended select count(id) from sbtest1; +----+-------------+---------+------------+-------+---------------+------+---------+------+----------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+------+---------+------+----------+----------+-------------+ | 1 | SIMPLE | sbtest1 | NULL | index | NULL | k_1 | 5 | NULL | 25926320 | 100.00 | Using index | +----+-------------+---------+------------+-------+---------------+------+---------+------+----------+----------+-------------+ 1 row in set, 2 warnings (0.00 sec) mysql> explain extended select count(k) from sbtest1; +----+-------------+---------+------------+-------+---------------+------+---------+------+----------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+------+---------+------+----------+----------+-------------+ | 1 | SIMPLE | sbtest1 | NULL | index | NULL | k_1 | 5 | NULL | 25926320 | 100.00 | Using index | +----+-------------+---------+------------+-------+---------------+------+---------+------+----------+----------+-------------+ 1 row in set, 2 warnings (0.00 sec) 测试的SQL [root@localhost ~]# mysqlslap -h 127.0.0.1 -P 3306 -u root -p'123456' --iterations=30 --concurrency=1 --number-of-queries=1 --create-schema='test' --query='select count(*) from sbtest1' Benchmark Average number of seconds to run all queries: 4.295 seconds Minimum number of seconds to run all queries: 4.197 seconds Maximum number of seconds to run all queries: 4.463 seconds Number of clients running queries: 1 Average number of queries per client: 1 [root@localhost ~]# mysqlslap -h 127.0.0.1 -P 3306 -u root -p'123456' --iterations=30 --concurrency=1 --number-of-queries=1 --create-schema='test' --query='select count(0) from sbtest1' Benchmark Average number of seconds to run all queries: 4.277 seconds Minimum number of seconds to run all queries: 4.192 seconds Maximum number of seconds to run all queries: 4.452 seconds Number of clients running queries: 1 Average number of queries per client: 1 [root@localhost ~]# mysqlslap -h 127.0.0.1 -P 3306 -u root -p'123456' --iterations=30 --concurrency=1 --number-of-queries=1 --create-schema='test' --query='select count(1) from sbtest1' Benchmark Average number of seconds to run all queries: 4.281 seconds Minimum number of seconds to run all queries: 4.188 seconds Maximum number of seconds to run all queries: 4.849 seconds Number of clients running queries: 1 Average number of queries per client: 1 [root@localhost ~]# mysqlslap -h 127.0.0.1 -P 3306 -u root -p'123456' --iterations=30 --concurrency=1 --number-of-queries=1 --create-schema='test' --query='select count(id) from sbtest1' Benchmark Average number of seconds to run all queries: 4.716 seconds Minimum number of seconds to run all queries: 4.631 seconds Maximum number of seconds to run all queries: 4.778 seconds Number of clients running queries: 1 Average number of queries per client: 1 [root@localhost ~]# mysqlslap -h 127.0.0.1 -P 3306 -u root -p'123456' --iterations=30 --concurrency=1 --number-of-queries=1 --create-schema='test' --query='select count(k) from sbtest1' Benchmark Average number of seconds to run all queries: 4.832 seconds Minimum number of seconds to run all queries: 4.739 seconds Maximum number of seconds to run all queries: 5.054 seconds Number of clients running queries: 1 Average number of queries per client: 1 从上面的测试可以看出查询速度：count(expression) > count(*) > count(column not null) > count( column can be null )值得说明的是:count(column not null) > count( column can be null ) 对于不同的列并不是绝对的，对于同一列的count(column not null) > count( column can be null )这个仍然是成立的。比如： mysql> alter table sbtest1 add index idx_is_used (is_used); Query OK, 0 rows affected (1 min 43.12 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table sbtest1 add index idx_gmt_create (gmt_create); Query OK, 0 rows affected (1 min 49.33 sec) Records: 0 Duplicates: 0 Warnings: 0 跑出来的结果是： [root@localhost ~]# mysqlslap -h 127.0.0.1 -P 3306 -u root -p'123456' --iterations=30 --concurrency=1 --number-of-queries=1 --create-schema='test' --query='select count(is_used) from sbtest1' Benchmark Average number of seconds to run all queries: 5.391 seconds Minimum number of seconds to run all queries: 5.222 seconds Maximum number of seconds to run all queries: 5.494 seconds Number of clients running queries: 1 Average number of queries per client: 1 [root@localhost ~]# mysqlslap -h 127.0.0.1 -P 3306 -u root -p'123456' --iterations=30 --concurrency=1 --number-of-queries=1 --create-schema='test' --query='select count(gmt_create) from sbtest1' Benchmark Average number of seconds to run all queries: 5.187 seconds Minimum number of seconds to run all queries: 5.104 seconds Maximum number of seconds to run all queries: 5.321 seconds Number of clients running queries: 1 Average number of queries per client: 1 这两列is_used和gmt_create都是非空，而k是是可空，但是查询速度却没有count(k)快，原因是什么呢？我们来看下执行计划： mysql> explain select count(gmt_create) from sbtest1; +----+-------------+---------+------------+-------+---------------+----------------+---------+------+----------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+----------------+---------+------+----------+----------+-------------+ | 1 | SIMPLE | sbtest1 | NULL | index | NULL | idx_gmt_create | 5 | NULL | 25931936 | 100.00 | Using index | +----+-------------+---------+------------+-------+---------------+----------------+---------+------+----------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select count(is_used) from sbtest1; +----+-------------+---------+------------+-------+---------------+-------------+---------+------+----------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+-------------+---------+------+----------+----------+-------------+ | 1 | SIMPLE | sbtest1 | NULL | index | NULL | idx_is_used | 4 | NULL | 25931936 | 100.00 | Using index | +----+-------------+---------+------------+-------+---------------+-------------+---------+------+----------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select count(k) from sbtest1; +----+-------------+---------+------------+-------+---------------+------+---------+------+----------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+------+---------+------+----------+----------+-------------+ | 1 | SIMPLE | sbtest1 | NULL | index | NULL | k_1 | 4 | NULL | 25931936 | 100.00 | Using index | +----+-------------+---------+------------+-------+---------------+------+---------+------+----------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) 我们再改变下列的形式： mysql> alter table sbtest1 change column k k int(11) NOT NULL DEFAULT '0'; Query OK, 0 rows affected (17 min 44.57 sec) Records: 0 Duplicates: 0 Warnings: 0 再跑下： [root@localhost ~]# mysqlslap -h 127.0.0.1 -P 3306 -u root -p'015891' --iterations=30 --concurrency=1 --number-of-queries=1 --create-schema='test' --query='select count(k) from sbtest1' Benchmark Average number of seconds to run all queries: 4.684 seconds Minimum number of seconds to run all queries: 4.528 seconds Maximum number of seconds to run all queries: 4.812 seconds Number of clients running queries: 1 Average number of queries per client: 1 对比之前的压测结果：对于k列非空比空快（4.684 < 4.832）。 count带where场景在实际的应用场景中，已经有其他方法代替不带where条件的count(比如预估值，比如计数器等)，很多带where条件的是需要count的，那这类SQL的速度是什么样的呢？ [root@localhost ~]# mysqlslap -h 127.0.0.1 -P 3306 -u root -p'123456' --iterations=30 --concurrency=1 --number-of-queries=1 --create-schema='test' --query='select count(*) from sbtest1 where id>1000000' Benchmark Average number of seconds to run all queries: 6.696 seconds Minimum number of seconds to run all queries: 6.508 seconds Maximum number of seconds to run all queries: 6.817 seconds Number of clients running queries: 1 Average number of queries per client: 1 [root@localhost ~]# mysqlslap -h 127.0.0.1 -P 3306 -u root -p'123456' --iterations=30 --concurrency=1 --number-of-queries=1 --create-schema='test' --query='select count(0) from sbtest1 where id>1000000' Benchmark Average number of seconds to run all queries: 6.717 seconds Minimum number of seconds to run all queries: 6.490 seconds Maximum number of seconds to run all queries: 6.865 seconds Number of clients running queries: 1 Average number of queries per client: 1 [root@localhost ~]# mysqlslap -h 127.0.0.1 -P 3306 -u root -p'123456' --iterations=30 --concurrency=1 --number-of-queries=1 --create-schema='test' --query='select count(1) from sbtest1 where id>1000000' Benchmark Average number of seconds to run all queries: 6.656 seconds Minimum number of seconds to run all queries: 6.519 seconds Maximum number of seconds to run all queries: 6.859 seconds Number of clients running queries: 1 Average number of queries per client: 1 [root@localhost ~]# mysqlslap -h 127.0.0.1 -P 3306 -u root -p'123456' --iterations=30 --concurrency=1 --number-of-queries=1 --create-schema='test' --query='select count(id) from sbtest1 where id>1000000' Benchmark Average number of seconds to run all queries: 6.691 seconds Minimum number of seconds to run all queries: 6.514 seconds Maximum number of seconds to run all queries: 6.865 seconds Number of clients running queries: 1 Average number of queries per client: 1 执行计划 mysql> explain select count(id) from sbtest1 where id>1000000; +----+-------------+---------+------------+-------+---------------+---------+---------+------+----------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+---------+---------+------+----------+----------+--------------------------+ | 1 | SIMPLE | sbtest1 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 12963160 | 100.00 | Using where; Using index | +----+-------------+---------+------------+-------+---------------+---------+---------+------+----------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select count(*) from sbtest1 where id>1000000; +----+-------------+---------+------------+-------+---------------+---------+---------+------+----------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+---------+---------+------+----------+----------+--------------------------+ | 1 | SIMPLE | sbtest1 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 12963160 | 100.00 | Using where; Using index | +----+-------------+---------+------------+-------+---------------+---------+---------+------+----------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select count(0) from sbtest1 where id>1000000; +----+-------------+---------+------------+-------+---------------+---------+---------+------+----------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+---------+---------+------+----------+----------+--------------------------+ | 1 | SIMPLE | sbtest1 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 12963160 | 100.00 | Using where; Using index | +----+-------------+---------+------------+-------+---------------+---------+---------+------+----------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select count(1) from sbtest1 where id>1000000; +----+-------------+---------+------------+-------+---------------+---------+---------+------+----------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+---------+---------+------+----------+----------+--------------------------+ | 1 | SIMPLE | sbtest1 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 12963160 | 100.00 | Using where; Using index | +----+-------------+---------+------------+-------+---------------+---------+---------+------+----------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) 在这类场景中count(expression)>count(id)>count(*), count(id)和count(*) 微差。另外在5.7中也做了一些改动，“[会根据flag判断是否可以把count(*)下推到引擎层，由于只有一次引擎层的调用，减少了Server层和InnoDB的交互，避免了无谓的内存操作或格式转换](http://mysql.taobao.org/monthly/2016/06/10/)”，从这个角度讲好像是优化了count（*），但是“[由于总是强制使用聚集索引，缺点很明显：当二级索引的大小远小于聚集索引，且数据不在内存中时，使用二级索引显然要快些，因此文件IO更少。](http://mysql.taobao.org/monthly/2016/06/10/)” 结论 count(expression)比count(*)和count(column)，如果没有特殊业务含义的话，可以优先使用到底是使用count(*)快还是count(column)快，这个也不是一定的参考 https://dev.mysql.com/doc/refman/5.6/en/group-by-functions.html#function_counthttp://www.rndblog.com/mysql-select-count/http://www.mysqldiary.com/limited-select-count/ http://mysqlha.blogspot.com/2009/08/fast-count-for-innodb.html

发表了文章 2018-01-11

MySQL和PostgreSQL的常用语法差异

背景在去年的DBMS评比中，PostgreSQL夺冠，PostgreSQL一直保持上升姿态，越来越多的客户选择使用PostgreSQL，还有一部分客户从MySQL迁往PostgreSQL，那PostgreSQL和MySQL对于开发者来说的差异在哪里呢？末学对比了下语法差异，不一样的地方用红色标记了出来，供大家参考。语法对比参考资料 https://www.postgresql.org/docs/manuals/ https://dev.mysql.com/doc/

发表了文章 2018-01-11

MySQL和PostgreSQL的数据类型对比

背景在去年的DBMS评比中，PostgreSQL夺冠，PostgreSQL一直保持上升姿态，越来越多的客户选择使用PostgreSQL，还有一部分客户从MySQL迁往PostgreSQL，那PostgreSQL和MySQL对于开发者来说的差异在哪里呢？我们先看下数据类型。数据类型参考资料 https://en.wikibooks.org/wiki/Converting_MySQL_to_PostgreSQL

发表了文章 2018-01-05

用sysbench轻松定制自己的测试场景

PostgreSQL的压测方法 PostgreSQL里自带的pgbench可以在SQL语句级别轻松实现造数据，而MySQL自带的mysqlslap则没有那么强大了。 MySQL的压测方法 MySQL的压测方法一般有几种，安装方法到网上一搜一堆，在这里就不在累述。 mysqlslap 优点 MySQL自带，不用单独安装，随便找一台有MySQL的机器上就可以运行。这种适用于简单的测试，比如已知SQL，想知道在某个并发下跑的性能是什么样的。缺点如果需要大量的数据得写成SQL文件导入，非常不方便。例子 mysqlslap -umyuser -pmypass -h 127.0.0.1 -P 3306 --iterations=10 --concurrency=50,100,500 --number-of-queries=1 --create-schema='mydb' --query='select * from tb where col1 = **;' sysbench sysbench既可以测试MySQL，又可以测试PostgreSQL，还可以自己写lua，根据自己的场景和SQL压测，非常方便。 sysbench的自带的压测场景在oltp_common.lua可以看到sysbench自带的压测场景。 local stmt_defs = { point_selects = { "SELECT c FROM sbtest%u WHERE id=?", t.INT}, simple_ranges = { "SELECT c FROM sbtest%u WHERE id BETWEEN ? AND ?", t.INT, t.INT}, sum_ranges = { "SELECT SUM(k) FROM sbtest%u WHERE id BETWEEN ? AND ?", t.INT, t.INT}, order_ranges = { "SELECT c FROM sbtest%u WHERE id BETWEEN ? AND ? ORDER BY c", t.INT, t.INT}, distinct_ranges = { "SELECT DISTINCT c FROM sbtest%u WHERE id BETWEEN ? AND ? ORDER BY c", t.INT, t.INT}, index_updates = { "UPDATE sbtest%u SET k=k+1 WHERE id=?", t.INT}, non_index_updates = { "UPDATE sbtest%u SET c=? WHERE id=?", {t.CHAR, 120}, t.INT}, deletes = { "DELETE FROM sbtest%u WHERE id=?", t.INT}, inserts = { "INSERT INTO sbtest%u (id, k, c, pad) VALUES (?, ?, ?, ?)", t.INT, t.INT, {t.CHAR, 120}, {t.CHAR, 60}}, } 自己可以修改sysbech内置lua，根据自己的场景定义表结构和想要测试的数据量。待会细说怎么改。 bulk_insert.lua oltp_common.lua oltp_delete.lua oltp_insert.lua oltp_point_select.lua oltp_read_only.lua oltp_read_write.lua oltp_update_index.lua oltp_update_non_index.lua oltp_write_only.lua select_random_points.lua select_random_ranges.lua 定义自己的表结构，修改oltp_common.lua，在187行开始,原始表： CREATE TABLE sbtest%d( id %s, k INTEGER DEFAULT '0' NOT NULL, c VARCHAR(500) DEFAULT '' NOT NULL, pad CHAR(60) DEFAULT '' NOT NULL, %s (id) ) %s %s]] 自己的表结构： CREATE TABLE sbtest%d( id %s, gmt_create datetime not null, gmt_modified datetime not null, k INTEGER DEFAULT '0' NOT NULL, c VARCHAR(500) DEFAULT '' NOT NULL, pad CHAR(60) DEFAULT '' NOT NULL, is_used INTEGER DEFAULT '0' NOT NULL, %s (id) ) %s %s]] 修改prepare准备数据部分： if (sysbench.opt.auto_inc) then query = string.format("('%s', '%s', %d, '%s', '%s')", os.date("%Y-%m-%d %H:%M:%S", os.time()), os.date("%Y-%m-%d %H:%M:%S", os.time()), sb_rand(1, sysbench.opt.table_size), c_val, pad_val) else query = string.format("(%d, '%s', '%s', %d, '%s', '%s')", i, os.date("%Y-%m-%d %H:%M:%S", os.time()), os.date("%Y-%m-%d %H:%M:%S", os.time()), sb_rand(1, sysbench.opt.table_size), c_val, pad_val) end 修改完之后按正常的三部曲执行就可以了 sysbench /usr/share/sysbench/oltp_read_only.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password='123456' --mysql-db=test --db-driver=mysql --tables=1 --table-size=200000000 --report-interval=10 --threads=100 --time=120 prepare/run/cleanup

发表了文章 2017-10-19

MySQL分区表的局限和限制

禁止构建分区表达式不支持以下几种构建：存储过程，存储函数，UDFS或者插件声明变量或者用户变量可以参考分区不支持的SQL函数算术和逻辑运算符分区表达式支持+,-,*算术运算，但是不支持DIV和/运算（还存在，可以查看Bug #30188, Bug #33182）。但是，结果必须是整形或者NULL（线性分区键除外，想了解更多信息，可以查看分区类型）。分区表达式不支持位运算：|，&，^，<<，>>，~ . HANDLER语句在MySQL 5.7.1之前的分区表不支持HANDLER语句，以后的版本取消了这一限制。服务器SQL模式如果要用用户自定义分区的表的话，需要注意的是，在创建分区表时的SQL模式是不保留的。在服务器SQL模式一章中已经讨论过，大多数MySQL函数和运算符的结果可能会根据服务器SQL模式而改变。所以，一旦SQL模式在创建分区表后改变，可能导致这些表的行为发生重大变化，很容易导致数据丢失或者损坏。基于以上原因，强烈建议你在创建分区表后千万不要修改服务器的SQL模式。举个例子来说明下上述情况： 1.错误处理 mysql> CREATE TABLE tn (c1 INT) -> PARTITION BY LIST(1 DIV c1) ( -> PARTITION p0 VALUES IN (NULL), -> PARTITION p1 VALUES IN (1) -> ); Query OK, 0 rows affected (0.05 sec) MySQL默认除以0的结果是NULL，而不是报错： mysql> SELECT @@sql_mode; +------------+ | @@sql_mode | +------------+ | | +------------+ 1 row in set (0.00 sec) mysql> INSERT INTO tn VALUES (NULL), (0), (1); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 然而如果我们修改SQL模式的话，就会报错： mysql> SET sql_mode='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO'; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO tn VALUES (NULL), (0), (1); ERROR 1365 (22012): Division by 0 2.表辅助功能有时候修改SQL模式可能会导致分区表不可用。比如有些表只有在SQL模式为NO_UNSIGNED_SUBTRACTION才发挥作用，比如： mysql> SELECT @@sql_mode; +------------+ | @@sql_mode | +------------+ | | +------------+ 1 row in set (0.00 sec) mysql> CREATE TABLE tu (c1 BIGINT UNSIGNED) -> PARTITION BY RANGE(c1 - 10) ( -> PARTITION p0 VALUES LESS THAN (-5), -> PARTITION p1 VALUES LESS THAN (0), -> PARTITION p2 VALUES LESS THAN (5), -> PARTITION p3 VALUES LESS THAN (10), -> PARTITION p4 VALUES LESS THAN (MAXVALUE) -> ); ERROR 1563 (HY000): Partition constant is out of partition function domain mysql> SET sql_mode='NO_UNSIGNED_SUBTRACTION'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @@sql_mode; +-------------------------+ | @@sql_mode | +-------------------------+ | NO_UNSIGNED_SUBTRACTION | +-------------------------+ 1 row in set (0.00 sec) mysql> CREATE TABLE tu (c1 BIGINT UNSIGNED) -> PARTITION BY RANGE(c1 - 10) ( -> PARTITION p0 VALUES LESS THAN (-5), -> PARTITION p1 VALUES LESS THAN (0), -> PARTITION p2 VALUES LESS THAN (5), -> PARTITION p3 VALUES LESS THAN (10), -> PARTITION p4 VALUES LESS THAN (MAXVALUE) -> ); Query OK, 0 rows affected (0.05 sec) 如果你在创建tu后，修改SQL模式，就可能再也不能访问这个表了： mysql> SET sql_mode='';Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM tu; ERROR 1563 (HY000): Partition constant is out of partition function domain mysql> INSERT INTO tu VALUES (20); ERROR 1563 (HY000): Partition constant is out of partition function domain 服务器端的SQL模式也会影响分区表的复制。在主备间使用不同的SQL模式可能会导致分区表达式主备上执行是不同的结果（而在阿里主备切换是很正常的操作）；这也会导致在主备复制过程中，不同分区间的数据分布不同；也有可能导致在主库上的分区表insert成功，而备库上失败。基于上述情况，最好的解决办法是保证主备间的SQL模式要保持一致（这个是DBA在运维过程中需要注意的）。性能注意事项下面是一些会影响分区操作性能的因素：文件系统操作分区或者重新分区（比如ALTER TABLE ...PARTITION BY ..., REORGANIZE PARTITION, 或者REMOVE PARTITIONING ）操作取决于文件系统的实现。意思是说上述操作会受操作系统上，比如：文件系统的类型和特性，磁盘速度，swap空间，操作系统上的文件处理效率，以及MySQL服务器上的和文件句柄相关的选项，变量等因素影响。需要特别说明的是，你需要保证large_files_support是enabled的，open_files_limit设置是合理的。对于MyISAM引擎的分区表来说，需要增加myisam_max_sort_file_size以提高性能；对于InnoDB表来说，分区或者重新分区操作通过enabled innodb_file_per_table效率会更快。也可以参考分区的最大数量。 MyISAM和分区文件描述符对于MyISAM分区表来说，MySQL为每个打开的表，每个分区使用两个文件描述符。这也就意味着，在MyISAM分区表上想执行操作（特别是ALTER TABLE操作）比相同的表没有分区，需要更多的文件描述符。假设我们要创建有100个分区的MyISAM表，语句如下： CREATE TABLE t (c1 VARCHAR(50)) PARTITION BY KEY (c1) PARTITIONS 100 ENGINE=MYISAM; 简单来讲，在这个例子中，虽然我们用的KEY分区，但是文件描述符的问题，在所有使用表引擎是MyISAM的分区里都会遇到，不管是分区类型是哪种。但是使用其他存储引擎（比如InnoDB）的分区表没有这个问题。假设你想对t重新分区，想让它有101个分区的话，使用下面的语句： ALTER TABLE t PARTITION BY KEY (c1) PARTITIONS 101; 如果要处理ALTER TABLE语句需要402个文件描述符，原来100个分区2个+101个新分区2。这是因为在重新组织表数据时，必须打开所有的（新旧）分区。所以建议在执行这些操作时，要确保--open-files-limit要设置的大些。表锁对表执行分区操作的进程会占用表的写锁，不影响读，例如在这些分区上的INSERT和UPDATE操作只有在分区操作完成后才能执行。存储引擎分区操作，比如查询，和更新操作通常情况下用MyISAM引擎要比InnoDB和NDB快。索引；分区修剪分区表和非分区表一样，合理的利用索引可以显著地提升查询速度。另外，设计分区表以及在这些表上的查询，可以利用分区修剪来显著提升性能。在MySQL 5.7.3版本之前，分区表不支持索引条件下推，之后的版本可以支持了。 load data性能在MySQL 5.7，load data 使用buffer提高性能。你需要知道的是buffer会占用每个分区的130KB来达到这个目的。分区的最大个数如果不是用NDB作为存储引擎的分区表，支持分区（这里子分区也包含在内）最大个数是8192。如果使用NDB作为存储引擎的用户自定义分区的最大分区个数，取决于MySQL Cluster的版本，数据节点和其他因素。如果你创建一个非常多（比最大分区数要少）的分区时，遇到诸如Got error ... from storage engine: Out of resources when opening file类的错误，你可能需要增加open_files_limit。但是open_files_limit其实也依赖操作系统，可能不是所有的平台都可以建议调整。还有一些其他情况，不建议使用巨大或者成百上千个分区，所以使用越来越多的分区并不见得能带来好结果。不支持Query cache 分区表不支持query cache，在分区表的查询中自动避开了query cache。也就是说在分区表的查询语句中query cache是不起作用的。每个分区一个key caches 在MySQL 5.7版本中，可以通过CACHE INDEX和LOAD INDEX INTO CACHE来使用MyISAM分区表的key cache。可以为一个，几个或者所有分区都定义key cache，这样可以把一个，几个或者所有分区的索引预加载到key cache中。不支持InnoDB分区表的外键使用InnoDB引擎的分区表不支持外键。下面的两种具体情况来阐述：在InnoDB表不能使用包含有外键的自定义分区；如果已经使用了外键的InnoDB表，则不能被分区。 InnoDB表不能包含一个和用户自定义分区表相关的外键；使用了用户自定义分区的InnoDB表，不能包含和外键相关的列。刚刚列出的限制的范围包括使用InnoDB存储引擎的所有表。违反这些限制的CREATE TABLE和ALTER TABLE语句是不被允许的。 ALTER TABLE ... ORDER BY 如果在分区表上执行ALTER TABLE ... ORDER BY的话，会导致每个分区的行排序。 REPLACE语句在修改primary key上的效率在某些情况下是需要修改表的primary key的，如果你的应用程序使用了REPLACE语句,这些语句的结果可能会被大幅度修改。全文索引分区表不支持全文索引或者搜索，即使分区表的存储引擎是InnoDB或者MyISAM也不行。空间列分区表不支持空间列，比如点或者几何。临时表不能对临时表进行分区(Bug #17497)。日志表不能对日志表进行分区，如果强制执行ALTER TABLE ... PARTITION BY ... 语句会报错。分区键的数据类型分区键必须是整形或者结果是整形的表达式。不能用结果为ENUM类型的表达式。因为这种类型的表达式可能是NULL。下面两种情况是例外的：当用LINER分区时，可以使用除TEXT或者BLOBS以外的数据类型作为分区键，因为MySQL内部的 hash函数会从这些列中产生正确的数据类型。例如，下面的创建语句是合法的： CREATE TABLE tkc (c1 CHAR) PARTITION BY KEY(c1) PARTITIONS 4; CREATE TABLE tke ( c1 ENUM('red', 'orange', 'yellow', 'green', 'blue', 'indigo', 'violet') ) PARTITION BY LINEAR KEY(c1) PARTITIONS 6; 2.当用RANGE，LIST，DATE或者DATETIME列分区的话，可能会用string。例如，下面的创建语句是合法的： CREATE TABLE rc (c1 INT, c2 DATE) PARTITION BY RANGE COLUMNS(c2) ( PARTITION p0 VALUES LESS THAN('1990-01-01'), PARTITION p1 VALUES LESS THAN('1995-01-01'), PARTITION p2 VALUES LESS THAN('2000-01-01'), PARTITION p3 VALUES LESS THAN('2005-01-01'), PARTITION p4 VALUES LESS THAN(MAXVALUE) ); CREATE TABLE lc (c1 INT, c2 CHAR(1)) PARTITION BY LIST COLUMNS(c2) ( PARTITION p0 VALUES IN('a', 'd', 'g', 'j', 'm', 'p', 's', 'v', 'y'), PARTITION p1 VALUES IN('b', 'e', 'h', 'k', 'n', 'q', 't', 'w', 'z'), PARTITION p2 VALUES IN('c', 'f', 'i', 'l', 'o', 'r', 'u', 'x', NULL) ); 上述异常都不适用于BLOB或TEXT列类型。子查询即使子查询避开整形值或者NULL值，分区键不能子查询。子分区的问题子分区必须使用HASH或者KEY分区。只有RANGE和LIST分区支持被子分区；HASH和KEY不支持被子分区。 SUBPARTITION BY KEY要求显示指定子分区列，不像PARTITION BY KEY可以省略（这种情况下会默认使用表的primary key）。例如，如果是这样创建表： CREATE TABLE ts ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) ); 你也可以使用相同的列的创建分区表（以KEY分区），使用下面语句： CREATE TABLE ts ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) ) PARTITION BY KEY() PARTITIONS 4; 前面的语句其实和下面的语句是一样的： CREATE TABLE ts ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) ) PARTITION BY KEY(id) PARTITIONS 4; 但是，如果尝试使用缺省列作为子分区列，创建子分区表的话，以下语句将失败，必须指定该语句才能执行成功，如下所示：（bug已知 Bug #51470）。 mysql> CREATE TABLE ts ( -> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> name VARCHAR(30) -> ) -> PARTITION BY RANGE(id) -> SUBPARTITION BY KEY() -> SUBPARTITIONS 4 -> ( -> PARTITION p0 VALUES LESS THAN (100), -> PARTITION p1 VALUES LESS THAN (MAXVALUE) -> ); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') mysql> CREATE TABLE ts ( -> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> name VARCHAR(30) -> ) -> PARTITION BY RANGE(id) -> SUBPARTITION BY KEY(id) -> SUBPARTITIONS 4 -> ( -> PARTITION p0 VALUES LESS THAN (100), -> PARTITION p1 VALUES LESS THAN (MAXVALUE) -> ); Query OK, 0 rows affected (0.07 sec) 数据字典和索引字典选项分区表的数据字典和索引字典受以下因素制约：表级的数据字典和索引字典被忽略（Bug #32091）在Windows系统上，MyISAM分区表不支持独立分区或子分区的数据字典和索引字典选项。但是支持InnoDB分区表的独立分区或者子分区的数据字典。修复和重建分区表分区表支持CHECK TABLE, OPTIMIZE TABLE, ANALYZE TABLE, 和 REPAIR TABLE语句。另外，你也可以用ALTER TABLE ... REBUILD PARTITION在一个分区表上重建一个或多个分区；用ALTER TABLE ... REORGANIZE PARTITION同样可以重建分区。从MySQL 5.7.2开始，子分区支持ANALYZE, CHECK, OPTIMIZE, REPAIR, 和 TRUNCATE操作。而在MySQL5.7.5之前的版本就已经引入REBUILD语法，只是不起作用（可以参考Bug #19075411, Bug #73130）。分区表不支持mysqlcheck, myisamchk, 和 myisampack操作。导出选项在MySQL 5.7.4以前的版本，不支持InnoDB分区表的FLUSH TABLES语句的导出选项（Bug #16943907）。参考资料 https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations.html

漫天花雨_个人页

个人介绍

擅长的技术

innodb表空间加密

阿里云数据库RDS MySQL的数据安全预防与恢复

子查询中有个不存在的列居然不报错是bug吗？

timestamp类型在not null时可以插入null值？

如何定位RDS MySQL内存使用率高？

MySQL的活跃连接数和连接数

RDS MySQL 5.7 4核和8核共享规格性能压测

分页语句的优化

如何查看MySQL的表空间

innodb_sort_buffer_size手册

查询分区表的语句

常用MySQL排查网站

ERROR 2020 (HY000): Got packet bigger than 'max_allowed_packet' bytes

理解innodb buffer pool

MySQL 8.0.3加force index优化跳过index dive

慎用mysql replace语句

这是一篇阿里云数据库专家的长期招聘帖

CloudDBA新功能上线--SQL过滤/限制/防火墙

truncate table须知

MySQL show processlist过滤

ERROR 1436 (HY000) at line 1943

MySQL里的回收表空间

Errcode: 24 - Too many open files

MySQL的binlog_row_image

分析MySQL网络包

常见计算机术语汇总

关系型数据库的历史

理解innodb buffer pool

长话短说mysql slow log的query time

那些从MySQL5.6升级到5.7的坑--innodb_large_prefix

MySQL里面的CONNECTION_ID

FLUSH TABLES WITH READ LOCK

MySQL里面的innodb_support_xa

Waiting for Slave Workers to free pending events

如何获取InnoDB树的高度

解读InnoDB页面--索引结构

解读InnodDB页面--普通页结构

解读InnoDB页面--系统文件结构

如何计算指定的InnoDB索引大小

count(*) VS count(X)

MySQL和PostgreSQL的常用语法差异

MySQL和PostgreSQL的数据类型对比

用sysbench轻松定制自己的测试场景

MySQL分区表的局限和限制

innodb表空间加密

阿里云数据库RDS MySQL的数据安全预防与恢复

innodb表空间加密

阿里云数据库RDS MySQL的数据安全预防与恢复

子查询中有个不存在的列居然不报错是bug吗？

timestamp类型在not null时可以插入null值？

如何定位RDS MySQL内存使用率高？

MySQL的活跃连接数和连接数

RDS MySQL 5.7 4核和8核共享规格性能压测

分页语句的优化

如何查看MySQL的表空间

innodb_sort_buffer_size手册

查询分区表的语句

常用MySQL排查网站

ERROR 2020 (HY000): Got packet bigger than 'max_allowed_packet' bytes

理解innodb buffer pool

MySQL 8.0.3加force index优化跳过index dive

慎用mysql replace语句

这是一篇阿里云数据库专家的长期招聘帖

CloudDBA新功能上线--SQL过滤/限制/防火墙

truncate table须知

MySQL show processlist过滤