предисловие
Сразу к делу, как бы вы ответили на такой вопрос?
10 миллионов, 20 миллионов или сотни миллионов единиц данных? Конкретный ответ не важен, и конечно это не будет фиксированное число.Сегодня мы вместе обсудим этот вопрос.
InnoDB — это механизм хранения общего назначения, учитывающий высокую надежность и высокую производительность, он имеет множество функций и возможностей, а его архитектура и принципы работы также сложны. На самом деле хочу объяснить ясно и подробно, не один или два сообщения в блоге не могут достичь, и это не в центре внимания сегодня.
Поэтому эта статья не требует слишком много принципиальных знаний.Мы сосредоточимся на вопросах, поднятых в начале, и проверим, ознакомившись с некоторыми основными понятиями и используя инструменты, чтобы хорошо знать эту проблему.
файловая структура
Мы знаем, что движок InnoDB поддерживает транзакции, поэтому данные в таблице должны храниться на диске. Если в тестовой базе данных создать две таблицы: t1 и t2, то в соответствующем каталоге данных будут найдены два файла.
[root@localhost test]# ls
db.opt t1.frm t1.ibd t2.frm t2.ibd
[root@localhost test]# pwd
/var/lib/mysql/test
Среди них файл frm — это информация о структуре таблицы, а файл ibd — это данные в таблице.
Информация о структуре таблицы содержит метаданные таблицы MySQL (например, определение таблицы), такие как имя таблицы, количество столбцов в таблице, тип данных столбца и т. д. Это не важно, мы оставим это в одиночестве;
Файл ibd хранит данные в таблице, такие как строки данных и индексы. Этот документ более важен, и именно он находится в центре нашего сегодняшнего исследования.
Мы говорим, что данные в таблице MySQL хранятся на диске. Тогда на диске наименьшая единица — это сектор, и каждый сектор может хранить 512 байт данных; наименьшая единица в операционной системе — это блок, а наименьшая единица — 4 КБ.
В системе Windows мы можем передатьfsutil fsinfo ntfsinfo c:Проверять.
C:\Windows\system32>fsutil fsinfo ntfsinfo c:
NTFS 卷序列号: 0x78f40b2cf40aec66
NTFS 版本: 3.1
LFS 版本: 2.0
扇区数量: 0x000000001bcb6fff
簇总数: 0x0000000003796dff
可用簇: 0x0000000000a63a03
保留总数: 0x00000000000017c3
每个扇区字节数: 512
每个物理扇区字节数: 4096
每个簇字节数: 4096
每个 FileRecord 段字节数: 1024
每个 FileRecord 段簇数: 0
В системах Linux его можно просмотреть с помощью следующих двух команд, в зависимости от формата файловой системы.
xfs_growfs /dev/mapper/centos-root | grep bsize
tune2fs -l /dev/mapper/centos-root | grep Block
Давайте вернемся назад и скажем, MySQL, механизм хранения InnoDB также имеет минимальную единицу хранения, называемую страницей, и размер по умолчанию составляет 16 КБ.
Создаем новую таблицу t3, данных в ней нет, посмотрим ее ibd файл.
[root@localhost test]# ll
总用量 18579600
-rw-r-----. 1 mysql mysql 67 11月 30 20:59 db.opt
-rw-r-----. 1 mysql mysql 12756 12月 7 21:10 t1.frm
-rw-r-----. 1 mysql mysql 13077839872 12月 7 21:37 t1.ibd
-rw-r-----. 1 mysql mysql 8608 12月 7 21:43 t2.frm
-rw-r-----. 1 mysql mysql 5947523072 12月 7 21:52 t2.ibd
-rw-r-----. 1 mysql mysql 12756 12月 8 21:02 t3.frm
-rw-r-----. 1 mysql mysql 98304 12月 8 21:02 t3.ibd
Не только t3, мы видим, что размер файла ibd любой таблицы всегда является целым числом, кратным 16k. Это очень важно понимать.MySQL загружает данные с диска и читает их постранично.Даже если вы запросите фрагмент данных, он прочитает страницу из 16k данных.
кластеризованный индекс
Данные в таблице базы данных хранятся на странице, так сколько записей может хранить эта страница?
Это зависит от размера строки записей, если размер строки данных 1k, то теоретически на одной странице может быть размещено 16 частей данных.
Конечно, при запросе данных MySQL не может просмотреть все страницы, поэтому существует индекс.Движок хранения InnoDB использует дерево B+ для построения индекса.
Кластерный индекс предназначен для построения дерева B+ в соответствии с первичным ключом каждой таблицы.Листовые узлы хранят всю строку данных записи, а неконечные узлы хранят значение ключа и указатель на страницу данных. время, между каждой страницей данных Все связаны двусвязным списком.
Как показано на рисунке выше, это общая структура дерева кластеризованных индексов. Сначала он сортирует записи данных в соответствии с первичным ключом и помещает их на разные страницы.Следующая строка — это страница данных. Приведенный выше неконечный узел хранит значение первичного ключа и указатель на страницу.
Когда мы запрашиваем по первичному ключу, напримерid=6Условием является процесс поиска данных через это дерево B+. Сначала он находит корневую страницу (смещение страницы = 3), а затем находит ее с помощью двоичного поиска.id=6Данные находятся на странице с указателем 5. Далее перейдите на страницу со смещением страницы=5 для загрузки данных.
Здесь нам нужно понять две вещи:
Корневой узел (смещение страницы = 3) дерева B+ на приведенном выше рисунке фиксирован и не изменится. Пока для таблицы создан кластеризованный индекс, ее根节点Номер страницы где-то записан. Другой момент заключается в том, что сам индекс дерева B+ не может напрямую найти конкретную запись, он может только знать, на какой странице находится запись, база данных загрузит страницу в память, а затем найдет конкретную запись с помощью бинарного поиска.
Теперь мы знаем, что наименьшая единица хранения механизма хранения InnoDB — это страница.В структуре индекса дерева B+ страница может помещать построчные данные (конечные узлы) или первичные ключи + указатели (неконечные узлы). .
Как было сказано выше, если размер строки данных 1k, то теоретически на одной странице можно разместить 16 фрагментов данных. Сколько первичных ключей + указателей можно разместить на этой странице?
Предположим, что идентификатор нашего первичного ключа имеет тип bigint, длина — 8 байт, а размер указателя — 6 байт в исходном коде InnoDB. Это 16384/14 = 1170, значит, на странице можно хранить 1170 указателей.
Указатель указывает на страницу для хранения записей, и на странице может быть размещено 16 элементов данных, тогда дерево B+ высотой 2 может хранить 1170 * 16 = 18720 элементов данных. Точно так же дерево B+ высотой 3 может хранить 1170 * 1170 * 16 = 21902400 записей.
Теоретически это так.В движке хранения InnoDB высота дерева B+ обычно составляет 2-4 слоя, что может удовлетворить хранение десятков миллионов данных. При поиске данных поиск на одной странице представляет собой один ввод-вывод. Когда мы запрашиваем индекс первичного ключа, нам на самом деле нужно максимум 2-4 ввода-вывода.
Итак, так ли это на самом деле? Давайте посмотрим вниз.
тип страницы
Прежде чем начать проверку, нам нужно не только понять страницу, но и знать, что в движке InnoDB существует не только один вид страницы. Общие типы страниц:
- Страница данных, узел B-дерева;
- страница отмены, страница журнала отмены;
- Системная страница, Системная страница;
- Страница данных транзакции, Страница системы транзакций;
- Вставить страницу растрового изображения буфера, Вставить растровое изображение буфера;
- Вставить страницу списка свободных буферов, Вставить список свободных буферов;
- Несжатая страница больших двоичных объектов, несжатая страница больших двоичных объектов;
Здесь мы фокусируемся на узле B-Tree, наш индекс и данные размещаются на этой странице. Поскольку существует другой тип страницы, как мы узнаем, к чему принадлежит текущая страница?
Затем нам нужно иметь общее представление о структуре страницы данных Страница данных состоит из семи частей, каждая из которых имеет разное значение.
- File Header, заголовок файла, фиксированные 38 байт;
- Page Header, заголовок страницы, фиксированные 56 байт;
- Инфимум + супремум, фиксированные 26 байт;
- Пользовательские записи, пользовательские записи, то есть записи строк, размер не фиксирован;
- Free Space, свободное пространство, размер не фиксирован;
- Каталог страниц, каталог страниц, размер не фиксирован;
- File Trailer, информация о конце файла, фиксированные 8 байт.
Среди них заголовок файла используется для записи некоторой информации заголовка страницы, занимая в общей сложности 38 байтов. В этой информации заголовка мы можем получить значение смещения страницы в табличном пространстве и тип страницы данных.
Далее следует заголовок страницы, который записывает информацию о состоянии страницы данных, занимая в общей сложности 56 байтов. В этой части мы можем получить две важные информации: количество записей на странице и уровень текущей страницы в дереве индекса, где 0x00 представляет конечный узел, Например, листовой узел в кластеризованном индексе содержит весь ряд данных. Он всегда находится на уровне 0.
проверять
Ранее мы говорили, что файл ibd — это файл данных таблицы. Этот файл будет расти по мере роста данных в таблице базы данных, но он всегда будет целым числом, кратным 16 КБ. Есть страницы одна за другой, потом мы можем разобрать их одну за другой, и мы можем судить, что это за страница по заголовку файла, найти Узел B-дерева, а затем мы можем увидеть уровень страницы внутри, его значение +1 , просто Представляет высоту текущего дерева B+.
Давайте теперь пересоздадим таблицу.Чтобы сделать размер строки данных в этой таблице 1k, мы можем установить несколько полей char(255).
CREATE TABLE `t5` (
`id` bigint(8) NOT NULL,
`c1` char(245) NOT NULL DEFAULT '1',
`c2` char(255) NOT NULL DEFAULT '1',
`c3` char(255) NOT NULL DEFAULT '1',
`c4` char(255) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Затем автор написал хранимую процедуру для пакетной вставки данных, для ускорения пакетной вставки автор также модифицировалinnodb_flush_log_at_trx_commit=0, помните, что нельзя так играть в производственной среде.
BEGIN
DECLARE i int DEFAULT 0;
select ifnull(max(id),0) into i from t5;
set i = i+1;
WHILE i <= 100000 DO
insert into t5(id)value(i);
set i = i+1;
END WHILE;
END
innodbPageInfo.jarЭто класс инструмента, написанный автором с помощью кода Java для вывода информации о странице в файл ibd.
-path 后面是文件的路径，-v 是否显示页的详情信息，0是 1否。
Мы создали таблицу t5 выше.Если данных нет, давайте посмотрим на информацию этого файла ibd.
[root@localhost innodbInfo]# java -jar innodbPageInfo.jar -path /var/lib/mysql/test/t5.ibd -v 0
page offset 00000000,page type <File Space Header>
page offset 00000001,page type <Insert Buffer Bitmap>
page offset 00000002,page type <File Segment inode>
page offset 00000003,page type <B-tree Node>,page level <0000>
page offset 00000000,page type <Freshly Allocated Page>
page offset 00000000,page type <Freshly Allocated Page>
数据页总记录数:0
Total number of page: 6
Insert Buffer Bitmap: 1
File Segment inode: 1
B-tree Node: 1
File Space Header: 1
Freshly Allocated Page: 2
[root@localhost innodbInfo]#
В таблице t5 сейчас нет никаких данных, а размер ее файла ibd равен 98304, что означает всего 6 страниц. Четвертая страница (смещение страницы 3) — это страница данных, а уровень страницы равен 0, что означает, что страница является конечным узлом. Поскольку данных пока нет, можно считать, что индекс дерева B+ имеет только один уровень.
Затем мы вставляем 10 фрагментов данных, уровень страницы по-прежнему равен 0, а высота B+-дерева по-прежнему равна 1, потому что страница может хранить около 16 фрагментов данных размером 1 КБ.
page offset 00000003,page type <B-tree Node>,page level <0000>
数据页总记录数:10
Total number of page: 6
Когда мы вставляем 15 фрагментов данных, одну страницу нельзя поместить, исходно выделенная страница (Свежераспределенная страница) станет страницей данных, а исходная корневая страница (смещение страницы = 3) будет обновлена до страницы элемента каталога хранилища. . Смещения 04 и 05 становятся страницами данных листовых узлов, поэтому высота всего дерева B+ теперь равна 2.
page offset 00000003,page type <B-tree Node>,page level <0001>
page offset 00000004,page type <B-tree Node>,page level <0000>
page offset 00000005,page type <B-tree Node>,page level <0000>
数据页总记录数:15
Total number of page: 6
Продолжайте вставлять 10 000 данных, давайте посмотрим на B + слишком высоко. Конечно, информация сейчас больше, мы пишем результаты вывода в файл.
java -jar innodbPageInfo.jar -path /var/lib/mysql/test/t5.ibd -v 0 > t5.txt
Перехваченная часть результатов выглядит следующим образом:
[root@localhost innodbInfo]# vim t5.txt
page offset 00000003,page type <B-tree Node>,page level <0001>
page offset 00000004,page type <B-tree Node>,page level <0000>
page offset 00000005,page type <B-tree Node>,page level <0000>
page offset 00000000,page type <Freshly Allocated Page>
数据页总记录数:10000
Total number of page: 1216
B-tree Node: 716
Видно, что 10 000 записей размером 1k разделяют 716 страниц данных, а высота дерева, отображаемая на корневой странице, по-прежнему составляет 2 слоя.
Ранее мы подсчитали, что двухслойное дерево B+ теоретически может хранить около 18 000 единиц данных, автор протестировал около 13 000 единиц данных, и дерево B+ станет трехуровневым.
page offset 00000003,page type <B-tree Node>,page level <0002>
数据页总记录数:13000
Total number of page: 1472
B-tree Node: 933
Причину понять несложно, потому что на каждой странице нельзя размещать только сами данные.
Во-первых, каждая страница имеет некоторые фиксированные форматы, такие как заголовок файла, заголовок страницы и конец файла.用户记录в этой части;
Во-вторых, пользовательская запись не только помещает строки данных, но каждая строка данных имеет некоторые другие отметки, такие как необходимость удаления, минимальная запись, количество записей, информация о местоположении в куче, тип записи, относительное положение следующей записи и т. д.;
Кроме того, в справочном руководстве MySQL также упоминается, что InnoDB зарезервирует 1/16 страницы для вставки или обновления индекса в будущем.Если идентификатор первичного ключа не вставляется последовательно, он может быть не равен 1. /16, и он будет занимать больше свободного места.
Короче говоря, мы понимаем, что страница не будет заполнена данными. Таким образом, совершенно нормально, что фактическое измерение не соответствует теории, поскольку приведенная выше теория не исключает эти термины.
Далее мы вставляем еще 10 миллионов фрагментов данных, и теперь файл ibd достиг 11 ГБ.
page offset 00000003,page type <B-tree Node>,page level <0002>
数据页总记录数:10000000
Total number of page: 725760
B-tree Node: 715059
Мы видим, что при 10 миллионах фрагментов данных уже имеется 710 000 страниц данных, а высота дерева B+ по-прежнему составляет 3 слоя, а это означает, что эффективность запросов десятков тысяч фрагментов данных и 10 миллионов фрагментов данных в основном то же самое.
Например, теперь мы запрашиваем часть данных на основе идентификатора первичного ключа,select * from t5 where id = 6548215;, время запроса показывает, что это заняло 0,010 секунды.
Когда будет на 4 этаже? Приблизительно к 13 миллионам дерево B+ увеличит высоту дерева до 4 слоев.
Когда будет на 5 этаже? Автор не проверял, так как при вставке 50 миллионов кусков данных размер файла данных ibd был уже 55G, а на виртуальной машине заканчивалось место. .
page offset 00000003,page type <B-tree Node>,page level <0003>
数据页总记录数:50000000
B-tree Node: 3575286
Даже если это 50 миллионов фрагментов данных, мы запрашиваем идентификатор первичного ключа, а время запроса составляет миллисекунды.
Теоретически, чтобы достичь 1 миллиарда - 10 миллиардов строк данных, высота дерева может достигать только 5 слоев. Если есть небольшой партнер, который использует этот метод для проверки 5-этажных данных, оставьте сообщение в области комментариев и позвольте мне взглянуть.
Кроме того, друзья осознали проблему? На самом деле на высоту B+-дерева влияют не только строки данных, но и длина идентификатора первичного ключа. В нашем тесте выше тип идентификатора — bigint (8). Когда длина других полей остается неизменной, мы меняем тип идентификатора на int (4). Та же высота дерева вмещает больше данных, поскольку количество указателей объем, который может нести одна страница, увеличился.
CREATE TABLE `t6` (
`id` int(4) NOT NULL,
`c1` char(245) NOT NULL DEFAULT '1',
`c2` char(255) NOT NULL DEFAULT '1',
`c3` char(255) NOT NULL DEFAULT '1',
`c4` char(255) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Для таблицы t6 мы вставляем 16 000 фрагментов данных, а затем выводим информацию о странице.
page offset 00000003,page type <B-tree Node>,page level <0001>
数据页总记录数:16000
B-tree Node: 1145
Посмотрим, если мы тестируем по типу идентификатора первичного ключа bigint(8), когда имеется 13 000 элементов данных, высота дерева уже равна 3, а теперь она изменена на int(4), 16 000 элементов данных, высота дерева по-прежнему 2 слоя. Хотя количество страниц данных (B-tree Node) по-прежнему велико, изменение невелико, но не влияет на высоту дерева.
Хорошо, видя это, я считаю, что у моих друзей уже есть свои ответы на вопросы, поднятые в начале.Если вы попробуете еще раз, ваше понимание может быть более глубоким.
Смотрите это, а также на роуд классическим вопросам лица: почему вы хотите использовать MySQL-индекс B + дерево, а не какую-то другую структуру дерева, такую как B-дерево ??
Проще говоря, одна из причин в том, что высота дерева B+ относительно стабильна, потому что его нелистовые узлы не сохраняют данные, а сохраняют только значения ключей и указатели, страница может нести большое количество данные. Вы думаете, что нелистовые узлы дерева B также будут сохранять данные. Та же строка размера данных составляет 1 КБ, поэтому она может сохранять только до 16 указателей на страницу. В случае большого объема данных дерево высота будет быстрее Инфляция приведет к большому количеству операций ввода-вывода, а запросы станут очень медленными.
Адрес источника
этой статьиinnodbPageInfo.jarКод является ссылкой автораMySQL技术内幕（InnoDB存储引擎）Инструментарий в книге автора книги написан на Python, поэтому здесь автор повторно реализовал его на Java.
Выкладываю исходный код Java-версии на GitHub:GitHub.com/Тао Сюнь/в нет…
Запакованную версию Jar также можно загрузить:disk.baidu.com/yes/1I Z V Jr NUK…Код экстракции: 5rnz.
Друзья, вы можете взглянуть на этот инструмент и посмотреть, сколько слоев индексов дерева B+ существует для таблиц, которые вы считаете большими?
Использованная литература:
Цзян Чэнъяо: «Инсайдер технологии MySQL: механизм хранения InnoDB»
Тяня Слез Сяову:Слезы конца света.blog.CSDN.net/article/decent…
Развевающийся красный шарф:Блог woohoo.cn на.com/Lee Freeman/…
Официальное справочное руководство MySQL:Dev.MySQL.com/doc/Furious/…