mysql6
mysql性能优化
总论
调优金字塔
- 硬件和os调优
- 硬件用几级的磁盘阵列(从0到10),一般来说用RAID 5
- 当使用多磁盘后,能不能把mysql的数据文件分散到不同的磁盘上
- 能不能用裸设备存放mysql文件
- 裸设备:又叫裸分区,没有经过格式化,无法通过操作系统文件系统读取,而是直接由应用程序读取
- 文件系统用什么?
- 在windows下面用ntfx
- 在linux下面又ext3、ext4
- 比较适合mysql的有xfs
- 操作系统的磁盘调度算法
- 在linux环境下用deadline,在linux2.68版本后默认CFQ算法,但是CFQ算法不利于mysql的性能,真正优秀的dba会改成deadline算法的
- mysql调优
- 架构调优
变化趋势
- 从下往上成本越来越高,从上往下效果越来越好
- 1.所以最先开始应该考虑的是架构调优,如果架构设计的不好,dba能够做的优化也很有限
- 有些业务类型mysql并不是适合,可以考虑放到大数据仓库,或者搜索引擎、或者缓存去做
- 如果写并发量多大,考虑分布式,如果读压力大,考虑读写分离
- 所以架构调优作为调优金字塔的底部,在上线后,无论是sql语句、硬件、还是各种参数定义方面,单台mysql服务器能够提供的扩展性基本定型了,性能很难再去提升了,但是如果架构设计的好,性能可以有几百倍,乃至几千倍的提高
- 2.对于mysql调优
- 数据表设计是否合理
- sql语句优化是否足够,该添加的索引是否都添加了
- 3.对于硬件和os调优
慢查询
慢查询定义及作用
- 慢查询日志,顾名思义,就是查询慢的日志,是指mysql记录所有执行超过long_query_time参数设定的时间阈值的SQL语句的日志。该日志能为SQL语句的优化带来很好的帮助。默认情况下,慢查询日志是关闭的,要使用慢查询日志功能,首先要开启慢查询日志功能。
慢查询基础-优化数据访问
- 大部分查询慢的sql都是查询数据太多,想办法减少查询数量
请求了不需要的数据?
- 查询不需要的记录
- 比如查询了1000条,但是业务中只使用到了10条,其余990条都是白查的
- limit 10000,20,此时就是查询了10020列,然后不要前10000条,只取20条,白扫描了10000条
- 总是取出全部列
- 最常见的就是select *,即使建立了二级索引,只要查询不是走主键,就一定要回表的, 回表是一个典型的随机io操作,性能很差,但是并不是绝对不能用select *,比如提前查出所有的数据,然后放到缓存中,这种是为了提高查询性能,是可以的
- 重复查询相同的数据
- 比如查询部门表,在一个业务中反复查,既然要反复查,而且部分表本身就是不常变的内容,为什么不把它缓存起来?
是否在扫描额外的记录?
- 导致响应时间增长
- 你写的sql语句本意只查1000条语句,但是sql写的不好,导致必须扫描10w条数据才能找出这1000条来,而在扫描这10w条的数据的时候,有可能会出现磁盘的io,同时也有可能锁表,这些最后就反应成响应时间的增长
- 扫描的行数和返回的行数
- 扫描的行数很多,但是结果就返回几行数据
- 特别是在关联查询的时候,mysql需要扫描很多行,才能形成返回记录
- 发现扫描的行数和返回的行数相差很大时,一般是使用覆盖索引,二可以改变库表结构,建立单独的汇总表,第三个可以重写这条查询语句
- 扫描的行数和访问类型
- 同样是查找数据,使用不同的索引会产生不同的结果,有的索引需要回表,而覆盖索引和主键索引可以直接返回数据
带索引的查询
- select * from where a>xx
- 不同的where条件情况
- 1.在存储引擎层就能通过where条件过滤掉不匹配的记录,这样的where条件是最好的
- 类比之前提到的三星索引,就是希望在存储引擎层就能使扫描行数越少越好,把不需要扫描的行都过滤掉
- 2.where条件使用覆盖索引是第二好的,不用回表,这是在servcice层做的
- Using index表示使用了覆盖索引
- 3.最坏的是需要回表的,回表后从聚簇索引中找出符合的数据,再过滤where条件,此时多了一步回表的过程,
- Using where表示进行了回表
- 1.在存储引擎层就能通过where条件过滤掉不匹配的记录,这样的where条件是最好的
重构查询的方式
一个复杂查询还是多个简单查询?
- 一个查询很慢,多半情况下是这个查询很复杂,能不能把这个复杂查询改成多个简单查询
切分查询
- 限制返回的数据量,总共查询可以返回10w条数据,分多次返回,每次只返回1000
分解关联查询?
-
分解关联查询并不是说数据库中不能有关联查询,需要根据查询性能而定,如果性能还可以没必要分解
-
比如把5个关联查询分解成2个关联查询或者3个关联查询
怎么分解关联查询
- 让缓存的效率更高
- 比如某个关联只是关联一个部门名称,这种数据量不多并且不经常变化的量,把这些数据缓存起来
- 可以减少锁的竞争
- 把一个关联查询分解以后还可以减少锁的竞争,因为当一个关联查询持续的时间很久,持有锁的时间也会变得很长
- 更容易做到高性能和可扩展
- 将关联查询拆分成单表后,也更容易做扩展,像分库分表
- 减少冗余记录的查询
- 相当于在应用中实现了哈希关联
慢查询配置
-
// 观察慢查询有没有开启 show VARIABLES like 'slow_query_log'; // 启动慢查询日志 set GLOBAL slow_query_log=1; // 慢查询阈值,默认是10秒 show VARIABLES like '%long_query_time%' // sql语句没有使用索引的都会记录在慢查询日志里面 show VARIABLES like '%log_queries_not_using_indexes%' // 慢查询记录位置,默认是输出到文件 set global log_output='FILE,TABLE'
总结
-
slow_query_log 启动停止技术慢查询日志 slow_query_log_file 指定慢查询日志得存储路径及文件(默认和数据文件放一起) long_query_time 指定记录慢查询日志SQL执行时间得伐值(单位:秒,默认10秒) log_queries_not_using_indexes 是否记录未使用索引的SQL log_output 日志存放的地方【TABLE】【FILE】【FILE,TABLE】
启动慢查询
慢查询日志观察
- mysql查询日志存放位置:
- /home/mysql/data/主机名-slow.log
- 直接查看日志中的数据很不方便,mysql提供了一个工具来查看
- /home/mysql/bin/mysqldumpslow
- mysqldumpslow专门用来帮我们对慢查询日志进行分析的
慢查询解读分析
-
“Time: 2021-04-05T07:50:53.243703Z”:查询执行时间 “User@Host: root[root] @ localhost [] Id: 3”:用户名 、用户的IP信息、线程ID号 “Query_time: 0.000495”:执行花费的时长【单位:毫秒】 “Lock_time: 0.000170”:执行获得锁的时长 “Rows_sent”:获得的结果行数 “Rows_examined”:扫描的数据行数 “SET timestamp”:这SQL执行的具体时间 最后一行:执行的SQL语句
mysqldumpslow
-
mysqldumpslow --help
- 查看命令解释
-
mysqldumpslow -s 是进行排序
-
mysqldumpslow -t 是限制查询结果的条数
-
mysqldumpslow -g是通过grep来删选语句
-
综合使用
- mysqldumpslow -s t -t 10 /home/mysql57/data/主机名-slow.log
- 对慢查询日志中花费时间最多的前10条进行排序并显示
- mysqldumpslow -s t -t 10 /home/mysql57/data/主机名-slow.log -g select
- 对上面的进一步过滤,只显示select相关的
- mysqldumpslow -s t -t 10 /home/mysql57/data/主机名-slow.log
pt_query_digest
- mysql高性能优化第三版中提到的一个工具
- 是用python编写的
- 分析结果要比mysqldumpslow更全
Explain执行计划
什么是执行计划
执行计划的语法
-
在SQL查询的前面加上EXPLAIN关键字就行。比如:EXPLAIN select * from order_exp;
-
id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE order_exp NULL ALL NULL NULL NULL NULL NULL 100.00 NULL
-
table
- EXPLAIN语句输出的每条记录都对应着某个单表的访问方法,该条记录的table列代表着该表的表名。
id
-
查询语句中每出现一个SELECT关键字,MySQL就会为它分配一个唯一的id值。这个id值就是EXPLAIN语句的第一个列
-
EXPLAIN SELECT * FROM s1 WHERE order_no = ‘a’;
- id = 1
-
EXPLAIN SELECT * FROM s1 INNER JOIN s2;
- 两个表的id都等于1
-
EXPLAIN SELECT * FROM s1 WHERE id IN (SELECT id FROM s2) OR order_no = ‘a’;
-
这里的子查询转换成了关联查询–通过命令show warnings\G可以观察到mysql优化器优化后的sql
-
s1表的id等于1,s2表的id等于2
-
-
EXPLAIN SELECT * FROM s1 WHERE id IN (SELECT id FROM s2 WHERE order_no = ‘a’);
-
子查询不算额外的
-
两个表的id都等于1
-
-
EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
- s1表的id等于1,s2表的id等于2,还多了一个id为null的行,这里会创建一个临时表,用来合并两个表的结果的
-
EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;
- s1表的id等于1,s2表的id等于2,用union all,就不会有临时表了,因为union all不用去重,直接合并就可以了
-
select_type
-
通过某个小查询的select_type属性,就知道了这个小查询在整个大查询中扮演了一个什么角色
- 上面id中有两个不同id的查询,他们的select_type也是不一样的,select_type能体现两者之间的关联关系
-
SIMPLE:简单的select 查询,不使用 union 及子查询,即使是关联查询,也是两个id都等于1,同时select_type都是SIMPLE PRIMARY:最外层的 select 查询,union查询中,最外面的查询称为primary UNION:UNION 中的第二个或随后的 select 查询,不 依赖于外部查询的结果集 UNION RESULT:UNION 结果集,union查询中用于去重的临时表 SUBQUERY:子查询中的第一个 select 查询,不相关的子查询,不依赖于外部查询的结果集,不能semi-join(semi-join:半连接的优化技术,本质上是一个查询上拉,把子查询的查询条件上拉到父查询里面),使用物化表的方式来执行这个查询时,这个子查询前面出现的就是SUBQUERY(物化表:物化的含义指的是mysql在执行这个查询的时候,发现可能子查询的内容有点大,需要把子查询的内容缓存一下,缓存在内存或者临时表里面) DEPENDENT UNION:UNION 中的第二个或随后的 select 查询,依赖于外部查询的结果集 DEPENDENT SUBQUERY:子查询中的第一个 select 查询,依赖于外部查询的结果集 DERIVED:用于 from 子句里有子查询的情况。 MySQL 会 递归执行这些子查询, 把结果放在临时表里。DERIVED是对于派生表而言的,是对派生表的物化,并不是实际存在的,mysql会把这张表物化 MATERIALIZED:物化子查询,MATERIALIZED是对子查询表的物化 UNCACHEABLE SUBQUERY:结果集不能被缓存的子查询,必须重新为外层查询的每一行进行评估,出现极少。(全局变量是不能被缓存的) UNCACHEABLE UNION:UNION 中的第二个或随后的select 查询,属于不可缓存的子查询,出现极少。
partitions
- 和分区表有关,一般情况下我们的查询语句的执行计划的partitions列的值都是NULL
- 如果没有分区的话,就是null
type
-
explain后很重要的观察指标
-
执行计划的一条记录就代表着MySQL对某个表的执行查询时的访问方法/访问类型,其中的type列就表明了这个访问方法/访问类型是个什么东西,是较为重要的一个指标,结果值从最好到最坏依次是:
-
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
-
出现比较多的是system>const>eq_ref>ref>range>index>ALL
-
一般来说,得保证查询至少达到range级别,最好能达到ref。
system
- 表只有一行记录(等于系统表),这是const类型的特列,平时不会出现,这个也可以忽略不计。
- explain select * from test
- 因为innodb引擎里面统计数据是不精确的,而myisam和memory这张存储引擎它的统计数据是精确的
- explain select * from test
const
-
根据主键或者唯一二级索引列与常数进行等值匹配时
- EXPLAIN SELECT * FROM s1 WHERE id = 716;
-
由于是等值匹配,算上二级索引查到主键索引回表的时间,就是两个常量时间,其结果仍然是常量时间
-
对于联合索引,只要每个索引列都有一个等值匹配,这样的实现也是const级别的,但是当有一个字段是允许null时,是达不到const级别的,
- 因为mysql在存储时,认为每一个null都是独一无二的,在每一个b+树的叶子节点里面,前面的记录全部用来存null值,null值存完了再开始存非null值的
- 当索引里面有null值,由于无法确定null值有多少个,所以就达不到const级别,所以尽量不要把列定义为允许null值,这样不光是在数据存储(优先存储在最前面),还是索引扫描(不能达到const级别),null值都会引入很多的麻烦
- 为什么要把null定义为独一无二的?mysql对null值有三种定义,第一种是独一无二的,第二种认为null值都是相等的,第三种是完全忽略null值,所以mysql本身对null值的处理就很分裂