Форум, знакомства, фото, чат, общение

Здравствуйте, гость ( Вход | Регистрация )

Приглашаем Информационных Партнеров!
> Случайные изображения












> Как Лучше Организовать Базу Данных В Mysql?

, советы по оптимизации php и mysql
Wyvvernbuse
сообщение 2.11.2009, 11:12
Сообщение #1


Новичок
*

Группа: Икринка
Сообщений: 4
Регистрация: 26.10.2009
Из: Russia
Пользователь №: 16792



Как лучше организовать базу данных в mysql?
планируется что база будет большая, и запросов будет много и нагрузка будет расти, что лучше делать одну большую таблицу и пихать по максимуму в нее инфу, или дробить узко направлено на мелкие таблички? интересно с точки зрения скорости обработки , нагрузки на сервер, и возможно безопасности инфы например стоит ли инфу какую дублировать в двух таблицах или не стоит это делать ? сейчас ситуация такая, мне надо например куда-то впихнуть права доступа к разному функционалу (специфическому не доступному большинству) вот я и думаю сделать отдельную таблицу, или сделать столбец дополнительный в той что есть ?

и еще вопрос про дублирование и безопасность, стоит дублировать инфу связанную например с бух учетом ? например в одну таблицу доступ только клиентов а в другую с другим набором инфы доступ персонала ? например бухгалтеров? или не надо эти дубли и все делать в одной таблице и не плодить дубли ?

Так же буду рад любым советам по вопросу оптимизации php и mysql
Go to the top of the page
 
+Quote Post
2 страниц V   1 2 >  
Start new topic
Ответов (1 - 19)
шпунтик
сообщение 28.2.2011, 17:06
Сообщение #2


Опытный Пользователь
****

Группа: Малёк
Сообщений: 241
Регистрация: 20.1.2009
Пользователь №: 14435



Оптимизируйте запросы для кэша запросов
У большинства 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 не кэширует такой запрос. В данном примере это можно предотвратить вычислением даты до выполнения запроса.
Go to the top of the page
 
+Quote Post
шпунтик
сообщение 28.2.2011, 17:11
Сообщение #3


Опытный Пользователь
****

Группа: Малёк
Сообщений: 241
Регистрация: 20.1.2009
Пользователь №: 14435



Используйте EXPLAIN для ваших запросов SELECT
Используя EXPLAIN, вы можете посмотреть, как именно MySQL выполняет ваш запрос. Это может помочь вам избавиться от слабых мест производительности и других проблем в вашем запросе или в структуре таблиц.
Результат EXPLAIN покажет вам, какие используются индексы, как выбираются и сортируются таблицы и т.д.
Возьмите ваш SELECT запрос (он может быть сложным, с объединениями) и добавьте в начало ключевое слово EXPLAIN. Для этого вы можете использовать phpmyadmin. В результате вы получите очень интересную таблицу. Для примера, пусть я забыл добавить индекс в таблицу, которая участвует в объединении:
Прикрепленный файл  1.jpg ( 36.39 килобайт ) Кол-во скачиваний: 3

После добавления индекса для поля group_id:
Прикрепленный файл  2.jpg ( 39.32 килобайт ) Кол-во скачиваний: 3

Теперь вместо 7883 строк, выбираются только 9 и 16 строк из двух таблиц. Перемножение всех чисел в столбце rows даст число прямо пропорциональное производительности запроса.
Go to the top of the page
 
+Quote Post
шпунтик
сообщение 28.2.2011, 17:12
Сообщение #4


Опытный Пользователь
****

Группа: Малёк
Сообщений: 241
Регистрация: 20.1.2009
Пользователь №: 14435



