предисловие
Как относительно популярная реляционная база данных, Mysql существует в современных ИТ-компаниях в различных эволюционных формах.Структура хранения на основе дерева B+ может обеспечить высокую эффективность запросов, но она также увеличивается с увеличением количества строк, хранящихся в одной таблице.И пространство для хранения увеличивается, в зависимости от количества слоев дерева B+ и пространства страницы кеша эффективность запросов резко падает. следовательно数据归档Это вполне нормальное техническое требование.Архивация обычно требует полного сканирования таблицы для фильтрации данных.Сцена, которую я хочу сегодня записать, - это разброс первичных ключей некоторых SQL-запросов во время архивации данных.
текст
задний план
Ниже приведена структура нашей таблицы goods_info (также создается вторичный индекс goods_id), мы хотим выполнить полное сканирование таблицы и архивировать эту таблицу, используемый sqlselect id from goods_info where id > {index} limit {num}, а затем каждый индекс является максимальным значением в последнем списке идентификаторов. После нескольких циклов окончательное значение запроса не может быть завершено. Весь запрос архива.
| Список | тип |
|---|---|
| id | bigint |
| store_id | bigint |
| goods_id | bigint |
| goods_name | varchar |
| create_time | timestamp |
| update_time | timestamp |
Затем, при использовании тестовой среды, обнаруживается проблема: при запросе, а затем фильтрации и архивации некоторые данные часто упускаются, в результате чего некоторые данные не архивируются должным образом. Вначале я думал, что это проблема конфигурации условия архива, поэтому я проверил в неправильном направлении и, наконец, обнаружил, что список идентификаторов, запрошенный оператором sql, не был последовательным, из-за чего мы пропустили некоторые данные.
Список того, что мы считаем первым поиском идентификатора:
| id |
|---|
| 1 |
| 3 |
| 5 |
| 8 |
| 14 |
Настоящий список идентификаторов первого запроса:
| id |
|---|
| 56 |
| 3 |
| 5 |
| 1 |
| 22 |
Возвращаемые результаты не только не соответствуют порядку, но и приводят к пропуску некоторых данных в наших правилах подачи.
выявить проблему
SQL-запрос, который мы выполнили в первый раз, выглядит так:select id from goods_info where id > -1 limit 100, индекс задается как -1, чтобы начать с первых данных (поскольку все идентификаторы в нашей таблице больше 0). В моем субъективном понимании Mysql, если к условию where прикреплен фильтр поля, это поле или объединенный индекс с префиксом этого поля будет использоваться для оптимизации запроса, чтобы избежать полного сканирования таблицы. Если в where этого sql есть идентификатор, индекс первичного ключа будет использоваться для его запроса, поэтому партнеры, знакомые со структурой базового листового узла дерева B+, также знают, что базовый порядок индекса первичного ключа должен быть в порядке.Как может произойти вышеописанная ситуация?
Объяснив этот sql, мы обнаружили проблему, этот sql фактически использовал вторичный индекс goods_id->id, что приводило к беспорядку результатов запроса.
Проанализируйте причины
Почему Mysql использует вторичный индекс goods_id->id?В основном проблема возникает из-за нашего sql.select id from goods_info where id > -1 limit 100.
- Во-первых, запрашиваемые данные — это только идентификатор, а вторичный индекс может завершить покрытие индекса и удовлетворить требования;
- Во-вторых, хотя и используется условие where, но есть записи минимального и максимального значений индекса первичного ключа в mysql, минимальное значение во всей таблице больше 0, и все данные удовлетворяют условиям. более длинная привязка, Mysql будет
选择性无视С этим условием; - Последнее ограничение заключается в том, что определенные данные необходимо запрашивать в пакетном режиме.
Таким образом, после анализа с вышеуказанной точки зрения, Mysql выбирает вторичный индекс goods_id->id с наименьшей площадью во время оптимизации запроса, чтобы пройти первые данные SQL и вернуть соответствующие результаты, поэтому проблем нет.
Решать проблему
Поскольку выбран неправильный индекс, мы можем заставить этот sql использовать индекс первичного ключа.
- порядок по идентификатору (порядок по индексу предпочтительнее при оптимизации запросов)
- force {index} (индекс силы, само собой разумеется)
- 3. Выберите больше полей, чтобы вторичный индекс не мог использовать преимущества покрытия индекса.
Эпилог
Из-за этой проблемы мы должны приложить все усилия, чтобы изменить некоторые стереотипы в оптимизации запросов Mysql для будущего использования и узнать больше, чтобы проанализировать основные причины.