MySQL进阶04_索引_索引使用_索引设计原则

六、索引使用

-- 10000000条数据
select * from tb_sku where sn = '1200230'  //10几秒
--加索引
create index idx_sku_sn on tb_sku(sn);  //也是执行很久，要给每个sn字段加
--再查询
select * from tb_sku where sn = '1200230'  //0.多秒
1
2
3
4
5
6

一、索引失效

最左前缀法则

如果索引了多列（联合索引)，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效(后面的字段索引失效)。

profession字段必须存在，跟where后的位置没关系
1
2
3

-- 为profession gae status创建联合索引
create index idx_user_pro_age_sta on tb_user(profession,gae,status);
1
2

范围查询

联合索引中，出现> < ,范围查询 右侧的列索引失效

--解决方案
如果业务允许的情况下，尽量使用>= 或 <=
1
2
3
4

索引列运算

• 不要在索引列上进行运算操作，索引将失效

--例（查询手机号后两位是15，就不会走索引） 
explain select * from tb_user where substring(phone,10,2) = '15';
1
2

字符串不加引号

• 字符串类型字段使用时，不加引号，索引将失效

--例
explain select * from tb_user where phone = 12341311915;
explain select * from tb_user profession='软件工程' and age=31 and status=0;
1
2
3

模糊查询

• 如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

--例
explain select * from tb_user profession like '软件%'  -- 没有失效
explain select * from tb_user profession like '%工程'  -- 失效
explain select * from tb_user profession like '%工%'   -- 失效
1
2
3
4

or连接的条件

• 用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

--例 id有索引 age没有索引
explain select * from tb_user where id=1 or age=23;  --没有使用索引
1
2

解决方法

--由于age没有索引，所以即使id、phone有索引，索引也会失效。所以需要针对于age也要建立索引。
create index idx_user_age on tb_user(age);
1
2

数据分布影响

• 如果MySQL评估使用索引比全表更慢，则不使用索引

--例 绝大多数phone数据大于13132131333 ，就不会走索引了
explain select * from tb_user where phone >= '13132131323'; 
explain select * from tb_user where phone >= '13132131333'; 

-- 如果表中大部分phone不为null，就不走索引
explain select * from tb_user where phone is null;

1
2
3
4
5
6
7

二、SQL提示

场景

表中有profession的联合索引，还有profession的单列索引，查询会走哪个索引?

use index 建议 ，MySQL自己决定 ，force index 强制

三、覆盖索引

• 尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到)，减少select *。

场景：索引有id profession age status

为什么尽量不要用select * ， 因为会很容易出现回表查询

思考

解答：
给username和password建立一个联合索引，避免回表，因为联合索引下面挂着的就是id
1
2

四、前缀索引

• 当字段类型为字符串(varchar，, text等),时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘O，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

语法

create index idx_xxx on table_name(column(n))

• 单列索引与联合索引
  • 单列索引：即一个索引只包含单个列
  • 联合索引：即一个索引包含了多个列

在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。

避免查询的字段没有索引，造成回表查询
1
2
3

七、索引设计原则