问题[innodb](dba)

编辑： 用户@mustaccio 让我承认我必须提出激发调整需求的问题：

查询在较旧的服务器上运行得更快，功能更弱，MySQL 5.7.42 版花费的时间减少了三倍。以下是其中之一的非常简化的示例：

询问：

INSERT INTO 
    mydatabase.mytable
SET 
    CODMED=56304,
    CODCLOS='82618',
    CONTACTOID=51482,
    TRIM_ALT_GRAL=8,
    TRIM_ANT_GRAL=10,
    TRIM_ACT_GRAL=6,
    TRIM_ACT_RMM=2,
    OS_TO=101,
    OS_PAF=49.5,
    OS_OS=51,
    OS_OS_F=1,
    OS_PA_F=1;

表定义：

CREATE TABLE `mytable` (
  `CODIGO` int NOT NULL,
  `CODCLOS` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `CODMED` int NOT NULL,
  `CONTACTOID` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL COMMENT 'id de contacto',
  `VALOR2` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `LINEASXMERCADOS_COMPLETO` json DEFAULT NULL,
  `LINEASXMERCADOS` json DEFAULT NULL,
  `LINEASXMERCADOS_M5G` varchar(5000) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT '{}' COMMENT 'VALOR4LM',
  `OBRASSOCIALES` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT '{}',
  `OS_OS` int NOT NULL,
  `OS_PR` int DEFAULT NULL,
  `OS_PA` int DEFAULT NULL COMMENT 'PAM',
  `OS_PAF` int NOT NULL,
  `OS_X` int DEFAULT NULL COMMENT 'varios',
  `OS_TO` int NOT NULL,
  `OS_PR_F` int NOT NULL DEFAULT '0',
  `OS_OS_F` int NOT NULL DEFAULT '0',
  `OS_PA_F` varchar(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT '0',
  `TRIM_ALT_GRAL` int NOT NULL,
  `TRIM_ALT_RMM` int DEFAULT NULL,
  `TRIM_ANT_GRAL` int NOT NULL,
  `TRIM_ANT_RMM` int DEFAULT NULL,
  `TRIM_ACT_GRAL` int NOT NULL,
  `TRIM_ACT_RMM` int NOT NULL,
  `EST_GRAL` varchar(2) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `EST_RMM` varchar(2) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `STAMP` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

ALTER TABLE `mytable`
  ADD PRIMARY KEY (`CODIGO`),
  ADD UNIQUE KEY `CODMED_2` (`CODMED`),
  ADD KEY `TRIM_ACT_GRAL` (`TRIM_ACT_GRAL`),
  ADD KEY `CONTACTOID` (`CONTACTOID`),
  ADD KEY `CODCLOS` (`CODCLOS`),
  ADD KEY `TRIM_ACT_RMM` (`TRIM_ACT_RMM`),
  ADD KEY `LINEASXMERCADOS_M5G` (`LINEASXMERCADOS_M5G`(333));

ALTER TABLE `mytable`
  MODIFY `CODIGO` int NOT NULL AUTO_INCREMENT;

我正在使用专用服务器：

• 内存32 GB
• CPU Intel Xeon D-2141I @2.20GHz（显示“处理器总数：16”，速度 2200.000 MHz，缓存 16384 KB）

总共 20 个数据库：

• 每个都有250张桌子。

  • 在这 250 个中，一个大小约为 700MB，大约有 4 个大小约为 300MB。那么剩下的就是80MB以下

• 较重的数据库是 100 GB

  • 其中较重的表（作为例外）为 4.5 GB，其次是 10 个约 2.5GB，以及约 40 个 1.2GB。

这是我的my.cnf配置：

[mysqld]
disable-log-bin=1
default-authentication-plugin=mysql_native_password
performance-schema=1
innodb_file_per_table=1 # Ensure that each innodb table is it's own binary data block just in case there's corruption.
# query_cache_size=64M
thread_cache_size=4
key_buffer_size=32M
max_allowed_packet=268435456
table_open_cache=512 # max 2048, can be increased if more Opened tables - SHOW STATUS LIKE 'Opened_tables';
wait_timeout=28800
max_user_connections=25
open_files_limit=40000
 
#delayed_insert_timeout=20 # Turn on if max_connections being reached due to delayed inserts
#delayed_queue_size=300 # Turn on if max_connections being reached due to delayed inserts
 
myisam_sort_buffer_size=2M # can be increased per sessions if needed for alter tables (indexes, repair)
 
#query_cache_limit=2M # leave at default unless there is a good reason
#join_buffer=2M # leave at default unless there is a good reason
#sort_buffer_size=2M # leave at default unless there is a good reason
#read_rnd_buffer_size=256K # leave at default unless there is a good reason
#read_buffer_size=2M # leave at default unless there is a good reason
 
collation_server=utf8_unicode_ci
character_set_server=utf8
 
#innodb_flush_method=O_DSYNC # Do not enable this without a good reason - it doesn't work well on new dedis
innodb_buffer_pool_size=4G
innodb_thread_concurrency=16 # Number of physical + virtual CPU's, be careful of adding more

tmp_table_size=768M
max_heap_table_size=768M
max_connections=150
 
table_definition_cache=512 # increase by the same factor as table_open_cache
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=64M
 
general_log=0
slow_query_log=0
log-output=TABLE # select * from mysql.general_log order by event_time desc limit 10;
long_query_time=20
 
innodb_io_capacity=100
innodb_buffer_pool_instances=4
mysqlx=0
sql_mode="NO_ENGINE_SUBSTITUTION,ALLOW_INVALID_DATES"
innodb_log_file_size=768M

这些是 mysqltuner 的建议：

 >>  MySQLTuner 2.1.1
     * Jean-Marie Renouard <[email protected]>
     * Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.pl/
 >>  Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
 
[OK] Currently running supported MySQL version 8.0.32
[OK] Operating on 64-bit architecture
 
-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/log/mysql.log exists
[--] Log file: /var/log/mysql.log (0B)
[--] Log file /var/log/mysql.log is empty. Assuming log-rotation. Use --server-log={file} for explicit file
 
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM -ndbcluster -ndbinfo +PERFORMANCE_SCHEMA 
[--] Data in MyISAM tables: 108.0G (Tables: 4194)
[--] Data in InnoDB tables: 5.9G (Tables: 2269)
[OK] Total fragmented tables: 0
 
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
 
-------- Views Metrics -----------------------------------------------------------------------------
 
-------- Triggers Metrics --------------------------------------------------------------------------
 
-------- Routines Metrics --------------------------------------------------------------------------
 
-------- Security Recommendations ------------------------------------------------------------------
[--] Skipped due to unsupported feature for MySQL 8.0+
 
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
 
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 4h 29m 19s (13M q [854.998 qps], 20K conn, TX: 8G, RX: 6G)
[--] Reads / Writes: 97% / 3%
[--] Binary logging is disabled
[--] Physical Memory     : 29.3G
[--] Max MySQL memory    : 42.8G
[--] Other process memory: 0B
[--] Total buffers: 4.8G global + 257.9M per thread (150 max threads)
[--] Performance_schema Max memory usage: 181M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 7.3G (24.88% of installed RAM)
[!!] Maximum possible memory usage: 42.8G (146.11% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (4/13M)
[OK] Highest usage of available connections: 6% (9/150)
[OK] Aborted connections: 0.10% (20/20208)
[!!] CPanel and Flex system skip-name-resolve should be on
[--] Query cache has been removed since MySQL 8.0
[OK] Sorts requiring temporary tables: 0% (1K temp sorts / 4M sorts)
[!!] Joins performed without indexes: 4818
[OK] Temporary tables created on disk: 0% (0 on disk / 60K total)
[OK] Thread cache hit rate: 99% (187 created / 20K connections)
[OK] Table cache hit rate: 99% (23M hits / 23M requests)
[!!] table_definition_cache (512) is less than number of tables (6795) 
[OK] Open file limit used: 1% (423/40K)
[OK] Table locks acquired immediately: 99% (20M immediate / 20M locks)
 
-------- Performance schema ------------------------------------------------------------------------
[--] Performance_schema is activated.
[--] Memory used by Performance_schema: 181.6M
[--] Sys schema is installed.
 
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
 
-------- MyISAM Metrics ----------------------------------------------------------------------------
[--] MyISAM Metrics are disabled since MySQL 8.0.
 
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 16
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 4.0G / 5.9G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (37.5%): 768.0M * 2 / 4.0G should be equal to 25%
[OK] InnoDB buffer pool instances: 4
[--] Number of InnoDB Buffer Pool Chunk: 32 for 4 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.99% (393143981 hits / 393168510 total)
[!!] InnoDB Write Log efficiency: 58.44% (506487 hits / 866740 total)
[OK] InnoDB log waits: 0.00% (0 waits / 360253 writes)
 
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine not available.
 
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
 
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
 
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
 
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server
 
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    MySQL was started within the last 24 hours: recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    name resolution is enabled due to cPanel doesn't support this disabled.
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
             See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
             (specially the conclusions at the bottom of the page).
    Buffer Key MyISAM set to 0, no MyISAM table detected
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    long_query_time (<= 10)
    skip-name-resolve=0
    join_buffer_size (> 256.0K, or always use indexes with JOINs)
    table_definition_cache (512) > 6795 or -1 (autosizing if supported)
    key_buffer_size=0
    innodb_buffer_pool_size (>= 5.9G) if possible.
    innodb_log_file_size should be (=512M) if possible, so InnoDB total log file size equals 25% of buffer pool size.

这些是来自 PHPMyAdmin 的红色变量，表示有警报（根据 mysqlturner，MySQL 在过去 24 小时内启动，因为我重新启动了 MySQL 服务）：

Handler read rnd: 12 M
Handler read rnd next: 1.7 G
Innodb buffer pool reads: 24.5 k
Key reads: 44 M
Opened tables: 213.1 k
Select full join: 5 k
Select range check: 23
Sort merge passes: 1.5 k
Table locks waited: 1.1 k

这些是我在调查时从别人的帖子中读到的提示：

第一个：来自用户@RolandoMySQLDBA 的how-large-should-be-mysql-innodb-buffer-pool-size

“这将为您提供 RIBPS，推荐的 InnoDB 缓冲池大小，基于所有 InnoDB 数据和索引，额外增加 60%。”。

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes FROM information_schema.tables WHERE engine='InnoDB') A

我的结果是 10

他还说：

这将为您提供此时 InnoDB 缓冲池中的 InnoDB 数据正在使用多少 GB 内存。

SET @IBPDataPages = (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_data'); -- SELECT @IBPDataPages;
SET @IBPTotalPages = (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total'); -- SELECT @IBPTotalPages;
SET @IBPPctFull = CAST(@IBPDataPages * 100.0 / @IBPTotalPages AS DECIMAL(5,2));
SELECT @IBPPctFull;

我的结果是12.84

并且他还对InnoDB Buffer Pool Size做了另外一个公式：

SELECT CONCAT(CEILING(RIBPS/POWER(1024,pw)),SUBSTR(' KMGT',pw+1,1))
Recommended_InnoDB_Buffer_Pool_Size FROM
(
    SELECT RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw
    FROM
    (
        SELECT SUM(data_length+index_length)*1.1*growth RIBPS
        FROM information_schema.tables AAA,
        (SELECT 1.25 growth) BBB
        WHERE ENGINE='InnoDB'
    ) AA
) A;

我的结果是9G

用户@max 和@RickJames 还有另一个推荐

SELECT
    'Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests' AS Expression,
    'Read requests that had to hit disk' AS Meaning,
    ROUND((SELECT
        VARIABLE_VALUE
    FROM
        performance_schema.global_status
    WHERE
        VARIABLE_NAME = 'Innodb_buffer_pool_reads'
    )
    /
    (SELECT
        VARIABLE_VALUE
    FROM
        performance_schema.global_status
    WHERE
        VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
    ) * 100, 2) AS Val,
    '0-2%' AS Recommend,
    'Increase innodb_buffer_pool_size if you have enough RAM.' AS WhatToDoIfOutOfRange
UNION
SELECT
    'Innodb_pages_read / Innodb_buffer_pool_read_requests' AS Expression,
    'Read requests that had to hit disk' AS Meaning,
    ROUND((SELECT
        VARIABLE_VALUE
    FROM
        performance_schema.global_status
    WHERE
        VARIABLE_NAME = 'Innodb_pages_read'
    )
    /
    (SELECT
        VARIABLE_VALUE
    FROM
        performance_schema.global_status
    WHERE
        VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
    ) * 100, 2) AS Val,
    '0-2%' AS Recommend,
    'Increase innodb_buffer_pool_size if you have enough RAM.' AS WhatToDoIfOutOfRange
UNION
SELECT
    'Innodb_pages_written / Innodb_buffer_pool_write_requests' AS Expression,
    'Write requests that had to hit disk' AS Meaning,
    ROUND((SELECT
        VARIABLE_VALUE
    FROM
        performance_schema.global_status
    WHERE
        VARIABLE_NAME = 'Innodb_pages_written'
    )
    /
    (SELECT
        VARIABLE_VALUE
    FROM
        performance_schema.global_status
    WHERE
        VARIABLE_NAME = 'Innodb_buffer_pool_write_requests'
    ) * 100, 2) AS Val,
    '0-15%' AS Recommend,
    'Increase innodb_buffer_pool_size if you have enough RAM.' AS WhatToDoIfOutOfRange
UNION
SELECT
    'Innodb_buffer_pool_reads / Uptime' AS Expression,
    'Reads' AS Meaning,
    ROUND((SELECT
        VARIABLE_VALUE
    FROM
        performance_schema.global_status
    WHERE
        VARIABLE_NAME = 'Innodb_buffer_pool_reads'
    )
    /
    (SELECT
        VARIABLE_VALUE
    FROM
        performance_schema.global_status
    WHERE
        VARIABLE_NAME = 'Uptime'
    )) AS Val,
    '0-100/sec' AS Recommend,
    'Increase innodb_buffer_pool_size if you have enough RAM.' AS WhatToDoIfOutOfRange
UNION
SELECT
    '(Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed)  / Uptime' AS Expression,
    'InnoDB I/O' AS Meaning,
    ROUND(((SELECT
        VARIABLE_VALUE
    FROM
        performance_schema.global_status
    WHERE
        VARIABLE_NAME = 'Innodb_buffer_pool_reads'
    )
    +
    (SELECT
        VARIABLE_VALUE
    FROM
        performance_schema.global_status
    WHERE
        VARIABLE_NAME = 'Innodb_buffer_pool_pages_flushed'
    ))
    /
    (SELECT
        VARIABLE_VALUE
    FROM
        performance_schema.global_status
    WHERE
        VARIABLE_NAME = 'Uptime'
    )) AS Val,
    '0-100/sec' AS Recommend,
    'Increase innodb_buffer_pool_size if you have enough RAM.' AS WhatToDoIfOutOfRange
UNION
SELECT
    'Innodb_buffer_pool_pages_flushed / Uptime' AS Expression,
    'Writes (flushes)' AS Meaning,
    ROUND((SELECT
        VARIABLE_VALUE
    FROM
        performance_schema.global_status
    WHERE
        VARIABLE_NAME = 'Innodb_buffer_pool_pages_flushed'
    )
    /
    (SELECT
        VARIABLE_VALUE
    FROM
        performance_schema.global_status
    WHERE
        VARIABLE_NAME = 'Uptime'
    )) AS Val,
    '0-100/sec' AS Recommend,
    'Increase innodb_buffer_pool_size if you have enough RAM.' AS WhatToDoIfOutOfRange
UNION
SELECT
    'Innodb_buffer_pool_wait_free / Uptime' AS Expression,
    'Counter for when there are no free pages in buffer_pool. That is, all pages are dirty.' AS Meaning,
    ROUND((SELECT
        VARIABLE_VALUE
    FROM
        performance_schema.global_status
    WHERE
        VARIABLE_NAME = 'Innodb_buffer_pool_wait_free'
    )
    /
    (SELECT
        VARIABLE_VALUE
    FROM
        performance_schema.global_status
    WHERE
        VARIABLE_NAME = 'Uptime'
    )) AS Val,
    '0-1/sec' AS Recommend,
    'First be sure innodb_buffer_pool_size is set reasonably; if still trouble, decrease innodb_max_dirty_pages_pct' AS WhatToDoIfOutOfRange

这些是我的结果：

另一篇文章来自用户@Abdul Manaf，答案来自@RolandoMySQLDBA

设置什么 innodb 缓冲池和为什么

他说

您需要计算的是在任何给定时刻在当前 DB Server 上加载了多少 InnoDB 缓冲池。

SET @IBPDataPages = (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_data'); -- SELECT @IBPDataPages;
SET @IBPTotalPages = (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total'); -- SELECT @IBPTotalPages;
SET @IBPPctFull = CAST(@IBPDataPages * 100.0 / @IBPTotalPages AS DECIMAL(5,2));
SELECT @IBPPctFull;

我的结果是 12.85

According to what has been posted, it may seems that the way is more or less clear.. but I would like your help, especially since I don't have a development server and I can only do modifications on the production server, I'm worried about causing harm.

Thanks!

我们必须将 MyISAM 表迁移到 InnoDB，但我们担心停机时间很长。在我们的数据库中，我们有 10 个 MyISAM 表，其中 2 个是最大的：~26GB 数据 + ~10GB 索引。

我的问题是：我可以在主服务器（带有原始引擎的表）和所有表都迁移到 InnoDB 的副本之间设置复制吗？然后副本将是我们的主数据库。

我阅读了一些关于将表从 MyISAM 迁移到 InnoDB 的文章（例如： http: //mysql.rjweb.org/doc.php/myisam2innodb或https://dev.mysql.com/doc/refman/5.7/en/converting -tables-to-innodb.html）并且需要额外的步骤（比如删除重复的索引和服务器配置更改）但是它会起作用吗？

是否有监控的经验法则mysql.innodb.buffer_pool_reads / mysql.innodb.buffer_pool_read_requests？

我正在使用以下公式，但我开始认为它太敏感了。

(mysql.innodb.buffer_pool_reads / mysql.innodb.buffer_pool_read_requests) * 100 > 0.2

我使用嵌套集表示 MySql 中的树。

树有几种不同的“类型”，每个用户可以拥有每种类型的一棵树。

该表如下所示：

CREATE TABLE folders (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    typeId TINYINT(1) UNSIGNED NOT NULL,
    userId INT UNSIGNED NOT NULL,
    lft INT NOT NULL,
    rgt INT NOT NULL,
    title VARCHAR(50) DEFAULT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(typeId) REFERENCES folderTypes(id),
    FOREIGN KEY(userId) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

例如，在树中某处插入节点的查询将如下所示：

START TRANSACTION;

# given id, lock relevant rows

SELECT          f2.id 
FROM            folders f1, folders f2 
WHERE           f1.id = ? and 
                f1.typeId = f2.typeId AND
                f1.userId = f2.userId
FOR UPDATE;

# given id, select typeId, userId & lft

SELECT          @typeId := typeId, @userId := userId, @lft := lft 
FROM            folders 
WHERE           id = ?;

# update tree

UPDATE          folders 
SET             rgt = rgt + 2 
WHERE           rgt > @lft and
                typeId = @typeId AND
                userId = @userId;

UPDATE          folders
SET             lft = lft + 2
WHERE           lft > @lft and
                typeId = @typeId AND        
                userId = @userId;

# insert node

INSERT INTO     folders (typeId, userId, title, lft, rgt)
VALUES          (@typeId, @userId, 'new', (@lft + 1), (@lft + 2));  

COMMIT;

这工作正常。但是我注意到，随着更多的树和行被添加，这个查询，以及其他类似的嵌套集中节点操作的查询，都不像以前那么快了。

我确实读过这篇文章（https://explainextended.com/2009/09/29/adjacency-list-vs-nested-sets-mysql/）但是我需要使用 InnoDB，所以看起来空间索引不适用于我。（而且即使我可以为此切换到 MyISAM，重构所有查询现在也不是一个真正的选择。）

我想知道：是否有任何其他索引可以/应该添加到这个 InnoDB 表中来帮助 MySql？

我了解到 InnoDB 中的所有二级 B 树都存储表单对（搜索键，TID），其中 TID 是记录的主键。我有以下问题：

（1）在InnoDB中，二级B树是否通过mark-deletion实现删除？即叶子中的条目被标记为删除而不是物理删除。

（2）如果是这样，当插入一个新的条目，其搜索键与一些现有的标记删除条目相同时，InnoDB如何确定标记删除条目是否已提交？例如，假设 B 树是唯一索引。假设第一个事务标记删除搜索键hello，第二个事务尝试插入搜索键hello。如果第一个事务尚未提交，则插入应该失败；否则，插入应该是成功的。我想知道 InnoDB 如何决定是否允许插入hello。

我知道双写旨在解决部分页面写入问题。但是如何在 buffer_pool 内存复制到 double_write_buffer 期间发生崩溃时恢复

我只是在查看有关 MySQL 表锁定系统的文章。我想了解如何在 UPDATE/DELETE 操作时避免 MySQL 中的死锁。

到目前为止我做了什么：

设置

CREATE TABLE `new_table` (
  `id` int NOT NULL AUTO_INCREMENT,
  `a` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx` (`a`)
);

START TRANSACTION;
truncate new_table;
insert into new_table(a) values(2),(3),(4),(5),(10),(11),(15),(19),(20),(25),(27),(35);
COMMIT;

• 试验一：

步骤1：

* SESSION 1:
    START TRANSACTION;
    delete from new_table where a in (2, 3, 4);
    insert into new_table (a) values (55);
    // Both the queries will be executed. (NOT COMMITTED YET)

* SESSION 2:
    START TRANSACTION;
    delete from new_table where a in (5, 10);insert into new_table (a) values(105);
    // waiting for lock

* SESSION 3:
    START TRANSACTION;
    insert into new_table (a) values(7); delete from new_table where a in (11, 15);
    // waiting for lock

第2步：

* SESSION 1:
    COMMIT;

* SESSION 2:
    ** DEADLOCK HERE **

* SESSION 3:
    // Both the queries will be executed. (NOT COMMITTED YET)

您会看到SESSION 2中存在死锁。我的想法是，这是由于 MySQL 中的 GAP 锁定所致（我可能错了，如果我在这里错了，请纠正我）。

为了避免 Gap 锁定，我将 GLOBAL Transaction 隔离更改为 READ COMMITTED（默认为 REPEATABLE READ）。但仍然是同样的问题。

1. 试验二：

步骤1：

* SESSION 1:
    SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
    START TRANSACTION;
    delete from new_table where a in (2, 3, 4);
    insert into new_table (a) values (55);
    // Both the queries will be executed. (NOT COMMITTED YET)

* SESSION 2:
    SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
    START TRANSACTION;
    delete from new_table where a in (5, 10);insert into new_table (a) values(105);

* SESSION 3:
    SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
    START TRANSACTION;
    insert into new_table (a) values(7); delete from new_table where a in (11, 15);

第2步：

* SESSION 1:
    COMMIT;

* SESSION 2:
    // Both the queries will be executed. (NOT COMMITTED YET)

* SESSION 3:
    ** DEADLOCK HERE **

注意变化，死锁转移到SESSION 3。如您所见，我没有尝试删除重叠的行。但无论如何，仍然存在僵局。

当我尝试使用PRIMARY KEYinWHERE子句执行上述删除查询时，一切正常。

谁能解释一下这里发生了什么？我们如何处理这种情况并避免 MySQL 中的死锁？

编辑：添加结果EXPLAIN ANALYZE

• 询问：EXPLAIN ANALYZE SELECT * from new_table where a in (2, 3, 4);

** 带索引列

-> Filter: (new_table.a in (2,3,4))  (cost=1.45 rows=4) (actual time=0.059..0.069 rows=3 loops=1)
    -> Covering index scan on new_table using idx  (cost=1.45 rows=12) (actual time=0.048..0.060 rows=12 loops=1)

** 从列中删除索引后

-> Filter: (new_table.a in (2,3,4))  (cost=1.45 rows=4) (actual time=0.034..0.043 rows=3 loops=1)
    -> Table scan on new_table  (cost=1.45 rows=12) (actual time=0.031..0.037 rows=12 loops=1)

** 带PK

• 询问：EXPLAIN ANALYZE SELECT * from new_table where id in (2, 3, 4);

-> Filter: (new_table.id in (2,3,4))  (cost=1.36 rows=3) (actual time=0.040..0.050 rows=3 loops=1)
    -> Index range scan on new_table using PRIMARY over (id = 2) OR (id = 3) OR (id = 4)  (cost=1.36 rows=3) (actual time=0.038..0.047 rows=3 loops=1)

在对该主题进行了一些讨论之后，我可以假设 MySQL InnoDB 有一个非常令人沮丧的事实：当涉及到 DML 时，它不支持（原子）事务。

如果您使用数据进行数据库迁移，那么有一个相当简单的解决方案可以使其完全失败或成功完成。

START TRANSACTION;

INSERT INTO orders(orderNumber,orderDate) VALUES (1,'2020-05-31');
INSERT INTO orders(orderNumber,orderDate) VALUES (1,'2020-05-31');

COMMIT;

事务是针对一个或多个数据库中的数据的数据库操作的原子单元。

不幸的是，以下情况并非如此：

START TRANSACTION;

CREATE TABLE Persons ( PersonID int, LastName varchar(255),FirstName varchar(255));
CREATE TABLE Ducks ( DuckID int, DuckName varchar(255));
CREATE INDEX duckname_index ON Ducks (DuckName varchar(255));

COMMIT;

每个语句都将创建一个隐式提交，因此如果在 MySQL 数据库损坏和迁移一半之间迁移失败。

从文档：

有些语句不能回滚。通常，这些包括数据定义语言 (DDL) 语句，例如创建或删除数据库的语句，创建、删除或更改表或存储例程的语句。您应该在设计事务时不包含此类语句。如果您在无法回滚的事务中早期发出语句，然后另一个语句稍后失败，则在这种情况下无法通过发出 ROLLBACK 语句来回滚事务的全部效果。

由于我们必须为某个软件实施自定义迁移系统，我们现在想知道如何解决这个问题？例如 Symfony ( https://symfony.com/ ) Doctrine ( https://www.doctrine-project.org/ ) 如何在内部解决这个问题？

想法：

1. 如果出现错误，请在 CI/CD 级别解决并恢复旧数据库？缺点：听起来真的很笨拙。

2. 仅允许仅包含一个 DML 语句的迁移，并严格分开 DML 和 DDL 迁移。缺点：每个生产部署将有 10 个或数百个迁移文件。

我仍然希望有更好的方法吗？该问题的最佳实际解决方案是什么 - 如果有的话？

对于这个表，在 mysql 5.7 中，

    CREATE TABLE `t` (
      `id` int(10) unsigned NOT NULL,
      `val` int(10) unsigned NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB;

• 该表没有自动增量列
• 该表没有分区
• 只有在此表上执行的查询是INSERT和SELECT
• 该表未在任何子查询中使用
• sql_mode 没有启用任何 STRICT_*

我需要插入此表，但该id值存在于 >50% 的案例中。我想使用单个 INSERT IGNORE 而不是 "SELECT id" ，如果不存在 "INSERT"

当执行 INSERT IGNORE （不是多插入）并且PRIMARY KEY存在时，是否/可以涉及任何锁定？

我想更改ibtmp1在 debian 11 上存储在 mysql 8.0.25 中的路径。如何做到这一点？我懂了：

mysql> SELECT * FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME='innodb_temporary'\G
*************************** 1. row ***************************
             FILE_ID: 4294967293
             FILE_NAME: ./ibtmp1

我改变了mysqld.cnf：

[mysqld]
innodb_tmpdir=/$newDir/
innodb_temporary=/$newDir/
innodb_temp_tablespaces_dir=/$newDir/
innodb_temp_data_file_path=/$newDir/

重新启动mysqld，但这并没有改变任何事情。与：

mysql> SELECT @@innodb_temp_data_file_path;
+------------------------------+
| @@innodb_temp_data_file_path |
+------------------------------+
| ibtmp1:12M:autoextend        |
+------------------------------+

仅当我更改init.d脚本时：

mysqld_safe --innodb-tmpdir=/$newDir/

我看到mysql> SELECT @@innodb_tmpdir;路径已设置（不幸的是，这不会影响ibtmp1.

我不确定是否mysqld.cnf正确读取，但/usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"显示其中一个 conf 文件是正确的。

有没有办法通过 mysql 语句来做到这一点 - 它看起来如何？最简单的方法，在文件系统中创建符号链接，（不再）不起作用。