LIMIT 1, когда нужна единственная строка
Иногда, обращаясь к таблице, вы точно знаете, что вам нужна только одна конкретная строка. Например, нужно получить одну уникальную строку или просто проверить существование записей, удовлетворяющих запросу WHERE.
В этом случае, добавление LIMIT 1 в ваш запрос будет оптимальнее. Таким образом, база данных остановит выборку записей, после нахождения первой же, вместо того, чтобы выбрать всю таблицу или индекс.
Код
// есть пользователи в Alabama?
// можно так:
$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) {    // ...}
Go to the top of the page
 
+Quote Post
шпунтик
сообщение 28.2.2011, 17:15
Сообщение #5


Опытный Пользователь
****

Группа: Малёк
Сообщений: 241
Регистрация: 20.1.2009
Пользователь №: 14435



Индексируйте поля, по которым ищите
Индекс это не только основной или уникальный ключ. Это так же любые столбцы в таблице, которые вы используете для поиска и их можно проиндексировать.
Прикрепленный файл  1.jpg ( 37.04 килобайт ) Кол-во скачиваний: 3

Как вы можете заметить, это правило также применимо для части строк, например — «last_name LIKE 'a%'». При поиске с начала строки, MySQL использует индекс этого столбца.
Вы так же должны понимать, что это не сработает для регулярных выражений. Например, когда вы ищите слово (т.е. «WHERE post_content LIKE '%apple%'»), то от обычного индекса не будет никакого толку. Лучше будет использовать полнотекстовый поиск или создать вашу собственную систему индексации.

Go to the top of the page
 
+Quote Post
шпунтик
сообщение 28.2.2011, 17:16
Сообщение #6


Опытный Пользователь
****

Группа: Малёк
Сообщений: 241
Регистрация: 20.1.2009
Пользователь №: 14435



Индексируйте поля для объединения и используйте для них одинаковые типы столбцов
Если ваше приложение содержит много объединений таблиц, вам необходимо проиндексировать в обеих таблицах поля, используемые для объединения. Это повлияет на то, как MySQL делает внутреннюю оптимизацию объединений.
Так же эти столбцы должны быть одного типа. Например, если вы объединяете столбец DECIMAL со столбцом INT из другой таблицы, MySQL не сможет использовать хотя бы один из индексов. Даже кодировки символов должны быть одного типа для строковых столбцов.
Код
// выборки компаний в штате пользователя
$r = mysql_query("SELECT company_name FROM usersJOIN companies ON (users.state = companies.state)users.id = $user_id");
// обе колонки state должны быть проиндексированны
// они обе должны иметь один тип данных и кодировку символов
// а иначе MySQL сделает полную выборку из этих таблиц
Go to the top of the page
 
+Quote Post
шпунтик
сообщение 28.2.2011, 17:17
Сообщение #7


Опытный Пользователь
****

Группа: Малёк
Сообщений: 241
Регистрация: 20.1.2009
Пользователь №: 14435



Не используйте ORDER BY RAND()
(Имеется в виду выборка единственной строки. Примечание переводчика)

Это одна из тех вещей, который выглядят очень хорошо на первый взгляд, но многие начинающие программисты попались на эту удочку. Вы даже не представляете, какое слабое место в производительности возникнет, если будете использовать это в запросах.
Если вам действительно нужен случайный порядок строк в запросе, то есть лучшие способы сделать это. Конечно, это приведет к дополнительному коду, но позволит избавиться от слабого места в производительности, которое будет сужаться экспоненциально при увеличении данных. Проблема в том, что MySQL будет выполнять RAND() (а это нагрузка на процессор) для каждой строки при сортировке, выдавая только одну строку.

Таким образом вы выберите случайный номер, который меньше количества строк и используете его для смещения в LIMIT.
Go to the top of the page
 
+Quote Post
шпунтик
сообщение 28.2.2011, 17:18
Сообщение #8


Опытный Пользователь
****

Группа: Малёк
Сообщений: 241
Регистрация: 20.1.2009
Пользователь №: 14435



Избегайте 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']}»;
// разница более значительна при большем наборе данных.
Go to the top of the page
 
+Quote Post
шпунтик
сообщение 28.2.2011, 17:19
Сообщение #9


Опытный Пользователь
****

Группа: Малёк
Сообщений: 241
Регистрация: 20.1.2009
Пользователь №: 14435



Старайтесь всегда создать поле ID
В каждой таблице нужно поле id, которое будет PRIMARY KEY, AUTO_INCREMENT, а так же иметь тип INT. Так же неплохо, чтобы оно было UNSIGNED, т.к. вряд ли у идентификатора будут отрицательные значения.
Даже если в вашей таблице пользователей есть уникальное поле username, не делаете его основным ключом. Использование поля VARCHAR, как основного ключа, очень медлительно. Да и структура вашего кода, относящаяся к пользователям, будет гораздо лучше, если у каждого пользователя будет свой внутренний идентификатор.
Есть так же и внутренние операции MySQL, использующие первичный ключ. И это становиться очень важно для более сложных конфигураций базы данных (кластеры, распараллеливание и т.д.)
Исключение из этого правила составляют «таблицы ассоциаций», используемые для связи «многие-ко-многим» между 2 таблицами. Например, таблица «posts_tags», содержит 2 поля: post_id, tag_id, который используется для объединения между двумя таблицами «Posts» и «Tags». Эта таблица будет иметь первичный ключ составленный из 2 полей.
Go to the top of the page
 
+Quote Post
шпунтик
сообщение 28.2.2011, 17:19
Сообщение #10


Опытный Пользователь
****

Группа: Малёк
Сообщений: 241
Регистрация: 20.1.2009
Пользователь №: 14435



Используйте ENUM вместо VARCHAR
ENUM — очень быстрый и компактный тип поля. Значения в нем храниться так же, как TINYINT, но отображаются как в строковом поле. Это делает его незаменимым в некоторых случаях.
Если у вас есть поле, в котором будет вполне определенный набор значений, используйте ENUM вместо VARCHAR. Например, если есть поле «status», его значения могут быть «active», «inactive», «pending», «expired» и т.д.
Можно даже получить от MySQL «совет» о том, как перестроить таблицу. Если у вас есть поле VARCHAR, MySQL может предложить заменить его на ENUM. Для этого используется PROCEDURE ANALYSE(), описанная ниже.
Go to the top of the page
 
+Quote Post
шпунтик
сообщение 28.2.2011, 17:21
Сообщение #11


Опытный Пользователь
****

Группа: Малёк
Сообщений: 241
Регистрация: 20.1.2009
Пользователь №: 14435



Используйте подсказки от PROCEDURE ANALYSE()
PROCEDURE ANALYSE() анализирует структуру вашей таблицы и данные в ней, и выдает возможные советы по оптимизации. Это возможно только при наличии реальных данных в таблице, т.к. анализ делается в основном на их основе.
Например, если вы создали первичный ключ типа INT, а записей не очень много, MySQL может предложить заменить его на MEDIUMINT. Или, если используется VARCHAR в котором есть несколько уникальных значений, будет предложен ENUM.
В phpmyadmin в структуре таблице есть ссылка «Анализ структуры таблицы», результат которой может быть, например, следующим:
Прикрепленный файл  1.jpg ( 26.82 килобайт ) Кол-во скачиваний: 0

Учтите, что это только советы. Если вы добавите еще записей, они могут стать не актуальными. В конечном итоге вам решать — использовать их или нет.
Go to the top of the page
 
+Quote Post
шпунтик
сообщение 28.2.2011, 17:22
Сообщение #12


Опытный Пользователь
****

Группа: Малёк
Сообщений: 241
Регистрация: 20.1.2009
Пользователь №: 14435



Используйте NOT NULL, если это возможно
Если есть особые причины использовать NULL — используйте его. Но перед этим спросите себя — есть ли разница между пустой строкой и NULL (для INT — 0 или NULL). Если таких причин нет, используйте NOT NULL.
NULL занимает больше места и, к тому же, усложняет сравнения с таким полем. Избегайте его, если это возможно. Тем не менее, бывают веские причины использовать NULL, это не всегда плохо.
Из документации MySQL:
«Столбцы NULL занимают больше места в записи, из-за необходимости отмечать, что это NULL значение. Для таблиц MyISAM, каждое поле с NULL занимает 1 дополнительный бит, который округляется до ближайшего байта».
Go to the top of the page
 
+Quote Post
шпунтик
сообщение 28.2.2011, 17:24
Сообщение #13


Опытный Пользователь
****

Группа: Малёк
Сообщений: 241
Регистрация: 20.1.2009
Пользователь №: 14435



Prepared Statements
Есть множество преимуществ в использовании prepared statements, как для безопасности, так и для улучшения производительности. Prepared statements фильтруют значения данных, добавляемых в запрос, что защищает запросы от SQL инъекций. Конечно, вы можете фильтровать переменные вручную, но тут может сказаться человеческая забывчивость и невнимательность. Конечно, это не столь важно при использовании какого-либо фреймворка или ORM.
Поскольку статья посвящена оптимизации, отмечу также выгоды для нее. Они проявляются, когда запрос выполняется много раз в приложении. Вы можете использовать для prepared statement разные значения, но MySQL будет разбирать запрос только один раз.
Кроме того, последние версии MySQL компилируют prepared statements в бинарную форму, что позволяет повысить эффективность.
Раньше многие программисты избегали prepared statements по одной единственной причине — они не кэшировались MySQL, но с версии 5.1 это не так.
Посмотрите mysqli extension для использования prepared statements или воспользуйтесь абстракцией базы данных, например, PDO.
Код
// создаем a prepared statement  
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();
}
Go to the top of the page
 
