mysql 前缀索引失效_MySQL索引失效的10大原因

最新推荐文章于 2024-03-16 17:55:29 发布
最新推荐文章于 2024-03-16 17:55:29 发布
阅读量386 收藏
点赞数
文章标签： mysql 前缀索引失效
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_39530437/article/details/113240945
版权

基本表信息：

mysql> desc staffs;

+----------+-------------+------+-----+-------------------+----------------+

| Field | Type | Null | Key | Default | Extra |

+----------+-------------+------+-----+-------------------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| name | varchar(24) | NO | | | |

| age | int(11) | NO | | 0 | |

| pos | varchar(20) | NO | | | |

| add_time | timestamp | NO | | CURRENT_TIMESTAMP | |

+----------+-------------+------+-----+-------------------+----------------+

5 rows in set (0.00 sec)

mysql> select * from staffs;

+----+------+-----+---------+---------------------+

| id | name | age | pos | add_time |

+----+------+-----+---------+---------------------+

| 1 | z3 | 22 | manager | 2019-03-10 19:52:42 |

| 2 | July | 22 | dev | 2019-03-10 19:52:42 |

| 3 | 2000 | 22 | dev | 2019-03-10 19:52:42 |

+----+------+-----+---------+---------------------+

3 rows in set (0.00 sec)

mysql> show index from staffs;

+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| staffs | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | |

| staffs | 1 | idx_staffs_nameAgePos | 1 | name | A | 3 | NULL | NULL | | BTREE | | |

| staffs | 1 | idx_staffs_nameAgePos | 2 | age | A | 3 | NULL | NULL | | BTREE | | |

| staffs | 1 | idx_staffs_nameAgePos | 3 | pos | A | 3 | NULL | NULL | | BTREE | | |

+--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

4 rows in set (0.00 sec)

1.全值匹配我最爱

2.最佳左前缀法则

如果索引了多列，要遵守此规则，指的是从索引最左前列开始并且不跳过索引中的列

带头大哥不能死，中间兄弟不能断

mysql> mysql> explain select * from staffs where age=23 and pos='dev';

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

| 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.01 sec)

3.不在索引列上做任何操作

计算、函数、(自动或手动)类型转换，会导致索引失效而转向全表扫描

mysql> explain select * from staffs where name= 'July';

+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+

| 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74 | const | 1 | 100.00 | NULL |

+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+

1 row in set, 1 warning (0.00 sec)

mysql> explain select * from staffs where left(name,4)= 'July';

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

| 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where |

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

4.存储引擎不能使用索引中范围条件右边的列

范围之后全失效

mysql> explain select * from staffs where name='July' and age =22;

+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+

| 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 78 | const,const | 1 | 100.00 | NULL |

+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+

1 row in set, 1 warning (0.00 sec)

mysql> explain select * from staffs where name='July' and age =22 and pos='manager';

+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+

| 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 140 | const,const,const | 1 | 100.00 | NULL |

+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+

1 row in set, 1 warning (0.00 sec)

5.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一直))，减少select *

mysql> explain select * from staffs where name='July' and age=25 and pos='manager';

+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+

| 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 140 | const,const,const | 1 | 100.00 | NULL |

+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+

1 row in set, 1 warning (0.00 sec)

mysql> explain select name, age, pos from staffs where name='July' and age=25 and pos='manager';

+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+

| 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 140 | const,const,const | 1 | 100.00 | Using index |

+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

6.mysql在使用不等于(！=或<>)时候，无法使用索引导致全表扫描

mysql> explain select * from staffs where name='July';

+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+

| 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74 | const | 1 | 100.00 | NULL |

+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+

1 row in set, 1 warning (0.00 sec)

mysql> explain select * from staffs where name!='July';

+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+

| 1 | SIMPLE | staffs | NULL | ALL | idx_staffs_nameAgePos | NULL | NULL | NULL | 3 | 66.67 | Using where |

+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

mysql> explain select * from staffs where name <> 'July';

+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+

| 1 | SIMPLE | staffs | NULL | ALL | idx_staffs_nameAgePos | NULL | NULL | NULL | 3 | 66.67 | Using where |

+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

7.is null，is not null也无法使用索引

mysql> explain select * from staffs where name is null;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+

| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+

1 row in set, 1 warning (0.00 sec)

mysql> explain select * from staffs where name is not null;

+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+

| 1 | SIMPLE | staffs | NULL | ALL | idx_staffs_nameAgePos | NULL | NULL | NULL | 3 | 66.67 | Using where |

+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

8.like以通配符开头，mysql索引失效会编程全表扫描的操作

百分like加右边

mysql> explain select * from staffs where name like '%July%';

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

| 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

mysql> explain select * from staffs where name like '%July';

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

| 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

mysql> explain select * from staffs where name like 'July%';

+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+

