|
Значительное время в генерации динамических страницы, занимает извлечение данных из базы сайта. Для того что бы ускорить этот процесс необходимо оптимизировать настройки сервера баз данных, схему базы данных, код запросов.
1. Оптимизация настроек сервера.
В начале необходимо проверить включено ли кэширование запросов (при выполнении запроса SELECT сервер баз данных MySQL "запоминает" сам этот запрос, а так же результат). При повторной отправке аналогичного запроса на сервер, система вернет ответ из кэша, вместо того что бы повторно выполнять данный запрос.
Проверяем включено ли оно: подключаемся к серверу баз данных и выполняем запрос SHOW VARIABLES LIKE '%query_cache%';
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 | 2147483648 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+------------+
6 rows in set (0.00 sec) |
Значение переменно have_query_cache говорит включено ли кэширование.
Переменная query_cache_type описывает какой тип кэширования используется: OFF(0) - кэширование отключено, ON(1) - кэширование включено для всех запросов, за исключением использования операторов SELECT с опцией SQL_NO_CACHE, DEMAND (2) - позволяет активировать кэширование запросов по требованию, когда используются операторы SELECT с опцией SQL_CACHE.
Мы рекомендуем использовать have_query_cache в значении ON(1).
Также необходимо проверить значение объема памяти выделяемое сервером под буфер индексов mysql - key_buffer_size.
Мы рекомендуем устанавливать это значение в 15-20% от размера оперативной памяти на сервере.
Открываем в текстовом редакторе файл /etc/my.cnf - и вносим правки. Например, key_buffer_size = 64M.
Для того что бы настройки применились необходимо перезапустить сервер БД (/etc/init.d/mysqld restart).
2. Оптимизация запросов.
Один из самых простых способов улучшить производительность базы данных - это добавить индексы к таблицам баз данных.
Проверить эффективность существующих индексов в базе данных можно с помощью запроса SHOW STATUS LIKE 'handler_read%';
mysql> SHOW STATUS LIKE 'handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 68 |
+-----------------------+-------+ |
Переменная Handler_read_key говорит насколько активно используются индексы, если её значение высокое, то значит ключи активно используются.
При большом значении Handler_read_rnd_next база данных часто подвергает таблицы последовательному сканированию таблицы. В этом случае необходимо добавлять индексы.
Добавление индексов ко всем таблицам, занимает длительное время, поэтому лучше всего выяснить какие запросы больше всего замедляют работу сайтов, чаще всего используются, и оптимизировать их.
Для этого необходимо активировать логирование длинных запросов.
Добавляем в конфигурационный файл /etc/my.cnf в секции [mysqld] следующие строки:
long_query_time=1
slow_query_log = /var/lib/mysql/mysql-slow-queries.log |
Параметр long_query_time указывает серверу БД учитывать запросы выполняющиеся более 1 секунды.
Эти запросы будут записывать в файл указанный в опции slow_query_log (в разных версиях mysql используется параметр slow_query_log либо log-slow-queries, используйте подходящий вашему серверу), В нашем случае "длинные" запросы будут записывать в файл /var/lib/mysql/mysql-slow-queries.log
Необходимо его создать:
| touch /var/lib/mysql/mysql-slow-queries.log |
, и выдать права серверу БД на запись в него:
| chown mysql:mysql /var/lib/mysql/mysql-slow-queries.log |
После этого перезапускаем сервер БД:
или, в случае использования сервера баз данных Mariadb, команда:
systemctl restart mariadb
|
Через некоторое время файл наполнится записями о "тяжелых" запросах.
Прочитаем его содержимое:
| cat /var/lib/mysql/mysql-slow-queries.log |
Получаем, например:
SET timestamp=1293244487;
# administrator command: Init DB;
# Time: 101225 4:34:50
# User@Host: test[pura] @ localhost []
# Query_time: 2.452726 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
SET timestamp=1293244490;
select ItemID, ItemName, ItemAlias, ItemPic1, BrandID, SexID, NodeTitle, NodeDescription, NodeKeywords from Item where ItemGroupID="15" and ItemVisible=1 and LanguageID=1 order by ItemSortOrder;
# Time: 101225 4:38:51
# User@Host: test[pura] @ localhost []
# Query_time: 3.196396 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
SET timestamp=1293244731;
select ItemID, ItemName, ItemAlias, ItemPic1, BrandID, SexID, NodeTitle, NodeDescription, NodeKeywords from Item where ItemGroupID="27" and ItemVisible=1 and LanguageID=1 order by ItemSortOrder;
# Time: 101225 4:57:50
# User@Host: test[pura] @ localhost []
# Query_time: 2.334119 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
SET timestamp=1293245870;
select ItemID, ItemName, ItemAlias, ItemPic1, BrandID, SexID, NodeTitle, NodeDescription, NodeKeywords from Item where ItemGroupID="22" and ItemVisible=1 and LanguageID=1 order by ItemSortOrder; |
Из них видно, что тормозят запросы к таблице Item;
Посмотрим текущие поля таблицы:
mysql> SHOW CREATE TABLE Item \G;
*************************** 1. row ***************************
Table: Item
Create Table: CREATE TABLE `Item` (
`ItemID` int(11) NOT NULL AUTO_INCREMENT,
`ItemGroupID` int(11) NOT NULL DEFAULT '0',
`BrandID` int(11) NOT NULL DEFAULT '0',
`DepartmentID` int(11) NOT NULL DEFAULT '0',
`SexID` int(2) NOT NULL DEFAULT '1',
`ItemName` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`ItemName2` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`ItemAlias` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`ItemPic1` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`ItemDescription` blob,
`ItemShortDescription` blob,
`ItemPrice` int(11) NOT NULL DEFAULT '0',
`ItemPriceBel` int(11) NOT NULL DEFAULT '0',
`ItemPriceEuro` float NOT NULL DEFAULT '0',
`ItemPriceUSD` float NOT NULL DEFAULT '0',
`ItemPriceMargin` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`ItemPriceDiscount` int(11) NOT NULL DEFAULT '0',
`ItemNumber` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`ItemCode` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`ItemVolume` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`ItemYears` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
`ItemSold` int(11) NOT NULL DEFAULT '0',
`NodeTitle` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`NodeDescription` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`NodeKeywords` blob,
`ItemSortOrder` int(11) NOT NULL DEFAULT '0',
`ItemVisible` int(1) NOT NULL DEFAULT '1',
`LanguageID` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`ItemID`)
) ENGINE=MyISAM AUTO_INCREMENT=11584 DEFAULT CHARSET=utf8
1 row in set (0.00 sec) |
В таблице есть лишь один первичный ключ по полю ItemID.
В запросах идет выборка по столбцу ItemGroupID, ключа для его нет, поэтому добавим его:
| mysql> ALTER TABLE Item ADD INDEX `ItemGroupID`(`ItemGroupID`); |
Анализируем таблицу, что бы ключи применились:
| mysql> ANALYZE TABLE Item; |
Дополнительную информацию вы можете найти в свободных источниках. Рекомендуем вам следующие ресурсы:
Использование оператора EXPLAIN (получение информации о SELECT)
* http://dev.mysql.com/doc/refman/5.6/en/explain.html
* http://www.mysql.ru/docs/man/EXPLAIN.html
|
(27 голос(а))
Эта статья помогла
Эта статья не помогла