众所周知，mysql在数据量很大的时候查询的效率是很低的，因为假如你需要 OFFSET 100000 LIMIT 5 这样的数据，数据库就需要跳过前100000条数据，才能返回给你你需要的５条数据。由于数据在磁盘上面不一定是相等长度的，所以没法在跳过这100000条数据上面进行优化，只能一条一条地查找数据、找到结尾处并查找下一条，这就导致了数据库很差的性能。解决的办法就是使用 seek 这种方法，可以参考 这篇文章 。
假如你有这样的数据，
| ID | VALUE | PAGE_BOUNDARY | |------|-------|---------------| | ... | ... | ... | | 474 | 2 | 0 | | 533 | 2 | 1 | <-- Last on page 5 | 640 | 2 | 0 | | 776 | 2 | 0 | | 815 | 2 | 0 | | 947 | 2 | 0 | | 37 | 3 | 1 | <-- Last on page 6 | 287 | 3 | 0 | | 450 | 3 | 0 | | ... | ... | ... |
你需要将第６页的数据返回给用户，这时不需要用OFFSET和LIMIT语句，取而代之的是这种方法――找到第５页的最后一个数据的标识（在这里是id和value），并传递给SQL语句，用于定位从哪里开始第６页的数据，这样即使前面有100000条数据，数据库也不用管这些，只需一行 where id > ? 这样的语句便可略过这100000条数据直接找到你需要的那５条。
用SQL表示为： SELECT id, value FROM t WHERE (value, id) > (2, 533) ORDER BY value, id LIMIT 5 用JOOQ则表示为： DSL.using(configuration) .select(T.ID, T.VALUE) .from(T) .orderBy(T.VALUE, T.ID) .seek(2, 533) .limit(5) .fetch();
返回的结果是
| ID | VALUE | |-----|-------| | 640 | 2 | | 776 | 2 | | 815 | 2 | | 947 | 2 | | 37 | 3 |
和用OFFSET、LIMIT语句查询的结果一样，但是性能方面显然这个更好。
在使用的时候，一般需要配合JOOQ的动态SQL来使用，以便区分各种筛选条件以及究竟是第一次查询还是需要查询更靠后面的数据，代码如下所示：
//动态生成SQL语句 public Condition whereCondition(List<Integer> ids, List<String> wordList, String time, boolean own) { Condition condition = trueCondition(); if (ids != null) { if (own) condition = condition.and(SELF_SITE.URL_ID.in(ids)); else condition = condition.and(SELF_SITE.URL_ID.notIn(ids)); } //加上关键字的条件 Condition wordCondition = falseCondition(); for (String word : wordList) { wordCondition = wordCondition.or(SELF_SITE.NAME.contains(word)); } condition = condition.and(wordCondition); //加上时间的条件 if (time != null) { Instant instant = Instant.ofEpochMilli(Long.parseLong(time)); LocalDateTime localDateTime = LocalDateTime.ofInstant(instant, ZoneOffset.ofHours(8)); condition = condition.and(SELF_SITE.FETCH_TIME.greaterOrEqual(localDateTime)); } return condition; } @Override public List<SelfSite> findData(List<Integer> ids, int self_site_id, List<String> wordList, String time, boolean own) { SelectQuery query = dsl.selectQuery(); query.addFrom(SELF_SITE); query.addConditions(whereCondition(ids, wordList, time, own)); query.addOrderBy(SELF_SITE.ID.desc()); if (self_site_id != -1) query.addSeekAfter(DSL.val(self_site_id)); query.addLimit(NUM_PER_PAGE); return query.fetch().into(SelfSite.class); }
MySQL has the ability to handle a decent amount of traffic as the business expands, however, when the database realizes maximum capacity, the e-commerce website will not work as efficiently as you want. This is because MySQL faces some difficulties and has limitations in handling a few things, such as the following:
Increased Availability : MySQL has a single point of failure at the master server. This means that if the master server goes down, then there will be downtime. When this happens, your customers will not be able to purchase anything from your e-commerce site and when the downtime lasts too long, they will get frustrated and will eventually take the business to some other place, even permanently in some cases. This directly translates to a loss in money for your business. Increasing Reads and Writes : MySQL has limitations on capacity, i.e., if more and more customers carry out complete transactions on your website, it won’t take long before the database stalls. It is true that MySQL can scale reads through read-slaves, however, at the same time the applications have to be aware that the reads are not synchronized with the write master. For example, if a customer updates products in his e-cart, it better be read from the write-master otherwise there will be a risk of having the wrong available-to-promise quantities. This will create a bottleneck in the checkout line, which will undoubtedly result in abandoned carts, unmanaged and unsold inventory, selling inventory you don’t have, refunds, negative social media exposure or worse ― unhappy customers who might not come back. Flexing Up and Down : MySQL writes are not scalable via slaves, which means that you can accommodate an increase in traffic by paying a premium and scaling up. Unfortunately, MySQL does not have the ability to flex up and down in order to benefit your e-commerce business. Tackling When it Reaches its Limit
There will come a time when MySQL will reach its limit causing your e-commerce website to stall. When your write master has been scaled, you must consider the following techniques:
Re-platforming : This technique involves moving the database from one platform to the other. For example, you can move Magento/MySQL to an Oracle-based platform. Sharding : In this, you partition the database across multiple different servers. This helps in improving the performance of MySQL even though it presents various shortcomings.
These techniques do enhance the performance of MySQL, however, they are quite complex processes and are costly to implement. Although, they are not your only options to fall back on.
Maintaining the Database