1. 使用函数或表达式
SELECT * FROM table WHERE DATE(timestamp) = '2021-06-01';
查询语句中使用了 DATE 函数,这是一个非常常见的误区,因为使用函数或表达式会让索引失效。正确的方式是将条件变形成区间查询:
SELECT * FROM table WHERE timestamp>='2021-06-01' AND timestamp<'2021-06-02';
2. 索引列参与运算
SELECT * FROM table WHERE id+100 = 200;
查询语句中 id 参与了运算,这也会让索引失效。正确的方式是将条件变形成单独的比较:
SELECT * FROM table WHERE id = 100;
3. 字符串类型进行比较
SELECT * FROM table WHERE name = 'Tom';
如果索引列是字符串类型,使用 WHERE 子句进行比较时,必须保证两端的列类型一致,否则索引也会失效。最好把数据类型都转换为字符串类型:
SELECT * FROM table WHERE name = 'Tom' COLLATE utf8_general_ci;
4. OR 运算符
SELECT * FROM table WHERE name = 'Tom' OR age = 18;
如果有 OR 运算符,即使其中一个子句使用了索引,整个查询也会失效。一个可行的解决方案是使用 UNION 运算符:
SELECT * FROM table WHERE name = 'Tom' UNION ALL SELECT * FROM table WHERE age = 18;
综上所述,要想避免索引失效,需要尽量避免使用函数、表达式、索引列参与运算、字符串类型进行比较和 OR 运算符。只有在确保使用索引的情况下,才能提高查询性能。