3 способа разделить таблицы в Mysql

база данных

Сначала поговорим о том, зачем нам нужно делить таблицу

Когда кусок данных достигает миллионов, время на один запрос увеличивается, если есть совместный запрос, я думаю, он может умереть там. Назначение подтаблиц — уменьшить нагрузку на базу данных и сократить время запроса.

По личному опыту, процесс выполнения sql в mysql выглядит следующим образом:
1 — получить sql; 2 — поставить sql в очередь; 3 — выполнить sql; 4 — вернуть результат выполнения. Где вы проводите больше всего времени в этом процессе исполнения? Первое — время ожидания в очереди, а второе — время выполнения sql. По сути, это одно и то же.Во время ожидания должен выполняться sql. Итак, мы хотим сократить время выполнения sql.

В mysql есть механизм блокировки таблиц и строк. Почему этот механизм обеспечивает целостность данных? Позвольте мне привести пример. Если есть два SQL, одни и те же данные одной и той же таблицы должны быть изменены. , Что мне делать в это время, могут ли оба sql одновременно изменять эти данные? Очевидно, что mysql обрабатывает эту ситуацию так, что одна блокировка таблицы (механизм хранения myisam), а другая — блокировка строк (механизм хранения innodb). Блокировка таблицы означает, что никто из вас не может работать с этой таблицей, вы должны подождать, пока я закончу работу с таблицей. То же самое верно и для блокировки строк.Другие SQL должны ждать, пока я завершу операцию с этими данными, прежде чем работать с этими данными. Если данных слишком много, время выполнения слишком велико, а время ожидания больше, поэтому мы делим таблицу.

Во-вторых, стол

1. Создайте кластер mysql, например: используйте кластер mysql, прокси-сервер mysql, репликацию mysql, drdb и т. д.

Некоторые люди спросят кластер MySQL, при чем здесь корневая таблица? Хотя в действительности это не подтаблица, она выполняет роль подтаблицы Каково значение кластера? Снизить нагрузку на БД, грубо говоря, это уменьшить количество SQL в очереди SQL.Например, если есть 10 SQL запросов, если их поставить в очередь сервера БД, то у него будет ждать долго.10 запросов SQL распределены по очередям 5 серверов БД, а очередей одного сервера БД всего 2. Сильно ли это сокращает время ожидания? Это уже очевидно. Поэтому я перечислил его в рамках подтаблицы и сделал несколько кластеров mysql:

Установка, настройка и разделение чтения и записи прокси linux mysql

взаимная установка и настройка master-slave репликации mysql, а также синхронизация данных

Преимущества: хорошая масштабируемость, отсутствие сложных операций над несколькими подтаблицами (код php)

Недостатки: Объем данных в одной таблице не изменился, время, затрачиваемое на одну операцию, все так же велико, а аппаратные накладные расходы высоки.

2. Заранее подсчитывается, что будут таблицы с большими объемами данных и частым обращением, и делим их на несколько таблиц

Такая оценка неплохая, список постов на форуме через долгое время будет очень большим, сотни тысяч или миллионы. В информационной таблице в чате десятки людей болтали вместе в течение ночи.Спустя долгое время данные в этой таблице должны быть очень большими. Таких случаев много. Поэтому для такого большого масштаба данных, который можно оценить, мы заранее разделим таблицы на N. Значение этого N зависит от реальной ситуации. В качестве примера возьмем информационную таблицу чата:

Я заранее построил 100 таких таблиц, message_00, message_01, message_02......message_98, message_99.Потом по ID пользователя определить в какую таблицу помещается информация о чате пользователя, можно использовать Его можно получить по хешу методом, а его можно получить, вычислив остаток.Методов много.Каждый думает об этом. Следующее использует метод hash для получения имени таблицы:

ПроверятькопироватьРаспечатать ?
  1. <?php  
  2. function get_hash_table($table,$userid) {  
  3.  $str = crc32($userid);  
  4.  if($str<0){  
  5.  $hash = "0".substr(abs($str), 0, 1);  
  6.  }else{  
  7.  $hash = substr($str, 0, 2);  
  8.  }  
  9.   
  10.  return $table."_".$hash;  
  11. }  
  12.   
  13. echo get_hash_table('message','user18991');//Результат: message_10
  14. echo get_hash_table('message','user34523');//Результат: message_13
  15. ?>  

