a 复合索引按最左前缀的原则筛选,例如
create index idx_a_b_c on test (a,b,c);  #给a,b,c建立的索引

where a=xxx
where a=xxx and b=xxx 
where a=xxx and b=xxx and c=xxx
where a=xxx and c=xxx 
where b=xxx and a=xxx

前三种情况用到了索引,第四种只用到了a的索引,最后一种a和b都没用到索引

如果where条件中同时有精确条件(=,in)和范围条件,那么靠左的列先使用了范围条件则靠右的列则用不到索引,因为mysql索引只支持一个而且是最近的范围索引:
idx_a_b_c_d (a,b,c,d)

where a=? and b=? and c>? and d<?

a,b都没有使用范围条件,所以c会用到索引,但是c用了范围条件,所以d没用到索引

where a>? and b=? and c=? and d<?

a用到索引,b,c,d没用到

所以范围查找尽可能放在最后

优: select * from test where a=10 and b>50

优: select * from test where order by a
差: select * from test where order by b
差: select * from test where order by c

优: select * from test where a=10 order by a
优: select * from test where a=10 order by b
差: select * from test where a=10 order by c

优: select * from test where a>10 order by a
差: select * from test where a>10 order by b
差: select * from test where a>10 order by c

优: select * from test where a=10 and b=10 order by a
优: select * from test where a=10 and b=10 order by b
优: select * from test where a=10 and b=10 order by c

优: select * from test where a=10 and b=10 order by a
优: select * from test where a=10 and b>10 order by b
差: select * from test where a=10 and b>10 order by c

所以在建立复合索引的时候,越常用的字段放越左边,上面常用性是a>b>c所以,定义的时候是(a,b,c)

b 如果一个 Like 语句的查询条件不以通配符起始则使用索引。
如：%车 或 %车%   不使用索引。
    车%              使用索引。
    
    
c 使用函数
如果没有使用基于函数的索引，那么where子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。下面的查询就不会使用索引：

Sql代码  收藏代码
select * from staff where trunc(birthdate) = '01-MAY-82';  
 
但是把函数应用在条件上，索引是可以生效的，把上面的语句改成下面的语句，就可以通过索引进行查找。

Sql代码  收藏代码
select * from staff where birthdate < (to_date('01-MAY-82') + 0.9999);  

d 比较不匹配的数据类型
比较不匹配的数据类型也是难于发现的性能问题之一。
下面的例子中，dept_id是一个varchar2型的字段，在这个字段上有索引，但是下面的语句会执行全表扫描。

Sql代码  收藏代码
select * from dept where dept_id = 900198;  
 
这是因为oracle会自动把where子句转换成to_number(dept_id)=900198，就是3所说的情况，这样就限制了索引的使用。
把SQL语句改为如下形式就可以使用索引

Sql代码  收藏代码
select * from dept where dept_id = '900198';  

 

e 索引列的范围查找
如果某列定义了索引,对该列使用 where between and / > / < 也是会使用到索引的,会用到索引范围查找;但是如果这个范围太大,数据库觉得成本太高，可能会变成全表索引。

 

f 一个常识 对经常作为搜索条件(where),经常排序(order),经常分组(group by) 的字段建立索引能提高效率
如果作为索引的字段有越多相同的值,那么这个索引的效率越低

 

g 关于多表联查时使用到的索引的情况
在多表联查的时候，数据库会指定一个表为驱动表，另一个表为被驱动表

如下：
select a.col1,b.col2 from a join b on a.id=b.id 

其中id是两个表的主键,如果a表被判定为驱动表,那么数据库可能会全表扫描a表,并用a表的每个id探测b表的索引查找匹配的记录。

那么我们先了解在join连接时哪个表是驱动表，哪个表是被驱动表：
1.当使用left join时，左表是驱动表，右表是被驱动表
2.当使用right join时，右表时驱动表，左表是驱动表
3.当使用join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表，我们知道如果大表做驱动表，会全表扫描驱动表，那么就会效率很低。也就是说join的情况下，数据库会自动做优化。

join查询中，永远是以小表驱动大表。

