mysql语句优化的几种方法_MySQL 对于千万级的大表要怎么优化？

很多人的第一反应是把它分成不同的类别；我给出的顺序是：

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

第二个缓存是，redis；

如果以上步骤都完成后还是很慢，可以使用主从复制或者主主复制，把读写分开，这个可以在应用层做，效率比较高，也可以使用第三方工具，推荐使用360 Atlas这个第三方工具，其他工具要么效率低，要么没人维护。

第四，如果以上操作后查询仍然很慢，就不要考虑拆分了。MySQL 自带分区表，先试试这个，对你的应用程序来说是透明的，不需要修改代码。不过，SQL 语句需要针对分区表进行优化，SQL 条件应该包括分区条件的列，这样查询就定位在少数几个分区上，否则会扫描所有分区。另外，分区表还有一些陷阱，这里就不多说了。

第五，如果上面这些你都做完了，那么先做垂直拆分，其实就是把一个大系统按照你模块的耦合程度，分成多个小系统，也就是分布式系统；

第六是水平切分，对于数据量很大的表来说，这一步是最麻烦的，也是最考验你的技术水平的，需要选择合理的key，为了达到好的查询效率，表结构也要改变，有一定的冗余和应用的改变，尽量在SQL中包含key，在有限的表中定位数据进行查询，而不是扫描所有表。

MySQL数据库一般都是这样演进的，成本从低到高；

有人会问“真的需要说第一步就是优化SQL和索引吗？”确实，大家都知道这个道理，但是很多时候，这一步并没有做好，甚至有人只根据SQL建立索引，根本不做SQL优化（你中招了吗？）。除了最简单的增删改查，实现一个查询，可以写很多种查询语句。不同的语句，取决于你选择的引擎，表中数据的分布，索引的情况，数据库的优化策略，查询中的锁策略等因素，最终查询效率差别很大；优化要从全局的角度去考虑，有时候你优化了某个语句之后，其他查询的效率就降低了，所以要找一个平衡点；即使你精通MySQL，除了纯技术方面的优化，也要根据业务方面去优化SQL语句，才能达到最佳效果；你敢说你的SQL和索引已经是最优的了吗？

再说一下不同引擎的优化，读效果好，但是写入效率差，这个和它的数据存储格式、索引指针以及锁策略都有关系。它的数据是顺序存储的（数据存储方式是聚簇索引），它的索引btree上的节点是指向数据物理位置的指针，所以查找很快（索引节点保存的是数据的主键，所以需要根据主键查找两次）；锁是表锁，只有读是并发的，而写和读（读和插入可以并发，设置参数，定时进行表优化操作，没有办法更新操作）是串行的，所以写入慢，而且默认的写优先级高于读优先级，高到当有写操作来的时候，可以立刻插在读操作前面。如果是批量写入，会造成读请求饿死，所以需要设置读写优先级或者设置在一定次数的写操作之后执行读操作的策略； 不要使用查询时间过长的SQL，如果策略使用不当，也会造成写饥饿，所以尽量拆分查询效率低的SQL，

一般是行锁，这个一般是指SQL使用索引的时候，行锁是加在索引上的，而不是加在数据记录上的，如果SQL没有使用索引，那么还是会锁表的。MySQL的读写是可以并发的，正常情况下是不需要加锁的，当查询的记录遇到锁的时候，采用一致性非锁定快照读，也就是按照数据库隔离级别策略，读被锁定行的快照，其他的更新或者锁定读语句使用当前读去读原始行。因为普通的读写不会冲突，所以不会出现读写饥饿的情况。因为使用索引的时候都是用行锁，锁粒度小，对同一个锁的竞争少，增加了并发处理，所以并发读写的效率还是很好的。问题是索引查询之后还要根据主键进行二次查找，导致效率低下。

ps：奇怪了，索引叶子节点为什么存储的是主键，而不是数据的物理地址指针呢？如果存储的是物理地址指针，就不需要二次查找了，这也是我最初的疑惑。大家根据数据存储方式的差异去思考就明白了，就不浪费口舌了！

所以为了避免二次查找，可以使用索引覆盖技术。如果不能使用索引覆盖，进一步的扩展就是基于索引覆盖实现延迟关联。如果你不知道什么是索引覆盖，建议你还是搞清楚吧！

尽你所能优化你的SQL！这是一项成本低但费时费力的工作。你需要熟悉技术和业务，并仔细优化才能达到最佳效果。优化的效果是立竿见影的！

作者：zhuqz
链接：https://www.zhihu.com/question/19719997/answer/81930332
往期推荐 

【内涵】说服自己上班的10个理由
涨姿势：为什么Java中“1000==1000”为false，而”100==100“为true？
连“辣鸡代码”都出书写准则，可怕！