怎么写网站规划方案,天猫网上购物商城,wordpress做小说网站,展示型网站方案1. 慢 SQL 优化思路
慢查询日志记录慢 SQLexplain 分析 SQL 的执行计划profile 分析执行耗时Optimizer Trace 分析详情确定问题并采用相应的措施
1. 慢查询日志记录慢 SQL 如何定位慢SQL呢#xff1f; 我们可以通过 慢查询日志 来查看慢 SQL。
①#xff1a;开启慢查询日志…1. 慢 SQL 优化思路
慢查询日志记录慢 SQLexplain 分析 SQL 的执行计划profile 分析执行耗时Optimizer Trace 分析详情确定问题并采用相应的措施
1. 慢查询日志记录慢 SQL 如何定位慢SQL呢 我们可以通过 慢查询日志 来查看慢 SQL。
①开启慢查询日志
SET global slow_query_log ON;设置慢查询开启的状态ON开启OFF关闭slow_query_log_file设置慢查询日志存放的位置SET global log_queries_not_using_indexes ON;记录没有使用索引的查询 SQL。前提是slow_query_log 的值为 ON否则不会奏效SET long_query_time 10;设置慢查询的阀值单位秒。如果SQL执行时间超过阀值就属于慢查询 记录到日志文件中
②查看慢查询日志配置
show variables like slow_query_log%show variables like long_query_time
③慢查询日志分析工具
mysqldumpslow该工具是慢查询自带的分析慢查询工具一般只要安装了mysql就会有该工具
# 取出使用最多的10条慢查询
mysqldumpslow -s c -t 10 /var/run/mysqld/mysqld-slow.log
# 取出查询时间最慢的3条慢查询
mysqldumpslow -s t -t 3 /var/run/mysqld/mysqld-slow.log
# 得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow-log
# 按照扫描行数最多的
mysqldumpslow -s r -t 10 -g left join /var/run/mysqld/mysqld-slow.log 注意: 使用 mysqldumpslow 的分析结果不会显示具体完整的sql语句,只会显示sql的组成结构;
假如: SELECT * FROM sms_send WHERE service_id10 GROUP BY content LIMIT 0, 1000; Count: 1 Time1.91s (1s) Lock0.00s (0s) Rows1000.0 (1000), vgos_dba[vgos_dba][10.130.229.196] SELECT * FROM sms_send WHERE service_idN GROUP BY content LIMIT N, N; 工具其实还有很多并不限制只有这一种还有 pt-query-digest、mysqlsla 等这些都是可以定位慢查询日志的小工具
慢查询原因
全表扫描explain分析type属性all全索引扫描explain分析type属性index索引过滤性不好靠索引字段选型、数据量和状态、表设计频繁的回表查询开销尽量少用select *使用覆盖索引
转详解 慢查询 之 mysqldumpslow
2. explain 查看分析 SQL 的执行计划
当定位出查询效率低的 SQL 后可以使用 explain 查看 SQL 的执行计划。
当 explain 与 SQL 一起使用时MySQL 将显示来自优化器的有关语句执行计划的信息。即MySQL 解释了它将如何处理该语句包括有关如何连接表以及以何种顺序连接表等信息 一般来说我们需要重点关注 type、key、rows、extra
13.1 type
type 表示连接类型查看索引执行情况的一个重要指标。以下性能从好到坏依次system const eq_ref ref ref_or_null index_merge unique_subquery index_subquery range index ALL
NULL表示不用访问表速度最快system这种类型要求数据库表中只有一条数据是 const 类型的一个特例一般情况下是不会出现的const通过一次索引就能找到数据一般用于主键或唯一索引作为条件这类扫描效率极高速度非常快eq_ref常用于主键或唯一索引扫描一般指使用主键的关联查询ref : 常用于非主键和唯一索引扫描ref_or_null这种连接类型类似于 ref区别在于 MySQL 会额外搜索包含 NULL 值的行index_merge使用了索引合并优化方法查询使用了两个以上的索引unique_subquery类似于 eq_ref条件用了 in 子查询index_subquery区别于 unique_subquery用于非唯一索引可以返回重复值range常用于范围查询比如between … and 或 In 等操作index全索引扫描all全表扫描
13.2 possible_keys
表示查询时能够使用到的索引显示的是索引名称只是可能用到的索引而不是实际上用到的索引
13.3 key
该列表示实际用到的索引。一般配合 possible_keys 列一起看
13.4 rows
MySQL查询优化器会根据统计信息估算 SQL 要查询到结果需要扫描多少行记录。原则上 rows 是越少效率越高可以直观的了解到SQL效率高低
13.5 extra
该字段包含有关 MySQL 如何解析查询的其他信息它一般会出现这几个值
Using filesort表示按文件排序一般是在指定的排序和索引排序不一致的情况才会出现。一般见于 order by 语句。建议优化Using temporary: 表示使用了临时表性能特别差需要重点优化。一般多见于 group by 语句或者 union 语句Using index 表示用了覆盖索引Using where : 表示使用了 where 条件过滤需要通过索引回表查询数据Using index conditionMySQL5.6 之后新增的索引下推。在存储引擎层进行数据过滤而不是在服务层过滤利用索引现有的数据减少回表的数据NULL查询的列未被索引覆盖
总结
extrawhere 条件select 的字段nullwhere 筛选条件是索引的前导列查询的列未被索引覆盖Using indexwhere 筛选条件是索引的前导列查询的列被索引覆盖Using where; Using indexwhere 筛选条件是索引列之一但不是前导列或者where筛选条件是索引列前导列的一个范围查询的列被索引覆盖Using where;where 筛选条件不是索引列-Using where;where 筛选条件不是索引前导列、是索引列前导列的一个范围()查询列未被索引覆盖Using index conditionwhere 索引列前导列的一个范围、between查询列未被索引覆盖
两种排序的情况
extra出现场景Using filesortfilesort主要用于查询数据结果集的排序操作首先MySQL会使用sort_buffer_size大小的内存进行排序如果结果集超过了sort_buffer_size大小会把这一个排序后的chunk转移到file上最后使用多路归并排序完成所有数据的排序操作。Using temporaryMySQL使用临时表保存临时的结构以用于后续的处理MySQL首先创建heap引擎的临时表如果临时的数据过多超过max_heap_table_size的大小会自动把临时表转换成MyISAM引擎的表来使用。 filesort 只能应用在单个表上如果有多个表的数据需要排序那么MySQL会先使用using temporary保存临时数据然后再在临时表上使用filesort进行排序最后输出结果 13.6 select_type
select_type表示查询的类型。
常用的值如下
SIMPLE 表示查询语句不包含子查询或 UNIONPRIMARY表示此查询是最外层的查询UNION表示此查询是 UNION 的第二个或后续的查询DEPENDENT UNIONUNION 中的第二个或后续的查询语句使用了外面查询结果UNION RESULTUNION 的结果SUBQUERYSELECT 子查询语句DEPENDENT SUBQUERYSELECT子查询语句依赖外层查询的结果
最常见的查询类型是 SIMPLE表示我们的查询没有子查询也没用到 UNION 查询
13.7 filtered
该列是一个百分比的值通过查询条件最终查询记录行数和通过 type 字段扫描记录行数的百分比。简单点说这个字段表示存储引擎返回的数据在经过过滤后剩下满足条件的记录数量的比例
13.8 key_len
表示查询使用了索引的字节数量可以判断是否全部使用了组合索引
key_len的计算规则如下
字符串类型字符串长度跟字符集有关latin1 1、gbk 2、utf8 3、utf8mb4 4 char(n)n * 字符集长度varchar(n)n * 字符集长度 2字节 数值类型 TINYINT1个字节SMALLINT2个字节MEDIUMINT3个字节INT、FLOAT4个字节BIGINT、DOUBLE8个字节 时间类型 DATE3个字节TIMESTAMP4个字节DATETIME8个字节 字段属性 NULL 属性占用1个字节如果一个字段设置了 NOT NULL则没有此项
3. profile 分析执行耗时
explain 只是看到 SQL 的预估执行计划如果要了解 SQL 真正的执行线程状态及消耗的时间需要使用 profiling
开启 profiling 参数后后续执行的 SQL 语句都会记录其资源开销包括 IO上下文切换CPU内存等等我们可以根据这些开销进一步分析当前慢 SQL 的瓶颈再进一步进行优化
查看是否开启 profiling
show variables like %profil%开启 profiling
set profilingON使用 profiling
show profilesshow profiles 会显示最近发给服务器的多条语句条数由变量 profiling_history_size 定义默认是 15。如果我们需要看单独某条 SQL 的分析可以 show profile 查看最近一条 SQL 的分析也可以使用 show profile for query id其中id就是show profiles中的 QUERY_ID查看具体一条的 SQL 语句分析 4. Optimizer Trace 分析详情
profile 只能查看到 SQL 的执行耗时但是无法看到 SQL 真正执行的过程信息即不知道 MySQL 优化器是如何选择执行计划。这时候我们可以使用 Optimizer Trace它可以跟踪执行语句的解析优化执行的全过程
开启
set optimizer_traceenabledon;查看分析其执行树会包括三个阶段
join_preparation准备阶段join_optimization分析阶段join_execution执行阶段 5. 确定问题并采用相应的措施
确认问题就采取对应的措施。
多数慢 SQL 都跟索引有关比如不加索引索引不生效、不合理等这时候我们可以优化索引我们还可以优化 SQL 语句比如一些in元素过多问题分批深分页问题基于上一次数据过滤等进行时间分段查询SQL 没办法很好优化可以改用 ES 的方式或者数仓如果单表数据量过大导致慢查询则可以考虑分库分表如果数据库在刷脏页导致慢查询考虑是否可以优化一些参数跟 DBA 讨论优化方案如果存量数据量太大考虑是否可以让部分数据归档
