六、索引使用
-- 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.多秒
一、索引失效
最左前缀法则
如果索引了多列(联合索引),要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始,并且不跳过索引中的列。如果跳跃某一列,索引将部分失效(后面的字段索引失效)。
profession字段必须存在,跟where后的位置没关系
-- 为profession gae status创建联合索引
create index idx_user_pro_age_sta on tb_user(profession,gae,status);
范围查询
联合索引中,出现> < ,范围查询 右侧的列索引失效
--解决方案
如果业务允许的情况下,尽量使用>= 或 <=
索引列运算
- 不要在索引列上进行运算操作,索引将失效
--例(查询手机号后两位是15,就不会走索引)
explain select * from tb_user where substring(phone,10,2) = '15';
字符串不加引号
- 字符串类型字段使用时,不加引号,索引将失效
--例
explain select * from tb_user where phone = 12341311915;
explain select * from tb_user profession='软件工程' and age=31 and status=0;
模糊查询
- 如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引失效。
--例
explain select * from tb_user profession like '软件%' -- 没有失效
explain select * from tb_user profession like '%工程' -- 失效
explain select * from tb_user profession like '%工%' -- 失效
or连接的条件
- 用or分割开的条件,如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到。
--例 id有索引 age没有索引
explain select * from tb_user where id=1 or age=23; --没有使用索引
解决方法
--由于age没有索引,所以即使id、phone有索引,索引也会失效。所以需要针对于age也要建立索引。
create index idx_user_age on tb_user(age);
数据分布影响
- 如果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;
二、SQL提示
场景
表中有profession的联合索引,还有profession的单列索引,查询会走哪个索引?
use index 建议 ,MySQL自己决定 ,force index 强制
三、覆盖索引
- 尽量使用覆盖索引(查询使用了索引,并且需要返回的列,在该索引中已经全部能够找到),减少select *。
场景:索引有id profession age status
为什么尽量不要用select * , 因为会很容易出现回表查询
思考
解答:
给username和password建立一个联合索引,避免回表,因为联合索引下面挂着的就是id
四、前缀索引
- 当字段类型为字符串(varchar,, text等),时,有时候需要索引很长的字符串,这会让索引变得很大,查询时,浪费大量的磁盘O,影响查询效率。此时可以只将字符串的一部分前缀,建立索引,这样可以大大节约索引空间,从而提高索引效率。
语法
create index idx_xxx on table_name(column(n))
- 单列索引与联合索引
- 单列索引:即一个索引只包含单个列
- 联合索引:即一个索引包含了多个列
在业务场景中,如果存在多个查询条件,考虑针对于查询字段建立索引时,建议建立联合索引,而非单列索引。
避免查询的字段没有索引,造成回表查询