Версия для печати темы
Нажмите сюда для просмотра этой темы в обычном формате
Форум _ Языки программирования _ Как Лучше Организовать Базу Данных В Mysql?
| | |Автор: Wyvvernbuse 2.11.2009, 11:12
Как лучше организовать базу данных в mysql?
планируется что база будет большая, и запросов будет много и нагрузка будет расти, что лучше делать одну большую таблицу и пихать по максимуму в нее инфу, или дробить узко направлено на мелкие таблички? интересно с точки зрения скорости обработки , нагрузки на сервер, и возможно безопасности инфы например стоит ли инфу какую дублировать в двух таблицах или не стоит это делать ? сейчас ситуация такая, мне надо например куда-то впихнуть права доступа к разному функционалу (специфическому не доступному большинству) вот я и думаю сделать отдельную таблицу, или сделать столбец дополнительный в той что есть ?
и еще вопрос про дублирование и безопасность, стоит дублировать инфу связанную например с бух учетом ? например в одну таблицу доступ только клиентов а в другую с другим набором инфы доступ персонала ? например бухгалтеров? или не надо эти дубли и все делать в одной таблице и не плодить дубли ?
Так же буду рад любым советам по вопросу оптимизации php и mysql
Автор: шпунтик 28.2.2011, 17:06
Оптимизируйте запросы для кэша запросов
У большинства MySQL серверов включено кэширование запросов. Один из наилучших способов улучшения производительности — просто предоставить кэширование самой базе данных. Когда какой-либо запрос повторяется много раз, его результат берется из кэша, что гораздо быстрее прямого обращения к базе данных.
Основная проблема в том, что многие просто используют запросы, которые не могут быть закэшированны:
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
// а так будет!
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");
Причина в том, что в первом запросе используется функция CURDATE(). Это относиться ко всем функциям, подобным NOW(), RAND() и другим, результат которых не детерминирован. Если результат функции может измениться, то MySQL не кэширует такой запрос. В данном примере это можно предотвратить вычислением даты до выполнения запроса.
Автор: шпунтик 28.2.2011, 17:11
Используйте EXPLAIN для ваших запросов SELECT
Используя EXPLAIN, вы можете посмотреть, как именно MySQL выполняет ваш запрос. Это может помочь вам избавиться от слабых мест производительности и других проблем в вашем запросе или в структуре таблиц.
Результат EXPLAIN покажет вам, какие используются индексы, как выбираются и сортируются таблицы и т.д.
Возьмите ваш SELECT запрос (он может быть сложным, с объединениями) и добавьте в начало ключевое слово EXPLAIN. Для этого вы можете использовать phpmyadmin. В результате вы получите очень интересную таблицу. Для примера, пусть я забыл добавить индекс в таблицу, которая участвует в объединении:
|
После добавления индекса для поля group_id:
|
Теперь вместо 7883 строк, выбираются только 9 и 16 строк из двух таблиц. Перемножение всех чисел в столбце rows даст число прямо пропорциональное производительности запроса.
Автор: шпунтик 28.2.2011, 17:12
LIMIT 1, когда нужна единственная строка
Иногда, обращаясь к таблице, вы точно знаете, что вам нужна только одна конкретная строка. Например, нужно получить одну уникальную строку или просто проверить существование записей, удовлетворяющих запросу WHERE.
В этом случае, добавление LIMIT 1 в ваш запрос будет оптимальнее. Таким образом, база данных остановит выборку записей, после нахождения первой же, вместо того, чтобы выбрать всю таблицу или индекс.
// можно так:
$r = mysql_query("SELECT * FROM user WHERE state = 'Alabama'");
if (mysql_num_rows($r) > 0) {// ...}
// но так лучше:
$r = mysql_query("SELECT * FROM user WHERE state = 'Alabama' LIMIT 1");
if (mysql_num_rows($r) > 0) { // ...}
Автор: шпунтик 28.2.2011, 17:15
Индексируйте поля, по которым ищите
Индекс это не только основной или уникальный ключ. Это так же любые столбцы в таблице, которые вы используете для поиска и их можно проиндексировать.
|
Как вы можете заметить, это правило также применимо для части строк, например — «last_name LIKE 'a%'». При поиске с начала строки, MySQL использует индекс этого столбца.
Вы так же должны понимать, что это не сработает для регулярных выражений. Например, когда вы ищите слово (т.е. «WHERE post_content LIKE '%apple%'»), то от обычного индекса не будет никакого толку. Лучше будет использовать полнотекстовый поиск или создать вашу собственную систему индексации.
Автор: шпунтик 28.2.2011, 17:16
Индексируйте поля для объединения и используйте для них одинаковые типы столбцов
Если ваше приложение содержит много объединений таблиц, вам необходимо проиндексировать в обеих таблицах поля, используемые для объединения. Это повлияет на то, как MySQL делает внутреннюю оптимизацию объединений.
Так же эти столбцы должны быть одного типа. Например, если вы объединяете столбец DECIMAL со столбцом INT из другой таблицы, MySQL не сможет использовать хотя бы один из индексов. Даже кодировки символов должны быть одного типа для строковых столбцов.
$r = mysql_query("SELECT company_name FROM usersJOIN companies ON (users.state = companies.state)users.id = $user_id");
// обе колонки state должны быть проиндексированны
// они обе должны иметь один тип данных и кодировку символов
// а иначе MySQL сделает полную выборку из этих таблиц
Автор: шпунтик 28.2.2011, 17:17
Не используйте ORDER BY RAND()
(Имеется в виду выборка единственной строки. Примечание переводчика)
Это одна из тех вещей, который выглядят очень хорошо на первый взгляд, но многие начинающие программисты попались на эту удочку. Вы даже не представляете, какое слабое место в производительности возникнет, если будете использовать это в запросах.
Если вам действительно нужен случайный порядок строк в запросе, то есть лучшие способы сделать это. Конечно, это приведет к дополнительному коду, но позволит избавиться от слабого места в производительности, которое будет сужаться экспоненциально при увеличении данных. Проблема в том, что MySQL будет выполнять RAND() (а это нагрузка на процессор) для каждой строки при сортировке, выдавая только одну строку.
Таким образом вы выберите случайный номер, который меньше количества строк и используете его для смещения в LIMIT.
Автор: шпунтик 28.2.2011, 17:18
Избегайте SELECT *
Чем больше данных считывается из таблицы, тем медленнее запрос. Это увеличивает время работы с хранилищем данных. Также, когда сервер базы данных установлен отдельно от web-сервера, будет большая задержка при передаче данных по сети.
Прописывать, какие именно столбцы из запроса вам нужны — хорошая привычка.
$r = mysql_query(«SELECT * FROM user WHERE user_id = 1»);
$d = mysql_fetch_assoc($r);
echo «Welcome {$d['username']}»;
// лучше:
$r = mysql_query(«SELECT username FROM user WHERE user_id = 1»);
$d = mysql_fetch_assoc($r);
echo «Welcome {$d['username']}»;
// разница более значительна при большем наборе данных.
Автор: шпунтик 28.2.2011, 17:19
Старайтесь всегда создать поле ID
В каждой таблице нужно поле id, которое будет PRIMARY KEY, AUTO_INCREMENT, а так же иметь тип INT. Так же неплохо, чтобы оно было UNSIGNED, т.к. вряд ли у идентификатора будут отрицательные значения.
Даже если в вашей таблице пользователей есть уникальное поле username, не делаете его основным ключом. Использование поля VARCHAR, как основного ключа, очень медлительно. Да и структура вашего кода, относящаяся к пользователям, будет гораздо лучше, если у каждого пользователя будет свой внутренний идентификатор.
Есть так же и внутренние операции MySQL, использующие первичный ключ. И это становиться очень важно для более сложных конфигураций базы данных (кластеры, распараллеливание и т.д.)
Исключение из этого правила составляют «таблицы ассоциаций», используемые для связи «многие-ко-многим» между 2 таблицами. Например, таблица «posts_tags», содержит 2 поля: post_id, tag_id, который используется для объединения между двумя таблицами «Posts» и «Tags». Эта таблица будет иметь первичный ключ составленный из 2 полей.
Автор: шпунтик 28.2.2011, 17:19
Используйте ENUM вместо VARCHAR
ENUM — очень быстрый и компактный тип поля. Значения в нем храниться так же, как TINYINT, но отображаются как в строковом поле. Это делает его незаменимым в некоторых случаях.
Если у вас есть поле, в котором будет вполне определенный набор значений, используйте ENUM вместо VARCHAR. Например, если есть поле «status», его значения могут быть «active», «inactive», «pending», «expired» и т.д.
Можно даже получить от MySQL «совет» о том, как перестроить таблицу. Если у вас есть поле VARCHAR, MySQL может предложить заменить его на ENUM. Для этого используется PROCEDURE ANALYSE(), описанная ниже.
Автор: шпунтик 28.2.2011, 17:21
Используйте подсказки от PROCEDURE ANALYSE()
PROCEDURE ANALYSE() анализирует структуру вашей таблицы и данные в ней, и выдает возможные советы по оптимизации. Это возможно только при наличии реальных данных в таблице, т.к. анализ делается в основном на их основе.
Например, если вы создали первичный ключ типа INT, а записей не очень много, MySQL может предложить заменить его на MEDIUMINT. Или, если используется VARCHAR в котором есть несколько уникальных значений, будет предложен ENUM.
В phpmyadmin в структуре таблице есть ссылка «Анализ структуры таблицы», результат которой может быть, например, следующим:
|
Учтите, что это только советы. Если вы добавите еще записей, они могут стать не актуальными. В конечном итоге вам решать — использовать их или нет.
Автор: шпунтик 28.2.2011, 17:22
Используйте NOT NULL, если это возможно
Если есть особые причины использовать NULL — используйте его. Но перед этим спросите себя — есть ли разница между пустой строкой и NULL (для INT — 0 или NULL). Если таких причин нет, используйте NOT NULL.
NULL занимает больше места и, к тому же, усложняет сравнения с таким полем. Избегайте его, если это возможно. Тем не менее, бывают веские причины использовать NULL, это не всегда плохо.
Из документации MySQL:
«Столбцы NULL занимают больше места в записи, из-за необходимости отмечать, что это NULL значение. Для таблиц MyISAM, каждое поле с NULL занимает 1 дополнительный бит, который округляется до ближайшего байта».
Автор: шпунтик 28.2.2011, 17:24
Prepared Statements
Есть множество преимуществ в использовании prepared statements, как для безопасности, так и для улучшения производительности. Prepared statements фильтруют значения данных, добавляемых в запрос, что защищает запросы от SQL инъекций. Конечно, вы можете фильтровать переменные вручную, но тут может сказаться человеческая забывчивость и невнимательность. Конечно, это не столь важно при использовании какого-либо фреймворка или ORM.
Поскольку статья посвящена оптимизации, отмечу также выгоды для нее. Они проявляются, когда запрос выполняется много раз в приложении. Вы можете использовать для prepared statement разные значения, но MySQL будет разбирать запрос только один раз.
Кроме того, последние версии MySQL компилируют prepared statements в бинарную форму, что позволяет повысить эффективность.
Раньше многие программисты избегали prepared statements по одной единственной причине — они не кэшировались MySQL, но с версии 5.1 это не так.
Посмотрите mysqli extension для использования prepared statements или воспользуйтесь абстракцией базы данных, например, PDO.
if ($stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?")) {
// привязываем значения
$stmt->bind_param("s", $state);
// выполняем
$stmt->execute();
// привязываем результат
$stmt->bind_result($username);
// получаем данные
$stmt->fetch();
printf("%s is from %s\n", $username, $state);
$stmt->close();
}
Автор: шпунтик 28.2.2011, 17:25
Небуферизованные запросы
Обычно, делая запрос, скрипт останавливается и ждет результата его выполнения. Вы можете изменить это, используя небуферизованные запросы.
Хорошее описание есть в документации функции mysql_unbuffered_query():
«mysql_unbuffered_query() отправляет SQL-запрос в MySQL, не извлекая и не автоматически буферизуя результирующие ряды, как это делает mysql_query(). С одной стороны, это сохраняет значительное количество памяти для SQL-запросов, дающих большие результирующие наборы. С другой стороны, вы можете начать работу с результирующим набором срезу после получения первого ряда: вам не нужно ожидать выполнения полного SQL-запроса»
Однако есть определенные ограничения. Вам придется считывать все записи или вызывать mysql_free_result() прежде, чем вы сможете выполнить другой запрос. Так же вы не можете использовать mysql_num_rows() или mysql_data_seek() для результата функции.
Автор: шпунтик 28.2.2011, 17:26
Храните IP в UNSIGNED INT
Многие программисты хранят IP адреса в поле типа VARCHAR(15), не зная что можно хранить его в целочисленном виде. INT занимает 4 байта и имеет фиксированный размер поля.
Убедитесь, что используете UNSIGNED INT, т.к. IP можно записать как 32 битное беззнаковое число.
Используйте в запросе INET_ATON() для конвертирования IP адреса в число, и INET_NTOA() для обратного преобразования. Такие же, такие функции есть и в PHP — ip2long() и long2ip() (в php эти функции могут вернуть и отрицательные значения. замечание от хабраюзера The_Lion).
Страницы: 1 2