SQL优化
- 查询SQL尽量不要使用select *,而是具体字段
反例:SELECT * FROM student
正例:SELECT id,NAME FROM student
理由:
字段多时,大表能达到100多个字段甚至达200多个字段
只取需要的字段,节省资源、减少网络开销
select * 进行查询时,很可能不会用到索引,就会造成全表扫描
- 避免在where子句中使用or来连接条件
理由: 使用or可能会使索引失效,从而全表扫描
对于or没有索引的salary这种情况,假设它走了id的索引,但是走到salary查询条件时,它还得全表扫描。也就是说整个过程需要三步:全表扫描+索引扫描+合并。如果它一开始就走全表扫描,直接一遍扫描就搞定。虽然mysql是有优化器的,处于效率与成本考虑,遇到or条件,索引还是可能失效的 - 使用varchar代替char
理由: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/system
单条记录,系统会把匹配行中的其他列作为常数处理,如主键或唯一索引查询null
MySQL不访问任何表或索引,直接返回结果
key:
- 真正使用的索引方式
- 创建name字段的索引
ALTER TABLE student ADD INDEX index_name (NAME)
- 优化like语句
模糊查询,程序员最喜欢的就是使用like,但是like很可能让你的索引失效
反例:
EXPLAIN
SELECT id,NAME FROM student WHERE NAME LIKE '%1'
EXPLAIN
SELECT id,NAME FROM student WHERE NAME LIKE '%1%'
正例:
EXPLAIN
SELECT id,NAME FROM student WHERE NAME LIKE '1%'
- 字符串怪现象
反例:
#未使用索引
EXPLAIN
SELECT * FROM student WHERE NAME=123
正例:
#使用索引
EXPLAIN
SELECT * FROM student WHERE NAME='123'
理由:
为什么第一条语句未加单引号就不走索引了呢?这是因为不加单引号时,是字符串跟数字的比较,它们类型不匹配,MySQL会做隐式的类型转换,把它们转换为数值类型再做比较
- 索引不宜太多,一般5个以内
- 索引并不是越多越好,虽其提高了查询的效率,但却会降低插入和更新的效率
- 索引可以理解为一个就是一张表,其可以存储数据,其数据就要占空间
- 再者,索引表的一个特点,其数据是排序的,那排序要不要花时间呢?肯定要
- insert或update时有可能会重建索引,如果数据量巨大,重建将进行记录的重新排序,所以建索引需要慎重考虑,视具体情况来定
- 一个表的索引数最好不要超过5个,若太多需要考虑一些索引是否有存在的必要
-
索引不适合建在有大量重复数据的字段上
如性别字段。因为SQL优化器是根据表中数据量来进行查询优化的,如果索引列有大量重复数据,Mysql查询优化器推算发现不走索引的成本更低,很可能就放弃索引了。 -
where限定查询的数据
理由:
需要什么数据,就去查什么数据,避免返回不必要的数据,节省开销 -
避免在where中对字段进行表达式操作
理由:
- SQL解析时,如果字段相关的是表达式就进行全表扫描
- 避免在where子句中使用!=或<>操作符
应尽量避免在where子句中使用!=或<>操作符,否则引擎将放弃使用索引而进行全表扫描。记住实现业务优先,实在没办法,就只能使用,并不是不能使用。如果不能使用,SQL也就无需支持了。
理由:
使用!=和<>很可能会让索引失效
去重distinct过滤字段要少
- 索引失效
带distinct
的语句占用cpu时间高于不带distinct的语句。因为当查询很多字段时,如果使用distinct,数据库引擎就会对数据进行比较,过滤掉重复数据,然而这个比较、过滤的过程会占用系统资源,如cpu时间 - inner join 、left join、right join,优先使用inner join
三种连接如果结果相同,优先使用inner join,如果使用left join左边表尽量小
inner join 内连接,只保留两张表中完全匹配的结果集
left join会返回左表所有的行,即使在右表中没有匹配的记录
right join会返回右表所有的行,即使在左表中没有匹配的记录
理由:
如果inner join是等值连接,返回的行数比较少,所以性能相对会好一点
同理,使用了左连接,左边表数据结果尽量小,条件尽量放到左边处理,意味着返回的行数可能比较少。这是mysql优化原则,就是小表驱动大表,小的数据集驱动大的数据集,从而让性能更优