例如: A是小表，B是大表
　　使用left join 时，则应该这样写select * from A a left join B b on a.code=b.code
　　A表时驱动表，B表是被驱动表

测试：A表140多条数据，B表20万左右的数据量
　　select * from A a left join B b on a.code=b.code
　　执行时间：7.5s

　　select * from B b left join A a on a.code=b.code
　　执行时间：19s

结论：小表驱动大表优于大表驱动小表

join查询在有索引条件下
　　驱动表有索引不会使用到索引
　　被驱动表建立索引会使用到索引

在以小表驱动大表的情况下，再给大表建立索引会大大提高执行速度

在我做的一个项目中有个查询,这个查询涉及到两张表:分类表和文章表
分类表 type 有20条数据,文章表 arts 有70万条数据,文章表有一个字段是is_send,用来标记文章是否发送,is_send字段的值只有两个。我想查每个分类下有多少篇文章

select t.id,t.name,count(*) as arts_count from arts a join type t on a.tid=t.id group by t.id;
 
我在arts中对tid也做了索引

上面使用了join所以,默认以type作为驱动,而且分组的对象t.id是主键,主键肯定也是做了索引的,所以上面的查询效率不会低,只花了1秒

但是如果加了一个条件 is_send=0
select t.id,t.name,count(*) as arts_count from arts a join type t on a.tid=t.id where is_send=0 group by t.id;

那么,查询时间变成了12秒

原因是is_send没有建立索引,所以以他为条件会对arts表全表扫描;
更关键的是is_send只有0和1两个值,所以即使对它建立了索引,效率也只能提高一半,而且还是0和1分布比较均匀的情况下才能提高一半,如果0占百分之90,1占百分之10,那么where is_send=0 提高的效率不到百分之10。

 

h 基于主键来取数据是最快的,基于二级索引(即普通的 index)则要进行两次索引查找,先找到二级索引再根据二级索引找到主键,再根据主键找到对应的记录

 

i 避免重复对一个列创建多个索引,这样会浪费空间,而且对一个列创建多个索引不会报错

 

j 使用覆盖索引可以大大提高性能
覆盖索引指所有数据可以从索引中得到,不需要去读取物理记录。例如
idx_a_b_c 

select a,b from tb1 where a=? and b=? and c=?

这就是覆盖索引,也避免了二次索引查找

 

k 利用索引排序

mysql有两种方式可以产生有序的结果:一种是文件排序(filesort)对记录排序,另一种是扫描有序的索引排序

文件排序,mysql是将取得的数据在内存中排序,如果对少量数据进行排序会很快,但如果是对大量数据排序就会很慢

order by create_time 就是文件排序
order by id 就是索引排序

但是之前做项目,都是对分页数据排序,每一页不超过100条数据,所以用文件排序也不慢

像复合索引在排序的时候也要遵循前导列和最左前缀原则,否则就不算索引排序
idx_a_b_c

order by a,b,c
where a=? and b=? order by c

这两个都复合索引排序

可以通过 explain 的extra查看是否是文件索引,显示filesort就是文件索引

 

l 避免冗余索引
冗余索引就是:
我定义了a字段为索引,有定义了(a,b)的复合索引

但是有一种情况是要定义冗余索引:比如原本我对a建立了索引,a是一个整型列,如果我突然想将a索引扩展为a,b索引,而b是一个长度较长的字符串列,那么索引会很大。此时就不得不添加一个新的复合索引，保留原本的索引

 

M 使用更短的索引

比如 我想对文章的标题建立索引，标题会很长，此时建立的索引会很大（我们知道建立索引会将索引字段单独放到一个表中存储），为此我们可以使用前缀索引，即只对标题的前多少个字符进行索引

index title (title(6))

就是只对标题的前6个字符进行索引,这样存进索引表的就不是整个标题而是标题的前6个字符

但是要确保所选择的前缀的长度的内容大部分值是唯一的

 

n where不以索引字段为条件时会全表扫描;
where以索引为条件时,如果索引效率不高时,mysql依旧会全表扫描;
所以不要对不必要的字段建立索引,例如性别

 

o innodb的主键不能太长,以防止二级索引过大。主键一般都是选整型