1.如何定位慢SQL
我们知道数据库瓶颈80%都在查询上，数据库优化有一个比较重要的环节就是定位系统中的慢SQL，那么我们如何快速定位到哪些查询语句比较耗时呢？Mysql有自己的慢SQL定位功能
MySQL的慢查询日志，用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过 long-query_time值的SQL，则会被记录到慢查询日志中。 long_query_time的默认值为 10，意思是运行10秒以上(不含10秒)的语句，认为是超出了我们的最大忍耐时间值。
| 命令 | 备注 |
|---|---|
| show status like ‘slow_queries’ | 查看慢查询数量 |
| show variables like ‘long_query_time’ | 可以显示当前慢查询时间 ， 默认阈值 10s |
| set long_query_time=1 | 可以修改慢查询时间 ， 可以通过my.ini永久修改 |
| show variables like ‘%slow_query_log%’ | 查看慢查询的日志路径 |
| SET GLOBAL slow_query_log = 1 | 开启慢查询日志 |
| SET GLOBAL slow_query_log_file=‘路径\slow.log’ | 指定慢查询日志文件 |
| set global log_output=‘TABLE’ | 把慢SQL输出到table |
| select * from mysql.slow_log | 查看日志内容 |
通过 show variables like ‘%slow_query_log% 查看慢日志是否开启以及可以看到日志存储位置，如果未开启使用SET GLOBAL slow_query_log = 1来开启
慢SQL记录默认执行时长为10S+的SQL，通过set long_query_time=1来修改慢SQL的时间。 此外还可以通过set global log_output=‘TABLE’来把慢SQL记录到Table中，通过SELECT *,CONVERT( sql_text USING utf8) AS text_data from mysql.slow_log来查看，这样就可以快速定位到慢SQL
上面的命令都是临时的，重启Mysql后就会失效，如果需要永久修改需要修改my.ini
[mysqld]
slow_query_log=ON # 开启慢查询日志的开关
slow_query_log_file=/var/lib/mysql/atguigu-slow.log #慢查询日志的目录和文件名信息
long_query_time=3 #设置慢查询的闽值为3秒，超出此设定值的SQL即被记录到慢查询日志
log_output=FILE
除此之外我们还可以通过三方工具来监控慢SQL，比如：Druid连接池的SQL监控
定位到慢SQL之后我们就需要去优化它，SQL优化的维度有很多，比如：
- SQL是否太复杂，或者JOIN表太多，子查询太多：这种情况需要进行SQL简化，可以通过SQL拆解把一条SQL拆成多条SQL，或者通过冗余字段来减少JOIN。
- 是不是数据量太大：如果是表数据量太大，那么需要考虑拆表，可以考虑垂直拆分或者水平拆分来减少表的数据量提高查询速度
- 如果是对于报表类的查询最好是做统计表+定时结算的方式，不要直接查大表
- 还有一个很重要的维度就是索引优化，500W的数据如果把索引优化好查询一般不会有大的问题
2.使用explain分析SQL
Explain用来分析SELECT查询语句，开发人员可以通过分析Explain结果来优化查询语句。通过explain命令我们可以学习到该条SQL是如何执行的，随后解析explain的结果可以帮助我们使用更好的索引，最终来优化它！通过explain命令我们可以知道以下信息：表的读取顺序，数据读取操作的类型，哪些索引可以使用，哪些索引实际使用了，表之间的引用，每张表有多少行被优化器查询等信息。如下,我们在 select 语句前面加上explain 就可以看到SQL有没有用到索引
ID列
id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。
select_type列
select_type 表示对应行是简单还是复杂的查询。
-
simple：代表一个简单的查询，没有连表的情况
-
primary：SQL的主查询，如：复杂查询中最外层的 select
-
subquery：包含在 select 中的子查询（不在 from 子句中）如：
EXPLAIN select id,(select name from t_user where id = 1) from t_user
-
derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）
-
union：在有union连表查询的时候会出现 如：
explain select 1 union all select 2;
table列
代表的是当前访问的是哪个表
partitions列
分区，如果查询是基于分区表的话，会显示查询将访问的分区。分区用的很少
type列
这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围，依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL 一般来说，得保证查询达到range级别，最好达到ref
const, system：const是常量的意思，Mysql能对查询的某部分进行优化并将其转化成一个常量，用于 primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。system是const的特例，表里只有一条元组匹配时为system.案例如下：
explain select * from t_user where id = 1
eq_ref：primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。多出现于外键连表的查询中案例： explain select * from t_user u join t_dept d on u.dept_id = d.id
ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀的情况，索引要和某个值相比较，可能会找到多个符合条件的行。案例如：explain select * from t_user where name = "zs"
range：范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。案例如：explain select * from t_user where id > 1
index：扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些。案例如：explain select name from t_user
all：即全表扫描，扫描你的聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了。
possible_keys 列
该列展示的是当前SQL可以选择的索引列，如果为null那么需要检查wehre后面的条件列是否有可选择的索引，案例 explain select * from t_user where name = "zs" and id = 1
可以选择的索引有3个 ：PRIMARY , index_name_age , name ，Mysql选择了主键索引。
key 列
这一列显示mysql实际采用哪个索引来优化对该表的访问。如果没有使用索引，则该列是 NULL。
key_len列
这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。 比如：
key_len为8，因为Mysql使用了主键列，主键列是BigInt类型，这种类型占8个字节。
ref列
显示索引的哪一列被使用了，如果有可能是一个常数，哪些列或const(常量)被用于查询索引列上的，比如：explain select * from t_user u join t_dept d on u.dept_id = d.id
rows列
这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。
Extra列
这一列展示的是额外信息。常见的重要值如下：
-
Using index：表示相应的select操作用使用覆盖索引，避免访问了表的数据行，案例：
explain select name from t_user
因为index_name这个索引中就包含了查询的name列，所以不用回表了 -
Using where：使用 where 语句来处理结果，并未使用到索引，需要优化 案例：
explain select * from t_user where age = 11这里看得出没有使用任何索引
-
Using index condition：是 MySQL 查询执行计划中的一个输出，它表示 MySQL 优化器决定使用“索引条件推送”优化技术来执行查询。索引条件推送 (Index Condition Pushdown, ICP): 这是一个查询优化技术，允许 MySQL 将部分 WHERE 子句的条件下推到存储引擎层，使其可以在读取索引条目时直接评估这些条件。这可以减少不必要的数据读取，从而提高查询性能。案例
explain select age from t_user where username = "zs" and name like "z%"
-
Using temporary：使用了临时表保存中间结果，mysql在查询结果排序时使用临时表。常见于排序order by和分组查询group by,案例：
explain select DISTINCT(name) from t_user
name列没有索引，那么在去重的时候会使用一个临时表来存储name列的值然后去重。如果给name列创建索引那么就会 ： using index 在索引中去重。 -
Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。案例
explain select * from t_user order by age
-
Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段时.案例
explain select max(id) from t_user
3.怎么选择索引列
这里我们需要一个常识，索引就是以空间换时间的打法，索引本身会占用磁盘空间，且索引会降低DML操作(增删改)的性能，当我们对数据发生写操作索引也需要跟着修改，所以索引不是越多越好，而是要找到过滤数据最有效的那个列来创建索引，或者结合业务场景来设计索引，那么什么样的列适合创建索引呢
- 不会出现在WHERE，ORDER BY, GROUP BY子句中字段不该创建索引
因为意义不大，这样的列即使创建了索引也大概率不会用到,反而增加了磁盘开销和维护成本 - 唯一性太差(离散度)的字段不适合单独创建索引
比如：状态，性别这种字段，假如我有1千万数据，对于性别最多过滤一般，剩下的数据量依然很大，所以这种字段创建索引没有太大意义。 - 更新非常频繁的字段不适合创建索引
因为字段值修改索引也要跟着修改，这样反而增加了索引维护成本会导致性能变差 - 对于记录数较少的表不适合创建索引
表数据少创建索引可能并不会带来明显的性能提升。相反，由于索引本身也需要存储和管理，因此可能会增加额外的开销。
4.索引失效的情况
在 MySQL 中，索引虽然能极大地提高查询性能，但在某些情况下，索引可能不会被优化器使用，即索引失效。以下是导致 MySQL 索引失效的一些常见情况：
-
使用了函数或表达式
当在 WHERE 子句中对索引列使用函数或表达式时，MySQL 通常无法使用该索引。例如：SELECT * FROM users WHERE YEAR(create_date) = 2023;在这个例子中，YEAR() 函数导致 create_date 列上的索引失效。因为B+tree的节点上存储的是一个确切的值，它无法和 函数计算过的不确定值进行比较 -
类型不一致导致隐式类型转换
当查询条件中的数据类型与索引列的数据类型不一致，并且 MySQL 需要进行隐式类型转换时，索引可能失效。例如：SELECT * FROM users WHERE phone_number = '1234567890';如果 phone_number 列是整数类型，而查询中使用了字符串字面量，MySQL 可能需要进行类型转换,从而用到转换函数，从而导致索引失效 -
使用了 OR 连接的条件
在某些情况下，使用 OR 连接的条件可能导致索引失效，尤其是当 OR 连接的列不在同一个索引中时。比如：SELECT * FROM users WHERE name = 'John' OR email = 'john@example.com';如果 name 和 email 不在同一个索引中，MySQL 可能无法使用索引来优化这个查询。 -
LIKE 查询以通配符开头
当使用 LIKE 查询时，如果搜索模式以通配符 % 开头，MySQL 通常无法使用该列的索引，比如：SELECT * FROM users WHERE name LIKE '%Smith';因为左边的’%'代表了一个不确定的值，无法和索引进行比较，如果是’Smith%'这可以使用到索引，因为可以向左匹配 -
不等于操作
使用 != 或 <> 进行不等于操作时，MySQL 通常不会使用索引，因为它需要扫描全表来找到不等于指定值的记录。 -
IS NULL 或 IS NOT NUL
在某些情况下，对索引列使用 IS NULL 或 IS NOT NULL 条件可能不会导致索引被使用，尤其是当表中包含大量 NULL 值时 -
Mysql优化器决定不使用索引
及时可以使用索引，如果mysql估计使用全表扫描要比使用索引快,则不使用索引，比如表里面只有一条记录
为了避免索引失效，你应该：
- 确保查询条件与索引列的数据类型和结构相匹配。
- 仔细选择和设计索引，包括复合索引的列顺序。
- 定期更新统计信息，以确保优化器做出正确的决策。
- 使用 EXPLAIN 语句来分析查询的执行计划，检查是否使用了预期的索引。
文章就先写到这把，拿去面试差不多也够用了，如果文章对你有所帮助请给个好评哦！！！
49
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
