Rick James

我认为我们需要修改夜间加载,以使索引创建更高效。

我假设:

• CSV文件中的数据将替换现有数据。
• 你愿意使用 FULLTEXT 用于搜索。

然后做:

1. CREATE TABLE new_data (...) ENGINE=InnoDB;
2. LOAD DATA INTO new_data ...;
3. 清理中的数据 new_data .
4. ALTER TABLE new_data ADD FULLTEXT(...) ; 此处要索引的列存在,或在步骤1或3中添加。
5. RENAME TABLE real_data TO old_data, new_data TO real_data;
6. DROP TABLE old_data;

请注意,这基本上没有停机时间 real_data 所以你可以继续这样做 SELECTs .

您还没有解释如何将单个CSV文件喷洒到wp_帖子和wp_Posteta中。这听起来像是埋在我第三步里的噩梦。

全文 远比用 wp_postmeta (我不知道是否有现有的方法或插件来实现这一点。)

有了`全文(描述),您的代码片段将使用

WHERE MATCH(description) AGAINST ('word1 word2' IN BOOLEAN MODE)

而不是非常缓慢 LIKE 使用前导通配符。

如果你必须使用wp_Posteta,我建议 https://wordpress.org/plugins/index-wp-mysql-for-speed/

等我们想出一个满意的解决方案时,你的问题就会解决了。但这里有一些想法。

• UPDATE ,特别是如果修改了很多行,这非常耗时。(这是因为在回滚时需要保存旧行。)
• 如果没有索引,我就无法对事情给出完整的建议。
• 这是一次性查询,对吗?将来的“事件”会在存储事件时进行更新,对吗?这可能会运行得相当快。
• 考虑到您有一种针对单个事件进行更新的方法,请将其作为进行初始更新的基础 使现代化 (你现在问的那个)。也就是说,编写一个特殊的一次性程序来运行1900个事件,执行必要的更新。(优点:只需编写一个更新。)
• 一定要 COMMIT 每次更新之后。(或与 autocommit=ON 否则1900次更新将是系统的一大负担,可能比开始讨论的单一更新更糟糕。

我会分多个步骤来处理这个问题:

$str = '
      APPLICATION ID                   :AAAAA#AA#0101       
      STATUS P=PENDING/A=ACTIVE        :A                    
      VALID-TO DATE                    :711231               
      APPLICATION TYPE (A/G)           :A                    
      DESCRIPTIVE TEXT                 :DESC folder AAAAA    
      OWNER ID                         :               
      RANDOM INFO                      :                     
            PERIOD OR RUN CYCLE GROUP NAME   :QAZ123XX        
            RUN CYCLE VALID-FROM             :711230          
            RUN CYCLE VALID-TO               :711231          
            RUN CYCLE DESCRIPTION            :MANUAL ORDER    
            RUN RANDOM IFO                   :                
      APPLICATION ID                   :BBBBB#BB#0101       
      STATUS P=PENDING/A=ACTIVE        :A                    
      VALID-TO DATE                    :711231               
      APPLICATION TYPE (A/G)           :A                    
      DESCRIPTIVE TEXT                 :                     
      OWNER ID                         :OWNER1               
      RANDOM INFO                      :                     
';

加

$groups = explode('APPLICATION ID', $str);
foreach ($groups as $group) {
    echo "----\n";
    $hash = [];
    if (empty($group)) continue;
    $group = 'APPLICATION ID' . $group;
    $lines = preg_split("/\n/", $group);
    foreach($lines as $line) {
        $kv = explode(':', $line);
       if (count($kv) == 2 && trim($kv[1]) != '')
            $hash[trim($kv[0])] = trim($kv[1]);
    }
    print_r($hash);

    // TODO:  Build an INSERT with just the values in $hash
    // Meanwhile, each column should be `DEFAULT NULL`.
}

产量:

----
Array
(
)
----
Array
(
    [APPLICATION ID] => AAAAA#AA#0101
    [STATUS P=PENDING/A=ACTIVE] => A
    [VALID-TO DATE] => 711231
    [APPLICATION TYPE (A/G)] => A
    [DESCRIPTIVE TEXT] => DESC folder AAAAA
    [PERIOD OR RUN CYCLE GROUP NAME] => QAZ123XX
    [RUN CYCLE VALID-FROM] => 711230
    [RUN CYCLE VALID-TO] => 711231
    [RUN CYCLE DESCRIPTION] => MANUAL ORDER
)
----
Array
(
    [APPLICATION ID] => BBBBB#BB#0101
    [STATUS P=PENDING/A=ACTIVE] => A
    [VALID-TO DATE] => 711231
    [APPLICATION TYPE (A/G)] => A
    [OWNER ID] => OWNER1
)

另一个小贴士: NULLIF(?, '')

** views 就好像它是一个数字一样使用。如果是这样,不要使用 VARCHAR 建议 INT UNSIGNED (最高可达40亿美元)。

**使用合理的限制,而不是 varchar(1000) .

**越小越好。所以规范化经常重复的列。 carrier 有9个不同的值吗?A. SMALLINT UNSIGNED 只需要2个字节,并且有足够的空间来处理大量的载波。大部分时间也是如此 瓦查尔(1000) .

**建议使用标准的双字母“国家/地区代码”( CHAR(2) )而不是 country(100) --在美国、英国、法国。。。

**特别是为了加快查询速度

KEY `id_link` (`id_link`),

到

KEY `id_link` (`id_link`, carrier),

**研究使用 WITH ROLLUP 和 GROUP BY 所以你不需要 UNION 其次 SELECT

**考虑建立和维护一个每日汇总表的“汇总表”。查询这个表将比查询当前的原始(“事实”)表快得多。更多信息: http://mysql.rjweb.org/doc.php/summarytables (这是Shubham在评论中提到的内容的扩展。)

哎哟双重否定。让我的大脑受伤。

这些索引可能有助于:

e:  INDEX(ent_id,  status_id)
uam:  INDEX(user_id,  account)
eam:  INDEX(event_id, account,  acct)

添加复合索引后,删除具有相同前导列的索引。 也就是说,当你有索引(a)和索引(a,b)时,扔掉前者。

请给我们看看 整个 EXPLAIN SELECT ...

通常,通过网络发送更多内容所花费的时间会使应用程序解决方案的速度变慢。

这个 GROUP BY 可能需要额外排序,或者如果数据以这种方式排序,则可能是“免费的”。(好的,你说没有索引。)

向我们展示查询和 SHOW CREATE TABLE ; 我们可以帮助建立索引。

一般来说,如果工作是在SQL中完成的,那么为用户编写的代码要少得多。

特别是MySQL,它会在两个方面进行选择

案例1:对数据进行排序 O(N*log N) ,然后对数据进行线性传递;这可能涉及也可能不涉及会增加开销的I/O

案例2:在RAM中建立一个查找表,用于收集分组信息,然后对数据进行线性传递(不需要索引);但你需要这样的东西 O(N*log n) 用于计数/求和/无论分组值是多少。

笔记:

• 我曾经 N 表格中的数字或行,以及 n 获取输出中的行数。
• 我不知道会导致优化器选择一种方法而不是另一种方法的条件。

如果将所有数据拖到客户机中,可能会选择其中一种算法。如果您碰巧知道您是在一个简单的整数上分组,那么查找(对于第二种算法)可以是一个简单的数组查找-- O(N) .但是,正如我所说,网络成本可能会降低性能。

编写is SQL非常简单:

SELECT DATE(`date`)  AS "day",
       MAX(value) - MIN(value) AS range
    FROM tbl
    GROUP BY DATE(`date`);

UPPER(HEX(UUID_SHORT()))

为您提供一个唯一的16个字符的字母数字字符串。它有一些不太可能的警告,明白吗 https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_uuid-short

“下一个”值通常是可预测的:

mysql> SELECT UPPER(HEX(UUID_SHORT()));
+--------------------------+
| UPPER(HEX(UUID_SHORT())) |
+--------------------------+
| 161AA3FA5000006          |
+--------------------------+

mysql> SELECT UPPER(HEX(UUID_SHORT()));
+--------------------------+
| UPPER(HEX(UUID_SHORT())) |
+--------------------------+
| 161AA3FA5000007          |
+--------------------------+

转换为BASE64可以将字符串减少到11个字符:

https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_to-base64

mysql> SELECT TO_BASE64(UNHEX(HEX(UUID_SHORT())));
+-------------------------------------+
| TO_BASE64(UNHEX(HEX(UUID_SHORT()))) |
+-------------------------------------+
| AWGqP6UAABA=                        |
+-------------------------------------+

这是12个字符,去掉“=”会得到11个字符。

这些可能会使它不适合你的使用:“下一个”盘子在某种程度上是可以预测的。可能有一些标点符号( + , / )在绳子上。小写字母可能会包括在内。

一个更好的指数会有所帮助,但不能完全超过预期 ORDER BY 和 LIMIT :

INDEX(stime, used, thing, flags)

“有索引”——我想你指的是每一列的单独索引。当你有 AND ,可能最好有一个“综合”指数。从 = 测试(注: IS NULL 算作 = ,然后做 IN (哪个 OR 变成了)。

有关创建索引的详细信息: http://mysql.rjweb.org/doc.php/index_cookbook_mysql

肯定的 ALTERs 不需要锁定表;有些甚至不修改数据的任何部分。如果你想让我们看看 ALTER 并提供MySQL版本号,我们可以更具体一些。

e5 不是ascii,也不是UTF-8。与 CHARACTER SET latin1 它是 å ;这有道理吗?所以,我建议您有两个错误:一个地方要求ascii,一个提供latin1,还有一个要求utf8。

然而,正如@lenz指出的, E5 也可以是CJK字符的第一个字节。由于错误消息正在查找ascii,因此它只显示第一个有问题的字节。看到接下来的两个字节将证实这一点。

我想你失踪了 use_unicode=True .

见 python 熊猫在那一页的后面写下了笔记。

我认为“预防”需要发生 之前 处理 DATA_GPS 空间功能。但是,这可能在之后起作用:

建立 MultiLineString ,然后检查 IsClosed() . 如有必要,在末尾添加原始点。

使用 LOAD DATA INFILE ,并确保包括 CHARACTER SET utf8mb4 .

也就是说,你可以扔掉 全部的 用这个MySQL命令替换它。

这些变量可能相关:

table_open_cache
table_open_cache_instances
table_definition_cache
schema_definition_cache  (new in 8.0)
tablespace_definition_cache  (new in 8.0)

每个缓存都是固定大小的,可在启动时配置。(在某些情况下,在较新的版本上,它可以动态更改。)的文档 5.7 比如说 table_open_cache 是“动态的”

这些全局状态值可能相关:

Open_table_definitions
Opened_table_definitions
Opened_tables
Opened_files
Table_open_cache_hits
Table_open_cache_misses
Table_open_cache_overflows

命中/未命中/溢出为您提供了关于 表缓存 足够大,但不是最佳尺寸。

在5.7及之前版本中,数据字典来自 .frm 分散在磁盘上的文件。在8.0中,它位于一组InnoDB表中。据推测,一个合适的查询可以提供一些相关的度量。

也可以看到像 performance_schema_max_table_handles .

如果col4是数字(例如, INT ,则问题如下:

1. 分析行以获取字符串 "7,344"
2. 剥去外壳 " : 7,344
3. 将字符串存储到 国际的 列。这需要将该字符串转换为数字。
4. 转换在第一个非数字字符(即逗号)处停止。
5. 结果: col4 设置为7,并且 ,344 被抛。

mysql不能处理数字上的“千个分隔符”。但你可以脱掉它们:

LOAD ...
    (col1, col2, col3, @num)
    SET col4 = REPLACE(@num, ',', '')

• 将计数器移到平行表。把它们分开也会加快 UPDATE 是的。“update”的内部处理涉及在其他连接访问同一行时生成整行的副本。
• 另外,不要在上面记录历史。我想大部分的“更新”都在这方面。
• 把所有的 has_% 和 is_% 旗子打成一个 SET 或 TINYINT UNSIGNED (最多8人,或 SMALLINT UNSIGNED 最多16个)。
• 没有 has 标志—只需使用 LEFT JOIN 或 EXISTS 当你需要检查“has”时。
• 制造 是 标志(可能还有其他列) NOT NULL 是的。一般来说,你应该有意识地决定 NULL 对业务逻辑有意义,而不是简单地让所有列 NULLable 是的。

(我的建议适用于mysql和mariadb的所有版本。)

单人桌……错了。

一个表描述了每个“设备”(或“传感器”)。它有一个 id 可能是2字节 SMALLINT UNSIGNED (范围是0..65K——没有比这更多的传感器了?)。请注意mac_地址和名称属于此表。这个 身份证件 用在另一张桌子上…

另一个表包含 sensor_id , timestamp 以及 value . 这张桌子应该有 PRIMARY KEY(sensor_id, timestamp) 并且be engine=innodb`。现在它非常有效地查找一个传感器在一段时间内的所有读数。

