[技术笔记] 【西安校区】MySQL优化之SQL语句优化

本帖最后由逆风TO 于 2018-3-30 09:16 编辑

语句优化原则

不查,少查,快查

in 查询优化

案例设计

数据库任然使用mysqldemo数据库,表使用demo表和user表
执行SQL语句 explain select id,name from demo where name in (select uname from user)
执行计划如下所示************ 1. row ************
      id: 1
select_type: SIMPLE
   table: <subquery2>
partitions: NULL
   type: ALL
possible_keys: NULL
   key: NULL
key_len: NULL
   ref: NULL
   rows: NULL
  filtered: 100.00
   Extra: Using where
************ 2. row ************
      id: 1
select_type: SIMPLE
   table: demo
partitions: NULL
   type: ALL
possible_keys: NULL
   key: NULL
key_len: NULL
   ref: NULL
   rows: 8962639
  filtered: 10.00
   Extra: Using where; Using join buffer (Block Nested Loop)
************ 3. row ************
      id: 2
select_type: MATERIALIZED
   table: user
partitions: NULL
   type: ALL
possible_keys: NULL
   key: NULL
key_len: NULL
   ref: NULL
   rows: 3
  filtered: 100.00
   Extra: NULL
3 rows in set, 1 warning (0.00 sec)
通过查看执行计划我们可以看到,该SQL语句进行了全表扫描,比较耗费时间
我们为demo表name列和user表uname列添加索引再次查看执行计划
explain select id,name from demo where name in (select uname from user) \G;
查看执行计划************ 1. row ************
      id: 1
select_type: SIMPLE
   table: user
partitions: NULL
   type: index
possible_keys: uname
   key: uname
key_len: 153
   ref: NULL
   rows: 3
  filtered: 100.00
   Extra: Using where; Using index; LooseScan
************ 2. row ************
      id: 1
select_type: SIMPLE
   table: demo
partitions: NULL
   type: ref
possible_keys: name
   key: name
key_len: 303
   ref: mysqldemo.user.uname
   rows: 2
  filtered: 100.00
   Extra: Using where; Using index
2 rows in set, 1 warning (0.00 sec)
我们看到查询的type使用的是索引

总结

使用in子查询,子查询的结果如果是一个索引列，不存在优化，如果不是索引列需要优化，如果能用联和查询的最好使用联和查询

count优化技巧

案例设计(需要特别注意5.7.2不能实现该优化必须在5.7.2以下的版本)
具体的原因可以参考http://www.ywnds.com/?p=10369

我们在demo表中查询id>100的数据
SQL语句如下所示
select count(*) from demo where id > 100;+----------+
| count(*) |
+----------+
| 9999900 |
+----------+
1 row in set (15.82 sec)

耗费时间15.82
我们查询全表的数量
select count(*) from demo ;+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (10.58 sec)

耗费时间10.58
我们查询
select count(*) from demo where id <= 100;+----------+
| count(*) |
+----------+
| 100 |
+----------+
1 row in set (0.01 sec)
我们可以把全表的总数查询出来,在把id<=100的数量查询出来
id > 100 = 总数－ id <= 100
SQL语句如下所示
select (select count() from demo) - (select count() from demo where id <= 100) as result;+---------+
| result |
+---------+
| 9999900 |
+---------+
1 row in set (5.35 sec)
我们可以看到结果相同，但是查询时间节省了许多
接着优化我们发现我们查询全表的时候时间还是比较长,我们可以通过添加索引再次优化,我们给demo表添加一个字段c,不存入数据,默认之为1
通过alter table demo add index (c);为c添加索引
设置好之后表结构如下所示************ 1. row **************
Table: demo
Create Table: CREATE TABLE `demo` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL,
`url` varchar(100) DEFAULT NULL,
`address` varchar(100) DEFAULT NULL,
`c` tinyint(4) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
我们执行
select count(*) from demo where c > 0;+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (1.83 sec)

