MySQL表中即使有索引也无法使用情况

最左前缀匹配原则
MySQL 会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。所以要尽量把这些条件放在最后,把“=”条件放在前面。举例如下:
反例,不会用到 b 列的索引:
select * from xttblog where a=1 and c>0 and b=2
正例,会用到 b 列的索引:
select * from xttblog where a=1 and b=2 and c>0
在索引选择上,尽量选择区分度高的列作为索引,区分度的公式是 count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少。


默认情况下,当取出的数据超过全表数据的 20% 时,不会使用索引。


like 使用原则
反例,不使用索引:
select * from codedq where name like '%业余草%'
正例,使用索引:
select * from codedq where name like '业余草%'
or 和 union all


or 和 union all 的使用选择。推荐尽量将 or 转换为 union all。
反例,不使用索引:
select * from xttblog where name='业余草' or age='20'
正例,使用索引:
select * from xttblog where name ='业余草' union all select * from xttblog where age ='20'
索引+函数


字段加上大多数函数都不会使用索引。所以尽量把函数放在数值上。
反例,不使用索引:
select * from xttblog where truncate(price) = 888
反例,使用索引:
select * from xttblog where price > 887 and price < 889


索引列数据类型转换
如果索引列使用的类型不对,发生类型转换。那么就需要注意,很可能不会按照你的期望走索引。
反例,不使用索引
select * from xttblog where mobile = 1314520
正例,使用索引
select * from xttblog where mobile = '1314520'


列上计算
字段加运算符不会使用索引。所以尽量把运算放在数值上。
反例,不使用索引:
SELECT account, amountFROM codedqWHERE amount + 3000 > 5000;
正例,使用索引:
SELECT account, amountFROM codedqWHERE amount > 2000 ;
组合索引


使用组合索引时,必须要包括第一个列。
例如
alter table xttblog add index(a,b,c):
反例,不使用索引:
select * from xttblog where b=1 and c=2;select * from xttblog where b=1;select * from xttblog where c=2;
正例,使用索引:
select * from xttblog where a=1 AND b=1 AND c=2;select * from xttblog where a=1 AND b=1;select * from xttblog where a=1 AND c=2;
is null或is not null


尽量避免使用 is null 或 is not null。
反例,不使用索引:
SELECT …FROM codedqWHERE age IS NOT NULL;
正例,使用索引:
SELECT …FROM codedqWHERE age >0;


不等于(!=)不等于(!=)不会使用索引。
反例,不使用索引:
SELECT nameFROM xttblogWHERE age !=0;
正例,使用索引:
SELECT nameFROM xttblogWHERE age >0;


ORDER BY 子句
ORDER BY 子句只在以下的条件下使用索引:
1.
ORDER BY 中所有的列必须包含在相同的索引中并保持在索引中的排列顺序


2.
ORDER BY 中不能既有 ASC 也有 DESC


举例:
alter table xttblog add index(a,b);alter table xttblog add index(c);
反例,不使用索引:
## 不在一个索引中
select * from xttblog order by a,c;
 ## 没有出现组合索引的第一列
select * from xttblog order by b;
 ## 混合 ASC 和 DESC
select * from xttblog order by a asc, b desc; 
## where和order by用的不是同一个索引,where使用索引,order by不使用。
select * from xttblog where a=1 order by c;


正例,使用索引:
select * from xttblog order by a,b;
select * from xttblog order where a=1 order by b;
select * from xttblog order where a=1 order by a,b;
select * from xttblog order by a desc, b desc;
select * from xttblog where c=1 order by c;

分割线
打赏
YWSOS.COM 平台代运维解决方案
 评论
 发表评论
姓   名:

Powered by AKCMS