查询单条数据

  1. 在查询确认单条数据时需要加上 limit 1

select * from users  where name ='xiaou' limit 1;

按条件批量删除/更新数据

⚠ 注意要尽量走索引的条件进行批量删除和更新数据

因为如果通过索引方式更新数据走的是行级锁。

MySQL 行级锁这里浅浅的描述一下：

优点：锁的粒度小，发生锁冲突的概率低；处理并发的能力强

缺点：开销大；加锁慢；会出现死锁。

我们可以先查询出来要删除信息的主键再根据主键分批删除数据，这样可以有效的避免开销大后被 MySQL 升级为表锁和死锁。

select id from users where age > 80;
delete * from users where id = 1;

如果停机维护时，为了节约时间可以使用表级锁，因为表级锁有：开销小；加锁快；无死锁的特点。

使用 exists 来代替 left join

-- 语句一
select a.id, a.v from a left join b on a.id = b.id where b.v = 'a';
-- 语句二
select a.id, a.v from a where EXISTS(select 1 from b where  a.id = b.id and b.v = 'a')

如果表 a 的数据量远大于表 b，LEFT JOIN 可能会产生大量的行，这可能会降低性能。相比之下，EXISTS 子查询可能会更快地返回结果，大部分情况下 MySQL 优化器会进行优化。

编写 SQL 时必看索引列表

SHOW INDEX FROM table_name;

  1. 因为有一些联合索引，如果没有使用索引开头字段的话会导致索引失效

    1. 联合索引生效口诀：带头大哥不能死，中间兄弟不能断

is null 优化

select id from t where num is null;

字段可以给定一个 0 作为默认值，可以优化为

select id from t where num = 0;

应尽量避免在 where 子句中使用 or 来连接条件

select id from t where num = 10 or num = 20;

可以这样查询：

select id from t where num = 10
union all
select id from t where num = 20;

避免在 where 子句中对字段进行表达式操作

select id from t where num / 2 = 100;

应改为:

select id from t where num = 100*2;

用 exists 代替 in 是一个好的选择

select num from a where num in(select num from b);

应改为:

select num from a where exists(select 1 from b where num=a.num);

like 全模糊检索可以考虑使用全文索引

select id from t where name like '%abc%';

可以改为:

记得对这个字段设置为全文所有

SELECT id FROM t 
WHERE MATCH ( name ) AGAINST ('+"${itemValue}"' IN BOOLEAN MODE )

in 大量数据优化

MySQL 当 in 的数量超过 200 (ep_range_index_dive_limit 参数的值) 个会导致索引失效

SELECT id FROM t 
WHERE code in (1,2,3,...500)

可以改为:

  1. 创建一个临时表

  2. 再通过inner join 表的方式进行处理