项目概况

Mysql优化示例

2026-01-297 min read后端技术
sql

作者: 西魏陶渊明 博客: https://blog.springlearn.cn/

!!! tip 西魏陶渊明 莫笑少年江湖梦，谁不少年梦江湖 !!!

创建表

sql
1use test; 2create table test03 3( 4 a1 int(4) not null, 5 a2 int(4) not null, 6 a3 int(4) not null, 7 a4 int(4) not null 8); 9alter table test03 add index idx_a1_a2_a3_a4(a1,a2,a3,a4); 10

建议一、按照复合索引顺序查询

需知

  1. 如果(a,b,c,d)复合索引和查询使用的顺序全部一致,则复合索引全部使用,如果不部分一致或者跨列使用则就是部分使用.
  2. where和order by拼起来也不要跨列,参考反例2和3

1. 建议

  • 建议: 按照where后面按照顺序使用复合索引
  • 建议: where 和 order by不要跨列 explain select a1,a2,a3,a4 from test03 where a1=1 and a2=2 and a3=3 and a4=4;

2. 反例1

explain select a1,a2,a3,a4 from test03 where a4=1 and a3=2 and a2=3 and a1=4;

可以看到还是一样的,索引都用了，原因是sql在执行时候被sql优化器进行了调整,最后被调整成了上面的顺序写法。 这是最理想的情况,但是实际中建议开发按照顺序来进行查询。

3. 反例2

explain select a1,a2,a3,a4 from test03 where a1=1 and a2=2 and a4=4 order by a3;

  • 因为查询条件中复合索引跨列了(跨了a3)，所以导致只能用a1 和a2索引。索引key_len变成8了
  • 索引中没有查询a3但是却用a3排序了,导致要回表查询a3

4. 反例3

explain select a1,a2,a3,a4 from test03 where a1=1 and a4=4 order by a3;

  • 跨了a2 a3 所以a4失效只能用a1 所以key_len只有一个 where 和 order by拼接起来是否也满足复合顺序,如果不满足 就会出现Using filesort
  • 反例2中where 生效的是a1和a2，但是order by是a3。满足复合索引顺序,所以不会出现Using filesort

explain select a1,a2,a3,a4 from test03 where a1=1 and a4=4 order by a2,a3;

  • where a1 order by a2 a3 所以不会出现上面Using fileSort

建议二、单表优化

建表及需求sql

text
1 2create table book 3( 4 bid int(4) primary key, 5 name varchar(20) not null, 6 authorid int(4) not null, 7 publicid int(4) not null, 8 typeid int(4) not null 9); 10 11insert into book values(1,'tjava',1,1,2); 12insert into book values(2,'tjava',2,1,2); 13insert into book values(3,'tjava',3,2,1); 14insert into book values(4,'tjava',4,2,3); 15

查询authorid = 1 并且 typeid 等于2或者3 然后根据typeid 排序

explain select bid from book where typeid in (2,3) and authorid = 1 order by typeid desc;

text
1mysql> explain select bid from book where typeid in (2,3) and authorid = 1 order by typeid desc; 2+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ 3| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | 4+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ 5| 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where; Using filesort | 6+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ 71 row in set, 1 warning (0.00 sec)

可以看出来未用到索引,type是all。就是全表查询了。

1. 优化1加索引

alter table book add index idx_bta(bid,typeid,authorid);

text
1mysql> explain select bid from book where typeid in (2,3) and authorid = 1 order by typeid desc; 2+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------------------+ 3| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | 4+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------------------+ 5| 1 | SIMPLE | book | NULL | index | NULL | idx_bta | 12 | NULL | 4 | 25.00 | Using where; Using index; Using filesort | 6+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------------------+ 71 row in set, 1 warning (0.00 sec)

可以看到通过了加索引type由之前的all变成了index,说明有一点进步。但是我们看到还有一个Using filesort.前面我们说了出现这个 是因为额外多了一次查询。根据sql的解析规则，第一个解析的是typeid,第二个是authorid。那么我们先对索引顺序做一个优化。

2. 优化索引顺序

复合索引一旦进行了升级优化，就要删除了，否则会进行干扰。drop index idx_bta on book;

按照sql执行顺序来创建索引。这里我们其实也可以不创建bid，但是如果不创建bid,就要回表去查询bid,所以也建议加上。 alter table book add index idx_tab(typeid,authorid,bid);

