网站创建网站,宿迁专业网站三合一建设,做的好的网站开发,大型销售网站建设总结
获取慢查询SQL
已经执行完的SQL#xff0c;检查慢查询日志#xff0c;日志中有执行慢的SQL正在执行中的SQL#xff0c;show proccesslist;#xff0c;结果中有执行慢的SQL
慢查询日志关键参数
名称解释Query_time查询消耗时间Time慢查询发生时间
分析慢查询SQL e…总结
获取慢查询SQL
已经执行完的SQL检查慢查询日志日志中有执行慢的SQL正在执行中的SQLshow proccesslist;结果中有执行慢的SQL
慢查询日志关键参数
名称解释Query_time查询消耗时间Time慢查询发生时间
分析慢查询SQL explain 慢SQL
explain关键参数
名称解释key实际用到的索引列type索引类型extra额外信息
type部分值
名称解释consts基于主键或唯一索引查询最多返回一条数据优化阶段可得到数据ref基于普通索引的等值查询表间等值连接range利用索引范围查询index全索引扫描ALL全表操作
阿里java开发手册-泰山版要求至少range
Extra部分值
名称解释Using index使用覆盖索引减少表扫描和回表Using index condition先条件过滤索引再查询数据Using filesort使用外部排序非索引排序Using where使用where条件Impossible wherewhere总是falseUsing temporary使用临时表一般发生在order by无索引列时Using join buffer (Block Nested Loop)在进行嵌套循环连接内表大Select tables optimized away该查询不需要访问实际的表而是通过优化方式直接计算出结果
优化慢SQL
准备数据库
库
CREATE DATABASE IF NOT EXISTS test_slow DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;
use test_slow;表
CREATE TABLE person_info_large ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, code VARCHAR (36),name VARCHAR (36),title VARCHAR (72),location VARCHAR (108),PRIMARY KEY pk_id (id),UNIQUE uk_code (code),KEY idx_title_location(title,location)
) ENGINE INNODB AUTO_INCREMENT 1 DEFAULT CHARSET utf8行java生成sql文件》导入sql文件
package com.xcrj.gen;import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.UUID;public class Main {public static void main(String[] args) {generate(300 * 10000);}private static String rand36Str() {long time System.currentTimeMillis();int random (int) (Math.random() * Integer.MAX_VALUE);UUID uuid new UUID(time, random);//随机种子return uuid.toString();}private static String rand36Str(int num) {StringBuilder sb new StringBuilder();UUID uuid;for (int i 0; i num; i) {uuid UUID.randomUUID();sb.append(uuid.toString());}return sb.toString();}private static void generate(int size) {String row INSERT INTO researcher(code,name,title,location) VALUE(%s);;
// System.out.println(String.format(sql,IdWorker.getId()));String path ./test_slow_researcher.sql;File file new File(path);if (!file.exists()) {try {file.createNewFile();} catch (IOException e) {e.printStackTrace();}}try (FileOutputStream fos new FileOutputStream(path);BufferedOutputStream bos new BufferedOutputStream(fos);) {for (int i 0; i size; i) {StringBuilder sb new StringBuilder();String code rand36Str(1);String name rand36Str(1);String title rand36Str(2);String location rand36Str(3);sb.append().append(code).append().append(,).append().append(name).append().append(,).append().append(title).append().append(,).append().append(location).append();bos.write(String.format(row, sb.toString()).getBytes());if (i size - 1) {bos.write(\n.getBytes());}}} catch (IOException e) {e.printStackTrace();}}
}开启慢查询日志
# 检查默认值
show variables like %quer%;# 开启慢查询日志
set global slow_query_logon;
# 设置慢查询阈值为1s
set global long_query_time1;
# 查看慢查询日志路径
show global variables like slow_query_log_file# 检查设置值若发现未生效关闭当前会话关闭数据库重新打开再检查
show variables like %quer%;参数含义slow_query_log是否开启慢查询日志slow_query_log_file慢查询日志路径long_query_time慢查询阈值默认10s
慢查询测试1已经执行完的慢查询
# 统计
SELECT count(*) FROM researcher;
# 无索引列
SELECT name FROM researcher ORDER BY name DESC;
# 有索引列
SELECT code FROM researcher ORDER BY code DESC;
# 查询慢查询日志文件地址 /var/lib/mysql/333a2bf4a87e-slow.log
show variables like %quer%;
# 查看慢查询日志
more /var/lib/mysql/333a2bf4a87e-slow.log
# 分析SQL
explain SELECT count(*) FROM researcher;
explain SELECT name FROM researcher ORDER BY name DESC;
explain SELECT code FROM researcher ORDER BY code DESC;SELECT count(*) FROM researcher;
Query_time: 大于18s
SELECT nameFROM researcher ORDER BYname DESC;
Query_time: 大于19s
SELECT codeFROM researcher ORDER BYcode DESC;
Query_time: 大于16s
explain SELECT count(*) FROM researcher;
keyuk_code可知 count(*) 会使用索引typeindexextraUsing index
explain SELECT nameFROM researcher ORDER BYname DESC;
keyNULLtypeALLextraUsing filesort
explain SELECT codeFROM researcher ORDER BYcode DESC;
keyuk_codetypeindexextraUsing index
explain SELECT idFROM researcher ORDER BYid DESC;
keyprimarytypeindexextraUsing index
参数说明
慢查询日志部分参数
名称解释Query_time查询消耗时间Time慢查询发生时间
explain部分值
名称解释key实际用到的索引列type索引类型extra额外信息select_type查询方式possible_keys可能用到的索引列
type部分值
名称解释consts基于主键或唯一索引查询最多返回一条数据优化阶段可得到数据ref基于普通索引的等值查询表间等值连接range利用索引范围查询index全索引扫描ALL全表操作
阿里java开发手册-泰山版要求至少range
Extra部分值
名称解释Using index使用覆盖索引减少表扫描和回表Using index condition先条件过滤索引再查询数据Using filesort使用外部排序非索引排序Using where使用where条件Impossible wherewhere总是falseUsing temporary使用临时表一般发生在order by无索引列时Using join buffer (Block Nested Loop)在进行嵌套循环连接内表大Select tables optimized away该查询不需要访问实际的表而是通过优化方式直接计算出结果
select_type部分值
名称解释Simple简单查询Primary关联查询或子查询的外层查询Unoin关联查询或子查询的后续查询
慢查询测试2正在执行的慢查询
SELECT name FROM researcher ORDER BY name DESC;
show processlist;Time34已经执行了34s
恢复默认参数
# 检查默认值
show variables like %quer%;
# 开启慢查询日志
set global slow_query_logoff;
# 设置慢查询阈值为1s
set global long_query_time10;
# 检查设置值若发现未生效关闭当前会话关闭数据库重新打开再检查
show variables like %quer%;
# 重置表包括自增ID
TRUNCATE TABLE researcher;
