(1) 最左前缀匹配原则
(2) 尽量选择区分度高的列作为索引。
(3) =和in可以乱序
(4) 索引列不能参与计算
(5) 尽量的扩展索引,不要新建索引。
建索引的几大原则
(1) 最左前缀匹配原则
对于多列索引,总是从索引的最前面字段开始,接着往后,中间不能跳过。比如创建了多列索引(name,age,sex),会先匹配name字段,再匹配age字段,再匹配sex字段的,中间不能跳过。mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。
一般,在创建多列索引时,where子句中使用最频繁的一列放在最左边。
看一个补符合最左前缀匹配原则和符合该原则的对比例子。
实例:表c2c_db.t_credit_detail建有索引(Flistid
,Fbank_listid
)
不符合最左前缀匹配原则的sql语句:
select * from t_credit_detail where Fbank_listid='201108010000199'\G
该sql直接用了第二个索引字段Fbank_listid,跳过了第一个索引字段Flistid,不符合最左前缀匹配原则。用explain命令查看sql语句的执行计划,如下图:
从上图可以看出,该sql未使用索引,是一个低效的全表扫描。
符合最左前缀匹配原则的sql语句:
select * from t_credit_detail where Flistid='2000000608201108010831508721' and Fbank_listid='201108010000199'\G
该sql先使用了索引的第一个字段Flistid,再使用索引的第二个字段Fbank_listid,中间没有跳过,符合最左前缀匹配原则。用explain命令查看sql语句的执行计划,如下图:
从上图可以看出,该sql使用了索引,仅扫描了一行。
对比可知,符合最左前缀匹配原则的sql语句比不符合该原则的sql语句效率有极大提高,从全表扫描上升到了常数扫描。
(2) 尽量选择区分度高的列作为索引。
比如,我们会选择学号做索引,而不会选择性别来做索引。
(3) =和in可以乱序
比如a = 1 and b = 2 and c = 3,建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式。
(4) 索引列不能参与计算,保持列“干净”
比如:Flistid+1>‘2000000608201108010831508721‘。原因很简单,假如索引列参与计算的话,那每次检索时,都会先将索引计算一次,再做比较,显然成本太大。
(5) 尽量的扩展索引,不要新建索引。
比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。