Skip to content

大体来说,MySQL 可以分为 Server 层存储引擎层两部分。

  • Server 层包括连接器、查询缓存、分析器、优化器、执行器等,涵盖 MySQL 的大多数核 心服务功能,以及所有的内置函数(如日期、时间、数学和加密函数等),所有跨存储引 擎的功能都在这一层实现,比如存储过程、触发器、视图等。
  • 存储引擎层负责数据的存储和提取。其架构模式是插件式的,支持 InnoDB、 MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB,它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

一条查询语句的执行过程一般是经过连接器、分析器、优化器、执行器等功能模块,最后到达存储引擎。

在 MySQL 中,索引是在存储引擎层实现的,所以并没有统一的索引标准,即不同存储引擎的索引的工作方式并不一样。

索引类型分为主键索引和非主键索引。在 InnoDB 里,主键索引也被称为聚簇索引(clustered index),非主键索引也被称为二级索引(secondary index)。

基于非主键索引的查询需要多扫描一棵索引树。因此,我们在应用中应该尽量使用主键查询。

开启慢查询

查看慢查询状态 show variables like 'slow_query_log'

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
# 无论是否超时,未被索引的记录也会记录下来。
SET GLOBAL log_queries_not_using_indexes = 'ON';
# 慢查询阈值(秒),SQL 执行超过这个阈值将被记录在日志中。
SET SESSION long_query_time = 1;
# 慢查询仅记录扫描行数大于此参数的 SQL。
SET SESSION min_examined_row_limit = 100;
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
# 无论是否超时,未被索引的记录也会记录下来。
SET GLOBAL log_queries_not_using_indexes = 'ON';
# 慢查询阈值(秒),SQL 执行超过这个阈值将被记录在日志中。
SET SESSION long_query_time = 1;
# 慢查询仅记录扫描行数大于此参数的 SQL。
SET SESSION min_examined_row_limit = 100;

最好将配置写入配置文件

sudo vi /etc/mysql/my.cnf

[mysqld]

slow-query-log = 1
slow-query-log-file = /var/log/mysql/localhost-slow.log
long_query_time = 1
log-queries-not-using-indexes
[mysqld]

slow-query-log = 1
slow-query-log-file = /var/log/mysql/localhost-slow.log
long_query_time = 1
log-queries-not-using-indexes

使用 mysqldumpslow 慢查询工具

使用 explain 分析SQL

EXPLAIN SELECT * FROM res_user ORDER BY modifiedtime LIMIT 0,1000

常见慢查询的优化

  • 拆解关联查询
  • 讲字段很多的表分解为多个表
  • 增加中间表
  • 优化limit