SQL 语句优化

emm，写SQL谁都是会写的啦！但是要提高效率这就是进阶的任务啦!所以我看到的坑就保存一下啦!

 

字段

1、尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED；

2、VARCHAR的长度只分配真正需要的空间；

3、尽量使用TIMESTAMP而非DATETIME；

原因：

 

相同点：

TIMESTAMP列的显示格式与datetime列相同。

换句话说，显示宽度固定在19字符，并且格式为YYYY-MM-DD HH:MM:SS

 

• 不同点：
  datetime
  以"YYYY-MM-DDHH:MM:SS"格式检索和显示datetime值。支持的范围为"1000-01-01 00:00:00"到"9999-12-31 23:59:59"
  TIMESTAMP值不能早于1970或晚于2037
  TIMESTAMP
  1、4个字节储存（Time stamp value is stored in 4 bytes）
  2、值以UTC格式保存（ it stores the number of milliseconds）
  3、时区转化 ，存储时对当前的时区进行转换，检索时再转换回当前的时区。
  datetime
  1、8个字节储存（8 bytes storage）
  2、实际格式储存（Just stores what you have stored and retrieves thesamething which you have stored.）
  3、与时区无关（It has nothing to deal with the TIMEZONEandConversion.）
  
  MySQL中尽量使用datetime，而不要使用timestamp
  粗看起来，在MySQL中timestamp和datetime都能保存年、月、日、时、分、秒，只是timestamp可以保存1秒以下的时间。
  但不仅仅是这样，timestamp是有限制范围的，从1970年1月2日到2037年。虽然这个限制看起来无所谓，因为一般用不到。
  但是，如果一旦有超过此范围的值insert或者update，MySQL并不会报错，并把一个年月日时分秒都是0的值放到表中。这一点在mysql文档中是有的，但是很可能未被注意到。
  由于MySQL不报错，因此应用程序并不知道。当再把此值查出来的时候，就导致应用程序出现意想不到的错误。
  因此，除非必须，否则在MySQL中尽量使用datetime，不用timestamp

 

4、单表不要有太多字段，建议在 20 以内；

5、避免使用 NULL 字段，很难查询优化且占用额外索引空间；

6、用整型来存 IP。

 

为什么要存整型：在Mysql中并没有提供针对IP地址存储的数据格式，在开发中我们可以选择使用char、varchar、int来存储，根据mysql字段类型选择的规则：字段类型定义使用最合适（最小）、最简单的数据类型，优先选择使用int类型来存储，其在逻辑运算上也要比char、varchar更快

int类型存储IP地址

在Mysql中提供了两个函数，用来把IP地址与数字类型的相互转化

• inet_aton()：把IP地址转化为数字

这种算法其实借用了国际上对各国IP地址的区分中使用的ip number。
a.b.c.d 的ip number是：
a 256的3次方 + b 256的2次方 + c 256的1次方 + d 256的0次方。

mysql> select inet_aton('255.255.255.255');
+------------------------------+
| inet_aton('255.255.255.255') |
+------------------------------+
|                   4294967295 |
+------------------------------+
1 row in set (0.00 sec)

• inet_ntoa()：把数字转化成IP地址

  mysql> select inet_ntoa(4294967295);
  +-----------------------+
  | inet_ntoa(4294967295) |
  +-----------------------+
  | 255.255.255.255       |
  +-----------------------+
  1 row in set (0.00 sec)

 

 

 

索引

1、索引并不是越多越好，要根据查询有针对性的创建，考虑在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描

2、应尽量避免在WHERE子句中对字段进行NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描；

3、值分布很稀少的字段不适合建索引，例如 "性别" 这种只有两三个值的字段；

 

4、字符字段只建前缀索引；

 

5、不用外键，由程序保证约束；

6、尽量不用UNIQUE，由程序保证约束

7、使用多列索引时主意顺序和查询条件保持一致，同时删除不必要的单列索引。

 

查询 SQL

• 可通过开启慢查询日志来找出较慢的 SQL；

• 不做列运算：SELECT id WHERE age + 1 = 10，任何对列的操作都将导致表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能将操作移至等号右边；

• SQL 语句尽可能简单：一条 SQL 只能在一个 CPU 运算；大语句拆小语句，减少锁时间；一条大 SQL 可以堵死整个库；

• 不用SELECT *；

• OR改写成IN：OR的效率是 n 级别，IN的效率是 log(n) 级别，in 的个数建议控制在 200 以内；

• 不用函数和触发器，在应用程序实现；

• 避免%xxx式查询；

• 少用JOIN；

• 使用同类型进行比较，比如用'123'和'123'比，123和123比；

• 尽量避免在WHERE子句中使用!= 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描；

• 对于连续数值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5；

• 列表数据不要拿全表，要使用LIMIT来分页，每页数量也不要太大。

实用版SQL优化：（持续添加）

这些都是我在项目中如果坑的，避免大家再次入坑，或者是能够引起反思的

一、索引最大化生效

1、where条件先后顺序

未优化之前：

select
    *
FROM
    app_banner
where
    tenant_id = #{tenantId}
AND
    del_flag = 0

优化之后：

select
    *
FROM
    app_banner
where
    del_flag = 0 
AND
    tenant_id = #{tenantId}

 分析：在编写SQL时，会建议将选择性高（过滤数据多）的条件放到WHERE条件的前面，这是为了让查询优化器优先考虑这些条件，减少生成最优(或相对最优)的执行计划的时间，但最终的执行计划生成过滤顺序还是决定这些条件的选择性与判断bool值的容易程度

欢迎加入 CSDN技术交流群：（点击即可加群）QQ群:681223095。

因经常有人留言，未能及时查看到和回复，所以特建此群，以方便交流。方便问题讨论,有问题和没有问题的小伙伴均可加入，用作自我学习和共同进步。本博主不一定长期在线，但是qq群里会有很多热心的小伙伴，大家一起讨论解决问题。
右边的二维码是公众号。关注公众号，更多学习内容给予推送，争取每日更新