MySQL——SQL语句和索引优化

SQL语句优化

• 避免嵌套语句（子查询）

  • 尽量用 join 代替子查询
  • 连接（JOIN）比子查询更有效率一些，是因为 MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作

  --子查询
  SELECT * FROM customerinfo 
  WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )
  
  --连接查询
  SELECT * FROM customerinfo 
  LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo. 
  CustomerID 
  WHERE salesinfo.CustomerID IS NULL
  

• 避免多表查询（复杂查询简单化）

  • 尽量将多表查询分解成单表查询
  • 将查询分解后，执行单个查询可以减少锁的竞争。
  • 在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。

select * from tag
join tag_post on tag_post.tag_id=tag.id
join post on tag_post.post_id=post.id
where tag.tag=’mysql’;

Select * from tag where tag=’mysql’;
Select * from tag_post where tag_id=1234;
Select * from post where id in(123,456,567,9989,8909);

• 正确使用exists和in

  • exists 适合子查询中表数据大于外查询表中数据的业务场景
  • in 适合外部表数据大于子查询的表数据的业务场景

• 尽量使用数字型字段

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

• 尽可能的使用 varchar/nvarchar 代替 char/nchar

  • 因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

索引优化

1.适当建立索引

• 创建并使用自增数字来建立主键索引

  • 效率更高，比较起来更快

• 为经常作为where条件的字段建立索引

• 添加索引的字段尽可能的保持唯一性

• 可考虑使用联合索引并进行索引覆盖

  • 联合索引的索引覆盖 (多个字段组合成了⼀个联合索引,在查询时,所要的字段和查询条件中的索引是⼀致)

• 建立适当数量的索引

  • 每建立一个索引都会建立一棵B+树，并且需要维护，这是狠费性能和存储空间的。

2.合理使用索引

• 索引失效情况(创建了索引却没用上索引)

  1. 在列上使用函数或进行运算

  --不要在列上使用函数，这将导致索引失效而进行权标扫描。
  select * from news where year(publish_time)=2017
  
  --改造
  select * from news where publih_time = '2017-01-01'
  

  --不要在列上进行运算，这也将导致索引失效而进行全表扫描
  select * from news where id/100 = 1
  
  --改造
  select * from news where id = 1*100
  

  2. 隐式转换可能影响索引失效

  --使用正常查询
  select * from users where name = '123456'
  
  --出现了隐式转换,导致索引失效
  select * from users where name = 123456
  

  3. ike语句的索引失效问题

  --当在尾部使用通配符时可以使用索引
  select * from users where name like 'zhang%';
  
  --当在头部(左侧)使用通配符时，则导致索引失效
  select * from users where mame like '%zhang%';
  select * from users where mame like '%zhang';
  

• 复合索引的使用（联合、组合）

1. 多个单列索引并不是最佳索引

• MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引，因此，为多个列创建单列索引并不能提高MySQL的查询性能。
• 事实上，MySQL 只能使⽤⼀个单列索引。使用多个单列索引，既浪费了空间，⼜没有提⾼性能（因为需要回⾏） 为了提⾼性能，可以使用复合索引并保证列都被索引覆盖。

2. 符复合索引的最左前缀原则

• 复合索引的最左前缀原则：查询条件中使用了复合索引的第一个字段，索引才会被使用。因此，在复合索引中索引列的顺序至关重要。如果不是按照索引的最左列开始查找，则无法使用索引。（MySQL会首先根据联合索引中最左边的、也就是第一个字段进行排序，在第一个字段排序的基础上，再对联合索引中后面的第二个字段进行排序，依此类推。）

3. 尽可能达成索引覆盖

如果一个索引包含所有需要的查询字段的值，直接根据索引的查询结果返回数据，而无需读表，能够极大的提高性能。因此，可以定义一个让索引包含的额外的列，即使这个列对于索引来说是毫无作用的。

慢查询

MySQL的慢查询，全名是慢查询日志

是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阀值的语句。

默认情况下不启动这个日志，需要手动设置参数来启动。

如果不是调优需要的话，一般不建议启动，开启慢查询日志或多或少会带来一定的性能影响

--查询慢查询日志的开启状态和慢查询日志储存的位置
show variables like '%quer%';

--设置慢查询存储的方式，可设置为table或者file,如果是table则慢查询信息会保存到mysql库下的slow_log表中，file则会保存到文件中
set globle log_output = file;

--临时开启
set global slow_query_log = on; 
--临时关闭
set global slow_query_log = off
--设置阀值(秒)
set long_query_time = 1;

EXPLAIN关键字

explain（执行计划），使用explain关键字可以模拟优化器执行sql查询语句，从而知道MySQL是如何处理sql语句。explain主要用于分析查询语句或表结构的性能瓶颈。

属性	含义
id	在⼀个⼤的查询语句中每个SELECT关键字都对应⼀个唯⼀的id
select_type	SELECT关键字对应的那个查询的类型
table	表名
partitions	匹配的分区信息
type	针对单表的访问⽅法
possible_keys	可能⽤到的索引
key	实际上使⽤的索引
key_len	实际使⽤到的索引⻓度
ref	当使⽤索引列等值查询时，与索引列进⾏等值匹配的对象信息
rows	预估的需要读取的记录条数
filtered	某个表经过搜索条件过滤后剩余记录条数的百分⽐
Extra	⼀些额外的信息