当前位置:首页 > 数码 > 索引失效的场景-MySQL (索引失效的场景)

索引失效的场景-MySQL (索引失效的场景)

admin1个月前 (04-15)数码7
当数据库查询无法有效利用索引时,就会发生索引失效,这会显著降低查询性能。以下是 MySQL 中一些常见的索引失效场景:

1. 使用非前导列进行查询

索引列的顺序很重要。如果查询使用非前导列,则索引将无法使用,因为 MySQL 只能从左到右使用索引。 sql CREATE TABLE mytable (id INT, name VARCHAR(255), age INT); CREATE INDEX idx_name ON mytable (name); 以下查询将使用索引: sql SELECT FROM mytable WHERE name = 'John'; 以下查询将不会使用索引,因为它是使用非前导列 age: sql SELECT FROM mytable WHERE age = 30;

2. 对索引列使用函数或表达式

如果对索引列使用函数或表达式,则索引将无法使用。 sql CREATE TABLE mytable (id INT, name VARCHAR(255), age INT); CREATE INDEX idx_name ON mytable (name); 以下查询将使用索引: sql SELECT FROM mytable WHERE name LIKE 'John%'; 以下查询将不会使用索引,因为它使用函数 UPPER(): sql SELECT FROM mytable WHERE UPPER(name) = 'JOHN'; mysql索引

3. 隐式数据类型转换

如果进行隐式数据类型转换,则索引将无法使用。 sql CREATE TABLE mytable (id INT, name VARCHAR(255), age INT); CREATE INDEX idx_age ON mytable (age); 以下查询将使用索引: sql SELECT FROM mytable WHERE age = 30; 以下查询将不会使用索引,因为它进行隐式数据类型转换将字符串 '30' 转换为整数: sql SELECT FROM mytable WHERE age = '30';

4. 使用 OR 条件连接不同列

如果在索引列之间使用 OR 条件,则索引将无法使用。 sql CREATE TABLE mytable (id INT, name VARCHAR(255), age INT); CREATE INDEX idx_name ON mytable (name); CREATE INDEX idx_age ON mytable (age); 以下查询将使用索引: sql SELECT FROM mytable WHERE name = 'John' OR age = 30; 以下查询将不会使用索引: sql SELECT FROM mytable WHERE name = 'John' AND age = 30;

5. 使用 LIKE 以通配符开始的模式

如果在 LIKE 模式中以通配符 % 开始,则索引将无法使用。 sql CREATE TABLE mytable (id INT, name VARCHAR(255)); CREATE INDEX idx_name ON mytable (name); 以下查询将使用索引: sql SELECT FROM mytable WHERE name LIKE 'John%'; 以下查询将不会使用索引: sql SELECT FROM mytable WHERE name LIKE '%John';

6. 范围查询后的列不使用索引

如果在范围查询之后使用非索引列,则索引将无法使用。 sql CREATE TABLE mytable (id INT, name VARCHAR(255), age INT); CREATE INDEX idx_age ON mytable (age); 以下查询将使用索引: sql SELECT FROM mytable WHERE age BETWEEN 20 AND 30; 以下查询将不会使用索引,因为它在范围查询之后使用了非索引列 name: sql SELECT FROM mytable WHERE age BETWEEN 20 AND 30 AND name = 'John';

7. WHERE 子句中使用不等式(<> 或 !=)

如果在 WHERE 子句中使用不等式 (<> 或 !=),则索引将无法使用。 sql CREATE TABLE mytable (id INT, name VARCHAR(255)); CREATE INDEX idx_name ON mytable (name); 以下查询将使用索引: sql SELECT FROM mytable WHERE name = 'John'; 以下查询将不会使用索引,因为它使用不等式 !=: sql SELECT FROM mytable WHERE name != 'John';

8. 使用 NULL 值进行查询

如果查询中使用 NULL 值,则索引将无法使用。 sql CREATE TABLE mytable (id INT, name VARCHAR(255)); CREATE INDEX idx_name ON mytable (name); 以下查询将使用索引: sql SELECT FROM mytable WHERE name IS NULL; 以下查询将不会使用索引,因为它使用不等式 !=: sql SELECT FROM mytable WHERE name = NULL;

9. 索引列参与计算或拼接

