MySQL的性能：查询优化

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

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

一.减少数据库的访问

1. 减少应用程序对数据库的访问：
   数据库和应⽤程序之间是网络通信，每次通信都有 I/O, 所以应该减少
   通信次数。
   能不通信的尽量不通信，能⼀次完成的尽量不要分多次。
   例如: 为应⽤程序增加缓存
2. 减少实际扫描的记录数：
   查询时扫描的⾏越多，查询越慢，尽量加以控制
   例如: 尽量配合条件去使⽤ limit、offset, 对比⼀下这两句的性能差异:

select * from xxx limit 10 offset 10000; -- 慢
select * from xxx where id > 10000 limit 10; -- 快

3. 减少数据的获取量：
   例如: 只需要获取几列数据时，不要获取全部列，避免使⽤

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 列上的索引)。

2. 最好是能转化为 INNER JOIN ，LEFT JOIN 的成本⽐ INNER JOIN ⾼
   很多。

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

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

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

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列表分解为等值查找，往往可以提⾼性能。

6. ⼀些应⽤可能需要访问不同的数据库实例，这种情况下，在应⽤层实现
   连接将是更好的选择。

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 也是这个⼤⼩

2. 避免在 where ⼦句中使⽤ != , <> 这样的符号, 否则会导致引擎放弃索
   引⽽产⽣全表扫描

SELECT id FROM table WHERE num != 0;

3. 避免在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'; 
-- 这种尽量避免

4. 避免在 where ⼦句中使⽤ like 模糊查询

-- 放弃索引，全表扫描
select count(1) from `user` where nickname like '%ab';
-- 使⽤索引
select count(1) from `user` where nickname like 'ab%';