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值越大优先级越高;
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一到三单元知识总结 进行在线思维导图编辑,本思维导图属于思维导图模板主题,文件编号是:523c77918d605338c0413c7d3e2f07b3
树图思维导图提供 深入浅出MySQL数据库 在线思维导图免费制作,点击“编辑”按钮,可对 深入浅出MySQL数据库 进行在线思维导图编辑,本思维导图属于思维导图模板主题,文件编号是:4973c1019c6e392a02790e406f45b609