SQL优化

为什么要对SQL进行优化

我们开发项目上线初期，由于业务数据量相对较少，一些SQL的执行效率对程序运行效率的影响不太明显，而开发和运维人员也无法判断SQL对程序的运行效率有多大，故很少针对SQL进行专门的优化，而随着时间的积累，业务数据量的增多，SQL的执行效率对程序的运行效率的影响逐渐增大，此时对SQL的优化就很有必要。

基础Sql优化

查询SQL尽量不要使用select *，而是具体字段

反例：

SELECT * FROM student

正例：

SELECT id,NAME FROM student

• 字段多时，大表能达到100多个字段甚至达200多个字段
• 只取需要的字段，节省资源、减少网络开销
• select * 进行查询时，很可能不会用到索引，就会造成全表扫描

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

如查询id为1或者薪水为3000的用户：
反例：

SELECT * FROM student WHERE id=1 OR salary=30000

正例：
使用union all

SELECT * FROM student WHERE id=1
UNION ALL
SELECT * FROM student WHERE salary=30000

• 使用or可能会使索引失效，从而全表扫描
• 对于or没有索引的salary这种情况，假设它走了id的索引，但是走到salary查询条件时，它还得全表扫描。也就是说整个过程需要三步：全表扫描+索引扫描+合并。如果它一开始就走全表扫描，直接一遍扫描就搞定。虽然mysql是有优化器的，处于效率与成本考虑，遇到or条件，索引还是可能失效的

使用varchar代替char

反例：

`deptname` char(100) DEFAULT NULL COMMENT '部门名称'

正例：

`deptname` varchar(100) DEFAULT NULL CMMENT '部门名称'

• varchar变长字段按数据内容实际长度存储，存储空间小，可以节省存储空间
• char按声明大小存储，不足补空格
• 其次对于查询来说，在一个相对较小的字段内搜索，效率更高

尽量使用数值替代字符串类型

• 主键（id）：primary key优先使用数值类型int，tinyint
• 性别（sex）：0-代表女，1-代表男；数据库没有布尔类型，mysql推荐使用tinyint
• 支付方式（payment）：1-现金、2-微信、3-支付宝、4-信用卡、5-银行卡
• 服务状态（state）：1-开启、2-暂停、3-停止
• 商品状态（state）：1-上架、2-下架、3-删除

查询尽量避免返回大量数据

如果查询返回数据量很大，就会造成查询时间过长，网络传输时间过长。同时，大量数据返回也可能没有实际意义。如返回上千条甚至更多，用户也看不过来。
通常采用分页，一页习惯10/20/50/100条。

使用explain分析你SQL执行计划

SQL很灵活，一个需求可以很多实现，那哪个最优呢？
SQL提供了explain关键字，它可以分析你的SQL执行计划，看它是否最佳。Explain主要看SQL是否使用了索引。

EXPLAIN SELECT * FROM student WHERE id=1

返回结果：

TYPE

• ALL 全表扫描，没有优化，最慢的方式
• index 索引全扫描
• range 索引范围扫描，常用语<，<=，>=，between等操作
• ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
• eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询
• const 当查询是对主键或者唯一键进行精确查询，系统会把匹配行中的其他列作为常数处理
• null MySQL不访问任何表或索引，直接返回结果
• System 表只有一条记录(实际中基本不存在这个情况)

性能排行：
System > const > eq_ref > ref > range > index > ALL