MySQL 编写SQL注意什么------SQL语句优化

MySQL 设计数据表和编写SQL注意什么-------SQL语句优化

• 对查询进行优化，应尽量避免全表扫描，首先应该需要考虑到where 和order by 所涉及到的列建立索引

  #score id这两列建索引
  select id,username,password,status,scoer from user where score=40 order by id;
  

• 尽量不要在where句子中使用!=或<>操作符，否则索引失效而进行全表扫描

• 尽量不要在where字句中使用null值判断，否则导致索引失效而进行全表扫描

  select id,username,password,status,scoer from user where score is null;
  #可以在score设置一个默认值0，确保表中score没有null值，然后可以这样查询：
  select id,username,password,status,scoer from user where score =0;
  

• 尽量不要在where字句中使用or连接条件，否则导致索引失效而进行全表扫描

  select id,username,password,status,scoer from user where score =10 or score =30 ;
  #可以使用 union all
  select id,username,password,status,scoer from user where score =10
  union all
  select id,username,password,status,scoer from user where score =30;
  

• 使用 like '%dd%'也会导致全表扫描

  select id,username,password,status,scoer from user where username like '%dhh%';
  #想要提高查询效率，可以考虑全文检索
  

• in和not in也要慎用，否则会导致全表扫描

  select id,username,password,status,scoer from user where id in (1,2,3);
  --对于连续的数值，能用bewweet就不用in
  select id,username,password,status,scoer from user where id between 1 and 3;
  

• 尽量不要在where字句中使用表达式操作，这样会导致索引失效，而进行全表扫描

  select id,username,password,status,scoer from user where score/3 =10;
  --应该改为
  select id,username,password,status,scoer from user where score =30;
  

• 尽量不要在where字句中对字段进行函数操作，这样会导致索引失效而进行全表扫描

  select id,username,password,status,scoer from user where substring(username,1,3)='abc';
  select id,username,password,status,scoer from user where datediff(day,create_day,'2022-06-15')=0;
  ---应改为
  select id,username,password,status,scoer from user where username like 'abc%';
  select id,username,password,status,scoer from user where create_day>='2022-06-15' and create_day<'2022-06-16';
  

• 如果在where字句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但是优化程序不能将访问计划的选择推迟到运行时。它必须在编译时进行选择。然而，如果在编译的时候建立访问计划，变量值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

  select id,username,password,status,scoer from user where score =@score;
  ---可以修改为强制查询使用索引：
  select id,username,password,status,scoer from user  with(index(索引名)) where score =@score;
  

• 尽量不要在where字句中的“=”左边进行函数、算术运算、或者是表达式运算，否则有可能无法正确使用索引

• 在使用索引字段作为条件时，如果索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用；还要注意的是：应尽可能的让字段顺序与索引顺序相一致

• 很多时候可以使用exists代替in是一个好的选择

  select id,username,password,status,scoer from user where id in (select id from order);
  ---可以换成
  select id,username,password,status,scoer from user where exists (select 1 from order where id=user.id);
  

• 并不是所有索引对查询都有效，SQL是根据表中数据来进行优化的，当索引有大量数据重复的时候，SQL查询可能不会去利用索引，如一表中有字段sex、male、female几乎个一半，那么即使在sex上建了索引也对查询效率起不了作用

• 索引并不是越多越好，索引虽然可以提高查询效率，但是同时会降低了insert和update的效率；因为insert或者update时有可能会重建索引，所以怎么建立索引需要三思，视情况而定；

• 尽量使用数字类型的字段建立索引，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储的开销。这是因为引擎在处理查询和连接时会逐个字符串的每一个字符，而对于数值类型而言只需要比较一次就够了

• 设计数据表的时候尽量使用varchar\nvarchar 代替cahr \nchar；首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高效些

• 最好不要是用select * from user，需要改为具体的字段代替，不要反回用不到的字段

• 尽量使用变量来代替临时表；如果表变量包含大量数据，需要主要索引非常有限（只有主键索引）

• 避免频繁创建和删除临时表，以减少系统表资源的消耗

• 尽量避免向用户返回大量的数据，若数据量过大，应该考虑响应的需求调整

• 尽量避免大事务操作，提高系统并发能力