SQL优化

SQL优化

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

SELECT * FROM user  >>  SELECT id,name FROM user

* 当时用select *查询时，有可能不会用到索引，就会全表扫描
* 用具体字段，可以节省资源、较少网络开销

2. 避免在where字句中使用or来连接条件，具体如下

错误示范：
SELECT * FROM user WHERE id=1 OR salary=30000
优化后：
SELECT * FROM user WHERE id=1
UNION ALL
SELECT * FROM user WHERE salary=30000

• 使用or可能会使索引失效，从而全表扫描

3. 使用varchar代替char

错误示范：
`deptname` char(100) DEFAULT NULL COMMENT '部门名称'
优化后：
`deptname` varchar(100) DEFAULT NULL COMMENT '部门名称'

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

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

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

5. 查询尽量避免返回大量数据
6. 使用explain分析SQL执行计划

EXPLAIN
SELECT * FROM user WHERE id=1

7. 是否使用了索引及其扫描类型

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

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

8. 优化like语句

错误示范：
EXPLAIN
SELECT id,name FROM user WHERE NAME LIKE '%1'
EXPLAIN
SELECT id,name FROM user WHERE NAME LIKE '%1%'
优化后：
EXPLAIN
SELECT id,name FROM user WHERE NAME LIKE '1%'