排序优化
- 尽量避免使用Using FileSort方式排序。
- order by语句使用索引最左前列或使用where子句与order by子句条件组合满足索引最左前列。
- where子句中如果出现索引范围查询会导致order by索引失效。
单表查询优化
内连接时,mysql会自动把小结果集的选为驱动表,所以大表的字段最好加上索引。左外连接时,左表会全表扫描,所以右边大表字段最好加上索引,右外连接同理。我们最好保证被驱动表上的字段建立了索引。
-- 单表查询优化
CREATE TABLE IF NOT EXISTS article (
id INT(10) PRIMARY KEY AUTO_INCREMENT,
author_id INT(10) NOT NULL,
category_id INT(10) NOT NULL,
views INT(10) NOT NULL,
comments INT(10) NOT NULL,
title VARBINARY(255) NOT NULL,
content TEXT NOT NULL
);
INSERT INTO article(author_id, category_id, views, comments, title, content) VALUES
(1, 1, 1, 1, '1', '1'),
(2, 2, 2, 2, '2', '2'),
(1, 1, 3, 3, '3', '3');
#1.查询category_id为1的,且comments大于1的情况下,views最多的id和author_id的信息
explain select id,author_id
from article
where category_id=1 and comments>1 order by views desc limit 1;
#2.建立索引
alter table article add index idx_ccv(category_id,comments,views);
#3.再次测试
explain select id,author_id
from article
where category_id=1 and comments>1 order by views desc limit 1;
#4.重新创建索引
drop index idx_ccv on article;
alter table article add index idx_cv(category_id,views);
#5.再次测试
explain select id,author_id
from article
where category_id=1 and comments>1 order by views desc limit 1;
联表查询优化
CREATE TABLE IF NOT EXISTS class (
id INT(10) AUTO_INCREMENT,
card INT(10),
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS book (
bookid INT(10) AUTO_INCREMENT,
card INT(10),
PRIMARY KEY (bookid)
);
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
#1.联表查询
explain select *
from class
left join book
on class.card = book.card;
#2.建立索引
alter table book add index idx_card(card);
#3.测试
explain select *
from class
left join book
on class.card = book.card;
分组查询优化
优化规则参照排序优化
drop table if exists students;
CREATE TABLE students (
id INT PRIMARY KEY AUTO_INCREMENT COMMENT "主键id",
sname VARCHAR (24) COMMENT '学生姓名',
age INT COMMENT '年龄',
score INT COMMENT '分数',
time TIMESTAMP COMMENT '入学时间'
);
INSERT INTO students(sname,age,score,time) VALUES('小明',22,100,now());
INSERT INTO students(sname,age,score,time) VALUES('小红',23,80,now());
INSERT INTO students(sname,age,score,time) VALUES('小绿',24,80,now());
INSERT INTO students(sname,age,score,time) VALUES('黑',23,70,now());
-- 分组优化
explain select count(*),sname from students GROUP BY sname;
alter table students add index idx_sas(sname,age,score);
explain select count(*),sname
from students
where sname="小明" and age > 22
GROUP BY score;
慢查询日志
MySQL的慢查询日志是MySQL提供的一种日志记录,他用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。可以由它来查看哪些SQL超出了我们最大忍耐时间值。
慢查询日志使用:
默认情况下,MySQL数据库没有开启慢查询日志,需要手动设置参数。
查看是否开启:show variables like '%slow_query_log%'
开启日志:set global slow_query_log = 1
单位=>秒
设置时间:set global long_query_time = 1
单位=>秒
查看时间:show variables like 'long_query_time%'
查看超时的SQL记录日志:MySQL的数据文件夹下 \5.6\Data\设备名称-slow.log
注意:非调优场景下,一般不建议改启动参数,慢查询日志支持将日志记录写入文件,开启慢查询日志会或多或少带来一定的性能影响。
-- 慢查询日志
-- 查看是否开启:show variables like '%slow_query_log%';
show variables like '%slow_query_log%';
-- 开启日志:set global slow_query_log = 1;
set global slow_query_log = 1;
-- 设置时间: set global long_query_time = 1;
set global long_query_time = 3;
-- 查看时间: SHOW VARIABLES LIKE 'long_query_time%';
SHOW VARIABLES LIKE 'long_query_time%';
select * from person where PNAME = "test2";
select * from students;