+Quote Post
шпунтик
сообщение 28.2.2011, 17:25
Сообщение #14


Опытный Пользователь
****

Группа: Малёк
Сообщений: 241
Регистрация: 20.1.2009
Пользователь №: 14435



Небуферизованные запросы
Обычно, делая запрос, скрипт останавливается и ждет результата его выполнения. Вы можете изменить это, используя небуферизованные запросы.
Хорошее описание есть в документации функции mysql_unbuffered_query():

«mysql_unbuffered_query() отправляет SQL-запрос в MySQL, не извлекая и не автоматически буферизуя результирующие ряды, как это делает mysql_query(). С одной стороны, это сохраняет значительное количество памяти для SQL-запросов, дающих большие результирующие наборы. С другой стороны, вы можете начать работу с результирующим набором срезу после получения первого ряда: вам не нужно ожидать выполнения полного SQL-запроса»

Однако есть определенные ограничения. Вам придется считывать все записи или вызывать mysql_free_result() прежде, чем вы сможете выполнить другой запрос. Так же вы не можете использовать mysql_num_rows() или mysql_data_seek() для результата функции.
Go to the top of the page
 
+Quote Post
шпунтик
сообщение 28.2.2011, 17:26
Сообщение #15


Опытный Пользователь
****

Группа: Малёк
Сообщений: 241
Регистрация: 20.1.2009
Пользователь №: 14435



