Введение
Запрос на пейджинг MySQL — часто задаваемый вопрос на собеседованиях по Java.Здесь необходимо практиковаться.Ведь практика приносит истинные знания. Многие студенты страдают от нехватки массивных данных при выполнении тестов, ведь у официала есть набор тестовых библиотек.
2. Данные моделирования
Здесь смоделированные данные импортируются в двух случаях: если вам нужна только проверка данных, рекомендуется использовать официальные данные. Если официальные данные не соответствуют потребностям, то моделируем данные сами.
1. Импортируйте официальную тестовую библиотеку
скачатьОфициальный файл базы данныхили вgithubскачать.
Библиотека тестов содержит 6 таблиц.
Введите первыйemployees_db, выполните команду импорта данных
mysql -uroot -proot -t < employees.sql
Некоторые среды могут сообщать об ошибках
ERROR 1193 (HY000) at line 38: Unknown system variable 'storage_engine'
Подключитесь к mysql, чтобы просмотреть движок по умолчанию и убедиться, что это не проблема с локальной средой.
mysql> show variables like '%engine%';
+----------------------------------+--------+
| Variable_name | Value |
+----------------------------------+--------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| disabled_storage_engines | |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set (0.01 sec)
Исправлятьemployees.sqlсценарий
set default_storage_engine = InnoDB;
-- set storage_engine = MyISAM;
-- set storage_engine = Falcon;
-- set storage_engine = PBXT;
-- set storage_engine = Maria;
select CONCAT('storage engine: ', @@default_storage_engine) as INFO;
Выполните снова и обнаружите, что импорт прошел успешно
➜ employees_db mysql -uroot -proot -t < employees.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------------------+
| INFO |
+-----------------------------+
| CREATING DATABASE STRUCTURE |
+-----------------------------+
+------------------------+
| INFO |
+------------------------+
| storage engine: InnoDB |
+------------------------+
+---------------------+
| INFO |
+---------------------+
| LOADING departments |
+---------------------+
+-------------------+
| INFO |
+-------------------+
| LOADING employees |
+-------------------+
+------------------+
| INFO |
+------------------+
| LOADING dept_emp |
+------------------+
+----------------------+
| INFO |
+----------------------+
| LOADING dept_manager |
+----------------------+
+----------------+
| INFO |
+----------------+
| LOADING titles |
+----------------+
+------------------+
| INFO |
+------------------+
| LOADING salaries |
+------------------+
Результаты проверки (изменение конфигурации такое же, как указано выше)
➜ employees_db mysql -uroot -proot -t < test_employees_sha.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------------+
| INFO |
+----------------------+
| TESTING INSTALLATION |
+----------------------+
+--------------+------------------+------------------------------------------+
| table_name | expected_records | expected_crc |
+--------------+------------------+------------------------------------------+
| departments | 9 | 4b315afa0e35ca6649df897b958345bcb3d2b764 |
| dept_emp | 331603 | d95ab9fe07df0865f592574b3b33b9c741d9fd1b |
| dept_manager | 24 | 9687a7d6f93ca8847388a42a6d8d93982a841c6c |
| employees | 300024 | 4d4aa689914d8fd41db7e45c2168e7dcb9697359 |
| salaries | 2844047 | b5a1785c27d75e33a4173aaa22ccf41ebd7d4a9f |
| titles | 443308 | d12d5f746b88f07e69b9e36675b6067abb01b60e |
+--------------+------------------+------------------------------------------+
Мы видим, что emp содержит около 330 000 единиц данных.
2. Хранимая процедура для импорта смоделированных данных
Здесь мы можем выбрать пакетный импорт хранимой процедуры.
Сначала создайте таблицу
drop table if exists `user`;
create table `user`(
`id` int unsigned auto_increment,
`username` varchar(64) not null default '',
`score` int(11) not null default 0,
primary key(`id`)
)ENGINE = InnoDB;
Создать хранимую процедуру
DROP PROCEDURE IF EXISTS batchInsert;
delimiter ? -- 声明存储过程结束符号
create procedure batchInsert() -- 创建存储过程
begin -- 存储过程主体开始
declare num int; -- 声明变量
set num=1; -- 初始值
while num<=3000000 do -- 循环条件
insert into user(`username`,`score`) values(concat('user-', num),num); -- 执行语句
set num=num+1; -- 循环变量自增
end while; -- 结束循环
end? -- 存储过程主体结束
delimiter ; #恢复;表示结束
CALL batchInsert; -- 执行存储过程
Видно, что тестовые данные 300 Вт вставляются примерно через 1046 с. Что ж, первоначальный план по импорту результатов 1000 Вт занял слишком много времени.
В-третьих, повторяемость и оптимизация часто используемых запросов на подкачку MySQL.
берем существующую таблицуuserДля тестирования таблица содержит 300 Вт данных.
1. Предварительная проверка
Сначала посмотрите на структуру таблицы и какие индексы существуют в настоящее время.
mysql> desc user;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(30) | NO | | | |
| score | int(11) | NO | | 0 | |
+----------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user | 0 | PRIMARY | 1 | id | A | 2991886 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
могу видеть толькоidИндекс первичного ключа.
Затем проверьте, включен ли он缓存(Избегайте влияния кеша запросов на эффективность выполнения)
mysql> show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
6 rows in set (0.00 sec)
mysql> show profiles;
Empty set, 1 warning (0.00 sec)
have_query_cacheа такжеquery_cache_typeУказывает, что кэширование поддерживается, но не включено.show profilesДисплей пуст, что указывает на то, что функция профилей отключена.
включиprofiles
mysql> SET profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show profiles;
+----------+------------+-------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------+
| 1 | 0.00012300 | SET profiling = 1 |
+----------+------------+-------------------+
1 row in set, 1 warning (0.00 sec)
2. Нет запроса на подкачку индекса
Как правило, наиболее часто используемый метод запроса пейджинга:order by + limit m,nКстати, теперь мы тестируем производительность пейджинга
select * from user order by score limit 0,10; -- 10 rows in set (0.65 sec)
select * from user order by score limit 10000,10; -- 10 rows in set (0.83 sec)
select * from user order by score limit 100000,10; -- 10 rows in set (1.03 sec)
select * from user order by score limit 1000000,10; -- 10 rows in set (1.14 sec)
Здесь мы подтверждаем, используется ли индекс
mysql> explain select * from user order by score limit 1000000,10;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 2991995 | 100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
Видно, что индекс не используется, а полное сканирование таблицы 100W подкачки данных занимает около 1,14 с.
3. Индексированный пейджинговый запрос
select * from user order by id limit 10000,10; -- 10 rows in set (0.01 sec)
select * from user order by id limit 1000000,10; -- 10 rows in set (0.18 sec)
select * from user order by id limit 2000000,10; -- 10 rows in set (0.35 sec)
Запрос использует индекс первичного ключа, поэтому эффективность запроса относительно высока. Видно, что когда объем данных становится больше, эффективность запроса значительно падает.
Здесь мы подтверждаем, используется ли индекс
mysql> explain select * from user order by id limit 2000000,10;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------+
| 1 | SIMPLE | user | NULL | index | NULL | PRIMARY | 4 | NULL | 2000010 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------+
1 row in set, 1 warning (0.00 sec)
Видно, что использовалось полное сканирование индекса, и всего было запрошено 2 000 010 строк данных.
4. Оптимизация
Согласно инструменту диагностического анализа запросов, который поставляется с MYSQL, мы проверяем трудоемкие детали каждой операции, выполняемой оператором SQL. Видно, что все 2 000 010 записей, полученных запросом, возвращаются клиенту, а затраты времени в основном сосредоточены на этапе отправки данных. Но клиенту нужно только 10 фрагментов данных, можем ли мы вернуть клиенту только 10 фрагментов данных?
mysql> show profiles;
+----------+------------+---------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------------------+
| 1 | 0.00012300 | SET profiling = 1 |
| 2 | 0.00009200 | SET profiling = 1 |
| 3 | 0.35689500 | select * from user order by id limit 2000000,10 |
| 4 | 0.00023900 | explain select * from user order by id limit 2000000,10 |
+----------+------------+---------------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)
mysql> show profile for query 3;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000071 |
| checking permissions | 0.000007 |
| Opening tables | 0.000012 |
| init | 0.000017 |
| System lock | 0.000008 |
| optimizing | 0.000005 |
| statistics | 0.000024 |
| preparing | 0.000016 |
| Sorting result | 0.000004 |
| executing | 0.000003 |
| Sending data | 0.356653 |
| end | 0.000013 |
| query end | 0.000005 |
| closing tables | 0.000008 |
| freeing items | 0.000019 |
| cleaning up | 0.000030 |
+----------------------+----------+
16 rows in set, 1 warning (0.00 sec)
онлайн-оптимизация: подзапрос + покрывающий индекс
mysql> select * from user where id > (select id from user order by id limit 2000000, 1) limit 10;
+---------+--------------+---------+
| id | username | score |
+---------+--------------+---------+
| 2000002 | user-2000002 | 2000002 |
| 2000003 | user-2000003 | 2000003 |
| 2000004 | user-2000004 | 2000004 |
| 2000005 | user-2000005 | 2000005 |
| 2000006 | user-2000006 | 2000006 |
| 2000007 | user-2000007 | 2000007 |
| 2000008 | user-2000008 | 2000008 |
| 2000009 | user-2000009 | 2000009 |
| 2000010 | user-2000010 | 2000010 |
| 2000011 | user-2000011 | 2000011 |
+---------+--------------+---------+
10 rows in set (0.29 sec)
mysql> explain select * from user where id > (select id from user order by id limit 2000000, 1) limit 10;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| 1 | PRIMARY | user | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 1495997 | 100.00 | Using where |
| 2 | SUBQUERY | user | NULL | index | NULL | PRIMARY | 4 | NULL | 2000001 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
2 rows in set, 1 warning (0.30 sec)
Однако это не улучшило производительность запросов. Не видно где проблема? Как видно из плана выполнения, индекс соответствует нашим ожиданиям. rows Здесь извлекается много строк. Посмотрите только на подзапросы
mysql> select id from user order by id limit 2000000, 1;
+---------+
| id |
+---------+
| 2000001 |
+---------+
1 row in set (0.29 sec)
mysql> explain select id from user order by id limit 2000000, 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | user | NULL | index | NULL | PRIMARY | 4 | NULL | 2000001 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
Здесь видно, что даже если подзапрос использует покрывающий индекс, он все равно потребляет около 3 с. Я думаю, что это время, затрачиваемое обычным индексным вводом-выводом. Официальных тестовых данных для сравнения я не нашел, а время, затраченное MySQL на IO-запрос, для сравнения.
Теоретически на одной странице первичного ключа int может храниться 1000 ключей, а корень находится в памяти, поэтому второй слой B+Tree имеет около 100 Вт ключей, а тестовые данные разбиваются на 200 Вт. IOs для поиска данных. Если время, затрачиваемое на 2 операции ввода-вывода, составляет 3 с, то 1 должно быть около 1,5 с. Мы проверяем пейджинг около 99 Вт, чтобы убедиться, что он соответствует предположению.
mysql> select id from user order by id limit 990000,1;
+--------+
| id |
+--------+
| 990001 |
+--------+
1 row in set (0.15 sec)
Так что тут автор смело догадывается, что в результате получаются обычные накладные расходы
Четвертый, последний
Первоначально я хотел проверить, надежна ли схема оптимизации подкачки в Интернете, но ожидаемые результаты все же отличаются. Я надеюсь, что умные читатели имеют другое мнение и дадут мне несколько советов. В официальном аккаунте есть QR-код автора WeChat.