Mysql索引使用的正确姿势

素颜马尾好姑娘i 2021-10-19 07:10 346阅读 0赞

今天给大家简单的介绍一下mysql的索引用法，像在我们日常业务开发中，最核心的其实就是写SQL命令，但是你写的SQL真的用到索引了吗？

索引实现原理

mysql数据库索引实现是在存储引擎中完成的，今天主要以InnoDB存储引擎为例给大家介绍一下。InnoDB存储引擎索引的实现采用B+树数据结构，今天我们主要讲应用，如果想要深究原理的童鞋，可以自己研究研究。

索引失效

索引失效的场景，基本属于老生常谈了，我这边就再啰嗦一遍：

1.  少用or，用它来连接时索引会失效。
2.  like查询以%开头。
3.  字符串不加单引号索引失效。
4.  如果mysql估计使用全表扫描要比使用索引快,则不使用索引。
5.  在MYSQL使用不等于（<,>,!=）的时候无法使用索引，会导致索引失效。
6.  is null或者is not null 也会导致无法使用索引。
7.  不在索引列上做任何操作（计算，函数，（自动或者手动）类型装换），会导致索引失效而导致全表扫描。
8.  最佳左前缀法则——如果索引了多列，要遵守最左前缀法则。

索引优化原则

给需要的字段加索引

一般需要加索引的都是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用法，这节课暂时不多说，更多的内容我会在下一章中给大家详细介绍。

![image][]

总结：

索引的内容当然不仅仅只有这些，索引用的好可以成倍的提高执行效率，但是如果用的不好，还不如不用索引。因为索引如果没生效，那加索引只会给数据库造成额外的负担。

**想要更多干货、技术猛料的孩子，快点拿起手机扫码关注我，我在这里等你哦~**

![aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy9zdTUxaWJiaWF5dnhzZEkwODVpY1JNWWljTDVFaWIwMVkxZ0drcTVNdWxMdXJ6T0QzU251V2oydzA5cjgwSnBTdm5xSTJtV2g0QUJ5RmhJTnZ1bHhwS2I2b1JBLzY0MD93eF9mbXQ9anBlZw][]

**林老师带你学编程**：[https://wolzq.com][https_wolzq.com]

[image]: /images/20211018/acfa2ad2245a4b85b1657b2af0f13aa2.png
[aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy9zdTUxaWJiaWF5dnhzZEkwODVpY1JNWWljTDVFaWIwMVkxZ0drcTVNdWxMdXJ6T0QzU251V2oydzA5cjgwSnBTdm5xSTJtV2g0QUJ5RmhJTnZ1bHhwS2I2b1JBLzY0MD93eF9mbXQ9anBlZw]: /images/20211018/849e14053c0044d9b03e88dc85e9a2a6.png
[https_wolzq.com]: https://wolzq.com/