MySQL的性能:查询优化

查询操作在实际开发中⽤的最多,程序调⽤的也最多。对于线上的程序,
性能的主要压⼒便来⾃于查询,尤其是复杂查询。

常⽤的查询优化策略有:减少数据访问、重写SQL、重新设计表、添加索
引4种

一.减少数据库的访问

  1. 减少应用程序对数据库的访问:
    数据库和应⽤程序之间是网络通信,每次通信都有 I/O, 所以应该减少
    通信次数。
    能不通信的尽量不通信,能⼀次完成的尽量不要分多次。
    例如: 为应⽤程序增加缓存
  2. 减少实际扫描的记录数:
    查询时扫描的⾏越多,查询越慢,尽量加以控制
    例如: 尽量配合条件去使⽤ limit、offset, 对比⼀下这两句的性能差异:
select * from xxx limit 10 offset 10000; -- 慢
select * from xxx where id > 10000 limit 10; -- 快
  1. 减少数据的获取量:
    例如: 只需要获取几列数据时,不要获取全部列,避免使⽤
select * from ...

二.重写SQL语句

由于复杂查询严重降低了并发性,因此为了让程序更适于扩展,我们可以
把复杂的查询分解为多个简单的查询。⼀般来说多个简单查询的总成本是
小于⼀个复杂查询的。

对于需要进行⼤量数据的操作,可以分批执⾏,以减少对⽣产系统产⽣的
影响,从而缓解复制超时。

由于MySQL连接 (JOIN) 严重降低了并发性,对于⾼并发⾼性能的服务,应
该尽量避免连接太多表,如果可能,对于⼀些严重影响性能的SQL,建议
程序在应⽤层就实现部分连接的功能。

这样的好处是: 可以更⽅便、更⾼效地缓存数据,⽅便迁移表到另外的机
器,扩展性也更好。

连接语句的优化

  1. ON、USING⼦句中的列确认有索引。如果优化器选择了连接的顺序为
    B、A,那么我们只需要在A表的列上创建索引即可。
    例如,对于这个查询语句:
SELECT B.*,A.*FROM B JOIN A ON B.col1=A.col2;

MySQL会全表扫描 B 表,对 B 表的每⼀⾏记录探测 A 表的记录(利⽤ A
表 col2 列上的索引)。

  1. 最好是能转化为 INNER JOIN ,LEFT JOIN 的成本⽐ INNER JOIN
    很多。

  2. 使⽤ EXPLAIN 检查连接,留意 EXPLAIN 输出的 rows 列,如果 rows
    太⾼,比如几千、上万,那么就需要考虑是否索引不佳或连接表的顺序
    不当。

  3. 反范式设计,这样可以减少连接表的个数,加快存取数据的速度。

  4. 考虑在应⽤层实现连接。
    对于⼀些复杂的连接查询,更值得推荐的做法是将它分解为⼏个简单的
    查询,可以先执行查询以获得⼀个较小的结果集,然后再遍历此结果
    集,最后根据⼀定的条件去获取完整的数据,这样做往往是更⾼效的。
    因为我们把数据分离了,更不容易发⽣变化,更⽅便缓存数据,数据也
    可以按照设计的需要从缓存或数据库中进⾏获取。
    例如,对于如下的查询:

SELECT a.* FROM a WHERE a.id B;
如果id=1~15的记录已经被存储在缓存(如Memcached)中了,那么我们
只需要到数据库查询: 
SELECT a.* FROM a WHERE a.id=16;
SELECT a.* FROM a WHERE a.id=17;
⽽且,把IN列表分解为等值查找,往往可以提⾼性能。
  1. ⼀些应⽤可能需要访问不同的数据库实例,这种情况下,在应⽤层实现
    连接将是更好的选择。

Group、Order、Distinct 的优化

GROUP BY 、DISTINCT 、ORDER BY 这⼏类⼦句⽐较类似,GROUP BY
认会进⾏ ORDER BY 排序
优化⽅向如下:

  1. 尽量对较少的⾏进⾏排序
  2. 如果连接了多张表, ORDER BY 的列应该属于连接顺序的第⼀张表。
  3. 利⽤索引排序。
  4. GROUP BY、ORDER BY语句参考的列应该尽量在⼀个表中,如果不在
    同⼀个表中,那么可以考虑冗余⼀些列,或者合并表。
  5. 需要保证索引列和ORDER BY的列相同,且各列均按相同的⽅向进⾏排
    序。

优化子查询

对于数据库来说,在绝⼤部分情况下,连接查询会比子查询更快。
使⽤连接的⽅式,MySQL优化器⼀般可以⽣成更佳的执行计划,可以预先
装载数据,更⾼效地处理查询。
而子查询往往需要运⾏重复的查询,⼦查询⽣成的临时表上也没有索引,
因此效率会更低。
⼀些商业数据库可以智能地识别⼦查询,转化⼦查询为连接查询。但
MySQL对于子查询的优化⼀直不佳,就⽬前的研发实践来说,⼦查询应尽
量改写成JOIN 的写法。

优化limit子句

limit ⼦句的问题主要出在 offset 上, offset的值很⼤,效率就会很差。
⼀般可以进⾏如下调整:

  1. 从应⽤程序上进⾏调整,限制⻚数,只显示前⼏⻚,超过了⼀定的⻚数
    后显示 “获取更多”
  2. 能避免 offset,尽量避免
  3. 不能避免的时候使⽤条件约束查询结果的范围,不要匹配太多

优化IN列表

  • IN 列表不宜过⻓,最好不要超过200。
  • 对于⾼并发的业务,⼩于⼏⼗为佳。

三.重新设计表结构

有些情况下,我们即使是重写SQL或添加索引也是解决不了问题的,这个
时候可能要考虑更改表结构的设计。

⽐如,可以增加⼀个缓存表,暂存统计数据,或者可以增加冗余列,以减
少连接。

优化的主要⽅向是进⾏反范式设计。

四.添加索引

⽣产环境中的性能问题,可能 80% 的都是索引的问题,所以优化好索引,
就已经有了⼀个好的开始。

好的索引能起到事半功倍的效果,但是在实际操作时也要注意⼀些事情,
不合理的 SQL 语句会导致索引失效。

  1. where⼦句中进⾏null值判断的话会导致引擎放弃索引⽽产⽣全表扫
SELECT id FROM table WHERE num is null;

在建⽴数据库的时候因尽量为字段设置默认值, 如int类型可以使⽤0,
varchar类型使⽤ ‘’
当你在指定类型⼤⼩如int(11)时, 其实空间就已经固定了, 即时存的是
null 也是这个⼤⼩

  1. 避免在 where ⼦句中使⽤ != , <> 这样的符号, 否则会导致引擎放弃索
    引⽽产⽣全表扫描
SELECT id FROM table WHERE num != 0;
  1. 避免在where⼦句中 = 的左边使⽤表达式操作或者函数操作
SELECT id FROM table WHERE num / 2 = 1;
-- 应换成
SELECT id FROM table WHERE num = 2;
-- 函数操作也是同理
SELECT id FROM table WHERE SUBSTRING(name, 1, 2) ='wise'; 
-- 这种尽量避免
  1. 避免在 where ⼦句中使⽤ like 模糊查询
-- 放弃索引,全表扫描
select count(1) from `user` where nickname like '%ab';
-- 使⽤索引
select count(1) from `user` where nickname like 'ab%';
©️2020 CSDN 皮肤主题: 游动-白 设计师:上身试试 返回首页