Alireza's questions

有时一些繁重的查询会增加服务器负载，一段时间后 CPU 和内存都会达到 100%。

这是my.cnf配置文件：

[mysqld]
expire_logs_days= 2

pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/storage/mysql

# LOGGING #
log-error                      = /var/storage/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/storage/mysql/mysql-slow.log

# MyISAM #
key-buffer-size                = 32M
myisam-recover-options         = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000

# BINARY LOGGING #
log-bin                        = /var/storage/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb-log-files-in-group      = 2
innodb_log_file_size=500M
innodb_buffer_pool_size=6G
innodb_flush_log_at_trx_commit=2
# recently added for compression, omit it if mysql has problems with it
innodb_file_format=Barracuda

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0


collation-server = utf8mb4_general_ci
init-connect='SET NAMES utf8mb4'
character-set-server = utf8mb4
skip-character-set-client-handshake

[client]
default-character-set   = utf8mb4

[mysql]
default-character-set   = utf8mb4

服务器有 6 个内核和 16 GB 的 RAM。有什么限制MySQL并使其安全的建议吗？

EDIT-1 这是缓慢的查询之一，需要 4.3 秒MySQL才能返回数据并检查了大约 300 万行：

# Time: 190107  6:22:30
# User@Host: root[root] @ localhost [127.0.0.1]
# Thread_id: 204732  Schema: my_db  QC_hit: No
# Query_time: 4.306051  Lock_time: 0.000058  Rows_sent: 0  Rows_examined: 3253235
# Rows_affected: 0
SET timestamp=1546842150;
SELECT ((old_credit + (1*amount))/10) credit
FROM `credits`
WHERE user_id=' ' or user_id=(SELECT user_id FROM accounts WHERE email=' ')
ORDER BY id DESC
LIMIT 1;

查询由前端 BI 应用程序创建（可以在一定程度上更改）。

该表的索引是：

MariaDB [my_db]> show index from credits;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| credits |          0 | PRIMARY    |            1 | id          | A         |     2014915 |     NULL | NULL   |      | BTREE      |         |               |
| credits |          1 | credit_id  |            1 | credit_id   | A         |     2014915 |     NULL | NULL   | YES  | BTREE      |         |               |
| credits |          1 | user_id    |            1 | user_id     | A         |      134327 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

编辑-2：

Database changed
MariaDB [my_db]> show index from accounts;
+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name             | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| accounts |          0 | PRIMARY             |            1 | id          | A         |      486034 |     NULL | NULL   |      | BTREE      |         |               |
| accounts |          1 | user_id             |            1 | user_id    | A         |      486034 |     NULL | NULL   |      | BTREE      |         |               |
| accounts |          1 | email_idx           |            1 | email       | A         |      486034 |      190 | NULL   |      | BTREE      |         |               |
| accounts |          1 | user_id_email_phone |            1 | user_id    | A         |      486034 |     NULL | NULL   |      | BTREE      |         |               |
| accounts |          1 | user_id_email_phone |            2 | email       | A         |      486034 |       40 | NULL   |      | BTREE      |         |               |
| accounts |          1 | user_id_email_phone |            3 | phone       | A         |      486034 |       15 | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.00 sec)

我当前的存储引擎是WiredTiger，它的压缩级别是默认的，snappy。我遇到过 MongoDB 文档，有人提到使用 zlib 压缩效果更好，但需要更多 CPU。

我想知道与压缩数据zlib相比，它会在内存中存储更多数据吗？snappy我有一台具有 16 个 CPU 内核的服务器。由于 RAM 更昂贵，我宁愿节省内存以防它保留更多数据。

这个对吗？可以盲目切换到zlib来缓存更多数据，提高读取性能吗？

注意：我们的服务器是读取密集型的。

从MongoDB文档中提到：

当您只需要文档中的一部分字段时，您可以通过仅返回您需要的字段来获得更好的性能

过滤字段如何影响性能？性能是否与通过网络传输的数据大小有关？或将保存在内存中的数据大小？这种性能究竟是如何提高的？文档中提到的这种性能是什么？

我的 MongoDB 查询很慢。返回子集是否会影响我的慢查询（我在该字段上有复合索引）？

由于innodb_file_per_table选项将表文件分开而不是将所有数据和数据库的索引放在一个ibdata文件中，因此使用此选项是否会提高alter table的速度？

我有一个 40M 行的表，当我更改特定表时，大约需要 5 到 6 个小时。

这个解决方案有帮助吗？是否有其他方法可以提高重型桌子上的 alter table 速度？

有没有办法根据值切换查询？假设我有以下查询：

select pdate as 'Payment Date', ROUND(@sum_amount := @sum_amount + `amount`) AS 'Total Amount' from
(
select @sum_amount := 0, date(issue_date) pdate, sum(amount) amount from payments group by YEAR(date(issue_date))
) inner_tbl

我想根据用户输入更改查询间隔（每年、每月、每天）。像下面这样的东西：

SELECT CASE input WHEN 1 THEN **RUN YEARLY QUERY**
     WHEN 2 THEN **RUN MONTHLY QUERY** ELSE **RUN DAILY QUERY** END;

