# 21 个写 sql 的好习惯

写完 SQL 先 explain 查看执行计划（SQL 性能优化）
操作 delete 或者 update 语句，加个 limit(SQL 后悔药）
- 降低写错 SQL 的代价, 你在命令行执行这个 SQL 的时候，如果不加 limit，执行的时候一个不小心手抖，可能数据全删掉了，如果删错了呢？加了 limit 200，就不一样了。删错也只是丢失 200 条数据，可以通过 binlog 日志快速恢复的。
- SQL 效率很可能更高，你在 SQL 行中，加了 limit 1，如果第一条就命中目标 return，没有 limit 的话，还会继续执行扫描表。
- 避免了长事务，delete 执行时,如果 age 加了索引，MySQL 会将所有相关的行加写锁和间隙锁，所有执行相关行会被锁住，如果删除数量大，会直接影响相关业务无法使用。
- 数据量大的话，容易把 CPU 打满 ,如果你删除数据量很大时，不加 limit 限制一下记录数，容易把 cpu 打满，导致越删越慢的。
设计表的时候，所有表和字段都添加相应的注释（SQL 规范优雅）
where 后面的字段，留意其数据类型的隐式转换（SQL 性能优化）
SQL 修改数据，养成 begin + commit 事务的习惯(SQL 后悔药)

-- 正例
begin;
update account set balance =1000000
where name ='捡田螺的小男孩';
commit;

-- 反例
update account set balance =1000000
where name ='捡田螺的小男孩';

1
2
3
4
5
6
7
8
9

# 使用 Exists 代替子查询

????????

# 适当的使用 JOIN 来代替子查询

# 使用 Where 替代不必要的 Having

# 使用精确的字段类型

在使用时间类型的字段的时候，就需要设置成 DateTime，不能用 varchar

# 使用批处理代替循环

# 使用 UNION ALL 替代 UNION

# 用精确的字段代替 *

使用 * 表示要查询所有字段，当我们的表是一个很简单的表，而且里面的字段都是一些小字段的时候，使用 * 完全是可以的

如果是对于一些大表特别是有 text 这种大字段的表，或者是一些敏感数据的表，我们还使用 * 号去查询数据的话，就会有很大的问题了，一方面是有安全隐患，一方面还是增加磁盘，内存和网络的传输，完全得不偿失。

# 给必要的字段增加索引

# 禁止使用左模糊或者全模糊查询

无法用到索引的

# 索引访问类型至少达到 range 级别

# 注意避免深分页

MySQL 深分页的时候，查询性能较差。

select * from user where name='一灯' limit 10000,10;

我们可以采用子查询的方式进行优化：

select * from user where id in (  select id from user   where name='一灯'  limit 10000,10);

这样可以减少非聚簇索引回表查询的次数。

# 单表字段不要超过 30 个

# 枚举字段不要使用字符类型

字符类型会占用更多的存储空间，当我们想要存储枚举值或者表示是否的时候，可以采用 tinyint 数值类型，最好采用无符号整数 unsigned tinyint。

# 小数类型禁止使用 float 和 double

在存储和计算的时候，float 和 double 都存在精度损失的问题，无法得到正确的结果。

所以在涉及到存储小数的时候，必须使用 decimal 类型。

# 所有字段必须设置默认值且不允许为 null

字段允许为 null，会占用额外的存储空间。
索引并不会索引 null 值，所以查询 null 值的时候无法用到索引。
当数值类型允许为 null，返回给映射实体类的时候还可能会报空指针异常。

# 必须创建主键，最好是有序数值类型

如果我们自己没有给表设置主键，InnoDB 会自动增加一列隐藏的主键，我们无法使用到，并且也占用的更多的存储空间，所以建表的时候，必须设置主键。

有序数值更适合做主键，插入数据的时候，由于是有序的，不会频繁调整 B+树结构，性能更好。

# 快速判断是否存在某条记录

一般我们判断表中是否存在某条记录的时候，会使用 count 函数，然后判断返回值是否大于 1。

select count(*) from user where name='一灯';

InnoDB 存储引擎并没有像 MyIsAm 那样缓存表的总行数，每次查询都是实时计算的，耗时较长。

我们可以采用 limit 加快查询效率：

select id from user where name='一灯' limit 1;

limit 1 表示匹配到一条就返回，查询效率更好，结果集只返回 id，还可以用到覆盖索引。

# in 条件中数量不宜过多

in 条件中数量不要超过 1000 个，不然耗时会非常长，可以拆成多批次查询。

# 单表索引数不要超过 5 个

创建适当的索引可以提高查询效率，但是过多的索引，不但占用更多存储空间，还会拖慢更新 SQL 的性能。

所以，索引好用，适度即可。