mysql中⾛与不⾛索引的情况汇集（待全量实验）
说明
在MySQL中，并不是你建⽴了索引，并且你在SQL中使⽤到了该列，MySQL就肯定会使⽤到那些索引的，有⼀些情况很可能在你不知不觉中，你就“成功的避开了”MySQL的所有索引。
索引列参与计算
如果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列中结尾的字符串⽽email上希望⾛索引时候，可以考虑数据库存储⼀个反向的内容reverse_email
SELECT * FROM `table` WHERE `reverse_email` LIKE REVERSE('%'); -- ⾛索引
  注：以上如果你使⽤REVERSE(email) = REVERSE(’%’)，⼀样得不到你想要的结果，因为你在索引列email列上使⽤了函
数，MySQL不会使⽤该列索引
同样的，索引列上使⽤正则表达式也不会⾛索引。
字符串列与数字直接⽐较
这是⼀个坑，假设有⼀张表,⾥⾯的a列是⼀个字符char类型，且a上建⽴了索引，你⽤它与数字类型做⽐较判断的话：
linux套接字编程CREATE TABLE `t1` (`a` char(10));
c语言合法用户标识符有哪些
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
boolean类型在数据库中对应
--如果条件中有or，即使其中有条件带索引也不会使⽤。换⾔之，就是要求使⽤的所有字段，都必须建⽴索引
所以除⾮每个列都建⽴了索引，否则不建议使⽤OR，在多列OR中，可以考虑⽤UNION 替换
select * from dept where dname='jaskey' union
select * from dept where loc='bj' union
select * from dept where deptno=45
ORDER BY 操作
在ORDER BY操作中，排序的列同时也在WHERE中时，MYSQL将⽆法使⽤索引；
MySQL索引通常是被⽤于提⾼WHERE条件的数据⾏匹配或者执⾏联结操作时匹配其它表的数据⾏的搜索速度。
MySQL也能利⽤索引来快速地执⾏ORDER BY和GROUP BY语句的排序和分组操作。
通过索引优化来实现MySQL的ORDER BY语句优化：
1、ORDER BY的索引优化。如果⼀个SQL语句形如：
SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort];
在[sort]这个栏位上建⽴索引就可以实现利⽤索引进⾏order by 优化。
2、WHERE + ORDER BY的索引优化，形如：
SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort];
建⽴⼀个联合索引(columnX,sort)来实现order by 优化。
注意：如果columnX对应多个值，如下⾯语句就⽆法利⽤索引来实现order by的优化
mysql面试题索引
SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] IN ([value1],[value2],…) ORDER BY[sort];
3、WHERE+ 多个字段ORDER BY
SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10;
建⽴索引(uid,x,y)实现order by的优化,⽐建⽴(x,y,uid)索引效果要好得多。
MySQL Order By不能使⽤索引来优化排序的情况
* 对不同的索引键做 ORDER BY ：(key1,key2分别建⽴索引)
SELECT * FROM t1 ORDER BY key1, key2;
* 在⾮连续的索引键部分上做 ORDER BY：(key_part1,key_part2建⽴联合索引;key2建⽴索引)
SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;
* 同时使⽤了 ASC 和 DESC：(key_part1,key_part2建⽴联合索引)
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
* ⽤于搜索记录的索引键和做 ORDER BY 的不是同⼀个：(key1,key2分别建⽴索引)
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
* 如果在WHERE和ORDER BY的栏位上应⽤表达式(函数)时，则⽆法利⽤索引来实现order by的优化
SELECT * FROM t1 ORDER BY YEAR(logindate) LIMIT 0,10;
特别提⽰:
1>mysql⼀次查询只能使⽤⼀个索引。如果要对多个字段使⽤索引，建⽴复合索引。
2>在ORDER BY操作中，MySQL只有在排序条件不是⼀个查询条件表达式的情况下才使⽤索引。
Offset Limit 操作
存在性能问题的⽅式
SELECT * FROM myTable ORDER BY `id` LIMIT 1000000, 30
写出这样SQL语句的⼈肯定⼼⾥是这样想的：MySQL数据库会直接定位到符合条件的第1000000位，然后再取30条数据。然⽽，实际上MySQL不是这样⼯作的。
LIMIT 1000000, 30 的意思是：扫描满⾜条件的1000030⾏，扔掉前⾯的1000000⾏，然后返回最后的30⾏。
mysql 的 limit 给分页带来了极⼤的⽅便，但数据偏移量⼀⼤，limit 的性能就急剧下降。
以下是两条查询语句，都是取10条数据，但性能就相去甚远
所以不能简单的使⽤ limit 语句实现数据分页。
探究
为什么 offset 偏⼤之后 limit 查会变慢？这需要了解 limit 操作是如何运作的，以下⾯这句查询为例：
select * from table_name limit 10000,10
这句 SQL 的执⾏逻辑是
1.从数据表中读取第N条数据添加到数据集中
2.重复第⼀步直到 N = 10000 + 10
3.根据 offset 抛弃前⾯ 10000 条数
4.返回剩余的 10 条数据
显然，导致这句 SQL 速度慢的问题出现在第⼆步！这前⾯的 10000 条数据完全对本次查询没有意义，但是却占据了绝⼤部分的查询时间！如何解决？⾸先我们得了解为什么数据库为什么会这样查询。
⾸先，数据库的数据存储并不是像我们想象中那样，按表按顺序存储数据，⼀⽅⾯是因为计算机存储本⾝就是随机读写，另⼀⽅⾯是因为数据的操作有很⼤的随机性，即使⼀开始数据的存储是有序的，经过⼀系列的增删查改之后也会变得凌乱不堪。所以数据库的数据存储是随机的，使⽤ B+Tree， Hash 等⽅式组织索引。所以当你让数据库读取第 10001 条数据的时候，数据库就只能⼀条⼀条的去查去数。
第⼀次优化
根据数据库这种查的特性，就有了⼀种想当然的⽅法，利⽤⾃增索引（假设为id）：
select * from table_name where (id >= 10000) limit 10
由于普通搜索是全表搜索，适当的添加 WHERE 条件就能把搜索从全表搜索转化为范围搜索，⼤⼤缩⼩搜索的范围，从⽽提⾼搜索效率。这个优化思路就是告诉数据库：「你别数了，我告诉你，第1000
1条数据是这样的，你直接去拿吧。」
但是你可能已经注意到了，这个查询太简单了，没有任何的附加查询条件，如果我需要⼀些额外的查询条件，⽐如我只要某个⽤户的数据，这种⽅法就⾏不通了。
可以见到这种思路是有局限性的，⾸先必须要有⾃增索引列，⽽且数据在逻辑上必须是连续的，其次，你还必须知道特征值。
如此苛刻的要求，在实际应⽤中是不可能满⾜的。
第⼆次优化
说起数据库查询优化，第⼀时间想到的就是索引，所以便有了第⼆次优化：先查出需要数据的索引列（假设为 id），再通过索引列查出需要的数据。
select * from table_name where( user = xxx ) limit 10000,10
相⽐较结果是（500w条数据）：第⼀条花费平均耗时约为第⼆条的 1/3 左右。
同样是较⼤的 offset，第⼀条的查询更为复杂，为什么性能反⽽得到了提升？
这涉及到 mysql 主索引的数据结构 b+Tree ，这⾥不展开，基本原理就是：
⼦查询只⽤到了索引列，没有取实际的数据，所以不涉及到磁盘IO，所以即使是⽐较⼤的 offset 查询速度也不会太差。
利⽤⼦查询的⽅式，把原来的基于 user 的搜索转化为基于主键（id）的搜索，主查询因为已经获得了准确的索引值，所以查询过程也相对较快。
第三次优化
在数据量⼤的时候 in 操作的效率就不怎么样了，我们需要把 in 操作替换掉，使⽤ join 就是⼀个不错的选择
select * from table_name inner join ( select id from table_name where (user = xxx) limit 10000,10) b using (id)vim编辑器退出
⾄此 limit 在查询上的优化就告⼀段落了。如果还有更好的优化⽅式，欢迎留⾔告知数据库基础电子书
最终优化
技术上的优化始终是有天花板的，业务的优化效果往往更为显著。
⽐如在本例中，因为数据的时效性，我们最终决定，只提供最近15天内的操作⽇志，在这个前提下，偏移值 offset 基本不会超过⼀万，这样⼀来，即使是没有经过任何优化的 sql，其执⾏效率也变得可以接受了，所以优化不能局限于技术层⾯，有时候对需求进⾏⼀下调整，可能会达到意想不到的效果

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。