| 1 | SIMPLE | staffs | NULL | range | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74 | NULL | 1 | 100.00 | Using index condition |

+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+

1 row in set, 1 warning (0.00 sec)

9.字符串不加单引号，索引失效

避免隐式类型转换

10.少用or，用它连接时索引会失效

weixin_39530437
关注
mysql索引详解
学Java，找哪吒
07-07 10万+
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离
MySQL前缀索引导致的慢查询分析总结
12-15
前端时间跟一个DB相关的项目，alanc反馈有一个查询，使用索引比不使用索引慢很多倍，有点毁三观。所以跟进了一下，用explain，看了看2个查询不同的结果。 不用索引的查询的时候结果如下，实际查询中速度比较块。 代码如下: mysql> explain select * from rosterusers limit 10000,3 ; +—-+————-+————-+——+—————+——+———+——+———+——-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Ext
mysql最左前缀索引（联合索引下索引失效问题）
郝大侠的博客
09-08 817
当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较...
mysql 前缀索引失效_因为前缀索引问题导致mysql无法使用覆盖索引 | 学步园
weixin_36337502的博客
01-19 332
今天帮开发改写一个复杂的sql，具体sql就不列出剥离出核心sql类似如：select sum(xx) from a group by n1,n2,n3并且n1字段定义为varchar(255) 实际存储的数据长度一般都是不超过10左右个字符长度的数字字符串 如01500060，另外有一个主键id为自增字段当时 想建一个复合索引来测试一下看看索引如：create index ind_winrecs...
myisam为什么比innodb查询快_我去，为什么最左前缀原则失效了？
weixin_39604139的博客
11-24 117
来源于公众号烟雨星空 ，作者烟雨星空问题最近，在 mysql 测试最左前缀原则，发现了匪夷所思的事情。根据最左前缀原则，本来应该索引失效，走全表扫描的，但是，却发现可以正常走索引。表结构如下( Mysql 版本 5.7.22)：CREATETABLE`user`(`id`int(11)NOTNULLAUTO_INCREMENT,`name`varchar(32)COLLA...
索引失效，怎么回事！
weixin_43166227的博客
07-02 689
索引失效，可以从一下几种原因中分析： 在where条件的等号左侧中使用函数 where date(create_at) = '2019-01-01' 带有列的函数计算值，导致索引无法使用。 表关联字符集格式以及排序方式不一致 关注CHARSET和COLLATION SQL写法错误导致的索引失效比较常见的例子是，我们存储手机号的字段格式为varchar，但是SQL却写的where phone=123; 统计信息不准确 在查询中使用 !=、not in、not exists SELECT * F
mysql group 索引失效_介绍mysql索引失效的情况
weixin_42410477的博客
02-17 1290
mysql视频教程栏目索引失效的情况。索引对于MySQL而言，是非常重要的篇章。索引知识点也巨多，要想掌握透彻，需要逐个知识点一一击破，今天来先来聊聊哪些情况下会导致索引失效。图片总结版相关免费学习推荐：mysql视频教程全值匹配(索引最佳)explain select * from user where name = 'zhangsan' and age = 20 and pos = 'cxy'...
导致MySQL索引失效的一些常见写法总结
09-14
以下是一些可能导致MySQL索引失效的常见写法： 1. **全表扫描（Full Table Scan）**：当查询涉及所有或大部分数据时，MySQL会选择不使用索引而进行全表扫描。例如，使用`SELECT * FROM table`会忽略所有索引。 2. ...
MySQL数据库索引失效的10种场景.zip
02-17
本篇将详细探讨10种可能导致MySQL数据库索引失效的常见场景，帮助你理解和避免这些问题。 **1. LIKE操作符的不当使用** 在查询语句中，如果使用LIKE操作符并且模式匹配出现在字符串的开头，索引可能无法被利用。...
mysql 数据量大 建索引失败_面试题： MySQL 索引失效的10大原因
weixin_33261455的博客
01-19 1983
1、建表：CREATE TABLE staffs (id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR (24) NOT NULL DEFAULT '' COMMENT '姓名',age INT NOT NULL DEFAULT 0 COMMENT '年龄',pos VARCHAR (20) NOT NULL DEFAULT '' COMMENT '职位'...
table-prefix:用于更改表前缀的 WP CLI 命令
06-06
表前缀 用于更改表前缀的 WP CLI 命令。 除了更改表前缀之外，wp-config 文件也将更新。 wp-config 文件的背面保存到 wp-config.bak.php 以防出现问题。 ##命令 更改将数据库中表的前缀更改为 ，除非另有指定，否则将使用默认前缀（请参阅：显示） show 显示数据库对象认为它正在使用的当前前缀（$wpdb->prefix） ##示例用法 wp 表前缀更改 notwp_ 一次性运行（不作为 wp-cli 命令添加） wp --require=../commands/table-prefix/table-prefix.php table-prefix change new_ ##未来增强想法 增强的多站点支持，目前只更改主表前缀，不允许您更改站点前缀（例如 wp_2_site 可以更改为 x_2_site 但不能更改为 wp_x_site）
详解最左前缀法则+索引失效
最新发布
qq_62835094的博客
03-16 1702
最左前缀是在使用innodb存储引擎索引时，需要遵守的法则。在一个联合索引如，执行查询SQL时，如果查询条件包含索引的最左前缀，那么可以使用联合索引加速查询。对于上述例子，最左前缀包括idx(a)idx(a, b)
【数据库MySQL】-- 索引（三）（最左前缀法则、索引失效情况、SQL提示）
张修宇的博客
05-03 1424
一、最左前缀法则 如果索引了多列（联合索引），要遵循最左前缀法则，最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。 如果跳跃某一列，索引将部分失效（后面的字段索引失效）。 例： 第一个语句可以索引。 第二个语句不能索引，因为profession最左侧的索引被跳过，后面的不能索引。 第三个语句部分索引，只有profession可以索引，status不能。 explain select * from tb_user where profession='软件工程' and age
索引失效的几种情况和原因剖析
爱码士的博客
10-17 1万+
在网上看到很多关于索引失效的情况，但是看了感觉都讲的不过明确，或者只告诉你这么查询会导致索引失效但是却没有告诉你索引失效的原因，这里做一些详细的说明。 一般我们常见的出现索引失效的有这么几个原因 1，使用or关键字（但是并不是所有带or的查询都会失效，如果有两个字段，两个字段都有索引就不会失效，会走两个索引） 2，使用like关键字（但是并不是所有like查询都会失效，只有在查询时字段最左侧加%和左右侧都加%才会导致索引失效） 3，组合索引（如果查询的字段在组合索引中不是最左侧的字段，那么该组合索引是不会生
【索引失效的原因】
qq_33453034的博客
01-10 578
索引失效的原因：模型数空运最快 1.模糊查询 like中 若以%开头，索引就会失效 2.数据类型错误，索引就会失效 3.字段用内部函数，失效，应建立基于函数的索引 4.空值，索引不存储空值，若不限制索引列not null，索引也会失效 5.对索引列进行加减乘除运算，索引失效 6.复合查询 不符合最左原则，索引失效 7.预计使用全表扫描比索引扫描更快，则不会使用索引 cookie和session的区别： 1.cookie是存储在浏览器端，大小不超过4kn。session存储在服务器端，主要用来存储用户会话的数
MySql-索引失效的情况
qq_61084129的博客
02-28 608
举例来说，如果有一个列 col1 上建立了索引，并且在查询中使用了 col1 * 2 = 10 这样的条件，这种情况下数据库可能无法直接利用 col1 的索引来加速查询，因为需要先对 col1 进行运算，然后才能进行匹配。这就导致了索引失效，从而可能降低查询性能。＝时，回表数量可能会很大，也可能产生索引失效。当通配符出现在搜索关键字的开头时，索引失效的主要原因是因为索引是按照值的顺序进行存储和排序的，而通配符 % 出现在开头时，表示需要匹配任意长度的字符串，这样就无法利用索引的有序性进行高效的检索。
MySQL索引失效原因及解决方案
weixin_45626288的博客
07-27 3166
在使用MySQL数据库时，索引是一种重要的性能优化工具。然而，有时候我们可能会遇到索引失效的情况。本文将介绍几种常见的MySQL索引失效原因以及相应的解决方案，并提供SQL语句的错误示例和正确示例。
MySQL高级篇——索引失效的11种情况
热门推荐
vincewm的博客
05-21 2万+
索引优化思路、要尽量满足全值匹配、最佳左前缀法则、主键插入顺序尽量自增、计算、函数导致索引失效、类型转换（手动或自动）导致索引失效、范围条件右边的列索引失效、不等于符号导致索引失效、is not null、not like无法使用索引、左模糊查询导致索引失效、“OR”前后存在非索引列，导致索引失效、不同字符集导致索引失败，建议utf8mb4
Mysql-最左前缀法则以及索引失效情况
m0_56642224的博客
04-29 1976
如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。以 tb_user 表为例，我们先来查看一下之前 tb_user 表所创建的索引~在上述的表tb_user中，我们设置了一个联合索引，这个联合索引涉及到三个字段，顺序分别为：profession，age，status对于最左前缀法则指的是，查询时，最左边的列，也就是profession必须存在，否则索引全部失效。
MySQL索引失效：组合索引详解及其影响
本文档深入探讨了MySQL中索引失效的问题，特别是针对组合索引的分析。索引在关系型数据库中扮演着至关重要的角色，它是一种物理存储结构，用于对数据库表中的一列或多列数据进行排序，提供快速的数据检索能力，类似...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值