SQL优化

update优化

update course set name = 'javaEE' where id = 1 ;
1

修改时: 需要带索引，不然索引失效--->行锁变表锁

行锁 优化

<update id="decreaseStock">
    update item_stock
    set stock = stock - #{amount}
    where item_id = #{itemId}
      and stock >= #{amount}
update>
1
2
3
4
5
6
7

• ​​item_id​​ 要加上 唯一索引，这样查询的时候会为数据库加上行锁，否则是 数据库表锁
• 给 ​​item_stock​​​ 表中的 ​​item_id​​ 字段加上唯一索引
  

IN 和 NOT IN

select * from t1 where phone not in (select phone from t2)
1

确定且有限的集合 时，可以使用。如 IN （0，1，2）

优化 join

先查一下 两张表，看谁的结果集更大，a 表结果集更大，选择a表为 被驱动表

ALTER TABLE book ADD INDEX Y ( card); #【被驱动表】，可以避免全表扫描
1

小表驱动大表: 小的结果集—>驱动大的结果集
三个结论：

1. 使用join语句，性能比强行拆成多个单表执行SQL语句的性能要好；
2. 如果使用join语句的话，需要让小表做驱动表。
3. 优先使用inner join

如果join语句很慢，就把join_buffer_size改大

show variables like '%join_buffer_size%';
1

优化group by

select user_id,user_name from `order`
group by user_id
having user_id <= 200;
1
2
3

分组是一个相对耗时的操作，为什么我们不先缩小数据的范围之后，再分组呢？

select user_id,user_name from `order`
where user_id <= 200
group by user_id
1
2
3

优化 order by

如果order by语句中没有加 where或 limit关键字，该sql语句将不会走索引

问题：在 WHERE 条件字段上加索引，但是为什么在 ORDER BY 字段上还要加索引呢？
尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列；
如果不同就使用联合索引

INDEX a_b_c(a,b,c)
order by 能使用索引最左前缀
- ORDER BY a
- ORDER BY a,b
- ORDER BY a,b,c
- ORDER BY a DESC,b DESC,c DESC
如果WHERE使用索引的最左前缀定义为常量，则order by 能使用索引
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b = const ORDER BY c
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b > const ORDER BY b,c # a为常量，一开始有个 b的范围，后面又接上了b，所以使用上了索引！
不能使用索引进行排序
- ORDER BY a ASC,b DESC,c DESC /* 排序不一致 */
- WHERE g = const ORDER BY b,c /*丢失a索引*/
- WHERE a = const ORDER BY c /*丢失b索引*/
- WHERE a = const ORDER BY a,d /*d不是索引的一部分*/
- WHERE a in (...) ORDER BY b,c /*对于排序来说，多个相等条件也是范围查询*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

SELECT * FROM user order by age desc;
1

MySQL 的流程是这样的，扫描所有行，把所有行加载到内存后，再按 age 排序生成一张临时表，再把这表排序后将相应行返回给客户端，更糟的，如果这张临时表的大小大于 tmp_table_size 的值（默认为 16 M），内存临时表会转为磁盘临时表，性能会更差！！

如果加了索引，索引本身是有序的 ，所以从磁盘读的行数本身就是按 age 排序好的，也就不会生成临时表，就不用再额外排序 ！！

show variables like 'tmp_table_size';
1

优化策略

1. 尝试提高 sort_buffer_size

2. 尝试提高 max_length_for_sort_data
3. Order by 时select * 是一个大忌。最好只Query需要的字段

分页查询优化

覆盖索引+子查询

[SQL]
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id >= (select id from emp order by id limit 4800000,1)
order by a.id limit 25;

受影响的行: 0
时间: 1.541s
1
2
3
4
5
6
7
8

起始位置重定义

[SQL]
SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id > 100 order by a.id limit 25;
受影响的行: 0
时间: 0.001s

[SQL]
# 随着 pageNo 不断增大，查询效率 变低
# int lastMaxId=100;(前端传递)
SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id > #{lastMaxId}   # 从lastMaxId开始
order by a.id limit 25;
受影响的行: 0
时间: 0.000s
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

where查询，缩小数据集的条件 放前面

# 缩小 数据集的条件放前面
# where 语句的执行顺序----> 从左往右
select id,name 
from restaurant
where wechat_id=1
	and is_delete=0
	and discount>20
1
2
3
4
5
6
7

联表查询

联表查询 先查 小表 得到大表的检索条件，再根据 条件查大表即可，也就是 改成了多次查询单表

select *
from goods g
where g.restaurant_id in(select id
						 from restaurant
						 where wechat_id=#{wechat_id}
					    )
1
2
3
4
5
6

提前缩小范围

SELECT * 
FROM   my_order o 
       LEFT JOIN my_userinfo u 
              ON o.uid = u.uid
       LEFT JOIN my_productinfo p 
              ON o.pid = p.pid 
WHERE  ( o.display = 0 ) 
       AND ( o.ostaus = 1 ) 
ORDER  BY o.selltime DESC 
LIMIT  0, 15 
1
2
3
4
5
6
7
8
9
10

由于最后WHERE条件以及排序均针对最左主表，因此可以先对my_order排序提前缩小数据量再做左连接。SQL重写后如下，执行时间缩小为1毫秒左右。

SELECT * 
FROM (
SELECT * 
FROM   my_order o 
WHERE  ( o.display = 0 ) 
       AND ( o.ostaus = 1 ) 
ORDER  BY o.selltime DESC 
LIMIT  0, 15
) o 
     LEFT JOIN my_userinfo u 
              ON o.uid = u.uid 
     LEFT JOIN my_productinfo p 
              ON o.pid = p.pid 
ORDER BY  o.selltime DESC
limit 0, 15

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

适合、不适合建立索引的情况

适合

• 频繁作为 where 条件语句 查询字段
• 关联字段 需要建立索引
• 排序字段 可以 建立索引
• 分组、统计 字段可以 建立索引
• 字符串前缀索引—>select COUNT(DISTINCT LEFT(city,3)) / COUNT(*) from table_name，city为列名，3为长度
• 频繁使用的列放在联合索引的最左边
• 联合索引优于单列索引

不适合

• 频繁 更新
• where、分组、排序 用不到 的字段不必要

show index fron table_name
1

索引 优先级

• 选择 组合索引 时，尽量 包含 where更多的 字段
• 组合索引 出现 范围查询，尽量放在 索引次序最后面
• 单键 索引， 选择 过滤性更好的字段（手机、身份证、订单号等）
• order by 没有 where条件 不走索引

索引优化

口诀

全值匹配我最爱，最左前缀要遵守
带头大哥不能死，中间兄弟不能断
索引列上少计算、函数，范围之后全失效(>= | <=可以使用索引)
like百分%加右边，覆盖索引不写*
不等空值还有or,索引失效少使用
数据分布有影响(is null | is not null)
类型转换不能有，varchar引号不能丢

# 强制 使用某个索引
select * from table force index(id) limit 2; #( 强制使用主键)
1
2

解释

全值匹配我最爱：索引的所有字段都使用到！
最佳左前缀法则:指的最左边的列，是指在查询时，联合索引的最左边的字段(即是
第一个字段)必须存在，与我们编写SQL时，条件编写的先后顺序无关

检查 where,order by,group by后面的列

多表关联的列是否已加索引，优先考虑组合索引

# 添加索引
alter table user add index idx_address_age (address,age);
1
2