mysql性能如何？

vpfxa7rd  于 2021-06-18  发布在  Mysql
关注(0)|答案(1)|浏览(271)

我在服务器上有两个主表，具有相同的结构和数据（唯一的区别是表的名称）。
表#1:2.14亿行，大小40gb（25gb索引）
表#2:26百万行，大小5.5gb（3.5gb索引）
第一个问题。当内存中有缓存时，一切都会很快工作。如果我清理缓存或重新启动服务器，那么mysql查询会非常慢。debian 8杰西。mysql将索引存储在内存中并将其作为缓存，总是这样吗？因为经过一些操作后，select查询非常快。需要执行哪些操作才能使服务器正常运行：
如果我使用表#1的副本，那么在执行过程中，据我所知，会发生一个读取操作，同时将信息缓存到内存中。在启动复制表时，这里有一个free-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的ram。在复制表之前执行查询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秒。我正在考虑将磁盘从sshd改为nvm2（快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 |
+------+--------------+--------------------------+------+---------------+--------------+---------+-------+--------+----------------+
mysqldebian

1条答案

按热度按时间
lx0bsm1f

lx0bsm1f1#

正如我在评论中所说的，确保在 date 菲尔德能帮上忙。另外，由于您可能无法从包含 p# 字段，您可以尝试这样的查询的非并集版本

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#, date) 或者 (date, p#) 对于每一个p#都应该对您最初的查询有很大的帮助。我想先试一下 date 不过，首先是索引，如果这还不够，那就用 (date, p#) 复合索引。对于您的问题 (date, p#) 索引可能不会比 (p#, date) 一个，但他们会有效地给你一个 date 其他查询可以利用的索引。

赞(0）分享  回复(0）举报  2021-06-18
我来回答

相关问题

    查看更多
    微信公众号

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com