SELECT * FROM user WHERE DATE(create_time) = ‘2020-09-03’;
3.索引列参与计算,不走索引
1
2
3
SELECT `username` FROM `t_user` WHERE age=20;-- 会使用索引
SELECT `username` FROM `t_user` WHERE age+10=30;-- 不会使用索引
SELECT `username` FROM `t_user` WHERE age=30-10;-- 会使用索引
4.索引列使用函数,可能不走索引
1
SELECT username FROM t_user WHERE concat(username,'1') = 'admin1';-- 不会使用索引,因为使用了函数运算,原理与上面相同SELECT username FROM t_user WHERE username = concat('admin','1');-- 会使用索引
5.索引列使用 like 语句,可能不走索引
1
2
3
SELECT * FROM USER WHERE username LIKE 'mysql测试%'--走索引
SELECT * FROM USER WHERE username LIKE '%mysql测试'--不走索引
SELECT * FROM USER WHERE username LIKE '%mysql测试%'--不走索引
6.数据类型隐式转换,字符串列与数字直接比较,不走索引
1
2
3
-- stock_code字符串类型带索引
SELECT * FROM `stock_data` WHERE stock_code = '600538'--走索引
SELECT * FROM `stock_data` WHERE stock_code = 600538--不走索引
7.尽量避免 OR 操作,只要有一个字段没有索引,改语句就不走索引,不走索引
1
2
3
4
-- stock_code带索引,open不带索引
SELECT * FROM `stock_data` WHERE `stock_code` = '600538'OR `open` = 6.62-- 不走索引
-- stock_code带索引,up_down_pre带索引
SELECT * FROM `stock_data` WHERE `stock_code` = '600538'OR `up_down_pre` = 5.1-- 走索引
8.is null,is not null也无法使用索引,不走索引
1
2
SELECT * FROM t_user WHERE username IS NULL -- 不走索引
SELECT * FROM t_user WHERE username IS NOT NULL -- 不走索引
9.索引列使用 in 语句,可能不走索引
1
2
3
4
5
-- stock_code数据类型为varchar
SELECT * FROM `stock_data` WHERE `stock_code` IN ('600538') -- 走索引
SELECT * FROM `stock_data` WHERE `stock_code` IN ('600538','688663','688280') -- 走索引
SELECT * FROM `stock_data` WHERE `stock_code` IN (大量数据) -- 不走索引
SELECT * FROM `stock_data` WHERE `stock_code` IN (600538) -- 不走索引