有效果的网站排名,wordpress 首页尾页,大淘客网站上的推广怎么做,长沙网站建设王道下拉惠1. 统计SQL的查询成本#xff1a;last_query_cost SHOW STATUS LIKE last_query_cost; 使用场景#xff1a;它对于比较开销是非常有用的#xff0c;特别是我们有好几种查询方式可选的时候。 SQL 查询是一个动态的过程#xff0c;从页加载的角度来看#xff0c;我们可以得到… 1. 统计SQL的查询成本last_query_cost SHOW STATUS LIKE last_query_cost; 使用场景它对于比较开销是非常有用的特别是我们有好几种查询方式可选的时候。 SQL 查询是一个动态的过程从页加载的角度来看我们可以得到以下两点结论 位置决定效率。如果页就在数据库缓冲池中那么效率是最高的否则还需要从内存或者磁盘中进行读取当然针对单个页的读取来说如果页存在于内存中会比在磁盘中读取效率高很多。 批量决定效率。如果我们从磁盘中对单一页进行随机读那么效率是很低的差不多10ms而采用顺序读取的方式批量对页进行读取平均一页的读取效率就会提升很多甚至要快于单个页面在内存中的随机读取。 所以说遇到I/O并不用担心方法找对了效率还是很高的。我们首先要考虑数据存放的位置如果是经常使用的数据就要尽量放到缓冲池中其次我们可以充分利用磁盘的吞吐能力一次性批量读取数据这样单个页的读取效率也就得到了提升。 2. 定位执行慢的SQL慢查询日志 MySQL的慢查询日志用来记录在MySQL中响应时间超过阈值的语句具体指运行时间超过long_query_time的值的SQL则会被记录到慢查询日志中。long_query_time的默认值为10意思是运行10秒以上不含10秒的语句认为是超出了我们的最大忍耐时间值。 默认情况下MySQL数据库没有开启慢查询日志需要我们手动来设置这个参数。如果不是调优需要的话一般不建议启动该参数因为开启慢查询日志会或多或少带来一定的性能影响。 2.1 开启慢查询日志参数 1. 开启slow_query_log set global slow_query_logON; 查看下慢查询日志是否开启以及慢查询日志文件的位置 show variables like %slow_query_log% 2. 修改long_query_time阈值 show variables like %long_query_time%; 测试发现设置global的方式对当前session的long_query_time失效。对新连接的客户端有效。所以可以一并 执行下述语句 mysql set global long_query_time 1;
mysql show global variables like %long_query_time%; mysql set long_query_time1;
mysql show variables like %long_query_time%; 2.2 查看慢查询数目 SHOW GLOBAL STATUS LIKE %Slow_queries%;2.3 慢查询日志分析工具mysqldumpslow #得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log
#得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log
#得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g left join /var/lib/mysql/atguigu-slow.log
#另外建议在使用这些命令时结合 | 和more 使用 否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more2.4 关闭慢查询日志 方式1永久性方式 [mysqld]
slow_query_logOFF
#或
[mysqld]
#slow_query_log OFF方式2临时性方式 SET GLOBAL slow_query_logoff;3. 查看 SQL 执行成本SHOW PROFILE show variables like profiling;
#开启
set profiling ON;
#查看
show profiles;
show profile cpu,block io for query 2;4. 分析查询语句EXPLAIN 4.1 基本语法 EXPLAIN SELECT select_options
#或者
DESCRIBE SELECT select_optionsEXPLAIN 语句输出的各个列的作用如下 列名 描述 id 在一个大的查询语句中每个SELECT关键字都对应一个唯一的id select_type SELECT关键字对应的那个查询的类型 table 表名 partitions 匹配的分区信息 type 针对单表的访问方法 possible_keys 可能用到的索引 key 实际上使用的索引 key_len 实际使用到的索引长度 ref 当使用索引列等值查询时与索引列进行等值匹配的对象信息 rows 预估的需要读取的记录条数 filtered 某个表经过搜索条件过滤后剩余记录条数的百分比 Extra 一些额外的信息 4.2 EXPLAIN各列作用 1. table 不论我们的查询语句有多复杂包含了多少个表 到最后也是需要对每个表进行单表访问的所以MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法该条记录的table列代表着该表的表名有时不是真实的表名字可能是简称。 2. id id如果相同可以认为是一组从上往下顺序执行 在所有组中id值越大优先级越高越先执行 关注点id号每个号码表示一趟独立的查询,一个sql的查询趟数越少越好 3. select_type 4. partitions 5. type重点 结果值从最好到最坏依次是 system const eq_ref ref fulltext ref_or_null index_merge unique_subquery index_subquery range index ALL SQL性能优化的目标至少要达到 range级别要求是ref级别最好是consts级别。 6. possible_keys和key 7. key_len重点 key_len的长度计算公式
varchar(10)变长字段且允许NULL 10 * ( character set utf83,gbk2,latin11)1(NULL)2(变长字段)
varchar(10)变长字段且不允许NULL 10 * ( character setutf83,gbk2,latin11)2(变长字段)
char(10)固定字段且允许NULL 10 * ( character setutf83,gbk2,latin11)1(NULL)
char(10)固定字段且不允许NULL 10 * ( character setutf83,gbk2,latin11) 8. ref 9. rows重点 预估的需要读取的记录条数 10. filtered 11. Extra 5. EXPLAIN的进一步使用 5.1 EXPLAIN四种输出格式 这里谈谈EXPLAIN的输出格式。EXPLAIN可以输出四种格式传统格式JSON格式TREE格式以及可视化输出。用户可以根据需要选择适用于自己的格式。 1. 传统格式 2. JSON格式 JSON格式在EXPLAIN单词和真正的查询语句中间加上FORMATJSON。用于查看执行成本cost_info 3. TREE格式 TREE格式是8.0.16版本之后引入的新格式主要根据查询的各个部分之间的关系和各部分的执行顺序来描述如何查询。 4. 可视化输出 可视化输出可以通过MySQL Workbench可视化查看MySQL的执行计划。 5.2 SHOW WARNINGS的使用 mysql EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 s2.key1 WHERE s2.common_field IS NOT NULL;
# 查看优化后的执行语句
mysql SHOW WARNINGS\G 6. 分析优化器执行计划trace # 开启
SET optimizer_traceenabledon,end_markers_in_jsonon;
# 设置大小
set optimizer_trace_max_mem_size1000000;
# 使用
select * from student where id 10;
select * from information_schema.optimizer_trace\G 7. MySQL监控分析视图-sys schema 7.1 Sys schema视图使用场景 索引情况 #1. 查询冗余索引
select * from sys.schema_redundant_indexes;
#2. 查询未使用过的索引
select * from sys.schema_unused_indexes;
#3. 查询索引的使用情况
select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted from sys.schema_index_statistics where table_schemadbname ;表相关 # 1. 查询表的访问量
select table_schema,table_name,sum(io_read_requestsio_write_requests) as io from sys.schema_table_statistics group by table_schema,table_name order by io desc;
# 2. 查询占用bufferpool较多的表
select object_schema,object_name,allocated,data
from sys.innodb_buffer_stats_by_table order by allocated limit 10;
# 3. 查看表的全表扫描情况
select * from sys.statements_with_full_table_scans where dbdbname;语句相关 #1. 监控SQL执行的频率
select db,exec_count,query from sys.statement_analysis order by exec_count desc;
#2. 监控使用了排序的SQL
select db,exec_count,first_seen,last_seen,query
from sys.statements_with_sorting limit 1;
#3. 监控使用了临时表或者磁盘临时表的SQL
select db,exec_count,tmp_tables,tmp_disk_tables,query
from sys.statement_analysis where tmp_tables0 or tmp_disk_tables 0 order by (tmp_tablestmp_disk_tables) desc;IO相关 #1. 查看消耗磁盘IO的文件
select file,avg_read,avg_write,avg_readavg_write as avg_io
from sys.io_global_by_file_by_bytes order by avg_read limit 10; Innodb 相关 #1. 行锁阻塞情况
select * from sys.innodb_lock_waits;
