那些年我们经常唠嗑的SQL优化

前言

我们出去面试经常会被问到数据库这一块，而涉及数据库这一块问的最多的就是数据库优化。那么我们怎么做才能做好优化问题呢？今天我们就来聊聊数据库优化那些事。

数据库优化我们一般可以从以下几个方面考虑：

• 数据库
• 表设计
• sql语句优化

数据库

大型项目拆分为小项目，每个项目有自己独立的数据库

原来所有数据表都放在一个数据库节点上，所有的读写请求也都发到这个MySQL上面，所以数据库的负载太高。如果把一个节点的数据库拆分成多个MySQL数据库，这样就可以有效的降低每个MySQL数据库的负载。

分表分库（取模分表、水平分表、垂直分表)

通过取模算法进行水平分表，例如总共有3张表， 1%3=1 放入第一张表，2%3=2,放入低二张表，3%3=0 放入第0张表，相当于用几张表来平分一张表，最好的做法是一张主表，再来几张字表。因为分表不好做分页，数据存放在多张表中，所以需要一张主表用来存放所有的数据，当子表无法满足时采用主表。

读写分离

把对数据库的读写操作分到不同的数据库服务上，以实现数据库的高性能和高并发能力；读写分离最大的弊端就是复制延迟，对于以查询为主的项目比较合适，看项目进行取舍。

表设计

数据库表设计遵循三范式

第一范式：原子约束，每列不能再分

第二范式：每张表只描述一件事情，就是主键就对应着所有信息。

第三范式：要保证表中的数据和主键直接相关，而不是间接相关。比如订单表中出现了快递的属性（快递单号，收件人姓名、收货地址），可以把收件人姓名和收货地址提出来单独放到一张表，只留快递单号在订单表中作为关联。并不是所有的表设计都必须按照三大范式设计，具体需要根据需求来定

表字段设计

尽量设计成not null，尽量使用数字型字段（如性别，男：1 女：2），若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销,字段长度设计合理，比如邮编，只需要位长度，没必要过长,

用varchar/nvarchar 代替 char/nchar

因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了， 不管是否插入值（NULL也包含在内），都是占用 100个字符的空间的，如果是varchar这样的变长字段， null 不占用空间。

sql语句优化

• 最大化使用索引，合理的创建索引情况下，最大化使用索引
• 减少数据访问：设置合理的字段类型
• 返回更少的数据：只返回需要的字段和数据分页处理
• 减少交互次数：批量DML操作，函数存储等减少数据连接次数
• 尽量减少数据库排序操作以及全表查询

sql语句优化的时候首先，我们需要定位有没有慢查询慢查询，顾名思义，执行很慢的查询。有多慢？超过long_query_time参数设定的时间阈值（默认10s）

慢查询基本配置

• slow_query_log 启动停止技术慢查询日志
• slow_query_log_file 指定慢查询日志得存储路径及文件（默认和数据文件放一起）
• long_query_time 指定记录慢查询日志SQL执行时间得伐值（单位：秒，默认10秒）
• log_queries_not_using_indexes 是否记录未使用索引的SQL
• log_output 日志存放的地方【TABLE】【FILE】【FILE,TABLE】

mysql5.7以上的版本，在my.ini中配置慢查询配置

# 启动慢查询日志
slow_query_log = ON
# 慢查询日志存放地方
slow_query_log_file = D:\\software\\mysql\\mysql-5.7.24-winx64\\data\\logs\\show.log
# 设置慢查询时间，默认10秒，我们此处设置为1秒，也就是超过1秒就是慢查询
long_query_time = 1

通过下面命令查看下上面的配置：

• show VARIABLES like '%slow_query_log%'
• show VARIABLES like '%slow_query_log_file%'
• show VARIABLES like '%long_query_time%'
• show VARIABLES like '%log_queries_not_using_indexes%'
• show VARIABLES like 'log_output'
• set global long_query_time=1; -- 默认10秒，这里为了演示方便设置为1
• set GLOBAL slow_query_log = 1; -- 开启慢查询日志
• set global log_output='FILE' -- 项目开发中日志只能记录在日志文件中，不能记表中

例如现在我执行 select sleep(2) 沉睡两秒，超过我设置慢查询时间1秒，就会会被记录到慢查询日志中如下

有查询时间，以及慢查询语句，当我们知道慢查询语句了我们就可以针对性优化sql了。而针对sql的优化最多的就是添加索引了

索引

索引用来快速地寻找那些具有特定值的记录，所有MySQL索引都以B-树的形式保存。如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。

索引原理：折半查找，减少全表扫描，索引文件中是一个B_tree ,索引的最大查找数为2^n次方，查找数独比较快

我们可以使用explain分析SQL的执行计划。该执行计划可以模拟SQL优化器执行SQL语句，可以帮助我们了解到自己编写SQL是否有用到索引。1）查看执行计划 语法：explain + SQL语句 eg：explain select * from tb;

id ：编号
select_type ：查询类型
table ：表
type ：类型
possible_keys ：预测用到的索引
key ：实际使用的索引
key_len ：实际使用索引的长度
ref ：表之间的引用
rows ：通过索引查询到的数据量
Extra ：额外的信息

创建索引 create index index_name on table_name(colum_name...);

索引使用注意事项

索引加在常使用字段上，主键除外，主键自带唯一索引，例如商品表查询名字和价格比较多的情况，就在这两个字段上加索引

尽量避免使用子查询,用关联查询替代

select t1.name,(select sex from user u1 where u1.user_id = t1.user_id) as sex from userInfo t1 
改成
select t1.name,u1.sex from userInfo left join user u1 on u1.user_id = t1.user_id;

用in来替换or ，且含In的范围查询，放到where条件的最后，防止索引失效。select * from ep_product where channel_id = 1 or channel_id = 2 改成 select * from ep_product where channel_id in(1,2)

尽量不要使用or，否则索引失效

不要使用like "%%",会全表扫描，但是可以使用 like "_%",不以%开头，会使用到索引进行查找 使用"%%"

使用"_%"

判断是否为null不能用=，要用is null ，=null 不会使用索引，is null会使用索引

避免数据类型不一致,如果设置的是int类型，条件最好是传入int类型，如果传入String类型数据库会先做转换在执行sql

select * from product where id = "1"
select * from product where id = 1

最好不要给数据库留 NULL，尽可能的使用 NOT NULL 填充数据库,可以在 name 上设置默认值 0，确保表中 name 列没有 null 值，然后这样查询：

select id from t where name = 0

分组的时候需要效率比较高就禁止使用排序、在group by 后面加上order by null

select channel_id,count(*) from ep_product  GROUP BY channel_id 
改成
select channel_id,count(*) from ep_product  GROUP BY channel_id order by null

应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。

select * from product where sell_price/2 = 100
改成 
select * from product where sell_price = 100*2

在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

• 复合索引，不要跨列或无序使用(左匹配原则)
• 复合索引，尽量使用全索引匹配，也就是说，你建立几个索引，就使用几个索引

Update 语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志

左关联查询，左表加索引，有关联查询，右表加索引

索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。

其他注意事项

• 避免出现select *
• 关联查询的表最好不超过3张，数据库的性能更加重要，适当考虑规范性就好
• 多表关联查询时，小表在前，大表在后。
• 新增多条数据是避免使用

insert into user values(1,"张三");
  insert into user values(2,"李四");
  使用以下方式可以减少语句解析的操作
  insert into user values(1,"张三"),(2,"李四");

总结

以上是小北给大家总结的部分数据库优化的思路，有什么不对的地方欢迎大家在评论区提出和补充。