开发者俱乐部

标题: MySQL在大数据、高并发场景下的SQL语句优化和"最佳实践" [打印本页]

作者: xman 时间: 2016-11-10 08:17
标题: MySQL在大数据、高并发场景下的SQL语句优化和"最佳实践"

　　本文主要针对中小型应用或网站，重点探讨日常程序开发中SQL语句的优化问题，所谓"大数据"、"高并发"仅针对中小型应用而言，专业的数据库运维大神请无视。以下实践为个人在实际开发工作中，针对相对"大数据"和相对"高并发"场景的一些应对策略，部分措施并没有经过严格的对比测试和原理分析，如有错漏欢迎各种批评指教。
　　减少查询的影响结果集，避免出现全表扫描。
　　影响结果集是SQL优化的核心。影响结果集不是查询返回的记录数，而是查询所扫描的结果数。通过Explain或Desc分析SQL，rows列的值即为影响结果集(还可以通过慢查询日志的Rows_examined后面的数字得到)。
　　以下是我常用的一些SQL优化策略：

　　不使用任何连表查询，通过分库和分表实现负载均衡。
　　随着数据量的增加，连表操作往往会导致影响结果集大增，从SQL优化的层面已经解决不了问题了。
　　此时，分库和分表是解决数据库性能压力的最优选择(具体分库和分表的方案通常结合实际业务的应用场景来确定，此处略过)。这里重点谈，如何更好的实现或者过渡到分库、分表的分布式数据库架构。
　　核心点就是必须先去除数据表之间的关联，即不用外键，不使用任何连表查询。为了确保不进行连表操作，在设计数据库表结构的时候，就需要设计适度冗余的字段来达到不连表的目的。
　　对于一些操作日志、支付记录等，设计一些记录用户信息的字段，个人认为其实不能算冗余，毕竟用户信息往往会更改，但是这种类似操作日志的表确实是需要记录用户操作时的信息，并且不需要在用户更新信息时同步更新。
　　实际开发中，为了实现不进行连表而冗余的字段，往往是需要在原表更新数据的时候同步更新冗余字段的数据的，如果应用层没有对数据表操作做合理封装，这往往是个棘手的问题，也不方便维护。
　　当然，现在主流的应用框架，一般采用orm的方式处理数据表，所以问题不大。相反，不连表事实上还可以提高开发效率，比如通过用户ID获取用户姓名操作，如果不连表就可以确保各个业务模块都通过同样的方式去获取用户姓名，调用同一个封装好的方法，这样，就能很方便的统一在应用层加入缓存机制或添加统一的业务逻辑。
　　同时如果要对用户表进行分库分表，通过应用层程序就可以简单平滑的实现。
　　使用Innodb。
　　关于Innodb和Myisam对比，我就不多说了。Myisam的表级锁是致命问题，考虑到MySQL已经默认使用Innodb作为数据库引擎，个人建议大部分情况可以直接使用Innodb，其他引擎这里就不详细讨论了。
　　使用缓存。
　　1) 尽可能在程序上实现常用数据的缓存，目前主流的应用框架应该都能快速实现缓存的需求。如果在程序上没有实现数据缓存，开启数据库的query cache也是缓解数据库压力的方式之一，如果确认使用，记得定时清理碎片flush query cache。
　　服务器相关优化
　　MySQL服务配置以及分布式架构的实现，请根据实际应用场景和业务需求定制，非本文重点，不做深入探讨。

欢迎光临开发者俱乐部 (http://xodn.com/)