MYSQL调优思维导图

大灰

2022-11-19

浏览量: 6

MYSQL初级知识

调优

数据库知识点

MYSQL初级知识点——MYSQL调优

树图思维导图提供《MYSQL调优》在线思维导图免费制作，点击“编辑”按钮，可对《MYSQL调优》进行在线思维导图编辑，本思维导图属于思维导图模板主题，文件编号是：69428b633d4c51165f1e870f55fac65a

思维导图大纲

影响MySQL性能的因素

业务需求（适合度）

存储定位

不适合放进MySQL的数据

二进制多媒体数据

流水队列数据

超大文本数据

需要放进缓存的数据

系统各种配置及规则

活跃用户的基本信息

活跃用户的个性化定制信息

准实时的统计信息

其他一些访问频繁但变更较少的数据

Schema设计对系统的性能影响

尽量减少对数据库访问的请求

尽量减少无用数据的查询请求

硬件环境对系统性能的影响

性能分析

MySQL常见瓶颈

CPU：CPU在饱和的时候，一般发生在数据装入内存或从磁盘读取数据。

IO：磁盘IO瓶颈发生在装入数据远大于内存容量的时候。

服务器硬件性能：top、free、iostat 和 vmstat 查看系统性能状态。

性能下降、SQL慢、执行时间长、等待时间长，原因分析

查询语句写的太烂；

索引失效（单值、复合）；

关联查询太多join（设计缺陷或不得已的需求）；

服务器调优及各个参数设置（缓冲、线程数等）；

常见性能分析手段

慢查询日志

MySQL提供的一种日志记录，用于记录MySQL中响应时间超过阈值的语句，具体指运行时间超过 long_query_time 值的收起来，会被记录到慢查询日志中。

long_query_time 的默认值为10，运行10秒以上的语句被记录。

默认情况下，MySQL数据库没有开启慢查询日志，需要手动设置参数开启。

查看开启状态

开启慢查询日志

临时配置

永久配置

日志分析工具 mysqldumpslow

得到返回记录集最多的10个SQL

得到访问次数最多的10个SQL

得到按照实际排序的前10条里面含有左连接的查询语句

Explain（执行计划）

是什么？

使用Explain 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或表结构的性能问题。

能干什么？

表的读取顺序

数据读取操作的操作类型

哪些索引可以使用

哪些索引被实际使用

表之间的引用

每张表有多少行被优化器查询

怎么用？

Explain + SQL语句，得到以下信息

Explain返回的各字段解析

id相同，执行顺序从上往下；

id全不同，如果是子查询，id的序号会递增，id值越大优先级越高；

id部分相同，执行顺序是按照数字大的先执行，然后数字相同的安装从上往下的顺序执行；

select_type

SIMPLE：简单的select查询，不含子查询或union；

PRIMARY：查询中若包含复杂的子部分，最外层被标记为PRIMARY；

SUBQUERY：在select或where列表中包含了子查询；

DERIVED：在from列表中包含的资产性被标记为derived，mysql会递归执行这些子查询，把结果放在临时表里；

UNION：若第二个select出现在union之后，则被标记为union，若union包含在from子句的子查询中，外层select将被标记为derived；

UNION RESULT：从union表获取结果的select；

table

type

system：表只有一行记录（等于系统表），是const类型的特例，平时不会出现。

const：表示通过索引一次就找到了，const用于比较primary key或unique索引，因为只要匹配一行数据，所以很快，如将主键置于where列表中，MySQL就能将该查询转换为一个常量。

eq_ref：唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描。

ref：非唯一索引扫描，范围匹配某个单独值得所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行。

range：只检索给定返回的行，使用一个索引来选择行。key列显示使用了哪个索引。一般是在where语句中出现 between、<、>、in等的查询。

index：full index scan，index与ALL的区别为index类型值遍历索引树，通常比ALL快，因为索引文件通常比数据文件小。（index 和 all 都是全表扫描，但index是从索引中读取，all是从磁盘中读取。）

ALL：Full table scan，将遍历全表找到匹配的行。

possible_keys

key

key_len

表示索引中使用的字节数，可通过该列计算计算查询中使用的索引长度。在不损失精确性的情况下，长度越短越好。

key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

ref

rows

Extra

using filesort: 说明mysql会对数据使用一个外部的索引排序，不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作称为“文件排序”。常见于order by和group by语句中

Using temporary：使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。

using index：表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错，如果同时出现using where，表明索引被用来执行索引键值的查找；否则索引被用来读取数据而非执行查找操作

using where：使用了where过滤

using join buffer：使用了连接缓存

impossible where：where子句的值总是false，不能用来获取任何元祖

select tables optimized away：在没有group by子句的情况下，基于索引优化操作或对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化

distinct：优化distinct操作，在找到第一匹配的元祖后即停止找同样值的动作

示例

示例图

第一行（执行顺序4）：id列为1，表示是union里的第一个select，select_type列的primary表示该查询为外层查询，table列被标记为，表示查询结果来自一个衍生表，其中derived3中3代表该查询衍生自第三个select查询，即id为3的select。【select d1.name......】

第二行（执行顺序2）：id为3，是整个查询中第三个select的一部分。因查询包含在from中，所以为derived。【select id,name from t1 where other_column=''】

第三行（执行顺序3）：select列表中的子查询select_type为subquery，为整个查询中的第二个select。【select id from t3】

第四行（执行顺序1）：select_type为union，说明第四个select是union里的第二个select，最先执行【select name,id from t2】

第五行（执行顺序5）：代表从union的临时表中读取行的阶段，table列的<union1,4>表示用第一个和第四个select的结果进行union操作。【两个结果union操作】

show profile分析查询

MySQL 提供可以用来分析当前会话中语句执行的资源消耗情况。

默认情况下，参数处于关闭状态，并保存最近15次的运行结果。