我在服务器上有 2 个主表，具有相同的结构和数据(唯一的区别在于表的名称(。
表 #1：2.14 亿行，大小 40GB(25GB 索引(
表 #2：2600 万行，大小 5.5GB(3.5GB 索引(
第一个问题。当内存中有缓存时，一切都可以非常快速地工作。如果我清理缓存或重新启动服务器，那么 mysql 查询会非常慢。Debian 8 jessie.MySQL 将索引存储在内存中并将其作为缓存，始终？因为经过一些操作后，选择查询非常快。需要执行哪些操作才能使服务器飞行：
如果我使用表 #1 的副本，那么据我所知，在其执行过程中，会发生读取操作，同时信息被缓存到内存中。这是启动重复表时的空闲 -m屏幕。
root@ns344370:~# free -m
total used free shared buffers cached
Mem: 128965 76802 52163 31 61 21714
-/+ buffers/cache: 55026 73939
Swap: 56141 0 56141
创建重复表时的结果：
root@ns344370:~# free -m
total used free shared buffers cached
Mem: 128965 126414 2551 31 49 65426
-/+ buffers/cache: 60938 68027
Swap: 56141 0 56141
缓存下有 50GB 内存。在重复表之前执行查询 105 秒：
# Query_time: 105.469931 Lock_time: 0.000180 Rows_sent: 41041 Rows_examined: 2097994
SET timestamp=1539135133;
SELECT SQL_CACHE `id`, `currency`, `handLimit`, `date`, `pp1` AS `profit`,`psd1` AS `isSD` FROM `ps_hands` WHERE `p1` = '274606' AND (`date` >= '2018-10-01' AND `date` <= '2018-10-10')
UNION ALL (SELECT `id`, `currency`, `handLimit`, `date`, `pp2` AS `profit`, `psd2` AS `isSD` FROM `ps_hands` WHERE `p2` = '274606' AND (`date` >= '2018-10-01' AND `date` <= '2018-10-10'))
UNION ALL (SELECT `id`, `currency`, `handLimit`, `date`, `pp3` AS `profit`, `psd3` AS `isSD` FROM `ps_hands` WHERE `p3` = '274606' AND (`date` >= '2018-10-01' AND `date` <= '2018-10-10'))
UNION ALL (SELECT `id`, `currency`, `handLimit`, `date`, `pp4` AS `profit`, `psd4` AS `isSD` FROM `ps_hands` WHERE `p4` = '274606' AND (`date` >= '2018-10-01' AND `date` <= '2018-10-10'))
UNION ALL (SELECT `id`, `currency`, `handLimit`, `date`, `pp5` AS `profit`, `psd5` AS `isSD` FROM `ps_hands` WHERE `p5` = '274606' AND (`date` >= '2018-10-01' AND `date` <= '2018-10-10'))
UNION ALL (SELECT `id`, `currency`, `handLimit`, `date`, `pp6` AS `profit`, `psd6` AS `isSD` FROM `ps_hands` WHERE `p6` = '274606' AND (`date` >= '2018-10-01' AND `date` <= '2018-10-10'))
UNION ALL (SELECT `id`, `currency`, `handLimit`, `date`, `pp7` AS `profit`, `psd7` AS `isSD` FROM `ps_hands` WHERE `p7` = '274606' AND (`date` >= '2018-10-01' AND `date` <= '2018-10-10'))
UNION ALL (SELECT `id`, `currency`, `handLimit`, `date`, `pp8` AS `profit`, `psd8` AS `isSD` FROM `ps_hands` WHERE `p8` = '274606' AND (`date` >= '2018-10-01' AND `date` <= '2018-10-10'))
UNION ALL (SELECT `id`, `currency`, `handLimit`, `date`, `pp9` AS `profit`, `psd9` AS `isSD` FROM `ps_hands` WHERE `p9` = '274606' AND (`date` >= '2018-10-01' AND `date` <= '2018-10-10'))
ORDER BY `id` ASC;
在复制表之后，其 0.2 秒而不是 105 秒。我正在考虑将磁盘从固态混合硬盘更改为 NVMe M2(快 5 倍(。但是我将有另外 4-5 张桌子，每张 50GB 的桌子，在这种情况下，我只是没有足够的 RAM。怎么做？ 我有 128GB 内存。除了这个项目，还有一个项目。分配了 50GB。其余的用于这个项目和MySQL。 我使用MyISAM而不是InnoDB，因为在网站上只选择和插入操作。InnoDB慢了20倍，在家用机器上也检查过，使用M.2磁盘。这是mysql配置文件：
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
query_cache_type = DEMAND
key_buffer_size = 55G
sort_buffer_size = 512M
read_buffer_size = 128M
max_allowed_packet = 32M
thread_stack = 192K
thread_cache_size = 4096
#MySQL Tuner
max_heap_table_size = 128M
tmp_table_size = 128M
table_open_cache = 4096
myisam-recover = BACKUP
max_connections = 2000
table_cache = 2048
thread_concurrency = 17
query_cache_limit = 128M
query_cache_size = 256M
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 1
#log-queries-not-using-indexes
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
innodb_buffer_pool_size = 512M
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
[isamchk]
key_buffer = 256M
!includedir /etc/mysql/conf.d/
有什么想法吗？请。
表 DDL：
CREATE TABLE `ps_hands` (
`id` bigint(14) NOT NULL DEFAULT '0',
`currency` tinyint(1) NOT NULL,
`handLimit` smallint(5) NOT NULL DEFAULT '0',
`date` date NOT NULL,
`maxPlayers` tinyint(1) NOT NULL,
`p1` mediumint(7) NOT NULL DEFAULT '0',
`p2` mediumint(7) NOT NULL DEFAULT '0',
`p3` mediumint(7) NOT NULL DEFAULT '0',
`p4` mediumint(7) NOT NULL DEFAULT '0',
`p5` mediumint(7) NOT NULL DEFAULT '0',
`p6` mediumint(7) NOT NULL DEFAULT '0',
`p7` mediumint(7) NOT NULL DEFAULT '0',
`p8` mediumint(7) NOT NULL DEFAULT '0',
`p9` mediumint(7) NOT NULL DEFAULT '0',
`pp1` mediumint(7) NOT NULL,
`pp2` mediumint(7) NOT NULL,
`pp3` mediumint(7) NOT NULL,
`pp4` mediumint(7) NOT NULL,
`pp5` mediumint(7) NOT NULL,
`pp6` mediumint(7) NOT NULL,
`pp7` mediumint(7) NOT NULL,
`pp8` mediumint(7) NOT NULL,
`pp9` mediumint(7) NOT NULL,
`psd1` tinyint(1) NOT NULL,
`psd2` tinyint(1) NOT NULL,
`psd3` tinyint(1) NOT NULL,
`psd4` tinyint(1) NOT NULL,
`psd5` tinyint(1) NOT NULL,
`psd6` tinyint(1) NOT NULL,
`psd7` tinyint(1) NOT NULL,
`psd8` tinyint(1) NOT NULL,
`psd9` tinyint(1) NOT NULL,
PRIMARY KEY (`id`),
KEY `maxPlayers` (`maxPlayers`),
KEY `p1_handLimit` (`p1`,`handLimit`),
KEY `p2_handLimit` (`p2`,`handLimit`),
KEY `p3_handLimit` (`p3`,`handLimit`),
KEY `p4_handLimit` (`p4`,`handLimit`),
KEY `p5_handLimit` (`p5`,`handLimit`),
KEY `p6_handLimit` (`p6`,`handLimit`),
KEY `p7_handLimit` (`p7`,`handLimit`),
KEY `p8_handLimit` (`p8`,`handLimit`),
KEY `p9_handLimit` (`p9`,`handLimit`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
查询说明：
+------+--------------+--------------------------+------+---------------+--------------+---------+-------+--------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------+--------------------------+------+---------------+--------------+---------+-------+--------+----------------+
| 1 | PRIMARY | ps_hands | ref | p1_handLimit | p1_handLimit | 3 | const | 182239 | Using where |
| 2 | UNION | ps_hands | ref | p2_handLimit | p2_handLimit | 3 | const | 290077 | Using where |
| 3 | UNION | ps_hands | ref | p3_handLimit | p3_handLimit | 3 | const | 273151 | Using where |
| 4 | UNION | ps_hands | ref | p4_handLimit | p4_handLimit | 3 | const | 248191 | Using where |
| 5 | UNION | ps_hands | ref | p5_handLimit | p5_handLimit | 3 | const | 255685 | Using where |
| 6 | UNION | ps_hands | ref | p6_handLimit | p6_handLimit | 3 | const | 362813 | Using where |
| 7 | UNION | ps_hands | ref | p7_handLimit | p7_handLimit | 3 | const | 358672 | Using where |
| 8 | UNION | ps_hands | ref | p8_handLimit | p8_handLimit | 3 | const | 264515 | Using where |
| 9 | UNION | ps_hands | ref | p9_handLimit | p9_handLimit | 3 | const | 221512 | Using where |
| NULL | UNION RESULT | <union1,2,3,4,5,6,7,8,9> | ALL | NULL | NULL | NULL | NULL | NULL | Using filesort |
+------+--------------+--------------------------+------+---------------+--------------+---------+-------+--------+----------------+
正如我在评论中所说，确保date字段上有索引(或以索引开头(可能会有所帮助。 此外，由于您可能无法从涉及p#字段的索引中受益，因此您可以尝试使用非 UNION 版本的查询，如下所示
SELECT `id`, `currency`, `handLimit`, `date`
, CASE
WHEN `p1` = '274606' THEN `pp1`
WHEN `p2` = '274606' THEN `pp2`
-- and so on ...
END AS `profit`
, CASE
WHEN `p1` = '274606' THEN `psd1`
WHEN `p2` = '274606' THEN `psd2`
-- and so on ...
END AS AS `isSD`
FROM `ps_hands`
WHERE `date` BETWEEN '2018-10-01' AND '2018-10-10'
AND '274606' IN (`p1`,`p2`,`p3`,`p4`,`p5`,`p6`,`p7`,`p8`,`p9`)
ORDER BY `id` ASC;
编辑/警告：如果同一行可以有多个具有相同值的 p#，则上述操作将不起作用;相反，需要调整profit和isSD的表达式(也许将 pp# 值加在一起？...而且您仍然不会为每个列匹配获得单独的结果行。
编辑#2：为每个 p#(p#, date)或(date, p#)单独的复合索引应该对您的原始查询有很大帮助。不过，我会首先尝试一个date索引，如果性能不够好，请使用(date, p#)复合索引。对于您的查询，(date, p#)索引可能不会比(p#, date)索引好多少，但它们将有效地为您提供其他查询可以利用的date索引。