SQL优化
查询SQL尽量不要使用select * ,而是使用具体字段,具体如下
SELECT * FROM user >> SELECT id,name FROM user
* 当时用select *查询时,有可能不会用到索引,就会全表扫描
* 用具体字段,可以节省资源、较少网络开销
避免在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
使用varchar代替char
错误示范:
`deptname` char(100) DEFAULT NULL COMMENT '部门名称'
优化后:
`deptname` varchar(100) DEFAULT NULL COMMENT '部门名称'
char按声明大小存储,不足补空格 varchar变长字段按数据内容实际长度存储,可以节省空间 对于查询来说,在相对较小的字段内搜索,效率更高
尽量使用数值替代字符串类型
主键(id) :primary key优先使用数值类型int,tinyint性别(sex) :0-代表女,1-代表男;数据库没有布尔类型,mysql推荐使用tinyint支付方式(payment) :1-现金、2-微信、3-支付宝、4-信用卡、5-银行卡服务状态(state) :1-开启、2-暂停、3-停止商品状态(state) :1-上架、2-下架、3-删除
查询尽量避免返回大量数据 使用explain分析SQL执行计划
EXPLAIN
SELECT * FROM user WHERE id=1
是否使用了索引及其扫描类型
ALL 全表扫描,没有优化,最慢的方式index 索引全扫描range 索引范围扫描,常用语<,<=,>=,between等操作ref 使用非唯一索引扫描或唯一索引前缀扫描,返回单条记录,常出现在关联查询中eq_ref 类似ref,区别在于使用的是唯一索引,使用主键的关联查询const 当查询是对主键或者唯一键进行精确查询,系统会把匹配行中的其他列作为常数处理null MySQL 不访问任何表或索引,直接返回结果System 表只有一条记录(实际中基本不存在这个情况)
性能排行:
System > const > eq_ref > ref > range > index > ALL
优化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%'