mysql索引失效场景以及如何选取索引字段和索引场景(干货)

例如：一张用户信息表 有字段属性 name,age 其中name为索引

下面列举几个索引失效的情况

1. select * from userinfo where name=‘aaa’ or age=20；

例如这种情况：当语句中带有or的时候 即使有索引也会失效。

2.select * from userinfo where name like‘%aaa’ ；

例如这种情况：当语句索引 like 带%的时候索引失效（注意：如果上句为 like‘xzz’此时索引是生效的）

3.select * from userinfo where name=333;（此处只是简单做个例子，实际场景中一般name不会为数字的）

例如这种情况：如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

4.如果mysql估计使用全表扫描要比使用索引快,则不使用索引

5.假如上述将name和age设置为联合索引，一定要注意顺序，mysql联合所以有最左原则，下面以name，age的顺序讲下

（1）select * from userinfo where name=‘aaa’ and age =12；

（2）select * from userinfo where age=12 and name=‘aaa’；

例如上诉两种情况：以name，age顺序为联合索引，（1）索引是生效的，（2）索引是失效的

6.比如age为索引：select * from userinfo where age-1>20;

例如这种情况：索引失效，不要在索引上进行操作，否则索引会失效（是有类似时间转换的问题和上诉问题一样）

==========================================================================

索引值字段选择：

日常在建数据表的时候，通常会在WHERE，GROUP BY，ORDER BY等常用的字段上建立索引，当有多个字段时候，有一项原则就是该字段的去重后的值个数越多，索引建立的必要性越强。

1. 表的某个字段值得离散度越高，该字段越适合选作索引的关键字。主键字段以及唯一性约束字段适合选作索引的关键字，原因就是这些字段的值非常离散。MySQL 在处理主键约束以及唯一性约束时，考虑周全。数据库用户创建主键约束的同时， MySQL 自动创建主索引（ primary index ），且索引名称为 Primary；数据库用户创建唯一性索引时， MySQL 自动创建唯一性索引（ unique index ），默认情况下，索引名为唯一性索引的字段名。

2. 占用存储空间少的字段更适合选作索引的关键字。例如，与字符串相比，整数字段占用的存储空间较少，因此，较为适合选作索引关键字。

3. 存储空间固定的字段更适合选作索引的关键字。与 text 类型的字段相比， char 类型的字段较为适合选作索引关键字。

4. Where 子句中经常使用的字段应该创建索引，分组字段或者排序字段应该创建索引，两个表的连接字段应该创建索引。

5. 更新频繁的字段不适合创建索引，不会出现在 where 子句中的字段不应该创建索引。

什么时候建立索引情况：

1、表的主键、外键必须有索引；

2、数据量超过300的表应该有索引；

3、经常与其他表进行连接的表，在连接字段上应该建立索引；

4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；

5、索引应该建在选择性高的字段上；

6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；

7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：

A、正确选择复合索引中的主列字段，一般是选择性较好的字段；

B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；

C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；

D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；

E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；

8、频繁进行数据操作的表，不要建立太多的索引；

9、删除无用的索引，避免对执行计划造成负面影响；

===========================================================================

记录学习，每天进步一点点的橘子大王

喜欢就关注我吧