mysql索引技巧_Mysql优化技巧 使用索引的十五个小技巧

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的主键不能太长,以防止二级索引过大。主键一般都是选整型