TreeMind树图在线AI思维导图
笔灵Logo笔灵AI论文写作三步搞定,GO>>
当前位置:树图思维导图模板IT互联网互联网干货MYSQL调优思维导图

MYSQL调优思维导图

  收藏
  分享
免费下载
免费使用文件
大灰 浏览量:62022-11-19 22:32:55
已被使用0次
查看详情MYSQL调优思维导图

MYSQL初级知识点——MYSQL调优

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

思维导图大纲

MYSQL调优思维导图模板大纲

影响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值越大优先级越高;

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次的运行结果。

性能优化

索引优化

全值(等值)匹配 是最优选择;

最佳左前缀法则,如联合索引(a,b,c),可利用的索引就有(a), (a,b), (a,b,c);

不在索引列上做任何操作(计算、函数、类型转换),会导致索引失效而转向全表扫描;

存储引擎不能使用索引中范围条件右边的列;

尽量使用覆盖索引,减少select;

is null ,is not null 也无法使用索引;

like "xxx%" 是可以用到索引的,like以通配符开头('%abc'或'%abc%')索引失效会变成全表扫描的操作;

字符串不加单引号索引失效;

少用or,用它来连接时会索引失效;

<、<=、>、>=、BETWEEN、IN 可用到索引,<>、not in、!= 则不行,会导致全表扫描;

查询优化

永远小表驱动大表

in 和 exists 的选择

order by 关键字优化

MySQL支持两种方式的排序:Index和FileSort;index 效果高(扫描索引本身完成排序),FileSort效率较低。

order by 子句尽量使用 Index方式排序,避免使用FileSort方式排序;

order by 语句使用索引最左前列;

使用where子句与order by子句条件组合满足索引最左前列;

filesort方式排序有两种算法

双路排序:MySQL4.1之前是使用该方式,两次扫描磁盘,最终得到数据。

单路排序:从磁盘读取查询需要的所有列,按照order by列在buffer对它们进行排序,然后扫描排序后的列表进行输出,效率高于双路排序。

优化策略

增大 sort_buffer_size 参数的设置;

增大 max_length_for_sort_data 参数的设置;

group by 关键字优化

group by 实际是先排序后进行分组,遵照索引建的最佳左前缀;

当无法使用索引列,增大 max_length_for_sort_data和sort_buffer_size参数的设置;

where 高于having,能在where限定的条件就不要去having限定了;

数据类型优化

更小的通常更好

简单就好

尽量避免NULL

相关思维导图模板

MySQL一到三单元知识总结思维导图

树图思维导图提供 MySQL一到三单元知识总结 在线思维导图免费制作,点击“编辑”按钮,可对 MySQL一到三单元知识总结  进行在线思维导图编辑,本思维导图属于思维导图模板主题,文件编号是:523c77918d605338c0413c7d3e2f07b3

深入浅出MySQL数据库思维导图

树图思维导图提供 深入浅出MySQL数据库 在线思维导图免费制作,点击“编辑”按钮,可对 深入浅出MySQL数据库  进行在线思维导图编辑,本思维导图属于思维导图模板主题,文件编号是:4973c1019c6e392a02790e406f45b609