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;
