1.思考流程

当遇到调优问题时该如何思考？

整个流程划分成了 观察（Show status） 和 行动（Action） 两个部分。字母 S 的部分代表观察（会使 用相应的分析工具），字母 A 代表的部分是行动（对应分析可以采取的行动）。

image-20230905163956703

image-20220627162345815

我们可以通过观察了解数据库整体的运行状态，通过性能分析工具可以让我们了解执行慢的SQL都有哪些，查看具体的SQL执行计划，甚至是SQL执行中的每一步的成本代价，这样才能定位问题所在，找到了问题，再采取相应的行动。

image-20230905164037734

2.性能分析

2.1.慢查询日志

慢查询日志（Slow Query Log）是数据库管理系统的一项重要功能，它用于记录执行时间超过一定阈值的查询语句

2.1.1.开启slow_query_log

查看是否开启

1
mysql > show variables like '%slow_query_log';

如果没开启，则开启日志

1
mysql > set global slow_query_log='ON';

image-20230905164730109

2.1.2. 修改 long_query_time 阈值

查看阈值 默认为10s

1
mysql > show variables like '%long_query_time%';

可以设置阈值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#global
mysql > set global long_query_time = 1;
mysql> show global variables like '%long_query_time%';

# session
mysql> set long_query_time=1;
mysql> show variables like '%long_query_time%';

#配置文件设置
[mysqld]
slow_query_log=ON  # 开启慢查询日志开关
slow_query_log_file=/var/lib/mysql/atguigu-low.log  # 慢查询日志的目录和文件名信息
long_query_time=3  # 设置慢查询的阈值为3秒，超出此设定值的SQL即被记录到慢查询日志
log_output=FILE

2.1.3.查看慢查询数目

1
SHOW GLOBAL STATUS LIKE '%Slow_queries%';

2.1.4.日志分析工具

mysqldumpslow

常见使用方式

1
2
3
4
5
6
7
8
9
10
11
#得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log

#得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log

#得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log

#另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

2.1.5.关闭日志

1
2
3
4
SET GLOBAL slow_query_log=off;

[mysqld]
slow_query_log=OFF

2.2.分析查询语句：EXPLAIN

基本语法

1
2
3
EXPLAIN SELECT select_options
或者
DESCRIBE SELECT select_options

EXPLAIN 语句输出的各个列的作用如下：

image-20230905170237546

3.索引优化

3.1.索引失效案例

计算、函数、类型转换(自动或手动)导致索引失效

情况一：函数

1
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';

情况二：参与计算

1
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;

情况三：类型转换

1
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;  #name为varchar,发生了类型转换

范围条件右边索引列失效

1
2
3
INDEX idx_age_classid_name;

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ; #不能全部利用索引 ，name字段索引失效

应将范围查询条件放在最后

1
2
3
4
#将索引改为
create index idx_age_name_classId on student(age,name,classId);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name = 'abc' AND student.classId>20;

不等于索引失效

1
CREATE INDEX idx_name ON student(NAME);

失效

1
2
3
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name <> 'abc';

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name != 'abc';

is not null索引失效

  • is null可以触发索引
  • is not null无法触发索引

左模糊和全模糊索引失效

1
2
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name LIKE '%ab';
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name LIKE '%ab%';

or前后存在非索引列

在WHERE子句中，如果在OR前的条件列进行了索引，而在OR后的条件列没有进行索引，那么索引会失效。也就是说，OR前后的两个条件中的列都是索引时，查询中才使用索引。

3.2.最左前缀原则

MySQL可以为多个字段创建索引，一个索引可以包含16个字段。对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。如果查询条件中没有用这些字段中第一个字段时，多列（或联合）索引不会被使用。

3.3.关联查询优化

  • 对于内连接来说，查询优化器可以决定谁作为驱动表，谁作为被驱动表出现的

  • 对于内连接来讲，如果表的连接条件只有一个字段有索引，则有索引的字段所在的表会被作为被驱动表

  • 对于内连接来讲，在两个表的连接条件都存在索引的情况下，会选择小表作为驱动表。“小表驱动大表”

3.4.子查询优化

可以使用连接（JOIN）查询来替代子查询。**连接查询 不需要建立临时表 ，其 速度比子查询 要快 ，如果查询中使用索引的话，性能就会更好。

3.5.排序优化

在MySQL中，支持两种排序方式，分别是 FileSort 和 Index 排序。

  • Index 排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高。
  • FileSort 排序则一般在 内存中 进行排序，占用CPU较多。如果待排结果较大，会产生临时文件 I/O 到磁盘进行排序的情况，效率较低。

优化策略

  1. SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中 避免全表扫描 ，在 ORDER BY 子句 避免使用 FileSort 排序 。当然，某些情况下全表扫描，或者 FileSort 排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。
  2. 尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列； 如果不同就使用联合索引。

3.6.使用覆盖索引

什么时覆盖索引

一个索引包含了满足查询结果的数据就叫做覆盖索引

4.索引设计原则

  • 频繁作为 WHERE 查询条件的字段
  • 字段的数值有唯一性的限制
  • 经常 GROUP BY 和 ORDER BY 的列
  • UPDATE、DELETE 的 WHERE 条件列
  • DISTINCT 字段需要创建索引
  • 多表连接时对连接条件创建索引
  • 使用字符串前缀创建索引
  • 区分度高(散列性高)的列适合作为索引
  • 使用最频繁的列放到联合索引的左侧

其他调优策略

。。。