文章目录
update优化
update course set name = 'javaEE' where id = 1 ;
修改时: 需要带索引,
不然索引失效--->行锁变表锁
行锁 优化
<!--减库存-->
<update id="decreaseStock">
update item_stock
set stock = stock - #{amount}
where item_id = #{itemId}
and stock >= #{amount}
</update>
- item_id 要加上 唯一索引,这样查询的时候会为
数据库加上行锁,否则是 数据库表锁
- 给 item_stock 表中的 item_id 字段加上唯一索引
IN 和 NOT IN
select * from t1 where phone not in (select phone from t2)
确定且有限的集合
时,可以使用。如 IN (0,1,2)
优化 join
先查一下 两张表,看谁的结果集更大,a 表结果集更大,选择a表为 被驱动表
ALTER TABLE book ADD INDEX Y ( card); #【被驱动表】,可以避免全表扫描
小表驱动大表
: 小的结果集—>驱动大的结果集
三个结论:
- 使用join语句,
性能比强行拆成多个单表执行SQL语句的性能要好
; - 如果使用join语句的话,需要让
小表做驱动表
。 - 优先使用
inner join
如果join语句很慢,就把join_buffer_size改大
show variables like '%join_buffer_size%';
优化group by
select user_id,user_name from `order`
group by user_id
having user_id <= 200;
分组是一个相对耗时的操作,为什么我们不先缩小数据的范围之后,再分组
呢?
select user_id,user_name from `order`
where user_id <= 200
group by user_id
优化 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 /*对于排序来说,多个相等条件也是范围查询*/
SELECT * FROM user order by age desc;
MySQL 的流程是这样的,扫描所有行,把所有行加载到内存后,再按 age 排序生成一张临时表,再把这表排序后将相应行返回给客户端,更糟的,如果这张临时表的大小大于
tmp_table_size 的值(默认为 16 M),内存临时表会转为磁盘临时表,性能会更差!!
如果加了索引,索引本身是有序的 ,所以从磁盘读的行数本身就是按 age 排序好的,也就不会生成临时表
,就不用再额外排序
!!
show variables like 'tmp_table_size';
优化策略
- 尝试提高
sort_buffer_size
- 尝试提高
max_length_for_sort_data
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
起始位置重定义
[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
where查询,缩小数据集的条件 放前面
# 缩小 数据集的条件放前面
# where 语句的执行顺序----> 从左往右
select id,name
from restaurant
where wechat_id=1
and is_delete=0
and discount>20
联表查询
联表查询 先查 小表 得到大表的检索条件,再根据 条件查大表即可,也就是 改成了多次查询单表
select *
from goods g
where g.restaurant_id in(select id
from restaurant
where wechat_id=#{wechat_id}
)
提前缩小范围
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
由于最后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
适合、不适合建立索引的情况
适合
- 频繁作为 where 条件语句 查询字段
- 关联字段 需要建立索引
- 排序字段 可以 建立索引
- 分组、统计 字段可以 建立索引
- 字符串前缀索引—>
select COUNT(DISTINCT LEFT(city,3)) / COUNT(*) from table_name
,city为列名,3为长度 - 频繁
使用的列
放在联合索引的最左边
- 联合索引优于单列索引
不适合
- 频繁 更新
- where、分组、排序 用不到 的字段不必要
show index fron table_name
索引 优先级
- 选择 组合索引 时,尽量 包含 where更多的 字段
- 组合索引 出现 范围查询,尽量放在 索引次序最后面
- 单键 索引, 选择 过滤性更好的字段(手机、身份证、订单号等)
order by
没有 where条件 不走索引
索引优化
口诀
全值匹配我最爱,最左前缀要遵守
带头大哥不能死,中间兄弟不能断
索引列上少计算、函数,范围之后全失效(>= | <=可以使用索引
)
like百分%加右边
,覆盖索引不写*
不等空值还有or
,索引失效少使用
数据分布有影响(is null | is not null
)
类型转换不能有
,varchar引号不能丢
# 强制 使用某个索引
select * from table force index(id) limit 2; #( 强制使用主键)
解释
全值匹配我最爱
:索引的所有字段都使用到!
最佳左前缀法则
:指的最左边的列,是指在查询时,联合索引的最左边的字段
(即是
第一个字段)必须存在
,与我们编写SQL时,条件编写的先后顺序无关
检查 where,order by,group by后面的列
多表关联的列
是否已加索引
,优先考虑组合索引
# 添加索引
alter table user add index idx_address_age (address,age);