我们在完成项目接口开发过程中,一定会涉及到对数据库的增删改查,SQL语句肯定是绕不开的。而SQL的优化一般都体现在查询语句上,在写完SQL后,闲暇之余一定要反思自己写的SQL语句,不断的剖析优化,从而养成在写SQL语句的时候就规避掉一些重大问题。遵循的最基本的原则是:先完成,后优化。有些攻城狮们一味的满脑子都是我要优化我要优化,可是sql都是错的,优化何从谈起,所以不要为了优化而优化,一定要保证功能首先是可行的。以下是我在项目开发中总结的几点优化方向,希望对一些也在研究该方向的攻城狮们一个指引方向。
字段值只有1-2种的可能的情况下,不要用索引。
有时候我们在设计表的时候,总有些字段其实你在设计初期都知道它肯定就那么一两个值,这时候这个字段就不要设置成索引了,没啥必要,都一样效果。比如楼主有些表会有enable字段(是否启用),总共结果你都知道就2个值(0-不启用,1-启用)就不要设置索引了。
经常用来查询条件的字段建立索引。
这个就不用楼主多说了,比如一个订单表,很多地方sql语句都会用到订单状态(state|status)这个字段来查询,这时候这种经常做查询条件的字段要建立索引。
拿来join的字段加索引,且类型要一致。
两个表关联查询,关联字段要加索引的。
select * from table1 t1,table t2 where t1.id=t2.t1Id
这里面的t1Id就得加索引,id是主键索引。关键是一定要看清楚,id和t1Id两个字段类型一定要一样,别一个是int类型,另一个是varchar类型,我可不敢保证优化器能干出什么事情来。
确定需要的是一条数据的时候 要习惯加 limit 1
有时候我们的业务场景肯定是需要一条记录时,加上LIMIT 1的时候会增加性能,引擎会在找到一条数据后停止搜索,不会再继续往下找了。
如果查询的字段只是某些字段,尽量不要使用select*
假设我们查询一个学生表,只是需要学生姓名和学生年龄。尽量写成以下形式,
#建议写法
select name,age from student
#尽量不要使用带 *
select * from student
楼主当初在数据量大的表中测试过,确实还是有差异的。
查询是否存在记录的时候,只关心存不存在时,建议用select 1
有时候某一个逻辑可能是判断该账号是否存在这种情况。不用select *,用select 1也能满足。程序中用一个整型字段去接收,结果要么是1要么是NULL。
select 1 from user_info where account='前端传递过来的账号'
少用 order by rand()形式
楼主以前有一个需求就是每次查询都要随机打乱数据,然后返回指定N行。这时候我年少无知,用的就是 order by rand(),初期数据量少的时候,还感觉不到什么,慢慢当数据量多的时候,性能出现了指数级的下降。rand()这函数很耗CPU,慎用慎用!!,后面楼主用了另一种方式解决,体验会好非常多,具体思路方案如下:
#先查询该表的总量
select count(*) from table1
#在程序中随机出一个值,假设随机出数字为5
$randNum=5
#在用limit去执行sql语句
select * from table1 limit $randNum,1
尽量字段设置为 NOT NULL
除非你有一个特别的原因使用NULL值,否则的话你应该要让你的字段保持NOT NULL。NULL需要额外的空间,并且在进行比较的时候,你的程序会更复杂。当然不是强制让你不用NULL,还是得看业务唱场景。
主表大的 从表数据量少的时候用in关键字,反过来用exist关键字
这个具体说的话,可能也能说长篇大论,这里就不细细去说。记住我觉得就好了,自己有空的时候可以去试验一下。
- 假设A表是大表(数据量非常大的表),B表是小表(数据量小的表),则建议用
in
关键字
select * from A where id in (select aid from B where ...)
- 假设A表是小表(数据量小的表),B表是大表(数据量非常大的表),则建议用
EXISTS
关键字
select * from A where exists(select 1 from B where A.id=B.aid)
用 not exists 替换 not in
主要是not in 带来两个问题,查询结果不准确
和性能低下
。楼主个人感觉not in 很吃IO。
尽量少用开头模糊查询,不走索引
#走索引
select * from table where name LIKE '张%'
#不走索引
select * from table where name LIKE '%张%'
设置索引的字段尽量不参与计算或者函数操作
在写sql的时候尽量不要让字段参与计算,会导致放弃走索引。如下面例子,score字段设置了索引。
#不走索引
select * from table where score/3=9
#走索引
select * from table where score=3*9
where条件左右两边数据类型要一致
有些人数据字段明明设置成int类型,非要在写sql的时候写成等于字符串的形式,导致字段不走索引,排查还得费点时间。
#假设age是int 类型
age(int 11)
#查询学生中年龄是11岁的
#错误写法
select * from student where age='11'
#正确写法
select * from student where age=11
垂直分割
大字段尽量分割出到另外的表,比如商品表中有商品标题、商品价格、商品数量、商品描述等字段,而商品描述这个字段是一个大字段,里面存储非常大的数据,这时候就要考虑业务场景中是否经常会查询到该字段,如果不会的话,可以建议新建一个拓展表(ext),将这些大字段表放在拓展表中,有需要的时候再关联出来。
适当的加一些冗余字段
如果去严格遵循数据库范式的话,我只能说会比较累。有些时候,我们业务中还是需要适当的冗余字段,这样可以不用关联很多表,在业务表中就可以带出来对应的列,唯一的问题只不过就是更新的时候要记得更新业务表,否则会出现数据不一致的情况。但是适当在合适的地方加冗余字段,往往会出现事半功倍的效果。
学会使用explain解释计划
强烈建议学习一下,非常有用。explain是让你知道MySQL是如何处理你的SQL语句。可以帮我们分析查询语句或者表结构的性能瓶颈。id,select_type,possible_keys,key,type,rows等参数。