记一次mysql优化

1、背景

单表以及多表联查

2、排查

1、使用慢sql

1、临时使用

查看是否开启慢查询日志

show variables like '%slow_query_log%';

开启慢查询（临时,当前会话有效）

set global slow_query_log='ON';

查看慢查询日志存放文件位置

show variables like '%slow_query_log_file%';

查看long_query_time阈值

show variables like '%long_query_time%';

设置long_query_time阈值（临时,当前会话有效）

set global long_query_time=3

2、全局生效

#编辑配置文件
vim /etc/my.cnf

# 修改配置项（如果没有就加上）
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/my-slow.log
long_query_time = 1
log_timestamps = SYSTEM
log_output = FILE

# 重启mysqld服务
systemctl restart mysqld

# 查看mysqld服务
systemctl status mysqld

3、慢sql分析

# Time: 2024-03-01T17:12:40.156488+08:00
# User@Host: panda[panda] @  [192.168.72.1]  Id:     8
# Query_time: 5.000688  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
SET timestamp=1709284355;
select sleep(5);

字段说明：
慢查询日志以#作为起始符。
Time:查询的时间。
User@Host：表示用户 和 慢查询查询的ip地址。
如上所述，表示 root用户 localhost地址。
Query_time: 表示SQL查询持续时间， 单位 (秒)。
Lock_time: 表示获取锁的时间， 单位(秒)。
Rows_sent: 表示发送给客户端的行数。
Rows_examined: 表示：服务器层检查的行数。
set timestamp ：表示 慢SQL 记录时的时间戳。
最后 select sleep(5) 则表示慢SQL语句。

2、查看 SQL 执行成本：SHOW PROFILE

Show Profile是MySQL提供的可以用来分析当前会话中SQL都做了什么、执行的资源消耗情况的工具，可用于sql调优的测量。默认情况下处于关闭状态，并保存最近15次的运行结果。

# 查看是否开启profile 分析执行耗时
show variables like '%profil%';
# OFF关闭，NO开启
set profiling=OFF;
# 查看最近执行的sql
show profiles ;
# 查看需要分析的sql
show profile cpu ,block io for query 2;

show profile的常用查询参数：

① ALL：显示所有的开销信息。
② BLOCK IO：显示块IO开销。
③ CONTEXT SWITCHES：上下文切换开销。
④ CPU：显示CPU开销信息。
⑤ IPC：显示发送和接收开销信息。
⑥ MEMORY：显示内存开销信息。
⑦ PAGE FAULTS：显示页面错误开销信息。
⑧ SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息。
⑨ SWAPS：显示交换次数开销信息。

日常开发需注意的结论：
①converting HEAP to MyISAM：查询结果太大，内存不够，数据往磁盘上搬了。
②Creating tmp table：创建临时表。先拷贝数据到临时表，用完后再删除临时表。
③Copying to tmp table on disk：把内存中临时表复制到磁盘上。
④locked。如果在show profile诊断结果中出现了以上5条结果中的任何一条，则sql语句需要优化。
注意：不过SHOW PROFILE命令将被弃用，我们可以用information_schema中的profiling数据表进行查看。

3、EXPLAIN

作用

 - 表的读取顺序 
 - 数据读取操作的操作类型 
 - 哪些索引可以使用 
 - 哪些索引被实际使用 
 - 表之间的引用 
 - 每张表有多少行被优化器查询

字段分析

3、语句及索引优化

1、避免使用== SELECT *== ，用到那列查询那列。

2、适当的使用limit

select * from user limit 1000000,10

对于 limit m, n 的分页查询，越往后面翻页（即m越大的情况下）SQL的耗时会越来越长，对于这种应该先取出主键id，然后通过主键id跟原表进行Join关联查询。因为MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写。

优化：

 - 使用索引覆盖+子查询优化：因为我们有主键 id，并且在上面建了索引，所以可以先在索引树中找到开始位置的 id 值，再根据找到的 id 值查询行数据。sql可以采用如下的写法：
select id,name from table_name where id > (select id from table_name order by id limit 866612, 1)
 - 起始位置重定义（效率最高，但是要记住ID值）：可以取前一页的最大行数的id（将上次遍历到的最末尾的数据ID传给数据库，然后直接定位到该ID处，再往后面遍历数据），然后根据这个最大的id来限制下一页的起点。比如此列中，上一页最大的id是866612。sql可以采用如下的写法：
select id,name from table_name where id > 866612 limit 20
 - 降级策略（分页场景）：配置 limit 的偏移量和获取数一个最大值，超过这个最大值，就返回空数据（4xx错误）。通常在查询时，超过我们配置的这个limit值可以认为用户已经不是在分页了，而是在刷数据了，如果确认要找数据，应该输入合适条件来缩小范围，而不是一页一页分页。

3、oredr by不是必要的话尽量不要使用

4、优化Join语句

当我们执行两个表的Join的时候，就会有一个比较的过程，逐条比较两个表的语句是比较慢的，因此可以把两个表中数据依次读进一个内存块中，在Mysql中执行：show variables like ‘join_buffer_size’，可以看到join在内存中的缓存池大小，其大小将会影响join语句的性能。在执行join的时候，数据库会选择一个表把他要返回以及需要进行和其他表进行比较的数据放进join_buffer。

什么是驱动表，什么是被驱动表

1.当连接查询没有where条件时
	1、left join 前面的表是驱动表，后面的表是被驱动表
	2、right join 后面的表是驱动表，前面的表是被驱动表
	3、inner join / join 会自动选择表数据比较少的作为驱动表
	4、straight_join(≈join) 直接选择左边的表作为驱动表（语义上与join类似，但去除了join自动选择小表作为驱动表的特性）
2.当连接查询有where条件时，带where条件的表是驱动表，否则是被驱动表

结论：

1、使用小表驱动大表
2、优先优化Nested Loop的内层循环（也就是最外层的Join连接），因为内层循环是循环中执行次数最多的，每次循环提升很小的性能都能在整个循环中提升很大的性能；
3、对被驱动表的join字段上建立索引；
4、当被驱动表的join字段上无法建立索引的时候，设置足够的Join Buffer Size。
5、尽量用inner join(因为其会自动选择小表去驱动大表).避免 LEFT JOIN (一般我们使用Left Join的场景是大表驱动小表)和NULL，那么如何优化Left Join呢？
	1。条件中尽量能够过滤一些行将驱动表变得小一点，用小表去驱动大表
	2。右表的条件列一定要加上索引（主键、唯一索引、前缀索引等），最好能够使type达到range及以上（ref,eq_ref,const,system）
6、适当地在表里面添加冗余信息来减少join的次数
7、使用更好的机器

Tips：Join左连接在右边建立索引；组合索引则尽量将数据量大的放在左边，在左边建立索引

4、索引优化

1、遵循最左前缀匹配规则
2、索引列尽量不做函数计算
3、连表查询关联字段尽量都设置索引，且字段类型一致，不一致会导致查询过慢
4、尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），不去进行回表。
5、模糊查询尽量使用==like%==模糊右边
6、在联合索引中要将区分粒度大的放在前边

附索引是否使用