9_MySQL索引优化与查询优化

Justinyh

已于 2023-01-17 17:57:42 修改

阅读量335

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 java

于 2022-11-17 13:20:39 首次发布

本文链接：https://blog.csdn.net/qq_38926742/article/details/127894224

版权

MySQL 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

系列文章目录

一、索引失效的情况

导入staffs员工表：
在这里插入图片描述

建立联合索引：

SHOW INDEX FROM staffs;

在这里插入图片描述

1.1、最佳左前缀法则

我建立了复合索引分别是name，age，pos，我只查一个，索引会不会失效？

EXPLAIN select * from staffs where name='July';

在这里插入图片描述
看下执行计划可以知道走索引上，你建立三个，我走了1个，没问题。

按索引顺序查三个：

EXPLAIN select * from staffs where name='July' and age=23 and pos='dev';

在这里插入图片描述 Extra为Null为什么？Extra为Null的时候，如果走了索引，说明这个查询，进行了回表！这次走的二级索引，查的字段是*，没有覆盖索引，根据查到的主键再去聚簇索引查一遍数据。
（覆盖索引：一个索引包含了所有需要查询的字段的值，称之为“覆盖索引”。覆盖索引查询无须回表，能极大地提高性能。）
比如查的字段是name，就能覆盖索引，不需要回表。

EXPLAIN select name from staffs where name='July' and age=23 and pos='dev';

在这里插入图片描述 where后面过滤条件用到联合索引中的字段越多，key_len就越长，就是全值匹配我最爱。

注意：

只用where name=‘July’，可以使用联合索引，但是key_len=63，没有131长。
跳过联合索引中的字段age，使用where name=‘July’ and pos=‘dev’，会使用索引，key_len=63。因为所以没用上改索引age及其后面的字段的索引。即“中间兄弟不能断”。
跳过name，直接 where age=23 and pos=‘dev’，不会使用该联合索引，即“带头大哥不能死”。

总结：全值匹配我最爱，最左前缀要遵守；带头大哥不能死，中间兄弟不能断；

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

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

explain select * from staffs where left(name, 4) = 'July';

在这里插入图片描述

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

explain select * from staffs where name = 'July' and age > 14 and pos = 'manager';

因为2楼已经是范围查找了，age用到了索引，进行范围查找，但是后面的索引pos就失效了，因为你告诉我2楼，没告诉我去3楼的路，所以我找不到3楼的目录，定位不到3楼，只能挨个去找入口！
** 建议：将可能做范围查询的字段的索引顺序放在最后**

1.4、尽量使用覆盖索引

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

1.5、使用不等于(!= 或者<>)的时候索引失效

mysql 在使用不等于(!= 或者<>)时，有时会无法使用索引会导致全表扫描。

1.6、字段的is not null 和is null

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

1.7、like 的前后模糊匹配

like以通配符开头(‘%abc…’)时，Mysql索引会失效变成全表扫！
前缀不能出现模糊匹配！

1.8、减少使用or

使用or时不走索引。

使用union all 或者union 来替代：

1.9、总结口诀

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

二、关联查询优化

保证被驱动表的JOIN字段已经创建了索引
LEFT JOIN 时，选择小表作为驱动表，大表作为被驱动表。减少外层循环的次数。

什么叫作“小表”？
在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与join的各
个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。小的度量单位指的是表行数*每行大小。

三、子查询优化

子查询要建立临时表，效率不高。换成JOIN。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能就会更好。

四、排序优化

在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中避免全表扫描，在 ORDER BY 子句避免使用 FileSort 排序。

五、分组优化

原则几乎跟order by一致。
where效率高于having，能写在where限定的条件就不要写在having中了。
减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。

六、索引下推

Index Condition Pushdown(ICP)是MySQL 5.6中新特性，是一种在存储引擎层使用索引过滤数据的一种优化方式。回表之前先过滤，再根据较少的信息去回表。
优点：ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数。

七、其它查询优化策略

7.1、COUNT(*)与COUNT(具体字段)效率

一般情况下，Select Count (*)和Select Count(1)两着返回结果是一样的
假如表沒有主键(Primary key), 那么count(1)比count(*)快，
如果有主键的话，那主键作为count的条件时候count(主键)最快
如果你的表只有一个字段的话那count(*)就是最快的
count(*) 跟 count(1) 的结果一样，都包括对NULL的统计，而count(column) 是不包括NULL的统计

7.2、关于SELECT(*)

不要使用 * 作为查询的字段列表，推荐使用SELECT <字段列表> 查询。
因为：
① MySQL 在解析的过程中，会通过查询数据字典将"*"按序转换成所有列名，这会大大的耗费资源和时间。
② 无法使用覆盖索引

7.3、LIMIT 1 对优化的影响

针对的是会扫描全表的 SQL 语句，如果你可以确定结果集只有一条，那么加上LIMIT 1 的时候，当找
到一条结果的时候就不会继续扫描了，这样会加快查询速度。
如果数据表已经对字段建立了唯一索引，那么可以通过索引进行查询。

参考：MySQL高级特性篇-宋红康

Justinyh

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
9_MySQL索引优化与查询优化

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

扫一扫