explain是解释计划,说明SQL的执行情况

explain select * from t_content where content_id = 17076710;
drop index idx_uid on t_content;
create index idx_uid on t_content(uid);
explain select * from t_content where uid=16940130;

drop index idx_uid on t_content;
create index idx_uid on t_content(uid);

精准匹配,允许使用btree索引

explain select * from t_content where uid = 16940130;

范围匹配,允许使用btree索引

explain select * from t_content where uid > 1260000 and uid < 12610000;

查询优化器会自动进行类型转换,但仍然建议使用与定义相符的类型

explain select * from t_content where uid like '1694%';

drop index idx_share_url on t_content;
create index idx_share_url on t_content(share_url);

字符串字段btree索引允许进行"前缀查询"

explain select * from t_content where share_url like 'http://a.f.budejie.com/share/%';

后缀查询与模糊匹配btree均不支持

explain select * from t_content where share_url like '%http://a.f.budejie.com/share/17076710';
explain select * from t_content where share_url like '%http://a.f.budejie.com/share/17076710%';

drop index idx_uid_sid on t_content;
create index idx_uid_sid on t_content( uid , source_id );

复合索引查询条件必须包含左侧列

EXPLAIN select * from t_content where uid = 14206986 ;

直接书写右侧列将导致数据无法查询,如果书写右侧列,必须要把左侧列书写上

EXPLAIN select * from t_content where uid=14206986 and source_id = 13054 ;

<>与not in会导致不使用索引

EXPLAIN select * from t_content where source_id <> 13054 ;
EXPLAIN select * from t_content where source_id <=13053 or source_id >=13055;