[大阪/横滨/德岛] 寻找基础设施/服务器端工程师!

[大阪/横滨/德岛] 寻找基础设施/服务器端工程师!

【超过500家企业部署】AWS搭建、运维、监控服务

【超过500家企业部署】AWS搭建、运维、监控服务

【CentOS的后继者】AlmaLinux OS服务器搭建/迁移服务

【CentOS的后继者】AlmaLinux OS服务器搭建/迁移服务

[仅适用于 WordPress] 云服务器“Web Speed”

[仅适用于 WordPress] 云服务器“Web Speed”

[便宜]网站安全自动诊断“快速扫描仪”

[便宜]网站安全自动诊断“快速扫描仪”

[预约系统开发] EDISONE定制开发服务

[预约系统开发] EDISONE定制开发服务

[注册100个URL 0日元] 网站监控服务“Appmill”

[注册100个URL 0日元] 网站监控服务“Appmill”

【兼容200多个国家】全球eSIM“超越SIM”

【兼容200多个国家】全球eSIM“超越SIM”

[如果您在中国旅行、出差或驻扎]中国SIM服务“Choco SIM”

[如果您在中国旅行、出差或驻扎]中国SIM服务“Choco SIM”

【全球专属服务】Beyond北美及中国MSP

【全球专属服务】Beyond北美及中国MSP

[YouTube]超越官方频道“美由丸频道”

[YouTube]超越官方频道“美由丸频道”

如何调整 SQL 以适应使用 MySQL 运行的系统第 2 部分 ~SQL 分析 ~

这是《Beyond》里的原冈。

这是前面内容的延续。

如何将 SQL 调整到运行在 MySQL 上的系统 - 发现瓶颈 -

这次,我将解释如何分析我发现的瓶颈 SQL。

如果发现大量 SQL,请排除原因。
修改SQL的方法有很多,但
基本思想是适当使用索引。

・什么是指数?

顾名思义,索引
是一个可以让您快速访问数据的标题。
最常用的基本索引是一种称为 B 树索引的结构。

【详情见下文】
Wiki索引(数据库)
B树索引简介

正确使用或不使用索引,
其效果不仅是数倍,而是数百倍、甚至数千倍,因此非常有效。

・使用说明

使用EXPLAIN来分析SQL。
使用起来很简单,只需在要分析的SQL前添加“EXPLAIN”即可。

如果使用explain来分析,会输出如下分析结果。

用于主键搜索

mysql > 解释 select col_b from tbl_a where col_b=100000; +----+------------+----------------+-- ------+------+---------+---------+---------+ ------+ -------+ | select_type | key_len | 额外 | ------------+--------+----------------+---- -----+-- -------+-------+------+| 简单 | 常量 | 1空 | +----+-------------+----------------+---- ---+---- -----------+---------+---------+--------+-- ----+--- ----+ 1 行一组(0.01 秒)

用于索引搜索

mysql > 解释 select col_b from tbl_a where col_c=2000000; +----+------------+----------------+-- ----+----------------+------------+------------+---- ---+------+ | id | 类型 | key_len | --------+----------------+--------+----------------+ ---------+---------+--------+------+------+ | 1 | | tbl_a | col_c | 1 | +----+------------- --+--------+----------------+-------------+-------- - +--------+------+-----+

用于全扫描

mysql > 解释 select col_b from tbl_a where col_d='aaa'; ------+----------------+------+---------+--------+--- -----+-------------+ | select_type | 类型 | key_len | ----------+----------------+------+----------------+ ------+---------+------+---------+------------+ | 1 | 简单 | NULL | 500000 | ------+------+----------------+--------+---------+--- -- -+---------+-------------+

如果你有“使用文件排序”

