索引建立的原则
- 在经常用作过滤器的字段上建立索引;
- 在SQL语句中经常进行GROUP BY、ORDER BY的字段上建立索引;
- 在不同值较少的字段上不必要建立索引,如性别字段;
- 对于经常存取的列避免建立索引;
- 用于联接的列(主健/外健)上建立索引;
- 在经常存取的多个列上建立复合索引,但要注意复合索引的建立顺序要按照使用的频度来确定;
- 缺省情况下建立的是非簇集索引,但在以下情况下最好考虑簇集索引,如:含有有限数目(不是很少)唯一的列;进行大范围的查询;充分的利用索引可以减少表扫描I/0的次数,有效的避免对整表的搜索。当然合理的索引要建立在对各种查询的分析和预测中,也取决于DBA的所设计的数据库结构。
索引
通过实例理解单列索引、多列索引以及最左前缀原则
实例:现在我们想查出满足以下条件的用户id:
mysql>SELECT `uid` FROM people WHERE lname`='Liu' AND `fname`='Zhiqun' AND `age`=26
因为我们不想扫描整表,故考虑用索引。
单列索引:
ALTER TABLE people ADD INDEX lname (lname);
#将lname列建索引,这样就把范围限制在lname='Liu'的结果集1上,之后扫描结果集1,产生满足fname='Zhiqun'的结果集2,再扫描结果集2,找到 age=26的结果集3,即最终结果。
由于建立了lname列的索引,与执行表的完全扫描相比,效率提高了很多,但我们要求扫描的记录数量仍旧远远超过了实际所需 要的。虽然我们可以删除lname列上的索引,再创建fname或者age 列的索引,但是,不论在哪个列上创建索引搜索效率仍旧相似。
多列索引:
ALTER TABLE people ADD INDEX lname_fname_age (lame,fname,age);
#为了提高搜索效率,我们需要考虑运用多列索引,由于索引文件以B-Tree格式保存,所以我们不用扫描任何记录,即可得到最终结果。
最左前缀:
#顾名思义,就是最左优先,上例中我们创建了lname_fname_age多列索引,相当于创建了(lname)单列索引,(lname,fname)组合索引以及(lname,fname,age)组合索引。
注:在创建多列索引时,要根据业务需求,where子句中使用最频繁的一列放在最左边。
使用场景总结
key (cId, subCid)
where subCid=1;
where subcid=1 and cid=1 ;
in 使用索引 not 不使用 <>(!=) 不使用索引 一次只能使用1个索引 group by order by group by cId order by cId desc; (使用)
group by cId order by subCId desc; (不使用) desc asc 共存不使用索引
order by cId desc, subCid desc
order by cId desc, subCid asc
order by cId asc, subCid asc
分类
uniq, key, primary key, hash
innoDB 主键 - 聚集索引 物理存放顺序和索引保持一致
慢查询优化
slow_query_time 100
explain
mysql 优化器 a and b与b and a是一样的