这完全避免了 SELECT DISTINCT 因为第一张桌子上没有傻瓜。

如果你要拉下,那么 TINYINT UNSIGNED (1字节,0..255)可能很大。

好吧,我帮你做了两个 SELECTs ;你还有别的什么。请记住,性能主要取决于需要触摸的行数。你拒绝的也算在内。

无效的日期范围导致检查太多行

WHERE DATE(created_at) > '2018-12-23'
  AND DATE(created_at) < '2019-01-23'
  AND campaign_id = 27
  AND message_id = 133

不要那样写日期比较。它不能使用包含 created_at 因为它隐藏在函数调用中( DATE() )而是:

WHERE created_at >= '2018-12-23'
  AND created_at  < '2018-12-23' + INTERVAL 1 MONTH

如果 数据() 东西是由第三方包生成的,你需要放弃它。

缺乏合适的指标

然后…您需要一个复合索引:

INDEX(campaign_id, message_id,   -- in either order
      created_at)                -- after those

就为了“今天”

SELECT COUNT(*) FROM contact_notification_logs 
    WHERE created_at >= '[current date]'
      AND created_at  < '[current date]' + INTERVAL 1 DAY

INDEX(created_at)  -- the previous index will not help for _this_ query

需要汇总表

有2800万行,你可能会发现我上面的建议是不够的。再提高10倍, build and maintain a Summary Table . 建议使用天,而不是周或月作为解决方案。

