mysql索引的使用及语句优化

一:mysql里目前只支持4种索引分别是:b-tree,full-text,hash以及r-tree索引 b-tree索引应该是mysql里最广泛的索引的了,除了archive,基本所有的存储引擎都支持它. 1.b-tree在myisam里的形式和innodb稍有不同 在innodb里面有两种形态:其一是primary key形态其leaf node里存放的是数据.而且不仅存放了索引键的数据,还存放了其他字段的数据.其二是secondary index,其leaf node和普通的b-tree差不多,只是还存放了指向主键的信息. 而在myisam里,主键和其他的并没有太大区别.不过和innodb不太一样的地方是,在myisam里,leaf node里存放的不是主键的信息,存的是指向数据文件里的对应数据行的信息. 2.hash索引,目前我所知道的就只有memory和ndb cluster支持这种索引. hash索引由于其结构,所以在每次查询的时候直接一次到位不行b-tree那样,一点点的前进,所以hash索引的效率高于b-tree.但hash也有缺点,主要如下: (1)由于存放的是hash值,所以仅支持<=>以及in操作. (2)hash索引无法通过操作索引来排序,这是因为存放的时候经过hash计算,但是计算的hash值和存放的不一定相等,所以无法排序. (3)在组合所以里,无法对部分使用索引. (4)不能避免全表扫描,只是由于在memory表里支持非唯一值hash索引,就是不同的索引键,可能存在相同的hash值. (5)当存在大量相同hash值得时候,hash索引的效率会变低. 3.full-text索引 full-text在mysql里仅有myisam支持它,而且支持full-text的字段只有char,varchar,以及text数据类型,full-text主要是用来代替like "%***%"效率低下的问题的. 4.r-tree索引 r-tree在mysql很少使用,仅支持geometry数据类型,支持该类型的存储引擎只有myisam,bdb,innodb,ndb,archive几种.相对于b-tree,r-tree的优势在于范围查找. 二:mysql里sql语句值得注意的地方 1.myisam里所以键的长度仅支持1000字节,innodb是767. 2.blob和text字段仅支持前缀索引. 3.使用!=以及<>不等于的时候mysql不使用索引. 4.当在字段时候函数的时候,mysql无法使用索引,在join的时候条件字段类型不一致的时候,mysql无法使用索引,在组合索引里使用非第一个索引时也不使用索引. 5.在使用like的时候,以%开头,即"%***"的时候无法使用索引,在使用or的时候,要求or前后字段都有索引. 有时候mysql query optimizer会认为使用索引并不是最优计划,所以不使用索引,可以在sql语句里可以用use,force index,当然有时候使用也不会比不用快,所以需要忽