如果索引列参与计算或拼接,则索引将无法使用。 sql CREATE TABLE mytable (id INT, name VARCHAR(255), address VARCHAR(255)); CREATE INDEX idx_name ON mytable (name); 以下查询将使用索引: sql SELECT FROM mytable WHERE name = 'John'; 以下查询将不会使用索引,因为它包含计算: sql SELECT FROM mytable WHERE name = 'John' AND address = '123 Main Street';

10. 数据分布不均匀导致的优化器选择不使用索引

如果数据分布不均匀,优化器可能会选择不使用索引。 例如,如果一个索引列的值非常稀疏,则优化器可能会认为使用索引的成本高于全表扫描的成本。

11. 索引选择性不高

如果索引的选择性不高,则优化器可能会选择不使用索引。 索引选择性是指索引中唯一值的比率。如果索引的选择性较低,则优化器可能会认为使用索引的成本高于全表扫描的成本。

结论

了解索引失效场景对于优化 MySQL 查询性能至关重要。通过避免这些场景,你可以确保你的查询充分利用索引,从而提高性能。

索引失效的情况和解释

索引并不是时时都会生效的,比如以下几种情况,将导致索引失效:1.如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因) 注意:要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引 2.对于多列索引,不是使用的第一部分,则不会使用索引 查询是以%开头4.如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引 5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引 此外,查看索引的使用情况 show status like ‘Handler_read%; 大家可以注意: handler_read_key:这个值越高越好,越高表示使用索引查询到的次数 handler_read_rnd_next:这个值越高,说明查询低效。 ------------------ 1、WHERE字句的查询条件里有不等于号(WHERE column!=...),MYSQL将无法使用索引 2、类似地,如果WHERE字句的查询条件里使用了函数(如:WHERE DAY(column)=...),MYSQL将无法使用索引 3、在JOIN操作中(需要从多个数据表提取数据时),MYSQL只有在主键和外键的数据类型相同时才能使用索引,否则即使建立了 索引也不会使用 4、如果WHERE子句的查询条件里使用了比较操作符LIKE和REGEXP,MYSQL只有在搜索模板的第一个字符不是通配符的情况下才能 使用索引。 比如说,如果查询条件是LIKE abc%,MYSQL将使用索引;如果条件是LIKE %abc,MYSQL将不使用索引。 5、在ORDER BY操作中,MYSQL只有在排序条件不是一个查询条件表达式的情况下才使用索引。 尽管如此,在涉及多个数据表的查 询里,即使有索引可用,那些索引在加快ORDER BY操作方面也没什么作用。 6、如果某个数据列里包含着许多重复的值,就算为它建立了索引也不会有很好的效果。 比如说,如果某个数据列里包含了净是 些诸如“0/1”或“Y/N”等值,就没有必要为它创建一个索引。

索引失效的几种情况

索引失效的几种情况分别是:隐式转换、类型不一致。

1、隐式转换

隐式转换会导致索引失效,特别是在查询时将字段作为number类型以where条件传给Oracle时。这种错误的行为在开发中是常见的,也是经常会犯的错误。因为表的字段定义都是varchar2类型,而查询时将该字段设置为number类型会导致索引不可访问。

2、类型不一致

类型不一致会导致索引失效。在设计表字段时,必须保持一致性,比如user表的id是int自增。如果使用数字类型进行查询,中间存在隐式类型转换,就会导致索引失效。

此外,如果查询条件中使用了一个不匹配索引的数据类型,MySQL将无法使用该索引来优化查询,从而导致索引失效。

索引的优缺点

一、索引的优点

1、通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。

2、可以大大加快数据的检索速度,这也是创建索引的最主要的原因。

3、可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。

4、在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。

5、通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能

二、索引的缺点

1、创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。

2、索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大。

3、当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。

免责声明:本文转载或采集自网络,版权归原作者所有。本网站刊发此文旨在传递更多信息,并不代表本网赞同其观点和对其真实性负责。如涉及版权、内容等问题,请联系本网,我们将在第一时间删除。同时,本网站不对所刊发内容的准确性、真实性、完整性、及时性、原创性等进行保证,请读者仅作参考,并请自行核实相关内容。对于因使用或依赖本文内容所产生的任何直接或间接损失,本网站不承担任何责任。

标签: mysql索引