您现在的位置是：网站首页> 编程资料编程资料
总结12个MySQL慢查询的原因分析_Mysql_
2023-05-26
416人已围观
简介 总结12个MySQL慢查询的原因分析_Mysql_
1. SQL 没加索引
很多时候，我们的慢查询，都是因为没有加索引。如果没有加索引的话，会导致全表扫描的。因此，应考虑在 where 的条件列，建立索引，尽量避免全表扫描。
反例：
select * from user_info where name =‘捡田螺的小男孩公众号' ;
正例：
//添加索引 alter table user_info add index idx_name (name);
2. SQL 索引不生效
有时候我们明明加了索引了，但是索引却不生效。
在哪些场景，索引会不生效呢？主要有以下十大经典场景：
2.1 隐式的类型转换，索引失效
我们创建一个用户 user 表：
CREATE TABLE user ( id int(11) NOT NULL AUTO_INCREMENT, userId varchar(32) NOT NULL, age varchar(16) NOT NULL, name varchar(255) NOT NULL, PRIMARY KEY (id), KEY idx_userid (userId) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
userId 字段为字串类型，是 B + 树的普通索引，如果查询条件传了一个数字过去，会导致索引失效。
如下：
如果给数字加上’', 也就是说，传的是一个字符串呢，当然是走索引，如下图：
为什么第一条语句未加单引号就不走索引了呢？这是因为不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL 会做隐式的类型转换，把它们转换为浮点数再做比较。隐式的类型转换，索引会失效。
2.2 查询条件包含 or，可能导致索引失效
我们还是用这个表结构：
CREATE TABLE user ( id int(11) NOT NULL AUTO_INCREMENT, userId varchar(32) NOT NULL, age varchar(16) NOT NULL, name varchar(255) NOT NULL, PRIMARY KEY (id), KEY idx_userid (userId) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
其中 userId 加了索引，但是 age 没有加索引的。我们使用了 or，以下 SQL 是不走索引的，如下：
对于 or+ 没有索引的 age 这种情况，假设它走了 userId 的索引，但是走到 age 查询条件时，它还得全表扫描，也就是需要三步过程：全表扫描 + 索引扫描 + 合并。如果它一开始就走全表扫描，直接一遍扫描就完事。Mysql 优化器出于效率与成本考虑，遇到 or 条件，让索引失效，看起来也合情合理嘛。
注意：如果 or 条件的列都加了索引，索引可能会走也可能不走，大家可以自己试一试哈。但是平时大家使用的时候，还是要注意一下这个 or，学会用 explain 分析。遇到不走索引的时候，考虑拆开两条 SQL。
2.3. like 通配符可能导致索引失效
并不是用了 like 通配符，索引一定会失效，而是 like 查询是以 % 开头，才会导致索引失效。
like 查询以 % 开头，索引失效
explain select * from user where userId like ‘%123';
把 % 放后面，发现索引还是正常走的，如下：
explain select * from user where userId like ‘123%';
既然 like 查询以 % 开头，会导致索引失效。我们如何优化呢？
使用覆盖索引把 % 放后面 2.4 查询条件不满足联合索引的最左匹配原则
MySQl 建立联合索引时，会遵循最左前缀匹配的原则，即最左优先。如果你建立一个（a,b,c）的联合索引，相当于建立了 (a)、(a,b)、(a,b,c) 三个索引。
假设有以下表结构：
CREATE TABLE user ( id int(11) NOT NULL AUTO_INCREMENT, user_id varchar(32) NOT NULL, age varchar(16) NOT NULL, name varchar(255) NOT NULL, PRIMARY KEY (id), KEY idx_userid_name (user_id,name) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
有一个联合索引 idx_userid_name，我们执行这个 SQL，查询条件是 name，索引是无效：
explain select * from user where name =‘捡田螺的小男孩';
因为查询条件列 name 不是联合索引 idx_userid_name 中的第一个列，索引不生效
在联合索引中，查询条件满足最左匹配原则时，索引才正常生效。
2.5 在索引列上使用 mysql 的内置函数
表结构：
CREATE TABLEuser(idint(11) NOT NULL AUTO_INCREMENT,userIdvarchar(32) NOT NULL,login_timedatetime NOT NULL, PRIMARY KEY (id), KEYidx_userId(userId) USING BTREE, KEYidx_login_time(login_Time) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
虽然 login_time 加了索引，但是因为使用了 mysql 的内置函数 Date_ADD()，索引直接 GG，如图：
一般这种情况怎么优化呢？可以把内置函数的逻辑转移到右边，如下：
explain select * from user where login_time = DATE_ADD(‘2022-05-22 00:00:00',INTERVAL -1 DAY);
2.6 对索引进行列运算（如，+、-、*、/）, 索引不生效
表结构：
CREATE TABLEuser(idint(11) NOT NULL AUTO_INCREMENT,userIdvarchar(32) NOT NULL,ageint(11) DEFAULT NULL, PRIMARY KEY (id), KEYidx_age(age) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
虽然 age 加了索引，但是因为它进行运算，索引直接迷路了
如图：
所以不可以对索引列进行运算，可以在代码处理好，再传参进去。
2.7 索引字段上使用（！= 或者 < >），索引可能失效
表结构：
CREATE TABLEuser(idint(11) NOT NULL AUTO_INCREMENT,userIdint(11) NOT NULL,ageint(11) DEFAULT NULL,namevarchar(255) NOT NULL, PRIMARY KEY (id), KEYidx_age(age) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
虽然 age 加了索引，但是使用了！= 或者 < >，not in 这些时，索引如同虚设。
如下：
其实这个也是跟 mySQL优化器有关，如果优化器觉得即使走了索引，还是需要扫描很多很多行的哈，它觉得不划算，不如直接不走索引。平时我们用！= 或者 < >，not in 的时候，留点心眼哈。
2.8 索引字段上使用 is null， is not null，索引可能失效
表结构:
CREATE TABLEuser(idint(11) NOT NULL AUTO_INCREMENT,cardvarchar(255) DEFAULT NULL,namevarchar(255) DEFAULT NULL, PRIMARY KEY (id), KEYidx_name(name) USING BTREE, KEYidx_card(card) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
单个 name 字段加上索引，并查询 name 为非空的语句，其实会走索引的，如下:
单个 card 字段加上索引，并查询 name 为非空的语句，其实会走索引的，如下:图片
但是它两用 or 连接起来，索引就失效了，如下：
提示：
本文由神整理自网络，如有侵权请联系本站删除！
本站声明：
1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持；
2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！
