为什么你创建的数据库索引没有生效,索引失效的条件!

栏目: 数据库 · 发布时间: 5年前

内容简介:几乎所有的小伙伴都可以随口说几句关于创建索引的优缺点,也知道什么时候创建索引能够提高我们的查询性能,什么时候索引会更新,但是你有没有注意到,即使你设置了索引,有些时候索引他是不会生效的!这不仅考察了大家对索引的了解程度,还要让大家在使用的时候能够正确的使用。以下介绍了一些可能会造成索引失效的特殊情况,希望大家在平时开发和面试的时候能够注意到!首先在接着探讨之前,我们先说一下,如何判断数据库的索引是否生效!相信大家应该猜到了,就是explain!explain显示了MySQL如何使用索引来处理select语句

几乎所有的小伙伴都可以随口说几句关于创建索引的优缺点,也知道什么时候创建索引能够提高我们的查询性能,什么时候索引会更新,但是你有没有注意到,即使你设置了索引,有些时候索引他是不会生效的!这不仅考察了大家对索引的了解程度,还要让大家在使用的时候能够正确的使用。以下介绍了一些可能会造成索引失效的特殊情况,希望大家在平时开发和面试的时候能够注意到!

一、如何判断数据库索引是否生效

首先在接着探讨之前,我们先说一下,如何判断数据库的索引是否生效!相信大家应该猜到了,就是explain!explain显示了 MySQL 如何使用索引来处理select语句以及连接表。他可以帮助选择更好的索引和写出更优化的查询语句。

例如我们有一张表user,为name列创建索引name_index,如下所示:

为什么你创建的数据库索引没有生效,索引失效的条件!

使用explain分析语句如下:

为什么你创建的数据库索引没有生效,索引失效的条件!

可以看到,使用explain显示了很多列,各个关键字的含义如下:

  • table:顾名思义,显示这一行的数据是关于哪张表的;

  • type:这是重要的列,显示连接使用了何种类型。从 最好到最差 的连接类型为:const、eq_reg、ref、range、indexhe和ALL;

  • possible_keys:显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从where语句中选择一个合适的语句;

  • key: 实际使用的索引。如果为NULL,则没有使用索引。很少的情况下,MySQL会选择优化不足的索引。这种情况下,可以在Select语句中使用USE INDEX(indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MySQL忽略索引;

  • key_len:使用的索引的长度。在不损失精确性的情况下,长度越短越好;

  • ref:显示索引的哪一列被使用了,如果可能的话,是一个常数;

  • rows:MySQL认为必须检查的用来返回请求数据的行数;

  • Extra:关于MySQL如何解析查询的额外信息。

具体的各个列所能表示的值以及含义可以参考MySQL官方文档介绍,地址: dev.mysql.com/doc/refman/…

二、哪些场景会造成索引生效

1、应尽量避免在 where 子句中使用 != 或 <> 操作符,否则引擎将放弃使用索引而进行全表扫描;

为什么你创建的数据库索引没有生效,索引失效的条件!

2、尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,即使其中有条件带索引也不会使用,这也是为什么尽量少用 or 的原因;

为什么你创建的数据库索引没有生效,索引失效的条件!

3、对于多列索引,不是使用的第一部分,则不会使用索引;

4、如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不会使用索引;

为什么你创建的数据库索引没有生效,索引失效的条件!

5、like的模糊查询以 % 开头,索引失效;

为什么你创建的数据库索引没有生效,索引失效的条件!

6、应尽量 避免 在 where 子句中对字段进行 表达式操作 ,这将导致引擎放弃使用索引而进行全表扫描;

如:

select id from t where num/2 = 100 
复制代码

应改为:

select id from t where num = 100*2;
复制代码

7、应尽量 避免 在 where 子句中对字段进行 函数操作 ,这将导致引擎放弃使用索引而进行全表扫描;

例如:

select id from t where substring(name,1,3) = 'abc' – name;
复制代码

以abc开头的,应改成:

select id from t where name like ‘abc%’ 
复制代码

例如:

select id from t where datediff(day, createdate, '2005-11-30') = 0 – '2005-11-30';
复制代码

应改为:

select id from t where createdate >= '2005-11-30' and createdate < '2005-12-1';
复制代码

8、不要在 where 子句中的 “=” 左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引;

9、如果MySQL估计使用全表扫描要比使用索引快,则不使用索引;

10、不适合键值较少的列(重复数据较多的列)

假如索引列TYPE有5个键值,如果有1万条数据,那么 WHERE TYPE = 1将访问表中的2000个数据块。再加上访问索引块,一共要访问大于200个的数据块。如果全表扫描,假设10条数据一个数据块,那么只需访问1000个数据块,既然全表扫描访问的数据块少一些,肯定就不会利用索引了。


以上所述就是小编给大家介绍的《为什么你创建的数据库索引没有生效,索引失效的条件!》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

Pro JavaScript Design Patterns

Pro JavaScript Design Patterns

Dustin Diaz、Ross Harmes / Apress / 2007-12-16 / USD 44.99

As a web developer, you’ll already know that JavaScript™ is a powerful language, allowing you to add an impressive array of dynamic functionality to otherwise static web sites. But there is more power......一起来看看 《Pro JavaScript Design Patterns》 这本书的介绍吧!

RGB转16进制工具
RGB转16进制工具

RGB HEX 互转工具

MD5 加密
MD5 加密

MD5 加密工具

正则表达式在线测试
正则表达式在线测试

正则表达式在线测试