众所周知，sql从数据库中获取数据的方式主要有通过索引扫描和全表扫描两种方式获取目标数据；当数据量不大的时候，两者在效率上可能没有区别，但是当数据量达到一定规模的时候，索引扫描明显比全表扫描的效率高出很多； 在此也并不说建立索引就是万能的，过多的索引浪费存储空间，加大了数据之间结构的复杂性，降低了插入，更新的效率；因此要根据实际应用场景选择合适的方式，话不多说，下面总结的几条经验如下：
尽量少使用select * from table； 因为这会导致扫描全表；
避免在where语句中对字段值为null的进行判断，可设置默认值0代替null；这样会导致查询放弃索引扫描而走全表扫描；
尽量对where 和order by，group by 设计的列建立索引；
慎用not in 和 in，会导致全表索引；
在where语句中尽量少出现 != < >；
对于where语句中条件为连续数据时，使用between，而不是in ；
将 select fields from table where name='**' or name ="&&" 改为
select fields from table where name='**' union all select fields from table where name='&&'
对于select fields from table where name like '%dfb%' 改为使用建立全文索引；
在where中条件尽量保持明确，避免使用代表式或是函数作为条件；
select fields from table where age/2=5；
select fields from table where substring(name,1,3) ='bcd' # 以bcd开头的name
用exists 代替 in；
select fields from table_a where exists(select fields from table_b where num=table_a.num)
尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够；
如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定
尽量保持一张表字段数量不要太多，可以考虑分表存储数据，然后通过join获取数据；
在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。
避免一次性向客户端返回大量数据；
并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引；
Ps: 建立全文索引的方式：
首先mysql的引擎innoDB是不支持使用全文索引的，所以如果是innoDB，先修改引擎类型：
alter table table_name engine=MyISAM
其次建立索引：alter table table_name add fulltext index(字段1，字段2, ....)
最后查询：select * from table_name where match(字段1，字段2,....) against(字段名)
match ... against ...:相关性排序，由高到低，match(字段顺序应与 fulltext中相同，不然会报错)
原文链接: https://www.onexing.cn
网友评论