SQL mysql优化的要点

东子的思考 2019-07-30 10:06:47

1。SQL的优化主要涉及到索引。SQL的查询，可以用explain来进行SQL语句的解析。从里面可以看到SQL语句执行的顺序，比如数据库表查询是怎么连接的。在它连接的时候，先查小便再查大表。以及可能使用到索引，具体使用哪个索引，等一些字段。都可以通过explain的结果反映出来。其实我们可以根据结果进行优化如果没有使用到索引，那么，我们就可以把没有使用到索引的原因找出来。从而改写SQL语句。 2。索引的底层是一个B+tree，针对每个字段添加了索引以后会生成一颗B+tree，主键索引又叫聚簇索引，索引里面的叶子结点直接保存表中的值，而其他索引，保存的是索引字段以及主键的值，这样在进行其他的普通索引查询以后，还需要对聚簇索引进行回表查询。为了防止进行回表查询，也可以针对普通索引做覆盖索引，这样普通索引里面存储的也是表中的字段，就可以不用再回表去聚簇索引那里做二次查询。不过一般不建议这么做。这里的话一些普通的应用，比如，like语句什么时候不用索引；不能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条件是遍历驱动表，再由驱动表的条件去找被驱动表，如果驱动表比较小，则可以减少循环的次数）。 3。对于SQL的分页。使用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只是案例字段，其他有规律字段同理）。

...全文