Храните IP в UNSIGNED INT
Многие программисты хранят IP адреса в поле типа VARCHAR(15), не зная что можно хранить его в целочисленном виде. INT занимает 4 байта и имеет фиксированный размер поля.
Убедитесь, что используете UNSIGNED INT, т.к. IP можно записать как 32 битное беззнаковое число.
Используйте в запросе INET_ATON() для конвертирования IP адреса в число, и INET_NTOA() для обратного преобразования. Такие же, такие функции есть и в PHP — ip2long() и long2ip() (в php эти функции могут вернуть и отрицательные значения. замечание от хабраюзера The_Lion).
Код
$r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id";
Go to the top of the page
 
+Quote Post
шпунтик
сообщение 28.2.2011, 17:27
Сообщение #16


Опытный Пользователь
****

Группа: Малёк
Сообщений: 241
Регистрация: 20.1.2009
Пользователь №: 14435



Таблицы фиксированного размера (статичные) — быстрее
Если каждая колонка в таблице имеет фиксированный размер, то такая таблица называется «статичной» или «фиксированного размера». Пример колонок не фиксированной длины: VARCHAR, TEXT, BLOB. Если включить в таблицу такое поле, она перестанет быть фиксированной и будет обрабатываться MySQL по-другому.
Использование таких таблицы увеличит эффективность, т.к. MySQL может просматривать записи в них быстрее. Когда надо выбрать нужную строку таблицы, MySQL может очень быстро вычислить ее позицию. Если размер записи не фиксирован, ее поиск происходит по индексу.
Так же эти таблицы проще кэшировать и восстанавливать после падения базы. Например, если перевести VARCHAR(20) в CHAR(20), запись будет занимать 20 байтов, вне зависимости от ее реального содержания.
Используя метод «вертикального разделения», вы можете вынести столбцы с переменной длиной строки в отдельную таблицу.
Go to the top of the page
 
+Quote Post
шпунтик
сообщение 28.2.2011, 17:27
Сообщение #17


Опытный Пользователь
****

Группа: Малёк
Сообщений: 241
Регистрация: 20.1.2009
Пользователь №: 14435



