mysql大数据表优化,mysql 大表查询优化

　　来源：知乎

　　版权归作者所有。商业转载请联系作者授权，非商业转载请注明出处。

　　很多人的第一反应就是各种分段；我按以下顺序给的：

　　首先，优化你的sql和索引；

　　第二缓存，memcached，redis；

　　第三条及以上都做了，还是慢，所以主从拷贝或者主从拷贝，读写分离，可以在应用层做，效率高，也可以用三方工具。第三方工具推荐360的atlas，其他要么效率低，要么无人问津；

　　第四，如果以上都做的比较慢，就不要想着分割了。mysql自带分区表。先试试这个。它对您的应用程序是透明的，您不需要更改代码。但是，sql语句需要针对分区表进行优化。sql条件应该有包含分区条件的列，这样查询就可以位于少数分区上，否则将扫描所有分区。另外，分区表还有一些洞，这里就不多说了。

　　第五，如果以上都做到了，那么先做垂直拆分。其实根据你模块的耦合程度，把一个大系统分成几个小系统，也就是分布式系统；

　　第六，横向分割。对于数据量大的表格，这一步是最麻烦的，能考验技术水平。应该选择一个合理的Harding键。为了有好的查询效率，表结构也要改变，做一些冗余，应用也要改变。尝试在sql中取Harding key，将数据定位到一个有限的表中进行检查，而不是扫描所有的表；

　　Mysql数据库一般按照这一步进化，成本从低到高；

　　有人可能会说，第一步是优化sql和索引。你一定要这么说吗？的确，我们都知道，但是很多时候，这一步做的并不到位，有的甚至只按照sql做索引建立，而根本不优化sql(你中枪了吗？)，除了最简单的添加、删除和查询，如果要实现一个查询，可以编写多种查询语句。不同的语句，根据你选择的引擎、表中的数据分布、索引、数据库优化策略、查询中的锁定策略等因素，最终查询的效率差别很大；优化要整体考虑。有时候优化一个语句，会降低其他查询的效率，所以要取一个平衡点。即使精通mysql，除了纯粹的技术优化，还要根据业务方面优化sql语句，才能达到最佳效果。你敢说你的sql和索引已经是最优的吗？

　　下面说说不同发动机的优化。myisam读取效果好，写入效率差，与其数据存储格式、索引指针、锁定策略有关。它的数据是顺序存储的(innodb数据存储方式是聚簇索引)，他的索引btree上的节点是指向数据物理位置的指针，所以可以快速查找。(innodb索引节点存储数据的主键，需要根据主键进行两次搜索)；Myisam锁是表锁，只在读写之间，读写之间是并发的(读和插可以并发，设置concurrent_insert参数，定期进行表优化，所以没有办法更新)。所以写的比较慢，默认写优先级比读优先级高。当写操作到来时，可以立即将其插入到读操作的前面。如果是批量写，会导致阅读。Myisam不使用查询时间太长的sql。如果策略使用不当，还会导致写饥饿，所以尽量使用查询效率低的拆分sql。

　　Innodb一般是行锁。这通常是指在索引中使用sql时，行锁被添加到索引中，而不是数据记录中。如果索引中没有使用sql，表仍将被锁定。mysql的读写可以并发，普通select不需要锁。当查询的记录被锁定时，将使用一致的未锁定快照读取，即根据数据库隔离级别策略，将读取锁定行的快照并进行其他更新。因为普通读写没有冲突，所以innodb在读写的时候不会饿死，而且因为使用索引的时候使用了行锁，锁的粒度小，相同锁的竞争少，增加了并发处理，所以并发读写的效率还是很优秀的。问题在于索引查询后根据主键进行二次搜索导致的效率低下；

　　Ps:很奇怪。为什么innodb的索引叶节点存储的是主键，而不是像mysism那样存储数据的物理地址指针？如果存储了物理地址指针，就不需要再去查了吗？这也是我最初的困惑。如果你根据mysism和innodb的数据存储方式的不同来想一想，你就明白了，我就不用多费口舌了！

　　因此，innodb可以使用索引覆盖技术来避免二次搜索。如果不能使用索引覆盖，可以扩展实现基于索引覆盖的延迟关联。我不知道什么是指数覆盖率。我建议你无论如何都要搞清楚它是什么！

　　尽力优化你的sql！成本低，但是费时费力。需要在技术和业务都比较熟悉的情况下仔细优化，优化的效果立竿见影！