主页 / dba / 问题 / 110707
Accepted
mvorisek
Asked: 2015-08-12 10:04:15 +0800 CST

如何强制 MySQL 忽略所有索引？

  • 772

我已阅读有关FORCE索引的文章，但如何强制 MySQL 建立IGNORE ALL索引？

我试过SELECT * FROM tbl IGNORE INDEX(*)了，但我没有成功。

至于为什么我（和其他人）需要这样做：例如，我需要像这样通过 tld 总结引用统计信息：

SELECT 
    count(*) as c, 
    SUBSTRING
    (
        domain_name, 
        LENGTH(domain_name) - LOCATE('.', REVERSE(domain_name)) + 2
    ) as tld
FROM `domains_import` 
    IGNORE INDEX(domain_name)
GROUP BY tld
ORDER BY c desc
LIMIT 100

...但我总是必须查看定义了哪些索引或通过解释确定将使用哪些索引。IGNORE INDEX ALL简单地写而不关心会非常方便。

有谁知道语法或黑客？（几十行通过MySQL定义表确实不是捷径）。

从聊天讨论中添加：

基准：

  • 无索引 = 148.5 秒

  • 索引 = 180 秒并且仍在运行 发送数据 SSD 阵列非常强大，您几乎不需要关心数据缓存...

基准的定义：

CREATE TABLE IF NOT EXISTS `domains_import` (
`domain_id` bigint(20) unsigned NOT NULL,
`domain_name` varchar(253) CHARACTER SET ascii COLLATE ascii_bin NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `domains_import`
ADD PRIMARY KEY (`domain_id`),
ADD UNIQUE KEY `domain_name` (`domain_name`);

ALTER TABLE `domains_import`
MODIFY `domain_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT;

InnoDB，带有索引的测试（没有 USE INDEX() 或类似的）仍在运行，250 秒，我刚刚杀了它。

mysql index

3 个回答

  1. Best Answer

    绝对不清楚您为什么要这样做，但您可以使用提示USE INDEX ()告诉优化器不要使用任何索引。来自 MySQL 文档：索引提示

    省略index_listforUSE INDEX在语法上是有效的，这意味着“不使用索引”。FORCE INDEX为or省略 index_listIGNORE INDEX是语法错误。

    您的查询变为：

    SELECT count(*) AS c, 
       substring_index(domain_name, '.', -1) AS tld
FROM domains_import 
       USE INDEX ()        -- use no indexes
GROUP BY tld
ORDER BY c DESC
LIMIT 100 ;

    旁注：复杂的表达式：

    SUBSTRING(domain_name, LENGTH(domain_name) - LOCATE('.', REVERSE(domain_name)) + 2)

    可以从 4 个函数调用简化为 1 个：

    SUBSTRING_INDEX(domain_name, '.', -1)
    • 27

  2. 你也可以嵌入WHERE 1=1

    SELECT 
    count(*) as c, 
    SUBSTRING
    (
        domain_name, 
        LENGTH(domain_name) - LOCATE('.', REVERSE(domain_name)) + 2
    ) as tld
FROM `domains_import` 
WHERE 1=1
GROUP BY tld
ORDER BY c desc
LIMIT 100

    超级立方体刚刚问我

    Rolando，MySQL 的优化器是否如此愚蠢以至于一个简单的永远为真的条件会禁止使用索引？

    是的，但是你给了 MySQL 一个非常愚蠢的查询。1=1将恢复为聚集索引。尽管如此，还有另一种方法，但它需要对优化器有点恶意。

    SELECT 
    count(*) as c, 
    SUBSTRING
    (
        domain_name, 
        LENGTH(domain_name) - LOCATE('.', REVERSE(domain_name)) + 2
    ) as tld
FROM `domains_import` 
WHERE domain_name = domain_name
GROUP BY tld
ORDER BY c desc
LIMIT 100

    这肯定会将每个索引都扔到总线下，因为domain_name要检查每一行的值。如果已编入索引，则必须为根本没有编入索引domain_name的列选择。WHERE column_name=column_name

    我刚刚在登台服务器的一张大桌子上试过这个

    mysql > explain SELECT COUNT(1) FROM VIDEO WHERE EMBEDDED_FLG=EMBEDDED_FLG;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | VIDEO | ALL  | NULL          | NULL | NULL    | NULL | 354327 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

    未选择任何索引

    • 2

  3. 假设您有这两个索引：

    ADD PRIMARY KEY (`domain_id`),
ADD UNIQUE KEY `domain_name` (`domain_name`);

    那么优化器做什么都没有关系；它必须扫描基本上相同数量的东西。

    案例 1：它进行表扫描（或使用 domain_id）：它将扫描 (id, name) 对，定位所有名称，执行 SUBSTRING..LOCATE、GROUP BY，最后是 ORDER BY。GROUP BY 和 ORDER BY 可能都需要一个 tmp 表和文件排序。检查EXPLAIN SELECT ...是否有。

    案例 2：它进行索引扫描（域名称）：该索引实际上包含（名称，id）对——因为 InnoDB 隐式地将 PK 放在任何辅助键的末尾。其余处理与案例 1 类似。

    一件事可能不同——两个 BTree 的大小。请SHOW TABLE STATUS LIKE domains_import查看 Data_length（针对案例 1）和 Index_length（针对案例 2）。较大的 BTree 会更慢。

    另一件事可能不同——缓存。的价值是innodb_buffer_pool_size多少？你有多少内存？数据（或索引）是否可以包含在缓冲池中。（或者它会是 37%，因为这是一个表/索引扫描？）如果它适合，那么运行两次查询。由于没有撞到磁盘（缓存），第二次将快 10 倍左右。

    如果这是一项一次性任务，SSD 会有所帮助。如果没有，并且您可以缓存整个表，那么在 buffer_pool 加载后将无济于事。

    • 0

相关问题