mysql > 解释 select col_b from tbl_a where col_d='aaa' order by col_e; --+-----+------+------+---------+-----+ --------+ --------------------------+ | select_type | 类型 | key_len | ---+-------------+----------------+---- --+--------- ------+------+---------+------+---------+------------ -------------------+ | 简单 | NULL | NULL | 使用文件排序 | +------------+----------------+--------+ ------------ ---+-----+---------+------+---------+- -------------- -----------------+

表 8.1 EXPLAIN 输出列

柱子 意义
ID 选择标识符。
选择类型 选择类型
桌子 输出行表
类型 组合型
可能的键 可选择索引
钥匙 实际选择的索引
密钥长度 选定的密钥长度
参考 列与索引的比较
估计检查的行数
额外的 附加信息

[参考]
EXPLAIN 输出格式

・注意事项


小心是否有select_type

type
ALL 正在对表进行全量扫描,因此使用索引比较重,

INDEX A 全量不会有太大影响。正在执行索引扫描,很重。

key
使用索引,如果为NULL则不使用

按行
基本上,如果该值很大,请小心。

额外的
Using filesort和Usingtemporary

【参考】
2014年MySQL初学者索引调优要点总结

・检查表结构和索引

使用desc检查表结构

mysql > desc tbl_a; +------------------------+-------------------- -+------+------+----------------------+--------+ | 字段 |类型 | 空 | 额外 | - ----+------+-----+----------------------+--------+ | col_b | 没有 | NULL | | varchar(20) | (5) | 空 | +--------------------+------------ ---- ---+------+----+--------------------+-------- +

使用 show index 检查索引

mysql > 显示 account_public 的索引; +----------------+------------+------------- ---+--------------+-----------------+------------ +------------+---------+---------+--------+-------- ---+---------+| 表 | 索引中的序列 | 排序规则 | | 索引_类型 | +------------+------------ ------+--------------+----------------+---------- --+ -------------+----------+---------+--------+----- ---- ---+---------+-----------+ | 0 | 500000 | | NULL | 0 | 80000 | +--------------+--------- --------+------------+--- ----------+------------+---- ----+--------+------------ --+---------+---------------- +

有一行叫做“Cardinality”,它是列值的类型。
该数字越大,索引过滤效率越高(基数越高),
该数字越小,索引过滤效率越低(基数较低)。
创建尽可能高效的索引。

・添加索引

如果发现ALL全扫描、索引全扫描、Using filesort等,则在目标表上创建索引。

在 tbl_a(col_d) 上创建索引 col_d;

索引的
创建/删除/信息显示/添加

创建索引后,尝试再次解释并检查效果。
我们还将按原样运行 SQL,并比较添加索引前后的执行速度。
耐心地重复,直到找到有效的方法。

·概括

  • 使用EXPLAIN分析SQL
  • 尽可能切换到基于索引的搜索
  • 如果type=ALL,type=INDEX,则使用索引
  • 使用文件排序和使用临时文件时要小心
  • 如果行数很大,请使其尽可能小
  • 创建和使用具有大基数的高索引

顺便

  • 子查询很慢,所以如果可能的话避免使用它们。
  • 不要进行不必要的连接

[其他参考文章]
Yahoo!内部举办MySQL调优研讨会

如果您觉得这篇文章有帮助,请点赞!
0
加载中...
0 票,平均:0.00 / 10
4,471
X Facebook 哈特纳书签 口袋
[2025.6.30 Amazon Linux 2 支持结束] Amazon Linux 服务器迁移解决方案

[2025.6.30 Amazon Linux 2 支持结束] Amazon Linux 服务器迁移解决方案

写这篇文章的人

关于作者

原冈正宏

我是Beyond Co., Ltd.的代表董事。

Oracle DBA(负责UNIX、Linux、Windows Sever上的数据库)
⇒Linux工程师(在DC中构建和操作服务器、FW、L3等)
⇒云工程师(多云AWS、GCP、Azure等)
⇒经理

我的爱好是读书。 我什么都看,包括漫画、网络小说、商业书籍。 我最近开始独自露营。