由于此设置:
mysql> show global variables like '%indexes';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON |
+-------------------------------+-------+
慢查询日志不断收到:
# Time: 120607 16:58:30
# User@Host: xbtit[xbtit] @ [123.30.53.244]
# Query_time: 0 Lock_time: 0 Rows_sent: 1 Rows_examined: 16006
SELECT * FROM xbtit_files WHERE IF(soha_id is null OR soha_id = '', info_hash, soha_id)='6d63dd4ab199190b531752067414d4d6e6568f90';
尝试解释此查询:
mysql> EXPLAIN SELECT * FROM xbtit_files WHERE IF(soha_id is null OR soha_id = '', info_hash, soha_id)='6d63dd4ab199190b531752067414d4d6e6568f90';
+----+-------------+-------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | xbtit_files | ALL | NULL | NULL | NULL | NULL | 16006 | Using where |
+----+-------------+-------------+------+---------------+------+---------+------+-------+-------------+
令我惊讶的是为什么MySQL不使用索引:
mysql> show index from xbtit_files;
+-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| xbtit_files | 0 | PRIMARY | 1 | info_hash | A | 16006 | NULL | NULL | | BTREE | |
| xbtit_files | 1 | filename | 1 | filename | A | 16006 | NULL | NULL | YES | BTREE | |
| xbtit_files | 1 | category | 1 | category | A | 1 | NULL | NULL | | BTREE | |
| xbtit_files | 1 | uploader | 1 | uploader | A | 16 | NULL | NULL | | BTREE | |
| xbtit_files | 1 | bin_hash | 1 | bin_hash | A | 16006 | 20 | NULL | | BTREE | |
| xbtit_files | 1 | ix_sohaid | 1 | soha_id | A | 16006 | NULL | NULL | YES | BTREE | |
+-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
FORCE INDEX
也不起作用:
mysql> EXPLAIN SELECT * FROM xbtit_files force index (PRIMARY) WHERE IF(soha_id is null OR soha_id = '', info_hash, soha_id)='6d63dd4ab199190b531752067414d4d6e6568f90';
+----+-------------+-------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | xbtit_files | ALL | NULL | NULL | NULL | NULL | 16006 | Using where |
+----+-------------+-------------+------+---------------+------+---------+------+-------+-------------+
我必须将此查询拆分为 2 个操作吗?
在 MySQL
中,您无法在表达式上创建索引,并且优化器不够智能,无法将查询拆分为两个索引。
使用这个:
SELECT *
FROM xbtit_files
WHERE soha_id = '6d63dd4ab199190b531752067414d4d6e6568f90'
UNION ALL
SELECT *
FROM xbtit_files
WHERE soha_id = ''
AND info_hash = '6d63dd4ab199190b531752067414d4d6e6568f90'
UNION ALL
SELECT *
FROM xbtit_files
WHERE soha_id IS NULL
AND info_hash = '6d63dd4ab199190b531752067414d4d6e6568f90'
每个查询都使用自己的索引。
您可以将其合并到单个查询中:
SELECT *
FROM xbtit_files
WHERE (
soha_id = '6d63dd4ab199190b531752067414d4d6e6568f90'
OR
(soha_id = '' AND info_hash = '6d63dd4ab199190b531752067414d4d6e6568f90')
OR
(soha_id IS NULL AND info_hash = '6d63dd4ab199190b531752067414d4d6e6568f90')
)
并在(soha_id, info_hash)
上创建一个合成索引,以便快速工作。
MySQL
还能够使用 index_merge
将两个索引的结果合并在一起,因此即使您没有创建复合索引,也有可能在第二个查询的规划中看到这一点。
您可以阅读本文以了解为什么OR
运算符不适用于索引数据库。
因为函数是黑盒: http://use-the-index-luke.com/sql/where-clause/functions/case-insensitive-search
编辑 - 给你的上下文太少了,对不起。
相关部分是:
It is a trap we all fall into. We instantly recognize the relation between
LAST_NAME and UPPER(LAST_NAME) and expect the database to “see” it as well.
In fact, the optimizer’s picture is more like that:
SELECT first_name, last_name, phone_number
FROM employees
WHERE BLACKBOX(...) = 'WINAND';
The UPPER function is just a black box. The parameters to the function are
not relevant because there is no general relationship between the function’s
parameters and the result.
这适用于所有功能:UPPER,IF,无论什么...
MySQL被划掉了,因为该问题的解决方案(在页面下方进一步描述)不适用于MySQL。
在可能降低性能的地方使用函数(LEFT
函数除外)。请尝试此查询
SELECT * FROM xbtit_files WHERE
((soha_id is null OR soha_id = '') AND (info_hash='6d63dd4ab199190b531752067414d4d6e6568f90')) OR
( (soha_id='6d63dd4ab199190b531752067414d4d6e6568f90'))
主键基于种子的哈希,但您可以添加字段 ID 并使用主键
定义它喜欢这个:
ALTER TABLE `xbtit_files` DROP PRIMARY KEY;
ALTER TABLE `xbtit_files` ADD `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
ALTER TABLE `xbtit_files` ADD UNIQUE (`info_hash`);
不要忘记将字段放在info_hash UNIQUE