MYSQL教程优化篇:高效使用数据库索引的技巧方法

作者 : IT 大叔 本文共3983个字,预计阅读时间需要10分钟 发布时间: 2020-11-5

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

免责声明:
1. 本站资源转自互联网,源码资源分享仅供交流学习,下载后切勿用于商业用途,否则开发者追究责任与本站无关!
2. 本站使用「署名 4.0 国际」创作协议,可自由转载、引用,但需署名原版权作者且注明文章出处
3. 未登录无法下载,登录使用金币下载所有资源。
IT小站 » MYSQL教程优化篇:高效使用数据库索引的技巧方法

常见问题FAQ

没有金币/金币不足 怎么办?
本站已开通每日签到送金币,每日签到赠送五枚金币,金币可累积。
所有资源普通会员都能下载吗?
本站所有资源普通会员都可以下载,需要消耗金币下载的白金会员资源,通过每日签到,即可获取免费金币,金币可累积使用。

发表评论