工作中,经常遇到这样的问题,我明明在MySQL表上面加了索引,为什么执行SQL查询的时候却没有用到索引?
同一条SQL有时候查询用到了索引,有时候却没用到索引,这是咋回事?
原因可能是索引失效了,失效的原因有以下几种,看你有没有踩过类似的坑?
1. 数据准备:
有这么一张用户表,在name字段上建个索引:
CREATE TABLE `user` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(255) DEFAULT NULL COMMENT '姓名',
`age` int DEFAULT NULL COMMENT '年龄',
PRIMARY KEY (`id`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB COMMENT='用户表';
2. Explain详解:
想要查看一条SQL是否用到索引?用到了哪种类型的索引?
可以使用
explain
关键字,查看SQL执行计划。例如:
explain select * from user where id=1;
可以看到type=const,表示使用了主键索引。
explain的所有type类型如下:
3. 失效原因
1. 数据类型隐式转换
name字段是varchar类型,如果我们使用数据类型查询,就会产生数据类型转换,虽然不会报错,但是无法用到索引。
explain select * from user where name='一灯';
explain select * from user where name=18;
2. 模糊查询 like 以%开头
explain select * from user where name like '张%';
explain select * from user where name like '%张';
3. or前后没有同时使用索引
虽然name字段上加了索引,但是age字段没有索引,使用or的时候会全表扫描。
# or前后没有同时使用索引,导致全表扫描
explain select * from user where name='一灯' or age=18;
4. 联合索引,没有使用第一列索引
如果我们在(name,age)上,建立联合索引,但是查询条件中只用到了age字段,也是无法用到索引的。
使用联合索引,必须遵循最左匹配原则,首先使用第一列字段,然后使用第二列字段。
CREATE TABLE `user` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(255) DEFAULT NULL COMMENT '姓名',
`age` int DEFAULT NULL COMMENT '年龄',
PRIMARY KEY (`id`),
KEY `idx_name_age` (`name`,`age`)
) ENGINE=InnoDB COMMENT='用户表';
5. 在索引字段进行计算操作
如果我们在索引列进行了计算操作,也是无法用到索引的。
# 在主键索引上进行计算操作,导致全表扫描
explain select * from user where id+1=2;
6. 在索引字段字段上使用函数
如果我们在索引列使用函数,也是无法用到索引的。
7. 优化器选错索引
同一条SQL有时候查询用到了索引,有时候却没用到索引,这是咋回事?
这可能是优化器选择的结果,会根据表中数据量选择是否使用索引。
当表中大部分name都是一灯,这时候用name='一灯'做查询,还会不会用到索引呢?
索引优化器会认为,用索引还不如全表扫描来得快,干脆不用索引了。
当然我们认为优化器优化的不对,也可以使用
force index
强制使用索引。
知识点总结:
目录一、
mysql
中如果
没有
索引
如何查找数据1、方式一:在一个页中查找2、方式二:在多个页中查找二、
mysql
中
索引
的概念1、前提条件2、目录项记录3、如何区分普通用户记录和目录项记录4、普通用户记录和目录项的区别有哪些?三、
mysql
中B+树1、如何快速定位目录项2、上图中B+树的解释
一、
mysql
中如果
没有
索引
如何查找数据
1、方式一:在一个页中查找
比如数据较少,都存储在一个页中。在查找记录时,是要根据搜索条件的不同分为两种情况的。
第一种情况:以主键列作为搜索条件
(1)、在页目录(Page
1.摘要:DBA经常会有新接手的业务数据库,之前大多事RD自己维护,
没有
专职DBA来维护
MySQL
,建
索引
随心所欲,导致好多表上有大
量
的
索引
,有的表
索引
大小比表数据都要大很多,作为一名DBA看到这种情况是不是会头大,很想把那些
没
用的index全部干掉,但也不能随便就干掉,怎么办,表示很需要耗时间。还有一种情况,DBA建了
索引
,怎么查看这些已经建完的
索引
就一定
使用
到了呢?有些业务可能过了一段时间就...
如果参数是1个会用到
索引
,
如果参数是2个及以上不会用到
索引
。
或者in里是连续的参数,比如where fee in (1,2)、where fee in (1,2,3),fee是int类型并且有单列
索引
,依然不会
使用
索引
.(包括int和varchar类型的字段)
2.like ‘%abc’ 或者 like‘%abc%’
3.条件中
带
有表达式,比如where num/2=100 或者 substring(a,1,3)='ab’
一、单表
查询
索引
列和
查询
索引
列+其他非
索引
列
查询
数据速度为什么有差?为什么
查询
非
索引
列会不
走
索引
?现象: 单个表,结构如下:CREATE TABLE `t_attach_new` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT,`fname` varchar(100) NOT NULL DEFAULT '',`ftype` varchar(50) ...
有时候在
MySQL
上执行
查询
时,即使WHERE条件中涉及的字段已经建立了
索引
,但
MySQL
仍然
没有
使用
到该
索引
,下面针对各种可能的原因进行总结。
下文中说到的原因均已在
MySQL
5.7.21中进行验证测试。
可能的原因
1)where中的字段涉及隐式数据类型转换
users表的a字段建有
索引
,数据类型为varchar
SELECT * FROM users WHERE a = 1
上述语句不会
使用
到a字段的
索引
,因为a字段的数据类型是varchar,而SQL中的WHERE条件是a=1,涉及到
隐式转换导致
索引
失效.这一点应当引起重视.也是开发中经常会犯的错误. 由于表的字段tu_mdn定义为varchar2(20),但在
查询
时把该字段作为number类型以where条件传给
mysql
,这样会导致
索引
失效. 错误的例子:select * from test where tu_mdn=13333333333; 正确的例子:select * from test where tu_mdn='1...
今天查看宝塔面板后台的
MySQL
设置状态时,发现“
没有
使用
索引
的
量
”非常多,达到10W+,刚开始不知道这是什么意思。去网上查找也
没有
找到关于宝塔面板的这个设置到底是什么意思?有什么影响。宝塔面板
MySQL
没有
使用
索引
的
量
过高有什么影响?如上图,
没有
使用
索引
的
量
达到10W+,经过查找资料总结了一下
MySQL
没有
使用
索引
查询
,相当于全表
查询
,效率底下,数据库会疯狂的消耗内存,并且会影响前端程序的执行。...
select * from tableName where a=1 and (b=1 or c=1)
其中建立了ab和ac的联合
索引
,但是看db监控慢
查询
的扫描行数和a=1的行数相同。通过explain看出执行计划是
走
ab的联合
索引
,但是为啥扫描行数却是a=1的行数呢?猜测是因为要查a=1 and b=1的数据时候,可以
走
ab
索引
,但是查出 a=1和c=1的数据,就需要扫描a=1的所有数据。可以通过联合
索引
的数据结构看出,当a相同时候,会按照b来排序。而且
mysql
一般不会选择
走
多个
索引
,除非要
索引
合并,