其他

不要使用 COUNT(id) 除非你需要检查 id 是 NULL . 相反,使用通用模式: COUNT(*) .

如果 创造在 IS型 DATE ,原始查询是一个月,减去一天。如果是 DATETIME ,则缺少开始日期的午夜。使用我的代码,无论数据类型如何,它都能正常工作。

如需进一步讨论,请提供 SHOW CREATE TABLE .

如果您使用的是windows,并且已经通过 chcp 65001 然后做 SET NAMES utf8mb4; 在 mysql 命令行工具。这将覆盖 cp850 在那张截图里。

如果你正在使用 terminal 在*nix系统中,它可能被默认为utf-8。所以,再一次,使用 设置名称utf8mb4; .

避免 IN ( SELECT ... )

在这种情况下,应该很容易把它变成 JOIN .

更改中间查询:

SELECT  COUNT(1)
    FROM  bsn_data
    WHERE  bsn_data.periode LIKE '2018-12-%%'
      AND  bsn_data.kodeupline2 = bsn_kode_agent.kodeagent
      AND  bsn_data.kodeagent IN (
        SELECT  bsn_data.kodeagent
            FROM  bsn_data
            WHERE  bsn_data.periode LIKE '2018-12-%%'
              AND  bsn_data.kodeupline2 = bsn_kode_agent.kodeagent
            GROUP BY  bsn_data.kodeagent
            ORDER BY  COUNT(1) DESC
            LIMIT  1 
                          )

