MySQL在大数据、高并发场景下的SQL语句优化

转载自：https://segmentfault.com/a/1190000007311574

本文主要针对中小型应用或网站，重点探讨日常程序开发中SQL语句的优化问题，所谓“大数据”、“高并发”仅针对中小型应用而言，专业的数据库运维大神请无视。以下实践为个人在实际开发工作中，针对相对“大数据”和相对“高并发”场景的一些应对策略，部分措施并没有经过严格的对比测试和原理分析，如有错漏欢迎各种批评指教。

一、减少查询的影响结果集，避免出现全表扫描

影响结果集是SQL优化的核心。影响结果集不是查询返回的记录数，而是查询所扫描的结果数。通过Explain或Desc分析SQL，rows列的值即为影响结果集（还可以通过慢查询日志的Rows_examined后面的数字得到）。

以下是我常用的一些SQL优化策略：
1. 去掉不必要的查询和搜索。其实在项目的实际应用中，很多查询条件是可有可无的，能从源头上避免的多余功能尽量砍掉，这是最简单粗暴的解决方案。

1. 合理使用索引和复合索引。建索引是SQL优化中最有效的手段。查找、删除、更新以及排序时常用的字段可以适当建立索引。不过要注意，单条查询不能同时使用多个索引，只能使用一个索引。查询条件较多时，可以使用多个字段合并的复合索引。切记，使用复合索引时，查询条件的字段顺序需要与复合索引的字段顺序保持一致。

2. 谨慎使用not in等可能无法使用索引的条件。索引也不是什么时候都可以发挥作用的，当出现”not in”，”!=”，”like ‘%xx%’”，”is null”等条件时，索引是无效的。使用这些条件的时候，请放到能有效使用索引的条件的右边。设计表结构时，个人建议尽可能用int类型代替varchar类型，int类型部分时候可以通过大于或小于代替”!=”等条件，同时也方便满足一些需要按类型排序的需求，至于可读性的问题，完善好数据库设计文档才是明智的选择。同时建议把所有可能的字段设置为”not null”，并设置默认值，避免在where字句中出现”is null”的判断。

3. 不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将无法正确使用索引。尽可能少用MySQL的函数，类似Now()完全可以通过程序实现并赋值，部分函数也可以通过适当的建立冗余字段来间接替代。

4. 在where条件中使用or，可能导致索引无效。可用 “union all” 或者 “union” （会过滤重复数据，效率比前者低） 代替，或程序上直接分开两次获取数据再合并，确保索引的有效利用。

5. 不使用select * ，倒不是能提高查询效率，主要是减少输出的数据量，提高传输速度。

6. 避免类型转换，这里所说的“类型转换”是指where子句中出现字段的类型和传入的参数类型不一致的时候发生的类型转换。

7. 分页查询的优化。页数比较多的情况下，如limit 10000,10 影响的结果集是10010行，查询速度会比较慢。推荐的解决方案是：先只查询主键select id from table where .. order by .. limit 10000,10（搜索条件和排序请建立索引），再通过主键去获取数据。

8. 统计相关的查询。影响结果集往往巨大，且部分SQL语句本身已经难以优化。因此，应避免在业务高峰期执行统计相关的查询，或者仅在从库中执行统计查询。部分统计数据，可以通过冗余的数据结构保存，同时建议把数据先保存在内存、缓存中（如redis），再按一定策略写入数据库。

二、不使用任何连表查询，通过分库和分表实现负载均衡

随着数据量的增加，连表操作往往会导致影响结果集大增，从SQL优化的层面已经解决不了问题了。

此时，分库和分表是解决数据库性能压力的最优选择（具体分库和分表的方案通常结合实际业务的应用场景来确定，此处略过）。这里重点谈，如何更好的实现或者过渡到分库、分表的分布式数据库架构。

核心点就是必须先去除数据表之间的关联，即不用外键，不使用任何连表查询。为了确保不进行连表操作，在设计数据库表结构的时候，就需要设计适度冗余的字段来达到不连表的目的。

对于一些操作日志、支付记录等，设计一些记录用户信息的字段，个人认为其实不能算冗余，毕竟用户信息往往会更改，但是这种类似操作日志的表确实是需要记录用户操作时的信息，并且不需要在用户更新信息时同步更新。

实际开发中，为了实现不进行连表而冗余的字段，往往是需要在原表更新数据的时候同步更新冗余字段的数据的，如果应用层没有对数据表操作做合理封装，这往往是个棘手的问题，也不方便维护。

当然，现在主流的应用框架，一般采用orm的方式处理数据表，所以问题不大。相反，不连表事实上还可以提高开发效率，比如通过用户ID获取用户姓名操作，如果不连表就可以确保各个业务模块都通过同样的方式去获取用户姓名，调用同一个封装好的方法，这样，就能很方便的统一在应用层加入缓存机制或添加统一的业务逻辑。

同时如果要对用户表进行分库分表，通过应用层程序就可以简单平滑的实现。

三、使用Innodb

关于Innodb和Myisam对比，我就不多说了。Myisam的表级锁是致命问题，考虑到MySQL已经默认使用Innodb作为数据库引擎，个人建议大部分情况可以直接使用Innodb，其他引擎这里就不详细讨论了。

四、使用缓存

1) 尽可能在程序上实现常用数据的缓存，目前主流的应用框架应该都能快速实现缓存的需求。如果在程序上没有实现数据缓存，开启数据库的query cache也是缓解数据库压力的方式之一，如果确认使用，记得定时清理碎片flush query cache。

五、服务器相关优化

MySQL服务配置以及分布式架构的实现，请根据实际应用场景和业务需求定制，非本文重点，不做深入探讨。