Подтаблица в проекте использует sharding-jdbc
В то время я изо всех сил пытался использовать mycat или sharding-jdbc, но, наконец, я использовал sharding-jdbc по следующим причинам:
1. mycat比较重, 相对于sharding-jdbc只需导入jar包就行, mycat还需要部署维护一个中间件服务.由于我们只有一个表需要分表, 直接用轻量级的sharding-jdbc即可.
2. mycat作为一个中间代理服务, 难免有性能损耗
3. 其他组用mycat的时候出现过生产BUG
Впрочем, шардинг-jdbc тоже полон ям и ям.Мы перешли с версии 2.х на версию 4.х, а потом с версии 4.х на версию 3.х.Каждая версия наступила на яму (некоторые шардируют -jdbc , некоторые из-за зависимостей нашего проекта), В конце концов, мне пришлось изменить исходный код, прежде чем я перейду (на самом деле, строка кода была закомментирована).
Сегодня поговорим об одной из ям — таблице и пейджинге.
описание проблемы
задний план
CREATE TABLE `order_00` (
`id` bigint(18) NOT NULL AUTO_INCREMENT COMMENT '逻辑主键',
`orderId` varchar(32) NOT NULL COMMENT '订单ID',
`CREATE_TM` datetime DEFAULT NULL COMMENT '订单创建时间',
PRIMARY KEY (`ID`) USING BTREE,
UNIQUE KEY `IDX_ORDER_POSTID` (`orderId`) USING BTREE,
KEY `IDX_ORDER_CREATE_TM` (`CREATE_TM`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='订单表';
CREATE TABLE `order_01` (
`id` bigint(18) NOT NULL AUTO_INCREMENT COMMENT '逻辑主键',
`orderId` varchar(32) NOT NULL COMMENT '订单ID',
`CREATE_TM` datetime DEFAULT NULL COMMENT '订单创建时间',
PRIMARY KEY (`ID`) USING BTREE,
UNIQUE KEY `IDX_ORDER_POSTID` (`orderId`) USING BTREE,
KEY `IDX_ORDER_CREATE_TM` (`CREATE_TM`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='订单表';
CREATE TABLE `order_02` (
`id` bigint(18) NOT NULL AUTO_INCREMENT COMMENT '逻辑主键',
`orderId` varchar(32) NOT NULL COMMENT '订单ID',
`CREATE_TM` datetime DEFAULT NULL COMMENT '订单创建时间',
PRIMARY KEY (`ID`) USING BTREE,
UNIQUE KEY `IDX_ORDER_POSTID` (`orderId`) USING BTREE,
KEY `IDX_ORDER_CREATE_TM` (`CREATE_TM`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='订单表';
Предполагая, что есть три указанные выше подтаблицы, логика подтаблицы использует по модулю orderId, то есть orderId=0 записывается в order_00, orderId=1 записывается в order_01, а orderId=2 записывается в заказ_02.
Примечания: В то время было довольно спорно, почему для хэширования использовался orderId вместо временной подтаблицы. Теоретически таблица порядка больше подходит для использования времени в качестве подтаблицы, так что чем старше данные, тем реже к ним обращаются, а старая подтаблица постепенно станет холодной и больше не будет использоваться. доступ. В то время ответственный сказал, что, поскольку частота чтения и записи этой таблицы высока (а основная база данных часто требуется для чтения в сцене), использование orderId для разделения таблицы может сбалансировать нагрузку на запись и читающая нагрузка. Это немного надуманно, но в этом есть смысл, так что давайте сделаем это первым.
В бизнесе пейджинговый запрос нужно выполнять по orderId или CREATE_TM, то есть mybatis пишет запрос sql примерно так:
<select id="queryPage" parameterType="xxx" resultMap="BaseResultMap">
select
<include refid="Base_Column_List"/>
from ORDER
<if test="orderId !=null and orderId !='' ">
AND orderId=#{orderId , jdbcType=VARCHAR}
</if>
<if test="createTmStartStr!=null and createTmStartStr!='' ">
AND create_tm >= concat(#{createTmStartStr, jdbcType=VARCHAR},' 00:00:00')
</if>
<if test="createTmEndStr!=null and createTmEndStr!='' ">
AND create_tm <= concat(#{createTmEndStr, jdbcType=VARCHAR},' 23:59:59')
</if>
limit #{page.begin}, #{page.pageSize}
</select>
Любой, кто использовал sharding-jdbc, знает, что в sharding-jdbc есть 5 стратегий сегментирования, как показано на следующем рисунке.Официальный сайт
В дополнение к стратегии сегментирования Hint, другие стратегии сегментирования требуют, чтобы условие sql для определения местоположения должно включать столбец сегментирования (orderId в нашей таблице).Очевидно, что в нашем бизнес-сценарии мы не можем гарантировать, что условие sql для определения местоположения должно содержать orderId. , поэтому мы можем использовать HintShardingStrategy только для передачи условий запроса страницы в алгоритм стратегии сегментирования, а затем определить, какую таблицу запрашивать.Примерный код выглядит следующим образом.
public class OrderHintShardingAlgorithm implements HintShardingAlgorithm {
public static final String ORDER_TABLE = "ORDER";
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames, ShardingValue shardingValue) {
ListShardingValue<String> listShardingValue = (ListShardingValue<String>) shardingValue;
List<String> list = Lists.newArrayList(listShardingValue.getValues());
List<String> actualTable = Lists.newArrayList();
// 页面上的查询条件会以json的方式传到shardingValue变量中
String json = list.get(0);
OrderQueryCondition req = JSON.parseObject(json, OrderQueryCondition.class);
String orderId = req.getOrderId();
// 查询条件没有orderId, 要查所有的分表
if(StringUtils.isEmpty(orderId)){
// 所有的分表
for(int i = 0 ; i< 3; i++){
actualTable.add(ORDER_TABLE + "_0" + i);
}
}else{
// 如果指定了orderId, 只查orderId所在的分表即可
long tableSuffix = ShardingUtils.getHashInteger(orderId);
actualTable.add(ORDER_TABLE + "_0" + tableSuffix);
}
// actualTable中包含sharding-jdbc实际会查询的表
return actualTable;
}
}
Таким образом, если мы запрашиваем на основе orderId, окончательный SQL, выполняемый sharding-jdbc, будет (при условии, что 10 элементов на странице):
select * from ORDER_XX where orderId = ? limit 0 ,10
Если условие запроса не имеет orderId, то окончательный выполненный SQL равен 3 (при условии, что 10 на страницу):
select * from ORDER_00 where create_tm >= ? and create_tm <= ? limit 0 ,10 ;
select * from ORDER_01 where create_tm >= ? and create_tm <= ? limit 0 ,10 ;
select * from ORDER_02 where create_tm >= ? and create_tm <= ? limit 0 ,10 ;
Обратите внимание, что в случае нескольких подтаблиц каждая таблица будет принимать первые 10 фрагментов данных (всего 30 фрагментов), а затем сортировать и брать первые 10 фрагментов данных.Эта логика неверна. апример, если следующий рисунок:
В примере на рисунке, если вы хотите получить общие 2-е и 3-е данные в двух таблицах, отсортированных по баллам, они должны быть 95 и 90. Так как исполняемый SQL может получить только вторые и третьи данные из каждой таблицы, то есть 90 и 80 получаются из таблицы t_score_0, 85 и 75 получаются из таблицы t_score_0. Поэтому при слиянии результата его можно слить только из полученных 90, 80, 85 и 75, поэтому как бы ни было реализовано слияние результата, получить правильный результат невозможно.
тогда что нам делать?
Практика sharding-jdbc заключается в том, чтобы переписать наш sql, сначала узнать все данные, а затем сделать сортировку слиянием
Например, при запросе страницы 2
原sql是:
select * from ORDER_00 where create_tm >= ? and create_tm <= ? limit 10 ,10 ;
select * from ORDER_01 where create_tm >= ? and create_tm <= ? limit 10 ,10 ;
select * from ORDER_02 where create_tm >= ? and create_tm <= ? limit 10 ,10 ;
会被改写成:
select * from ORDER_00 where create_tm >= ? and create_tm <= ? limit 0 ,20 ;
select * from ORDER_01 where create_tm >= ? and create_tm <= ? limit 0 ,20 ;
select * from ORDER_02 where create_tm >= ? and create_tm <= ? limit 0 ,20 ;
При запросе страницы 3
原sql是:
select * from ORDER_00 where create_tm >= ? and create_tm <= ? limit 20 ,10 ;
select * from ORDER_01 where create_tm >= ? and create_tm <= ? limit 20 ,10 ;
select * from ORDER_02 where create_tm >= ? and create_tm <= ? limit 20 ,10 ;
会被改写成:
select * from ORDER_00 where create_tm >= ? and create_tm <= ? limit 0 ,30 ;
select * from ORDER_01 where create_tm >= ? and create_tm <= ? limit 0 ,30 ;
select * from ORDER_02 where create_tm >= ? and create_tm <= ? limit 0 ,30 ;
Конечно, все обязательно подумают, что производительность обработки будет плохой, на самом деле так оно и есть, но на этом основании и оптимизируется sharing-jdbc, то есть упомянутое выше «слияние», Конкретный процесс интеграции может бытькликните сюдаПосмотрите описание на официальном сайте, длина относительно большая, поэтому я не буду размещать его здесь.
Общая логика заключается в том, чтобы сначала узнать данные всех страниц, затем пропустить предыдущие страницы с помощью потоковой обработки и взять только последние необходимые страницы и, наконец, достичь цели разбиения по страницам.
Ступай на яму
Теперь, когда sharding-jdbc оптимизирован, на какую яму мы наступили?
слушай меня медленно
В io.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement#getResultSet() есть логика,
Если число запрашиваемых подтаблиц равно только одной, логика слияния не будет выполняться (однако, даже если выполняется поиск только в одной подтаблице, предложение limit в sql будет переписано), как показано на рисунке:
Возвращаясь к нашему бизнес-сценарию, если условие запроса включает orderId, так как оно может найти конкретную таблицу, в конце нужно запросить только одну подтаблицу.
Затем возникает проблема, потому что sharding-jdbc переписал предложение limit нашего sql, Позже, поскольку была проверена только одна подтаблица и не было выполнено слияние (то есть предыдущая страница не была пропущена), независимо от того, какая страница была запрошена, выполненный sql был (при условии, что размер страницы 10000):
select * from ORDER_XX where orderId = ? limit 0 ,10000
select * from ORDER_XX where orderId = ? limit 0 ,20000
select * from ORDER_XX where orderId = ? limit 0 ,30000
select * from ORDER_XX where orderId = ? limit 0 ,40000
......
Это приводит к проблеме, независимо от того, какой номер страницы я передам, sharding-jdbc вернет мне одни и те же данные.Очевидно, что это неправильно.
Конечно, осторожные друзья могут обнаружить, что, поскольку orderId является уникальным индексом, должна быть только одна часть данных, поэтому никогда не будет случая запроса второй страницы.
Обычно это так, но в нашем коде тоже есть старая логика: при экспорте результатов запроса (то есть экспорте данных всех страниц) он будет асинхронно постранично в фоновом режиме Экспортируйте до тех пор, пока не будут экспортированы все страницы или не будет достигнуто максимальное количество запросов (при условии, что 10 000 запросов).
Поэтому при экспорте по orderId, т.к. каждая страница возвращает одни и те же данные, невозможно судить, когда "все страницы были импортированы", поэтому правильный результат должен иметь только один кусок данных, но под sharding-jdbc Executed 10 000 раз и экспортировал 10 000 одинаковых данных Думаете это яма?
Если вы знаете проблему, то решение простое, но эта статья не для того, чтобы рассказать о том, как решить эту проблему, а для того, чтобы рассказать о размышлениях, вызванных этой проблемой:
在mysql分表环境下, 如何高效地做分页查询?
Думая о подкачке mysql
ограничить оптимизацию
Прежде чем обсуждать производительность подкачки в среде с разделенными таблицами, давайте посмотрим, как подкачка должна быть реализована в среде с одной таблицей.
Как мы все знаем, для подкачки в mysql нужно использовать только предложение limit, то есть
select * from order limit (pageNo-1) * pageSize, pageSize
Поскольку в реализации mysql ограничение смещения, размера заключается в том, чтобы сначала просмотреть и пропустить фрагменты данных с предыдущим смещением, а затем взять фрагменты данных размера. Когда pageNo больше, смещение будет больше, и данные, сканируемые mysql, будут больше, поэтому производительность резко упадет.
Таким образом, первая проблема, которую необходимо решить при пейджинге, заключается в том, как оптимизировать производительность, когда номер страницы слишком велик.
Первый вариантПокрытие индекса, описанное в этой статьеплан. Подводя итог, sql переписывается следующим образом:
select * from order where id >= (select id from order limit (pageNo-1) * pageSize, 1) limit pageSize
Используя принцип покрытия индекса, сначала непосредственно найдите минимальный идентификатор первых данных текущей страницы, а затем извлеките необходимые данные.
Это действительно может улучшить производительность, но я не думаю, что это полностью решает проблему, потому что, когда номер страницы слишком велик, mysql все равно нужно просмотреть много строк, чтобы найти наименьший идентификатор, и эти просматриваемые строки бессмысленны.
запрос курсора прокрутки
Курсорный запрос — это термин в elasticSearch, но здесь я имею в виду не реальный запрос прокрутки, а использование идей ES для реализации запроса пейджинга mysql.
Так называемая прокрутка — это прокрутка страницы за страницей, общая идея такова:
1. 查询第1页
select * from order limit 0, pageSize;
2. 记录第1页的最大id: maxId
3. 查询第2页
select * from order where id > maxId limit pageSize
4. 把maxId更新为第2页的最大id
... 以此类推
Можно видеть, что этот алгоритм не требует стресса для mysql, поскольку для достижения цели ему нужно только каждый раз сканировать фрагменты данных pageSize.По сравнению с приведенной выше схемой покрытия индекса производительность запросов может быть значительно улучшена.
Конечно, он также имеет свои ограничения:
1. 性能的提高带来的代价是代码逻辑的复杂度提高. 这个分页逻辑实现起来比较复杂.
2. 这个算法对业务数据是有要求的, 例如id必须是单调递增的,而且查询的结果需要是用Id排序的.
如果查询的结果需要按其他字段(例如createTime)排序, 那就要求createTime也是单调的, 并把算法中的id替换成createTime.
有某些排序的场景下, 这种算法会不适用.
3. 这个算法是需要业务上做妥协的, 你必须说服你的产品经理放弃"跳转到特定页"的功能, 只能通过点击"下一页"来进行翻页.
(这才是scroll的含义, 在手机或平板上,只能通过滚动来翻页,而无法直接跳转到特定页)
Пейджинговый запрос в среде с разделенной таблицей
Как обсуждалось выше, в среде с одной таблицей относительно просто добиться эффективного разбиения по страницам.
Так в чем же разница в реализации пейджинга в среде с секционированием таблиц?
Как упомянуто выше, это было продемонстрировано в Sharding-JDBC, пейджинговое запрос настольной среды, если вы не ставите
select * from ORDER_00 where create_tm >= ? and create_tm <= ? limit (pageNo-1) * pageSize ,pageSize ;
select * from ORDER_01 where create_tm >= ? and create_tm <= ? limit (pageNo-1) * pageSize ,pageSize;
select * from ORDER_02 where create_tm >= ? and create_tm <= ? limit (pageNo-1) * pageSize ,pageSize ;
Переписать
select * from ORDER_00 where create_tm >= ? and create_tm <= ? limit 0 , (pageNo-1) * pageSize + pageSize ;
select * from ORDER_01 where create_tm >= ? and create_tm <= ? limit 0 , (pageNo-1) * pageSize + pageSize;
select * from ORDER_02 where create_tm >= ? and create_tm <= ? limit 0 , (pageNo-1) * pageSize + pageSize ;
Тогда окончательно найденные данные могут быть неправильными данными.Поэтому в среде подтаблицы упомянутые выше «метод покрытия индекса» и «метод запроса курсора» определенно не применимы.Потому что все должны быть найдены данные узла , а затем слились, то есть правильные данные.
Следовательно, для реализации функции подкачки в среде разделения таблиц в основном необходимо переписать предложение limit.
Давайте сначала посмотрим на решение шаринга-jdbc.Переписанный лимит 0, (pageNo-1) * pageSize + pageSize сравнивается с исходным лимитом (pageNo-1) * pageSize, pageSize, давление запроса на стороне базы данных аналогично , так как почти все Сканировать (pageNo-1) * pageSize строки для получения данных Разница в том, что после перезаписи sql потребление памяти и потребление сети клиентом становятся больше.
sharding-jdbc умело использует комбинацию потоковой обработки и приоритетной очереди, Устраните нагрузку, связанную с потреблением клиентской памяти, но влияние потребления сети по-прежнему нельзя устранить.
Так неужели нет лучшего решения?
Должно быть, существуетОтраслевая проблема — четыре решения для «подкачки между базами данных»В этой статье автор упомянул «вторичный метод запроса», который очень умно решил проблему этого запроса на подкачку. Вы можете обратиться к нему.
Но если хорошенько подумать, некоторые ограничения все же есть:
1. 当分表数为N时, 查一页数据要执行N*2条sql.(这个无解, 只要分表了就必须这样)
2. 当offset很大的时候, 第一次查询中扫描offset行数据依然会非常的慢, 如果只分表不分库的话, 那么一次查询会在一个库中产生N条慢sql
3. 算法实现起来代码逻辑应该不简单, 如果为了一个分页功能写这么复杂的逻辑, 是不是划不来,
而且后期也不好维护
Если первоначальный автор алгоритма увидит здесь мои придирки, захочет ли он меня немного презирать~~
На самом деле, я хочу сказать, что, поскольку не существует идеального решения для запросов на подкачку в среде с секционированием таблиц или если стоимость реализации слишком высока, можно ли считать, что запросы на подкачку не следует выполнять в таблице? среда разбиения?
Автономные вычисления + сервис с потерями
Как упоминалось выше, на самом деле не подходит функция запроса на подкачку в среде с секционированием таблиц. Однако бизнес-требования не означают, что их можно удалить.Во многих случаях функция подкачки необходима.Однако существование запросов подкачки обычно также необходимо для защиты базы данных.Если функция подкачки удаляется, нагрузка на базу данных будет еще больше.
Значит, есть только один выбор между таблицей и нумерацией страниц?
Нет, я хочу все это, я хочу таблицу, я хочу еще и нумерацию страниц!
Но функция подкачки выполняется не в табличной среде, а в другой сводной таблице для выполнения функции запроса подкачки.
Приблизительное решение:
1. 正常的业务读写分表
2. 根据具体的业务需求,例如实时计算/离线计算技术(spark, hadoop,hive, kafka等)生成各分表的一张汇总表
3. 分页查询的接口直接查询汇总表
Кроме того, следует отметить, что это решение определенно наносит ущерб бизнесу, а именно:
1. 不管是离线计算还是实时计算, 都不能保证实时性, 查询结果肯定是有时延的
2. 由于汇总表是不可能包含分表的所有数据的, 所以汇总表肯定是只包含部分数据的,例如只有一个月内的,具体根据业务场景而定
Как правило, данные системы отчетов генерируются системой хранилища данных, но генерируются только те данные, которые должны быть у пользователя, а остальные удаляются.
Когда я искал информацию, чтобы написать это резюме, я увидел предложение:
其实分表的根本目的是分摊写负载, 而不是分摊读负载
На самом деле это имеет смысл, если нагрузка на чтение слишком велика, мы можем увеличить кеш, добавить узлы данных и многие другие методы, но если нагрузка на запись слишком велика, то в принципе необходимо разделить таблицу.
Из этой теории пейджинг — это операция чтения, читать подтаблицу вообще не нужно, просто читаем из других мест (здесь мы — хранилище данных)
Нет разделения таблицы (раздел tidb mongoDb ES)
На самом деле, большинство таблиц mysql не нужно разбивать на таблицы.
До MySQL 5.5 количество таблиц должно быть оптимизировано примерно после 500 Вт.После MySQL 5.5 количество таблиц необходимо оптимизировать, когда оно составляет от 1 до 2 кВт. До этой точки перегиба производительности можно считать, что mysql вполне способен ее нести.Конечно, это зависит от частоты qps и конфликтов чтения и записи.
Как насчет точки перегиба производительности?Тогда нам нужно рассмотреть возможность разделения таблицы mysql.Метод разделения таблицы может быть подтаблицей и подбиблиотекой или простым разделением.
По сути, улучшение производительности, вызванное разделом и таблицей, одинаково, Поскольку секционирование фактически можно рассматривать как нижний уровень MySQL, помогающий нам реализовать логику секционирования таблиц, условно говоря, секционирование таблиц приведет к большей сложности кодирования, чем секционирование (при секционировании не нужно учитывать проблему многотабличного страничного запроса на все). С этой точки зрения общий бизнес можно разделить напрямую.
Конечно, выбор раздела или таблицы по-прежнему требует небольшого компромисса:
1. 表中的数据只有部分热点数据经常访问, 其他的不常访问的话, 适合用分区表
2. 分区表相对容易维护, 可以针对单独一个分区进行检查,优化, 批量删除大量数据时, 分区表会比一般的表更快
3. 分区表可以分布在不同的物理设备上, 从而可以高效地利用多个硬盘
4. 如果查询条件不包含partition key的话, 分区表不一定有分表效率高
5. 如果分区表中绝对的热点数据, 每一条数据都有可能被访问到, 也不太适合分区
6. 如果数据量超大, 由于mysql只能分1024个分区, 如果1024个分区的数据都是千万以上, 那肯定是也不适合分区的了
Подводя итог, если нам достаточно таблицы разделов, то нет необходимости делить таблицу для увеличения сложности программирования.
Кроме того, если вы не хотите разбивать таблицу данных, а количество данных в таблице действительно велико, nosql также является альтернативой.Особенно для тех операций с таблицами, которые не требуют сильных транзакций, Он очень подходит для nosql, так что можно избежать сложности программирования, и нет чрезмерной потери в производительности.
Также есть много решений для nosql:
1. mongoDb
2. hbase
3. tidb
4. elasticSearch
Конечно, можно использовать и комбинацию mysql+nosql, например обычные операции чтения и записи mysql, пейджинговые запросы через ES и т.д.
Я напишу это первым сегодня, и я напишу о mysql и nosql, когда у меня будет возможность.