Вертикальное разделение
Вертикальное разделение — означает разделение таблицы по столбцам для увеличения производительности.
Пример 1. Если в таблице пользователей хранятся адреса, то не факт что они будут нужны вам очень часто. Вы можете разбить таблицу и хранить адреса в отдельной таблице. Таким образом, таблица пользователей сократиться в размере. Производительность возрастет.
Пример 2. У вас есть поле «last_login» в таблице. Оно обновляется при каждом входе пользователя на сайт. Но все изменения в таблице очищают ее кэш. Храня это поле в другой таблице, вы сведете изменения в таблице пользователей к минимуму.
Но если вы будете постоянно использовать объединение этих таблиц, это приведет к ухудшению производительности.
Go to the top of the page
 
+Quote Post
шпунтик
сообщение 28.2.2011, 17:28
Сообщение #18


Опытный Пользователь
****

Группа: Малёк
Сообщений: 241
Регистрация: 20.1.2009
Пользователь №: 14435



Разделяйте большие запросы DELETE и INSERT
Если вам необходимо сделать большой запрос на удаление или вставку данных, надо быть осторожным, чтобы не нарушить работу приложения. Выполнение большого запроса может заблокировать таблицу и привести к неправильной работе всего приложения.
Apache может выполнять несколько параллельных процессов одновременно. Поэтому он работает более эффективно, если скрипты выполняются как можно быстрее.
Если вы блокируете таблицы на долгий срок (например, на 30 секунд или дольше), то при большой посещаемости сайта, может возникнуть большая очередь процессов и запросов, что может привести к медленной работе сайта или даже к падению сервера.
Если у вас есть такие запросы, используйте LIMIT, чтобы выполнять их небольшими сериями.
Код
while (1) {
    mysql_query("DELETE FROM logs WHERE log_date <= '2009-10-01' LIMIT 10000");
    if (mysql_affected_rows() ==  0) {
        // удалили
        break;
    }
    // небольшая пауза
    usleep(50000);
}
Go to the top of the page
 
+Quote Post
шпунтик
сообщение 28.2.2011, 17:30
Сообщение #19


Опытный Пользователь
****

Группа: Малёк
Сообщений: 241
Регистрация: 20.1.2009
Пользователь №: 14435



Маленькие столбцы быстрее
Для базы данных работа с жестким диском, возможно, является самым слабым местом. Маленькие и компактные записи обычно лучше с точки зрения производительности, т.к. уменьшают работу с диском.
В документации к MySQL есть список требований к хранилищам данных для всех типов данных.
Если ваша таблица будет хранить мало строк, то не имеет смысла делать основной ключ типом INT, возможно лучше будет сделать его MEDIUMINT, SMALLINT или даже TINYINT. Если вам не нужно хранить время, используйте DATE вместо DATETIME.
Однако будьте осторожны, что бы не вышло как с Slashdot.
Go to the top of the page
 
+Quote Post
шпунтик
сообщение 28.2.2011, 17:30
Сообщение #20


Опытный Пользователь
****

Группа: Малёк
Сообщений: 241
Регистрация: 20.1.2009
Пользователь №: 14435



Выбирайте правильный тип таблицы
Два основных типа таблиц — MyISAM и InnoDB, у каждого есть свои плюсы и минусы.
MyISAM хорошо считывает из таблиц большое количество данных, но он плох для записи. Даже если вы изменяете всего одну строку, блокируется вся таблица, и ни один процесс не может ничего из нее прочитать. MyISAM очень быстро выполняет запросы типа SELECT COUNT(*).
У InnoDB более сложный механизм хранения данных, и он может быть медленнее, чем MyISAM, для маленьких приложений. Но он поддерживает блокировку строк, что более эффективно при масштабировании. Так же поддерживаются некоторые дополнительные функции, такие операции как транзакции.
Подробнее:MyISAM Storage EngineInnoDB Storage Engine
Go to the top of the page
 
+Quote Post

2 страниц V   1 2 >
Reply to this topicStart new topic

 


Текстовая версия Сейчас: 29.3.2024, 13:18

Как Лучше Организовать Базу Данных В Mysql? - Форум




Рейтинг@Mail.ru Rambler's Top100

forum.ribca.net | Web Дизайн: WonderWorker | http://Ribca.Net