MySQL之SQL优化

试剑江湖
951 阅读4分钟

1. 前言

1.1 为什么要优化SQL?

性能低、执行时间太长、等待时间太长、SQL 语句欠佳（连接查询）、索引失效、服务器参数设置不合理（缓冲、线程数）

1.2 SQL 语句

  • 编写过程
select dinstinct  ..from  ..join ..on ..where ..group by ...having ..order by ..limit ..
  • 解析过程
from .. on.. join ..where ..group by ....having ...select dinstinct ..order by limit ...

1.3 SQL优化的本质

SQL 优化主要是在优化索引

索引是帮助MySQL高效获取数据的排好序的数据结构。

1.4 索引优劣势

索引的弊端

  1. 索引本身很大， 可以存放在内存/硬盘（通常为 硬盘）
  2. 索引不是所有情况均适用： a. 少量数据 b. 频繁更新的字段 c. 很少使用的字段
  3. 索引会降低增删改的效率（增删改 查）

索引的优势

  1. 提高查询效率（降低 IO 使用率）
  2. 降低 CPU 使用率 （...order by age desc,因为 B 树索引 本身就是一个 好排序的结构，因此在排序时 可以直接使用）

MySQL 执行流程推荐：MySQL 总体架构--->查询执行流程--->语句执行顺序

2. 避免索引失效原则

不要再索引上进行任何操作（计算、函数、类型转换），否则索引失效。

  • 不要在索引上进行加减乘除计算，例如：where t.tid * 3 = ‘’；乘与3为计算，会导致索引失效。
  • 不能使用不等于（!=、<、>、is null、is not null），模糊查询like以百分号%开头（’%参数%’）。

对于复合索引。

  • 不要跨列使用或无序使用（满足最佳左前缀）。
  • 尽量使用全索引匹配，复合索引中将不用的索引去掉。
  • 左边的索引失效会导致右边的索引失效。

2.1 计算导致索引失效

# 该查询会导致索引失效
explain select * from book where authorid * 2 = 1 and typeid = 2;
  • 由于乘与2，导致左边authorid索引失效

如果是复合索引，左边的失效，右边的也会失效。

image.png

如果不是复合索引，左边失效了不会导致右边失效。

# 删除book的索引
drop index index_atb on book;

# 添加单值索引
alter table book add index index_a(authorid);
alter table book add index index_t(typeid);

# 索引计算查询
explain select * from book where authorid * 2 = 1 and typeid = 2;

单值索引情况下，右边的索引不会失效。

image.png

2.2 !=判断，导致索引失效

# !=条件查询
explain select * from book where authorid != 1 and typeid = 2;

authorid使用了’!='运算符，导致索引失效。

image.png

2.3 比较符号（> < !=），导致索引失效

因为服务层中存在SQL优化器，可能会影响我们的优化。

# 删除book索引
drop index index_t on book;
drop index index_a on book;

# 添加复合索引
alter table book add index index_at(authorid, typeid);

# 条件查询，索引全部都在使用
explain select * from book where authorid = 1 and typeid = 2;

# 用了>比较，进行条件查询
explain select * from book where authorid > 1 and typeid = 2;
  • 在5.6及以前的数据库版本中：’>‘运算符本身以及右边的索引会失效。
  • 在5.7及以前的数据库版本中：’>'运算符本身会是range级别，而右边的会失效。

image.png

# < 1 的情况下本身不失效
explain select * from book where authorid < 1 and typeid = 2;

# < 4 的情况下索引全部失效
explain select * from book where authorid < 4 and typeid = 2;

索引失效可能性是概率事件。

image.png

结论：

  • 一般的SQL优化在大部分情况下适合使用，但是由于SQL优化器原因，不是100%正确。
  • 一般而言，范围查询（>、<、in），本身索引有效，右边的全部会失效。
  • 尽量使用覆盖索引（using index），即索引不会失效。

2.4 索引失效（模糊查询like），导致索引失效

# 给name创建索引
alter table book add index index_name(name);

# 模糊查询
explain select * from book where name like '%1%';

由于使用了模糊查询like，以百分号%开头，会造成索引失效：

image.png

# 使用不包含%开头的模糊查询
explain select * from book where name like '1%';

因为模糊查询中不是以备份好%开头，索引不会失效： image.png

2.5 不要进行类型转换

# 类型转换查询
explain select * from book where name = 123;

因为name为字符串类型，123为整数类型，SQL底层将123转换为’123’，进行了类型转换，因此索引失效。

image.png

2.6 尽量不使用or

# or条件查询
explain select * from book where name = '' or typeid = '';

因为使用了or连接条件，会导致两边的索引全部失效：

image.png

3. 为什么会导致索引失效？

  • 因为在设置索引的时候就将字段的类型进行存储，如在强转过程中会导致索引失效，是因为强转后该字段的类型与索引中的不匹配，导致全表扫描。
  • 在聚合索引中为什么要满足最佳左前缀？因为聚合索引在存储过程中以原本的顺序进行存储，当查询条件顺序与索引顺序不一致也会导致索引失效。
avatar
文章
阅读
粉丝