[TOC] # 独立的列 如果查询中的列不是独立的,则mysql就不会使用索引.`"独立的列"`是指索引列不能是表达式的一部分,也不能是函数的参数 例如,下面这个查询无法使用actor_id列的索引 ~~~ select actor_id from sakila.actor where actor_id + 1 = 5; ~~~ 我们应该养成简化where条件的习惯 # 前缀索引和索引选择性 有时候需要索引很长的字符列,这会让索引变得大且慢.一个策略是前面提到的哈希索引,但有时这还不够,还可以做些什么? 通常可以索引开始的部分字符,这样可以大大节约索引空间,从而提高索引效率,但是这样降低索引的选择性,不重复的索引值(也称为基数)和数据表的记录总数(`#T`)的比值,范围从 `1/#T`到1之间.索引的选择性越高则查询效率越高,因为选择性高的索引可以让mysql在查找时过滤到更多的行.唯一索引的选择性是1,这是最好的索引选择性,性能也是最好 一般情况下某个列前缀的选择性也是足够高的,足以满足查询性能.对于blog,text或者很长的varchar类型的列,必须使用前缀索引,因为mysql不允许索引这些列的完整长度 # 多列索引 很多人对多列索引的理解还不够.一个常见的错误就是,为每个列创建独立的索引,或者按照错误的顺序创建多列索引 这种索引策略,一般是由于人们听到一些专家建议:把where条件里面的列都加上索引,这样模糊的建议导致的 实际上这个建议是非常错误的.这样一来最好的情况也只能是一星索引,其性能比起真正最优的索引可能差几个数量级.有时候如果无法设计一个三星索引,那么还不如忽略掉where子句,集中精力优化索引列的顺序,或者创建一个全覆盖索引 在多个列上建立单独索引大部分情况下并不能提高mysql的查询性能.mysql5.0和更新版本引入一种叫"索引合并"的策略,一定程度上可以使用表上的多个单列索引来定位指定的行.更早版本的mysql只能使用其中某一个单列索引,然而这种情况下没有哪一个独立的单列索引是非常有效 例如,表film_actor在字段film_id和actor_id上各有一个单列索引.但对于下面这个查询where条件,这两个单列索引都不是好的选择 ~~~ select film_id,actor_id from sakila.film_actor where actor_id =1 or film_id =1; ~~~ 在老的mysql版本中,mysql对这个表的查询会使用全表扫描.除非改写成两个表的查询union的方式 ~~~ select film_id,actor_id from sakila.film_actor where actor_id =1 union all select film_id,actor_id from sakila.film_actor where film_id =1 and actor_id <> 1; ~~~ 但是在mysql5.0和更新版本中,查询能够同时使用这两个单列索引进行扫描,并将结果进行合并.这种算法有3个变种:OR条件的联合,add条件的相交组合前两种情况联合及提交 下面的查询就是使用2个索引扫描的联合,通过explain中的extra列就可以看到这一点  mysql会使用这类技术优化复杂查询,所以在某些语句的extra列还可以看到嵌套操作 索引合并策略有时候是一种优化的结果,但实际上更多说明了表上的索引建的很糟糕 * 当初学服务器对多个索引做相交操作时(通常有多个AND条件),通常意味着需要一个包含所有相关列的多列索引,而不是多个独立的单列索引 * 当服务器需要对多个索引做联合操作时(通常有多个OR条件),通常需要耗费大量CPU和内存资源在算法的缓存,排序和合并操作上.特别是当其中有些索引的选择性不高,需要合并扫描返回大量数据的时候 * 更重要的是,优化器不会把这些计算到"查询成本"(cost)中,优化器只关心随机页面读取.这会使得查询的成本被"低估",导致该执行计划还不如走全表扫描.这样做不但会消耗更多的CPU和内存资源,还可能会影响查询的并发性,但如果是单独运行这样的查询往往会忽略并发性的影响,通常来说,还不如像在mysql4.1或更早的时代一样,将查询改写成UNION方式更好 如果在EXPLAIN中看到有索引合并,应该好好检查一下查询和表的结构,看是不是最优的,也可以通过参数optimizer_switch来关闭索引合并功能.也可以使用IGNORE INDEX提示让优化器忽略掉某些索引