MySQL高级（下）之索引失效和一些优化

最新推荐文章于 2024-09-10 22:36:20 发布

可乐不可乐。

最新推荐文章于 2024-09-10 22:36:20 发布

阅读量599

点赞数

分类专栏：笔记文章标签： mysql

本文链接：https://blog.youkuaiyun.com/l2470334493/article/details/108640357

版权

本文探讨了MySQL中索引失效的常见原因，如全值匹配、最左前缀法则、索引列上的计算和范围查询等。并介绍了如何通过优化关联查询、子查询、排序分组等方式提高查询效率，包括避免使用OR、使用覆盖索引和注意ORDER BY的顺序。还讲解了MySQL的排序算法及其优化策略，如调整sort_buffer_size和max_length_for_sort_data参数。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、表单使用索引的常见索引失效

1、1 全值匹配

有以下sql语句

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4 
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4 AND emp.name = 'abcd'

建立索引
CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);
在这里插入图片描述
结论：全值匹配我最爱指的是，查询的字段按照顺序在索引中都可以匹配到！

SQL中查询字段的顺序，跟使用索引中字段的顺序，没有关系，优化器会在不影响SQL执行结果的前提下，给你自动的优化

1、2 最佳做左前缀法则

在这里插入图片描述
查询字段与索引字段的顺序的不同，会导致索引无法充分使用，甚至索引失效！
原因：使用复合索引，需要遵循最佳做前缀法则，即如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不跳过缩影中的列。
结论：过滤条件要使用索引必须要按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。

1、3 不要在索引列上做任何计算

不再索引列上做任何操作（计算、函数、(自动 or 手动) 类型转换）。会导致索引失效而转向全表扫描。

1、3、1 在查询列上使用了函数

EXPLAIN SELECT SQL_NO_CAHE * FROM emp WHERE age= 30；

EXPLAIN SELECT SQL_NO_CAHE * FROM emp WHERE LEFT(age,3)= 30；
在这里插入图片描述
结论：等号左边无计算！

1、3、2 在查询列上做了转换

create index idx_name on emp(name);
explain select sql_no_cache * from emp where name=‘3000’;
explain select sql_no_cache * from emp where name=3000;

字符串不加单引号，则会在name列上做一次转换！
在这里插入图片描述
结论：等号右边无转换!

1、4 索引列上不能有范围查询

explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid =5 AND emp.name='abcd;
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid<=5 AND emp.name=‘abcd’;

在这里插入图片描述

1、5 尽量使用覆盖索引

即查询列和索引列一致，不要写 select *

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND deptid=4 AND name='XamgXt
explain SELECT SQL_NO_CACHE age,deptId,name FROM emp WHERE emp.age=30 and deptId=4 and name=‘XamgXt’;

在这里插入图片描述

1、6 使用不等于（!=或者<>）的时候

mysql 在使用不等于(!= 或者<>)时候，有时无法使用索引会导致全表扫描
在这里插入图片描述

1、7 字段的is not null 和 is null

在这里插入图片描述
当字段允许为null 的条件下：

is not null 用不到索引，is null 可以用到索引。

1、8 的前模糊匹配

在这里插入图片描述
前缀不能出现模糊匹配！

1、9 减少使用or

在这里插入图片描述
使用union 或者union来替代

1、10 练习

假设index(a,b,c)

Where 语句	索引是否被使用
Where a =3	Y,使用到a
where a =3 and b = 5	Y，使用到a，b
where a = 3 and b=5 and c=4	Y，使用到a,b,c
where b =3 或者where b= 3 and c=4 或者where c=4	N
where b=3 and c=5	使用a，但是c不可以，b中间断了
where a is null and b is not null	is null 支持索引，但是is not null 不支持，所以a 可以使用索引，但是b不可以使用
where a <> 3	不嫩使用索引
where abs(a)=3	不能使用索引
where a =3 and b like ’kk%’; and c=4	Y,使用到a,b,c
where a=3 and b like ‘%kk’ and c=4	Y，只使用到a
where a=3 and b like‘%kk%’ and c =4	Y,只用到a
where a =3 and b like’k%kk%’ and c=4	Y,使用到a，b，c

1、11 口诀

全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
LIKE百分写最右，覆盖索引不写*；
不等空值还有OR，索引影响要注意；
VAR 引号不可丢，SQL优化有诀窍。

2、关联查询优化

2、1 建表语句

CREATE TABLE IF NOT EXISTS `class` ( 
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 
`card` INT(10) UNSIGNED NOT NULL,
 PRIMARY KEY (`id`) );
 
 CREATE TABLE IF NOT EXISTS `book` (
 bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 
 `card` INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`bookid`) );

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); 
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)))</