到

SELECT  COUNT(1)
    FROM  
        ( SELECT  bsn_data.kodeagent
            FROM  bsn_data
            WHERE  bsn_data.periode >= '2018-12-01'
              AND  bsn_data.periode  < '2018-12-01' + INTERVAL 1 MONTH
              AND  bsn_data.kodeupline2 = bsn_kode_agent.kodeagent
            GROUP BY  bsn_data.kodeagent
            ORDER BY  COUNT(1) DESC
            LIMIT  1 
        ) AS x
    JOIN  bsn_data  ON x.kodeagent = bsn_data.kodeagent
    WHERE  bsn_data.periode >= '2018-12-01'
      AND  bsn_data.periode  < '2018-12-01' + INTERVAL 1 MONTH
      AND  bsn_data.kodeupline2 = bsn_kode_agent.kodeagent

指标:

bsn_data:  INDEX(kodeupline2, periode, kodeagent)  -- in this order
bsn_data:  (kodeagent)  -- is this the PRIMARY KEY?

但是等等!难道不能简化为

SELECT  COUNT(1) AS ct
    FROM  bsn_data
    WHERE  bsn_data.periode >= '2018-12-01'
      AND  bsn_data.periode <  '2018-12-01' + INTERVAL 1 MONTH
      AND  bsn_data.kodeupline2 = bsn_kode_agent.kodeagent
    GROUP BY  bsn_data.kodeagent
    ORDER BY  COUNT(1) DESC
    LIMIT  1 

从 mysqldump 博士:

--禁用键,-k

对于每个表,用/*!40000个alter table tbl_name禁用密钥 ;和/ !40000 alter table tbl_name enable keys*/;语句。这使得加载转储文件更快,因为索引是在插入所有行之后创建的。此选项仅对myisam表的非唯一索引有效。对其他桌子没有影响。

确保在stock2上为group by中的列创建了一个复合索引

create index  idx1 on stock2 ( PhoneModal_Name,  Rate, Unit_Name,
                               STORE_NAME, PhoneModal_No )

嗯,相差10倍左右。听起来数据比 innodb_buffer_pool_size . 有时 SELECT 主要在ram中运行(cpu限制在0.6s),有时主要从磁盘运行(i/o限制在8-10s)。

