MYSQL:由一条慢查询引入思考 (MYSQL8)

// 查看慢查询是否开启
show variables like 'slow_query_log';
// 开启慢查询（值可以是1或on)
set global slow_query_log = 1;
// 关闭慢查询（值可以是1或off)
set global slow_query_log = 0;

// 查看long_query_time值
show variable like 'long_query_time';
// 设置long_query_time值 (单位是秒)
set global long_query_time=5;

注：若用以上命令来配置，则在重启后会失效，若想永久生效可以配置在my.conf中。

开启了配置之后，通过以下命令获取慢查询SQL的日志路径

show variables like 'slow_query_log_file%'

1.3 引起慢查询的原因和影响

在B端的应用系统中容易出现慢查询。B端系统上线一段时间之后，随着数据量越来越大，系统的性能问题也逐步浮水面，最常见的就是因慢查询引起的功能反应速度变慢而使用户体验越来越差。

慢查询可能会引起CPU损耗过高和系统IO压力增加，更严重的情况是整个系统崩溃。

引起慢查询的原因大致归纳如下：

没有索引或者SQL没有命中索引导致索引失效（思考：为什么会索引失效？）
单表数据量过多，导致查询遇到瓶颈。比如2000万条数据（思考：为什么有些表两千万条数据很快，而有些表两千万条数据就很慢？）
网络速度慢
I/O吞吐量小，形成了瓶颈效应
热点数据导致单点负载不均衡
内存不足
查询结果集的数据量过大
行锁或表锁
返回不必要的列

二、如何解决和避免慢查询

提高网速、更换更高容量的硬盘、增加内存或者 cpu 的数量等等
调整配置参数：mysql 有许多参数可以配置，可以根据实际情况调整这些参数，如增加缓存大小、线程池大小等等。
添加索引：索引可以提高查询效率，特别是对于大型表。通过分析慢查询日志或者使用 explain 命令找到需要优化的查询语句，然后为其中涉及的列添加索引（注意不要添加过多的索引）
优化查询语句：合理优化查询语句可以减少查询时间。例如，可以尝试减少子查询的数量，避免使用SELECT *，多表JOIN，避免使用 like '%xxx%' 的模糊查询等。
批量处理数据：有时候大量数据的操作往往比单个数据的操作更有效率。因此，尽可能以批量方式操作数据，如使用 insert .. values() 和 update .. set .. where in() 等。
分库分表：若数据量较大，可能会对单个数据库的性能造成压力。此时可以考虑将数据分散存储到多个数据库中，或者将单张表的数据拆分为多张表来存储。注意，这种方法需要谨慎设计，在实际应用中可能会引入更多的问题。
表中的大字段剥离
字段冗余
减少sql中函数运算与其他计算
修改SQL语句：优化查询语句，避免使用SELECT *、子查询、多表JOIN等不必要的操作。
数据库优化：调整数据库参数、内存占用、磁盘IO等，提高系统性能，增加查询效率。
针对查询频繁的热点数据增加缓存，引入非关系型数据库
主从复制，读写分离，一般情况下，查询的情况比写的情况多，所以考虑将数据库分为主库，从库，主库处理写的操作，从库处理读的操作

三、提升SQL性能必知必会

常常听到同事们讨论有关慢查询的问题 “哎这个SQL怎么这么慢，看一下加索引了没有？”

思考：添加索引后为什么查询速度会增加？为什么即使添加索引也不能提升查询速度？

3.1 添加索引提升查询速度

数据内存中比较相比mysql的查询产生io的耗时可忽略不计，所以查询速度取决于查询过程中的IO次数耗时，即提高查询次数的有效方法是减少IO次数（mysql的数据是存储在磁盘中）
mysql innoDB引擎索引数据结构是B+tree结构（树节点称为数据叶）
每个数据叶默认大小为16kb(16384)（show VARIABLES like 'innodb_page_size';）
假设我们用bigint做为主键索引大概占8个字节，（B+tree特点）有指向下一个的指针大概占6个字符，则每个数据叶可以存放的索引信息有 16384 / （8 + 6）= 1170个索引信息。
- 对于主键过引，假设一行数据1kb，则叶子可存16条数据。当B+Tree的高度为h = 2 则数据量为 1170 * 16 = 18720条数据，当B+Tree的高度为 h = 3 则数据量为1170 * 11170 * 16 = 21902400条数据（2190.24万）
- 对于非主键索引，则叶子节点的索引信息有 16384 /（8+8）= 1024个索引信息。若h=2 则数据量为 1170 * 1024 = 1198080，若h=3 则数据量为 1170*1170*1024 = 1401753600条数据（14亿零175.36万）
结论：
1. 非主键索引，索引覆盖，14亿条数据情况下只需要3次io即可查询到想要的数据
2. 主键索引查询，2190.24万条数据情况下只走需要3次io即可查询到想要的数据

思考：非主键索引查询速度快还是主键索引查询速度快？为什么InnoDB的索引用B+Tree结构而不用其他数据结构？