Объясните, вышеуказанный метод говорит нам, что сообщения пользователя user18991 записываются в таблицу message_10, а сообщения пользователя user34523 записываются в таблицу message_13.При чтении просто читать из соответствующих им таблиц.Просто берите.

Преимущества: избегайте миллионов фрагментов данных в таблице, сокращайте время выполнения sql.

Недостаток: Когда правило определено, нарушить это правило будет очень проблематично.В приведенном выше примере я использовал алгоритм хэширования crc32.Если я не хочу использовать этот алгоритм сейчас, после переключения на md5, он будет потому что сообщения одного и того же пользователя хранятся в разных таблицах, поэтому данные перепутались. Масштабируемость плохая.

3. Используйте механизм хранения слияния для реализации подтаблиц

Я думаю, что этот метод больше подходит для тех ситуаций, когда запрос данных происходит медленно без предварительного рассмотрения. На данный момент больнее разделить существующие большие масштабы данных, самое больное - изменить код, потому что оператор SQL в программе написан, и теперь таблицу нужно разделить на десятки таблиц, а то и сотни их.таблицу, значит sql оператор надо переписать? Например, я люблю поднимать

Когда mysql>show Engines, вы обнаружите, что mrg_myisam на самом деле является слиянием.

ПроверятькопироватьРаспечатать ?
  1. mysql> CREATE TABLE IF NOT EXISTS `user1` (  
  2.  ->   `id` int(11) NOT NULL AUTO_INCREMENT,  
  3.  ->   `name` varchar(50) DEFAULT NULL,  
  4.  ->   `sex` int(1) NOT NULL DEFAULT '0',  
  5.  ->   PRIMARY KEY (`id`)  
  6.  -> ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;  
  7. Query OK, 0 rows affected (0.05 sec)  
  8.   
  9. mysql> CREATE TABLE IF NOT EXISTS `user2` (  
  10.  ->   `id` int(11) NOT NULL AUTO_INCREMENT,  
  11.  ->   `name` varchar(50) DEFAULT NULL,  
  12.  ->   `sex` int(1) NOT NULL DEFAULT '0',  
  13.  ->   PRIMARY KEY (`id`)  
  14.  -> ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;  
  15. Query OK, 0 rows affected (0.01 sec)  
  16.   
  17. mysql> ВСТАВИТЬ В `user1` (`имя`, `пол`) VALUES('Чжан Ин', 0);
  18. Query OK, 1 row affected (0.00 sec)  
  19.   
  20. mysql> INSERT INTO `user2` (`name`, `sex`) VALUES('tank', 1);  
  21. Query OK, 1 row affected (0.00 sec)  
  22.   
  23. mysql> CREATE TABLE IF NOT EXISTS `alluser` (  
  24.  ->   `id` int(11) NOT NULL AUTO_INCREMENT,  
  25.  ->   `name` varchar(50) DEFAULT NULL,  
  26.  ->   `sex` int(1) NOT NULL DEFAULT '0',  
  27.  ->   INDEX(id)  
  28.  -> ) TYPE=MERGE UNION=(user1,user2) INSERT_METHOD=LAST AUTO_INCREMENT=1 ;  
  29. Query OK, 0 rows affected, 1 warning (0.00 sec)  
  30.   
  31. mysql> select id,name,sex from alluser;  
  32. +----+--------+-----+  
  33. | id | name   | sex |  
  34. +----+--------+-----+  
  35. |1 |Чжан Ин |0 |
  36. |  1 | tank   |   1 |  
  37. +----+--------+-----+  
  38. 2 rows in set (0.00 sec)  
  39.   
  40. mysql> INSERT INTO `alluser` (`name`, `sex`) VALUES('tank2', 0);  
  41. Query OK, 1 row affected (0.00 sec)  
  42.   
  43. mysql> select id,name,sex from user2  
  44.  -> ;  
  45. +----+-------+-----+  
  46. | id | name  | sex |  
  47. +----+-------+-----+  
  48. |  1 | tank  |   1 |  
  49. |  2 | tank2 |   0 |  
  50. +----+-------+-----+  
  51. 2 rows in set (0.00 sec)  

Из вышеописанной операции я не знаю, нашли ли вы что-нибудь? Предположим, у меня есть пользовательская таблица user с фрагментами данных по 50 Вт, и теперь мне нужно разделить ее на две таблицы user1 и user2, каждая с фрагментами данных по 25 Вт,

INSERT INTO user1(user1.id,user1.name,user1.sex)SELECT (user.id,user.name,user.sex)FROM user where user.id <= 250000

INSERT INTO user2(user2.id,user2.name,user2.sex)SELECT (user.id,user.name,user.sex)FROM user where user.id > 250000

Таким способом я успешно разделил пользовательскую таблицу на две таблицы.В данный момент возникла проблема.Что делать с оператором sql в коде.Раньше была одна таблица,а теперь стало две таблицы. Код сильно изменился, это создает большую нагрузку на программиста, есть ли хороший способ решить эту проблему? Способ состоит в том, чтобы создать резервную копию предыдущей пользовательской таблицы, а затем удалить ее.В приведенной выше операции я создал таблицу alluser, просто измените имя таблицы alluser на user. Однако не все операции MySQL можно использовать.

a, если вы используете alter table для слияния таблицы с таблицей другого типа, сопоставление с базовой таблицей будет потеряно. Вместо этого строки из базовой таблицы myisam копируются в замененную таблицу, которой затем присваивается новый тип.

б, я видел в Интернете, что замена не работает, я попробовал, и она работает. Гало первый

  1. mysql> UPDATE alluser SET sex=REPLACE(sex, 0, 1) where id=2;  
  2. Query OK, 1 row affected (0.00 sec)  
  3. Rows matched: 1  Changed: 1  Warnings: 0  
  4.   
  5. mysql> select * from alluser;  
  6. +----+--------+-----+  
  7. | id | name   | sex |  
  8. +----+--------+-----+  
  9. |1 |Чжан Ин |0 |
  10. |  1 | tank   |   1 |  
  11. |  2 | tank2  |   1 |  
  12. +----+--------+-----+  
  13. 3 rows in set (0.00 sec)  

c таблица слияния не может поддерживать уникальные ограничения для всей таблицы. Когда вы выполняете вставку, данные помещаются в первую или последнюю таблицу myisam (в зависимости от значения параметра insert_method). mysql гарантирует, что уникальные значения ключей останутся уникальными в этой таблице myisam, но не во всех таблицах коллекции.

г. Когда вы создаете таблицу слияния, не проверяется, существует ли базовая таблица и имеет ли она ту же организацию. Когда используются таблицы слияния, mysql проверяет, равны ли длины записей каждой отображенной таблицы, но это не очень надежно. Если вы создаете таблицу слияния из непохожих таблиц myisam, вы, скорее всего, столкнетесь со странными проблемами.

Я такой сонный.В интернете видел c и d.Теста нет.Попробуем.

Достоинства: хорошая масштабируемость, и код программы не сильно меняется

Недостаток: этот метод менее эффективен, чем второй

В-третьих, резюмируя

Из трех методов, упомянутых выше, я на самом деле сделал два, первый и второй. Третьего еще не было, поэтому буду более конкретным. Ха-ха. Во всем, что вы делаете, есть степень. Если вы превысите степень, станет очень плохо. Нельзя вслепую построить кластер серверов баз данных. Вам нужно потратить деньги на покупку оборудования. Не разделяйте таблицы вслепую, разделите 1000 таблицы, а хранилище mysql рутировано. В конце концов, оно все равно хранится на жестком диске в виде файлов. Одна таблица соответствует трем файлам, а 1000 подтаблиц соответствуют 3000 файлам, так что поиск тоже будет стать очень медленным. мое предложение

Комбинация метода 1 и метода 2 для разделения таблицы

Комбинация метода 1 и метода 3 используется для разделения таблицы

Мои два предложения подходят для разных ситуаций.Это зависит от конкретной ситуации.Я думаю, что многие люди выберут сочетание метода 1 и метода 3.



Перепечатка, пожалуйста, укажите
Автор: Морской орел
адрес:blog.51One.com/MySQL/949, Также…
Категории