• MySQL索引失效的问题有哪些?
  • 发布于 2个月前
  • 267 热度
    0 评论
  • 离人愁
  • 0 粉丝 25 篇博客
  •   
索引失效的情况:
1.使用 like ‘%abc’或者like ‘%abc%’
2.查询列参与了函数计算(并没有使用函数索引)
3.数据不够离散,扫描的行数和加载索引的成本超过了全表扫描
4.联合索引没有使用最左匹配,或者在范围运算(>,<,<>)等运算的后面

5.where中索引列有运算


除了上面的几个明显的问题外,还有索引的选择问题。MySQL 在执行一段 sql 的时候,会先决定使用哪一个索引,如果 选了一个性能比较差的索引,即使走了索引,也会带来性能问题。

对上面的第 4 条做一个例子说明:
1.定义 abcd 字段一个联合索引
2.如果使用 a>0 and b=1 .. 则 a 本身走索引,但 a 后面的字段都不走索引

3.a=1 and b=1 and c>1 and d=1 这个例子 只有 d 不走索引,如果 索引顺序更改为 abdc 则都会走索引。


准备工作
create database ITTest;
use ITTest;
CREATE TABLE tbl_user (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
    userName varchar(100) not null comment '姓名',
    userAge int default 0 comment '年龄',
    userStatus int default 0 comment '用户状态 0 有效,1 无效',
    userSex int default 0 comment '性别 0 男 1 女',
    birthDate DATE   COMMENT '生日',
    createTime DATETIME DEFAULT NOW() COMMENT '创建时间',
  dataChangeLastTime DATETIME DEFAULT NOW() ON UPDATE NOW() COMMENT '最后更新时间'
);

alter table tbl_user add index idx_userName(userName);
alter table tbl_user add index idx_userSex(userSex);
alter table tbl_user add index idx_userStatus(userStatus);
alter table tbl_user add INDEX  idx_union_userStatus_userSex_birthDate(userStatus,userSex,birthDate );

insert into tbl_user(userName,userAge,userStatus,userSex,birthDate)
values
('user001',rand()*100,rand(),rand(),curdate()),
('user002',rand()*100,rand(),rand(),curdate()),
('user003',rand()*100,rand(),rand(),curdate()),
('user004',rand()*100,rand(),rand(),curdate()),
('user005',rand()*100,rand(),rand(),curdate()),
('user006',rand()*100,rand(),rand(),curdate()),
('user007',rand()*100,rand(),rand(),curdate()),
('user008',rand()*100,rand(),rand(),curdate()),
('user009',rand()*100,rand(),rand(),curdate())
Explain 查看索引使用情况

各个字段的含义
mysql> EXPLAIN SELECT `birday` FROM `user` WHERE `birthday` < "1990/2/2"; 
-- 结果: 
id: 1 
select_type: SIMPLE -- 查询类型(简单查询、联合查询、子查询) 
table: user -- 显示这一行的数据是关于哪张表的 。
type: range -- 区间索引(在小于1990/2/2区间的数据),这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL,const代表一次就命中,ALL代表扫描了全表才确定结果。一般来说,得保证查询至少达到range级别,最好能达到ref。 
possible_keys: birthday  -- 指出MySQL能使用哪个索引在该表中找到行。如果是空的,没有相关的索引。这时要提高性能,可通过检验WHERE子句,看是否引用某些字段,或者检查字段不是适合索引。  

key: birthday -- 实际使用到的索引。如果为NULL,则没有使用索引。如果为primary的话,表示使用了主键。 
key_len: 5 -- 最长的索引宽度。如果键是NULL,长度就是NULL。在不损失精确性的情况下,长度越短越好。
ref: const -- 显示哪个字段或常数与key一起被使用。  
rows: 20 -- 这个数表示mysql要遍历多少数据才能找到,在innodb上是不准确的。 

Extra: Using where; Using index -- 执行状态说明,这里可以看到的坏的例子是Using temporary和Using


索引选择的决定的因素
1.如果在一个 sql 中,有使用了两个索引,是否会同时使用?如果不是,那如何决定使用哪一个?

2.如果有一个字段有单独的索引,又符合联合索引的最左匹配原则,索引会怎么选?


MySQL 索引的选取是基于成本计算的,影响查询成本的因素有 扫描行数、是否需要临时表以及是否需要排序**等。

成本的决定因素很多,就那扫描行数来说,影响扫描行数最大的因素是数据的离散度,但是数据又是动态变化的,所以在使用的离散度比较低的索引的时候需要注意后续的索引变化。例如:
select * from  user where userSex=0 and userStatus=0 and birthDate>'1897-01-01'
select * from  user where userSex in(0,1) and userStatus=0 and birthDate>'1897-01-01'
分析下上面的两个sql 的执行速度那个快:
1.上面的sql 不同点是 userSex 的的过滤条件
2.根据上面建立的两个索引规则,有可能走到联合索引和 userStatus 单独的索引
3.在执行筛选的时候,如果这个时候,MySQL 发现 userSex 走索引的成本小于全表扫描,则可能会走索引下推
4.使用 in 的时候,userSex 的字段直接默认走全表扫描,不会计算成本
用户评论