我试图将数据库索引与ibdata1我采取的步骤分开，即减少 ibdata-in-mysql 的大小

我转储了数据，删除了数据库，并启动了 mysql 服务。

我收到以下错误，现在我被卡住了：

mysql -u root -p < alldb.sql
mysql: unknown option '--innodb_file_per_table'

现在有什么解决办法？

当我从所有内容中删除以下行时，my.cnf一切正常：

[mysql]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G

为什么innodb_file_per_table是未知选项？

奇怪的是我可以动态设置，当我将文件更改为 InnoDB 引擎时innodb_file_per_table它会分隔文件。.ibd

编辑：
它应该是一个未知的选项，因为我把它放在[mysql]not之下[mysqld]。我再次经历了这个过程，现在它工作得很好。ibd文件位于各自的数据库文件夹中。

呸...

我有两个疑问：

第一个如下：

SELECT * 
FROM batch bp 
  INNER JOIN sender_log sl ON sl.id=bp.log_id 
    AND bp.protocol='someprotocol'

第二个查询用于删除行：

delete from batch where protocol='someprotocol'

我知道在 postgres 我们有RETURNING *返回已删除的行。我想要实现的是删除行并在一个查询中返回它们。我怎样才能做到这一点？

当我插入一条记录时，我需要返回插入的 id RETURNING。问题是表是分区的，在分区表中我不能使用RETURNING。我一次运行多个查询，所以我急需RETURNING. 有没有办法做到这一点？

我使用年龄来获取日期字段之间的间隔。他们的类型是时间戳。

我的代码如下：

select age(timestamp MySecondDateField, timestamp MyFirstDateField)

我的字段中的数据包含：

2014-01-26 09:53:17.036359

我应该如何减去两个日期？

当我按如下方式使用年龄时，它会起作用：

select age(timestamp '2001-04-10', timestamp '1957-06-13')

我有一个非常大的用户表，如下所示：

CREATE TABLE `karbar` (
 `uid` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
 `email` varchar(70) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `password` char(41) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `token` char(40) NOT NULL,
 `joined` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `totalspam` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `flname` varchar(60) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `activation_link` char(40) NOT NULL,
 `role` tinyint(3) unsigned NOT NULL DEFAULT '1',
 `gender` char(1) NOT NULL DEFAULT 'u',
 `is_shop_active` char(0) DEFAULT '',
 `english_name` varchar(30) NOT NULL,
 `followings` mediumint(8) unsigned NOT NULL DEFAULT '0' ,
 `followers` mediumint(8) unsigned NOT NULL DEFAULT '0' ,
 PRIMARY KEY (`uid`),
 UNIQUE KEY `email` (`email`),
) ENGINE=InnoDB

我想用大量数据填充此表。假设有数百万条记录。这个怎么做？我需要生成独特的电子邮件。电子邮件的不同数据。我知道我可以使用CONCAT但不知道如何生成随机数据

假设我有一个words包含很多记录的表。
列是id和name。

在words我的表中，例如：

 'systematic', 'سلام','gear','synthesis','mysterious', etc.  

注意：我们也有 utf8 字。
如何有效地查询以查看哪些单词包含字母's'和'm'（'e'全部）？

输出将是：

systematic,mysterious

我不知道该怎么做。它应该是有效的，因为否则我们的服务器会受到影响。

在最新版本的 PostgreSQL（截至 2013 年 12 月）中，我们可以在两个或更多内核之间共享查询以提高性能吗？还是我们应该获得更快的内核？

我在社交网站上有一张用户表。我希望它从头开始变得高效。如果我将提到的字段放在表上，那么我必须在每次更改时更新用户表。我应该为这些经常变化的字段准备另一个表吗？我应该担心碎片化吗？

有没有我不知道的更好的方法？

我在表中有一个唯一的复合键，例如 fr(fromid,toid)，当我使用 explain 运行查询时，我得到以下结果：

