一.索引篇SQL的优化主要涉及到索引。SQL的查询，可以用explain来进行SQL语句的解析。从里面可以看到SQL语句执行计划，比如数据库表查询是怎么连接的。在它连接的时候，先查小便再查大表。以及可能使用到索引，具体使用哪个索引，等一些字段。都可以通过explain的结果反映出来。针对怎么查看执行计划做如下大致说明：放个执行计划的案例图：id表示执行顺序（由上至下，编号逆序），select_type表示查询类型（根据英文释义就能大致理解），table就是查询的表，type表示访问类型级别（分析的重要指标），possible_keys表示可能使用索引，key表示具体使用的索引，ref表示索引列的使用情况（如上图索引列u被d关联使用），extra表示sql执行的额外信息（图片中表示使用了where过滤）依我看来，整个计划大致只需分析几个部分就行（依次是 key， type，id，extra），第一个是索引使用情况，如果有一些查询条件添加了索引但没有使用到索引，那么，我们就可以把没有使用 到索引的原因找出来。依此改写SQL语句。第二就是执行级别，一般来说只要不是all（遍历全表），index（遍历索引）其它情况都可以接受，当然，也可以朝着更高的级别去优化。第三的id，分析索引和执行级别时也需要结合语句的执行顺序来思考。第四个extra，一般不出现using filesort（无索引排序，order by无索引），using temporary（使用中间表，一般出现在group by无索引）就可以接受。索引的底层是一个B+tree，针对每个字段添加了索引以后会生成一颗B+tree，主键索引又叫聚簇索引，索引里面的叶子结点直接保存表中的值，而其他索引，保存的是索引字段以及主键的值，这样在进行其他的普通索引查询以后，还需要对聚簇索引进行回表查询。为了防止进行回表查询，也可以针对普通索引做覆盖索引，这样普通索引里面存储的也是表中的字段，就可以不用再回表去聚簇索引那里做二次查询。一些普通的sql优化应用，比如，like语句什么时候不用索引；联合索引最左匹配；不使用!=，not exit，not in等非类型的判断条件；不能where后面进行表达式操作；select后面必须指定字段；同样的or条件也可能造成不使用索引，可以使用union all，来代替or来进行SQL语句的改写。等等就不详细介绍了，相信大家都知道。以前看过一些文章。说SQL用in语句表达式，会造成不用索引，其实是不对的。in表达式，其实也是会使用索引的。但是in不能连接太多条件。如果条件是连续的，可以使用between来代替in。如果是包含了子查询的SQL，也可以使用exists，not exist来代替in，exist和in的区别主要在于，是由子查询来驱动外层查询，还是由外层查询来驱动查询，exist主要是外层表驱动子查询，当外层驱动的表大于子查询的表，使用exist；否则则用in，in表达式先进行子查询，然后再由子查询驱动外层查询。具体是先执行外层查询还是查询，也可以用explain来校验结果。还有一点，经常看见where语句后面不能有null值的判断以及不等于判断，会造成不使用索引。其实这个是不对的。有null的SQL也可能会使用到索引。可以用explain去解析一条包含null的语句。会发现其实是使用到索引的。null的数据，会存放在b+tree的最左边节点，所以也是可以走索引的。其实走不走索引，主要原因是mysql的内部优化器决定的。针对于每一条SQL语句，mysql会进行分析，例如一个null的判断，如果索引中null的值数量非常大，在查询到null所有的值以后，还需要针对聚簇索引，进行回表查询。如果数据量太大，查询的效果比全表扫描还慢。mysql优化器会自动选择全表扫描，而不走索引。mysql有一个算法，会模糊的计算出查询出来的值大概有多少条。优化器不只会根据情况来决定使不使用索引，也会涉及到索引的使用优先级，例如两个表join，优化器会选择更小的表的索引字段来进行查询。如果需要强制使用哪个索引，可以使用force index加索引名，来强制选择使用的索引。也可以使用straight_join来强制左边的表成为驱动表（一般使用在inner join，且左边的表小右边的表大，由小表驱动大表，因为join条件是遍历驱动表，再由驱动表的条件去找被驱动表，如果驱动表比较小，则可以减少循环的次数）。二.分表分库篇当表中的数据量太大，即使加上索引也会影响sql性能时，就需要考虑分表分库（阿里建议时500w数据就会影响性能，个人觉得要针对具体的业务增长来看，例如一张日增长数据上万的表，那么建议在上线稳定后就开始做分表分库），目前业内使用的较多的就是sharedingjdbc，mycat两种分表分库，sharedingjdbc在代码层面对sql进行分发，mycat则是以中间件的形式代理mysql对外提供链接，在mycat内部配置做分表分库策略。代理的模式更加人性化，分表分库对coding都是透明的，coding层无需做任何修改。水平分表分需要注意根据业务来进行衡量，如果有非分片字段查询频繁的数据，需要考虑奖非分片字段垂直切割出去后，在对原表进行分表；需要进行任意表join的时候，也会因为可能join的数据不在一个节点上导致查询数据丢失；mycat中对接了多个底层db，还可能会导致事务失败，在mycat第一阶段prepare成功后，第二步commit如果某个节点失败而其他节点提交成功，mycat不会回滚；还有就是分页了，mycat的分页算法是取每个节点分页条件前的所有数据，在合并结果集取出对应的数据，数据量庞大时耗时太大。所以当需要进行复杂的算法改写时，还是shareding jdbc更加灵活一些。三.分页篇mysql的分页查询使用的是关键字limit。但是limit如果数据量太大，前面需要跳过的条数太多，会造成SQL查询非常缓慢。这是需要针对SQL进行处理，例如：如果是自增长的id，并且没有删除数据，可以对ID进行跳页操作，例如翻到第5页，可以在翻页前面加上 ID>5*pageSize 的条件。如果是可以删除的操作，那么可以限制跳页，只允许翻页，这样就可以获取前一页最大的ID进行条件判断（这里的分页条件不局限于ID，也可以是时间，前提是条件必须添加索引）。当数据量过大时，需要进行分表分库。对某个字段进行水平拆分之后，跨库的分页查询，会造成难题。一般的做法就是当翻到第几页时，两个库就取相同的页码进行比较，取两边数据，合并后最小的那一页。但这样会有一个问题。就是当页码过大以后，获取的数据就会越多，分页性能会急速下降。这时需要用到上文所说的，根据分表字段进行整改，例如如果根据自增长的ID进行分表，并且禁止跳页，可以根据前一页的最大ID进行SQL的修改。跳过最大的ID取得靠近的一页，两台机器的这一页数据返回在进行数据的比对，获取数据最小的一页返回给前端，这样可以避免当页码过大以后获取每一张分表的数据太大。如果能够确保分表时候的字段非常均匀。也可以直接跳过对应的数据。例如有两个分表，每页10条记录需要查第5页。此时可以针对两个分表，每一个分表，跳20条记录进行查询，每张分表查5条记录，这个方法可能会丢失数据精度，但业务允许的话还是非常方便的。除此之外，还有一种能够确保精度，而且可以避免查询数据量太多的分页方法。上面说到的跳跃数据量的方法中，因为不知道具体应该跳的最大ID是多少，所以只能在多个分表中平摊。假设知道应该跳跃的最大ID是多少，就可以明确需要跳跃的ID在各个分表中的位置。精确的位置之后，再取出对应分页条数进行拼接就可以得到需要查询页码的精确数据。所以这个分页我们可以进行两次查询。第1次查询，是为了定位需要跳跃的最大ID，在每一个分表中的位置，为了找出需要跳跃的最大ID是多少；第2次查询在进行前文提到的查询合并数据。那么要如何来找到这个需要跳跃的最大id呢？我们可以进行前文说到的分摊查找，比如有三张分表，如果要跳第30条记录，那每一张分表，就跳10条记录，然后从三张分表中获取最小的数据进行比对，然后，其他两张分表再拿最小的那条数据分表的数据记录再进行第2次查询，第2次查询其他两张分表的数据结果集，不止包含第1次查询出来的数据，还包含了第1张分表最小数据，到他们各自最小数据的记录。这时就能定位到，需要跳的第30条记录，在各个分表的位置。定位到需要跳的最大ID。的位置以后就可以取出你想要的分页数据（注：以上所说ID只是案例字段，其他有规律字段同理）。四.事务及分布式事务mysql的几种事务隔离级别：读未提交，读已提交，可重复读，串行化；几种读问题： 脏读（读取其他事务未提交数据，读已提交能够避免），不可重复度（事务两次读数据不一致，可重复度能够避免），幻读（两次读取行数不一致，串行化能够避免）。spring的几种事务传播机制：PROPAGATION_REQUIRED:依赖当前事务，没有则重新开启事务。PROPAGATION_SUPPORT:依赖当前事务，没有则非事务执行。PROPAGATION_MANDATORY:依赖当前事务，没有则抛出异常。PROPAGATION_REQUEST_NEW:总是开启新的事务，里层外层事务不依赖。PROPAGATION_NEVER:总是以非事务执行。PROPAGATION_NESTED:嵌套事务，总是开启新事务运行，但里层事务依赖与外层事务mysql解决读问题是基于MVCC来控制的，基于版本号来控制数据，不用加读锁。例如可重复读，就是把当时事务对应的版本号保存，第二次根据版本号找到对应的undo log，基于undo log将数据回滚到对应版本即可。分布式事务一般用2PC,3PC来实现，这些算法能够保证数据的强一致性，有一些开源的比如tcc-transaction也是基于这些算法修改而来，像我们的系统一般不需要保证强一致性，可以使用mq（例如rabbitmq，用它的手动ack，持久化消息的功能，保证整个事务的最终一致性）。