薛定谔的风口猪

站在巨人的肩膀上学习,猪都能看得很远

避免写出不走索引的SQL, MySQL

在MySQL中,并不是你建立了索引,并且你在SQL中使用到了该列,MySQL就肯定会使用到那些索引的,有一些情况很可能在你不知不觉中,你就“成功的避开了”MySQL的所有索引。

现假设有t_stu表,age,sname上建立了索引

索引列参与计算

如果where条件中age列中使用了计算,则不会使用该索引

SELECT `sname` FROM `t_stu` WHERE `age`=20;-- 会使用索引
SELECT `sname` FROM `t_stu` WHERE `age`+10=30;-- 不会使用索引!!因为所有索引列参与了计算
SELECT `sname` FROM `t_stu` WHERE `age`=30-10;-- 会使用索引

故,如果需要计算,千万不要计算到索引列,想方设法让其计算到表达式的另一边去。

索引列使用了函数

同样的道理,索引列使用了函数,一样会导致相同的后果

SELECT `sname` FROM `stu` WHERE concat(`sname`,'abc') ='Jaskeyabc'; -- 不会使用索引,因为使用了函数运算,原理与上面相同
SELECT `sname` FROM `stu` WHERE `sname` =concat('Jaskey','abc'); -- 会使用索引

索引列使用了Like %XXX

SELECT * FROM `houdunwang` WHERE `uname` LIKE '前缀就走索引%' -- 走索引
SELECT * FROM `houdunwang` WHERE `uname` LIKE '后缀不走索引%' -- 不走索引

所以当需要搜索email列中.com结尾的字符串而email上希望走索引时候,可以考虑数据库存储一个反向的内容reverse_email

SELECT * FROM `table` WHERE `reverse_email` LIKE REVERSE('%.com'); -- 走索引

注:以上如果你使用REVERSE(email) = REVERSE('%.com'),一样得不到你想要的结果,因为你在索引列email列上使用了函数,MySQL不会使用该列索引

同样的,索引列上使用正则表达式也不会走索引。

字符串列与数字直接比较

这是一个坑,假设有一张表,里面的a列是一个字符char类型,且a上建立了索引,你用它与数字类型做比较判断的话:

 CREATE TABLE `t1` (`a` char(10));

 SELECT * FROM `t1` WHERE `a`='1' -- 走索引
 SELECT * FROM `t2` WHERE `a`=1 -- 字符串和数字比较,不走索引!

但是如果那个表那个列是一个数字类型,拿来和字符类型的做比较,则不会影响到使用索引

 CREATE TABLE `t2` (`b` int);

 SELECT * FROM `t2` WHERE `b`='1' -- 虽然b是数字类型,和'1'比较依然走索引

但是,无论如何,这种额外的隐式类型转换都是开销,而且由于有字符和数字比就不走索引的情况,故建议避免一切隐式类型转换

尽量避免 OR 操作

select * from dept where dname='jaskey' or loc='bj' or deptno=45 --如果条件中有or,即使其中有条件带索引也不会使用。换言之,就是要求使用的所有字段,都必须建立索引

所以除非每个列都建立了索引,否则不建议使用OR,在多列OR中,可以考虑用UNION 替换

select * from dept where dname='jaskey' union
select * from dept where loc='bj' union
select * from dept where deptno=45