Impossible WHERE noticed after reading const tables`

我运行的查询：

explain SELECT rid FROM relationship WHERE fromid=78 AND toid=60   

有什么帮助吗？

EDIT1：
当我使用以下查询时：

explain SELECT rid FROM relationship WHERE fromid=60 and toid=78 AND is_approved='s'  OR is_approved='f' OR is_approved='t'

我看到USING WHERE而不是上一条消息，但是当我使用以下查询时：

explain SELECT rid FROM relationship WHERE fromid=60 and toid=78 AND (is_approved='s'  OR is_approved='f' OR is_approved='t')  

我再次收到第一impossible ...条消息！这些括号在这里做什么？

编辑2：

CREATE TABLE `relationship` (
 `rid` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `fromid` mediumint(8) unsigned NOT NULL,
 `toid` mediumint(8) unsigned NOT NULL,
 `type` tinyint(3) unsigned NOT NULL,
 `is_approved` char(1) NOT NULL,
 PRIMARY KEY (`rid`),
 UNIQUE KEY `fromid` (`fromid`,`toid`),
 KEY `toid` (`toid`),
 CONSTRAINT `relationship_ibfk_1` FOREIGN KEY (`fromid`) REFERENCES `user` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `relationship_ibfk_2` FOREIGN KEY (`toid`) REFERENCES `user` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB

EDIT3：
正如mysql网站所说：

阅读 const 表后注意到不可能的 WHERE

MySQL 已读取所有 const（和系统）表并注意到 WHERE 子句始终为 false。

但是在查询中我得到了我想要的结果，这WHERE部分不是false. 有没有人可以解释这一点并阐明这个问题？

我有一个名为 ips 的表，如下所示：

CREATE TABLE `ips` (
 `id` int(10) unsigned NOT NULL DEFAULT '0',
 `begin_ip_num` int(11) unsigned DEFAULT NULL,
 `end_ip_num` int(11) unsigned DEFAULT NULL,
 `iso` varchar(3) DEFAULT NULL,
 `country` varchar(150) DEFAULT NULL
) ENGINE=InnoDB

假设我countryid在国家表中有一个字段，如下所示：

CREATE TABLE `country` (
 `countryid` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `ordering` smallint(5) unsigned NOT NULL DEFAULT '0',
 `iso` char(2) NOT NULL,
 PRIMARY KEY (`countryid`)
) ENGINE=InnoDB

ips 表中大约有 100,000 条记录。是否对以下情况有任何查询：
检查是否ips.iso等于country.iso，如果等于则将 country.coutryid 添加到该记录。我想不出任何办法。你知道怎么做吗？

当我们索引timetime这样的时间戳字段时。我们可以将它用于ORDER BY timetime DESC还是只能用于ORDER BY timetime ASCOR ORDER BY timetime？mySQL 在进行 vise verse 排序时是否使用此索引？

我有users表：

CREATE TABLE `users` (
 `uid` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
 `email` varchar(70) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `flname` varchar(60),
 PRIMARY KEY (`uid`)
) ENGINE=InnoDB

还有一个关系表：

CREATE TABLE `relationship` (
 `rid` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `from` mediumint(8) unsigned NOT NULL,
 `to` mediumint(8) unsigned NOT NULL,
 PRIMARY KEY (`rid`),
 UNIQUE KEY `from_2` (`from`,`to`),
 KEY `to` (`to`),
 CONSTRAINT `relationship_ibfk_1` FOREIGN KEY (`from`) REFERENCES `users` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `relationship_ibfk_2` FOREIGN KEY (`to`) REFERENCES `users` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB

我想获取那些不在特定用户圈子中的用户（如 G+）。这意味着用户尚未将这些人添加到他/她的圈子中。我尝试了以下查询，但它是空的：

SELECT uid FROM users WHERE flname LIKE'%john%' AND NOT EXISTS (Select `to` FROM relationship WHERE `from`=60)  

以下查询返回 3 条记录：

SELECT uid,flname FROM users WHERE flname LIKE'%john%' 

john现在应该不会显示以 uid=60 添加的用户名！此查询的结果为空。我想不通，我做错了什么？

当我在文件管理器中编辑 MySQL 5.0 中的 my.cnf 并添加以下设置时，我会自动从 Kloxo 注销，当我删除这些行并重新启动服务器时一切正常：

innodb_buffer_pool_size = 50M
innodb_log_file_size = 15M

但是当我添加以下行并重新启动时我没有问题并且已设置此设置：

bulk_insert_buffer_size=25M

为什么会这样？

PS：
我应该注意到 Horde_groupware 数据库有 innoDB 表，当一切都搞砸了并且SELECT * FROM Horde_groupware.tblname我收到错误消息说 .frm 文件中有错误信息。

我有一个写密集型的网络应用程序，我买了一个 768M RAM 的 VDS

• 这个 my.cnf 适合我的目的吗？

我将 bulk_insert_buffer_size 指定为 126M 和一些其他参数如下：

[mysqld]
bulk_insert_buffer_size=126M
set-variable = max_connections=500
safe-show-database
max_user_connections=200
key_buffer_size = 16M
query_cache_size = 50M
tmp_table_size = 50M
max_heap_table_size  = 200M
thread_cache_size = 4
table_cache = 80
thread_concurrency = 8
innodb_buffer_pool_size = 100M
innodb_log_file_size = 25M
query_cache_limit = 50M
innodb_flush_log_at_trx_commit = 2

当表中有唯一键（不是PRIMARY KEY）并且过程正在运行时，如果出现重复键错误，整个过程将停止。我想恢复错误并再次调用该过程。

步骤：

DELIMITER $$

CREATE PROCEDURE `injatest`.`LoadData` ()
BEGIN

    DECLARE x INT;

    SET x = 1;
    WHILE x <= 14400 DO
        INSERT INTO junc_question_course_iq(q_id,iq_id,ct_id) VALUES
        (CEIL(RAND()*1000), CEIL(RAND()*48), CEIL(RAND()*10));
        SET x = x + 1;
    END WHILE;

END $$


DELIMITER ;  

我想在联结表中插入大约 14000 条记录，但是当唯一（iq_id，q_id）存在重复键时会出现问题？做什么？