今天给大家简单的介绍一下mysql的索引用法,像在我们日常业务开发中,最核心的其实就是写SQL命令,但是你写的SQL真的用到索引了吗?
索引实现原理
mysql数据库索引实现是在存储引擎中完成的,今天主要以InnoDB存储引擎为例给大家介绍一下。InnoDB存储引擎索引的实现采用B+树数据结构,今天我们主要讲应用,如果想要深究原理的童鞋,可以自己研究研究。
索引失效
索引失效的场景,基本属于老生常谈了,我这边就再啰嗦一遍:
索引优化原则
给需要的字段加索引
一般需要加索引的都是where中经常使用的字段,但是像性别这种属性字段,加索引的意义不大,因为性别这类字段选择性太低(基础/总数)。
如下所示,如果userinfo表中的数据90%以上,都是性别为男的用户,这个时候给sex字段加索引,收效甚微,
select sum(sex='男') as sexCount,sum(1) as countNum from userinfo
结果:
sexCount countNum
4 5
错误的为每个列创建独立索引
有些人可能会把where条件后面所有的字段都加上索引,但是后面却会发现这样做起到的效果收效甚微,这个时候我们因该优先使用联合索引。
如下所示:虽然我给name和age都设置了索引,但是真正使用到的只有name索引。如果用户表中存在大量名叫张三的用户,那age就需要进行二次筛选,这就会耗费相当的资源。
select * from userinfo where name= '张三' and age=21
索引使用情况:
1 SIMPLE userinfo ref name,age name 768 const 1 33.33 Using where
如果我们给name+age设置联合索引,我们就可以快速检索到我们需要的数据,不需要进行二次筛选。
索引使用情况:
1 SIMPLE userinfo ref name_age name_age 773 const,const 1 100
正确使用联合索引
联合索引一定要注意索引顺序,一般放在前面的都是选择性比较高的索引字段。
例如一个用户表,需要查询手机号为138+性别为女的数据,如果是性别+手机号这样的顺序作为联合索引,就会导致第一次索引的数据很大,再用手机接着索引效率就会特别低,如果索引顺序反过来就可以避免这类问题。
避免检索全部
一定要避免select *from的出现,因为这样不仅不会用到覆盖索引,而且数据传输也会耗费很长的时间。
如果select出来的字段都在where条件之内,这种情况就叫做覆盖索引。覆盖索引检索出来的数据可以直接返回给前端,不需要通过id去查询其它字段,所以效率更高。
order by排序字段注意重排序
使用索引字段来做排序,如果order by排序的字段不在索引中,则会导致数据重排,如果数据量较大会很耗费cpu性能。
如下所示,pwd字段不在索引里面,就会导致Using filesort的出现。
EXPLAIN
select * from userinfo where name= '张三' and age=21 ORDER BY pwd;
索引使用情况:
1 SIMPLE userinfo ref name_age name_age 773 const,const 1 100 Using index condition; Using filesort
去除冗余索引
去除不必要的冗余索引,在添加索引的时候,可能有人不注意会设置重复的索引,像用户名称+年龄的联合索引和用户名称的单列索引,就是典型的冗余索引字段。
name_age name, age NORMAL 0 A 3 0
name name NORMAL 0 A 3 0
SQL索引检查:
我们写完SQL之后,要如何确定这条SQL有没有用到索引,用到的索引具体是哪一个,不用着急,这边我们可以用explain关键字来进行SQL索引分析。explain用法,这节课暂时不多说,更多的内容我会在下一章中给大家详细介绍。
总结:
索引的内容当然不仅仅只有这些,索引用的好可以成倍的提高执行效率,但是如果用的不好,还不如不用索引。因为索引如果没生效,那加索引只会给数据库造成额外的负担。