text
1mysql> explain select bid,typeid from book where typeid in (2,3) and authorid = 1 order by typeid desc; 2+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------------------------+ 3| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | 4+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------------------------+ 5| 1 | SIMPLE | book | NULL | range | idx_tab | idx_tab | 8 | NULL | 2 | 100.00 | Using where; Backward index scan; Using index | 6+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------------------------+ 71 row in set, 1 warning (0.00 sec)

可以看到已经没有了,Using filesort。

3. 总结

  • 索引优化不会一步到位,要多次优化。
  • 索引顺序会影响查询效率,如果不知道怎么优化,建议sql执行顺序,多尝试几次不同顺序。通过看type的级别来调整。
  • type如果是range或者ref。其实就可以了。如果是index或者all就要考虑是否要进行优化。
  • in会是索引失效

建议三、多表优化

建表及需求sql

text
1create table teacher2 2( 3 tid int(4) primary key, 4 cid int(4) not null 5); 6 7insert into teacher2 values(1,2); 8insert into teacher2 values(2,1); 9insert into teacher2 values(3,3); 10 11create table course2 12( 13 cid int(4), 14 cname varchar(20) 15); 16insert into course2 values(1,'java'); 17insert into course2 values(2,'python'); 18insert into course2 values(3,'kotlin');

需求sql

select * from teacher2 t left outer join course2 c on t.cid=c.cid where c.cname = 'java';

text
1mysql> select * from teacher2 t left outer join course2 c on t.cid=c.cid where c.cname = 'java'; 2+-----+-----+------+-------+ 3| tid | cid | cid | cname | 4+-----+-----+------+-------+ 5| 2 | 1 | 1 | java | 6+-----+-----+------+-------+ 71 row in set (0.00 sec)

执行计划分析

text
1mysql> explain select * from teacher2 t left outer join course2 c on t.cid=c.cid where c.cname = 'java'; 2+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+ 3| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | 4+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+ 5| 1 | SIMPLE | c | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where | 6| 1 | SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where; Using join buffer (Block Nested Loop) | 7+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+ 82 rows in set, 1 warning (0.00 sec)

可以看到有一个Using join buffer 。 说明Sql写的太差了，mysql给你加了一个缓存。经过下面的索引优化会去掉。

优化1小表驱动大表

当编写语句时候,将数据量小的表放左边(假设此时t表小,on t.cid = c.cid);反之如果c表小(on c.cid = t.tic)

优化2加索引

索引建立在经常使用的字段上,本例中t.cid使用频繁。

alter table teacher2 add index idx_teacher2_cid(cid);

text
1mysql> explain select * from teacher2 t left outer join course2 c on t.cid=c.cid where c.cname = 'java'; 2+----+-------------+-------+------------+------+------------------+------------------+---------+------------+------+----------+-------------+ 3| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | 4+----+-------------+-------+------------+------+------------------+------------------+---------+------------+------+----------+-------------+ 5| 1 | SIMPLE | c | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where | 6| 1 | SIMPLE | t | NULL | ref | idx_teacher2_cid | idx_teacher2_cid | 4 | test.c.cid | 1 | 100.00 | Using index | 7+----+-------------+-------+------------+------+------------------+------------------+---------+------------+------+----------+-------------+ 82 rows in set, 1 warning (0.00 sec) 9

给name也加索引

alter table course2 add index idx_course2_name(cname);

text
1mysql> explain select * from teacher2 t left outer join course2 c on t.cid=c.cid where c.cname = 'java'; 2+----+-------------+-------+------------+------+------------------+------------------+---------+------------+------+----------+-------------+ 3| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | 4+----+-------------+-------+------------+------+------------------+------------------+---------+------------+------+----------+-------------+ 5| 1 | SIMPLE | c | NULL | ref | idx_course2_name | idx_course2_name | 83 | const | 1 | 100.00 | Using where | 6| 1 | SIMPLE | t | NULL | ref | idx_teacher2_cid | idx_teacher2_cid | 4 | test.c.cid | 1 | 100.00 | Using index | 7+----+-------------+-------+------------+------+------------------+------------------+---------+------------+------+----------+-------------+ 82 rows in set, 1 warning (0.00 sec)

这样两个查询就都用到了索引。

建议四、sql优化

