一、Mysql性能分析
1.1 MySQL Query Optimizer
MySQL Optimizer是一个专门负责优化SELECT 语句的优化器模块,它主要的功能就是通过计算分析系统中收集的各种统计信息,为客户端请求的Query 给出他认为最优的执行计划,也就是他认为最优的数据检索方式。
Mysql自己认为最优的不见得是DBA认为最优的 这部分会耗时!
1.2 MySQL 常见瓶颈
CPU:cpu的饱和一般发生在数据装入内存或从磁盘上读取数据时候
IO:磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
服务器硬件的性能瓶颈 机器不行或参数配置问题
常用命令:
vmstat: 显示虚拟内存的统计信息,还可以报告关于进程、内存、I/O等系统整体运行状态。 选项: -a:显示内存活动 -f:显示启动后创建的进程总数 -s:以表格方式显示时间计数器和内存状态,不能刷新显示 -d:显示磁盘状态 -p:显示指定的分区状态 -S:指定输出信息的单位 -n:状态信息刷新的时间间隔和次数 top: https://blog.csdn.net/zhuoya_/article/details/81049967
free:
free命令可以显示Linux系统中空闲的、已用的物理内存及swap内存, 被内核使用的buffer。在Linux系统监控的工具中,free命令是最经常使用的命令之一。 1.命令格式: free [参数] 2.命令功能: free 命令显示系统使用和空闲的内存情况,包括物理内存、交互区内存(swap)和内核缓冲区内存。 共享 内存将被忽略 3.命令参数 -b 以Byte为单位显示内存使用情况。 -k 以KB为单位显示内存使用情况。 -m 以MB为单位显示内存使用情况。 -g 以GB为单位显示内存使用情况。 -o 不显示缓冲区调节列。 -s<间隔秒数> 持续观察内存使用状况。 -t 显示内存总和列。 -V 显示版本信息。
iostat:https://www.cnblogs.com/xiuluo--angel/p/7086637.html
1.3 MySQL Explain
1.3 .1 MySQL Explain 是什么
模拟优化器执行SQL语句,从而知道mysql是如何处理定义的SQL的 分析查询语句或表结构的性能瓶颈
1.3 .2 MySQL Explain 能干什么
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引被实际使用
- 表之间的关系
- 每张表有多少行被优化器查询
1.3 .3 MySQL Explain 怎么用
explain + sql 语句
mysql> explain select * from test1;
mysql> explain select * from test1;
±—±------------±------±------±--------------±--------±--------±-----±-----±------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±------±------±--------------±--------±--------±-----±-----±------------+
| 1 | SIMPLE | test1 | index | NULL | PRIMARY | 4 | NULL | 4 | Using index |
±—±------------±------±------±--------------±--------±--------±-----±-----±------------+
1 row in set (0.00 sec)
1.3 .4 MySQL Explain 各字段解释
1.3 .5 MySQL Explain 实例
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
id:
select 查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序
三种情况:
(1) id相同 执行顺序由上至下
--test1DROP TABLE IF EXISTS `test1`;CREATE TABLE `test1` ( `id` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;--test2DROP TABLE IF EXISTS `test2`;CREATE TABLE `test2` ( `id` int(11) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;--test3 DROP TABLE IF EXISTS `test3`;CREATE TABLE `test3` ( `id` int(11) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;--执行sqlexplain select test1.* from test1,test2,test3 where test1.id =test2.id and test1.id = test3.idand test1.name ="测试"
这里可以看到 id是一样的 执行顺序就是后边table那一列的执行顺序 test1 test2 test3
(2)id不同 如果是子查询id序号会递增 id值大的优先级越高 越优先被执行
explain SELECT*FROM test1WHERE test1.id = (SELECT test2.id FROM test2 WHERE test2.id = (SELECT test3.id FROM test3))
这里可以看到 id从1到3 分别是 test1 test2 test3 所以执行顺序是 test3>test2>test1
(3)id相同和不同 同时存在
id数字大的优先级最高 id数字相等的 顺序执行 就是(1)和(2)的结合体
derived是衍生的意思 derived2 是 2的衍生 也就是id为2的表t3的衍生
奇怪:
我本地执行类似的sql 结果没有衍生这一行
explain select test2.* from (select test3.* from test3 where test3.id = 1) s1 , test2 where s1.id = test2.id
总结:id表示了 表的读取顺序
select_type:
查询类型,主要用于区别 普通查询,联合查询,子查询等的复杂查询
(1) SIMPLE 普通查询 简单的select查询 查询中不包含子查询或union
explain select * from test1,test2 where test2.id =test1.id
(2)PRIMARY 查询中若包含任何复杂的子查询 最外层查询标记为PRIMARY
(3)SUNQUERY 在select或where列表中包含的子查询
(4)DERIVED 在from列表中包含的子查询被标记为DERIVED(衍生)
MYSQL 会递归执行这些子查询,把结果放在临时表里
EXPLAIN select * from (select id from test1 union allselect id from test3) a
(5)UNION 若第二个select 出现在UNION之后 则被标记为UNION
若UNION包含在FROM 子句的子查询中,外层SELECT 将会被标记为DERIVED
EXPLAIN select id from test1union allselect id from test3
(6)UNION RESULT 从UNION表获取结果的select
EXPLAIN select test2.* from test2 union select test3.* from test3
table:
这个咱就不说了 就是指标 如果有联合查询可能会有 <union x,y>
如果有子查询 可能会有DERIVED(衍生)
type:
显示查询使用何种类型,从最好到最差依次是
工作常用:system>const>eq_ref>ref>range>index>ALL
实际:system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL
一般来说至少要达到merge级别 最好能达到ref级别
system: 表只有一行记录(等于系统表),这是const类型的特例,平时不会出现,忽略不计
const: 表示通过索引一次就能找到,const用于比较primary key 或者unique 索引,因为只匹配一行数据,所以很快,如将主键置于where 列表中 MYSQL就能将该查询转换为一个常量
eq_ref:
唯一性索引,对于每个索引键,表中只有一条记录与之匹配 常见于主键或唯一索引
-- 创建表 DROP TABLE IF EXISTS `test4`;CREATE TABLE `test4` ( `id` int(11) NOT NULL, `tt` varchar(255) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniq` (`tt`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;explain select * from test4 where test4.id in(select id from test1 )
test4 有且仅有一条数据与test1匹配
ref: 非唯一性索引扫描,返回匹配某个单独值得所有行, 本质上也是一种索引访问,它返回所有匹配某个单独值得行,然后它可能会找到多个符合条件的行,所以他属于查找和扫描的混合体
-- 创建表 age字段有索引 DROP TABLE IF EXISTS `test5`;CREATE TABLE `test5` ( `id` int(11) DEFAULT NULL, `age` int(11) DEFAULT NULL, KEY `index` (`age`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;--执行sql 12就是那个所谓的单独值explain select * from test5 where age=12
range: 只检索给定范围的行,使用一个索引来选择行。 key 列显示使用了哪个索引,
一般就是在你的where 语句中出现 between < > in 等的子查询
这种范围扫描索引比全表扫描要好,因为它只需要开始于索引的某一点,而结束于另一点,不用扫描全表
explain select * from test5 where age <12
index:
Full index scan ,index 与ALL的区别为 index 类型只遍历索引树,通常比ALL快,因为索引文件通常比数据文件小(也就是说虽然all 和index 都是读全表 但是index是从索引中读取的,而all是从硬盘读取的)
explain select id from test1
ALL 全表扫描 数据超过百万如果还有all 必须优化
explain select * from test1
possible_keys | key | key_len | ref | rows | Extra 明日再叙~ sleep!