ok 仅仅用时1.83s
接着执行
select (select count() from demo where c > 0) - (select count() from demo where id <= 100) as result;+---------+
| result |
+---------+
| 9999900 |
+---------+
1 row in set (1.86 sec)

ok 结果相同时间更短总结主要利用了MySQL count()对全表缓存查询快的特性,以及索引的特性
网友说count(具体的某一列)比count()更好,可以自己测试,他们之间速度差不多相同,没有太大的区别

group by优化

group by 的实质是先排序后分组,也就是分组之前必排序
explain select * from demo group by name \G

************* 1. row **************
         id: 1
  select_type: SIMPLE
      table: demo
partitions: NULL
      type: ALL
possible_keys: NULL
      key: NULL
   key_len: NULL
      ref: NULL
      rows: 8962639
   filtered: 100.00
      Extra: Using temporary; Using filesort
1 row in set, 1 warning (0.00 sec)

我们看到Using filesort，说明MySQL帮助我们做了排序
优化思路有2中

我们禁止自动帮助我们排序
explain select * from demo group by name order by null \G************** 1. row **************
      id: 1
select_type: SIMPLE
   table: demo
partitions: NULL
   type: ALL
possible_keys: NULL
   key: NULL
key_len: NULL
   ref: NULL
   rows: 8962639
  filtered: 100.00
   Extra: Using temporary
1 row in set, 1 warning (0.01 sec)

我们发现没有使用排序，但任然使用了Using temporary 零时表
既然需要排序，我们 group by 分组条件能不能写成一个有序的例如索引列，主键列都是有序的，这样就避免了MySQL帮助我们排序的过程，我们按照id来分组
explain select * from demo group by id \G************** 1. row **************
      id: 1
select_type: SIMPLE
   table: demo
partitions: NULL
   type: index
possible_keys: PRIMARY
   key: PRIMARY
key_len: 4
   ref: NULL
   rows: 8962639
  filtered: 100.00
   Extra: NULL
1 row in set, 1 warning (0.00 sec)

调整之后发现没有用到时表

limit分页查询优化

分页查询一般的实现方式使用limit来做
select id,name,url,address from demo limit 0,10;
0表示起始页数，10表示每页显示的记录数
我们做如下测试起始页分别是下面的数据
0，10，100，1000，10000，100000，1000000
select id,name,url,address from demo limit 0,10;
select id,name,url,address from demo limit 10,10;
select id,name,url,address from demo limit 100,10;
select id,name,url,address from demo limit 1000,10;
select id,name,url,address from demo limit 10000,10;
select id,name,url,address from demo limit 100000,10;
select id,name,url,address from demo limit 1000000,10;
我们观察耗费的时间在逐渐的增加
出现这种情况的原因是因为，limit是逐行扫描例如现在起始行是10000，需要查询出前10000行，然后从10000行之后在取10条记录出来，这样分页数越大查询越慢

从业务逻辑上优化,例如我们默认只能做79页分页,其他的数据忽略不计,可以查看百度和谷歌的分页来展示
我们可以跳过前10000行数据，然后在取10条出来,对应的SQL
select id,name,url,address from demo where id > 10000 limit 10;
这样也是很快,原因是有索引,但是有一个问题,如果之前删除过一些数据,这样会造成分页数据不准确
该中方式必须是数据没有进行物理删除过
假如现在数据存在物理删除，还要不限制分页，可以使用延迟关联策略
思路：我们只查询id列
select id from demo limit 1000000,10;
获取到10个id,我们在通过10个id分别查询出10条记录
select demo.id,name,url,address from demo inner join (select id from demo limit 1000000,10) as tmp on demo.id = tmp.id;

总结

优化不是绝对的，需要根据自己的业务逻辑，项目配置具体情况具体分析

逆风TO · 逆风TO

谢谢老师分享

帐号		自动登录	找回密码
密码			加入黑马

[技术笔记] 【西安校区】MySQL优化之SQL语句优化

1 个回复