1. exist和in

如果主查询的数据集大用 in ,如果子查询数据量大使用 exist

2. order by优化

Using filesort 有两种算法: 双路排序、单路排序(根据IO的次数,即访问磁盘的顺序)

  • MySQL4.1前默认使用双路排序,即扫描两次磁盘(1. 从磁盘读取排序字段,2. 扫描其他字段)
  • MySQL4.1后默认使用单路排序,即访问一次磁盘(1. 只读取一次字段,然后在buffer中进行排序)

但是单路排序有隐患就是不一定是只访问一次磁盘,因为加入数据量特别大,则无法将所有字段的 数据都放到buffer中,要多次分片读取。此时可以考虑调大buffer容量大小。

set max_length_for_sort_data = 1024 (单位/字节)

如果缓存区大小太小,mysql会自动从单路调整到双路。

建议

  • 避免使用select *
  • 复合索引不要跨列使用,如果where和order尽量也按照顺序使用
  • order by 如果是多个字段,建议都是升序或者都是降序

3. 慢查询日志

如何找到垃圾SQL语句,你知道这些方式吗？

慢sql就是mysql提供的一种日志记录,用于记录响应的时间超过阀值得语句;

查询是否开启慢查询

show variables like '%slow_query_log%';

text
1mysql> show variables like '%slow_query_log%'; 2+---------------------+-----------------------------------------+ 3| Variable_name | Value | 4+---------------------+-----------------------------------------+ 5| slow_query_log | OFF | 6| slow_query_log_file | /usr/local/var/mysql/localhost-slow.log | 7+---------------------+-----------------------------------------+ 82 rows in set (0.06 sec)

临时开启:

text
1set global slow_query_log = 1; 2exit; 3services mysql restart

查询慢查询日志阀值

show variables like '%long_query_time%';

text
1mysql> show variables like '%long_query_time%'; 2+-----------------+-----------+ 3| Variable_name | Value | 4+-----------------+-----------+ 5| long_query_time | 10.000000 | 6+-----------------+-----------+ 71 row in set (0.00 sec)

临时设置阀值

修改完成之后要重新登录生效

text
1set global long_query_time = 5;
text
1mysql> show variables like '%long_query_time%'; 2+-----------------+-----------+ 3| Variable_name | Value | 4+-----------------+-----------+ 5| long_query_time | 10.000000 | 6+-----------------+-----------+ 71 row in set (0.00 sec) 8 9mysql> set global long_query_time = 5; 10Query OK, 0 rows affected (0.00 sec) 11 12mysql> show variables like '%long_query_time%'; 13+-----------------+-----------+ 14| Variable_name | Value | 15+-----------------+-----------+ 16| long_query_time | 10.000000 | 17+-----------------+-----------+ 181 row in set (0.00 sec) 19 20mysql> exit 21Bye 22 liuxin@localhost  ~  mysql -u root -p 23Enter password: 24Welcome to the MySQL monitor. Commands end with ; or g. 25Your MySQL connection id is 9 26Server version: 8.0.16 Homebrew 27 28Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. 29 30Oracle is a registered trademark of Oracle Corporation and/or its 31affiliates. Other names may be trademarks of their respective 32owners. 33 34Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. 35 36mysql> show variables like '%long_query_time%'; 37+-----------------+----------+ 38| Variable_name | Value | 39+-----------------+----------+ 40| long_query_time | 5.000000 | 41+-----------------+----------+ 421 row in set (0.00 sec) 43 44mysql> 45

超过阀值得数量

show global status like '%slow_queries%';

text
1mysql> show global status like '%slow_queries%'; 2+---------------+-------+ 3| Variable_name | Value | 4+---------------+-------+ 5| Slow_queries | 0 | 6+---------------+-------+ 71 row in set (0.01 sec)

总结

  1. 复合索引,不要跨列或无序使用
  2. 复合索引,尽量使用全索引匹配
  3. 不要在索引上进行计算、函数、类型转换
  4. 复合索引不能使用 != 、 <> 、 is null
  5. like尽量以"常量"开头,不要以"%"开头,否则索引失效
  6. 尽量不要使用类型转换（显式、隐式）,否则索引失效。where name = 'lx' 可以。where name
  7. 尽量不要用or,否则左右索引都可能失效

最后求关注,求订阅,谢谢你的阅读!