个人中心
文章发布
退出
作者热门文章
- ubuntu12.04环境下使用kvm ioctl接口实现最简单的虚拟机
- Ubuntu 通过无线网络安装Ubuntu Server启动系统后连接无线网络的方法
- 在Ubuntu上搭建网桥的方法
- ubuntu 虚拟机上网方式及相关配置详解
24
4
CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.
这篇CFSDN的博客文章浅谈mysql哪些情况会导致索引失效由作者收集整理，如果你对这篇文章有兴趣，记得点赞哟.
下面有一些培训教学机构的口诀和我个人的一些总结： 为了讲解以下索引内容，我们先建立一个临时的表 test02 。
|
1
2
3
4
5
6
7
8
|
create
table
`sys_user` (
`id`
varchar
(64)
not
null
comment
'主键'
,
`
name
`
varchar
(64)
default
null
comment
'名字'
,
`age`
int
(64)
default
null
comment
'年龄'
,
`pos`
varchar
(64)
default
null
comment
'职位'
,
primary
key
(`id`),
key
`idx_sys_user_nameagepos` (`
name
`,`age`,`pos`)
) engine=innodb
default
charset=utf8mb4 comment=
'用户表'
;
|
这个表有四个字段 主键 、名字、年龄、职位 。
下面我们来讲解第一个口诀： 1.全值匹配我最爱 2.最佳左前缀法则（重要） 全值匹配意思就是联立的复合索引的顺序和个数要和检索的条件顺序和个数相同。 最佳左前缀法则是指，如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列 下面我们给这个表建立一个复合索引 。
|
1
|
alter
table
sys_user
add
index
idx_sys_user_nameagepos(
name
,age,pos);
|
以下是我们的检索语句:
|
1
|
select
*
from
sys_user
where
name
=
'小明'
and
age = 22
and
pos =
'java'
;
|
我们通过在检索语句前面加关键字 exlain，可以知道是否使用的索引 。
|
1
2
3
|
（1）explain
select
*
from
sys_user
where
name
=
'小明'
and
age = 22
and
pos =
'java'
;
（2）explain
select
*
from
sys_user
where
name
=
'小明'
and
age = 22 ;
（3）explain
select
*
from
sys_user
where
name
=
'小明'
and
pos =
'java'
;
|
通过展示的结果我们可以知道，第一个复合索引的三个字段我们都用了，第二个复合索引我们只用到两个字段，第三个复合索引我们只用到一个字段。三个语句我们都用到索引，显然第一种是最优的.
我们再看看哪种情况会失效:
|
1
2
3
|
（4）explain
select
*
from
sys_user
where
age = 22;
（5）explain
select
*
from
sys_user
where
pos =
'java'
;
（6）explain
select
*
from
sys_user
where
age = 22
and
pos =
'java'
;
|
以上三种情况都变成了全表扫描，原因是违反了最左左前缀原则，因为复合索引最左边的是name，当检索条件name没在前面索引将失效，第一种情况满足了全值匹配，第二种满足了两个字段name和age，第三种因为只满足了name，所以索引只用到name.
3.不在索引列上做任何操作（计算、函数（自动或手动）类型转换），会使索引失效转为全表扫描 。
|
1
|
（7）explain
select
*
from
sys_user
where
left
(
name
,1)=
'小明'
;
|
第七种情况失效是因为索引列做了计算或者函数的操作，导致了全表扫描.
4.存储引擎不能使用索引中范围条件右边的列 可能大家关看上面的文字不知道是什么意思，下面我们执行一下查询语句就清楚了 。
|
1
|
（8）explain
select
*
from
sys_user
where
name
=
'小明'
and
age < 22
and
pos =
'java'
;
|
从上图我们可以知道type变成了范围级别，也就是说age<22之后的pos字段的索引失效了.
5.尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致），减少select * 的使用 这个就是字面意思，查询具体的字段比查询*效率更高，下面我们坐一下对比 。
|
1
2
|
（9）explain
select
*
from
sys_user
where
name
=
'小明'
and
age =22
and
pos =
'java'
;
（10）explain
select
name
,age,pos
from
sys_user
where
name
=
'小明'
and
age =22
and
pos =
'java'
;
|
6.mysql在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描 (!= 或者<>)通常会匹配到大量数据，当使用索引的花销大于全表扫描时，mysql则会放弃使用索引而选择全表扫描 。
|
1
|
（11）explain
select
*
from
sys_user
where
name
!=
'小明'
|
结果显示索引失效导致了全表扫描 。
7.is null，is not null 也无法使用索引 is null，is not null 通常会匹配到大量数据，当使用索引的花销大于全表扫描时，mysql则会放弃使用索引而选择全表扫描 。
|
1
|
（12）explain
select
*
from
sys_user
where
name
is
not
null
|
8.like以通配符开头（'%abc…'）mysql索引会失效变成全表扫描的操作，（%写右边则可以避免索引失效，如果业务实在需要'%abc…%'则可以用覆盖索引避免索引失效） 。
|
1
2
3
|
（13）explain
select
*
from
sys_user
where
name
like
'%明%'
（14）explain
select
*
from
sys_user
where
name
like
'明%'
（15）explain
select
name
,age,pos
from
sys_user
where
name
like
'%明%'
|
从上面的结果，第一种索引失效，第二种只写右边的%则可以避免索引失效，第三种如果业务实在需要‘%abc…%'这种sql，则可以用覆盖索引解决索引失效的问题 。
9.字符串不加单引号索引会失效 。
|
1
|
（16）explain
select
*
from
sys_user
where
name
=222;
|
因为检索字符串是必须加单引号，上面用用了222是int类型，mysql在检索的时候会判断name是varchar的类型会将222转换为'222'进行检索，索引列发生了类型转换，故索引失效.
10.少用or,用它连接时会索引失效 。
|
1
|
（16）explain
select
*
from
sys_user
where
name
=
'小明'
or
age = 22;
|
到此这篇关于浅谈mysql哪些情况会导致索引失效的文章就介绍到这了,更多相关mysql 索引失效内容请搜索我以前的文章或继续浏览下面的相关文章希望大家以后多多支持我！ 。
原文链接：https://blog.csdn.net/qq_40976527/article/details/105017541 。
最后此篇关于浅谈mysql哪些情况会导致索引失效的文章就讲到这里了,如果你想了解更多关于浅谈mysql哪些情况会导致索引失效的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！ 。
24
4
0
我正在创建一个 sql server 存储过程，它将输入作为逗号分隔的 productid 或选项“全部”。当用户传入逗号分隔的产品 ID 时，查询应输出所有产品 ID 的数据。我使用“IN”语句执行
我有一个自动生成的 Web 服务客户端。我有很多复杂的类，我必须对其进行模式匹配。现在我的结构如下所示: val response = client.getResponse response matc
关闭。这个问题是opinion-based .它目前不接受答案。 想改进这个问题？更新问题，以便 editing this post 可以用事实和引用来回答它. 7年前关闭。 Improve this
我需要正确的 tsql 语法来解决这个问题: Select * from table where var_A='10' select * from table where var_B='10' 何时使
我遇到了这个问题。每当我运行程序并在需要时键入字母 m 时，我的 if 语句都不会识别它。有人知道为什么吗？我已经这样做了一个小时，但没有结果。 #include #include #includ
我从数据库列名称“你有护照”创建了一个表，用户回答是或否我如何将 css 应用到这个动态工作的表。 table, th, td { border: 1px solid black;
我对 LocationListener 类的 onStatusChanged 有一些疑问。 它知道它可以呈现三种状态:AVAILABLE、TEMPORARILY_UNAVAILABLE 和 OUT_O
当引入新的异常类型时，我总是不确定如何正确地做到这一点。有共同约定吗？你怎么做呢？ 我对您组织它们的范围感兴趣(将它们保留在它们所使用的单元中？在组件级别有一个单元？包级别？应用程序？) 这也会影响命
我使用以下内容创建了日期维度: https://www.codeproject.com/Articles/647950/Create-and-Populate-Date-Dimension-for-D
您好，我正在使用 Android 完全 Kiosk 浏览器，该浏览器使用 chrome Webview。但是 javascript 中的某些方法或函数无法正常工作，例如 window.print()。
我有以下代码: public void OpenFile(string FileName) { if (FileName == null)
获取索引越界异常 for (int recordData = 0; recordData < recordDataList.size(); recordData++) {
我使用它在发生错误时在登录中显示一条消息: × Invalid user or password
这是我的场景，我有一个异常列表，其中包含来自不同层次结构的任意异常，下面的代码快照将解释我需要做什么 private List connectionExceptions; try { // tryin
我尝试动态更新 Jtextpane 中的左缩进。但我不能!这是我尝试过的! DefaultStyledDocument document = (DefaultStyledDocument) textp
我不知道为什么这个异常不起作用...... import java.util.*; public class a { public static void main(String[] args
我目前在 case 中使用多个 when 时遇到问题。当我删除第二个当时，它就起作用了。这是什么问题？ 报告的MYSQL错误为: #1064 - You have an error in your S
例如，我有一个表记录用户查看和下载文件的事件， file_id user activity 2 Tim view 1 Ron
这是一个非常愚蠢的问题，但我需要一点安慰/帮助。我有当前的“递归”情况: void add( int value ) { // do something ... // if ( conditi
我尝试使用以下代码在按钮数组上注册回调。但我似乎无法理解如何绑定(bind)回调中需要的字符串。任何建议将不胜感激! for (var i = 0; i < this.car_types.length
我是一名优秀的程序员,十分优秀！