请提供 SHOW CREATE TABLE , SHOW TABLE STATUS , InnoDB_缓冲池大小 和内存大小。同样,做 显示表状态 在delete+insert之前和之后。

桌子 mysql.user 你在问什么?或者这是一个新的插件?

该表中的密码不是实际的密码,而是通过单向加密输入,然后存储在与ascii、latin1和utf8兼容的文本中。别担心表中的那一列。

如果您正在进行自己的密码管理,那么您已经漏掉了一些信息。你…吗 连接 是UTF8还是Latin1?让我们看看 SHOW CREATE TABLE . 您是否在不加密的情况下存储pwd(因此更容易受到攻击)?

你是怎么把桌子换成的?有几个 错误的 方法。我建议你检查一下 现在 在事情变得更糟之前。在某个表中存储一个非ascii字符,然后 SELECT col, HEX(col) FROM ... ;我们可以看到它是否被正确存储,而不是“双重编码”或其他讨厌的。更多 discussion

查询2是 不 最优的。转动索引:

INDEX(tx_type, report_date)

也就是说,用 = 第一 ,与基数无关。

IN ( SELECT ... ) 是 不 一 JOIN . (在较新版本中, 可以 转换为 加入 )

试试看:

SELECT  d.*
    FROM  t_settle_trx AS s
    JOIN  t_deposit_trx AS d  ON s.tx_id = d.remit_tx_id
    WHERE  s.tx_type = 'CANCEL'
      AND  s.report_date >= '2019-03-01'
      AND  s.report_date <  '2019-03-01' + INTERVAL 2 DAY

s: INDEX(tx_type, report_date, tx_id)
d: INDEX(remit_tx_id)

想想这个 SELECT 用一个 加入 从一张桌子开始 WHERE 条款.

(注意:我排列了表和where子句的顺序,这样您就可以看到优化器是如何思考的。表的顺序和where子句的顺序不相关;优化器将根据需要重新排列。)

我的公式应该

• 充分利用两个表上的索引。
• 避免全表扫描
• 对 第一 它查看的表

全表扫描是 不 必然是件坏事。如果 最 一张桌子无论如何都会被触摸,实际上 更快 只需扫描表,而不在索引的btree和数据的btree之间跳转。(你的 具体的 凯斯还有其他不足之处,我把重点放在了这些方面。)

INDEX(tx_type, report_date, tx_id)

是“覆盖”的,因为查询所需的所有列都在一个索引中找到。覆盖“给你一个稍微额外的性能提升。它显示在 EXPLAIN 通过 Using index (不是) Using index condition )

对于tx_seq,这可能是最佳的:

INDEX(tx_type, report_date, tx_seq)

900表示Unicode 9.0(最好,但8.0是新的)
520表示Unicode 5.20(最好在5.7中提供)
_unicode_ci 引用Unicode 4.0(旧)
_general_ci (更不复杂)

_ci (没有 _as )指案件 和 口音 不敏感 .
_bin 指案件 和 口音 敏感的 ;比较位。
mysql没有提供许多混合敏感度的排序规则。

使用 SHOW COLLATION; 查看系统中可用的排序规则。

php提示:“interpolation”的工作方式如下:

• "...$var[...]..." 将其视为数组查找 $var[...] .
• 没有 [ , $var 假定为标量。
• "...{$var}[...]..." 是你需要的

最后一个示例有大括号 {} 告诉PHP在不关心后面的内容的情况下评估内部内容。更常见的用法:

$var = 'abc';
// I want to generate "abc123"
$bad  = "$var123";   // looks for variable 'var123'
$good = "{$var}123;  // correctly yields "abc123"

您的第二次尝试可以修复,因此:

REGEXP '(^|\+)$id(\+|$)'

含义:

1. 在开始或之后 + ,
2. 查找$ID(插入后),
3. 然后停下来 + 或字符串的结尾。

不要为系统表更换发动机

MySQL尚未更改足够的代码以允许 mysql.* 不是我的错。MySQL8.0通过将表(“数据字典”)转换为具有完全不同结构和功能的InnoDB表来进行更改。

既然你现在是5.7.23,你离8.0.xx只有一步之遥。考虑升级。

复制与myisam表一起工作, 但是 集群复制并不——galera和group复制以其他方式处理这些myisam表。请参阅有关发生什么情况的文档 GRANT , CREATE USER 等等,不要使用 UPDATE 和 INSERT 操作与登录相关的表。

(此问题的作者似乎已通过卸载插件修复了该问题。)